CREATE OR REPLACE PROCEDURE copy(Source IN VARCHAR2,
Destination IN VARCHAR2) IS
id_var temp_table.num_col%type;
name_var temp_table.char_col%type;
source_cursor INTEGER;
destination_cursor INTEGER;
ignore INTEGER;
BEGIN
source_cursor := dbms_sql.open_cursor;
DBMS_SQL.PARSE(source_cursor,
'SELECT NUM_COL, CHAR_COL FROM ' || source,
DBMS_SQL.native);
DBMS_SQL.DEFINE_COLUMN(source_cursor, 1, id_var);
DBMS_SQL.DEFINE_COLUMN(source_cursor, 2, name_var, 2000);
ignore := DBMS_SQL.EXECUTE(source_cursor);
-- Prepare acursor 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)',
DBMS_SQL.native);
-- Fetch a rowfrom 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);
--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);
ignore :=DBMS_SQL.EXECUTE(destination_cursor);
ELSE
--No more rows to copy:
EXIT;
END IF;
END LOOP;
-- Commit andclose 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);
Dbms_Output.put_line(ignore);
END IF;
IF DBMS_SQL.IS_OPEN(destination_cursor) THEN
DBMS_SQL.CLOSE_CURSOR(destination_cursor);
Dbms_Output.put_line(ignore);
END IF;
RAISE;
END;
#######################################
下面存储过程例子通过传送源代码和目标表,然后从原始表拷贝数据行到目标表。
create table temp_table_1 as select * from temp_table where 1=3;
目标表temp_table_1的列类型和temp_table完全一样,这是为了测试该存储过程的使用规则
########################################
SQL> execcopy('temp_table','temp_table_1'); --temp_table是目标数据插入到1表中
PL/SQL proceduresuccessfully completed
Executed in 0.016 seconds