Microsoft PowerPoint - PLSQL.ppt

Size: px

Start display at page:

Download "Microsoft PowerPoint - PLSQL.ppt"

권순 예
6 years ago
Views:

1 PL/SQL Consultant 주종면 1 - 목차 - 1장. PL/SQL 2장. 커서와에러처리 3장. 패키지와트리거 4장. PL/SQL의관리 2

2 1 PL-SQL 의개념 3 SQL 의장점과단점 < 장점 > 오라클 DB 지역정보부서코드지역명 1 서울 2 부산 SQL (1) 사용자가이해하기쉬운단어로구성 (2) 쉽게배울수있다. (3) 복잡한로직을간단하게작성할수있다. (4) ANSI 에의해문법이표준화되어있다. < 단점 > (1) 반복처리를할수없다.(Loop) (2) 비교처리를할수없다.(IF) (3) Error 처리를할수없다.( 예외처리 ) (4) SQL문을캡슐화할수없다. (5) 변수선언을할수없다. (6) 실행할때마다분석작업후실행 (7) Network Traffic을유발한다. 4

3 PL/SQL 의생성과실행 오라클 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 DECLARE Stored Procedure Package Stored Function EXCEPTION Trigger Object Type 6

4 PL/SQL-BLOCK 구조. 선언부 ( 변수, 상수 ) 1) SCLAR 변수 2) TABLE 변수 3) RECORD 변수. 예외처리부 1) 미리정의된예외 -NO_DATA_FOUND -TOO_MANY_ROWS 2) 정의안된예외 - PRAGMA_EXCEPTION_INIT 3) SQLCODE, SQLERRM DECLARE EXCEPTION. 실행부 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 PL/SQL 의기본문법 구분 Anonymous Procedure Stored Procedure 형식 Declare ~ ~ Exception ~ Create or Replace Procedure [ 프로시저명 ] Exception Stored Function Create or Replace Function [ 함수명 ] Return Exception Package Create or Replace Package [ 패키지명 ] ~ Create or Replace Package Body ~ Trigger Create or Replace Trigger [ 트리거명 ] AFTER [BEFORE] [ 조건 ] 8

5 Stored Function 9 Syntax 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

6 함수개발절차 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 3 Compiler (pcode 생성 ) 4 SQL> VARIABLE v_sal number SQL> EXECUTE :v_sal := chk_sal (7900); CHK_SAL 11 Function 의실행방법. EXECUTE 명령어에의한실행 (SQL*Plus 환경 ) SQL> VARIABLE v_sal number SQL> EXECUTE :v_sal := chk_sal (7900); SQL> PRINT v_sal v_sal 표현식의일부로서호출될수있습니다. SQL> SELECT empno, sal, chk_sal(empno) FROM emp WHERE deptno = 10; 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

7 Stored Procedure 13 Syntax Create [or Replace] Procedure [ 프로시저명 ] ( argument1 [mode] data_type1, argument2 [mode] data_type2, IS [AS] Exception. Drop Procedure [ 프로시저명 ] ;. [ 프로시저명 ] : 데이터베이스내에저장될프로시저이름. Argument : 운영체계에서프로시저내로어떤값을전달할때의변수. Mode : IN, OUT, IN OUT. Data_Type : Argument 변수의데이터타입. ~ End : 실행하려는처리절차 14

8 프로시저개발절차 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 3 Compiler (pcode 생성 ) 4 SQL> EXECUTE salary_cal SALARY_CAL 15 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 WHERE [ 조건절 ] ; SET [column-1] = value] DELETE FROM table_name WHERE [ 조건절 ] ; 16

9 Procedure 의실행방법. EXECUTE 명령어에의한실행 (SQL*Plus 환경 ) SQL> EXECUTE a_dept ( v_name, 서울 );. Anonymous Procedure 에서호출에의한실행 SQL> Declare ( v_name IN varcahr2) a_dept ( v_name, 서울 );. 하나의 Stored Procedure 에서호출에의한실행 SQL> Create or Replace Procedure a_emp ( v_loc IN varcahr2) a_dept ( 총무과, v_loc); 17 매개변수의종류 % sqlplus scott/tiger Sql> variable c number; Sql> Execute test(1234, 100, :c); Sql> print c; c Create Procedure test ( a IN number; b IN OUT number; c OUT number; C := 1234; EXCEPTION 18

10 매개변수의전달방법. 위치에의한전달방법 (Positional Method). 변수명에의한전달방법 (Named Method). 위치 / 변수명에의한전달방법 (Combination Method) 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); SQL> a_dept ( 총무과, 서울 ); a_dept ( v_loc => 서울, v_name => 전산과 ); a_dept ( 자재과, v_loc => 부산 ); Positional Method Named Method Combination Method 19 PL/SQL 변수 DECLARE EXCEPTION. SCLAR 변수 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 number; 20

11 Sclar 변수 변수명 [CONSTANT] [data_type] [Not Null] [:= DEFAULT [ 표현식 ]]; DECLARE V_no NUMBER; V_name VARCHAR2(15) DEFAULT unknown V_loc CONSTANT NUMBER(7, 2) := 0.2; NUMBER 정수, 소수점을포함한숫자 BINARY_INTEGER CHAR VARCHAR2 LONG LONG RAW ~ 사이의정수고정길이문자가변길이문자대용량의고정길이문자 (2GB) 대용량의이진 (Binary) 문자 (2GB) DATE BOOLEAN 날짜와시간 참과거짓을표현 (True, False) 21 Type 변수 변수명 Table 명.Column 명 %TYPE ; DECLARE 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) 22

12 RowType 변수 변수명 Table 명 %ROWTYPE ; DECLARE V_dept dept%rowtype; v_dept.deptno := 10 ; v_dept.dname := 주종면 ; v_dept.deptno := 서울 ; * Dept 테이블 * Deptno number(2) Dname varchar2(13) Loc varchar2(14) 23 Record 변수 TYPE [type명] IS RECORD ( field-1 data_type-1,, field-n data_type-n); Record명 Type명 ; DECLARE 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

13 Composite 변수 TYPE [type명] IS TABLE OF { Column_type Or variable%type or 테이블명.column명%type } INDEX BY BINARY_INTEGER; 식별자 Type명 ; DECLARE 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 Bind 변수 Bind 변수 % sqlplus scott/tiger Sql> variable c number; Sql> Execute test(1234, 100, :c); Sql> print c; c Create Procedure test ( a IN number; b IN number; c OUT number; C := 1234; EXCEPTION 26

14 PL/SQL 블록작성지침 1. 블록내에 SQL 문을여러번작성할수있습니다. 2. 식별자는최대 30 문자로작성할수있습니다. 3. 식별자는테이블또는컬럼명과동일할수없습니다. 4. 식별자는알파벳문자로시작해야합니다. 5. 문자와날짜타입은단일인용부호로표시합니다. 6. 주석은단일라인인경우 2 개의대쉬 ( - -) 여러라인인경우에는 /* ~ */ 로표시합니다. 7. 블록내에는 CREATEST, LEAST, DECODE, 그룹함수를사용할수없습니다. 27 비교문 IF [ 조건절 ] THEN [ 처리문장 ] END IF; IF [ 조건절 ] THEN [ 처리문장 1] ELSE [ 처리문장 2] END IF; IF [ 조건절 1] THEN [ 처리문장 1] ELSIF [ 조건절 2] THEN [ 처리문장 2] ELSE [ 처리문장 3] END IF; 28

15 반복문 LOOP [ 처리문장 ] EXIT [WHEN [ 조건절 ] END LOOP; FOR [ 변수 ] IN [REVERSE] 최소값.. 최대값 LOOP [ 처리문장 ] END LOOP; WHILE [ 조건절 ] LOOP [ 처리문장 ] END LOOP; 29 프로시저와함수의차이점 프로시저함수 PL/SQL 문장으로실행 (EXECUTE 명령문 ) RETURN 데이터형이없음 하나이상의값을반환 표현식의일부로호출또는 Execute 로실행 (SELECT 문의 Seletct-List, WHERE 절과 HAVING 절, CONNECT BY, START WITH 절, INSERT 문의 VALUES 절, UPDATE 문의 SET 절 ) 하나의 RETURN 데이터형이존재 하나의값만반환 30

16 2 Cursor 와에러처리 31 CURSOR. PL/SQL 블록내에서실행되는 SELECT 문을의미합니다 1. 암시적커서 ( Implict Cursor ) SELECT empno, ename INTO :v_no, :v_ename FROM emp WHERE deptno = 10; 2. 명시적커서 ( 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; EMPNO ENAME DEPTNO SMITH ALLEN WARD JONES MARTIN BLAKE CLARK 20 32

17 명시적 CURSOR CURSOR cursor_name IS [ SELECT 문 ] ; CURSOR 선언 OPEN cursor_name; OPEN FETCH LOOP FETCH cursor_name INTO 변수 ; EXIT WHEN [ 처리내용 ]; END LOOP; CLOSE CLOSE cursor_name; 33 명시적커서의종류 CURSOR [ 커서명 ] IS [SELECT 절 ]; OPEN [ 커서명 ]; FOR FETCH [ 커서명 ] INTO [ 변수명 ]; EXIT WHEN [ 조건절 ]; END LOOP; CLOSE [ 커서명 ]; CURSOR [ 커서명 ] IS [SELECT 절 ]; FOR [Record명] IN [ 처리부 ] END LOOP; [ 커서명 ] LOOP 34

18 CURSOR 의속성 종류설명 %ROWCOUNT 커서의현재 Row Count 를리턴 %FOUND 커서가현재조건을만족하는지를리턴 %NOTFOUND 커서가현재조건을만족하지않는지리턴 %ISOPEN 커서가현재 open 되어있는지를리턴 35 매개변수와커서 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 [ 커서명 ]; 36

19 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 [ 조건절 ]; END LOOP; CLOSE [ 커서명 ]; 37 에러처리방법 1. PL/SQL 블록내의 SQL문이정상적으로실행되지못할때에러가발생합니다. 2. 에러가발생하면 EXCEPTION절에의해처리할수있습니다. 3. 자주발생하는에러처리기는오라클사에서기본적으로제공하며, 자주발생하지않는에러는사용자가직접정의할수있습니다. Predefined (Internal) Exception UnDefined Exception User Defined Internal Exception Exception Trapping Function 38

20 미리정의된에러처리 DECLARE EXCEPTION WHEN [ 예외조건-1] THEN [ 처리문장-1]; WHEN [ 예외조건-2] THEN [ 처리문장-2];. WHEN OTHERS THEN [ 처리문장-n]; 예외조건 NO_DATA_FOUND NOT_LOGGED_ON TOO_MANY_ROWS VALUE_ERROR ZERO_DEVIDE INVALID_CURSOR INVALID_NUMBER DUP_VAL_ON_INDEX 설명조건을만족하는행이없는경우 DB에접속하지않은채실행하는경우 Sclar 변수에여러개의리턴행을저장하는경우지정된변수길이보다큰값을저장하는경우변수값을 0 값으로나누려는경우잘못된커서연산이발생하는경우수의문자열변환이잘못된경우이미저장된값을다시입력하는경우 39 미리정의되지않은에러처리 DECLARE 예외조건-1 EXCEPTION; PRAGMA EXCEPTION_INIT ( [ 예외조건-1], [ 에러코드 ]); EXCEPTION WHEN [ 예외조건-1] THEN [ 처리문장-1];. WHEN OTHERS THEN [ 처리문장-n]; DECLARE e_invalid EXCEPTION; PRAGMA EXCEPTION_INIT (e_invalid, -2292); EXCEPTION WHEN e_invalid THEN [ 처리문장-1];. WHEN OTHERS THEN [ 처리문장-n]; 40

21 사용자가정의하는에러처리 DECLARE 예외조건-1 EXCEPTION; DECLARE e_invalid EXCEPTION; RAISE 예외조건 -1; RAISE e_invalid; EXCEPTION WHEN [ 예외조건-1] THEN [ 처리문장-1];. WHEN OTHERS THEN [ 처리문장-n]; EXCEPTION WHEN e_invalid THEN [ 처리문장-1];. WHEN OTHERS THEN [ 처리문장-n]; 41 예외트래핑함수 DECLARE * 트래핑함수 예외조건 설 명 SQLCODE SQLERRM 에러코드를리턴해줍니다. 에러코드에대한에러메시지를리턴해줍니다. EXCEPTION * SQLCODE 종류 예외조건 설 명 WHEN OTHERS THEN v_code := SQLCODE; v_mwssage := SQLERRM; 에러없이정상적으로실행되었습니다. 사용자가정의한에러가발생하였습니다. 해당조건을만족하는행을찾지못했습니다. RAISE_APPLICATION_ERROR (-01403, 데이터가존재하지않음 ); 양수 * 에러출력함수 다른오라클서버에러 Raise_Application_Error ( error_code, message, {TRUE FALSE} ) 42

22 Dynamic-SQL 43 Dynamic-SQL SELECT empno, ename FROM emp Where deptno = :c 검색조건을입력하시오! Condition 10 SELECT empno, ename, deptno FROM emp Where deptno = 10 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

23 예제 DECLARE sql_stmt VARCHAR2(200); emp_id NUMBER(4) := 7934; salary NUMBER(7,2); dept_id NUMBER(2) := 50; dept_name location emp_rec VARCHAR2(14) := 'PERSONNEL'; VARCHAR2(13) := 'DALLAS'; emp%rowtype; EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)'; 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 Dynamic-SQL 의명시적커서 DECLARE 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); END LOOP; CLOSE emp_cv; 커서의선언커서의시작데이터의인출커서의종료 46

24 3 Package 와 Trigger 47 Package 48

25 패키지 1) 자주사용되는프로그램과로직을모듈화할수있습니다. 2) 응용프로그램을쉽게개발할수있습니다. 3) 프로그램의처리흐름을노출하지않아보안기능이좋습니다. 4) 프로그램에대한유지보수작업이편해집니다. 5) 같은이름의프로시저와함수를여러개생성할수있습니다. 49 Syntax 패키지명세 CREATE PACKAGE [package_name] IS 전역변수선언 Procedure [procedure_name1]; CREATE PACKAGE BODY [package_name] IS 지역변수선언 패키지몸체 Function [function_name1] IS. Procedure [procedure_name1] IS function1(); 50

26 패키지의실행 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); SQL> EXECUTE scott. comm_package.reset_comm(1500); 51 오버로딩 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

27 Forward Declaration. 어떤프로시저나함수를호출할때해당프로시저보다먼저정의되어야합니다. CREATE PACKAGE BODY comm_package IS Procedure reset_comm ( a OUT number, b IN number) IS Procedure chk_sal ( ) v_sal := reset_comm(1, 2); 53 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

28 패키지함수. 호출되는패키지내의테이블과변수에대한읽기 / 쓰기를제한할때사용되는함수입니다. PRAGMA RESTRICT_REFERENCES ( function_name, WNDS, [WNPS] [RNDS] [RNPS] ); 데이터베이스내의테이블을수정할수없습니다. 패키지내의변수값을수정할수없습니다. 데이터베이스내의테이블을 SELECT 할수없습니다. 패키지내의전역변수값을수정할수없습니다. 55 오라클제공패키지 종류예제 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 에접속된다른사용자의정보를참조할때사용합니다. UTL_FILE O/S 상의텍스트파일을 PL/SQL 내에서읽을때사용합니다. 56

29 Trigger 57 Data Integrity 사원관리 입력 수정 삭제 사원번호 2001 성 명 주종면 급 여 종료 Create trigger chk_emp Before update or delete or update on emp If Len(:new.no) <> 5 Then Dbms_output.put_line( 입력할수없음!! ); end if; 메시지사원번호는 5 자리입니다!!!! 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

30 트리거 오라클 DB Database 트리거 CHECK_SALARY 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; EMP EMPNO ENAME SALARY KING BLAKE CLERK SCOTT 3000 SQL> UPDATE emp SET sal= sal* 2; ORA Error 주말에는변경할수없습니다!! 시스템날자 : ( 토 ) 59 트리거구성요소 구성요소 가능한값 설 명 트리거유형트리거타이밍트리거이벤트트리거몸체 Statement Level Row Level BEFORE AFTER INSERT UPDATE DELETE PL/SQL 블록 트리거몸체의내용이몇번이나실행되는가? 사용자가트리거이벤트를유발시킬때데이터베이스트리거를언제실행하는가? 테이블상에어떤데이터조작연산이트리거를발생시키는가? 어떤이벤트가발생하면어떤작업이수행되는가? 트리거조건 WHEN [ 조건 ] 사용자의트리거이벤트중에조건을만족하는데이터만트리거합니다. 60

31 Statement Trigger. 기본테이블에 DML문이실행되면여러행에대한변경이발생하더라도트리거가한번만발생합니다. EMP 사원번호사원명직급급여부서번호 1 주종면차장 주영현과장 홍경옥과장 UPDATE EMP SET 급여 = 급여 * 1.1 ; 61 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,,,,,,, 62

32 Row-Level Trigger. 기본테이블에 DML문이실행될때여러행에대한변경이발생하면각행에대해트리거가발생합니다. EMP 사원번호사원명직급급여부서번호 1 주종면차장 주영현과장 홍경옥과장 UPDATE EMP SET 급여 = 급여 * 1.1 ; 63 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

33 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; 65 트리거의변경 1) 트리거의상태를비활성화또는활성화할수있습니다. ALTER TRIGGER trigger_name DISABLE ENABLE; 2) 해당테이블과관련된모든트리거의상태를비활성화또는활성화할수있습니다. ALTER TABLE table_name DISABLE ENABLE ALL TRIGGER; 3) 트리거를재컴파일할수있습니다. ALTER TRIGGER trigger_name COMPILE; 4) 트리거를삭제할수있습니다. DROP TRIGGER trigger_name; 66

34 프로시저와트리거의차이점 프로시저 CREATE PROCEDURE 문법사용 트리거 CREATE TRIGGER 문법사용 생성하면소소코드와실행코드 (P-CODE) 가생성됨 EXECUTE 명령어로실행 COMMIT, ROLLBACK 실행가능 생성하면소소코드와실행코드 (P-CODE) 가생성됨 생성후자동실행 COMMIT, ROLLBACK 실행안됨 67 관리규칙 -1. 변경중인테이블로부터데이터를참조하지마라. EMP Foreign-Key UPDATE EMP SET SAL = 1500 WHERE empno=30; 사원번호사원명직급 sal 부서번호 1 주종면차장 Error ora 주영현과장 홍경옥과장 CREATE OR REPLACE TRIGGER cascade_updates1 BEFORE UPDATE on emp FOR EACH ROW DECLARE v_sal emp.sal%type; SELECT max(sal) INTO v_sal FROM emp; 68

35 관리규칙 -2. 제약테이블의기본키, 외래키, 유일키컬럼에대해데이터를변경하지말것. UPDATE DEPT deptno = 30 WHERE deptno=10; SET EMP Foreign-Key 사원번호 사원명 직급 급여 부서번호 1 주종면 차장 주영현 과장 부서정보 부서번호 부서명 지역코드 10 전산과 1 20 경영지원과 1 3 홍경옥과장 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 트리거의응용범위 범위 설 명 보안 데이터베이스내테이블에대한변경을제한할수있습니다 감사 사용자들의데이터베이스사용에대한모든내용을감시할수있습니다. 데이터의무결성 테이블에원치않는데이터가저장되는것을방지할수있습니다. 테이블의복제 기본테이블에대한똑같은복사태이블을온라인으로생성, 관리할수있습니다. 연속적작업수행 기본테이블에데이터가입력되면또다른테이블에데이터를변경하는연속적인작업을할수있습니다. 70

36 4 PL/SQL 의관리 71 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.. DESCRIBE User_errors DBMS_OUTPUT 패키지 72

37 USER_OBJECTS. 데이터베이스내의모든객체를참조할수있습니다. 컬럼 설 명 OBJECT_NAME 객체의이름 OBJECT_ID OBJECT_TYPE CREATED 데이터베이스가객체를관리하는번호 객체의유형 (TABLE, PROCEDURE, FUNCTION, PACKAGE) 객체를마지막으로수정한날짜 TIMESTAMP 객체가마지막으로컴파일된날짜와시간 STATUS 객체의사용가능여부 (VALID, INVALID) 73 USER_SOURCE. PL/SQL 블록의 Source 내용을참조할수있습니다. 컬럼 설 명 NAME TYPE LINE 객체의이름 객체의유형 (TABLE, PROCEDURE, FUNCTION, PACKAGE) PL/SQL 블록의 SOURCE 코드의행번호 TEXT PL/SQL 블록의 SOURCE 코드 74

38 USER_ERRORS. PL/SQL 블록생성시발생한에러를참조할수있습니다. 컬럼 설 명 NAME TYPE SEQUENCE 객체의이름 객체의유형 (TABLE, PROCEDURE, FUNCTION, PACKAGE) 에러를표시하기위한일련번호 LINE 에러가발생한소스코드의행번호 POSITION 에러가발생한행의위치 TEXT 에러내용 75 USER_TRIGGER. 트리거의종류와상태정보를참조할수있습니다. 컬럼 설 명 TRIGGER_NAME 트리거의이름 TRIGGER_TYPE 트리거의유형 (BEFORE, AFTER, INSTEAD OF) TRIGGER_EVENT 트리거이벤트 (UPDATE, INSERT, DELETE) TABLE_LINE REFERENCING _NAMES WHEN_CLAUSE 트리거이벤트가되는테이블 :OLD, :NEW 에대해사용된이름 행레벨트리거에사용된 WHEN 절의내용 STATUS 트리거의사용가능여부 76

39 DESCRIBE 명령어. 트리거에사용된 IN/OUT 매개변수의상태를참조할수있습니다. SQL> DESCRIBE CHK_SAL FUNCTION CHK_SAL RETURN NUMBER Argument Name_Type In/Out Derfault V_NO NUMBER IN 77 USER_DEPENDENCIES CREATE PROCEDURE A IS (V_NO NUMBER;). V_NO := B( SALES ); CREATE PROCEDURE B IS (V_JOB VARCHAR2;). SELECT * INTO.. FROM EMP; CREATE TABLE EMP (EMPNO NUMBER, ENAME VARCHAR2(10), JOB VARCHAR2(15),.. );. 상호간에참조하는 PL/SQL 블록의의존관계를참조할수있습니다. SQL> SELECT * FROM USER_DEPENDENCIES WHERE referenced_name IN ( EMP ); NAME Type Referenced_Name Referenced_Name A PROCEDURE B PROCEDURE B PROCEDURE EMP TABLE 78

40 DEPTREE 와 IDEPTREE VIEW. 상호간에참조하는 PL/SQL 블록의의존관계를 TREE 형태로참조할수있습니다. SQL> EXECUTE deptree_fill ( TABLE, SCOTT, EMP ); SQL> SELECT nested_level, type, name FROM deptree ORDER BY seq# ; Nasted_Level Type Name PROCEDURE A 1 PROCEDURE B 79

Microsoft PowerPoint - PLSQL.ppt

Microsoft PowerPoint - PLSQL.ppt SQL 의장점과단점 PL/SQL 오라클 DB 지역정보부서코드지역 1 서울 2 부산 < 장점 > (1) 사용자가이해하기쉬운단어로구성 (2) 쉽게배울수있다. (3) 복잡한로직을간단하게작성할수있다. (4) ANSI에의해문법이표준화되어있다. SQL < 단점 > Consultant 주종면 jina6678@yahoo.co.kr 1 (1) 반복처리를할수없다.(Loop)