【oracle资料整理】--【15】存储过程和函数

存储过程和函数
  没有名字的PL/SQL块(匿名)
  有名字的PL/SQL块(子程序-存储过程和函数)
存储过程
      create or replace procedure p1
      as
      begin
      exception
      end;

<最简单的存储过程>
       create or replace procedure p_jd
       as
         hello varchar2(20); 
       begin
        select 'Hello World' into hello from dual;
        dbms_output.put_line(hello);
       end;
 执行存储过程的方法
      <1> execute p_jd;     (SQL*PLUS中SQL>)
      <2> begin
           p_jd;
          end; 
 带参数的存储过程
   --输入参数in
   --不写in的参数都是输入参数
   --根据部门编号查员工姓名
    create or replace procedure p_getemp(no  number)
    as
      cursor c1 is select * from emp
       where deptno = no;
      c1rec c1%rowtype;
    begin
  --       no := 20; 输入参数是不能赋值的
       for c1rec in c1 loop
        dbms_output.put_line(c1rec.ename);
       end loop;
    end;    
   --输出参数out
   --根据部门编号查出部门的平均工资,返回平均工资的值
   -- in 输入 (在procedure中是不能赋值的)
   --  out 输出 (在procedure中是能赋值的)
-- 定义参数是不能指定长度的
 --定义变量是必须指定长度的

    create or replace procedure p_getavgsal(no  number,avgsal out number)
    -- no   输入参数
    -- avgsal  输出参数
    as
     aa varchar2(10); --变量
    begin
       select avg(sal) into avgsal
       from emp where deptno = no; 
    end;    
    调用它只能使用PL/SQL块
        declare
         av number;
        begin         
          p_getavgsal(10,av);
          dbms_output.put_line('平均工资:'||round(av,2));
        end;       
   --一个参数同时可以输入,也可以输出
   --输入输出参数
    create or replace procedure
    p_getavgsal(n in out number)
    as

    begin
       select avg(sal) into n
       from emp where deptno = n; 
    end;
   
   declare
         av number;
        begin         
          av  := 10;
          p_getavgsal(av);
          dbms_output.put_line('平均工资:'||round(av,2));
        end;       
  --带多个参数的存储过程
     create or replace procedure
      p_getM(no number,pjob varchar2) as
       --参数游标c2,定义参数的时候
       --只能指定类型,不能指定长度 
       --参数只能出现在select语句=号的右侧
       cursor c2(no1 number,pjob1 varchar2) is select * from emp
         where deptno = no1 and job=pjob1;

       c2rec c2%rowtype;
       --定义变量的时候要指定长度
       v_job varchar2(20);
     begin

          --参数在游标中使用
          for c2rec in c2(no,pjob) loop
            dbms_output.put_line(c2rec.deptno||'-'||c2rec.ename);

          end loop;

     end;
  
    调用方法:execute p_getm(10,'MANAGER'); --按位置
   -- no = 10 , pjob = 'MANAGER'
          
      execute p_getm(pjob => 'MANAGER',no => 10);
   --按参数的名字 来传值

  函数:
    必须要有返回值
    只能返回一个值
  
   --根据部门编号查出部门的平均工资,返回平均工资的值(利用函数)
    create or replace function
    f_getavgsal(no number)
    return number
    as
      avgsal number(7,2);
    begin
       select avg(sal) into avgsal
       from emp where deptno = no;
       --返回值
       return avgsal;  
    end;  
 
--带输出参数
 --每个部门的平均工资和工资总额
 --一个函数返回2个值
create or replace function
    f_getavgsal(no number,sumsal out number)
    return number
    as
      avgsal number(7,2);
    begin
       --平均工资
       select avg(sal) into avgsal
       from emp where deptno = no;
       --工资总额
       select sum(sal) into sumsal
       from emp where deptno = no;
       --返回值
       return avgsal;  
    end;    

  --调用方法
     <1>PL/SQL块调用
      declare
       aa number;
      begin
        aa := f_getavgsal(10)  ;
        dbms_output.put_line(to_char(aa));
      end; 
    <2> SQL语句来调用(DML)
       select f_getavgsal(10) from dual;

       select deptno,f_getavgsal(deptno) from dept;

    <3>
       create or replace function f1
       return number
       as
         update emp set comm = 1000
          where job='CLERK';
         return sql%rowcount;
       end;
      --select语句是无法调用它的,因为其中含有修改语句