PL/SQL 은 무엇인가 ?

1. PL/SQL은 무엇인가? • PL/SQL(Procedural Language/SQL) • 오라클 환경에서 실행되는 절차적 DB 프로그래밍 언어 • SQL의 확장 • 프로그램 단위인 Block으로 Application Logic 작성 • 사용 이유 • 모든 오라클 프로그래밍의 기초가 됨 • 서버에서 수행되는 프로세스 작성 지원 • 복잡한 Logic을 캡슐화모듈화, 추상화 가능 • 트리거 작성복잡한 무결성 제약 가능 • 독립적 플랫폼 수준 제공 • PL/SQL 제공명령문 • 모든 SQL문 • 변수·상수 선언문, 대입문, 제어문, 호출문, NULL문 • PL/SQL로 작성할 수 있는 것 • SQL*Plus 스크립트 • 프로시저, 함수, 트리거 • 패키지

2. PL/SQL의 기본 블록 구조 [DECLARE 선언문] --변수, 상수, 커서, 프로시저, 함수 등 선언 BEGIN 실행문 [EXCEPTION 예외처리문] END; / --실행 • 기본 구조 : Block • Error 발생 시 • 실행을중단하고 EXCEPTION절로 이동, 정상종료 • EXCEPTION절이 없으면 Error 메시지 출력 후 강제 종료

3. PL/SQL의 블록 작성 및 실행 • Block 작성 순서 : p284, Reading • ‘SET SERVEROUTPUT ON’ • DBMS_OUTPUT 패키지의 PUT_LINE() 프로시저의 화면 출력을 가능하게 하는 SQL*Plus 명령어 • (실습) 예제 8-1 : ‘C9901’학생의 평균점수 출력 DECLARE v_avg NUMBER(3) := 0; v_student_ID VARCHAR2(5) := 'C9901'; BEGIN SELECT AVG(SCORE) INTO v_avg FROM SG_Scores WHERE Student_ID = v_student_ID GROUP BY Student_ID; DBMS_OUTPUT.PUT_LINE (v_student_ID || '의 평균점수는 [' || v_avg || ']점 입니다.'); END; / • Block의 실행방법 • *.sql로 스크립트 저장후 SQL*Plus에서 호출 실행 : “start *.sql” • Editor로 작성 후 copy하여 실행 : “run”, “/” • 그림 8-6 : p288, Developer에서실행

4. 선언절에변수, 상수 선언 • PL/SQL의 Data Type • 오라클 SQL의 모든 Data Type • PL/SQL의 추가 Data Type • 추가 Data Type • Boolean: True, False, Unknown • Binary_Interger : 부호가 있는 정수, -2147483647~ 2147483647 • Natural : 0~ 2147483647 • Positive : 1~ 2147483647 • %Type : 기존 Table의 한 컬럼의 Data Type • %RowType : 기존 Table의 모든 컬럼의 Data Type • 선언 Syntax • 변수명데이터타입 [:=초기값]; P_count NUMBER(3) :=0; • 상수명 CONSTANT 데이터타입 :=상수값; K CONSTANT POSITIVE :=100;

5. %Type • 변수명테이블명.컬럼명%TYPE; v_ageSG_Scores.Score%TYPE :=0; • %RowType • 변수명테이블명%TYPE; v_rec SG_Scores%ROWTYPE; … V.Grade :=‘A’; • (실습) 예제 8-4 : 예제 8-1의 확장 DECLARE v SG_Scores%ROWTYPE; v_cnt NUMBER(2) := 0; BEGIN v.Student_ID := 'C9901'; SELECT Count(*), AVG(SCORE) INTO v_cnt, v.Score FROM SG_Scores WHERE Student_ID = v.Student_ID GROUP BY Student_ID; IF v.score >= 90 THEN v.grade := 'A'; ELSIF v.Score >= 80 THEN v.grade := 'B'; ELSIF v.Score >= 70 THEN v.grade := 'C'; ELSIF v.Score >= 60 THEN v.grade := 'D'; ELSE v.grade := 'F'; END IF; DBMS_OUTPUT.PUT_LINE (v.student_ID || '의 과목수는 [' ||v_cnt || ']이고 평균점수는 [' || v.Score ||']점 [' || v.grade || '] 등급입니다.'); END; /

6. (Try) %ROWTYPE 이용하여 'C9901' 학생 레코드 출력 DECLARE v_std Student%ROWTYPE; BEGIN END; • 출력 결과 : C9901, 한영삼, 컴정

7. TYPE 테이블타입명 IS TABLE OF 데이터타입 INDEX BY BINARY_INTEGER; 테이블변수명 테이블타입명; TYPE 학번_tableIS TABLE OF Student.std_id%Type INDEX BY BINARY_INTEGER; array_학번 학번_table; … FOR … LOOP SELECT std_id INTO v_학번 FROM Student WHERE …; array_학번(i):= v_학번; i := i+1; END LOOP • PL/SQL 테이블과 사용자 정의 레코드 • 복수개의 변수를 선언하는 방법 • PL/SQL 테이블 • 한 type의 변수를 동적으로 복수개 저장하기위한 자료구조 • Syntax • 예

8. (Try) PL/SQL 테이블 이용하여 학생 정보 출력 DECLARE TYPE id_table IS TABLE OF student.student_id%TYPE INDEX BY BINARY_INTEGER; TYPE name_table IS TABLE OF student.name%TYPE INDEX BY BINARY_INTEGER; TYPE dept_table IS TABLE OF student.dept_id%TYPE INDEX BY BINARY_INTEGER; vid_tab id_table ; vname_tab name_table ; vdept_tab dept_table; i BINARY_INTEGER := 0; BEGIN FOR std_list IN(SELECT student_id, name, dept_id FROM student) LOOP i := i + 1; vid_tab(i) := std_list.student_id; vname_tab(i) := std_list.name; vdept_tab(i) := std_list.dept_id; END LOOP; FOR j IN 1..i LOOP DBMS_OUTPUT.PUT_LINE( vid_tab(j)||', '||vname_tab(j)||', '||vdept_tab(j)); END LOOP; END; • (Try) 위 프로그램을 Professor 테이블의 professor_id, name, position 출력하도록 수정

9. TYPE 레코드타입명 IS RECORD (필드명1 데이터타입 [NOTNULL] [초기값], … ); 레코드명 레코드타입명; • 사용자 정의 레코드 선언 • 타입과 크기가 다른 기억장소가 복수개 선언됨 • Syntax • 예 : 예제 8-6

10. (Try) 레코드 타입 이용하여 학생 정보 출력 DECLARE TYPE std_record IS RECORD (std_id student.student_id%type, name student.name%type, dept_id student.dept_id%type); vstd std_record; i BINARY_INTEGER := 0; BEGIN FOR std_list IN(SELECT student_id, name, dept_id FROM student) LOOP i := i + 1; vstd.std_id := std_list.student_id; vstd.name := std_list.name; vstd.dept_id := std_list.dept_id; DBMS_OUTPUT.PUT_LINE(vstd.std_id||', '||vstd.name||', '||vstd.dept_id); END LOOP; END; • (Try) 위 프로그램을 Professor 테이블의 professor_id, name, position 출력하도록 수정

11. 조건 판단문 IF 조건1 THEN 명령문1; … 명령문 N; [ELSEIF 조건2 THEN 명령문1; … 명령문 N; … ] [ELSE 명령문1; … 명령문 N; ] END IF; • Syntax • 예 : 예제 8-7

12. 반복문 • LOOP ~ END LOOP • 무한 루프 • 탈출 • EXIT; • EXIT [레이블명] WHEN 조건; • (실습) 예제 8-8 CREATE TABLE Temp1 (col1 NUMBER(3),Col2 DATE ); DECLARE Max_No CONSTANT POSITIVE := 10; I NATURAL := 0; BEGIN LOOP I := I + 1; EXIT WHEN I > Max_No; INSERT INTO Temp1 VALUES (I, SYSDATE); END LOOP; END;

13. FOR 변수 IN [REVERSE] 초기값..최종값 LOOP 명령문1; … 명령문 N; END LOOF; • WHILE LOOP • (실습) 예제 8-9 CREATE TABLE Temp2 (col1 NUMBER(3),Col2 DATE ); DECLARE Max_No CONSTANT POSITIVE := 10; I NATURAL := 0; BEGIN WHILE I < MAX_No LOOP I := I + 1; INSERT INTO TEMP2 VALUES (I, SYSDATE); END LOOP; END; • FOR LOOP • Syntax • (실습) 예제 8-10 CREATE TABLE Temp3 (col1 NUMBER(3),Col2 DATE ); DECLARE Max_No CONSTANT POSITIVE := 10; I NATURAL := 0; BEGIN FOR I IN 1..MAX_No LOOP INSERT INTO TEMP3 VALUES (I, SYSDATE); END LOOP; END;

14. 기타 명령문 • NULL문 • 어떤 처리도 하지 않음 • 대입문 • ':=' 사용 • GOTO문 • 예 : p303, 예제 8-12 • 주석 • '--' : 단일 줄 주석 • /* … */ : 여러 줄 주석

15. 실행절에 SQL문 사용 • PL/SQL Block에서의 SELECT문 • 비절차적 SQL과 다름 : 한 행만 검색 가능 • 검색 결과를 INTO절 변수에 저장 • 검색 결과가 0행(NO_DATA_FOUND)이거나 복수행(TOO_MANY_ROWS)이면 Error 발생 • ORDER BY 절 사용 못함 • 복수행 검색을 위해서는 커서(Cursor) 사용해야 함 • (실습) 예제 8-13 : 'C9901'의 과목수, 평균점수 계산 DECLARE v_avg NUMBER(3) := 0; -- 평균점수 저장 v_cnt NUMBER(2) := 0; /* 과목수 저장 */ v_student_ID VARCHAR2(5) := 'C9901'; /* 검색할 학번 */ BEGIN SELECT COUNT(Course_Id), AVG(Score) INTO v_cnt, v_avg FROM SG_Scores WHERE Student_ID = v_student_ID GROUP BY Student_ID; DBMS_OUTPUT.PUT_LINE (v_student_ID || '의 [' || v_cnt || ']과목에 대한 평균점수는 [' || v_avg || ']점 입니다.'); END; • (Try) 예제 8-13에서 'C9901'의 수강 과목번호와 각각의 점수 출력 : Error

16. declare cursor cur_1 is select 이름 from 학생; v_name char(10); begin dbms_output.enable; open cur_1; fetch cur_1 into v_name; loop dbms_output.put_line(v_name); fetch cur_1 into v_name; exit when cur_1%notfound; end loop; close cur_1; end; / • (Try) 커서 사용한 복수행 검색 DECLARE v_cid VARCHAR2(5); v_score NUMBER(3); v_student_ID VARCHAR2(7):='C9901'; cursor c_std is SELECT Course_Id, Score FROM SG_Scores WHERE Student_ID = v_student_ID; BEGIN END;

17. PL/SQL에서 화면에 출력하기 • DBMS_OUTPUT 패키지 이용 • SET SERVEROUTPUT ON : 세션 종료까지 유효 • DBMS_OUTPUT.ENABLE : 패키지 활성화 • DBMS_OUTPUT.DISABLE : 패키지 비활성화 • DBMS_OUTPUT.PUT_LINE( ) : 문자열 화면 출력 • (실습) 예제 8-15 : 활성화/비활성화 Test BEGIN SET SERVEROUTPUT ON DBMS_OUTPUT.DISABLE; DBMS_OUTPUT.PUT_LINE('1.HELLO WORLD.'); DBMS_OUTPUT.ENABLE; DBMS_OUTPUT.PUT_LINE('2.HELLO WORLD.'); END;