CREATE OR REPLACE PROCEDURE INSERTWCMTEMP2(P_TABLENAME IN CHAR,
P_TEMPTNAME IN CHAR) AS
V_CURSORID INTEGER;
I_CURSORID INTEGER;
I_DUMMY INTEGER;
V_DOCID NUMBER;
V_INSERTSTMT VARCHAR2(200);
V_SELECTSTMT VARCHAR2(500);
V_DUMMY INTEGER;
C_DOCID VARCHAR2(10);
V_SQL_TYPE CHAR := '1';
V_TRS_FLAG CHAR := '2';
V_SEQ_ID INTEGER;
BEGIN
I_CURSORID := DBMS_SQL.OPEN_CURSOR;
V_SELECTSTMT := 'SELECT docid FROM ' || P_TABLENAME;
DBMS_SQL.PARSE(I_CURSORID, V_SELECTSTMT, DBMS_SQL.NATIVE);
-- DBMS_SQL.DEFINE_COLUMN(I_CURSORID, 1, V_DOCID, 20);
DBMS_SQL.DEFINE_COLUMN(I_CURSORID, 1, V_DOCID);
-- Execute the statement.
I_DUMMY := DBMS_SQL.EXECUTE(I_CURSORID);
V_CURSORID := DBMS_SQL.OPEN_CURSOR;
V_INSERTSTMT := 'insert into ' || P_TEMPTNAME ||
' (SEQ_ID,DOCID,SQL_TYPE,TRS_FLAG) ' ||
'values(:x_seq_id, :x_docid, :x_sql_type, :x_trs_flag)';
DBMS_SQL.PARSE(V_CURSORID, V_INSERTSTMT, DBMS_SQL.NATIVE);
LOOP
IF DBMS_SQL.FETCH_ROWS(I_CURSORID) = 0 THEN
EXIT;
END IF;
DBMS_SQL.COLUMN_VALUE(I_CURSORID, 1, V_DOCID);
-- v_SEQ_ID := A增量$_SEQ.NEXTVAL;
SELECT A增量$_SEQ.NEXTVAL INTO V_SEQ_ID FROM DUAL;
C_DOCID := TO_CHAR(V_DOCID);
DBMS_SQL.BIND_VARIABLE(V_CURSORID, ':x_seq_id', V_SEQ_ID);
DBMS_SQL.BIND_VARIABLE(V_CURSORID, ':x_docid', C_DOCID);
DBMS_SQL.BIND_VARIABLE(V_CURSORID, ':x_sql_type', V_SQL_TYPE);
DBMS_SQL.BIND_VARIABLE(V_CURSORID, ':x_trs_flag', V_TRS_FLAG);
V_DUMMY := DBMS_SQL.EXECUTE(V_CURSORID);
END LOOP;
DBMS_SQL.CLOSE_CURSOR(V_CURSORID);
END;
阅读(2995) | 评论(1) | 转发(0) |