020920

SQL

020920

유순이 2020. 9. 2. 10:58

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
;