oracle 动态传入表名查询,动态SQL的绑定变量_表名作为参数传入

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) |