05.28(PL/SQL)

PL/SQL(Procedural Language Extension to SQL)

기존 SQL문에 절차적인 언어 확장판이라 할 수 있음
변수정의, 조건처리, 반복처리의 기능이 추가됨
procedure, function, job, scheduler 등의 객체에서 pl/sql문법을 사용

 

1. 익명블럭(anonymous block)
1회용 pl/sql. 객체로 저장된 형태가 아님

 

declare(선택)
        --선언부 : 변수/상수를 선언
begin
        --실행부 : 조건문, 반복문, 일반 sql문 사용
exception(선택)
        --예외처리부 : (자바 catch절 생각하면 됨) 예외발생시 처리구문 작성
end;
/  --이게 있어야 끝났다고 인지

--사원명으로 사번, 사원명, 직급명, 조회하기(익명블럭)

--콘솔 출력 설정(매 세션마다 설정할 것) 
set serveroutput on;

declare
    id employee.emp_id%type;
    name employee.emp_name%type;
    job_name job.job_name%type;
begin
    select emp_id, emp_name, job_name
    into id, name, job_name
    from employee join job using(job_code)
    where emp_name = '&사원명';
    
    --출력
    dbms_output.put_line('사번 : '||id);
    dbms_output.put_line('사원명 : '||name);
    dbms_output.put_line('직급명 : '||job_name);

end;
/

2. procedure 

일련의 작업절차가 저장된 서브프로그램 객체
단독실행되거나, 다른 프로시져에 의해서 실행할 수 있는 서브 프로그램 
반환값X 
매개인자를 사용할 수 있음 

create or replace procedure proc_test
    --매개변수 선언
    --매개변수가 없는 경우에는 소괄호 생략
is
    --지역변수 선언
begin

    delete from emp_copy;
    --트랜젝션 처리
    commit;
end;
/

--위의 결과에서 not null인제약조건 찾아 데이터 추가해보고 프로시져 실행해보기
insert into emp_copy(emp_id, emp_name, emp_no, job_code, sal_level)
values(111, '홍길동', '900101-1234567', 'J1', 'S1');

procedure호출 2가지 방법
① 익명블럭에서 프로시져 호출(일반 sql에서는 procedure 사용 못함??)
execute 명령어 없이 호출할 것!

begin 
    proc_test; 
end; 
/ 

② 단독실행 execute 키워드 통한 호출

exec proc_test; 
execute proc_test;

 

매개변수(IN OUT INOUT)가 있는 프로시져
① IN 프로시져 데이터 전달
② OUT 수행된 결과를 받아갈때 사용하는 매개변수
③ INOUT 두역할을 한 매개변수에서 처리

--#선언부
create or replace procedure proc_search_by_emp_id(
    p_emp_id in employee.emp_id%type,  --값이 넘어옴
    p_emp_name out employee.emp_name%type,  --주소값(공간)이 넘어옴 ->여기에 조회된 결과를 담아달라는 이야기 (call by reference)
    p_phone out employee.phone%type
)
is
     --위에 필요한 애들 다 있어서 지역변수 선언 x
begin
    --조회하고 조회한걸 전달받을 매개변수에 담는게 프로시져 끝
    select emp_name, phone
    into p_emp_name, p_phone
    from employee
    where emp_id = p_emp_id;
end;
/

--#호출부
--매개변수 나열은 , 하나하나 컴파일 해야할때는 ;
--익명블럭에서 호출
declare
    v_emp_name employee.emp_name%type;
    v_phone employee.phone%type;
begin
    --호출시 값 하나 넘어가고 조회된 결과가 v_emp_name, v_phone에 값이 담겨짐
    proc_search_by_emp_id('&사번', v_emp_name, v_phone);
    
    dbms_output.put_line('이름 : ' || v_emp_name);
    dbms_output.put_line('전화번호 : ' || v_phone);
end;
/

 

3. function 

반환값이 반드시 존재하는 프로시져 객체(function은 프로시져의 변형된 형태) 
매개인자를 사용할 수 있음

create function my_func (
    --자료형 크기 지정X 오류남
    p_str varchar2
)
--자료형 크기 지정X 오류남 & 리턴 타입 꼭 지정
return varchar2
is
    --리턴할 변수 생성
    result varchar2(32767);
    --일반 varchar2이지만, 자료형의 크기는 max 32767바이트
begin
    result := 'd' || p_str || 'b';
    
    return result;
--exception
--    return;
end;
/

--1. 일반sql문 호출(빌트인 함수 쓰듯이 호출 가능)
select my_func(emp_name)
from employee;

--2. 다른 프로시져객체, 함수객체에서 호출사용가능(ex. 익명블럭)
begin
    dbms_output.put_line(my_func('&이름'));
end;
/

--DataDictionary에서 확인
select *
from user_procedures
where object_type = 'FUNCTION';