1、不带参数的存储过程
注意在Scott权限下进行:
create or replace procedure update_emp
as
begin
update scott.emp set ename='candy' where empno=7876;
end update_emp;
调用:
declare
begin
update_emp;
end;
2、计算指定系总学分大于40的人数
create or replace procedure count_grade
(v_zym in xs.zym%type,person_num out number)
as
begin
select count(*) into person_num from xs
where zym=v_zym and zxf>40;
end count_grade;
调用:
declare
person_n number(3);
begin
count_grade('计算机',person_n);
dbms_output.put_line(person_n);
end;
3、创建一个存储过程,以部门号为该存储过程的in类型参数,查询该部门的平均工资,并输出该部门中比平均工资高的员工号、员工名。
存储过程show_emp:
create or replace procedure show_emp
(p_deptno scott.emp.deptno%type)
as
v_sal scott.emp.sal%type;
begin
select avg(sal) into v_sal from scott.emp
where deptno=p_deptno;
dbms_output.put_line(p_deptno||' '||'avarage salary is:'||v_sal);
for v_emp in (select * from scott.emp where deptno=p_deptno and sal>v_sal)
loop
dbms_output.put_line(v_emp.empno||' '||v_emp.ename);
end loop;
end show_emp;
调用:
begin
show_emp(20);
end;
4、用存储工程进行模糊查找,如查找ename中包含L的雇员信息。
存储过程tp1:
create or replace procedure tp1
(var scott.emp.ename%type)
as
cursor c_1 is select * from scott.emp
where ename like '%'||var||'%';
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 tp1;
调用:
begin
tp1('L');
end;