PLSQL创建一个procedure，使用Oracle数据库dept表 --存储过程内容：输入部门编号，利用传出参数返回部门的平均工资；存储过程中添加异常处理块；...

create or replace procedure p1(p_deptno number,p_avg_sal out number)
is
ex exception;
cursor yb is select * from dept;
flag number := 0;
begin
for r in yb loop
if r.deptno=p_deptno then
flag := 1;
end if;
end loop;

if flag=1 then
select avg(sal) into p_avg_sal from emp where deptno=p_deptno;
else
raise ex;
end if;

exception
when ex then dbms_output.put_line('没有此部门');
end;

/

declare
v_avg_sal number(4);
begin
p1(11,v_avg_sal);
dbms_output.put_line(v_avg_sal);

end;

转载于:https://www.cnblogs.com/y-zz/p/8010652.html