編寫電子書包平臺所有表主鍵之儲存過程_sp_stored procedure

#有了一點寫儲存過程的思路,花幾個小時,人真笨
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