-
PL/SQL p.101 시작
제어문
if 조건식1
then 구문1 (다수 가능)
elsif 조건2 (스펠링 확인,,, 저게 맞다는데?)
then 구문2 (다수 가능)
else
구문3 (다수 가능)
end if;
create or replace procedure dept_search(p_empno IN emp.empno%type) is --decalre v_deptno emp.deptno%type; begin dbms_output.enable; --메시지 버퍼 활성화 select deptno into v_deptno from emp where empno = p_empno ; if v_deptno = 10 then dbms_output.put_line('ACCOUNTING 부서 사원입니다.'); elsif v_deptno = 20 then dbms_output.put_line('RESEARCH 부서 사원입니다.'); elsif v_deptno = 30 then dbms_output.put_line('SALES 부서 사원입니다.'); else dbms_output.put_line('OPERATIONS 부서 사원입니다.'); end if; --여긴 디폴트값인거 같아 end; / exec dept_search(7521); --명령문 상황에서 inline 주석 사용할 수 없다네;;loop문
loop - while / do ~ while
loop
~
exit when ~
end loop;
for loop - for
while loop
procedure
특정 작업을 수행하는 이름이 있는 PL/SQL 블록
create or rreplace procedure procedure_name in argument out argument in out argument is [변수 선언] begin [PL/SQL 블록] [exception] end;Procedure Example
--프로시저 예제 -- 약간 main이라고 생각하면 되려나? create or replace procedure hello is msg varchar2(100) := 'Hello Oracle!' ; begin dbms_output.put_line(msg) ; end; / exec hello; -- 프로시저 실행까지Procedure check
select object_name, object_type from user_objects where object_type = 'PROCEDURE' ;매개변수
Procedure에 value를 input할 때에 사용
create or replace procedure prc_arg (arg_ename emp.ename%type) is p_empno emp.empno%type; begin -- 사원 이름으로 사원번호 조회해서 변수 p_empno에 input select empno into p_empno from emp where ename = arg_ename ; --출력 dbms_output.put_line('사원 번호는 '||p_empno||'번 입니다.') ; end ;매개변수 in / out / in out
in : procedure 실행 시 넘겨줄 매개변수
out : procedure에게 반환받을 매개변수
in out : procedur에게 값도 전달하고 반환도 받고
--매개변수 예제 create or replace procedure prc_arg_io (arg_empno in emp.empno%type, arg_ename out emp.ename%type, arg_sal out emp.sal%type, arg_job out emp.job%type) -- procedure 안의 변수들 선언 is begin --사원번호를 조회해서 각 매개변수 값 대입 select ename, sal, job into arg_ename, arg_sal, arg_job from emp where empno = arg_empno ; end ; / --바인드 변수를 선언해야하나봐 아마, 실행시킬 때에 사원번호만 입력하면 안되니까,,, --out매개변수를 담아오기위해 실행부분에 선언하는 변수. variable var_ename varchar2(15); variable var_sal number; variable var_job varchar2(9); exec prc_arg_io(7782, :var_ename, :var_sal, :var_job); -- 여기선 ';'가 내려오면 안돼!!!! ****** print var_ename; print var_sal; print var_job;out 매개변수를 담아오기위해 실행부분에 선언하는 변수 = 바인드 변수
함수
주로 값을 계산하고 결과를 반환하기 위해 많이 사용
create or replace function fnc_annsal (arg_empno emp.empno%type) return number --필수 is p_sal emp.sal%type; -- 변수 선언 begin select sal into p_sal from emp where empno = arg_empno ; --리턴 return (p_sal*12) ; end; / --바인드 변수 선언 variable var_annsal number; exec :var_annsal := fnc_annsal(7782); print var_annsal;create or replace function fnc_name [arguement] return datatype --반환되는 데이터 타입 is [변수 선언 부분] begin [PL/SQL block] -- 리턴문이 꼭 존재해야 한다. return 변수; end;커서
select 문의 조회 결과가 1행 이상일 떄, 커서를 통해 처리
%rowtype을 이용해 한 행단위를 담을 수 있는 변수를 선언하고,
커서변수에 다중행의 조회결과를 담는다.
이를 반복문을 통해 한 행 씩 순회하며, 데이터를 처리
declare cursor cursor_name is statement - 커서선언 begin for record_name in cursor_name loop statement end loop; end ;--커서 예제 create or replace procedure prc_cursor is p_row dept%rowtype; cursor c1 is select * from dept; --변수, 커서 선언 begin dbms_output.put_line('부서번호/부서명/지역'); dbms_output.put_line('----------------'); --반복문 for p_row in c1 loop exit when c1%notfound; dbms_output.put_line(p_row.deptno||'/'||p_row.dname||'/'||p_row.loc); end loop; end; / exec prc_cursor;패키지
procedure, function을 묶어서 효율적으로 관리하기 위함
--패키지 예제 create or replace package pkg is function fnc_annsal(arg_empno emp.empno%type) return number; --함수에는 리턴값 필수 procedure prc_arg(arg_ename emp.ename%type); end; / create or replace package body pkg is --함수 정의 function fnc_annsal(arg_empno emp.empno%type) return number is p_sal number(7, 2); begin --사원이름으로 사원번호 조회 후 변수 p_ sal에 대입 select sal into p_sal from emp where empno = arg_empno; return (p_sal*12); end; --프로시져 정의 procedure prc_arg(arg_ename emp.ename%type) is p_empno emp.empno%type; begin --사원이름으로 사원번호 조회 후, p_empno에 대입 select empno into p_empno from emp where ename = arg_ename ; --출력 dbms_output.put_line('사원 번호는 '||p_empno||'입니다.'); end; end; / variable var_annsal number; exec :var_annsal := pkg.fnc_annsal(7782); print var_annsal; exec pkg.prc_arg('KING');트리거
지정한 조건을 만족하면 특정 이벤트를 발생시키는 데이터베이스 객체
insert, update, delete문이 테이블에 대해 행해질 때 암묵적으로 수행되는 procedure
테이블의 변화에 영향을 받아 동작하지만 별도의 데이터베이스의 저장된다.
뷰가 아닌 테이블에 대해서만 정의
create or replace triggeer trg_name [ before | after ] [ insert | update | delete ] on table_name [ for each each ] begin [PL/SQL Block] end ;TRIGGER.insert
create or replace trigger trg_insert after insert on emp01 for each row begin insert into emp02 values (:new.empno, :new.ename, :new.job); end; / insert into emp01 values (1, 'green', 'carpenter') ; insert into emp01 values (2, 'yellow', 'painter') ; insert into emp01 values (3, 'blue', 'programmer') ;:old
:new
생각
TRIGGER.update
create or replace trigger trg_update after update on emp01 for each row begin update emp02 set ename =:new.ename, job =:new.job where empno = :new.empno --primary key라 변경 불가인건가?? ; end; / update emp01 set ename = 'violet' where empno = 3 ;TRIGGER.delete
create or replace trigger trg_delete after delete on emp01 for each row begin delete from emp02 where empno =:old.empno ; end; / delete from emp01 where empno = 3 ;