create or replace package pack_cursor

  is

    procedure cursor_open;

    procedure cursor_fetch (a number);

    procedure cursor_close;

  end;

  / 


  create or replace package body pack_cursor

  is

    cursor emp_cursor is

      select rownum as no, e.* 

      from (select * from emp

            order by hiredate) e;


    emp_rec emp_cursor%rowtype;


    no number := 1;


    procedure cursor_open

    is

    begin

      if emp_cursor%isopen = false then 

        open emp_cursor;

      end if;

    end;


    procedure cursor_fetch (a number)

    is

    begin

      if emp_cursor%isopen = true then 

        for i in 3..3+4-1 loop

          fetch emp_cursor into emp_rec;

          p(emp_rec.empno||' '||emp_rec.hiredate);

          no := no+1;

        end loop;

      end if;

    end;


    procedure cursor_close

    is

    begin

      close emp_cursor;

    end;


  end;

  / 


  exec pack_cursor.cursor_open

  exec pack_cursor.cursor_fetch(2)


  exec pack_cursor.cursor_fetch(4)

  exec pack_cursor.cursor_fetch(2)


  exec pack_cursor.cursor_close

'Oracle > PL/SQL' 카테고리의 다른 글

15일차 # 4-7  (0) 2012.04.24
15일차 Chapter 4 - Packages II # 4-3  (0) 2012.04.24
15일차 # 3-14: USER_SOURCE data dictionary  (0) 2012.04.24
15일차 # 3-11  (0) 2012.04.24
15일차 # 3-10: PACKAGE BODY  (0) 2012.04.24

select distinct type

from user_source;


TYPE

------------

PROCEDURE

PACKAGE BODY

PACKAGE

FUNCTION


select name, line, text

from user_source

where type = 'PACKAGE'

union all

select name, line, text

from user_source

where type = 'PACKAGE BODY';


'Oracle > PL/SQL' 카테고리의 다른 글

15일차 Chapter 4 - Packages II # 4-3  (0) 2012.04.24
15일차 # 3-17  (0) 2012.04.24
15일차 # 3-11  (0) 2012.04.24
15일차 # 3-10: PACKAGE BODY  (0) 2012.04.24
15일차 Chapter 3 - Packages I # 3-8: PACKAGE SPECIFICATION  (0) 2012.04.24

* SQL*Plus 에서 패키지의 변수, 프로시저 사용


exec p(comm_pkg.std_comm)


exec comm_pkg.reset_comm(0.3)


exec p(comm_pkg.std_comm)


exec p(comm_pkg.validate(0.2))

==> 오류: ORA-06550: 줄 1, 열18:PLS-00302: 'VALIDATE' 구성 요소가 정의되어야 합니다

   validate 함수는 패키지 본문(바디)에 정의되어 있음 --> private



  * PL/SQL 블록에서 패키지의 변수, 프로시저 사용


declare

 v_comm comm_pkg.std_comm%type := comm_pkg.std_comm;

begin

 p('comm_pkg.std_comm : ' || comm_pkg.std_comm);

 p('v_comm : ' || v_comm);

 comm_pkg.std_comm := 0.15;

 p('comm_pkg.std_comm : ' || comm_pkg.std_comm);


 comm_pkg.reset_comm(0.12);

 p('comm_pkg.std_comm : ' || comm_pkg.std_comm);


 comm_pkg.reset_comm(0.5);

 p('comm_pkg.std_comm : ' || comm_pkg.std_comm);


exception

 when others then

   p('오류가 발생하였어요~');

   p('comm_pkg.std_comm : ' || comm_pkg.std_comm);

end;

/



-----------------------------------



  create or replace package pack_ja

  is 

    var1 number;                            -- public(global) variable

    procedure p1 (p_in number);                -- public subprogram

    procedure p2 (p_in number);                -- public subprogram

  end;

  /


  create or replace package body pack_ja

  is 

    var2 number;                            -- private variable


    function f1 (a number) return boolean      -- private subprogram

    is

    begin

      if a < 3000 then 

        return true;

      else  

        return false;

      end if;

    end;


    procedure p1 (p_in number)

    is

      var3 number;                          -- local variable

    begin

      if f1(p_in) then

        var2 := p_in + 1000;

      else 

        var2 := p_in + 500;  

      end if;


      p(var2);

    end;


    procedure p2 (p_in number)

    is

      var4 number;                          -- local variable

      procedure local_proc (a varchar2)        -- local subprogram

      is

      begin

        p(a);

      end;

    begin

      var2 := 1000;

      local_proc('Korea');

      local_proc('Seoul');

    end;


  end;

  /


  exec pack_ja.var1 := 1000;


  set serveroutput on

  exec pack_ja.p1(4000)

  exec pack_ja.p2(100)


  exec pack_ja.var2 := 1000;   --> private component를 사용하려 하면 에러가 발생한다.

CREATE OR REPLACE PACKAGE BODY comm_pkg IS


  FUNCTION validate(comm NUMBER) RETURN BOOLEAN 

  IS

    max_comm employees.commission_pct%type;

  BEGIN

    SELECT MAX(commission_pct) INTO max_comm

    FROM   employees;

    RETURN (comm BETWEEN 0.0 AND max_comm);

  END validate;


  PROCEDURE reset_comm (new_comm NUMBER)

  IS

  BEGIN

    IF validate(new_comm) THEN

      std_comm := new_comm; -- reset public var

    ELSE

      RAISE_APPLICATION_ERROR(-20210, 'Bad Commission');

    END IF;

  END reset_comm;


END comm_pkg;

/

CREATE OR REPLACE PACKAGE comm_pkg IS

  std_comm NUMBER := 0.10;  --initialized to 0.10

  PROCEDURE reset_comm(new_comm NUMBER);

END comm_pkg;

/


'Oracle > PL/SQL' 카테고리의 다른 글

15일차 # 3-11  (0) 2012.04.24
15일차 # 3-10: PACKAGE BODY  (0) 2012.04.24
15일차 # 2-15: USER_SOURCE data dictionary view  (0) 2012.04.24
15일차 # 2-12  (0) 2012.04.24
15일차 # 2-11  (0) 2012.04.24

desc user_source

col name format a20


select name, line, text

from user_source

where type = 'FUNCTION';



  * 유저가 생성한 함수의 개수 구하기


select count(distinct name)

from user_source

where type = 'FUNCTION';



   cf.) select count(name) --> distinct가 없는 경우

from user_source

where type = 'FUNCTION';



<Practice 2>

  a.

create or replace function get_job (p_empno emp.empno%type)

return varchar2

is

 v_ret emp.job%type;

begin

 select job into v_ret

 from emp

 where empno = p_empno;


 return v_ret;

end;

/

  b.

variable title varchar2(35)


exec :title := get_job(7788)

print title


select empno, ename, job

from emp

where empno = 7788;


'Oracle > PL/SQL' 카테고리의 다른 글

15일차 # 3-10: PACKAGE BODY  (0) 2012.04.24
15일차 Chapter 3 - Packages I # 3-8: PACKAGE SPECIFICATION  (0) 2012.04.24
15일차 # 2-12  (0) 2012.04.24
15일차 # 2-11  (0) 2012.04.24
15일차 Chapter 2 - Functions  (0) 2012.04.24

 - Functions called from a SELECT statement cannot contain DML statements

 - Functions called from an UPDATE or DELETE statement on a table T cannot query

     or contain DML on the same table T

 - Functions called from SQL statements cannot end transactions 

     (that is,cannot execute COMMIT or ROLLBACK operations)


# 2-11 : 이 페이지의 요구사항을 만족시키지 못하는 함수는 SQL 문장에 사용할 수 없다.


  create or replace function uf_sal_check (a number) return boolean

  is

  begin

    if a <= 3000 then

      return true;

    else

      return false;

    end if;

  end;

  /


  select empno, sal, uf_sal_check(sal)    --> ORA-06553: PLS-382: expression is of wrong type

  from emp;

==> boolean은 SQL data type이 아닙니다



  begin

     if uf_sal_check(a => &up_sal) then

       p('Good!');

     else

       p('Bad!');

     end if;

  end;

  /

'Oracle > PL/SQL' 카테고리의 다른 글

15일차 # 2-15: USER_SOURCE data dictionary view  (0) 2012.04.24
15일차 # 2-12  (0) 2012.04.24
15일차 Chapter 2 - Functions  (0) 2012.04.24
15일차 # 1-25: USER_SOURCE  (0) 2012.04.24
15일차 # 1-20: 프로시저에서 예외 처리  (0) 2012.04.24

# 함수 예제 1


create or replace function uf_first return clob

is

 v clob;

begin


 v := '

SQL> select * from emp;


     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO

---------- ---------- --------- ---------- -------- ---------- ---------- ----------

      7369 SMITH      CLERK           7902 80/12/17        800                    20

      7499 ALLEN      SALESMAN        7698 81/02/20       1600        300         30

      7521 WARD       SALESMAN        7698 81/02/22       1250        500         30

      7566 JONES      MANAGER         7839 81/04/02       2975                    20

      7654 MARTIN     SALESMAN        7698 81/09/28       1250       1400         30

      7698 BLAKE      MANAGER         7839 81/05/01       2850                    30

      7782 CLARK      MANAGER         7839 81/06/09       2450                    10

      7788 SCOTT      ANALYST         7566 82/12/09       3000                    20

      7839 KING       PRESIDENT            81/11/17       5000                    10

      7844 TURNER     SALESMAN        7698 81/09/08       1500          0         30

      7876 ADAMS      CLERK           7788 83/01/12       1100                    20

      7900 JAMES      CLERK           7698 81/12/03        950                    30

      7902 FORD       ANALYST         7566 81/12/03       3000                    20

      7934 MILLER     CLERK           7782 82/01/23       1300                    10


14 개의 행이 선택되었습니다.


SQL>


사랑해요, 여러분들.. ㅎㅎ';


return v;


end;

/



  * 함수 활용 1 - select-list에 사용 가능 (SQL 문)


set long 2000000

set heading off

col ret format a100


select uf_first as ret from dual;



  * 함수 활용 2 - exec를 이용(environment)


exec dbms_output.put_line(uf_first);



  * 함수 활용 3 - 바인드 변수 사용


var g_ret clob

exec :g_ret := uf_first

print g_ret


exec :g_ret := uf_first || 'aaa'

print g_ret



    - 만약에 uf_first가 프로시저였다면

exec uf_first(:g_ret)

exec :g_ret := :g_ret || 'aaa'



  * 함수 활용 4 - pl/sql 블럭내에서 사용


declare

 v clob;

begin

 v := uf_first;

 p(v);


-- 또는

--  p(uf_first);

end;

/




# 함수 예제 2


set heading on

  

select deptno, max(sal) 

from emp

group by deptno;

  

  ↓ ↓ ↓ 


create index emp_deptno_sal_idx 

on emp (deptno, sal);



create or replace function uf_dept_sal_max(p_deptno emp.deptno%type) return number

is 

 v_sal emp.sal%type;

begin

 select /*+ index_desc(emp emp_deptno_sal_idx) */

   sal into v_sal

 from emp

 where deptno = p_deptno

 and rownum = 1;


 return v_sal;

end;

/



select deptno, dname, loc, uf_dept_sal_max(deptno) as max_sal

from dept;




# 함수 예제 3


drop table t1 purge;

create table t1 as select * from emp where 1=2;



create or replace function uf_emp_row(a emp.empno%type)

return emp%rowtype

is 

 r emp%rowtype;

begin

 select * into r from emp where empno = a;

 return r;

end;

/



select * from t1;

선택된 레코드가 없습니다.



declare

 emp_rec emp%rowtype;

begin

 emp_rec := uf_emp_row(&sv_empno);

 insert into t1 values emp_rec;

end;

/



Enter value for sv_empno: 7900


select * from t1;


    EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO

---------- ---------- --------- ---------- -------- ---------- ---------- ----------

     7900 JAMES      CLERK           7698 81/12/03        950                    30




# 함수 예제 4 : 도량형 변환


create or replace function uf_pyoung_2_squre_meter(a number) return number

is

begin

 return a * 3.305785;

end;

/



select level as pyoung, uf_pyoung_2_squre_meter(level) as squre_meter

from dual

connect by level <= 100;




# 문제: 급여가 2000 미만이면 Low, 2000 이상 4000 미만이면 Mid, 4000 이상이면 High를 반환하는

함수를 작성하고 emp 테이블의 각 사원에 적용하세요.



create or replace function uf_grade(a number) 

 return varchar2

is

 v_grade varchar2(4);

begin

 v_grade := case when a < 2000 then 'Low'

                 when a < 4000 then 'Mid'

                 else               'High' 

            end;

 return v_grade;

end;

/


col grade format a10


select empno, ename, sal, uf_grade(sal) as grade

from emp;


'Oracle > PL/SQL' 카테고리의 다른 글

15일차 # 2-12  (0) 2012.04.24
15일차 # 2-11  (0) 2012.04.24
15일차 # 1-25: USER_SOURCE  (0) 2012.04.24
15일차 # 1-20: 프로시저에서 예외 처리  (0) 2012.04.24
15일차 # 1-17  (0) 2012.04.24

        col name format a10

col text format a80


select name, line, text from user_source

where name = 'P';



select distinct type from user_source;


TYPE

------------

PROCEDURE

PACKAGE

TRIGGER

FUNCTION

TYPE

'Oracle > PL/SQL' 카테고리의 다른 글

15일차 # 2-11  (0) 2012.04.24
15일차 Chapter 2 - Functions  (0) 2012.04.24
15일차 # 1-20: 프로시저에서 예외 처리  (0) 2012.04.24
15일차 # 1-17  (0) 2012.04.24
15일차 # 1-16: DEFAULT  (0) 2012.04.24

프로시저에서 예외 처리


  - 교재 정리


'Oracle > PL/SQL' 카테고리의 다른 글

15일차 Chapter 2 - Functions  (0) 2012.04.24
15일차 # 1-25: USER_SOURCE  (0) 2012.04.24
15일차 # 1-17  (0) 2012.04.24
15일차 # 1-16: DEFAULT  (0) 2012.04.24
15일차 # 문제  (0) 2012.04.24

create or replace procedure up_mode (

 a in     number

, b out    number

  , c in out number

)

is

 v number;

begin

 v := a; 

--  a := 100;  --> expression 'A' cannot be used as an assignment target


 v := b;

 b := 100;


 v := c;

 c := 100;


 b := a*0.12;

 c := c*0.12;

end;

/



declare

 v1 number := 100;

 v2 number := 100;

 v3 number := 200;

begin

 up_mode(v1, v2, v3);

 p(v1||' '||v2||' '||v3);

end;

/


'Oracle > PL/SQL' 카테고리의 다른 글

15일차 # 1-25: USER_SOURCE  (0) 2012.04.24
15일차 # 1-20: 프로시저에서 예외 처리  (0) 2012.04.24
15일차 # 1-16: DEFAULT  (0) 2012.04.24
15일차 # 문제  (0) 2012.04.24
15일차 # 1-13  (0) 2012.04.24

        create or replace procedure up_proc_default (

   p_deptno in emp.deptno%type := 10

 , p_minsal in emp.sal%type default 2000)

is

begin

 for r in (select * from emp

   where deptno = p_deptno

   and sal >= p_minsal)  loop

   p(r.deptno || ' ' || r.empno || ' ' || r.ename || ' ' || r.sal);

 end loop;

end;

/


set serveroutput on

exec up_proc_default

exec up_proc_default(20)

exec up_proc_default(20, 1000)



exec up_proc_default(default, 1000)

==> 오류: ORA-06550: 줄 1, 열23:PLS-00103: 심볼 "DEFAULT"를 만났습니다


↓ ↓ ↓ 


exec up_proc_default(p_minsal => 1000)


'Oracle > PL/SQL' 카테고리의 다른 글

15일차 # 1-20: 프로시저에서 예외 처리  (0) 2012.04.24
15일차 # 1-17  (0) 2012.04.24
15일차 # 문제  (0) 2012.04.24
15일차 # 1-13  (0) 2012.04.24
15일차 # 1-11  (0) 2012.04.24

# 문제: 사원 번호가 7788인 사원의 사원번호와 관리자의 사원번호 및 상관의 이름을 출력하세요.

단, 관리자의 사원번호와 이름은 프로시저를 이용하여 구하세요.



  해답:

create or replace procedure up_emp_manager (

   p_worker_empno in  emp.empno%type

 , p_mgr_empno    out emp.empno%type

 , p_mgr_ename    out emp.ename%type

)

is

begin

-- 방법 1: 서브쿼리를 이용

/* select empno, ename into p_mgr_empno, p_mgr_ename

   from emp

   where empno = (select mgr from emp

                  where empno = p_worker_empno);  */


-- 방법 2: 조인 이용

 select m.empno, m.ename into p_mgr_empno, p_mgr_ename

   from emp w, emp m

   where w.mgr = m.empno

   and w.empno = p_worker_empno;


end;

/



declare

 v_empno emp.empno%type;

 v_ename emp.ename%type;

begin

 up_emp_manager(p_worker_empno => 7788, p_mgr_empno => v_empno, p_mgr_ename => v_ename);

 p('Worker  Empno : '||7788);

 p('Manager Empno : '||v_empno);

 p('Manager Ename : '||v_ename);

end;

/




# 문제: 부서 번호를 입력받아 부서 이름, 해당 부서의 사원 수, 해당 부서 사원들의 총급여를

구하는 프로시저를 작성하세요. 그리고 바인드 변수를 사용하여 테스트 하세요.



  해답:

create or replace procedure up_emp_calc (

 p_deptno  in  dept.deptno%type

, p_dname   out dept.dname%type

, p_emp_cnt out number

, p_sal_sum out number

)

is

begin

 select d.dname, count(*), sum(e.sal) into p_dname, p_emp_cnt, p_sal_sum

 from emp e, dept d

 where e.deptno = d.deptno

 and d.deptno = p_deptno

 group by dname;

end;

/



var g_dname   varchar2(30)

var g_emp_cnt number

var g_sal_sum number

 

exec up_emp_calc(10, :g_dname, :g_emp_cnt, :g_sal_sum)

exec up_emp_calc(20, :g_dname, :g_emp_cnt, :g_sal_sum)

exec up_emp_calc(30, :g_dname, :g_emp_cnt, :g_sal_sum)

'Oracle > PL/SQL' 카테고리의 다른 글

15일차 # 1-17  (0) 2012.04.24
15일차 # 1-16: DEFAULT  (0) 2012.04.24
15일차 # 1-13  (0) 2012.04.24
15일차 # 1-11  (0) 2012.04.24
15일차 # 1-10  (0) 2012.04.24

        create or replace procedure up_hp_format (a in out varchar2)

is

begin

 if length(trim(a)) = 11 then

   a := substr(a, 1, 3)||'-'||substr(a, 4, 4)||'-'||substr(a, 8);

 else

   a := substr(a, 1, 3)||'-'||substr(a, 4, 3)||'-'||substr(a, 7);

 end if;

end;

/



set autoprint on

var g_hp varchar2(30)


exec :g_hp := '0107779999'

exec up_hp_format(:g_hp)


exec :g_hp := '01077779999'

exec up_hp_format(:g_hp)

'Oracle > PL/SQL' 카테고리의 다른 글

15일차 # 1-16: DEFAULT  (0) 2012.04.24
15일차 # 문제  (0) 2012.04.24
15일차 # 1-11  (0) 2012.04.24
15일차 # 1-10  (0) 2012.04.24
15일차 # 1-6  (0) 2012.04.24

+ Recent posts