[5일차] DO IT 오라클 데이터베이스/p419 ~ 521 / PL-SQL, 커서, 프로시져

-- PL-SQL 선언 구문

--DECLARE
--
--[ 선언 ]
--
--BEGIN
--
--[ 실행 ]
--
--EXCEPTION
--
--[ 예외처리 ]
--
--END;

-- DECLARE / 선언 : 변수들을 정의

-- BEGIN / 실행 : 실제 실행될 PL/SQL 이 들어감 (필수!!)

-- EXCEPTION / 예외처리 : 예외 발생 구문을 적는 부분


set serveroutput on;
begin
 dbms_output.put_line('Hello, PL/SQL!');
end;
/

 

 

declare
v_empno number(4) := 7788;
v_ename varchar2(10);
begin
v_ename := 'SCOTT';
/*
 DBMS_OUTPUT.PUT_LINE('V_EMPNO : ' || V_EMPNO);
DBMS_OUTPUT.PUT_LINE('V_ENAME : ' || V_ENAME);
*/
end;
/


-- 변수이름 자료형 := 값 또는 값이 도출되는 여러 표현식;


declare 
       v_empno number(4) := 7788;
       v_ename varchar2(10);
begin
       v_ename := 'SCOTT';
       DBMS_OUTPUT.PUT_LINE('V_EMPNO : ' || V_EMPNO);
       DBMS_OUTPUT.PUT_LINE('V_ENAME : ' || V_EMAME);
end;
/

-- 변수이름 constraint 자료형 := 값 또는 값을 도출하는 여러 표현식;

 

 

declare 
        v_tax constant number(1) := 3;
begin
        dbms_output.put_line('v_tex : '  || v_tax);
end;
/



declare
      v_deptno number(2) default 10;
begin
     dbms_output.put_line('v_deptno : '  ||  v_deptno);
end;
/

-- 변수이름 자료형 NOT NULL  := 또는 DEFAULT 값 또는 값을 도출하는 여러 표현식;

 

 

declare 
    v_deptno number(2) not null default 10;
begin
    dbms_output.put_line('v_deptno : ' || v_deptno);
end;
/

 

 

 

 

/*
    if 조건식 then
    수행할 명령어;
    elsif 조건식
    수행할 명령어;
    elsif
    수행할 명령어
    ...
    else
    수행할 명령어
    end if;
*/

 

 

declare 
    v_score number := 87;
begin
    if v_score >= 90 then
    dbms_output.put_line('A학점');
    elsif v_score >= 80 then
    dbms_output.put_line('B학점');
    elsif v_score >= 70 then
    dbms_output.put_line('C학점');
    elsif v_score >= 60 then
    dbms_output.put_line('D학점');
    else
    dbms_output.put_line('F학점');
    end if;
end;
/


/*
    case 비교 기준
    when 값1 then
    수행할 명령어;
    when 값2 then
    수행할 명령어;
    ...
    else
    수행할 명령어
    end case;
*/

 

 

 

declare
    v_score number := 87;
begin
    case trunc(v_score/10)
    when 10 then dbms_output.put_line('A학점');
    when 9 then dbms_output.put_line('A학점');
    when 8 then dbms_output.put_line('B학점');
    when 7 then dbms_output.put_line('C학점');
    when 6 then dbms_output.put_line('D학점');
    else dbms_output.put_line('F학점');
    end case;
end;
/



declare
    v_score number := 87;
begin
    case
        when v_score >= 90 then dbms_output.put_line('A학점');
        when v_score >= 80 then dbms_output.put_line('B학점');
        when v_score >= 70 then dbms_output.put_line('C학점');
        when v_score >= 60 then dbms_output.put_line('D학점');
        else dbms_output.put_line('F학점');
    end case;
end;
/


/*
     loop
     반복 수행 작업;
     end loop;
*/

 

 

 

declare
    v_num number := 0;
begin
    loop
        dbms_output.put_line('현재 v_num ' || v_num);
        v_num := v_num + 1;
        exit when v_num > 4;
    end loop;
end;
/


/*
    while 조건식 loop
    반복 수행 작업;
    end loop;
*/

 

 

 

declare 
    v_num number := 0;
begin
    while v_num < 4 loop
    dbms_output.put_line('현재 v_num' || v_num);
    v_num := v_num + 1;
    end loop;
end;
/



/*
    for i in 시작값 .. 종료 값 loop
    반복 수행 작업;
    end loop;
*/

 

 

 

begin
    for i in 0..4 loop
    dbms_output.put_line('현재 i의 값 : ' || i);
    end loop;
end;
/


/*
    for i in reverse 시작값 .. 종료 값 loop
    반복 수행 작업;
    end loop;
*/


begin
    for i in 0..4 loop
    continue when mod(i, 2) = 1;
    dbms_output.put_line('현재 i의 값 : '  || i );
    end loop;
end;
/

 

 

 

 

 

 

 

DECLARE
   TYPE ITAB_EX IS TABLE OF VARCHAR2(20)
      INDEX BY PLS_INTEGER;

   text_arr ITAB_EX;

BEGIN
   text_arr(1) := '1st data';
   text_arr(2) := '2nd data';
   text_arr(3) := '3rd data';
   text_arr(4) := '4th data';

   DBMS_OUTPUT.PUT_LINE('text_arr(1) : ' || text_arr(1));
   DBMS_OUTPUT.PUT_LINE('text_arr(2) : ' || text_arr(2));
   DBMS_OUTPUT.PUT_LINE('text_arr(3) : ' || text_arr(3));
   DBMS_OUTPUT.PUT_LINE('text_arr(4) : ' || text_arr(4));
END;
/

 

 

 

 

 

DECLARE
   TYPE ITAB_DEPT IS TABLE OF DEPT%ROWTYPE
      INDEX BY PLS_INTEGER;

   dept_arr ITAB_DEPT;
   idx PLS_INTEGER := 0;

BEGIN
   FOR i IN(SELECT * FROM DEPT) LOOP
      idx := idx + 1;
      dept_arr(idx).deptno := i.DEPTNO;
      dept_arr(idx).dname := i.DNAME;
      dept_arr(idx).loc := i.LOC;

      DBMS_OUTPUT.PUT_LINE(
      dept_arr(idx).deptno || ' : ' ||
      dept_arr(idx).dname || ' : ' ||
      dept_arr(idx).loc);
   END LOOP;
END;
/

 

 

 

 

 

--저장 서브 프로그램

/*
    create [or replace] procedure 프로시저이름
    is | as
    선언부
    begin
    실행부
    exception
    예외 처리부
    end[프로시저 이름]
*/

 

 

-- excute 프로시저 이름;

 

CREATE OR REPLACE PROCEDURE pro_noparam
IS
   V_EMPNO NUMBER(4) := 7788;
   V_ENAME VARCHAR2(10);
BEGIN
   V_ENAME := 'SCOTT';
   DBMS_OUTPUT.PUT_LINE('V_EMPNO : ' || V_EMPNO);
   DBMS_OUTPUT.PUT_LINE('V_ENAME : ' || V_ENAME);
END;
/

 

 

SET SERVEROUTPUT ON;

EXECUTE pro_noparam;

 

 

BEGIN
   pro_noparam;
END;
/

 

 

 

DROP PROCEDURE PRO_NOPARAM;


CREATE OR REPLACE PROCEDURE pro_param_in
(
   param1 IN NUMBER,
   param2 NUMBER,
   param3 NUMBER := 3,
   param4 NUMBER DEFAULT 4
)
IS

BEGIN
   DBMS_OUTPUT.PUT_LINE('param1 : ' || param1);
   DBMS_OUTPUT.PUT_LINE('param2 : ' || param2);
   DBMS_OUTPUT.PUT_LINE('param3 : ' || param3);
   DBMS_OUTPUT.PUT_LINE('param4 : ' || param4);
END;
/

 

 

DECLARE
   no NUMBER;
BEGIN
   no := 5;
   pro_param_inout(no);
   DBMS_OUTPUT.PUT_LINE('no : ' || no);
END;
/

 

 

 

CREATE OR REPLACE FUNCTION func_aftertax(
   sal IN NUMBER
)
RETURN NUMBER
IS
   tax NUMBER := 0.05;
BEGIN
   RETURN (ROUND(sal - (sal * tax)));
END func_aftertax;
/