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);
(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');
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);
(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;
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博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-667565/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-667565/