存储过程（procedure）

一、存储过程的创建与执行

1.不带参数的存储过程

创建一存储过程update_emp,该过程用于将emp表中empno为7876的员工姓名修改为candy

//注意在程序窗口中写procedure的创建，写完后要编译
create or replace procedure p1
is 
begin 
  update scott.emp set ename='candy'where empno=7876;
end p1;

//在命令窗口中写procedure的调用
begin
  p1;
end;

2.带参数的存储过程（(一个in，一个out)）

计算指定系总学分大于40的人数

(指定系参数模式---in,大于40的人数参数模式---out)

//创建
create or replace procedure p2(v_zym in xs.zym%type,person_num out number)
或者
CREATE OR REPLACE PROCEDURE p2(v_zym in varchar2,person_num out number) //注意字符型变量不带长度
as
begin 
  select count(zxf) into person_num from xs where zym=v_zym and zxf>=40;
end p2;

//调用
declare
  person_n number(3);
begin
  p2('计算机',person_n);
  dbms_output.put_line(person_n);
end;

补充：rownum的应用（注意：rownum只能在具体表中应用）

编写一个存储过程，计算显示部门人数最多的部门号、人数及平均工资。
create or replace procedure p1
is
v_deptno scott.emp.deptno%type;
v_pn number;
v_avg scott.emp.sal%type;
begin
  select deptno,personNum,avgSal into v_deptno,v_pn,v_avg from 
(select deptno,count(*) personNum,avg(sal) avgSal from scott.emp
 group by deptno
 order by personNum desc) //括号括起来放到from后面相当于一个表
 where rownum<=1; //条数为1
 dbms_output.put_line(v_deptno||' '||v_pn||' '||v_avg);
 end p1;
 
 begin
   p1;
 end;

二、存储过程提高篇

1.返回多个值的存储过程（一个in，两个out）

创建一个存储过程，以部门号为参数，返回该部门的人数和平均工资。
create or replace procedure p2
(p_deptno in scott.emp.deptno%type,
p_avgsal out scott.emp.sal%type,
p_count out scott.emp.sal%type)
as
begin
  select avg(sal),count(*) into p_avgsal,p_count from scott.emp where deptno=p_deptno;
end p2;

declare 
   v_avgsal scott.emp.sal%type;
   v_count scott.emp.sal%type;
begin
  p2(20,v_avgsal,v_count);
  dbms_output.put_line(v_avgsal||' '||v_count);
end;

注意：形参为OUT类型的参数需要在procedure的begin块中赋值。（一般有select into 赋值） 

2. 存储过程与游标结合

例子：创建一个存储过程，以部门号为该存储过程的in类型参数，查询该部门的平均工资，并输出该部门中比平均工资高的员工号、员工名。
注意：用到了(for 循环遍历  游标)
create or replace procedure p3(
v_deptno  scott.emp.deptno%type)
is
v_sal scott.emp.sal%type;
begin
  select avg(sal) into v_sal from scott.emp where deptno=v_deptno;
  dbms_output.put_line(v_deptno||' '||'average salary is: '||v_sal);
 for v_emp in(select * from scott.emp where deptno=v_deptno and sal>v_sal) //注意此句
  loop
    dbms_output.put_line(v_emp.deptno||' '||v_emp.ename);
  end loop;
end p3;

begin
  p3(20);
end;

3.模糊查询

select * from scott.emp where ename like '%A%';

例子：用存储过程进行模糊查找，如查找ename中包含L的雇员信息
create or replace procedure p4(
v_ename scott.emp.ename%type)
is
  cursor c_1 is select * from scott.emp where ename like '%'||v_ename||'%'; //与游标结合
begin
 for v_1 in c_1
  loop
    dbms_output.put_line(v_1.empno||' '||v_1.ename||' '||v_1.job||' '||v_1.deptno);
  end loop;
end p4;

begin
  p4('L');
end;