zbb20170601 oracle 存储过程 函数 包

-- 过程 函数----------
-- 过程 ----------

create or replace procedure pro_printemp
is
-- 定义部分
   cursor cur_emp is select * from emp;

begin
  -- 执行部分
  for v_row in cur_emp loop
    dbms_output.put_line(v_row.empno||'  '||v_row.ename||'  '||v_row.deptno);
  end loop;
  
  exception when others then
    dbms_output.put_line(sqlerrm);
end pro_printemp;

-- 执行过程
SQL> execute pro_printemp;

SQL> exec pro_printemp;

-- JDBC调用方式
SQL> call pro_printemp();

-- 定义带参的过程
create or replace procedure pro_pebd(dno number) 
as       cursor cur_emp is select * from emp where deptno=dno;
begin
       for v_row in cur_emp loop
         dbms_output.put_line(v_row.empno||'  '||v_row.ename||'  '||v_row.deptno);
       end loop;

  exception when others then
    dbms_output.put_line(sqlerrm);
end pro_pebd;

-- 执行
SQL> exec pro_pebd(10);

-- 参数的分类：
-- 1- 输入类型参数：参数只负责接收值到过程中

-- 2- 输出类型参数：参数只负责将过程中结果带出
-- 根据员工编号得到工资
create or replace procedure pro_getsal(eno number,esal out number)
is

begin
  select sal into esal from emp where empno=eno;
end pro_getsal;

-- 执行带输出类型参数的过程：
SQL> variable e_sal number; -- 定义一个变量，承载输出值
SQL> exec pro_getsal(7788,:e_sal); -- 执行，注意：变量使用时格式： ":变量名"
SQL> print e_sal; -- 打印变量值，注意：打印时不能带符号":";

-- 3- 输入输出类型参数：参数负责将值带入过程执行，并带出结果

create or replace procedure pro_getsal(val in out number)
is

begin
  select sal into val from emp where empno=val;
  
  exception when others then 
    dbms_output.put_line(sqlerrm);
end pro_getsal;

-- 执行带输入、输出类型的参数：
SQL> var val number; -- 定义变量
SQL> exec :val := 7839; -- 给变量赋值
SQL> exec pro_getsal(:val); -- 执行
SQL> print val;-- 打印结果

/**
     参数定义规范：
     尽量只使用输入类型参数，而尽量不要使用输出类型参数和输入输出类型参数。
*/

-- 函数 ----- 有返回值的过程
-- 注意： 形参不能定义精度
create or replace function fun_getsal(eno number)
       return number
   as
       v_sal number;
   begin
     select sal into v_sal from emp where empno=eno;
     return v_sal;
     
     exception when others then
       dbms_output.put_line(sqlerrm);
   
   end fun_getsal;

-- 执行1：使用变量接收函数结果
SQL> var v_sal number; -- 定义变量
SQL> exec :v_sal := fun_getsal(7788); -- 使用变量接收函数执行结果
SQL> print v_sal; -- 打印变量

-- 执行2：直接将函数结果输出
SQL> exec dbms_output.put_line(fun_getsal(7839));

-- 执行3：JDBC执行
 -- 1 定义变量，在JavaSE中定义
 
 -- 2 执行
SQL> call :v_sal := fun_getsal(7788); -- 使用变量接收函数执行结果
-- 3- 获取变量，在JavaSE中

-- 执行4：像系统提供的函数一样，使用在DML、DDL ... 语句中
-- 输出每个部门员工工资占部门总工资百分比

create or replace function fun_get_dept_sal(dno number) return number
is
  sumsal number;
begin
  select sum(sal) into sumsal from emp where deptno=dno;
  return sumsal;
  
  exception when others then
    dbms_output.put_line(sqlerrm);
end fun_get_dept_sal;

select e.*,round(e.sal/fun_get_dept_sal(e.deptno)*100,2)||'%' percent_sal
from emp e

/**
     定义过程和函数的规范：
         如果不需要返回值，则定义为过程：procedure
         如果需要返回值，则定义为函数：function
         尽量不要使用输出类型的参数
*/

-- 调用函数或者过程时，实参填写的分类：
create or replace procedure pro_addemp(
          v_empno number,v_ename varchar2,v_sal number,v_deptno number) is
begin
  insert into emp(empno,ename,sal,deptno) values(v_empno,v_ename,v_sal,v_deptno);
  commit;
  
  exception when others then
    dbms_output.put_line(sqlerrm);
end pro_addemp;

-- 1- 按位置输入
SQL> exec pro_addemp(1000,'aa',2000,10);
-- 2- 按名称输入
SQL> exec pro_addemp(v_ename => 'bb',
                v_deptno => 20,v_sal => 3000,v_empno => 7777);


-- 3- 混合输入
SQL> exec pro_addemp(1001,'cc',v_deptno => 20,v_sal => 2500);

-- 注意：混合输入时：必须先按位置再按名称，一旦使用了按名称后不能再
--       使用按位置赋值。
-- 错误方式：
-- SQL> exec pro_addemp(1001,'cc',v_sal=>2500,20);

/**
   赋予实参的规范：
   只按照位置赋予，不允许按照名称和混合赋予；
*/


------- 包 ---------
-- 整合一组功能的 过程和函数、变量所形成的一个功能体

-- 1- 声明部分
create or replace package pak_scott is

  -- Author  : ADMINISTRATOR
  -- Created : 2014/8/13 10:35:14
  -- Purpose : operation scott

  -- Public variable declarations
  v_sal number;

  -- Public function and procedure declarations
  function get_sal(eno number) return number;
  procedure print_sal(eno number);
  procedure print_sal(v_ename varchar2);

end pak_scott;
-- 2- 实现部分
create or replace package body pak_scott is

  -- Private variable declarations
  v_deptno number;

  -- public Function and procedure implementations
  function get_sal(eno number) return number
    is
    begin
      select sal into v_sal from emp where empno=eno;
      return v_sal;
    end;
    
  procedure print_sal(eno number)
    as
    begin
      dbms_output.put_line(get_sal(eno));
    end;
 -- private function or procedure 
   function get_eno(v_ename varchar2) return number
     is
        v_eno number;
     begin
       select empno into v_eno from emp where ename=v_ename;
       return v_eno;
     end;
     
     
   -- public print_sal   
   procedure print_sal(v_ename varchar2) 
     is 
     begin
       dbms_output.put_line(get_sal(get_eno(v_ename)));
     end;
   
     
end pak_scott;

-- 执行包：
-- 执行过程
SQL> exec pak_scott.print_sal(7788);

SQL> exec pak_scott.print_sal('KING');
-- 执行函数
SQL> exec dbms_output.put_line(pak_scott.get_sal(7788));

-- 执行（使用）变量
SQL> exec pak_scott.v_sal:=10;
SQL> exec dbms_output.put_line(pak_scott.v_sal);

select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp;