PL/SQL 2008 DB system and programming 보충자료 PL/SQL의실행절 BEGIN 절에서의몇가지규칙 - 실행문은여러라인에걸쳐사용할수있다. - 변수명의명명규칙은오라클의일반적인명명규칙과동일하다. PL/SQL 블록내에서 SQL 문을사용할때에는컬럼명과같은변수명은피해야한다. - SQL에서와마찬가지로날짜와문자는홑따옴표 ( ) 를사용하여인용하여야한다. - PL/SQL 블록은 / 기호를사용하여실행시켜야한다.( 아니면실행되지않는다 ) 주석 - 일반적인프로그래밍언어에서는코드의설명을위해주석을사용한다. PL/SQL도프로그래밍언어로서주석의기능을제공한다. - 주석이단한줄일때는 (( 기호를주석의첫머리에표기한다. - 주석이두줄이상때에는 /* 기호를사용하여시작하고주석의마지막에는 */ 기호사용하여끝낸다. PL/SQL 실행문에서의 SQL 함수사용 - DECODE 함수와 GROUP 함수는 PL/SQL 내에서사용할수없다. 그외의단일행함수, 데이터변환함수등은사용이가능하다. 블록의중첩과변수의범위 - PL/SQL 문은내부에또하나의 PL/SQL 문을둘수있다. 즉, 블록을중첩시켜사용할수있다는것이다. 여기에서문제되는것은변수의범위이다. 내부블록에있는변수는외부블록에서어떻게되며외부블록에있는변수는내부블록에서어떻게사용되는지가문제인것이다. DECLARE 변수 A 의범위 A NUMBER; BEGIN DECLARE B NUMBER; BEGIN 변수 B 의범위 END; END; - 변수는블록바깥의변수를참조할수있다. - 변수는자신이속한블록내부에중첩된 ( 즉, 블록내부 ) 의블록의변수를참조할수없다. 1
PL/SQL 내의 SELECT 문 2008 DB system and programming 보충자료 SELECT 컬럼명 INTO 변수명 FROM 테이블명 WHERE 조건 ; 2 v_deptno dept.deptno%type; 3 v_dname dept.dname%type; 4 BEGIN 5 SELECT deptno, dname 6 INTO v_deptno, v_dname 7 FROM dept 8 WHERE loc = BOSTON ; 9 DBMS_OUTPUT.PUTLINE(v_deptno 번부서의부서명은 v_dname 이다 ; 10 END; 11 / 40 번부서의부서명은 OPERATIONS 이다 PL/SQL을사용한데이터조작 데이터삽입 2 v_deptno dept.deptno%type := 90; 3 v_dname dept.dname%type := 총무부 ; 4 v_loc dept.loc%type := 부산 ; 5 BEGIN 6 INSERT INTO DEPT VALUES( vdeptno, v_dname, v_loc); 7 END; 8 / 데이터변경 2 v_comm emp.comm%type := 400; 3 BEGIN 4 UPDATE emp 5 SET comm. = NVL(comm.,0) + v_comm 6 WHERE deptno = 10; 7 END; 8 / %TYPE 속성을사용하여 emp 테이블의 comm. 컬럼의데이터타입과동일한변수를선언하고기본값으로 400을할당하였다. UPDATE문에서는부서번호가 10인모든사원의 comm 컬럼의값을 400씩더해주는변경을실행하였다. 데이터삭제 2 v_deptno dept.deptno%type := &Department_Number; 3 BEGIN 4 DELETE FROM emp 5 WHERE deptno = v_deptno; 6 END; 7 / department_number의값을입력하십시오 : 10 구 2: v_deptno dept.deptno%type := &Department_Number; 신 2: v_deptno dept.deptno%type := 10; 2
2008 DB system and programming 보충자료 바로위의예에서새로운데이터를삽입하고기존의데이터를변경, 삭제하는 DML문을사용하여데이터베이스를변경하였다. 트랜잭션과관련한 COMMIT 문과 ROLLBACK문역시 PL/SQL 문에서사용할수있다. IF 조건문 2 v_ename emp.ename%type; 3 v_sal emp.sal%type; 4 v_job emp.job%type; 5 v_deptno emp.deptno%type; 6 v_newcomm emp.comm.%type; 7 BEGIN 8 SELECT ename, sal 9 INTO v_ename, v_sal 10 FROM emp 11 WHERE empno = 7788; 12 IF v_ename = SCOTT THEN 13 v_job := MANAGER ; 14 v_deptno := 30; 15 v_newcomm := v_sal * 0.3; 16 END IF; 17 END; 18 / IF THEN ELSIF THEN 2 v_deptno emp.deptno%type; 3 v_empno emp.empno%type; 4 BEGIN 5 SELECT deptno, empno INTO v_deptno, v_empno 6 FROM emp WHERE empno = &v_empno; 7 IF v_deptno = 10 then 8 DBMS_OUTPUT.PUT_LINE(v_empno 번사원의부서는 ACCOUNTING입니다 ); 9 ELSIF v_deptno = 20 then 10 DBMS_OUTPUT.PUT_LINE(v_empno 번사원의부서는 RESEARCH입니다 ); 11 ELSIF v_deptno = 30 then 12 DBMS_OUTPUT.PUT_LINE(v_empno 번사원의부서는 SALES입니다 ); 13 ELSIF v_deptno = 40 then 14 DBMS_OUTPUT.PUT_LINE(v_empno 번사원의부서는 OPERATIONS입니다 ); 15 ELSE 16 DBMS_OUTPUT.PUT_LINE(v_empno 번사원은부서가없네요 ); 17 END IF; Basic LOOP 18 END; 19 / v_empno의값을입력하십시오 : 7788 구 8: WHERE empnno = &v_empno; 신 8: WHERE empno = 7788; 7788번사원의부서는 RESEARCH입니다. 3
FOR LOOP 2008 DB system and programming 보충자료 2 v_counter 2 v_name NUMBER dept.dname%type := 1; := NO NAME ; 3 BEGIN 3 BEGIN 4 LOOP 4 FOR I IN (REVERSE) 1..9 LOOP 5 INSERT 5 INTO INSERT dept(deptno) INTO dept(deptno, dname) 6 VALUES 6 VALUES (v_counter); (I, v_dname); 7 v_counter 7 END LOOP; := v_counter+1; 8 EXIT 8 END; WHEN v_counter >9; 9 END 9 / LOOP; 10 END; 11 / PL/SQL처리가정상적으로완료되었습니다. WHILE LOOP 2 v_name dept.dname%type := NO NAME ; 3 v_counter NUMBER := 1; 4 BEGIN 5 WHILE v_counter < 10 LOOP 6 INSERT INTO dept(deptno, dname) 7 VALUES (I, v_dname); 8 v_counter = v_counter + 1; 9 END LOOP; 10 END; 11 / PL/SQL 처리가정상적으로완료되었습니다. PL/SQL 레코드테이블을생성할때와비슷한구조를취하고있다. 테이블의각컬럼을사용자가원하는대로생성할수있듯이 PL/SQL의레코드타입도비슷하다고여기면된다. 복합데이터타입의일종으로하나의변수에여러개의데이터를담는것이가능하다. 2 TYPE test_record_type IS RECORD 3 (id NUMBER := 5232, 4 name VARCHAR(10) := james, 5 age NUMBER := 31, 6 phone NUMBER := 027885789); 7 8 test_record test_record_type; 9 BEGIN 10 INSERT INTO emp (empno, ename) 11 VALUES (test_record.id, test_record.name); 12 END; 13 / %ROWTYPE 속성특정테이블의각컬럼의데이터타입과사이즈를그대로가져와서사용하는것이다. %ROWTYPE 속성은레코드데이터타입을사용자가정의해주지않아도되게끔한다. 명시된테이블의각컬럼의데이터타입, 사이 4
2008 DB system and programming 보충자료 즈까지모두그래도사용할수있게한다. 이러한 %ROWTYPE 속성은다음과같은장점이있다. - 테이블의컬럼타입과컬럼수등을모를때변수를동적으로선언하여유용하게사용할수있다. - 테이블의컬럼타입등이변하여도변수는동일하게사용된다 - SELECT 문을사용하여변수에값을할당할때유용하다. 2 dept_record dept%rowtype; 3 BEGIN 4 SELECT * 5 INTO dept_record 6 FROM dept 7 WHERE deptno = 10; 8 INSERT INTO dept2 /* 테이블생성필요 */ 9 VALUES (dept_record.deptno, dept_record.dname, dept_record.loc); 10 END; 11 / 암시적커서 SQL문이실행될때마다오라클서버는명령이분석되고실행될메모리영역을여는데, 이메모리영역을커서라고한다. 커서에는암시적커서와명시적커서두종류가있다. 암시적커서는모든 DML문과 SELECT 문에대해생성되고사용되는오라클의메모리영역이다. 암시적커서는오라클에의해자동생성되고또한자동적으로관리되고닫힌다. 이전의예제에서본 DML문들을사용할때암시적커서가생성된다. 암시적커서는사용자의눈에보이지않는다. 관련 SQL문을실행시키면오라클서버가자동적으로생성하여그영역에서 SQL문을분석하고실행시킨다. 또한 SQL문의실행이끝나면자동적으로커서를닫는다. 눈에보이지않는암시적커서는커서의속성을사용하여커서에접근할수있다. 암시적커서의속성은다음의네가지가있다. - SQL%ROWCOUNT : 수행된 SQL문의결과로서나온행의수를반환한다. - SQL%FOUND : 수행되고있는 SQL 문에서반환될행이남아있는지의여부를 Boolean 값으로나타낸다. 반환될행이남아있으며 TRUE 값을반환한다. - SQL%NOTFOUND : SQL%FOUND와반대로반환될행이없으면 TRUE값을반환한다. - SQL%ISOPEN : 커서가열려있는지의여부를 Boolean값으로나타낸다. 암시적커서에서는 SQl문이수행되고난뒤즉시닫히기때문에항상 FALSE값을반환한다. 명시적커서명시적커서는사용자에의해선언되어사용되고닫히는형태로서 SQL CURSOR영역을열고닫는것을사용자가임의대로설정하여다룰수있다. 명시적커서는 DECLARE절에서선언하고 BEGIN절에서 OPEN, FETCH, CLOSE 절을사용하여구성된다. - DECLARE : DECLARE 절에서커서의이름을만들고커서내에서수행될 SELECCT문을정의하여커서를선언한다. - OPEN : 커서내의 SELECT 문을수행하여선택된행들을 FETCH단계에서사용할수있게한다. 선택된행들이모인것을 active set이라한다. - FETCH : OPEN 문에서선택된행들을하나씩뽑아내어변수에값을할당한다. 행들을하나씩추출할때마다 active set의 pointer를다음행으로이동시킨다. Active set 에있는행들을모두추출하면 CLOSE 절로넘어간다. - CLOSE : 커서를닫는다. 커서가닫히면서 active set이닫히고새로운 active set을생성하게된다. 5
커서 FETCH 2008 DB system and programming 보충자료 2 v_deptno dept.deptno%type; 3 v_dname dept.dname%type; 4 v_loc dept.loc%type; 5 6 CURSOR dept_cursor IS 7 SELECT * 8 FROM dept; 9 BEGIN 10 OPEN dept_cursor; 11 12 FOR I IN 1..3 LOOP 13 FETCH dept_cursor INTO v_deptno, v_dname, v_loc; 14 DBMS_OUTPUT.PUT_LINE(v_deptno v_dname v_loc); 15 END LOOP; 16 17 CLOSE dept_cursor; 18 END; 19 / 커서에서의레코드타입사용 2 CURSOR dept_cursor IS 3 SELECT * 4 FROM dept; 5 dept_record dept_cursor%rowtype; 6 BEGIN 7 OPEN dept_cursor; 8 FOR I IN 1..3 LOOP 9 FETCH dept_cursor INTO dept_record; 10 END LOOP; 11 CLOSE dept_cursor; 12 END; 13 / 예외처리 3 v_empno NUMBER(2); 4 v_ename VARCHAR2(15); 5 BEGIN 6 SELECT empno, ename 7 INTO v_empno, v_ename 8 FROM emp; 9 EXCEPTION 10 WHEN TOO_MANY_ROWS THEN 11 DBMS_OUTPUT.PUT_LINE( 결과행이너무많습니다 ); 12 WHEN OTHERS THEN 13 DBMS_OUTPUT.PUT_LINE( 아무런에러도없습니다 ); 14 END; 15 / 6
2008 DB system and programming 보충자료 PL/SQL 레코드테이블을생성할때와비슷한구조를취하고있다. 테이블의각컬럼을사용자가원하는대로생성할수있듯이 PL/SQL의레코드타입도비슷하다고여기면된다. 복합데이터타입의일종으로하나의변수에여러개의데이터를담는것이가능하다. 14 TYPE test_record_type IS RECORD 15 (id NUMBER := 5232, 16 name VARCHAR(10) := james, 17 age NUMBER := 31, 18 phone NUMBER := 027885789); 19 20 test_record test_record_type; 21 BEGIN 22 INSERT INTO emp (empno, ename) 23 VALUES (test_record.id, test_record.name); 24 END; 25 / %ROWTYPE 속성 %TYPE 속성에대해설명하였다. %TYPE 속성은특정테이블내의특정컬럼의데이터타입과사이즈를그대로가져와사용하는것이다. 이것과비슷하게 %ROWTYPE 속성이라는것이존재한다. 명시된테이블의각컬럼의데이터타입을그대로사용하는속성이다. - 변수명테이블명 %ROWTYPE %ROWTYPE속성은레코드데이터타입을사용자가정해주지않아도되도록한다. 명시된테이블의각컬럼의데이터타입, 사이즈까지모두그대로사용할수있게한다. %TYPE의속성을보면변수명에사용될데이터타입을설정하지않았다. 명시된테이블의컬럼데이터타입과사이즈를그대로사용할수있었다. %ROWTYPE 속성도동일한원리이다. 2 dept_record dept%rowtype; 3 BEGIN 4 2 TYPE dept_record_type IS RECORD; 3 (deptno NUMBER(2), 4 dname VARCHAR(14), 5 loc VARCHAR(13) ); 6 dept_record dept_record_type; 7 BEGIN. Dept_record라는이름의변수가 %ROWTYPE을사용하여선언되었다. 이것은 dept테이블의각컬럼타입을그대로따르겠다는의미이다. Dept 테이블의필드타입이변하더라고이변수는그대로사용될수있다. 그림으로보면다음과같다. deptno NUMBER(2) dname VARCHAR(14) loc VARCHAR(13) 이러한 %ROWTYPE 속성은다음과같은장점이있다. - 테이블의컬럼타입과컬럼수등을모를때변수를동적으로선언하여유용하게사용할수있다. - 테이블의컬럼타입등이변하여도변수는동일하게사용된다 - SELECT 문을사용하여변수에값을할당할때유용하다. 7
아래의예제를실습하기전에다음의 SQL문장을이용해 dept2 테이블을생성한다. create table dept2 as select * from dept where deptno=''; 2008 DB system and programming 보충자료 2 dept_record dept%rowtype; 3 BEGIN 4 SELECT * 5 INTO dept_record 6 FROM dept 7 WHERE deptno = 10; 8 INSERT INTO dept2 /* 테이블생성필요 */ 9 VALUES (dept_record.deptno, dept_record.dname, dept_record.loc); 10 END; 11 / PL/SQL 테이블의개념과구조 PL/SQL 레코드타입에서는 %ROWTYPE 속성을사용하여테이블의한행을데이터값으로취하는형식이일반적으로사용된다. PL/SQL 테이블타입을사용하면행이아니라테이블의한열전체를데이터값으로취할수있다. 또한여기에레코드타입을같이사용하면한테이블전체를데이터값으로사용할수있다. PL/SQL 테이블타입은 BINARY_INTEGER 형의 Primary Key와스칼라데이터타입혹은레코드데이터타입의컬럼, 이렇게두가지로이루어진다. TYPE 타입명 IS TABLE OF 컬럼타입 INDEX BY BINARY_INTEGER; 변수명타입명 ; 타입명은사용자가정의하고자하는테이블타입의이름을말한다. 컬럼타입은테이블타입에서컬럼의데이터타입을말한다. 이는스칼라데이터타입을사용하면된다. INDEX BY BINARY_INTEGER는테이블타입에서각데이터의키값을이루게된다. TYPE test_table_type IS TABLE OF NUMBER(5) INDEX BY BINARY_INTEGER; Test_table test_table_type; test_table_type이라는이름으로테이블타입을정의하였다. Test_table_type의컬럼타입은스칼라데이터타입중 NUMBER형으로정의되었다. 이렇게선언된타입형으로 test_table 이라는변수가선언된것이다. Test_table 변수의구조를그림으로보면다음과같다 Primary key column 1 NULL 2 NULL 3 NULL 4 NULL BINARY_INTEGER NUMBER 앞의 1,2,3,4 로되어있는부분은 test_table의키값이다. 이것은 INDEX BY BINARY_INTEGER라는절로 8
2008 DB system and programming 보충자료 선언된것이다. 이키값은사용자가선언하기만하면된다. 그값은자동적으로증가하기때문에사용자는신경쓰지않아도된다. 사용자는키값을통해데이터에접근하고데이터를사용할수있다. 뒤의 NULL.. 로되어있는부분은 NUMBER(5) 로선언된데이터가들어갈부분이다. 선언할때값이초기화되지않았으므로 NULL로초기화된다. 2 TYPE ename_table_type IS TABLE OF emp.ename%type 3 INDEX BY BINARY_INTEGER; 4 TYPE hiredate_table_type IS TABLE OF DATE 5 INDEX BY BINARY_INTEGER; 6 ename_table ename_table_type; 7 hiredate_table hiredate_table_type; 8 BEGIN 9 ename_table(1) := 'CAMERON'; 10 hiredate_table(8) := SYSDATE + 7; 11 DBMS_OUTPUT.PUT_LINE (ename_table(1)); 12 DBMS_OUTPUT.PUT_LINE (hiredate_table(8)); 13 END; 14 / PL/SQL 테이블과레코드타입 PL/SQL의레코드타입과테이블타입을같이사용하여두타입의기능을모두사용할수있다. 테이블타입 을정의할때컬럼의데이터타입을 %ROWTYPE속성을사용하여정의하면된다. TYPE 타입명 IS TABLE OF 테이블명 %ROWTYPE INDEX BY BINARY_INTEGER; 변수명 타입명 ; Primary key column dname loc 1 NULL NULL NULL 2 NULL NULL NULL 3 NULL NULL NULL 4 NULL NULL NULL BINARY_INTEGER NUMBER VARCHAR2 VARCHAR2 PL/SQL 테이블메쏘드오라클에서는 PL/SQL 테이블타입에서사용할수있는다음과같은메소드를제공한다. 이를사용하면테이블타입에서데이터를더욱더쉽게다룰수있다. EXISTS(n): 테이블타입의변수에서 n번째키값에데이터가존재하면참값을반환한다. COUNT : 테이블타입의변수가현재포함하고있는값의개수를반환한다. DELETE : 테이블타입의변수가포함하고있는모든데이터를삭제한다. - DELETE(n) : 테이블타입의변수에서 n번째키값에있는데이터를삭제한다. - DELETE(m,n) 테이블타입의변수에서 m 부터 n번키값에있는데이터를삭제한다. FIRST, LAST : 테이블타입의변수가포함하고있는데이터의첫번째키값과마지막키값을반환한다. 9
2008 DB system and programming 보충자료 2 TYPE dept_table_type IS TABLE OF dept%rowtype 3 INDEX BY BINARY_INTEGER; 4 dept_table dept_table_type; 5 BEGIN 6 dept_table(2).dname := ' 영업부 '; 7 dept_table(3).dname := ' 총무부 '; 8 dept_table(2).deptno := 11; 9 dept_table(3).deptno := 22; 10 dept_table(2).loc := ' 부산 '; 11 dept_table(3).loc := ' 독도 '; 12 13 DBMS_OUTPUT.PUT_LINE (dept_table(2).dname); 14 DBMS_OUTPUT.PUT_LINE (dept_table(3).dname); 15 DBMS_OUTPUT.PUT_LINE (dept_table(2).deptno); 16 DBMS_OUTPUT.PUT_LINE (dept_table(3).deptno); 17 DBMS_OUTPUT.PUT_LINE (dept_table(2).loc); 18 DBMS_OUTPUT.PUT_LINE (dept_table(3).loc); 19 20 dept_table.delete(2); 21 IF dept_table.exists(2) THEN 22 DBMS_OUTPUT.PUT_LINE (' 데이터가존재합니다 '); 23 ELSE 24 DBMS_OUTPUT.PUT_LINE (' 데이터가존재하지않습니다.'); 25 END IF; 26 END; 27 / 영업부총무부 11 22 부산독도데이터가존재하지않습니다. PL/SQL 처리가정상적으로완료되었습니다. 10