儲存過程plsql_stored procedure_為業務系統所有業務表生成主鍵pk方式

create or replace procedure  p_get_maxid
(v_table_name IN VARCHAR2,v_maxid out varchar2)
  
IS
   ROWS      INTEGER;
     
BEGIN
   SELECT COUNT (*)
     INTO ROWS
     FROM SYSTEM_MAXID
    WHERE UPPER (table_name) = UPPER (v_table_name);

   IF ROWS = 0
   THEN
     v_maxid:=lpad(to_char(rows),16,'0');
     

      INSERT INTO SYSTEM_MAXID
                  (serial_no, table_name, maxid, id_len,
                   id_type, applier, apply_time
                  )
           VALUES (seq_maxid.NEXTVAL, UPPER (v_table_name), v_maxid, 20,
                   'number', 'aibo_User', SYSDATE
                  );
   ELSE
      SELECT maxid
        INTO rows
        FROM SYSTEM_MAXID
       WHERE UPPER (table_name) = UPPER (v_table_name);
  

   v_maxid := lpad(to_char(rows + 1),16,'0');

   UPDATE SYSTEM_MAXID
      SET maxid = v_maxid,
          apply_time = SYSDATE
    WHERE UPPER (table_name) = UPPER (v_table_name);
  END IF;
 
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      dbms_output.put_line('no record');
END p_get_maxid;