SQL 의장점과단점 PL/SQL 오라클 DB 지역정보부서코드지역 1 서울 2 부산 < 장점 > (1) 사용자가이해하기쉬운단어로구성 (2) 쉽게배울수있다. (3) 복잡한로직을간단하게작성할수있다. (4) ANSI에의해문법이표준화되어있다. SQL < 단점 > Consultant 주종면 jina6678@yahoo.co.kr 1 (1) 반복처리를할수없다.(Loop) (2) 비교처리를할수없다.(IF) (3) Error 처리를할수없다.( 예외처리 ) (4) SQL문을캡슐화할수없다. (5) 변수선언을할수없다. (6) 실행할때마다분석작업후실행 (7) Network Traffic을유발한다. 4 PL/SQL 의생성과실행 - 목차 - 1장. PL/SQL 2장. 커서와에러처리 3장. 패키지와트리거 4장. PL/SQL의관리 2 오라클 DB 급여계산 Procedure SALARY_CAL EMP 테이블 DEPT 테이블 SQL> Create Procedure SALARY_CAL Select * INTO :a,m :b From emp Where id = 10; If SQL%NotFound then. SQL> Execute SALARY_CAL; 1. 반복처리를할수있다.(Loop) 2. 비교처리를할수있다.(IF) 3. Error 처리를할수있다.( 예외처리 ) 4. SQL문을캡슐화할수있다.( 데이터의보안및무결성 ) 5. 변수선언을할수있다. 6. 실행할때마다분석된결과를실행만하기때문에성능이빠르다. 7. Network Traffic이감소된다. 5 PL/SQL 의종류 Anonymous Procedure Stored Procedure 1 PL-SQL 의개념 Package Stored Function Trigger Object Type 3 6
PL/SQL-BLOCK 구조 Syntax. 선언부 ( 변수, 상수 ) 1) SCLAR 변수 2) TABLE 변수 3) RECORD 변수. 예외처리부 1) 미리정의된예외 - NO_DATA_FOUND - TOO_MANY_ROWS 2) 정의안된예외 - PRAGMA INIT 3) SQLCODE, SQLERRM. 실행부 1) SQL 문 - Select / Update 문 - Insert / Delete 문 - Commit / Rollback 문 2) 비교문 - IF ~ THEN ~ ELSE ~ 3) 제어문 - BASIC LOOP - FOR IN LOOP -WHILE LOOP 4) CURSOR 속성 -%RIWCOUNT - %FOUND - %NOTFOUND - %ISOPEN 7 Create [or Replace] Function [ 함수 ] ( argument1 [mode] data_type1, argument2 [mode] data_type2, Return data_type; IS [AS] Return ( 변수 ); Exception. Drop Function [ 함수 ] ;. [ 함수 ] : 데이터베이스내에저장될프로시저이름. Argument : 운영체계에서프로시저내로어떤값을전달할때의변수. Mode : IN, OUT, IN OUT. Data_Type : Argument 변수의데이터타입. ~ End : 실행하려는처리절차 10 PL/SQL 의기본문법 함수개발절차 구분 Anonymous Procedure Stored Procedure Stored Function 형식 Declare ~ ~ Exception ~ Create or Replace Procedure [ 프로시저 ] Exception Create or Replace Function [ 함수 ] Return Exception 1. O/S 의편집기로파일생성 (chk_sal.sql) Create Function CHK_SAL IS ( v_sal number; ) Return number; v_chk := v_sal * 0.01; return ( v_chk ); 2 $ sqlplus scott/tiger SQL> @chk_sal.sql 3 Compiler (pcode 생성 ) Package Trigger Create or Replace Package [ 패키지 ] ~ Create or Replace Package Body ~ Create or Replace Trigger [ 트리거 ] AFTER [BEFORE] [ 조건 ] 8 4 SQL> VARIABLE v_sal number SQL> EXECUTE :v_sal := chk_sal (7900); CHK_SAL 11 Stored Function Function 의실행방법. EXECUTE 령어에의한실행 (SQL*Plus 환경 ) SQL> VARIABLE v_sal number SQL> EXECUTE :v_sal := chk_sal (7900); SQL> PRINT v_sal v_sal --------------------- 10. 표현식의일부로서호출될수있습니다. SQL> SELECT empno, sal, chk_sal(empno) FROM emp WHERE deptno = 10; 9 SQL> SELECT SUM(sal) FROM emp WHERE deptno = 10;. Select-list절, Where과 Having절. Connect By, Start With, Order By, Group By 절. Insert문의 Values절과 Update문의 Set절 12
PL/SQL 에서 SQL 문 SELECT [column-1],, [column-n] INTO {variable-1,, variable-n} FROM table_name WHERE [ 조건절 ] ; Stored Procedure INSERT INTO table_name VALUES ( [variable-1,, variable-n] ); UPDATE table_name WHERE [ 조건절 ] ; SET [column-1] = value] 13 DELETE FROM table_name WHERE [ 조건절 ] ; 16 Syntax Procedure 의실행방법 Create [or Replace] Procedure [ 프로시저 ] ( argument1 [mode] data_type1, argument2 [mode] data_type2, IS [AS] Exception.. EXECUTE 령어에의한실행 (SQL*Plus 환경 ) SQL> EXECUTE a_dept ( v_name, 서울 );. Anonymous Procedure 에서호출에의한실행 SQL> Declare ( v_name IN varcahr2) a_dept ( v_name, 서울 ); Drop Procedure [ 프로시저 ] ;. 하나의 Stored Procedure 에서호출에의한실행. [ 프로시저 ] : 데이터베이스내에저장될프로시저이름. Argument : 운영체계에서프로시저내로어떤값을전달할때의변수. Mode : IN, OUT, IN OUT. Data_Type : Argument 변수의데이터타입. ~ End : 실행하려는처리절차 14 SQL> Create or Replace Procedure a_emp ( v_loc IN varcahr2) a_dept ( 총무과, v_loc); 17 프로시저개발절차 매개변수의종류 1. O/S의편집기로파일생성 (salary_cal.sql) Create Procedure SALARY_CAL Select * INTO :a,m :b From emp Where id = 10; If SQL%NotFound then. / 2 $ sqlplus scott/tiger SQL> @salary_cal.sql 3 Compiler (pcode 생성 ) % sqlplus scott/tiger Sql> variable c number; Sql> Execute test(1234, 100, :c); Sql> print c; c ------- 1234 Create Procedure test ( a IN number; b IN OUT number; c OUT number; C := 1234; 4 SQL> EXECUTE salary_cal SALARY_CAL 15 18
매개변수의전달방법. 위치에의한전달방법 (Positional Method). 변수에의한전달방법 (Named Method). 위치 / 변수에의한전달방법 (Combination Method) Type 변수 변수 Table.Column %TYPE ; SQL> Create or Replace Procedure a_dept ( v_name IN varcahr2, v_loc IN varchar2) INSERT INTO dept VALUES (seq_dept.nextval, v_name, v_loc); Positional Method Named Method V_no dept.deptno%type; V_name dept.dname%type; V_loc dept.loc%type; * Dept 테이블 * Deptno number(2) Dname varchar2(13) Loc varchar2(14) SQL> a_dept ( 총무과, 서울 ); a_dept ( v_loc => 서울, v_name => 전산과 ); a_dept ( 자재과, v_loc => 부산 ); Combination Method 19 22 PL/SQL 변수. SCLAR 변수 V_sal number := 10; V_name varchar2;. References 변수 (%TYPE형) V_empno emp.empno%type; RowType 변수 변수 Table %ROWTYPE ;. 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; V_dept dept%rowtype; v_dept.deptno := 10 ; v_dept.dname := 주종면 ; v_dept.deptno := 서울 ; * Dept 테이블 * Deptno number(2) Dname varchar2(13) Loc varchar2(14). Bind 변수 Variable V_empno number; 20 23 Sclar 변수 Record 변수 변수 [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 사이의정수 CHAR 고정길이문자 VARCHAR2 가변길이문자 LONG 대용량의고정길이문자 (2GB) LONG RAW 대용량의이진 (Binary) 문자 (2GB) DATE 날짜와시간 21 BOOLEAN 참과거짓을표현 (True, False) TYPE [type] IS RECORD ( field-1 data_type-1,, field-n data_type-n); Record Type ; Type dept_record_type IS RECORD ( deptno NUMBER(2), dname VARCHAR2(13), loc VARCHAR2(14)); Dept_record dept_record_type; dept_record.deptno := 10; dept_record.dname := 주종면 ; dept_record.loc := 서울 ; 24
Composite 변수 TYPE [type] IS TABLE OF { Column_type Or variable%type or 테이블.column%type } INDEX BY BINARY_INTEGER; 식별자 Type ; 비교문 IF [ 조건절 ] THEN [ 처리문장 ] END IF; Type ename_table_type IS TABLE OF emp.ename%type INDEX BY BINARY_INTEGER; Ename_table ename_table_type; ename_table(1) := 주종면 ; ename_table(2) := 주종면 ; IF ename_table.exists(1) THEN ; 25 IF [ 조건절 ] THEN [ 처리문장 1] ELSE [ 처리문장 2] END IF; IF [ 조건절 1] THEN [ 처리문장 1] ELSIF [ 조건절 2] THEN [ 처리문장 2] ELSE [ 처리문장 3] END IF; 28 Bind 변수 Bind 변수 반복문 % sqlplus scott/tiger Sql> variable c number; Sql> Execute test(1234, 100, :c); Sql> print c; c ------- 1234 Create Procedure test ( a IN number; b IN number; c OUT number; LOOP [ 처리문장 ] EXIT [WHEN [ 조건절 ] FOR [ 변수 ] IN [REVERSE] 최소값.. 최대값 LOOP [ 처리문장 ] C := 1234; WHILE [ 조건절 ]LOOP [ 처리문장 ] 26 29 PL/SQL 블록작성지침 프로시저와함수의차이점 1. 블록내에 SQL문을여러번작성할수있습니다. 2. 식별자는최대 30문자로작성할수있습니다. 3. 식별자는테이블또는컬럼과동일할수없습니다. 4. 식별자는알파벳문자로시작해야합니다. 5. 문자와날짜타입은단일인용부호로표시합니다. 6. 주석은단일라인인경우 2개의대쉬 ( - -) 여러라인인경우에는 /* ~ */ 로표시합니다. 7. 블록내에는 CREATEST, LEAST, DECODE, 그룹함수를사용할수없습니다. 27 프로시저함수 PL/SQL 문장으로실행 (EXECUTE 령문 ) RETURN 데이터형이없음 하나이상의값을반환 표현식의일부로호출또는 Execute 로실행 (SELECT 문의 Seletct-List, WHERE 절과 HAVING 절, CONNECT BY, START WITH 절, INSERT 문의 VALUES 절, UPDATE 문의 SET 절 ) 하나의 RETURN 데이터형이존재 하나의값만반환 30
시적커서의종류 2 Cursor 와에러처리 CURSOR [ 커서 ] IS [SELECT 절 ]; OPEN [ 커서 ]; FOR FETCH [ 커서 ] INTO [ 변수 ]; EXIT WHEN [ 조건절 ]; CLOSE [ 커서 ]; 31 CURSOR [ 커서 ] IS [SELECT 절 ]; FOR [Record] IN [ 처리부 ] [ 커서 ] LOOP 34 CURSOR CURSOR 의속성. PL/SQL 블록내에서실행되는 SELECT 문을의미합니다 1. 암시적커서 ( Implict Cursor ) SELECT empno, ename INTO :v_no, :v_ename FROM emp WHERE deptno = 10; EMPNO ENAME DEPTNO ---------- ---------- -------------- 7369 SMITH 10 종 류 %ROWCOUNT %FOUND 커서의현재 Row Count를리턴커서가현재조건을만족하는지를리턴 2. 시적커서 ( Explict Cursor ) 7499 ALLEN 30 7521 WARD 30 %NOTFOUND 커서가현재조건을만족하지않는지리턴 Cursor C1 IS SELECT empno, ename FROM emp WHERE deptno = 20; OPEN C1; LOOP FETCH C1 INTO v_no, v_ename CLOSE C1; 7566 JONES 20 7654 MARTIN 30 7698 BLAKE 30 7782 CLARK 20 32 %ISOPEN 커서가현재 open 되어있는지를리턴 35 시적 CURSOR CURSOR cursor_name IS [ SELECT 문 ] ; 매개변수와커서 CURSOR 선언 OPEN FETCH OPEN cursor_name; LOOP FETCH cursor_name INTO 변수 ; EXIT WHEN [ 처리내용 ]; CURSOR [ 커서 ] [ (parameter_name data_type,. ) ] IS [SELECT {column_list} FROM table_name WHERE 조건 = parameter_name]; OPEN [ 커서 ]( parameter_value,. ) ; FOR FETCH [ 커서 ] INTO [ 변수 ]; EXIT WHEN [ 조건절 ]; CLOSE [ 커서 ]; CLOSE CLOSE cursor_name; 33 36
Where Current Of 절 미리정의되지않은에러처리 CURSOR [ 커서 ] IS [SELECT {column_list} FROM table_name; OPEN [ 커서 ]; FOR FETCH [ 커서 ] INTO [ 변수 ]; update [table_name] set column = value where current of [ 커서 ] ; EXIT WHEN [ 조건절 ]; CLOSE [ 커서 ]; 예외조건-1 ; PRAGMA _INIT ( [ 예외조건-1], [ 에러코드 ]); WHEN [ 예외조건-1] THEN [ 처리문장-1];. WHEN OTHERS THEN [ 처리문장-n]; e_invalid ; PRAGMA _INIT (e_invalid, -2292); WHEN e_invalid THEN [ 처리문장-1];. WHEN OTHERS THEN [ 처리문장-n]; 37 40 에러처리방법 사용자가정의하는에러처리 1. PL/SQL 블록내의 SQL문이정상적으로실행되지못할때에러가발생합니다. 예외조건-1 ; e_invalid ; 2. 에러가발생하면 절에의해처리할수있습니다. 3. 자주발생하는에러처리기는오라클사에서기본적으로제공하며, 자주발생하지않는에러는사용자가직접정의할수있습니다. Predefined (Internal) Exception UnDefined Exception User Defined Internal Exception Exception Trapping Function 38 RAISE 예외조건-1; WHEN [ 예외조건-1] THEN [ 처리문장-1];. WHEN OTHERS THEN [ 처리문장-n]; RAISE e_invalid; WHEN e_invalid THEN [ 처리문장-1];. WHEN OTHERS THEN [ 처리문장-n]; 41 미리정의된에러처리 예외트래핑함수 예외조건 NO_DATA_FOUND NOT_LOGGED_ON 조건을만족하는행이없는경우 DB에접속하지않은채실행하는경우 * 트래핑함수 예외조건 SQLCODE SQLERRM 에러코드를리턴해줍니다. 에러코드에대한에러메시지를리턴해줍니다. WHEN [ 예외조건-1] THEN [ 처리문장-1]; WHEN [ 예외조건-2] THEN [ 처리문장-2];. WHEN OTHERS THEN TOO_MANY_ROWS VALUE_ERROR ZERO_DEVIDE INVALID_CURSOR INVALID_NUMBER DUP_VAL_ON_INDEX Sclar 변수에여러개의리턴행을저장하는경우지정된변수길이보다큰값을저장하는경우변수값을 0 값으로나누려는경우잘못된커서연산이발생하는경우수의문자열변환이잘못된경우이미저장된값을다시입력하는경우 WHEN OTHERS THEN v_code := SQLCODE; v_mwssage := SQLERRM; RAISE_APPLICATION_ERROR * SQLCODE 종류 예외조건 0 1 +100 양수 에러없이정상적으로실행되었습니다. 사용자가정의한에러가발생하였습니다. 해당조건을만족하는행을찾지못했습니다. 다른오라클서버에러 [ 처리문장 -n]; (-01403, 데이터가존재하지않음 ); * 에러출력함수 Raise_Application_Error ( error_code, message, {TRUE FALSE} ) 39 42
Dynamic-SQL 의시적커서 Dynamic-SQL 43 emp_rec emp%rowtype; sql_stmt VARCHAR2(200); my_job VARCHAR2(15) := 'CLERK'; TYPE EmpCurTyp IS REF CURSOR; emp_cv EmpCurTyp; sql_stmt := 'SELECT * FROM emp WHERE job = :j'; OPEN emp_cv FOR sql_stmt USING my_job; LOOP FETCH emp_cv INTO emp_rec; EXIT WHEN emp_cv%notfound; DBMS_OUTPUT.PUT_LINE(emp_rec.empno ' ' emp_rec.ename); CLOSE emp_cv; 커서의선언커서의시작데이터의인출커서의종료 46 Dynamic-SQL SELECT empno, ename FROM emp Where deptno = :c 검색조건을입력하시오! Condition 10 SELECT empno, ename, deptno FROM emp Where deptno = 10 3 Package와 Trigger Declare sql_stmt VARCHAR2(200); emp_rec emp%rowtype; v_deptno NUMBER(2) := 10; sql_stmt := 'SELECT empno, ename FROM emp WHERE deptno = :id'; EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING v_deptno; 44 47 예제 sql_stmt VARCHAR2(200); emp_id NUMBER(4) := 7934; salary NUMBER(7,2); dept_id NUMBER(2) := 50; dept_name VARCHAR2(14) := 'PERSONNEL'; location VARCHAR2(13) := 'DALLAS'; emp_rec emp%rowtype; EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)'; Package sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)'; EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location; sql_stmt := 'SELECT * FROM emp WHERE empno = :id'; EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id; sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1 RETURNING sal INTO :2'; EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary; EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num USING dept_id; 45 48
패키지 1) 자주사용되는프로그램과로직을모듈화할수있습니다. 2) 응용프로그램을쉽게개발할수있습니다. 3) 프로그램의처리흐름을노출하지않아보안기능이좋습니다. 4) 프로그램에대한유지보수작업이편해집니다. 5) 같은이름의프로시저와함수를여러개생성할수있습니다. 49 오버로딩 1) 패키지내에서동일한이름의프로시저를여러개만들수있습니다. 2) 매개변수의개수, 순서, 데이터타입은달라야합니다. CREATE PACKAGE BODY comm_package IS Procedure reset_comm ( a OUT number, b IN number) IS Procedure reset_comm ( c OUT number, d IN varchar2) IS 52 Syntax Forward Declaration 패키지세 CREATE PACKAGE [package_name] IS 전역변수선언. 어떤프로시저나함수를호출할때해당프로시저보다먼저정의되어야합니다. Procedure [procedure_name1]; CREATE PACKAGE BODY comm_package IS 패키지몸체 CREATE PACKAGE BODY [package_name] IS 지역변수선언 Function [function_name1] IS. Procedure [procedure_name1] IS function1(); 50 Procedure reset_comm ( a OUT number, b IN number) IS Procedure chk_sal () v_sal := reset_comm(1, 2); 53 패키지의실행 CREATE PACKAGE comm_package IS g_comm number := 10; Procedure reset_comm (v_comm IN number); End comm_package; CREATE PACKAGE BODY comm_package IS Procedure reset_comm IS v_comm IN number; Function validate_comm IS. SQL> EXECUTE comm_package.reset_comm(1500); 51 SQL> EXECUTE scott. comm_package.reset_comm(1500); One-Time Only 프로시저. 패키지가사용자세션에서처음으로호출될때 One-Time Olny 프로시저가한번실행됩니다. CREATE PACKAGE BODY comm_package IS Procedure reset_comm ( a OUT number, b IN number) IS SELECT AVG(comm) INTO g_comm FROM emp; 54
패키지함수 Data Integrity. 호출되는패키지내의테이블과변수에대한읽기 / 쓰기를제한할때사용되는함수입니다. PRAGMA RESTRICT_REFERENCES ( function_name, WNDS, [WNPS] [RNDS] [RNPS] ); 데이터베이스내의테이블을수정할수없습니다. 패키지내의변수값을수정할수없습니다. 데이터베이스내의테이블을 SELECT 할수없습니다. 패키지내의전역변수값을수정할수없습니다. 55 사원관리 입력 수정 삭제 사원번호 2001 성주종면 급여 10000 종료 메시지사원번호는 5 자리입니다!!!! Create trigger chk_emp Before update or delete or update on emp If Len(:new.no) <> 5 Then Dbms_output.put_line( 입력할수없음!! ); end if; Create table emp ( no number(4) Primary Key, name varchar2(10) Not Null, Loc vaㅋrchar2(15) CHECK (Loc in ( 서울, 부산 )), jumin_no char(15) Unique, deptno number(2) References dept(deptno)); 58 오라클제공패키지 트리거 종류예제 DBMS_OUTPUT PL/SQL 블록내의변수데이터를화면에출력할때사용합니다. DBMS_SQL PL/SQL 블록내에서 Dynamic-SQL 문을구현할때사용합니다. DBMS_JOB 특정 PL/SQL 블록을스케쥴링하여실행할때사용합니다. DBMS_DDL PL/SQL 블록내에서 DDL 문을실행할때사용합니다. DBMS_PIPE DB 에접속된다른사용자에게메시지를전송할때사용합니다. DBMS_AQ DBMS_PIPE 의향상된기능으로 DB 에접속된다른사용자에게메시지를전송할때사용합니다. DBMS_SESSION DB 에접속된다른사용자의정보를참조할때사용합니다. 오라클 DB Database 트리거 CHECK_SALARY EMP EMPNO ENAME SALARY ----------------------------------------------- 7838 KING 5000 7698 BLAKE 2850 7369 CLERK 800 7788 SCOTT 3000 CREATE TRIGGER CHECK_SALARY BEFORE update or delete or insert on EMP IF (to_char(sysdate, DY ) IN ( SAT, SUN )) THEN raise_application_error(-20500, 주말에는변경할수없습니다!! ); END IF; SQL> UPDATE emp SET sal = sal * 2; ORA-20500 Error 주말에는변경할수없습니다!! 시스템날자 : 2002. 7. 6( 토 ) UTL_FILE O/S 상의텍스트파일을 PL/SQL 내에서읽을때사용합니다. 56 59 트리거구성요소 구성요소 가능한값 트리거유형 Statement Level Row Level 트리거몸체의내용이몇번이나실행되는가? 트리거타이밍 BEFORE AFTER 사용자가트리거이벤트를유발시킬때데이터베이스트리거를언제실행하는가? Trigger 트리거이벤트 INSERT UPDATE DELETE 테이블상에어떤데이터조작연산이트리거를발생시키는가? 트리거몸체 PL/SQL 블록 어떤이벤트가발생하면어떤작업이수행되는가? 트리거조건 WHEN [ 조건 ] 사용자의트리거이벤트중에조건을만족하는데이터만트리거합니다. 57 60
Statement Trigger. 기본테이블에 DML문이실행되면여러행에대한변경이발생하더라도트리거가 한번만발생합니다. EMP 사원번호 사원 직급 급여 부서번호 1 주종면 차장 300 10 UPDATE EMP 2 주영현 과장 250 10 SET 급여 = 급여 * 1.1 ; 3 홍경옥 과장 200 20 61 Syntax CREATE TRIGGER trigger_name IS [ 타이밍 ] [ 이벤트 -1] [OR 이벤트 -2 OR,,,,,,,] ON table_name FOR EACH ROW [WHEN 조건절 ],,,,,,, CREATE TRIGGER check_emp IS BEFORE INSERT or UPDATE or DELETE ON emp FOR EACH ROW WHEN ( :new.job = SALESMAN ),,,,,,, 64 Syntax CREATE TRIGGER trigger_name IS [ 타이밍 ] [ 이벤트 -1] [OR 이벤트 -2 OR,,,,,,,] ON table_name,,,,,,, CREATE TRIGGER check_emp IS BEFORE INSERT or UPDATE or DELETE ON emp,,,,,,, Create trigger backup_emp Before update or delete or update on emp If Inserting then Insert into back_emp(deptno, dname, sal, gu) Values(:new.deptno,:new.name,:new.sal, 입력 ); Elsif Updating then Insert into back_emp Values(:new.deptno,:new.name,:new.sal, :old.deptno,:old.name,:old.sal, 수정 ); Elsif Deleting then Insert into back_emp Values(:old.deptno,:old.name,:old.sal, 삭제 ); end if; 62 65 Row-Level Trigger. 기본테이블에 DML문이실행될때여러행에대한변경이발생하면각행에대해트리거가발생합니다. 트리거의변경 1) 트리거의상태를비활성화또는활성화할수있습니다. ALTER TRIGGER trigger_name DISABLE ENABLE; EMP 사원번호 사원 직급 급여 부서번호 1 주종면 차장 300 10 2 주영현 과장 250 10 3 홍경옥 과장 200 20 UPDATE EMP SET 급여 = 급여 * 1.1 ; 2) 해당테이블과관련된모든트리거의상태를비활성화또는활성화할수있습니다. ALTER TABLE table_name DISABLE ENABLE ALL TRIGGER; 3) 트리거를재컴파일할수있습니다. ALTER TRIGGER trigger_name COMPILE; 4) 트리거를삭제할수있습니다. 63 DROP TRIGGER trigger_name; 66
프로시저와트리거의차이점 트리거의응용범위 프로시저 트리거 범위 CREATE PROCEDURE 문법사용 CREATE TRIGGER 문법사용 보안 데이터베이스내테이블에대한변경을제한할수있습니다 생성하면소소코드와실행코드 (P-CODE) 가생성됨 생성하면소소코드와실행코드 (P-CODE) 가생성됨 감사 사용자들의데이터베이스사용에대한모든내용을감시할수있습니다. EXECUTE 령어로실행 생성후자동실행 데이터의무결성 테이블에원치않는데이터가저장되는것을방지할수있습니다. COMMIT, ROLLBACK 실행가능 COMMIT, ROLLBACK 실행안됨 테이블의복제 기본테이블에대한똑같은복사태이블을온라인으로생성, 관리할수있습니다. 연속적작업수행 기본테이블에데이터가입력되면또다른테이블에데이터를변경하는연속적인작업을할수있습니다. 67 70 관리규칙 -1. 변경중인테이블로부터데이터를참조하지마라. EMP Foreign-Key 사원번호 사원 직급 sal 부서번호 1 주종면 차장 300 10 2 주영현 과장 250 10 3 홍경옥 과장 200 20 UPDATE EMP SET SAL = 1500 WHERE empno=30; Error ora-00491 4 PL/SQL 의관리 CREATE OR REPLACE TRIGGER cascade_updates1 BEFORE UPDATE on emp FOR EACH ROW v_sal emp.sal%type; SELECT max(sal) INTO v_sal FROM emp; 68 71 관리규칙 -2. 제약테이블의기본키, 외래키, 유일키컬럼에대해데이터를 변경하지말것. UPDATE DEPT SET deptno = 30 WHERE deptno=10; EMP Foreign-Key 사원번호사원직급급여부서번호 1 주종면차장 300 10 2 주영현과장 250 10 3 홍경옥과장 200 20 부서정보 부서번호부서지역코드 10 전산과 1 20 경영지원과 1 PL/SQL 의관리 User_objects User_trigger User_source 오라클 DB 급여계산 Procedure SALARY_CAL SQL> Create Procedure CHK_SAL IS (v_no number; ) Select * INTO :a,m :b From emp Where id = 10; If SQL%NotFound then. DBMS_OUTPUT.PUTLINE(..); Compile Error.. CREATE OR REPLACE TRIGGER cascade_updates AFTER UPDATE on DEPT FOR EACH ROW UPDATE emp SET emp.deptno = :new.deptno WHERE emp.deptno = :old.deptno; 69 DESCRIBE User_errors DBMS_OUTPUT 패키지 72
USER_OBJECTS USER_TRIGGER. 데이터베이스내의모든객체를참조할수있습니다.. 트리거의종류와상태정보를참조할수있습니다. 컬럼 컬럼 OBJECT_NAME 객체의이름 TRIGGER_NAME 트리거의이름 OBJECT_ID 데이터베이스가객체를관리하는번호 TRIGGER_TYPE 트리거의유형 (BEFORE, AFTER, INSTEAD OF) OBJECT_TYPE CREATED TIMESTAMP 객체의유형 (TABLE, PROCEDURE, FUNCTION, PACKAGE) 객체를마지막으로수정한날짜 객체가마지막으로컴파일된날짜와시간 TRIGGER_EVENT TABLE_LINE REFERENCING _NAMES 트리거이벤트 (UPDATE, INSERT, DELETE) 트리거이벤트가되는테이블 :OLD, :NEW 에대해사용된이름 STATUS 객체의사용가능여부 (VALID, INVALID) WHEN_CLAUSE 행레벨트리거에사용된 WHEN 절의내용 73 STATUS 트리거의사용가능여부 76 USER_SOURCE DESCRIBE 령어. PL/SQL 블록의 Source 내용을참조할수있습니다.. 트리거에사용된 IN/OUT 매개변수의상태를참조할수있습니다. 컬럼 NAME TYPE LINE 객체의이름 객체의유형 (TABLE, PROCEDURE, FUNCTION, PACKAGE) PL/SQL 블록의 SOURCE 코드의행번호 SQL> DESCRIBE CHK_SAL FUNCTION CHK_SAL RETURN NUMBER Argument Name_Type In/Out Derfault -------------------------------------------------------------------------------- V_NO NUMBER IN TEXT PL/SQL 블록의 SOURCE 코드 74 77 USER_ERRORS. PL/SQL 블록생성시발생한에러를참조할수있습니다. 컬럼 NAME TYPE SEQUENCE 객체의이름 객체의유형 (TABLE, PROCEDURE, FUNCTION, PACKAGE) 에러를표시하기위한일련번호 USER_DEPENDENCIES CREATE PROCEDURE B CREATE PROCEDURE A CREATE TABLE EMP IS IS (EMPNO NUMBER, (V_JOB VARCHAR2;) (V_NO NUMBER;) ENAME VARCHAR2(10), JOB VARCHAR2(15),.... SELECT * INTO.. V_NO := B( SALES ); FROM EMP; );. 상호간에참조하는 PL/SQL 블록의의존관계를참조할수있습니다. LINE POSITION TEXT 에러가발생한소스코드의행번호 에러가발생한행의위치 에러내용 SQL> SELECT * FROM USER_DEPENDENCIES WHERE referenced_name IN ( EMP ); NAME Type Referenced_Name Referenced_Name ------------------------------------------------------------------------------------------- A PROCEDURE B PROCEDURE B PROCEDURE EMP TABLE 75 78
DEPTREE 와 IDEPTREE VIEW. 상호간에참조하는 PL/SQL 블록의의존관계를 TREE 형태로참조할수있습니다. SQL> @$HOME/rdbms/admin/utldtree.sql SQL> EXECUTE deptree_fill ( TABLE, SCOTT, EMP ); SQL> SELECT nested_level, type, name FROM deptree ORDER BY seq# ; Nasted_Level Type Name ------------------------------------------------------------------------------------------- 0 PROCEDURE A 1 PROCEDURE B 79