![[Oracle] PL/SQL](https://img1.daumcdn.net/thumb/R750x0/?scode=mtistory2&fname=https%3A%2F%2Ft1.daumcdn.net%2Fcfile%2Ftistory%2F99697F445B93D43D12)
10. PL/SQL
가. PL/SQL 이란?
PL/SQL : Oracle's Procedural Language extension to SQL
오라클에 내장되어 있는 절차형 언어
프로그램을 논리적인 블록으로 나누는 구조화된 블록 언어
변수 선언문, 조건문, 반복문(loop, while, for)등을 지원
나. PL/SQL Block Structure
선언부(선택), 실행부(필수), 예외 처리부(선택)로 구성됨
BEGIN과 END 키워드는 반드시 기술
image
1) Declarative Section(선언부, 선택)
변수, 상수, CURSOR 등을 선언
2) Executable Section(실행부, 필수)
SQL 문장, 반복문, 조건문 등을 실행
BEGIN으로 시작하고 END로 끝남
3) Exception Handling Section(예외처리부, 선택)
예외에 대한 처리
다. Block Type(PL/SQL 블럭의 유형)
[ Anonymous ]
[ Procedure ]
[ Function ]
image
1) Anonymous Block(익명 블록)
이름이 없는 블록
2) Procedure(프로시저)
특정 작업을 수행할 수 있고, 이름이 있는 PL/SQL 블록
매개 변수를 입력받을 수 있음
DB에 저장되어 반복적으로 사용할 수 있음.
배치 작업 또는 구현이 복잡한 트랜잭션을 수행하는 용도로 사용함
3) Function(함수)
값을 게산하고 결과값을 반환하기 위해서 사용
저장 프로시저와의 차이점 : 입력 매개변수만 사용할 수 있고 리턴 타입을 반드시 지정해야 함
라. 저장 프로시저
1) Stored Procedure(SP, 저장 프로시저)
가) 특정 작업을 수행할 수 있고, 이름이 있는 PL/SQL 블록
나) 매개 변수를 입력받을 수 있음
다) DB에 저장되어 반복적으로 사용할 수 있음.
라) 배치 작업 또는 구현이 복잡한 트랜잭션을 수행하는 용도로 사용함
2) 형식
CREATE OR REPLACE 저장프로시저이름
(매개변수)
IS
변수 선언
BEGIN
문장
END;
3) 저장 프로시저 실습 예제
가) 급여 인상 저장 프로시저 실습
-- create or replace procedure 프로시저이름(매개변수)
-- 입력매개변수 : 변수명 in 자료형
-- 출력매개변수 : 변수명 out 자료형
create or replace procedure update_sal(v_empno in number)
is
begin
-- 모든 sql + 제어문(if, loop, for)
update emp
set sal = sal * 1.1
where empno = v_empno;
end;
/
-- 저장 프로시저의 실행 방법
-- execute 저장프로시저이름(입력값)
select * from emp;
execute update_sal(7369);
나) Servers/Tomcat v.9.0...../context.xml
마) src/emp/EmpDAO.java 09;32
카) WebContent/memo/memo_list.jsp
마. 함수(Function)
1) 값을 계산하고 결과값을 반환하기 위해서 사용
2) 저장 프로시저와의 차이점
입력 매개변수만 사용할 수 있고 리턴 타입을 반드시 지정해야 함
3) 형식
CREATE OR REPLACE FUNCTION 함수이름
[(argument...)]
RETURN datatype
-- Datatype은 반환되는 값의 datatype 입니다.
IS
변수 선언
BEGIN
문장
return
END;
4) 함수 예제
create or replace function fn_update_sal(v_empno number)
return number -- 리턴 자료형
is
v_sal number; -- 지역 변수
begin
update emp set sal = sal * 1.1 where empno = v_empno;
-- select sal * 1.1 into v_sal from emp -- 10% 인상금액을 v_sal에 저장
select sal into v_sal from emp
where empno = v_empno;
return v_sal; -- 인상된 금액을 리턴
end;
/
-- 7369번 사원의 급여
blah blah
바. PL/SQL 제어문
1) %TYPE 데이터형
가) 테이블의 컬럼 데이터 타입을 모를 경우 사용
나) 테이블의 데이터 타입이 변경될 경우 다시 수정할 필요가 없음
다) 예제
-- 테이블.컬럼%type
create or replace procedure emp_info(p_empno in emp.empno%type)
is -- 변수 선언
v_empno emp.empno%type -- 테이블.컬럼%type
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
select empno, ename, sal into v_empno, v_ename, v_sal
from emp
where empno = p_empno;
-- dbms_output 패키지의 put_line 함수 호출
dbms_output.put_line('사번: ' || v_empno); -- pl/sql 출력문
blah blah
2) If 문
가) 형식
나) if문 에제
create or replace procedure dept_search(p_empno in number)
is
v_deptno number;
begin
select deptno into v_deptno from emp
where empno = p_empno;
blah blah
사. 커서(Cursor)
1) SQL의 작업 영역
2) 모든 SQL문은 연관된 각각의 커서를 소유함
3) 커서의 종류
가) 암시적 커서 : 모든 DML과 PL/SQL SELECT 문에 대해 선언됨
나) 명시적 커서 : 프로그래머에 의해 선언되며 이름이 있는 커서
4) 암시적 커서
가) 암시적인 커서 : 오라클이나 PL/SQL 실행 메커니즘에 의해 처리되는 SQL 문장이 처리되는 곳에 대한 익명의 주소값
나) 암시적 커서의 속성
SQL%ROWCOUNT : 해당 SQL 문에 영향을 받는 행의 수
SQL%FOUND : 해당 SQL 영향을 받는 행의 수가 1개 이상일 경우 TRUE
SQL%NOTFOUND : 해당 SQL 문에 영향을 받는 행의 수가 없을 경우 TRUE
SQL%ISOPEN : 항상 FALSE, 암시적 커서가 열려 있는지의 여부 검색
다) 암시적 커서는 SQL문이 실행되는 순간 자동으로 열림과 닫힘 실행
5) 커서의 사용 방법
가) 커서 열기(OPEN)
OPEN cursor_name;
나) 커서 패치(FETCH)
FETCH cursor_name INTO variable1, variable2;
현재 레코드를 OUTPUT 변수에 저장(한 라인씩 데이터를 읽음)
커서의 SELECT 문의 컬럼수와 OUTPUT 변수의 수와 데이터 타입이 동일해야 함
다) 커서 닫기(CLOSE)
CLOSE cursor_name;
사용을 마친 커서는 반드시 닫아 주어야 함
커서를 닫은 상태에서 FETCH를 할 수 없음
6) 커서 실습
-- in 입력매개변수(in 생략가능)
3;22 blah blah
자. 트리거
image
1) Trigger(방아쇠) : 데이터베이스에서의 연쇄적인 동작을 정의함
2) INSERT, UPDATE, DELETE 문이 실행될 때 묵시적으로 수행되는 PROCEDURE
3) Table에만 정의될 수 있음(View에는 사용할 수 없음)
4) Before Trigger : INSERT, UPDATE, DELETE 문이 실행되기 전에 실행
5) After Trigger : INSERT, UPDATE, DELETE 문이 실행된 후 실행
6) FOR EACH ROW : 행 트리거
가) 컬럼의 각각의 행이 데이터 행 변화가 생길 때마다 실행됨
나) 문장 트리거 : 1회만 실행됨
7) 트리거 예제
create or replace
blah blah 010107 start