s_sql := 'select '||colname||' from '||tablename;
v_cursorid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cursorid,s_sql,DBMS_SQL.V7);
v_cursorid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cursorid,s_sql,DBMS_SQL.V7);
CREATE OR REPLACE PROCEDURE demo (
source IN VARCHAR2,
destination IN VARCHAR2) IS
id_var NUMBER;
name_var VARCHAR2(30);
birthdate_var DATE;
source_cursor INTEGER;
destination_cursor INTEGER;
ignore INTEGER;
BEGIN
-- Prepare a cursor to select from the source table:
source_cursor := dbms_sql.open_cursor;
DBMS_SQL.PARSE(source_cursor,
'SELECT id, name, birthdate FROM ' || source,
DBMS_SQL.native);
DBMS_SQL.DEFINE_COLUMN(source_cursor, 1, id_var);
DBMS_SQL.DEFINE_COLUMN(source_cursor, 2, name_var, 30);
DBMS_SQL.DEFINE_COLUMN(source_cursor, 3, birthdate_var);
ignore := DBMS_SQL.EXECUTE(source_cursor);
-- Prepare a cursor to insert into the destination table:
destination_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(destination_cursor,
'INSERT INTO ' || destination ||
' VALUES (:id_bind, :name_bind, :birthdate_bind)',
DBMS_SQL.native);
-- Fetch a row from the source table and insert it into the destination table:
LOOP
IF DBMS_SQL.FETCH_ROWS(source_cursor)>0 THEN
-- get column values of the row
DBMS_SQL.COLUMN_VALUE(source_cursor, 1, id_var);
DBMS_SQL.COLUMN_VALUE(source_cursor, 2, name_var);
DBMS_SQL.COLUMN_VALUE(source_cursor, 3, birthdate_var);
-- Bind the row into the cursor that inserts into the destination table. You
-- could alter this example to require the use of dynamic SQL by inserting an
-- if condition before the bind.
DBMS_SQL.BIND_VARIABLE(destination_cursor, ':id_bind', id_var);
DBMS_SQL.BIND_VARIABLE(destination_cursor, ':name_bind', name_var);
DBMS_SQL.BIND_VARIABLE(destination_cursor, ':birthdate_bind',
birthdate_var);
ignore := DBMS_SQL.EXECUTE(destination_cursor);
ELSE
-- No more rows to copy:
EXIT;
END IF;
END LOOP; -- Commit and close all cursors:
COMMIT;
DBMS_SQL.CLOSE_CURSOR(source_cursor);
DBMS_SQL.CLOSE_CURSOR(destination_cursor);
EXCEPTION
WHEN OTHERS THEN
IF DBMS_SQL.IS_OPEN(source_cursor) THEN
DBMS_SQL.CLOSE_CURSOR(source_cursor);
END IF;
IF DBMS_SQL.IS_OPEN(destination_cursor) THEN
DBMS_SQL.CLOSE_CURSOR(destination_cursor);
END IF;
RAISE;
END;
/
TYPE FLIGHTRCTYPE IS REF CURSOR ;--RETURN FLIGHTRESULT;
FUNCTION SELECTCOMPARE (TNAME IN VARCHAR2) RETURN FLIGHTRCTYPE;
END FLIGHTRESULT;
CREATE OR REPLACE PACKAGE BODY FLIGHTRESULT AS
FUNCTION SELECTCOMPARE (TNAME IN VARCHAR2)
--TNAME:IN OR OUT
RETURN FLIGHTRCTYPE IS
RC FLIGHTRCTYPE; --返回结果集游标
EXCSQLSTR1 VARCHAR2(3000):='';
BEGIN
EXCSQLSTR1:='SELECT * FROM '||TNAME;
OPEN RC FOR EXCSQLSTR1;
RETURN RC;
EXCEPTION --异常处理
WHEN NO_DATA_FOUND THEN
OPEN RC FOR SELECT -2 SEATS FROM DUAL;
RETURN RC; --数据没有找到
WHEN OTHERS THEN
OPEN RC FOR SELECT -3 SEATS FROM DUAL;
RETURN RC; --运行时发生意外错误
END SELECTCOMPARE;
END FLIGHTRESULT;