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;

ITPub博客

存储过程plsql_stored procedure_为业务系统所有业务表生成主键pk方式

最新文章