PL/SQL은 무엇인가?
- PL/SQL(Procedural Language/SQL)의 약어
- 오라클 DB 환경에서 실행되는 절차적인 데이터베이스 프로그래밍 언어
- 표준 SQL과 3세대 언어의 강력한 일부 기능을 포함한 SQL의 확장 언어
- PL/SQL에서는 프로그램 단위를 블록(block)이라 부르며, 애플리케이션 로직들을 작성
그림 8.1 SQL문과 PL/SQL 블록의 실행 비교
■ 오라클 환경에서 PL/SQL을 학습하는 이유
▪ 오라클 개발 도구를 수행하는 모든 프로그래밍의 기초
▪ 클라이언트가 아닌 서버 상에서 프로세스를 수행하는데 PL/SQL을 사용함
▪ PL/SQL을 사용하면 업무 규칙이나 복잡한 로직을 캡슐화(Encapsulation)할 수 있어, 모듈화(Modularity)와 추상화(Abstraction)가 가능
▪ 데이터베이스 트리거를 통하여 데이터베이스 무결성을 제약하는 복잡한 규칙의 코딩과 변경 내역, 데이터를 복사
▪ PL/SQL은 독립적인 플랫폼 수준을 제공
■ PL/SQL에서 제공하는 명령문은
▪ 모든 SQL문
▪ 변수 및 상수 등의 선언문
▪ 대입문
▪ 조건 판단문
▪ 제어 흐름문
▪ 반복 처리문
■ PL/SQL로 작성할 수 있는 것은?
▪ SQL*Plus 스크립트(scripts)
▪ 프로시저(Procedure), 함수(Function) 서브프로그램
▪ 패키지(Package)
▪ 데이터베이스 트리거 (Database Trigger)
▪ 애플리케이션 로직 (Application Logic)
8.2 PL/SQL의 기본 블록 구조
- PL/SQL의 프로그램 단위는 블록(Block)
DECLARE 선택 변수와 상수, 프로시저와 함수 서브프로그램,
커서 등을 선언
선언문
.....
-----------------------------------------------
BEGIN 필수 처리할 명령문들을 절차적으로 기술
▪ 모든 SQL문
실행문 ▪ 대입문, 반복 처리문, 조건 판단문, 제어 흐름문
..... ▪ 커서 처리문
-----------------------------------------------
EXCEPTION 선택 오류 처리에 관한 예외처리 명령문을 기술
예외처리문
.....
-----------------------------------------------
END; 필수
/
그림 8.2 PL/SQL의 기본 블록 구조
8.3 PL/SQL의 블록 작성 및 실행
8.3.1 PL/SQL 블록의 작성
▪ ‘무엇을 어떻게 처리할 것인가?’를 분석
▪ DECLARE를 기술하고, 블록에서 필요한 변수나 상수, 서브 프로그램, 커서 등을 문법에 맞추어 한 줄에 하나씩 기술
▪ BEGIN을 기술하고, 절차적인 실행 순서에 의하여 명령문을 기술
▪ EXCEPTION을 기술하고, 실행절에 기술된 명령문이 실행될 때 발생될 수 있는 오류에 대한 예외처리문을 기술
▪ 마지막으로 END;을 기술
▪ PL/SQL 블록에서 한 문장이 종료할 때마다 세미콜론(;)을 쓴다.
【예제 】
SG_Scores 테이블에서 학번이 ‘C9901'인 학생의 평균점수를 계산하여 출력하는 익명의 블록을 작성하라.
SQL> SET SERVEROUTPUT ON
SQL> get ex8_01.sql
1 DECLARE
2 v_avg NUMBER(3) := 0;
3 v_student_id VARCHAR2(5) := 'C9901';
4 BEGIN
5 SELECT AVG(Score)
6 INTO v_avg
7 FROM SG_Scores
8 WHERE student_id = v_student_id
9 GROUP BY student_id;
10 DBMS_OUTPUT.PUT_LINE (v_student_id || '의 평균점수는 ['
11 || v_avg || ']점 입니다.');
12* END;
SQL> /
C9901의 평균점수는 [80]점 입니다.
PL/SQL 처리가 정상적으로 완료되었습니다.
8.3.2 PL/SQL 블록의 실행
☞ SQL*Plus를 이용하는 방법
▪ 편집기(editor)로 PL/SQL 블록을 입력한 후, *.sql 파일로 저장
▪ @ 또는 start 명령어로 *.sql 파일을 호출하여 실행
Netterm이나 텔넷(Telnet)을 이용하여 SQL*Plus상에서 블록을 실행
☞ Oracle Developer의 Procedure Builder를 이용하는 방법
▪ Program PL/SQL Interpreter 메뉴를 선택
▪ PL/SQL Interpreter 화면에 작성한 블록을 입력
▪ END;이 입력되면 자동 실행
8.4 선언절에 변수, 상수 선언
상수는 프로그램이 시작하여 종료될 때까지 일정한 값을 가지는 수,
어떤 값을 대입 불가
12, 87과 같은 숫자형 리터럴과 ‘Computer', '홍길동’, ‘T' 와 같은 문자형 리터럴
변수는 프로그램이 시작하여 종료될 때까지 변하는 수,
어떤 값을 대입 가능. i, Course_id 등과 같이 문자로 지정
8.4.1 PL/SQL의 데이터타입
• SQL에서 사용 가능한 모든 데이터타입과 추가적인 데이터타입
데이터타입 설 명
-------------------------------------------------------------------------
BOOLEAN 논리적인 데이터(True, False, Unknown)를 저장할 때 사용
BINARY_INTEGER 부호가 있는 정수를 저장할 때 사용
데이터 범위는 -2147483647부터 2147483647까지
ꋯ NATURAL 정수 중에서 0부터 2147483647까지 저장할 때 사용
ꋯ POSITIVE 정수 중에서 1부터 2147483647까지 저장할 때 사용
%TYPE 기존 테이블의 한 칼럼에 정의된 데이터타입을 참조
%ROWTYPE 기존 테이블의 모든 칼럼에 정의된 데이터타입을 참조
8.4.2 변수와 상수 선언
변수명 데이터타입(크기) [ := 초기값 ] ;
상수명 CONSTAT 데이터타입(크기) := 상수값 ;
■ 변수명이나 상수명을 정의할 때는
∙ SQL 객체명과 동일한 규칙으로 정의하고
∙ 상수명은 데이터타입과 일치하는 상수 값을 반드시 기술하고
∙ 한 줄에 한 개의 변수나 상수를 정의하며
∙ 초기 값이 Null이나 0인 경우 생략할 수 있다.
【예제】변수와 상수의 선언 예
P_count NUMBER(3) := 0;
Zip_Code VARCHAR2(9);
I BINARY_INTEGER := 0;
k CONSTANT POSITIVE := 100;
8.4.3 테이블을 참조하여 변수 선언
- 기존 테이블에 정의된 데이터타입과 크기를 참조하여 변수를 선언
■ %TYPE
형식=> 변수명 테이블명.칼럼명%TYPE ;
- 기존 테이블의 칼럼에 선언된 데이터타입과 크기를 참조하여 변수를 선언
- 테이블 칼럼의 제약조건은 적용되지 않음.
v_student_id SG_Scores.student_id%TYPE := 'C9901';
v_avg SG_Scores.Score%TYPE := 0;
v_grade SG_Scores.Grade%TYPE;
■ %ROWTYPE
형식=> 변수명 테이블명%ROWTYPE ;
- 기존 테이블의 각 칼럼에 정의된 데이터타입과 크기를 참조
- 칼럼 수와 동일한 복수 개의 변수가 선언,
- 각 기억 장소의 구분은 “변수명.칼럼명”으로 구분
【예제】SG_Scores 테이블을 참조하여 학번과 평균 점수를 저장하기 위한 변수를 %ROWTYPE을 이용하여 선언하라.
SQL> get ex8_04.sql
1 DECLARE
2 v SG_Scores%ROWTYPE;
3 v_cnt NUMBER(2) := 0;
4 BEGIN
5 v.student_id := 'C9901';
6 SELECT Count(*), AVG(SCORE)
7 INTO v_cnt, v.Score
8 FROM SG_Scores
9 WHERE student_id = v.student_id
10 GROUP BY student_id;
11 IF v.score >= 90
12 THEN v.grade := 'A';
13 ELSIF v.Score >= 80
14 THEN v.grade := 'B';
15 ELSIF v.Score >= 70
16 THEN v.grade := 'C';
17 ELSIF v.Score >= 60
18 THEN v.grade := 'D';
19 ELSE v.grade := 'F';
20 END IF;
21 DBMS_OUTPUT.PUT_LINE (v.student_id || '의 과목수는 [' ||
22 v_cnt || ']이고 평균점수는 [' || v.Score ||
23 ']점 [' || v.grade || '] 등급입니다.');
24* END;
8.4.4 PL/SQL 테이블과 사용자 정의 레코드 선언
■ PL/SQL 테이블 선언
데이터타입과 크기가 동일한 기억장소가 동적으로 복수 개 선언
형식=>
TYPE 테이블타입명 IS TABLE OF
데이터타입(크기)
INDEX BY BINARY_INTEGER;
테이블명 테이블타입명;
- 각 기억장소 구분은 “테이블명(첨자명)”,
- 첨자명은 정수로 된 첨자명이거나, 양의 정수
- 테이블의 크기는 사용할 때 동적으로 결정
【예제】PL/SQL 테이블 선언 및 사용 예
DECLARE
TYPE Table_type IS TABLE OF
VARCHAR2(20)
INDEX BY BINARY_INTEGER;
v_Dept_Name Table_type;
BEGIN
...
v_Dept_Name(2) := '컴퓨터정보기술계열‘;
...
END;
■ 사용자 정의 레코드 선언
- 데이터타입과 크기가 다른 기억장소가 복수 개 선언
형식=> TYPE 레코드타입명 IS RECORD
( 필드명1 데이터타입(크기) [NOT NULL] [초기값],
. . .
필드명n 데이터타입(크기) [NOT NULL] [초기값] ) ;
레코드명 레코드타입명;
- 각 기억 장소의 구분은 “레코드명.필드명“으로 구분
【예제 】사용자 정의 레코드 선언 및 사용 예
DECLARE
TYPE Record_Type IS RECORD
Dept_ID Department.Dept_ID%type,
Dept_Name Department.Dept_Name%type;
Dept_Rec Record_Type;
...
BEGIN
...
Dept_Rec.Dept_ID := '컴정‘;
Dept_Rec.Dept_Name := '컴퓨터정보기술계열‘;
...
END;
8.5 조건 판단문
문법>
IF 조건1
THEN 명령문1; . . . ; 명령문N;
[ ELSIF 조건2
[ THEN 명령문1; . . . ; 명령문N; ]
[ ELSIF 조건3
. . .
[ ELSE 명령문1; . . . ; 명령문N; ]
END IF;
- 조건의 결과가 참이면 THEN 이하의 명령문을 처리하고,
거짓이면 ELSIF절을 실행
- 조건이 모두 거짓이면 ELSE절
- ELSIF문은 16개까지 반복하여 사용
IF문은
① IF 조건 THEN 명령문_1
END IF;
② IF 조건 THEN 명령문_1
ELSE 명령문_2
END IF;
③ IF 조건_1
THEN 명령문_1
ELSIF 조건_2
THEN 명령문_2
ELSIF 조건_3 THEN 명령문_3
...
ELSE 명령문_n
END IF;
【예제 】 IF ~ THEN ~ ELSIF문 예
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;
8.6 반복문
8.6.1 LOOP문
문법> LOOP
명령문1;
. . . ;
명령문N;
END LOOP;
- EXIT문은 무한 루프로부터 탈출하는 명령문
■ EXIT문
문법> ① EXIT;
② EXIT [레이블명] WHEN 조건;
∙ EXIT문은 무한 루프로부터 무조건 탈출
∙ EXIT WHEN 조건 명령문은 조건이 참(True)일 때 탈출
【예제 】1에서 10까지 반복하여 TEMP 테이블에 저장하라. (LOOP문 사용)
SQL> CREATE TABLE Temp (
2 Col1 NUMBER(3),
3 Col2 DATE);
SQL> DECLARE
2 Max_No CONSTANT POSITIVE := 10;
3 I NATURAL := 0;
4 BEGIN
5 LOOP
6 I := I + 1;
7 EXIT WHEN I > Max_No;
8 INSERT INTO TEMP
9 VALUES (I, SYSDATE);
10 END LOOP;
11* END;
SQL> SELECT * FROM Temp;
8.6.2 WHILE ~ LOOP문
조건이 참(True)일 때, LOOP ~ END LOOP내의 명령문들을 반복 처리
문법> WHILE 조건 LOOP
명령문1;
. . . ;
명령문N;
END LOOP;
【예제】1에서 10까지 반복하여 TEMP 테이블에 저장하라. (WHILE문 사용)
SQL> DECLARE
2 Max_No CONSTANT POSITIVE := 10;
3 I NATURAL := 0;
4 BEGIN
5 WHILE I < MAX_NO LOOP
6 I := I + 1;
7 INSERT INTO TEMP
8 VALUES (I, SYSDATE);
9 END LOOP;
10* END;
8.6.3 FOR ~ LOOP문
- 변수가 초기 값부터 1씩 증가하여 최종 값이 될 때까지 반복 처리
문법> FOR 변수 IN [REVERSE] 초기값..최종값 LOOP
명령문1;
. . . ;
명령문N;
END LOOP;
【예제】1에서 10까지 반복하여 TEMP 테이블에 저장하라. (FOR문 사용)
SQL> DECLARE
2 Max_No CONSTANT POSITIVE := 10;
3 I NATURAL := 0;
4 BEGIN
5 FOR I IN 1..MAX_NO LOOP
6 INSERT INTO TEMP
7 VALUES (I, SYSDATE);
8 END LOOP;
9* END;
8.7 NULL문
- 어떤 처리도 하지 않음. 문법 형식을 갖추기 위해 사용
문법>NULL;
【예제 】변수 a가 0보다 크지 않으면 i 값을 1 증가시켜라.
IF a > 0 THEN NULL;
ELSE i = i + 1;
END IF;
8.8 대입문
문법> expr1 := expr2
우변(칼럼명, 변수, 리터럴, 수식, 함수)의 값을 좌변(칼럼명, 변수)에 저장
8.9 제어문
GOTO문은 무조건 레이블명으로 이동
문법> GOTO 레이블명
【예제 】GOTO문을 이용한 제어문 사용 예
....
GOTO label_1;
....
<>
...
8.10 주석 달기
☞ -- 문자열
- 단일 줄에 주석
☞ /* 문자열 */
- 여러 줄로 주석
8.11 실행절에 SQL문 사용
8.11.1 PL/SQL에서의 SELECT문
- 검색된 행의 수가 한 행이 되어야 하고, 검색한 값을 INTO절의 변수에 저장
- 검색된 행의 수가 0행(NO_DATA_FOUND)이거나 복수 행(TOO_MANY_ROWS)일 때 오류가 발생
- ORDER BY절은 사용하지 않음
문법>
SELECT 칼럼명1, 칼럼명2, 리터럴, 함수, 수식, ...
INTO 변수명, . . .
FROM 테이블명1, 테이블명2, 뷰명1, ...
WHERE 검색조건1 ...
GROUP BY 칼럼명1, 칼럼명2, ...
HAVING 검색조건2 ;
■ SQL과 PL/SQL에서 SELECT문의 차이점
∙ SELECT 구문이 다름
∙ 결과 행
∙ 결과를 출력
【예제 】SG_Scores 테이블을 참조하여 ‘C9901'학번의 과목수, 평균 점수를 계산하여 출력하라.
SQL> DECLARE
2 -- 변수 선언
3 v_avg NUMBER(3) := 0; -- 평균점수 저장
4 v_cnt NUMBER(2) := 0; /* 과목수 저장 */
5 v_student_id VARCHAR2(5) := 'C9901'; /* 검색할 학번 */
6 BEGIN
7 -- 학번의 데이터를 검색하여 과목수와 평균점수 계산
8 SELECT COUNT(Course_Id), AVG(Score)
9 INTO v_cnt, v_avg
10 FROM SG_Scores
11 WHERE student_id = v_student_id
12 GROUP BY student_id;
13 -- 화면에 표시
14 DBMS_OUTPUT.PUT_LINE (v_student_id || '의 [' || v_cnt ||
15 ']과목에 대한 평균점수는 [' || v_avg || ']점 입니다.');
16* END;
☞ SET SERVEROUTPUT ON
【예제 】SG_Scores 테이블을 참조하여 ‘A9901'학번의 과목수, 평균점수를 계산하여 출력하라.
SQL> DECLARE
2 v_avg NUMBER(3) := 0; -- 평균점수 저장
3 v_cnt NUMBER(2) := 0; /* 과목수 저장 */
4 v_student_id VARCHAR2(5) := 'A9901'; /* 검색할 학번 */
5 BEGIN
6 SELECT COUNT(Course_Id), AVG(Score)
7 INTO v_cnt, v_avg
8 FROM SG_Scores
9 WHERE student_id = v_student_id
10 GROUP BY student_id;
11 DBMS_OUTPUT.PUT_LINE (v_student_id || '의 [' || v_cnt ||
12 ']과목에 대한 평균점수는 [' || v_avg || ']점 입니다.');
13* END;
SQL> /
DECLARE
*
1행에 오류:
ORA-01403: no data found
ORA-06512: at line 8
8.12 PL/SQL에서 화면에 출력하기
▪ DBMS_OUTPUT 내장프로시저 사용하기 위한 SQL*Plus 명령어
문법> SET SERVEROUTPUT ON
▪DBMS_OUTPUT 프로시저의 활성화와 비활성화
문법> DBMS_OUTPUT.ENABLE;
DBMS_OUTPUT.DISABLE;
▪문자열을 화면에 출력하는 DBMS_OUTPUT.PUT_LINE
문법> DBMS_OUTPUT.PUT_LINE(‘출력할 내용’)
【예제】‘HELLO WORLD' 문자열을 DBMS_OUTPUT 프로시저를 비활성화하여 출력하고, 활성화한 후 출력하라.
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2 DBMS_OUTPUT.DISABLE;
3 DBMS_OUTPUT.PUT_LINE('1. HELLO WORLD.');
4 DBMS_OUTPUT.ENABLE;
5 DBMS_OUTPUT.PUT_LINE('2. HELLO WORLD.');
6* END;
SQL> /
2. HELLO WORLD.
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL>
- PL/SQL(Procedural Language/SQL)의 약어
- 오라클 DB 환경에서 실행되는 절차적인 데이터베이스 프로그래밍 언어
- 표준 SQL과 3세대 언어의 강력한 일부 기능을 포함한 SQL의 확장 언어
- PL/SQL에서는 프로그램 단위를 블록(block)이라 부르며, 애플리케이션 로직들을 작성
그림 8.1 SQL문과 PL/SQL 블록의 실행 비교
■ 오라클 환경에서 PL/SQL을 학습하는 이유
▪ 오라클 개발 도구를 수행하는 모든 프로그래밍의 기초
▪ 클라이언트가 아닌 서버 상에서 프로세스를 수행하는데 PL/SQL을 사용함
▪ PL/SQL을 사용하면 업무 규칙이나 복잡한 로직을 캡슐화(Encapsulation)할 수 있어, 모듈화(Modularity)와 추상화(Abstraction)가 가능
▪ 데이터베이스 트리거를 통하여 데이터베이스 무결성을 제약하는 복잡한 규칙의 코딩과 변경 내역, 데이터를 복사
▪ PL/SQL은 독립적인 플랫폼 수준을 제공
■ PL/SQL에서 제공하는 명령문은
▪ 모든 SQL문
▪ 변수 및 상수 등의 선언문
▪ 대입문
▪ 조건 판단문
▪ 제어 흐름문
▪ 반복 처리문
■ PL/SQL로 작성할 수 있는 것은?
▪ SQL*Plus 스크립트(scripts)
▪ 프로시저(Procedure), 함수(Function) 서브프로그램
▪ 패키지(Package)
▪ 데이터베이스 트리거 (Database Trigger)
▪ 애플리케이션 로직 (Application Logic)
8.2 PL/SQL의 기본 블록 구조
- PL/SQL의 프로그램 단위는 블록(Block)
DECLARE 선택 변수와 상수, 프로시저와 함수 서브프로그램,
커서 등을 선언
선언문
.....
-----------------------------------------------
BEGIN 필수 처리할 명령문들을 절차적으로 기술
▪ 모든 SQL문
실행문 ▪ 대입문, 반복 처리문, 조건 판단문, 제어 흐름문
..... ▪ 커서 처리문
-----------------------------------------------
EXCEPTION 선택 오류 처리에 관한 예외처리 명령문을 기술
예외처리문
.....
-----------------------------------------------
END; 필수
/
그림 8.2 PL/SQL의 기본 블록 구조
8.3 PL/SQL의 블록 작성 및 실행
8.3.1 PL/SQL 블록의 작성
▪ ‘무엇을 어떻게 처리할 것인가?’를 분석
▪ DECLARE를 기술하고, 블록에서 필요한 변수나 상수, 서브 프로그램, 커서 등을 문법에 맞추어 한 줄에 하나씩 기술
▪ BEGIN을 기술하고, 절차적인 실행 순서에 의하여 명령문을 기술
▪ EXCEPTION을 기술하고, 실행절에 기술된 명령문이 실행될 때 발생될 수 있는 오류에 대한 예외처리문을 기술
▪ 마지막으로 END;을 기술
▪ PL/SQL 블록에서 한 문장이 종료할 때마다 세미콜론(;)을 쓴다.
【예제 】
SG_Scores 테이블에서 학번이 ‘C9901'인 학생의 평균점수를 계산하여 출력하는 익명의 블록을 작성하라.
SQL> SET SERVEROUTPUT ON
SQL> get ex8_01.sql
1 DECLARE
2 v_avg NUMBER(3) := 0;
3 v_student_id VARCHAR2(5) := 'C9901';
4 BEGIN
5 SELECT AVG(Score)
6 INTO v_avg
7 FROM SG_Scores
8 WHERE student_id = v_student_id
9 GROUP BY student_id;
10 DBMS_OUTPUT.PUT_LINE (v_student_id || '의 평균점수는 ['
11 || v_avg || ']점 입니다.');
12* END;
SQL> /
C9901의 평균점수는 [80]점 입니다.
PL/SQL 처리가 정상적으로 완료되었습니다.
8.3.2 PL/SQL 블록의 실행
☞ SQL*Plus를 이용하는 방법
▪ 편집기(editor)로 PL/SQL 블록을 입력한 후, *.sql 파일로 저장
▪ @ 또는 start 명령어로 *.sql 파일을 호출하여 실행
Netterm이나 텔넷(Telnet)을 이용하여 SQL*Plus상에서 블록을 실행
☞ Oracle Developer의 Procedure Builder를 이용하는 방법
▪ Program PL/SQL Interpreter 메뉴를 선택
▪ PL/SQL Interpreter 화면에 작성한 블록을 입력
▪ END;이 입력되면 자동 실행
8.4 선언절에 변수, 상수 선언
상수는 프로그램이 시작하여 종료될 때까지 일정한 값을 가지는 수,
어떤 값을 대입 불가
12, 87과 같은 숫자형 리터럴과 ‘Computer', '홍길동’, ‘T' 와 같은 문자형 리터럴
변수는 프로그램이 시작하여 종료될 때까지 변하는 수,
어떤 값을 대입 가능. i, Course_id 등과 같이 문자로 지정
8.4.1 PL/SQL의 데이터타입
• SQL에서 사용 가능한 모든 데이터타입과 추가적인 데이터타입
데이터타입 설 명
-------------------------------------------------------------------------
BOOLEAN 논리적인 데이터(True, False, Unknown)를 저장할 때 사용
BINARY_INTEGER 부호가 있는 정수를 저장할 때 사용
데이터 범위는 -2147483647부터 2147483647까지
ꋯ NATURAL 정수 중에서 0부터 2147483647까지 저장할 때 사용
ꋯ POSITIVE 정수 중에서 1부터 2147483647까지 저장할 때 사용
%TYPE 기존 테이블의 한 칼럼에 정의된 데이터타입을 참조
%ROWTYPE 기존 테이블의 모든 칼럼에 정의된 데이터타입을 참조
8.4.2 변수와 상수 선언
변수명 데이터타입(크기) [ := 초기값 ] ;
상수명 CONSTAT 데이터타입(크기) := 상수값 ;
■ 변수명이나 상수명을 정의할 때는
∙ SQL 객체명과 동일한 규칙으로 정의하고
∙ 상수명은 데이터타입과 일치하는 상수 값을 반드시 기술하고
∙ 한 줄에 한 개의 변수나 상수를 정의하며
∙ 초기 값이 Null이나 0인 경우 생략할 수 있다.
【예제】변수와 상수의 선언 예
P_count NUMBER(3) := 0;
Zip_Code VARCHAR2(9);
I BINARY_INTEGER := 0;
k CONSTANT POSITIVE := 100;
8.4.3 테이블을 참조하여 변수 선언
- 기존 테이블에 정의된 데이터타입과 크기를 참조하여 변수를 선언
■ %TYPE
형식=> 변수명 테이블명.칼럼명%TYPE ;
- 기존 테이블의 칼럼에 선언된 데이터타입과 크기를 참조하여 변수를 선언
- 테이블 칼럼의 제약조건은 적용되지 않음.
v_student_id SG_Scores.student_id%TYPE := 'C9901';
v_avg SG_Scores.Score%TYPE := 0;
v_grade SG_Scores.Grade%TYPE;
■ %ROWTYPE
형식=> 변수명 테이블명%ROWTYPE ;
- 기존 테이블의 각 칼럼에 정의된 데이터타입과 크기를 참조
- 칼럼 수와 동일한 복수 개의 변수가 선언,
- 각 기억 장소의 구분은 “변수명.칼럼명”으로 구분
【예제】SG_Scores 테이블을 참조하여 학번과 평균 점수를 저장하기 위한 변수를 %ROWTYPE을 이용하여 선언하라.
SQL> get ex8_04.sql
1 DECLARE
2 v SG_Scores%ROWTYPE;
3 v_cnt NUMBER(2) := 0;
4 BEGIN
5 v.student_id := 'C9901';
6 SELECT Count(*), AVG(SCORE)
7 INTO v_cnt, v.Score
8 FROM SG_Scores
9 WHERE student_id = v.student_id
10 GROUP BY student_id;
11 IF v.score >= 90
12 THEN v.grade := 'A';
13 ELSIF v.Score >= 80
14 THEN v.grade := 'B';
15 ELSIF v.Score >= 70
16 THEN v.grade := 'C';
17 ELSIF v.Score >= 60
18 THEN v.grade := 'D';
19 ELSE v.grade := 'F';
20 END IF;
21 DBMS_OUTPUT.PUT_LINE (v.student_id || '의 과목수는 [' ||
22 v_cnt || ']이고 평균점수는 [' || v.Score ||
23 ']점 [' || v.grade || '] 등급입니다.');
24* END;
8.4.4 PL/SQL 테이블과 사용자 정의 레코드 선언
■ PL/SQL 테이블 선언
데이터타입과 크기가 동일한 기억장소가 동적으로 복수 개 선언
형식=>
TYPE 테이블타입명 IS TABLE OF
데이터타입(크기)
INDEX BY BINARY_INTEGER;
테이블명 테이블타입명;
- 각 기억장소 구분은 “테이블명(첨자명)”,
- 첨자명은 정수로 된 첨자명이거나, 양의 정수
- 테이블의 크기는 사용할 때 동적으로 결정
【예제】PL/SQL 테이블 선언 및 사용 예
DECLARE
TYPE Table_type IS TABLE OF
VARCHAR2(20)
INDEX BY BINARY_INTEGER;
v_Dept_Name Table_type;
BEGIN
...
v_Dept_Name(2) := '컴퓨터정보기술계열‘;
...
END;
■ 사용자 정의 레코드 선언
- 데이터타입과 크기가 다른 기억장소가 복수 개 선언
형식=> TYPE 레코드타입명 IS RECORD
( 필드명1 데이터타입(크기) [NOT NULL] [초기값],
. . .
필드명n 데이터타입(크기) [NOT NULL] [초기값] ) ;
레코드명 레코드타입명;
- 각 기억 장소의 구분은 “레코드명.필드명“으로 구분
【예제 】사용자 정의 레코드 선언 및 사용 예
DECLARE
TYPE Record_Type IS RECORD
Dept_ID Department.Dept_ID%type,
Dept_Name Department.Dept_Name%type;
Dept_Rec Record_Type;
...
BEGIN
...
Dept_Rec.Dept_ID := '컴정‘;
Dept_Rec.Dept_Name := '컴퓨터정보기술계열‘;
...
END;
8.5 조건 판단문
문법>
IF 조건1
THEN 명령문1; . . . ; 명령문N;
[ ELSIF 조건2
[ THEN 명령문1; . . . ; 명령문N; ]
[ ELSIF 조건3
. . .
[ ELSE 명령문1; . . . ; 명령문N; ]
END IF;
- 조건의 결과가 참이면 THEN 이하의 명령문을 처리하고,
거짓이면 ELSIF절을 실행
- 조건이 모두 거짓이면 ELSE절
- ELSIF문은 16개까지 반복하여 사용
IF문은
① IF 조건 THEN 명령문_1
END IF;
② IF 조건 THEN 명령문_1
ELSE 명령문_2
END IF;
③ IF 조건_1
THEN 명령문_1
ELSIF 조건_2
THEN 명령문_2
ELSIF 조건_3 THEN 명령문_3
...
ELSE 명령문_n
END IF;
【예제 】 IF ~ THEN ~ ELSIF문 예
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;
8.6 반복문
8.6.1 LOOP문
문법> LOOP
명령문1;
. . . ;
명령문N;
END LOOP;
- EXIT문은 무한 루프로부터 탈출하는 명령문
■ EXIT문
문법> ① EXIT;
② EXIT [레이블명] WHEN 조건;
∙ EXIT문은 무한 루프로부터 무조건 탈출
∙ EXIT WHEN 조건 명령문은 조건이 참(True)일 때 탈출
【예제 】1에서 10까지 반복하여 TEMP 테이블에 저장하라. (LOOP문 사용)
SQL> CREATE TABLE Temp (
2 Col1 NUMBER(3),
3 Col2 DATE);
SQL> DECLARE
2 Max_No CONSTANT POSITIVE := 10;
3 I NATURAL := 0;
4 BEGIN
5 LOOP
6 I := I + 1;
7 EXIT WHEN I > Max_No;
8 INSERT INTO TEMP
9 VALUES (I, SYSDATE);
10 END LOOP;
11* END;
SQL> SELECT * FROM Temp;
8.6.2 WHILE ~ LOOP문
조건이 참(True)일 때, LOOP ~ END LOOP내의 명령문들을 반복 처리
문법> WHILE 조건 LOOP
명령문1;
. . . ;
명령문N;
END LOOP;
【예제】1에서 10까지 반복하여 TEMP 테이블에 저장하라. (WHILE문 사용)
SQL> DECLARE
2 Max_No CONSTANT POSITIVE := 10;
3 I NATURAL := 0;
4 BEGIN
5 WHILE I < MAX_NO LOOP
6 I := I + 1;
7 INSERT INTO TEMP
8 VALUES (I, SYSDATE);
9 END LOOP;
10* END;
8.6.3 FOR ~ LOOP문
- 변수가 초기 값부터 1씩 증가하여 최종 값이 될 때까지 반복 처리
문법> FOR 변수 IN [REVERSE] 초기값..최종값 LOOP
명령문1;
. . . ;
명령문N;
END LOOP;
【예제】1에서 10까지 반복하여 TEMP 테이블에 저장하라. (FOR문 사용)
SQL> DECLARE
2 Max_No CONSTANT POSITIVE := 10;
3 I NATURAL := 0;
4 BEGIN
5 FOR I IN 1..MAX_NO LOOP
6 INSERT INTO TEMP
7 VALUES (I, SYSDATE);
8 END LOOP;
9* END;
8.7 NULL문
- 어떤 처리도 하지 않음. 문법 형식을 갖추기 위해 사용
문법>NULL;
【예제 】변수 a가 0보다 크지 않으면 i 값을 1 증가시켜라.
IF a > 0 THEN NULL;
ELSE i = i + 1;
END IF;
8.8 대입문
문법> expr1 := expr2
우변(칼럼명, 변수, 리터럴, 수식, 함수)의 값을 좌변(칼럼명, 변수)에 저장
8.9 제어문
GOTO문은 무조건 레이블명으로 이동
문법> GOTO 레이블명
【예제 】GOTO문을 이용한 제어문 사용 예
....
GOTO label_1;
....
<
...
8.10 주석 달기
☞ -- 문자열
- 단일 줄에 주석
☞ /* 문자열 */
- 여러 줄로 주석
8.11 실행절에 SQL문 사용
8.11.1 PL/SQL에서의 SELECT문
- 검색된 행의 수가 한 행이 되어야 하고, 검색한 값을 INTO절의 변수에 저장
- 검색된 행의 수가 0행(NO_DATA_FOUND)이거나 복수 행(TOO_MANY_ROWS)일 때 오류가 발생
- ORDER BY절은 사용하지 않음
문법>
SELECT 칼럼명1, 칼럼명2, 리터럴, 함수, 수식, ...
INTO 변수명, . . .
FROM 테이블명1, 테이블명2, 뷰명1, ...
WHERE 검색조건1 ...
GROUP BY 칼럼명1, 칼럼명2, ...
HAVING 검색조건2 ;
■ SQL과 PL/SQL에서 SELECT문의 차이점
∙ SELECT 구문이 다름
∙ 결과 행
∙ 결과를 출력
【예제 】SG_Scores 테이블을 참조하여 ‘C9901'학번의 과목수, 평균 점수를 계산하여 출력하라.
SQL> DECLARE
2 -- 변수 선언
3 v_avg NUMBER(3) := 0; -- 평균점수 저장
4 v_cnt NUMBER(2) := 0; /* 과목수 저장 */
5 v_student_id VARCHAR2(5) := 'C9901'; /* 검색할 학번 */
6 BEGIN
7 -- 학번의 데이터를 검색하여 과목수와 평균점수 계산
8 SELECT COUNT(Course_Id), AVG(Score)
9 INTO v_cnt, v_avg
10 FROM SG_Scores
11 WHERE student_id = v_student_id
12 GROUP BY student_id;
13 -- 화면에 표시
14 DBMS_OUTPUT.PUT_LINE (v_student_id || '의 [' || v_cnt ||
15 ']과목에 대한 평균점수는 [' || v_avg || ']점 입니다.');
16* END;
☞ SET SERVEROUTPUT ON
【예제 】SG_Scores 테이블을 참조하여 ‘A9901'학번의 과목수, 평균점수를 계산하여 출력하라.
SQL> DECLARE
2 v_avg NUMBER(3) := 0; -- 평균점수 저장
3 v_cnt NUMBER(2) := 0; /* 과목수 저장 */
4 v_student_id VARCHAR2(5) := 'A9901'; /* 검색할 학번 */
5 BEGIN
6 SELECT COUNT(Course_Id), AVG(Score)
7 INTO v_cnt, v_avg
8 FROM SG_Scores
9 WHERE student_id = v_student_id
10 GROUP BY student_id;
11 DBMS_OUTPUT.PUT_LINE (v_student_id || '의 [' || v_cnt ||
12 ']과목에 대한 평균점수는 [' || v_avg || ']점 입니다.');
13* END;
SQL> /
DECLARE
*
1행에 오류:
ORA-01403: no data found
ORA-06512: at line 8
8.12 PL/SQL에서 화면에 출력하기
▪ DBMS_OUTPUT 내장프로시저 사용하기 위한 SQL*Plus 명령어
문법> SET SERVEROUTPUT ON
▪DBMS_OUTPUT 프로시저의 활성화와 비활성화
문법> DBMS_OUTPUT.ENABLE;
DBMS_OUTPUT.DISABLE;
▪문자열을 화면에 출력하는 DBMS_OUTPUT.PUT_LINE
문법> DBMS_OUTPUT.PUT_LINE(‘출력할 내용’)
【예제】‘HELLO WORLD' 문자열을 DBMS_OUTPUT 프로시저를 비활성화하여 출력하고, 활성화한 후 출력하라.
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2 DBMS_OUTPUT.DISABLE;
3 DBMS_OUTPUT.PUT_LINE('1. HELLO WORLD.');
4 DBMS_OUTPUT.ENABLE;
5 DBMS_OUTPUT.PUT_LINE('2. HELLO WORLD.');
6* END;
SQL> /
2. HELLO WORLD.
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL>
출처 : [기타] 인터넷 : http://www.it-sesang.com 오라클공인센터
Posted by 깡통~
Rss Feed