一、存储过程的创建与执行
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;