【Oracle篇】存储过程

转载请注明出处：http://blog.csdn.net/feng1790291543

一、PL/SQL为我们提供了一种叫做存储子程序的机制，允许在应用程序之间共享PL/SQL代码。而且这些PL/SQL代码可以存储在数据库中。
二、存储子程序是存储在数据库中的一个命名的PL/SQL块。PL/SQL9i中支持三种类型的存储子程序：
    ---过程、函数、包
三、为什么在PL/SQL要使用存储子程序？我们来分析一下它的优势：
  1、可扩展性
  2、模块化
  3、可重用性
  4、可维护性
  5、抽象和数据隐藏
  6、安全性

三、函数与过程相似，也是数据库中存储的命名PL/SQL程序块。建立它们遵循了相同的规则。它们的安全方式和参数传递也相同。函数的主要独特特性   是它必须返回一个值。这个值可以是NUMBER或者VARCHAR2这样的单独数据类型，或者也可以是PL/SQL数组或者对象这样的复杂数据类型。

--1：定义一个 procedure ,完成，根据输入一个员工编号，输出其姓名以及工作。

create or replace procedure pro_emp(v_eno number)
as
   cursor cur_emp is select e.ename,e.job from emp e where e.empno=v_eno; 
begin
    for v_row in cur_emp loop
      dbms_output.put_line(v_row.ename||' '||v_row.job);
     end loop;
end pro_emp;


--2：定义一个 procedure ,完成，对dept表的信息插入。（尝试按名称调用和混合调用）.并且提交。

create or replace procedure pro_add_dept(v_dno number,v_dna varchar2,v_dloc varchar2)
as
begin
  insert into dept(deptno,dname,loc) values(v_dno,v_dna,v_dloc);
  commit;
end pro_add_dept;


--3：-- 使用自定义函数完成：输出scott.emp每个人的信息，及其工资占部门总工资百分比。

create or replace function fun_scott_empsal return number
as
     emp_salpers  number;

begin
  select round((select e.sal from emp e where e.ename='SCOTT')/(select sum(sal) from emp e where e.deptno=(select e.deptno from emp e
  where e.ename='SCOTT'))*100,2) into emp_salpers from emp e where e.ename='SCOTT';
  
  return emp_salpers;
end fun_scott_empsal;

-- 过程调用函数输出信息
create or replace procedure pro_empsal(v_salpers number)
as
   cursor cur_emp is select e.* from emp e where e.ename='SCOTT'; 
begin
    for v_row in cur_emp loop
      dbms_output.put_line(v_row.empno||' '||v_row.ename||' '||v_row.job||' '||v_row.mgr||' '||v_row.hiredate||' '||v_row.sal||' '||v_row.comm||' '||v_row.deptno||' '||v_salpers);
     end loop;
end pro_emp;

--       要求使用自定义函数完成： 根据部门编号，统计部门总工资的函数。

create or replace function fun_scott_emp(v_deptno number) return number
as
     dept_sum  number;
begin
  select sum(sal) into dept_sum from emp e where e.deptno=v_deptno;
  return dept_sum;
end fun_scott_emp;


/*
4：

    定义一个包，用来管理emp表
      共有属性：员工工资
     共有方法：根据员工编号返回员工工资
               根据员工工资返回工资等级   需要借助salgrade;     

     共有重载过程：根据部门编号输出员工姓名。
               根据部门名称输出员工姓名。（要求 先 调用“根据部门名称得到部门编号”私有方法 得到部门编号
                                                再 调用“根据部门编号输出员工姓名。”来输出）

     私有属性：部门编号
     私有方法：根据部门名称得到部门编号

*/

create or replace package pak_emp is

   --    共有属性：员工工资
   emp_sal number;

   --共有方法：根据员工编号返回员工工资(1) ,根据员工工资返回工资等级 (2) 需要借助salgrade;
   function getSalbyeno(eno number) return number;
   function getSalgradeby(esal number) return number;

   -- 共有重载过程：根据部门编号输出员工姓名。
   procedure pro_printName(dno number);

   --根据部门名称输出员工姓名。（要求 先 调用“根据部门名称得到部门编号”私有方法 得到部门编号
                              --再 调用“根据部门编号输出员工姓名。”来输出）
   procedure pro_printName(dname number);

end pak_emp;


create or replace package body pak_emp is

   -- 私有属性：部门编号
   dno varchar2(10);
   -- 私有方法：根据部门名称得到部门编号
   function getDnobydname(v_dname varchar2) return number
     is
     begin 
       select deptno into v_dno from dept where dname=v_dname;   
       end getDnobydname;
       
   --共有方法：根据员工编号返回员工工资(1) ,根据员工工资返回工资等级 (2) 需要借助salgrade; 
   function getSalbyeno(eno number) return number
     is
     begin 
       select sal into v_sal from emp where empno=eno;
       end getSalbyeno;
       
    create or replace function getSalgradeby(esal number) return number
     is
     begin 
       select emp_rk.rk salgrade from
       (
          select e.*,rank() over(order by sal desc) rk from emp e where e.sal=esal
       ) emp_rk;
       end getSalgradeby;
   
   -- 共有重载过程：根据部门编号输出员工姓名。
   procedure pro_printName(dno number)
     is
     begin 
       select ename into v_ename from emp where deptno=dno;
       end;
   
   --根据部门名称输出员工姓名。（要求 先 调用“根据部门名称得到部门编号”私有方法 得到部门编号
                              --再 调用“根据部门编号输出员工姓名。”来输出）
   procedure pro_printName(v_dname number);  
   is
     begin 
       select e.ename into v_ena from dept d,emp e where dname=v_dname and e.deptno=d.deptno;
       end;
 
end pak_emp;