[인터넷DB응용] PL-SQL - 1

PL/SQL 개요

• set echo on
• set serveroutput on: 출력을 위한 옵션

 

---

SQL의 장점과 단점

 

장점

• 사용자가 이해하기 쉬운 단어로 구성
• 쉽게 배울 수 있다.
• 복잡한 로직을 간단하게 작성할 수 있다.
• ANSI에 의해 문법이 표준화되어 있다.

 

단점

• 반복처리를 할 수 없다.(Loop)
• 비교처리를 할 수 없다.(IF)
• Error 처리를 할 수 없다.(예외처리)
• SQL문을 캡슐화 할 수 없다.
• 변수 선언을 할 수 없다.
• 실행할 때 마다 분석작업 후 실행
• Network Traffic을 유발한다.

 

---

PL/SQL의 생성과 실행

• 반복 처리를 할 수 있다.(Loop)
• 비교 처리를 할 수 있다.(IF)
• Error 처리를 할 수 있다.(예외 처리)
• SQL문을 캡슐화 할 수 있다.(데이터의 보안 및 무결성)
• 변수 선언을 할 수 있다.
• 실행할 때 마다 분석된 결과를 실행만 하기 때문에 성능이 빠르다.
• Network Traffic이 감소된다.

 

---

PL/SQL의 종류

 

DECLARE

• Anonymous Procedure, Stored Procedure

BEGIN

• Package, Stored Function

EXCEPTION

• Trigger, Object Type

END;

 

---

PL/SQL-BLOCK 구조

DECLARE

 

선언부 (변수, 상수)

• SCALAR 변수
• TABLE 변수
• RECORD 변수

 

BEGIN

 

실행부

• SQL문
  • Select/Update 문
  • Insert/Delete 문
  • Commit/Rollback 문
• 비교문
  • IF~THEN~ELSE~
• 제어문
  • BASIC LOOP
  • FOR IN LOOP
  • WHILE LOOP
• CURSOR 속성
  • %ROWCOUNT
  • %FOUND
  • %NOTFOUND
  • %ISOPEN

 

EXCEPTION

 

예외 처리부

• 미리 정의된 예외
  • NO_DATA_FOUND
  • TOO_MANY_ROWS
• 정의 안된 예외
  • PRAGMA_EXCEPTION_INIT
• SQLCODE, SQLERRM

 

+ := 대입연산자

 

END;

 

---

PL/SQL의 기본문법

 

구분	형식
Anonymous Procedure	Declare ~ Begin ~ Exception ~ End;
Stored Procedure	Create or Replace Procedrue [프로시저명] Begin Exception End;
Stored Function	Create or Replace Function [함수명] Return Begin Exception End;
Package	Create or Replace Package [패키지명] Begin~End; Create or Replace Package Body Begin ~ End;
Trigger	Create pr Replace Trigger [트리거명] AFTER [BEFORE] [조건] Begin End;

 

---

Stored Procedure

Syntax

Create [or Replace] Procedure [프로시저명]
( argument1 [mode] data_type1,
  argument2 [mode] data_type2,
  ........
IS [AS]
Begin
  ..........
Exception
  .............
End;

Drop Procedure [프로시저명];

• [프로시저명] : 데이터베이스 내에 저장될 프로시저 이름
• Argument : 운영체계에서 프로시저 내로 어떤 값을 전달할 때의 변수
• Mode : IN, OUT, IN OUT
• Data Type: Argument 변수의 데이터 타입
• Begin ~ End : 실행하려는 처리 절차

 

---

프로시저 개발절차

• O/S의 편집기로 파일생성(salary_cal.sql)

Create Procedure SALARY_CAL
Begin
	Select * INTO :a, b
    From emp
    Where id = 10;
    If SQL%NotFound then
      ...
END;
/

@salary_cal.sql

Compiler(pcode 생성)

SALARY_CAL

EXECUTE salary_cal

 

---

PL/SQL에서 SQL문

SELECT [column-1], ..., [column-n]
INTO {variable-1, ..., variable-n}
FROM table_name
WHERE [조건절];

INSERT INTO table_name
VALUES ([variable-1, ..., variable-n]);

UPDATE table_name SET [colunm-1 = value]
WHERE [조건절];

DELETE FROM table_name
WHERE [조건절];

 

---

Procedure의 실행방법

• EXECUTE 명령어에 의한 실행 (SQL*Plus 환경)

EXECUTE a_dept ( v_name, '서울');

• Anonymous Procedure에서 호출에 의한 실행

Declare
  ( v_name IN varchar2 )
  Begin
  	a_dept (v_name, '서울' );
  End;

• 하나의 Stored Procedure에서 호출에 의한 실행

Create or Replace Procedure a_emp
 (v_loc IN varchar2)
 Begin
   a_dept('총무과', v_loc);
 End;

 

---

매개 변수의 종류

-- db_week5_1.sql
Create or replace procedure test1
(a IN number,
 b IN OUT number,
 c OUT number)
IS
Begin
	   c := 1234;
End;
/

-- cmd
variable b number;
variable c number;
execute test1(1234, :b, :c);
print c;

-- 출력
c
-------
1234

 

---

매개 변수의 전달방법

-- db_week5_2.sql
Create or replace procedure test2
(v_sjid IN varchar2,
 v_sjname IN varchar2,
 v_sjdept IN varchar2)
IS
Begin
	  Insert into subject values
      (v_sjid, v_sjname, v_sjdept);
End;
/

-- cmd
Begin
	  test2 ('cm003', 'web', 'computer');
	  test2 (v_sjid => 'ec003', v_sjname => 'leadership', v_sjdept => 'economy');
	  test2 ('ec004', v_sjname => 'statistics', v_sjdept => 'economy');
End;
/

 

---

PL/SQL 변수

DECLARE
------------------> 여기에 변수 작성
BEGIN

EXCEPTION

END;

-- SCALAR 변수
v_sal number:=10;
v_name varchar2;

-- References 변수(%TYPE형)
v_empno emp.empno%type;

-- References 변수(%ROWTYPE형)
v_emp emp%rowtype;

-- Composite 변수(RECORD 형)
TYPE t_emp IS RECORD
(emp_id number(5);
 emp_name varchar2(20);)
 
v_emp t_emp;

-- Composite 변수(TABLE 형)
TYPE t_emp IS TABLE
				OF emp%ROWTYPE
INDEX BY BINARY_INTEGER;
v_emp t_emp;

-- Bind 변수
Variable V_empno nubmer;

 

---

Scalar 변수

변수명 [CONSTANT] [data_type] [Not Null] [:= DEFAULT [표현식]];

V_no NUMBER;
V_name VARCHAR2(15) DEFAULT 'unknown'
V_loc CONSTANT NUMBER(7, 2) := 0.2;

NUMBER: 정수, 소수점을 포함한 숫자
BINARY_INTEGER: -2147483647 ~ +2147483647 사이의 정수
(PLS_INTEGER-10g)
CHAR, NCHAR: 고정길이 문자
VARCHAR2, NVARCHAR2: 가변길이 문자
LONG, CLOB: 대용량의 고정길이 문자(2GB/4GB)
LONG RAW, BLOB: 대용량의 이진(Binary) 문자(2GB/4GB)
RAW: Binary 데이터(최대 32767 바이트)
DATE, TIMESTAMP: 날짜와 시간
BOOLEAN: 참과 거짓을 표현(True, False)

 

---

Type 변수

변수명 Table명.Column명%TYPE;

v_no dept.deptno%type;
v_name dept.dname%type;
v_loc dept.loc%type;

-- db_week5_3.sql
Create or replace procedure test3
IS
	v_stuid varchar2(7);
    v_stuname stu.stu_name%TYPE;
BEGIN
	select stu_id, stu_name into v_stuid, v_stuname from stu
    where stu_id = '9911111';
    DBMS_OUTPUT.PUT_LINE('학번 : ' || v_stuid || ', 이름 : ' || v_stuname);
End;
/

 

---

RowType 변수

변수명 Table명%ROWTYPE;

v_dept dept%rowtype;
......
Begin
 v_dept.deptno := 10;
 v_dept.dname := '주종면';
 v_dept.loc := '서울';
End;

--db_week5_4.sql
Create or replace procedure test4
( v_stuid IN sugang.stu_id%type,
  v_sjid IN sugang.sj_id%type
)
IS
	v_sugang sugang%ROWTYPE;
Begin
	v_sugang.stu_id := v_stuid;
	v_sugang.sj_id := v_sjid;
	Insert into sugang values (v_sugang.stu_id, v_sugang.sj_id); 
End;
/

 

---

Record 변수

TYPE [type명] IS RECORD
( field-1 data_type-1, ..., field-n data_type-n);
Record명

Type dept_record_type IS RECORD
( deptno NUMBER(2),
  dname VARCHAR2(13),
  loc VARCHAR2(14));
Dept_record dept_record_type;
Begin
  dept_record.deptno := 10;
  dept_record.dname := '주종면';
  dept_record.loc := '서울';
  ......
End;

 

---

Composite 변수

TYPE [type명] IS TABLE OF
{ Column_type Or variable%type or 테이블명.column명%type }
INDEX BY BINARY_INTEGER;
식별자 Type명;

Type ename_table_type IS TABLE OF
  emp.ename%Type
INDEX BY BINARY_INTEGER;
ename_table ename_table_type;
Begin
  ename_table(1) := '주종면';
  ename_table(2) := '홍길동';
  ......
  IF ename_table EXISTS(1) THEN ......;
End;

 

---

Bind 변수

• 앞에 : 을 붙여준다.

Create Procedure test1
(a IN number;
 b IN number;
 c OUT number;)
BEGIN
 C:=a+b;
EXCEPTION
END;

-- CMD
variable c number;
execute test(1234, 100, :c);
print c;
c
-------
1334

 

---

비교문

IF [조건절] THEN [처리문장] END IF;

IF [조건절] THEN [처리문장1]
ELSE [처리문장2] END IF;

IF [조건절1] THEN [처리문장1]
ELSIF [조건절2] THEN [처리문장2]
ELSE [처리문장3] END IF;

-- db_week5_5.sql
Create or replace procedure test5
( v_stuid IN stu.stu_id%type )
IS
	v_stuname stu.stu_name%TYPE;
	v_studept stu.stu_dept%TYPE;
Begin
	select stu_name, stu_dept into v_stuname, v_studept from stu
	where stu_id = v_stuid;
	if v_stuid = '9911111' then DBMS_OUTPUT.PUT_LINE('홍길동이네!!!');
	else DBMS_OUTPUT.PUT_LINE('학번 : ' || v_stuid || ', 이름 : ' || v_stuname || ', 학과 : ' || v_studept);
	end if;

End;
/

 

 

 

---

반복문

LOOP [처리문장] EXIT [WHEN [조건절] END LOOP;

FOR [변수] IN [REVERSE] 최소값..최대값 LOOP
     [처리문장]
END LOOP;

WHILE [조건절] LOOP
        [처리문장]
END LOOP;

-- db_week5_6.sql
Create or replace procedure test6
(limit IN number )
IS
i number;
Begin
	for i in 1..5 loop
		DBMS_OUTPUT.PUT_LINE(i);
	end loop;

	i := 0;	

	while i < limit loop
		DBMS_OUTPUT.PUT_LINE(i);
 		i := i + 1;		
	end loop;
End;
/

+ 프로시저는 OUT 매개변수를 써서 값을 리턴할 수 있다.

 

---

PL/SQL 블록 작성지침

 

• 블록 내에 SQL문을 여러 번 작성할 수 있습니다.
• 식별자는 최대 30문자로 작성할 수 있습니다.
• 식별자는 테이블 또는 컬럼 명과 동일할 수 없습니다.
• 식별자는 알파벳 문자로 시작해야 합니다.
• 문자와 날짜 타입은 단일 인용부호로 표시합니다.
• 주석은 단일 라인인 경우 2개의 대쉬( - -) 여러 라인인 경우에는 /* ~ */로 표시합니다.
• 블록 내에는 CREATE, LEAST, DECODE, 그룹함수를 사용할 수 없습니다.

 

---

실습 문제

-- 1. stu 테이블에 학번, 이름, 학과를 입력받아 새로운 학생을 추가시키는 Stored Procedure를 만드시오.
Create Procedure ADD_STUDENT
( v_stuid stu.stu_id%type,
  v_stuname stu.stu_name%type,
  v_studept stu.stu_dept%type)
IS
Begin
	Insert into stu values
	(v_stuid, v_stuname, v_studept);
End;
/

-- 2.  subject 테이블에 과목번호, 과목이름, 개설학과를 입력받아 새로운 과목을 추가시키는 Stored Procedure를 만드시오.
Create Procedure ADD_SUBJECT
( v_sjid subject.sj_id%TYPE,
  v_sjname subject.sj_name%TYPE,
  v_sjdept subject.sj_dept%TYPE)
IS
Begin
	Insert into subject values
	(v_sjid, v_sjname, v_sjdept);
End;
/

-- 3. 특정 학번을 입력받아 sugang 테이블에서 그 학생과 관련된 수강 내역을 삭제하고 stu 테이블에서도 삭제하는 Stored Procedure를 만드시오.
Create Procedure DELETE_SUGANGANDSTU
( v_stuid stu.stu_id%TYPE)
IS
Begin
	delete from sugang where sugang.stu_id=v_stuid;
	delete from stu where stu.stu_id=v_stuid;
End;
/

-- 4. subject 테이블에서 과목번호를 입력받아 과목명을 출력하는 Stored Procedure를 만드시오.(프로시저를 실행하면 출력까지 다 되어야 합니다.)
Create Procedure PRINT_SJNAME
( v_sjid subject.sj_id%TYPE)
IS
  v_sjname subject.sj_name%TYPE;
Begin
	select sj_name into v_sjname from subject where sj_id=v_sjid;
	DBMS_OUTPUT.PUT_LINE('과목명: ' || v_sjname);
End;
/

---

Stored Function

Syntax

Create [or Replace] Function [함수명]
( argument1 [mode] data_type1,
  argument2 [mode] data_type2,
  .........)
  Return data_type
IS [AS]
Begin
  .........
  Return (변수);
Exception
  .........
End;

Drop Function [함수명];

[함수명]: 데이터베이스 내에 저장될 프로시저 이름
Argument: 운영체계에서 프로시저 내로 어떤 값을 전달할 때의 변수
Mode: IN, OUT, IN OUT
Data_Type: Argument 변수의 데이터 타입
Begin ~ End: 실행하려는 처리 절차

 

---

함수 개발절차

• O/S의 편집기로 파일 생성
• Compiler(pcode 생성)

Create or Replace Function CHK_SAL
(v_sal number)
Return  number
IS
  v_chk number;
Begin
      v_chk :=  v_sal * 0.01;
      return v_chk; 
END;
/

-- cmd
variable v_sal number
execute :v_sal := chk_sal(7900);
print v_sal
v_sal
------
79

 

---

function의 실행방법

-- EXECUTE 명령어에 의한 실행
VARIABLE v_sal number
EXECUTE :v_sal := chk_sal(7900);

-- 표현식의 일부로서 호췰 될 수 있음
SELECT empno, sal, chk_sal(empno)
FROM emp
WHERE deptno = 10;

SELECT SUM(sal)
FROM emp
WHERE deptno = 10;

• Select-list절, Where과 Having절
• Connect By, Start With, Order By, Group By 절
• Insert문의 Values절과 Update문의 Set절

 

---

프로시저와 함수의 차이점

프로시저	함수
PL/SQL 문장으로 실행 (EXECUTE 명령문)	표현식의 일부로 호출 또는 Execute로 실행 (SELECT문의 Seletct-List, WHERE절과 HAVING절, CONNECT BY, START WITH절, INSERT문의 VALUES절, UPDATE문의 SET절)
PL/SQL 문장으로 실행 (EXECUTE 명령문)	하나의 RETURN 데이터형이 존재
하나 이상의 값을 반환할 수 있다.	하나의 값은 확실하게 반환, 더 많은 값의 반환도 가능

 

---

Cursor

PL/SQL 블록 내에서 실행되는 SELECT문의 결과 레코드를 의미한다.

• 자바에서의 ResultSet 개념과 유사하다.

-- 암시적 커서 (Implict CURSOR)
SELECT empno, ename
INTO :v_no, :v_ename
from emp
where deptno = 10;

-- 명시적 커서 (Explict Cursor)
Cursor C1 IS
SELECT empno, ename
FROM emp
WHERE deptno = 20;
OPEN C1;
LOOP
 FETCH C1 INTO v_no, v_ename
END LOOP;
CLOSE C1;

 

---

명시적 CURSOR

• CURSOR 선언 -> OPEN -> FETCH -> CLOSE
• FETCH -> 다음 레코드

CURSOR cursor_name IS
[SELECT문];

OPEN cursor_name;

LOOP
 FETCH cursor_name INTO 변수;
 EXIT WHEN [처리내용];
END LOOP;

CLOSE cursor_name;

 

---

명시적 커서 처리 방법

CURSOR [커서명]
IS
    [SELECT 절];
OPEN [커서명];
LOOP
  FETCH [커서명] INTO [변수명];
  EXIT WHEN [조건절];
END LOOP;
CLOSE [커서명];

CURSOR [커서명]
IS
    [SELECT 절];
FOR [Record명] IN [커서명] LOOP
	[처리부]
END LOOP;

-- db_week5_8.sql
-- Cursor 예제1. 학과가 'computer'인 학생들의 학번과 이름을 보여 주는 프로시저 만들기
Create or replace procedure show_com
IS
   v_stuid  stu.stu_id%TYPE;
   v_stuname  stu.stu_name%TYPE;
   CURSOR stu_cursor IS
   select stu_id, stu_name from stu
   where stu_dept = 'computer';
BEGIN
   open stu_cursor;
   loop 
      fetch stu_cursor into v_stuid, v_stuname;
      exit when stu_cursor%NOTFOUND;
      
      dbms_output.put_line(v_stuid || ' ' || v_stuname);
   end loop;
   close stu_cursor;
END show_com;
/

Create or replace procedure show_com2
IS
   v_stu  stu%ROWTYPE;
   CURSOR stu_cursor IS
   select * from stu
   where stu_dept = 'computer';
BEGIN

   for v_stu IN stu_cursor loop 

      exit when stu_cursor%NOTFOUND;
      dbms_output.put_line(v_stu.stu_id || ' ' || v_stu.stu_name);

   end loop;
   
END show_com2;
/

 

---

CURSOR의 속성 변수

종류	설명
%ROWCOUNT	커서의 현재 Row Count를 리턴
%FOUND	커서가 fetch할 레코드가 있으면 true를 리턴
%NOTFOUND	커서가 fetch할 레코드가 있으면 true를 리턴
%ISOPEN	커서가 현재 open되어 있으면 true를 리턴

 

---

매개변수와 커서

CURSOR [커서명]
   [ (parameter_name, data_type, .... ) ]
IS
	 [SELECT {column_list}
      FROM table_name
      WHERE 조건=parameter_name];
OPEN [커서명](parameter_value, ... );
FOR
  FETCH [커서명] INTO [변수명];
  EXIT WHEN [조건절];
END LOOP;
CLOSE [커서명];

-- db_week5_9.sql
-- Cursor 예제2. 커서 매개 변수로 학번을 집어넣어서 그 학번에 해당하는 학생이 수강하는 과목의
--               과목 코드를 보여 주는 프로시저 만들기

Create or replace procedure show_subid
IS
   v_sjid  sugang.sj_id%TYPE;

   CURSOR sg_cursor(v_stuid varchar2) IS
   select sj_id from sugang
   where stu_id = v_stuid;
BEGIN
   open sg_cursor('9922222');
   loop 
      fetch sg_cursor into v_sjid;
      exit when sg_cursor%NOTFOUND;
      
      dbms_output.put_line(v_sjid);
   end loop;
   close sg_cursor;
END show_subid;
/

 

---

Where Current Of 절

• 처리 중인 데이터베이스 커서의 위치를 참조하는 데 사용되는 절이다.
• 일반적으로 데이터베이스에서 특정 레코드를 업데이트(UPDATE)하거나 삭제(DELETE)할 때 사용된다.
• for update; -> 업데이트 하기 위한 커서임을 알려준다.

CURSOR [커서명]
IS
	[SELECT {column_list}
   	FROM table_name;
OPEN [커서명];
FOR
  FETCH [커서명] INTO [변수명];
    update [table_name]
    set column = value
    where current of [커서명];
  EXIT WHEN [조건절];
END LOOP;
CLOSE [커서명];

-- db_week5_10.sql
-- Cursor 예제3. stu 테이블에서 이름이 'sslee'인 학생의 이름을 'cmlee'로 바꾸는 프로시저 만들기

Create or replace procedure up_name
IS
   v_stuname  stu.stu_name%TYPE;
   CURSOR up_cursor IS
   select stu_name from stu
   where stu_name = 'sslee'
   for update;
BEGIN
   open up_cursor;
   loop 
      
      fetch up_cursor into v_stuname;
      exit when up_cursor%NOTFOUND;
      update stu set stu_name = 'cmlee'
      where current of up_cursor; 
      
   end loop;
   close up_cursor;
END up_name;
/

 

---

실습 문제

-- 5. 커서를 이용하여 매개 변수로 학번을 입력받아 이 학생이 어떤 과목을 수강하고 있는지 과목번호와 과목 이름을 모두 출력하는 프로시저를 만드시오. 
Create or replace procedure SHOW_SUGANGSUBJECT
( v_stuid stu.stu_id%TYPE)
IS
  v_subject subject%ROWTYPE;
  CURSOR sj_cursor(vv_stuid stu.stu_id%TYPE) IS
  select * from subject
  where sj_id in (select sj_id from sugang where stu_id=vv_stuid);
Begin
  open sj_cursor(v_stuid);
  loop
     fetch sj_cursor into v_subject;
     exit when sj_cursor%NOTFOUND;
  
     DBMS_OUTPUT.PUT_LINE('과목번호 : ' || v_subject.sj_id ||', 과목이름: ' || v_subject.sj_name);
  end loop;
  close sj_cursor;
END SHOW_SUGANGSUBJECT;
/