Chap 7. PL/SQL

PL/SQL(Procedural Language)은 Orcale에서 제공하는 절차지향적인 SQL 언어이다.

DECLARE 선언부, BEGIN 시작부, EXCEPTION 예외부, END 끝 등의 구조로 이루어져있다.

1. 선언부 DECLARE SECTION

PL/SQL은 Oracle이 제공하는 SQL의 모든 데이타 형을 지원하며 아래 데이타 형들을 추가로 지원한다.

• BOOLEAN : BOOLEAN : True, False, Null
• BINARY_INTEGER (-2,147,483,647~+2,147,483,647)
• PLS_INTEGER (-2,147,483,647~+2,147,483,647)

두 INT 형은 NUMBER보다 작은 범위지만 메모리 적게 차지

2. CONTROL STATEMENTS

• 조건문(IF~THEN~ELSIF~THEN~ELSE~THEN~END IF)
• 반복문(FOR/WHILE ~ LOOP ~ END LOOP) EXIT 탈출

SQL> set serveroutput on
SQL>
DECLARE
	v_sal number :=0;
	v_comment varchar2(20);
BEGIN
	SELECT salary INTO v_sal FROM EMP WHERE ename = '춘향';
	IF v_sal >=200 THEN
		v_comment := 'high sal';
	ELSIF v_sal >100 AND v_sal <200 THEN
		v_comment := 'middle sal';
	ELSE
		v_comment :='low sal';
	END IF;
	DBMS_OUTPUT.PUT_LINE(v_sal||' is a '||v_comment);
END;
/

// 1부터 100까지 1씩 증가
BEGIN
	FOR v_counter IN 1..100 LOOP 
		INSERT INTO temp_table VALUES( v_counter );
	END LOOP;
END;

// 100부터 1까지 1씩 감소
BEGIN
	FOR v_counter IN REVERSE 1..100 LOOP 
		INSERT INTO temp_table VALUES( v_counter );
	END LOOP;
END;

3. CURSOR 

1) 개념

Oracle은 결과집합을 관리 위해 Context Area를 할당하고 Context area의 통제와 관리를 cursor를 이용한다.

 

2) 종류

• explicit cursor : 명시적 커서 cursor_name%cursor_attribute
• implicit cursor : 묵시적 커서 SQL%cursor_attribute

%cursor_attribute 종류

• %FOUND : Boolean의 값을 가지며 fetch가 행을 반환하면 TRUE를 아니면 FALSE를 가진다.
• %NOTFOUND : %FOUND와 반대
• %ISOPEN : 커서가 open되었으면 TRUE를 아니면 FALSE를 가진다.
• %ROWCOUNT : SELEXT 문의 경우, 지금까지 FETCH된 행들의 수를 가진다(나머지 DML은 영향끼친 수)

3) 문법

declare, open, fetch, close

// EXPLICIT CURSOR
DECLARE
	CURSOR c_emp IS select eid, ename from emp;
	v_eid emp.eid%type;
	v_ename emp.ename%type;
BEGIN
	OPEN c_emp;
	LOOP
		FETCH c_emp INTO v_eid, v_ename;
		IF c_emp%NOTFOUND THEN
			EXIT;
		END IF;
		DBMS_OUTPUT.PUT_LINE('eid= '||v_eid || ' ename= ' || v_ename);
    END LOOP;
	CLOSE c_emp;
END;

// IMPLICIT CURSOR
BEGIN
	UPDATE EMP SET ename ='향단' WHERE did = 300;
	IF SQL%NOTFOUND THEN 
		INSERT INTO EMP ( eid, ename, address ) VALUES ( 80, '무대', '중국');
	END IF;
	COMMIT;
END;

4. EXCEPTION

예외적인 상황에 대한 처리를 정의한 것.

(참고로 DECLARE와 RAISE를 통해 사용자 정의 예외도 처리 가능하다)

EXCEPTION
	WHEN exception_name THEN
		Sequence_of_statements1;
	WHEN exception_name THEN
		Sequence_of_statements2;
	[WHEN OTHERS THEN
		Sequence_of_statements3;]
END;

5. EXEC[UTE] (SQL*PLUS CMD)

EXEC statement
EXEC [:bind_variable :=] package.procedure;
EXEC [:bind_variable :=] package.function(parameters);

 

가장 흔히 사용할 수 있는 것은 선언부와 할당을 한 번에 수행하는 것이다.

// 선언부, 할당
DECLARE
	VARIABLE b_ename VARCHAR2(20)
BEGIN 
	:b_ename := '일지매'; 
END;

// EXEC
EXEC :b_ename := '일지매';

6. EXECUTE IMMEDIATE statement

dynamic sql statement 또는 익명의 pl/sql block 실행할 수 있는 문법

// 문법
EXECUTE IMMEDIATE dynamic_sql_string
	[INTO {define_var1 [, define_var2] ... | plsql_record }]
	[USING [IN | OUT | IN OUT] bind_arg1 [, [IN | OUT | IN OUT] bind_arg2] ...];
    

// 실 사용
v_sql := 'CREATE TABLE emp_test (eid number, ename varchar2(20))';
EXECUTE IMMEDIATE v_sql;
v_sql := 'SELECT COUNT(*) cnt FROM '|| :b_table_name;
EXECUTE IMMEDIATE v_sql INTO v_cnt ;
v_sql := 'INSERT INTO emp_test VALUES (' || v_cnt || ', :ename1)';
EXECUTE IMMEDIATE v_sql USING :b_table_name;
DBMS_OUTPUT.PUT_LINE(' 테이블 명 : ' || :b_table_name || ' 데이터 수 : ' || v_cnt);

7. Stored Procedure(저장 프로시저)

• 특정 작업을 수행할 수 있는 named PL/SQL Block이며 parameter를 통한 프로그램 작성이 가능하며 PL/SQL Block으로 구성하여 Function과 Procedure 등을 호출할 수 있다.
• 연속 실행 또는 구현이 복잡한 transaction을 수행하는 PL/SQL Block들을 database에 저장한 후, 반복적으로 사용하기 위해 생성한다.
• Stored Procedure 내의 PL/SQL Block은 어떠한 DML 문도 포함할 수 있으나 DDL 문은 사용할 수 없다.
• 하나의 독립된 트랜잭션으로 처리된다.

CREATE [OR REPLACE] PROCEDURE procedure_name [ (argument [IN | OUT | IN OUT] datatype [, argument [IN | OUT | IN OUT] datatype] ...)]
{IS | AS}
	[변수선언] // DECLARE를 사용하면 오류 발생한다.
BEGIN
	[PL/SQL Block] // SQL 문, PL/SQL 제어문
[EXCEPTION]
	// Exception 처리문
END procedure_name;

{Execute | Exec} procedure_name(parameter_value); // 실행
DROP PROCEDURE procedure_name; // 삭제
ALTER PROCEDURE proc_updateSalaryAmount COMPILE; // 재컴파일

CREATE OR REPLACE PROCEDURE proc_updateSalary(p_eid IN number)
IS
	err1 EXCEPTION;
BEGIN
	IF p_eid < 0 THEN
		RAISE err1;
	END IF;
	
    UPDATE emp SET salary = salary *1.1 WHERE eid = p_eid;
	IF SQL%rowcount = 0 THEN
		DBMS_OUTPUT.PUT_LINE('해당하는 사원이 없습니다.');
	ELSE
		DBMS_OUTPUT.PUT_LINE('사번 '|| p_eid || '급여가 10% 인상되었습니다');
	END IF;
	COMMIT; // 이걸 여기 두느냐 아니냐는 반드시 결정되어야 한다.

	EXCEPTION
		WHEN err1 THEN
			DBMS_OUTPUT.PUT_LINE('eid >= 0');
		WHEN OTHERS THEN
			DBMS_OUTPUT.PUT_LINE(SQLCODE||' '|| SQLERRM);
END proc_updateSalary;

JDBC에서 Stroed Procedure 처리하기

try {
  conn.setAutoCommit(false);
  Statement stmt = conn.createStatement();
  
  sql = "{call proc_updateSal(?,?)}";
  CallableStatement cstmt = null;
  cstmt = conn.prepareCall(sql);
  cstmt.setInt(1,20);
  cstmt.registerOutParameter(2, java.sql.Types.INTEGER);
  cstmt.execute();
  System.out.println(cstmt.getInt(2) + " row(s) updated");
  
  conn.commit();
  conn.setAutoCommit(true);
  cstmt.close();
  conn.close();
} catch(Exception e) {
  System.err.println("sql error = " + e.getMessage());
}

8. Stored Function 저장 함수

• 계산한 결과값을 반환하기 위해서 사용한다.
• Procedure와 유사하지만 IN parameter 만을 사용할 수 있다.
• 반환될 데이터 타입을 return 문에 선언해야 한다.
• PL/SQL Block내에 return 문을 통해서 값을 반환해야 한다.

create or replace function func_tax(p_salary in number)
RETURN number
is
	begin
	if (p_salary is null) then
		return 0;
	else
		return p_salary * 0.1;
	end if;
end;

9. Package 패키지

• DB에 저장된 서로 관련있는 Procedure들과 Function들의 집합이다.
• 장점 : 유지보수에 유리
• 단점 : Package내의 하나의 함수를 호출하면 해당 Package내의 모든 함수가 메모리에 Load됨으로 메모리 공간을 많이 차지한다.
• Package Specification 들로 선언부와 Package Body로 실행을 정의하는 부로 나누어진다.

// PACKAGE SPECIFICATION
CREATE OR REPLACE PACKAGE package_emp_info AS
	PROCEDURE emp_info;
	PROCEDURE dept_sal_info(v_deptno in number);
END package_emp_info;

// PACKAGE BODY
CREATE OR REPLACE PACKAGE BODY package_emp_info AS
PROCEDURE emp_info
IS
	CURSOR emp_cursor IS SELECT id, name, to_char(start_date, 'yyyy/mm/dd') hiredate FROM emp ORDER BY hiredate;
BEGIN
	FOR each_emp IN emp_cursor LOOP
		DBMS_OUTPUT.PUT_LINE('사번 :'||each_emp.id|| ' 성명 :'||each_emp.name ||' 입사일 :'||each_emp.hiredate);
	END LOOP;
EXCEPTION
	WHEN OTHERS THEN
		DBMS_OUTPUT.PUT_LINE(SQLCODE ||' '|| SQLERRM);
END emp_info;

PROCEDURE dept_sal_info(v_deptno in number)
IS
	CURSOR sal_cursor IS SELECT round(avg(salary),3) avg_sal, max(salary) max_sal , min(salary) min_sal FROM emp WHERE id=v_deptno;
BEGIN
	FOR each_emp IN sal_cursor LOOP
		DBMS_OUTPUT.PUTLINE('전체급여평균금액' ||each_emp.avg_sal);
		DBMS_OUTPUT.PUTLINE('최대급여금액' ||each_emp.max_sal);
		DBMS_OUTPUT.PUTLINE('최소급여금액' ||each_emp.min_sal);
	END LOOP;
EXCEPTION
	WHEN OTHERS THEN
		DBMS_OUTPUT.PUT_LINE(SQLCODE ||' '|| SQLERRM);
END dept_sal_info;
END package_emp_info;

10. Trigger(트리거)

1) 개념

• Active Database
• INSERT, UPDATE, 또는 DELETE 문이 TABLE에 대해 행해질 때 implicitly 수행되는 Procedure이다.
• Trigger는 VIEW에 대하여 정의될 수 없다.
• Data integrity 유지와 table이 변경될 때 필요한 동작을 명시하기 위해 사용된다.
• 주로 LOG 기록용으로 적절하다, 실무에서는 제약조건을 구현하기에는 적절치 않다.

2) 종류

• ROW Trigger(행 트리거)
• STATEMENT Trigger(문 트리거)
• BEFORE/AFTER

3) 특징

• Trigger는 Table에 연관되어 있으므로 Trigger 유형에 설정된 연관된 Table에 Insert, Update, Delete 문 실행 시 자동으로 Oracle Server에 의해 호출되어 실행된다.
• Trigger에는 transaction control statement(COMMIT, ROLLBACK, SAVEPOINT)를 사용할 수 없다. 따라서 COMMIT, ROLLBACK 문을 실행하는 Procedure, Function, Package를 호출할 수 없다.
• Triggering Statement가 COMMIT, ROLLBACK될 때 Trigger의 작업도 COMMIT, ROLLBACK 된다. 즉, Triggering Statement가 trigger 작업을 실행시키므로 그 둘은 같은 트랜잭션에 있게 된다.
• Trigger의 PL/SQL Block 내에서는 LONG 또는 LONG RAW 변수를 선언할 수 없다. 또한 :new, :old는 trigger가 정의되는 테이블에서 LONG 또는 LONG RAW 칼럼을 참조할 수 없다.
• Error 발생 시에는 RAISE_APPLICATION_ERROR Built-In Procedure를 사용하여 Error를 표시한다. RAISE_APPLICATION_ERROR Procedure에서는 Error Number가 20000 - 20999의 Range를 가진다.

// 테이블 EMP의 record들에서 salary가 변경되면, 
// 기존 salary 보다 적도록 변경되거나 20%를 초과하여 변경되는 경우,
// 오류를 발생시키는 trigger trig_limitSalaryUpdate를 작성하시오

create or replace trigger trig_limitSalaryUpdate
after update of salary on emp
for each row
begin
	if (:old.salary > :new.salary) or (:old.salary * 1.2 < :new.salary) then
		raise_application_error(-20001, '급여의 변경범위가 맞지 않습니다');
	end if;
end;

// 테이블 EMP에 수행되는 각 insert (delete, update) 문에 대하여
// 테이블 monitor에 “emp에 행 입력(삭제, 변경)”이라는 행을 삽입하는 
// trigger trig_monitor를 작성하시오.

create or replace trigger trg_monitor
before insert OR delete OR update on emp
begin
	if INSERTING then
		insert into monitor values('EMP에 행입력', SYSDATE);
	elsif DELETING then
		insert into monitor values('EMP에서 행삭제', SYSDATE);
	elsif UPDATING then
		insert into monitor values('EMP에서 행변경', SYSDATE);
	end if;
end;

CF) Update 와 Delete에 대한 CASCADE 에 대한 제약조건은 Trigger로 처리하지 않아야 함!