--根据不同的前缀生成唯一的Sequence ID。前缀为表名,需要使用sequence的地方必须按照表名建立相应的sequence。该sequence的名称格式为 tablename + "_seq"
CREATE OR REPLACE FUNCTION ECCDE.GETROWID(p_tablename IN VARCHAR2)
RETURN VARCHAR2 AS
v_sql VARCHAR2(1000);
v_rowid VARCHAR2(400);
BEGIN
v_sql := 'SELECT to_char(sysdate, ''YYYYMMDDHH24'')||lpad('||p_tablename||'_seq.nextval, 6, ''0'') FROM dual';
EXECUTE IMMEDIATE v_sql INTO v_rowid;
RETURN v_rowid;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
CREATE OR REPLACE FUNCTION ECCDE.GETROWID(p_tablename IN VARCHAR2)
RETURN VARCHAR2 AS
v_sql VARCHAR2(1000);
v_rowid VARCHAR2(400);
BEGIN
v_sql := 'SELECT to_char(sysdate, ''YYYYMMDDHH24'')||lpad('||p_tablename||'_seq.nextval, 6, ''0'') FROM dual';
EXECUTE IMMEDIATE v_sql INTO v_rowid;
RETURN v_rowid;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
SELECT GETROWID('tablename') FROM DUAL;