子程序和程序包-3

----储过程编译错误

--案例05：存储过程编译错误解决
create or replace procedure testproc
as
begin
select * from emp;
end;

--解决办法01：
　　show errors
/*数据库会自动提示编译错误的原因！*/
--解决方法02：
select * from user_errors;

--案例06：带有参数的存储过程完整案例
--新建代码过程
create or replace procedure runbyparameters
(isal in emp.sal%type, --输入指定的工资
sname out varchar, /*输出查询的结果：姓名*/
--sjob in out emp.job%type --输入工作，输出‘找到’或‘没有找到’
sjob in out varchar
/*如果作为输出时类型最好使用长一些的字符型号可以保证
能够保存输出结果;所以输出参数通常不需要带精度，输入参数可以带精度！*/
)
as
icount number;/*过程内部变量声明，用于记录查询到符合条件的记录数目*/
begin
select count(*) into icount from emp
where sal>isal and job=sjob;/*根据两个输入参数将结果给输出参数*/
if icount=1 then
select ename into sname from emp where sal>isal and job=sjob;
/*查找到的信息情况*/
sname:=' 姓名为 :'||sname||'的职工 工资:'||to_char(isal);
sjob:='工作为:'||sjob;
else
sname:='没有查到工资为:'||to_char(isal);
sjob:=' 工作为:'||sjob||' 的记录!';
end if;
exception
when too_many_rows then
dbms_output.put_line('返回的值多余一行!');
when others then
dbms_output.put_line ('执行过程中发生了不可预知的错误!');
end;

--调用该存储过程
declare
realsal emp.sal%type;
realname varchar(40);
realjob varchar(40); --emp.job%type;
begin
realsal:=1100;
realname:='';
realjob:='CLERK';
/*第一次调用*/
runbyparameters(realsal, realname, realjob);
dbms_output.put_line(realname||' '||realjob);
/*第二次调用*/
realjob:='MANAGER';
runbyparameters(2900, realname, realjob);
dbms_output.put_line(realname||' '||realjob);
/*第三次调用*/
runbyparameters
(isal=>realsal, sname=>realname, sjob=>realjob);
dbms_output.put_line('带有联合符号'||realname||''||realjob);
end;

--如果分步骤执行则如下：
--第一次调用：
declare
realsal emp.sal%type;
realname varchar(40);
realjob varchar(40); --emp.job%type;
begin
realsal:=1100;
realname:='';
realjob:='CLERK';
/*第一次调用*/
runbyparameters(realsal, realname, realjob);
dbms_output.put_line(realname||' '||realjob);
end;

--第二次调用：
declare
realsal emp.sal%type;
realname varchar(40);
realjob varchar(40); --emp.job%type;
begin
realsal:=1100; --由于调用时使用了2900则这里的1100实效
realname:='';
realjob:='CLERK';
/*第二次调用*/
realjob:='MANAGER';
runbyparameters(2900, realname, realjob);
dbms_output.put_line(realname||' '||realjob);
end;

--第三次调用：

declare
realsal emp.sal%type;
realname varchar(40);
realjob varchar(40); --emp.job%type;
begin
realsal:=1100;
realname:='';
realjob:='CLERK';
/*第三次调用*/
runbyparameters
(isal=>realsal, sname=>realname, sjob=>realjob);
dbms_output.put_line('带有联合符号'||realname||''||realjob);
end;

-------自主事务

--案例07：需要使用自主事务的情况
/*步骤1：新建过程p1*/
create or replace procedure p1
as
begin
insert into student values (106, '成龙', '男');
rollback;
end;

/*步骤2：新建内部调用过程P1的过程P2*/
create or replace procedure p2
as
begin
update student set sex='女';
p1; --调用过程P1
end;

/*
由于P2过程调用时会调用过程P1，P2过程先更新值，再运行P1的插入值并且回滚，这里的rollback
只是影响插入的操作还是把更新和插入的操作都回滚呢？

*/

/*步骤3：调用过程P2验证结果：*/
execute p2;
select * from student;

--案例08：使用自主事务

/*步骤1：编译新建过程P1*/
create or replace procedure p1
as
pragma autonomous_transaction;
begin
insert into student values (106, '成龙', '男');
rollback;
end;

/*验证结果*/
--运行过程P2
execute p2;
select * from student;