PL/SQLパッケージの基本使用

簡単な例です

--     (   java       ，    )
create or replace package emp_pack 
is 
  function fun_get_name_by_num(p_num emp.empno%type) return emp.ename%type;
  procedure pro_get_name_by_num(p_num in emp.empno%type ,p_name out emp.ename%type);
end emp_pack;


--    
create or replace package body emp_pack
is 
  --  
  function fun_get_name_by_num(p_num emp.empno%type) 
  return emp.ename%type is
    v_name emp.ename%type;
  begin
    select ename into v_name from emp where empno=p_num;
  return 
    v_name;
  end;
  
  --  
  procedure pro_get_name_by_num(p_num in emp.empno%type ,p_name out emp.ename%type)
  is
  begin
    select ename into p_name from emp where empno=p_num;
  end pro_get_name_by_num;
end emp_pack;

--   
declare
  p_num emp.empno%type :=7369;
  p_name  emp.ename%type;  
begin
  dbms_output.put_line(emp_pack.fun_get_name_by_num(p_num));
  emp_pack.pro_get_name_by_num(p_num,p_name);
  dbms_output.put_line(p_name);
end;

パッケージ内のカーソルの使用

--         
create or replace package pkg_emp_deptno
is  
  cursor cur_emp(p_dno emp.deptno%type)
  return emp%rowtype;  
  procedure pro_show_into(p_dno emp.deptno%type);
end pkg_emp_deptno;

create or replace package body pkg_emp_deptno 
is  
  --cursor
  cursor cur_emp(p_dno emp.deptno%type)
  return emp%rowtype
  is select * from emp where deptno=p_dno;
  
  --procedure
  procedure pro_show_into(p_dno emp.deptno%type)
  is
  begin
  for rec_emp in cur_emp(p_dno) loop
    dbms_output.put_line(rec_emp.ename||' '||rec_emp.sal);
  end loop;
  end pro_show_into;
  
end pkg_emp_deptno;



declare
  p_num emp.deptno%type :=20;
begin
 pkg_emp_deptno.pro_show_into(p_num);
end;