oracle学習ノート(21)パッケージ

パッケージ
以前に呼び出されたdbms_output.put_line('')、dbms_outputはパッケージです
パッケージ作成構文
1.パッケージの宣言

--         ，  ，      
--                create or repalce
create or replace package dbms_my
is
    record   。
    procedure $procedure_name$ [(  ...)];
    function $function_name$ [(  ...)]
    ......
end dbms_my;

2.実装パッケージ

create or replace package body dbms_my
is
    procedure 
    is
    ......
end dbms_my;

パッケージ例

--     
create or replace package dbms_my
is
    --         
    TYPE type_my_rec is record(
        empno number,
        ename varchar2(10),
        sal number
    );
    procedure sum_sal(deptno department.deptno%type,sum in out number); 
    function avg_sal(deptno department.deptno%type) return  number;
end dbms_my;

--     
create or replace package body dbms_my
is
    procedure sum_sal(p_deptno department.deptno%type,sum in out number)
    is 
        sum number;
    begin
        select sum(sal) into sum from employee where deptno=p_deptno;
    end sum_sal;
    
    function avg_sal(deptno department.deptno%type) return  number
    is
        result number;
    begin
        select avg(sal) into result from employee where deptno=p_deptno;
        return result;
    end avg_sal;
end dbms_my;    

--     
declare
    --                 
    emp_rec dbms_my.type_my_rec;
    v_deptno number default 7879;
    sum_sal number;
    avg_sal number;
    x varchar2(100) := 'default value';
begin
    emp_rec.empno := 7879;--          
    sum_sal := dbms_my.sum_sal(deptno);--    
    dbms_output.put_line(sum_sal);--    
    select dbms_my.avg_sal(7879) into avg_sal from dual;--    
    dbms_output.put_line(avg_sal);--    
end;
/