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