[days13] PL/SQL, 익명 프로시저

PL/SQL

1. Procedural Language + 확장된 SQL

2. 비절차적인 SQL 언어를 절차적으로 사용할 수 있도록 한다.

 

int deptno = select max(deptno)+10 from dept;
for/while/if 제어문 {
  insert into dept values (deptno);
}

이런 절차적인 언어 문법을 사용할 수 있도록 

3. 블록 구조로 된 언어

4. PL/SQL의 구조 = 3가지 블럭 구조 = 선언형식

    ㄱ. 선언 블록
    ㄴ. 실행 블록
    ㄷ. 예외 처리 블록

 

[declare] 
begin 
[exception]
end;

 

declare
begin 
    insert
    update
    select
    insert
    delete --필요에 의해 DML 여러번 사용 가능, 식별자는 테이블 또는 컬럼명과 같을 수 없음 
exception
end; --블럭 내에는 CREATEST, LEAST, DECODE, 그룹함수를 사용할 수 없음 

 

5. PL/SQL 종류

    ㄱ.익명 프로시저(anonymous procedure)
        anonymous PL/SQL은 DECLARE ...로 시작되며, 사용자가 반복적으로 실행하려는 SQL문을 필요할 때마다

        작성하여 실행하는 방법, 데이터베이스에 그 정보가 저장되지 않음 
    ㄴ.저장 프로시저(stored procedure) *** 성능때문에 엄청 중요
    ㄷ.저장 함수(stored function)
    ㄹ. 패키지(package) 
    ㅁ. 트리거(trigger)
    ㅂ. object type  

 

6. 익명 프로시저

  원래 declare 생략 가능하지만 익명 프로시저에서는 있어야 함

100, 홍길동 출력하기

declare  
    vnum number  ; --세미콜론
    vname varchar2(20);
begin
    --실행블록
vnum := 100; --변수값을 저장하기 위해서는 := 를 사용한다.
vname := '홍길동';
--System.out.println( vnum+","+vname);
dbms_output.put_line(vnum || ',' || vname);
--exception
end;

--PL/SQL 프로시저가 성공적으로 완료되었습니다.
--보기>DBMS출력 > +버튼 scott연결

deptno를 입력받아 그에 맞는 ename, sal, deptno 출력하기

declare 

         vename varchar2(10);
        vsal number(7,2);
        --,vdeptno number(2); --실제 emp 테이블의 deptno 컬럼 자료형 읽어와 반영시키겠다고 코딩
        --%type 형변수 선언
        vdeptno emp.deptno%type; --실제 emp 테이블의 자료형 확인 필요없이 emp.deptno%type 쓰면 됨
        --테이블 구조가 자주 변경될 때 사용하면 유용 dept테이블의 자료형과 똑같냐
begin 
    select ename, sal, deptno
        into vename, vsal, vdeptno
    from emp
    where empno = :pempno; -- :yyyy 는 입력받는다는 뜻
    
    dbms_output.put_line(vename||','||vsal||','||vdeptno);
--exception
end;

10번 부서에서 급여 가장 많이 받는 사원의 이름, sal 출력하기

declare 
    vmax_sal emp.sal%type;
    vempno emp.empno%type;
    vename emp.ename%type;
begin
    select max(sal) into vmax_sal --상관서브쿼리 대신에 vmax_sal로 받겠다
    from emp
    where deptno=10;
    
    select empno, ename into  vempno, vename
    from emp
    where deptno =10 and sal = vmax_sal;
    
    dbms_output.put_line(vmax_sal||','|| vempno||','|| vename);
--exception
end;

7. :pxxx -> 한 행의 정보 모든 컬럼을 조회(출력)

empno를 입력받아 모든 정보 출력

declare 
    vEMPNO             NUMBER(4);    
    vENAME             VARCHAR2(10); 
    vJOB               VARCHAR2(9);  
    vMGR               NUMBER(4);    
    vHIREDATE          DATE;         
    vSAL               NUMBER(7,2);  
    vCOMM              NUMBER(7,2);  
    vDEPTNO            NUMBER(2);   
begin 
    select empno, ename, job, mgr, hiredate, sal, comm, deptno
        into vempno, vename, vjob, vmgr, vhiredate, vsal, vcomm, vdeptno
    from emp
    where empno = :pempno;
    
    dbms_output.put_line
    (vempno||','||vename||','||vjob||','||vmgr||','||vhiredate||','||vsal||','||vcomm||','||vdeptno);
--exception
end;

이럴 필요 없이 %ROWTYPE형 변수  사용

declare 
    vrow emp%rowtype; --수정
begin 
    select empno, ename, job, mgr, hiredate, sal, comm, deptno
        into vrow.empno, vrow.ename, vrow.job, vrow.mgr, vrow.hiredate, vrow.sal, vrow.comm, vrow.deptno --수정
    from emp
    where empno = :pempno;
    
    dbms_output.put_line(vrow.ename); --수정
--exception
end;

--여기서 부분만 저장하고 싶다해도 rowtype 하고 into에 필요한 것만 저장하면 됨
--한두개 저장할거면 rowtype 저장할 필요는 없고

--PL/SQL 에서는 여러개의 실행결과(행)을 처리하기 위해서는 커서(cursor)를 사용해야 된다.

 

문제)  :pn 을 입력받아서 1~pn 까지의 홀수의 합 출력하는 익명프로시저 작성        
     예) 1+3+5+7+9=25

declare
        vi number(3) := 1;
        vii number(3);
        vsum number := 0;
begin
            while vi <= :vii
             loop
                if mod(:vii, 2)=1 and vi=:vii then   dbms_output.put(vi);  
                elsif mod(:vii, 2)=0 and vi=:vii-1 then   dbms_output.put(vi); 
                 else dbms_output.put(vi||'+'); 
                 end if;
               vsum := vsum + vi;
               vi := vi+2;
             end loop;
             <<for_out>>
               dbms_output.put_line('='||vsum);
end;