#有了一点写存储过程的思路,花几个小时,人真笨
declare
v_max_id number;
begin
select max_id into v_max_id from t_max_id where tname='&table';
update t_max_id set max_id=v_max_id+1;
commit;
exception when no_data_found then
v_max_id:=1;
insert into t_max_id values('&table',v_max_id);
commit;
end;
/

PL/SQL procedure successfully completed.

#######建立测试表
SQL> desc t_max_id;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
TNAME                                              VARCHAR2(30)
MAX_ID                                             NUMBER

SQL>

#########生成pd电子书包平台所有表主键的存储过程
create or replace procedure p_get_table_max_pk(v_table_name varchar2,v_out_max_id out number)
is
v_max_id number;
begin
select max_id into v_max_id from t_max_id where tname=v_table_name;
update t_max_id set max_id=v_max_id+1 where tname=v_table_name;
commit;
v_out_max_id:=v_max_id+1;
exception when no_data_found then
   v_max_id:=1;
   insert into t_max_id values(v_table_name,v_max_id);
   v_out_max_id:=v_max_id;
   commit;
end;
/

小结;

1,异常要写在plsql代码块的最后,而不是最前面

2,out输出的参数,在plsql代码块中:比如:v_out_max_id同时要在正常处理逻辑与异常处理逻辑两处进行 v_out_max_id:=某个变量或什么值的操作;

3,在sqlplus中调用存储过程及显示输出参数out为如下方法

SQL> var a number #定义与存储过程out输出参数对应的变量

SQL> exec p_get_table_max_pk('t1',:a);#执行存储过程

PL/SQL procedure successfully completed.

SQL> select * from t_max_id;

TNAME MAX_ID
------------------------------ ----------
t1 2

SQL> print a;#显示输出变量值

A
----------
2

ITPub博客

编写电子书包平台所有表主键之存储过程_sp_stored procedure

最新文章