SQL PLSQL Procedure

20. PL SQL : SQL언어에 절차적프로그래밍 언어를 가미해 만든것. SQL문장에 
  변수정의, 조건처리(if), 반복(loop, while, for)등을 지원하여 sql의 단점보완
 
 -기능    변수선언, 비교처리, 반복처리
 -구조    declare  선언
        begin    실행
        exception 예외
        end      종료
 
 
 -변수의 종류
 --스칼라:기존sql의 자료형과 유사
    ex) ename varchar2(10);
 --레퍼런스: 선언된 다른변수 혹은 데이터베이스 컬럼에 맞추어 선언(%type). 
    또한 row(행)단위로 참조할수있는 %rowtype
    ex) vempno emp.empno%type;
    ex) vemp emp%rowtype;
 
 -select문 사용
 --into절 필요 개수와 데이터타입 길이 일치해야함
 
 -레코드타입: 프로그램언어의 구조체와 유사. 필드의 집합을 
 -하나의 논리적 단위로 처리할수있게 해주므로 테이블의 row를 읽어올때 편리
  ex) TYPE emp_rec_type IS RECORD (v_empno emp.empno%type,
                                   v_ename emp.ename%type,
                                   v_sal emp.sal%type,
                                   v_deptno emp.deptno%type);
     v_emp emp_rec_type;
 
 --is table of :배열과 유사, 동적으로 자유롭게 증가 가능
  ex) type emp_table_ename_type is TABLE OF emp.ename%type
         INDEX BY PLS_INTEGER;
      vemp_ename emp_table_ename_type;
 
 -if 사용
 -- if ~~~ then ~~~ 
    elsif ~~~ then ~~~
    else ~~~
    end if;
 
 -loop 사용
 -- loop
      출력문
    n:=n+1;
    if n>=5 then exit;
    end loop;
 
 -for loop 사용
 -- for n in 1..5 loop
    end loop;
 
 -while loop는 안배웠나;
 
21. 저장 프로시저, 함수, 커서
 -프로시져: 반복적인 작업을 하나로 묶어놓은것을 의미함. 다시 입력할 필요 없이 간단히 호출하여 실행 결과를 얻을수있음. 성능향상, 호환성문제 해결
 -생성     create or replace procedure proc01
     is
       변수선언
    begin
       select문
       +필요한 기능
    end;
 
-사용 in 데이터 전달받을때
      out 수행결과 받을때
      inout 두가지 목적 모두
 
-실행: exec 프로시저이름; /execute 프로시저이름;
 
-삭제 : drop procedure 프로시저이름
 
 
 
 
 
 
 
 

 

SET SERVEROUTPUT ON;
   
-- plsql의 구조
declare
    v_sum number:=10;
    v_name varchar(40):='hong';
begin
    dbms_output.put_line(v_sum);
    dbms_output.put_line(v_name);
end;
 
--plsql의 구조2
declare
    vempno number(4);
    vempname varchar2(10):='hong';
begin
    vempno:=7788;
    dbms_output.put_line('no'||vempno);
    dbms_output.put_line('name'||vempname);
end;
 
 
--plsql의 구조3  select에 into 사용
declare
    vempno emp.empno%type;
    vname emp.ename%type;
begin
    select empno, ename into vempno, vname
    from emp
    where empno = 7934;
    dbms_output.put_line('empno : '||vempno);
    dbms_output.put_line('empname : '||vname);
end;
 
--프로시져:반복적인 작업을 하나로 묶어놓은것을 의미함
--is 와 begin 사이에 변수
create procedure proc01
is
    v_sum number:= 0;
    begin
        select avg(sal) into v_sum
        from emp;
        dbms_output.put_line('sum:'||v_sum);
    end;
    
--실행
exec proc01();
 
--외부에서 사번을 받아, 사번에 대한 정보 출력
--if / else / end if; 
create or replace procedure proc02(a number)
is
    v_sal number:=0;
    begin
        select avg(sal) into v_sal
        from emp
        where deptno= a;
        if v_sal is null then
            dbms_output.put_line('입력하신 번호의 사원이 없습니다');
        else
        dbms_output.put_line('평균임금은 '||v_sal);
        end if;
    end;
    
exec proc02(7788);
 
--레퍼런스 사원번호, 사원명, 입사일, 급여
create or replace procedure proc03(eno emp.empno%type)
is
    v_empno emp.empno%type;
    v_ename emp.ename%type;
    v_hiredate emp.hiredate%type;
    v_sal emp.sal%type;
    
    begin
        select empno, ename, hiredate, sal into v_empno, v_ename, v_hiredate, v_sal
        from emp
        where empno = eno;
        
        if v_ename is null then
        dbms_output.put_line('no data');
        else
        dbms_output.put_line('사원번호 : '||v_empno);
        dbms_output.put_line('이름 : '||v_ename);
        dbms_output.put_line('입사일 : '||v_hiredate);
        dbms_output.put_line('급여 : '||v_sal);
        end if;
    end;
    
    --exception
create or replace procedure proc0003(eno emp.empno%type)
is
    v_empno emp.empno%type;
    v_ename emp.ename%type;
    v_hiredate emp.hiredate%type;
    v_sal emp.sal%type;
    
    begin
        select empno, ename, hiredate, sal into v_empno, v_ename, v_hiredate, v_sal
        from emp
        where empno = eno;
        
        dbms_output.put_line('사원번호 : '||v_empno);
        dbms_output.put_line('이름 : '||v_ename);
        dbms_output.put_line('입사일 : '||v_hiredate);
        dbms_output.put_line('급여 : '||v_sal);
        
        exception
            WHEN NO_DATA_FOUND THEN
            dbms_output.put_line('데이터 없음');
    end;
 
exec proc03(7788);
exec proc0003(102); 
 
--exception
create or replace procedure proc4(a number, b number)
is
    begin
        dbms_output.put_line(a/b);    
    
    exception
        when zero_divide then
        dbms_output.put_line('0으로 나눌수없습니다');
        
    end;
 
exec proc4(4, 0);
 
create or replace procedure proc5(a number)
is 
    emp_rec emp%rowtype;
    begin
        select * into emp_rec
        from emp
        where empno = a;
        
        dbms_output.put_line(emp_rec.empno);
        dbms_output.put_line(emp_rec.ename);
        dbms_output.put_line(emp_rec.sal);
        
        exception
            when NO_DATA_FOUND then
            dbms_output.put_line('no dataaaa');
    end;
    
exec proc5(7844);
exec proc5(102);
 
--레코드타입1
create or replace PROCEDURE proc6(a number)
is 
    TYPE emp_rec_type IS RECORD(empno emp.empno%type,
                                sal emp.sal%type,
                                hiredate emp.hiredate%type);
    v_emp_rec emp_rec_type;
    begin
        select empno, sal, hiredate into v_emp_rec
        from emp 
        where empno = a;
        
        dbms_output.put_line(v_emp_rec.empno);
        dbms_output.put_line(v_emp_rec.sal);
        dbms_output.put_line(v_emp_rec.hiredate);
 
    end;
    
exec proc6(7844);
 
--레코드타입2
create or replace procedure proc7(a number)
is 
    TYPE emp_rec_type IS RECORD(empno emp.empno%type,
                                ename emp.ename%type,
                                job emp.job%type,
                                sal emp.sal%type,
                                deptno emp.deptno%type);
    vemp emp_rec_type;
    begin
        select empno, ename, job, sal, deptno into vemp
        from emp
        where empno = a;
        dbms_output.put_line(vemp.empno);
        dbms_output.put_line(vemp.ename);
        dbms_output.put_line(vemp.job);
        dbms_output.put_line(vemp.sal);
        dbms_output.put_line(vemp.deptno);     
        
        exception
            when no_data_found then
            dbms_output.put_line('no data');
    end;
 
exec proc7(7844);    
exec proc7(784);    
 
--2000이상row / 2000~4000middle / 4000이상high
--if... deptno 10=a 20=b 30=c 40=d
--사원번호, 이름, 급여, 급여결과, 부서코드 부서명
create or replace procedure proc08(a number)
is
    TYPE emp_rec_type IS RECORD (v_empno emp.empno%type,
                                v_ename emp.ename%type,
                                v_sal emp.sal%type,
                                v_deptno emp.deptno%type);
    v_emp emp_rec_type; 
    v_salresult varchar2(20);
    v_dname varchar2(20);
    begin
        select empno, ename, sal, deptno into v_emp
        from emp
        where empno = a;
        
        if (v_emp.v_sal < 2000) then v_salresult := 'low';
        elsif (v_emp.v_sal<4000) then v_salresult := 'mid';
        else v_salresult := 'high';
        end if;
        
        if v_emp.v_deptno = 10 then v_dname:='a';
        elsif v_emp.v_deptno =20 then v_dname := 'b';
        elsif v_emp.v_deptno =30 then v_dname := 'c';
        else v_dname := 'd';
        end if;
        
        dbms_output.put_line(v_emp.v_empno||'/'||v_emp.v_ename||'/'||v_emp.v_sal||'/'||v_salresult||'/'||v_emp.v_deptno||'/'||v_dname);
 
        exception
            when no_data_found then
            dbms_output.put_line('no data');
    end;
 
exec proc08(7844);
 
--루프문
declare
    n number(4):=0;
begin
    loop
        dbms_output.put_line(n);
        n:=n+1;
        if n>=5 then
            exit;
        end if;
    end loop;
end;
 
--13579 출력
declare
    v_number number(4):= 1;
begin
    loop
        dbms_output.put_line(v_number);
        v_number:=v_number+2;
        if v_number>=10 then 
            exit;
        end if;
    end loop;
end;
 
--for문 : for i in 1..5 loop
--declare
begin
    for i in 1..5 loop
        dbms_output.put_line(i);
    end loop;
end;
 
--procedure + 루프
create or replace procedure getDept(dcode emp.deptno%type)
is
    type emp_table_ename_type is TABLE OF emp.ename%type
    INDEX BY PLS_INTEGER;
        vemp_ename emp_table_ename_type;
    begin
        select ename bulk collect into vemp_ename
        from emp
        where deptno = dcode;
   
    for i in vemp_ename.first .. vemp_ename.last loop
        dbms_output.put_line(vemp_ename(i));
        end loop;
    end;
    
    exec getDept(10);
    
--20번부서 사원의 모든 자료 출력
create or replace procedure getdept001(dcode emp.deptno%type)
is
    Type emp_table_type is table of emp%rowtype 
        index by pls_integer;
    v_emp emp_table_type;
    begin
        select * bulk collect into v_emp
        from emp
        where deptno=dcode;
    
    for i in v_emp.first.. v_emp.last loop
        dbms_output.put_line(v_emp(i).empno);
        dbms_output.put_line(v_emp(i).ename);
        dbms_output.put_line(v_emp(i).sal);
        dbms_output.put_line(v_emp(i).deptno);
        dbms_output.put_line(v_emp(i).hiredate);
    end loop;
    exception
        when no_data_found then
        dbms_output.put_line('no data');  
    end;