#5. 함수 결과 캐싱(Function Result Caching)
오라클11g의 함수결과 캐싱(Function Result Caching)은 함수의 결과를 캐싱하는 기능으로 캐싱기능을 이용하여 함수를 호출하면 오라클은 같은 파라미터 입력 값으로 이미 실행되었는지 검사하고, 실행된적이 있다면 함수 BODY를 실행하지 않고 캐시 영역에서 결과치를 리턴한다. 만약 처음 실행한다면 함수를 실행하고 결과를 캐시에 저장한다.
만약 원본 데이터에 변경이 생기면 오라클 서버는 캐시를 없애고 함수의 요청이 있다면 다시 실행하고 캐시에 저장한다.
SQL> select count(*) from myemp1;
COUNT(*)
----------
10000000
SQL> CREATE OR REPLACE FUNCTION get_sal
(p_deptno IN NUMBER)
RETURN NUMBER
IS
v_sal NUMBER;
BEGIN
SELECT sum(sal)
INTO v_sal
FROM myemp1
WHERE deptno = p_deptno;
RETURN v_sal;
END get_sal ;
/
함수가 생성되었습니다.
SQL> set timing on
SQL> select get_sal(0) from dual;
GET_SAL(0)
----------
1.0000E+12
경 과: 00:00:10.68
SQL> select get_sal(0) from dual;
GET_SAL(0)
----------
1.0000E+12
경 과: 00:00:07.81
이번에는 함수 결과를 캐싱 해보자.
SQL> CREATE OR REPLACE FUNCTION get_sal2
(p_deptno IN NUMBER)
RETURN NUMBER
RESULT_CACHE RELIES_ON (myemp1)
IS
v_sal NUMBER;
BEGIN
SELECT sum(sal)
INTO v_sal
FROM myemp1
WHERE deptno = p_deptno;
RETURN v_sal;
END get_sal2 ;
/
SQL> select get_sal2(0) from dual;
GET_SAL2(0)
-----------
1.0000E+12
경 과: 00:00:08.32
-- 아래는 캐시에서 결과를 가져온다.
SQL> select get_sal2(0) from dual;
GET_SAL2(0)
-----------
1.0000E+12
경 과: 00:00:00.01
-- 이번에는 원본데이터에 변화를 줘보자.
SQL> insert into myemp1 (empno, ename, sal, deptno) values (11111111,'11111111길동',1000000, 0);
SQL> commit;
-- 원본데이터가 변경되었으므로 함수를 다시 실행하고 결과를 캐시에 보관한다.
SQL> select get_sal2(0) from dual;
GET_SAL2(0)
-----------
5.8000E+12
경 과: 00:00:14.09
-- 다시 실행하니 캐시에서 가져와 빠르다.
SQL> select get_sal2(0) from dual;
GET_SAL2(0)
-----------
5.8000E+12
경 과: 00:00:00.17
2016년 9월 28일 수요일
[오라클학원,SQL학원,PLSQL학원추천◆탑크리에듀]#4. PLSQL 프로시저(Procedure)
#4. PLSQL 프로시저(Procedure)
프러시저(Procedure)
자신을 호출한 곳으로 리턴 해주는 값은 없고 호출되어 실행만 된다.
실행환경과 Stored Program 사이에 값을 전달하기 위해 파라미터를 사용한다.
파라미터 종류
IN : 호출하는 곳에서 함수 or 프로시저로 값을 전달
OUT : 함수 or 프로시저에서 호출한 곳으로 값을 전달
IN OUT : 호출하는 곳에서 함수 or 프로시저로 값을 전달하고 동시에 함수 or 프로시저에서 호출한 곳으로 값을 전달하는 변수.
프러시저 Header의 끝에는 IS[AS]가 와야하고 Is와 Begin사이에 Begin ~ End에서 사용할 변수를 선언한다.
[형식]
CREATE [OR REPLACE] PROCEDURE procedure_name [parameters]
IS[/AS]
Declaration_section
BEGIN
Execution_section
EXCEPTION
exception section
END;
/
SQL> edit ojc3
CREATE OR REPLACE PROCEDURE ojc3
(p_empno IN NUMBER, p_new_sal IN NUMBER)
IS
BEGIN
UPDATE emp
SET sal = p_new_sal
WHERE empno = p_empno;
COMMIT;
END ojc3 ;
/
SQL> @ojc3
프로시저가 생성되었습니다.
SQL> select empno, ename, sal from emp where empno = 7369;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
SQL> exec ojc3(7369, 4500)
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> select empno, ename, sal from emp where empno = 7369;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 4500
[Procedure에서 OUT 변수 사용 예제]
SQL> edit ojc4
CREATE OR REPLACE PROCEDURE ojc4(p_empno IN NUMBER, p_sal OUT NUMBER)
IS
BEGIN
SELECT sal INTO p_sal FROM emp
WHERE empno = p_empno;
END ojc4;
/
SQL> @ojc4
프로시저가 생성되었습니다.
SQL> variable sal number
SQL> exec ojc4(7369, :sal)
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> print sal
SAL
----------
4500
프러시저(Procedure)
자신을 호출한 곳으로 리턴 해주는 값은 없고 호출되어 실행만 된다.
실행환경과 Stored Program 사이에 값을 전달하기 위해 파라미터를 사용한다.
파라미터 종류
IN : 호출하는 곳에서 함수 or 프로시저로 값을 전달
OUT : 함수 or 프로시저에서 호출한 곳으로 값을 전달
IN OUT : 호출하는 곳에서 함수 or 프로시저로 값을 전달하고 동시에 함수 or 프로시저에서 호출한 곳으로 값을 전달하는 변수.
프러시저 Header의 끝에는 IS[AS]가 와야하고 Is와 Begin사이에 Begin ~ End에서 사용할 변수를 선언한다.
[형식]
CREATE [OR REPLACE] PROCEDURE procedure_name [parameters]
IS[/AS]
Declaration_section
BEGIN
Execution_section
EXCEPTION
exception section
END;
/
SQL> edit ojc3
CREATE OR REPLACE PROCEDURE ojc3
(p_empno IN NUMBER, p_new_sal IN NUMBER)
IS
BEGIN
UPDATE emp
SET sal = p_new_sal
WHERE empno = p_empno;
COMMIT;
END ojc3 ;
/
SQL> @ojc3
프로시저가 생성되었습니다.
SQL> select empno, ename, sal from emp where empno = 7369;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
SQL> exec ojc3(7369, 4500)
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> select empno, ename, sal from emp where empno = 7369;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 4500
[Procedure에서 OUT 변수 사용 예제]
SQL> edit ojc4
CREATE OR REPLACE PROCEDURE ojc4(p_empno IN NUMBER, p_sal OUT NUMBER)
IS
BEGIN
SELECT sal INTO p_sal FROM emp
WHERE empno = p_empno;
END ojc4;
/
SQL> @ojc4
프로시저가 생성되었습니다.
SQL> variable sal number
SQL> exec ojc4(7369, :sal)
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> print sal
SAL
----------
4500
[오라클학원,SQL학원,PLSQL학원추천◆탑크리에듀]#3. PLSQL 함수(Function)
#3. PLSQL 함수(Function)
함수(Function)
자신을 호출한 곳으로 반드시 하나의 값을 리턴해 줘야되는 PL/SQL Stored Program 이다.
함수 Header에 리턴되는 데이터 타입을 기술해야 한다.
함수 Header의 끝에는 IS[AS]가 와야하고 Is와 Begin사이에 Begin~End에서 사용할 변수를 선언한다.
[형식]
CREATE [OR REPLACE] FUNCTION function_name [parameters]
RETURN 리턴되는 데이터타입
IS[/AS]
Declaration_section
BEGIN
Execution_section
Return return_variable;
EXCEPTION
exception section
Return return_variable;
END;
/
SQL> edit ojc2
CREATE OR REPLACE FUNCTION ojc2(p_empno IN NUMBER)
RETURN VARCHAR2
IS
v_ename emp.ename%TYPE;
BEGIN
-- BEGIN~END 사이의 SELECT절은 반드시 한건만 SELECT 되야하며 INTO절이 반드시 필요
SELECT ename
INTO v_ename
FROM emp
WHERE empno = p_empno;
RETURN v_ename;
END ojc2;
/
SQL> @ojc2
함수가 생성되었습니다.
SQL> variable ename varchar2(50)
SQL> exec :ename := ojc2(7369)
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> print ename
ENAME
-------------------------------------------------------------------
SMITH
SQL> select ojc2(7369) from dual;
OJC2(7788)
------------------------------------
SCOTT
SQL> drop table emp_temp;
SQL> create table emp_temp as select empno, sal, ename from emp where 1 = 2;
SQL> insert into emp_temp values (7788, 9999, ojc2(7788));
1 개의 행이 만들어졌습니다.
SQL> select * from emp_temp;
EMPNO SAL ENAME
---------- ---------- ----------
77888 9999 SCOTT
함수(Function)
자신을 호출한 곳으로 반드시 하나의 값을 리턴해 줘야되는 PL/SQL Stored Program 이다.
함수 Header에 리턴되는 데이터 타입을 기술해야 한다.
함수 Header의 끝에는 IS[AS]가 와야하고 Is와 Begin사이에 Begin~End에서 사용할 변수를 선언한다.
[형식]
CREATE [OR REPLACE] FUNCTION function_name [parameters]
RETURN 리턴되는 데이터타입
IS[/AS]
Declaration_section
BEGIN
Execution_section
Return return_variable;
EXCEPTION
exception section
Return return_variable;
END;
/
SQL> edit ojc2
CREATE OR REPLACE FUNCTION ojc2(p_empno IN NUMBER)
RETURN VARCHAR2
IS
v_ename emp.ename%TYPE;
BEGIN
-- BEGIN~END 사이의 SELECT절은 반드시 한건만 SELECT 되야하며 INTO절이 반드시 필요
SELECT ename
INTO v_ename
FROM emp
WHERE empno = p_empno;
RETURN v_ename;
END ojc2;
/
SQL> @ojc2
함수가 생성되었습니다.
SQL> variable ename varchar2(50)
SQL> exec :ename := ojc2(7369)
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> print ename
ENAME
-------------------------------------------------------------------
SMITH
SQL> select ojc2(7369) from dual;
OJC2(7788)
------------------------------------
SCOTT
SQL> drop table emp_temp;
SQL> create table emp_temp as select empno, sal, ename from emp where 1 = 2;
SQL> insert into emp_temp values (7788, 9999, ojc2(7788));
1 개의 행이 만들어졌습니다.
SQL> select * from emp_temp;
EMPNO SAL ENAME
---------- ---------- ----------
77888 9999 SCOTT
[오라클학원,SQL학원,PLSQL학원추천◆탑크리에듀]#2. PLSQL과 관련된 SQL 명령어
#2. PLSQL과 관련된 SQL 명령어
PL/SQL과 관련된 SQL*Plus 명령어
Accept : 사용자에게 메시지를 보여주며 사용자의 입력을 기술한 치환변수로 받아낸다.
Variable : PL/SQL내부에서 참조되는 바인드변수를 정의한다.
Print : 바인드 변수의 값을 출력한다.
Execute : PL/SQL 명령문을 실행(함수나 프로시저를 실행한다)
치환변수 : 명령이 실행될 때마다 사용자에게 입력값을 요구한다. 단일 엠퍼샌드(&)를 사용한다. 이중 앰퍼샌드(&&)로 선언하는 경우에는 세션기간동안 또는 변수가 재설정 될 때까지 그 값을 유지하므로 명령이 실행될 때 마다 입력값을 요구하지는 않는다.
SQL> edit ojc1
ACCEPT p_deptno PROMPT 'Enter the Deptno:'
VARIABLE total_sal NUMBER
DECLARE
BEGIN
SELECT SUM(SAL) INTO :total_sal FROM EMP
WHERE deptno = &p_deptno;
END;
/
PRINT total_sal
SQL> @OJC1
Enter the Deptno:10
구 4: WHERE deptno = &p_deptno;
신 4: WHERE deptno = 10;
PL/SQL 처리가 정상적으로 완료되었습니다.
TOTAL_SAL
----------
8750
PL/SQL과 관련된 SQL*Plus 명령어
Accept : 사용자에게 메시지를 보여주며 사용자의 입력을 기술한 치환변수로 받아낸다.
Variable : PL/SQL내부에서 참조되는 바인드변수를 정의한다.
Print : 바인드 변수의 값을 출력한다.
Execute : PL/SQL 명령문을 실행(함수나 프로시저를 실행한다)
치환변수 : 명령이 실행될 때마다 사용자에게 입력값을 요구한다. 단일 엠퍼샌드(&)를 사용한다. 이중 앰퍼샌드(&&)로 선언하는 경우에는 세션기간동안 또는 변수가 재설정 될 때까지 그 값을 유지하므로 명령이 실행될 때 마다 입력값을 요구하지는 않는다.
SQL> edit ojc1
ACCEPT p_deptno PROMPT 'Enter the Deptno:'
VARIABLE total_sal NUMBER
DECLARE
BEGIN
SELECT SUM(SAL) INTO :total_sal FROM EMP
WHERE deptno = &p_deptno;
END;
/
PRINT total_sal
SQL> @OJC1
Enter the Deptno:10
구 4: WHERE deptno = &p_deptno;
신 4: WHERE deptno = 10;
PL/SQL 처리가 정상적으로 완료되었습니다.
TOTAL_SAL
----------
8750
[오라클학원,SQL학원,PLSQL학원추천◆탑크리에듀]#1. PLSQL개요,장점,작성법,간단예제
#1. PLSQL개요,장점,작성법,간단예제
PL/SQL 개요
1-1. PL/SQL 이란?
PL/SQL은 PROCEDUAL LANGUAGE/SQL의 약어로 SQL 구문을 절차적인 형태로 프로그래밍 할 수 있게 한 SQL의 확장이다.
PL/SQL은 BEGIN~END 사이에 PL/SQL코드를 기술하는데 DML문, 절차적 언어의 IF문과 같은 제어문, 반복문(LOOP~END LOOP) 등을 사용하여 SQL을 이용하여 프로그래밍이 가능하도록 한 것이다.
PL/SQL은 DECLARE로 선언되는 익명 블록, 값을 반드시 리턴하는 함수(FUNCTION), 실행만 하는 프로시저(PROCEDURE), 트리거링 이벤트에 의해 자동 실행되는 프러시저인 트리거(Trigger), 비슷한 기능을 하는 함수, 프러시저를 묶어 놓은 패키지 등이 있다.
익명 블럭은 오라클 서버에서 컴파일되어 SGA의 SHARED POOL에 위치시키지만 소스코드와 컴파일된 코드를 별도로 저장하지는 않는다.
함수나 프로시저, 패키지는 STORED PROGRAM 이라 하며 데이터베이스 SCHEMA OBJECTS로 저장되고 호출되어 실행된다.
PL/SQL로 작성된 구문중 절차적 언어와 같은 구문은 PROCEDUAL STATEMENT EXECUTOR에서 실행하고 SQL구문은 SQL STATEMENT EXECUTOR에서 실행한다.
PL/SQL 프로그램은 오라클 서버에서 컴파일 되고 저장되어 호출되어 실행된다
1-2. PL/SQL 장점
BEGIN~END와 같은 블록 구조를 가지므로 블록안의 여러 SQL 구문이 한번에 서버로 전송되므로 수행속도가 향상되고 통신량도 줄일 수 있다.
블록안에 또 다른 블록을 포함하는 형태로 코드를 모듈화 할 수 있다.
단일형 데이터 타입(SCALAR DATA TYPE), 복합데이터 타입(COMPOSITE DATA TYPE), 상수 등 여러 형태의 변수 선언이 가능하다.
테이블의 칼럼을 참조하는 동적 변수를 정의 할 수 있다.(%TYPE, %ROWTYPE)
IF문과 같은 조건문을 사용할 수 있다.
LOOP~END LOOP, FOR..LOOP~END LOOP, WHILE .. LOOP~END LOOP와 같은 반복문을 사용할 수 있다.
Exception절을 이용하여 프로그래밍 언어처럼 예외(오류)를 처리 할 수 있으며 사용자 정의 예외의 사용도 가능하다.
1-3. PL/SQL 작성방법
명령문의 종료는 세미콜론(;) 이다.
CREATE 구문을 사용했다면 실행을 위해 마지막에 슬래시(‘/’)가 필요하다.
익명의 블록을 만들기 위해서는 DECLARE 구문으로, 이름있는 블록을 만들기 위해서는 CREATE 구문을 사용해야 한다.
하나의 BEGIN에 대응되는 END 구문은 하나이며 세미콜론(;)으로 끝나야 하고, END 다음에 CREATE 구문에서 사용된 함수나 프로시저의 이름을 써줘도 된다.
Declare로 선언된 익명블럭인 경우 컴파일시 에러가 발생하면 에러 코드/메시지를 하단에 보여주지만 CREATE로 시작하는 함수나 프러시저는 오류 발생시 SHOW ERRORS(ERROR)로 확인해야 한다.
SQL> SHOW ERRORS
LINE/COL ERROR
------------- ----------------------------------------------------------------
10/13 PL/SQL: SQL Statement ignored
11/10 PLS-00201: ‘EMP.SAL' …..
1-4. PL/SQL 블록 구조
DECLARE
Variable declaration
BEGIN
Program Execution
……
EXCEPTION
Exception handling
……
END;
[PL/SQL 블록 구조]
Declare절 : 선언부로써 필수는 아님, 변수 or 커서 등을 선언한다. 이름을 정의하고 재 사용할 PL/SQL 블록을 만들기 위해서는 CREATE구문을 사용해야 한다.
- 변수선언예
goodsNo NUMBER(4);
isLogin BOOLEAN;
ename VARCHAR2(20);
- 커서 선언 예
Declare
Cursor cEmp is select empno, ename from emp where deptno = 10;
Begin
……
End;
/
Begin ~ End절 : 필수 요소이며 절차적 언어의 특징 및 SQL을 사용하여 비즈니스 로직을 구현한다.
Exception절 : 예외처리절(프로그래밍 언어의 CATCH구문) 이다.
[PL/SQL 예문]
SQL> DROP TABLE emp_temp;
SQL> CREATE TABLE emp_temp AS SELECT empno, sal, ename FROM emp WHERE 1 = 2;
SQL> EDIT SAMPLE.SQL
-- 사번이 7876인 사원부터 시작해서 관리자(MGR)를 계속 확인하면서
-- 계층구조 위쪽으로 탐색하다가 급여가 3000보다큰 경우 반복문을 빠져 나와
-- EMP_TEMP 테이블에 INSERT하는 예제
--이름없는 익명 블록을 만든다.
DECLARE
-- 선언부 : BEGIN절에서 사용할 변수를 선언한다.
V_EMPNO EMP.EMPNO%TYPE;
V_SAL EMP.SAL%TYPE := 0;
V_MGR_NUM EMP.MGR%TYPE;
V_ENAME EMP.ENAME%TYPE;
V_STARTING_EMPNO EMP.EMPNO%TYPE := 7876;
-- 실행부(BEGIN~END)
BEGIN
SELECT MGR INTO V_MGR_NUM
FROM EMP
WHERE EMPNO = V_STARTING_EMPNO;
-- 7876부터 상위로 관리자(MGR)을 따라 위로 검색하면서 급여가 3000보다 크다면 루프를 빠져나간다.
WHILE V_SAL <= 3000 LOOP
SELECT EMPNO, SAL, ENAME, MGR
INTO V_EMPNO, V_SAL, V_ENAME, V_MGR_NUM
FROM EMP
WHERE EMPNO = V_MGR_NUM;
END LOOP;
INSERT INTO EMP_TEMP VALUES(V_EMPNO, V_SAL, V_ENAME);
COMMIT;
-- 예외처리부
EXCEPTION
-- 오라클 서버에 사전에 정의된 예외들
WHEN NO_DATA_FOUND THEN
INSERT INTO EMP_TEMP VALUES (NULL,NULL,'NOT FOUND');
COMMIT;
WHEN TOO_MANY_ROWS THEN
INSERT INTO EMP_TEMP VALUES (NULL,NULL,'TOO MANY ROWS');
COMMIT;
END;
/
SQL>@SAMPLE
SQL>SELECT * FROM EMP_TEMP;
PL/SQL 개요
1-1. PL/SQL 이란?
PL/SQL은 PROCEDUAL LANGUAGE/SQL의 약어로 SQL 구문을 절차적인 형태로 프로그래밍 할 수 있게 한 SQL의 확장이다.
PL/SQL은 BEGIN~END 사이에 PL/SQL코드를 기술하는데 DML문, 절차적 언어의 IF문과 같은 제어문, 반복문(LOOP~END LOOP) 등을 사용하여 SQL을 이용하여 프로그래밍이 가능하도록 한 것이다.
PL/SQL은 DECLARE로 선언되는 익명 블록, 값을 반드시 리턴하는 함수(FUNCTION), 실행만 하는 프로시저(PROCEDURE), 트리거링 이벤트에 의해 자동 실행되는 프러시저인 트리거(Trigger), 비슷한 기능을 하는 함수, 프러시저를 묶어 놓은 패키지 등이 있다.
익명 블럭은 오라클 서버에서 컴파일되어 SGA의 SHARED POOL에 위치시키지만 소스코드와 컴파일된 코드를 별도로 저장하지는 않는다.
함수나 프로시저, 패키지는 STORED PROGRAM 이라 하며 데이터베이스 SCHEMA OBJECTS로 저장되고 호출되어 실행된다.
PL/SQL로 작성된 구문중 절차적 언어와 같은 구문은 PROCEDUAL STATEMENT EXECUTOR에서 실행하고 SQL구문은 SQL STATEMENT EXECUTOR에서 실행한다.
PL/SQL 프로그램은 오라클 서버에서 컴파일 되고 저장되어 호출되어 실행된다
1-2. PL/SQL 장점
BEGIN~END와 같은 블록 구조를 가지므로 블록안의 여러 SQL 구문이 한번에 서버로 전송되므로 수행속도가 향상되고 통신량도 줄일 수 있다.
블록안에 또 다른 블록을 포함하는 형태로 코드를 모듈화 할 수 있다.
단일형 데이터 타입(SCALAR DATA TYPE), 복합데이터 타입(COMPOSITE DATA TYPE), 상수 등 여러 형태의 변수 선언이 가능하다.
테이블의 칼럼을 참조하는 동적 변수를 정의 할 수 있다.(%TYPE, %ROWTYPE)
IF문과 같은 조건문을 사용할 수 있다.
LOOP~END LOOP, FOR..LOOP~END LOOP, WHILE .. LOOP~END LOOP와 같은 반복문을 사용할 수 있다.
Exception절을 이용하여 프로그래밍 언어처럼 예외(오류)를 처리 할 수 있으며 사용자 정의 예외의 사용도 가능하다.
1-3. PL/SQL 작성방법
명령문의 종료는 세미콜론(;) 이다.
CREATE 구문을 사용했다면 실행을 위해 마지막에 슬래시(‘/’)가 필요하다.
익명의 블록을 만들기 위해서는 DECLARE 구문으로, 이름있는 블록을 만들기 위해서는 CREATE 구문을 사용해야 한다.
하나의 BEGIN에 대응되는 END 구문은 하나이며 세미콜론(;)으로 끝나야 하고, END 다음에 CREATE 구문에서 사용된 함수나 프로시저의 이름을 써줘도 된다.
Declare로 선언된 익명블럭인 경우 컴파일시 에러가 발생하면 에러 코드/메시지를 하단에 보여주지만 CREATE로 시작하는 함수나 프러시저는 오류 발생시 SHOW ERRORS(ERROR)로 확인해야 한다.
SQL> SHOW ERRORS
LINE/COL ERROR
------------- ----------------------------------------------------------------
10/13 PL/SQL: SQL Statement ignored
11/10 PLS-00201: ‘EMP.SAL' …..
1-4. PL/SQL 블록 구조
DECLARE
Variable declaration
BEGIN
Program Execution
……
EXCEPTION
Exception handling
……
END;
[PL/SQL 블록 구조]
Declare절 : 선언부로써 필수는 아님, 변수 or 커서 등을 선언한다. 이름을 정의하고 재 사용할 PL/SQL 블록을 만들기 위해서는 CREATE구문을 사용해야 한다.
- 변수선언예
goodsNo NUMBER(4);
isLogin BOOLEAN;
ename VARCHAR2(20);
- 커서 선언 예
Declare
Cursor cEmp is select empno, ename from emp where deptno = 10;
Begin
……
End;
/
Begin ~ End절 : 필수 요소이며 절차적 언어의 특징 및 SQL을 사용하여 비즈니스 로직을 구현한다.
Exception절 : 예외처리절(프로그래밍 언어의 CATCH구문) 이다.
[PL/SQL 예문]
SQL> DROP TABLE emp_temp;
SQL> CREATE TABLE emp_temp AS SELECT empno, sal, ename FROM emp WHERE 1 = 2;
SQL> EDIT SAMPLE.SQL
-- 사번이 7876인 사원부터 시작해서 관리자(MGR)를 계속 확인하면서
-- 계층구조 위쪽으로 탐색하다가 급여가 3000보다큰 경우 반복문을 빠져 나와
-- EMP_TEMP 테이블에 INSERT하는 예제
--이름없는 익명 블록을 만든다.
DECLARE
-- 선언부 : BEGIN절에서 사용할 변수를 선언한다.
V_EMPNO EMP.EMPNO%TYPE;
V_SAL EMP.SAL%TYPE := 0;
V_MGR_NUM EMP.MGR%TYPE;
V_ENAME EMP.ENAME%TYPE;
V_STARTING_EMPNO EMP.EMPNO%TYPE := 7876;
-- 실행부(BEGIN~END)
BEGIN
SELECT MGR INTO V_MGR_NUM
FROM EMP
WHERE EMPNO = V_STARTING_EMPNO;
-- 7876부터 상위로 관리자(MGR)을 따라 위로 검색하면서 급여가 3000보다 크다면 루프를 빠져나간다.
WHILE V_SAL <= 3000 LOOP
SELECT EMPNO, SAL, ENAME, MGR
INTO V_EMPNO, V_SAL, V_ENAME, V_MGR_NUM
FROM EMP
WHERE EMPNO = V_MGR_NUM;
END LOOP;
INSERT INTO EMP_TEMP VALUES(V_EMPNO, V_SAL, V_ENAME);
COMMIT;
-- 예외처리부
EXCEPTION
-- 오라클 서버에 사전에 정의된 예외들
WHEN NO_DATA_FOUND THEN
INSERT INTO EMP_TEMP VALUES (NULL,NULL,'NOT FOUND');
COMMIT;
WHEN TOO_MANY_ROWS THEN
INSERT INTO EMP_TEMP VALUES (NULL,NULL,'TOO MANY ROWS');
COMMIT;
END;
/
SQL>@SAMPLE
SQL>SELECT * FROM EMP_TEMP;
피드 구독하기:
글 (Atom)