用oracle的内部存储过程包dbms_sql构造sql,然后执行。见下面的例子(摘自sql programing)PROCEDURE drop_object (object_type_in IN VARCHAR2, object_name_in IN VARCHAR2) IS cursor_id INTEGER; BEGIN /* || Open a cursor which will handle the dynamic SQL statement. || The function returns the pointer to that cursor. */ cursor_id := DBMS_SQL.OPEN_CURSOR; /* || Parse and execute the drop command which is formed through || concatenation of the arguments. */ DBMS_SQL.PARSE (cursor_id, 'DROP ' || object_type_in || ' ' || object_name_in, DBMS_SQL.NATIVE); /* Close the cursor. */ DBMS_SQL.CLOSE_CURSOR (cursor_id); EXCEPTION /* If any problem arises, also make sure the cursor is closed. */ WHEN OTHERS THEN DBMS_SQL.CLOSE_CURSOR (cursor_id); END;
EXECUTE IMMEDATE SQL_STR;
(object_type_in IN VARCHAR2, object_name_in IN VARCHAR2)
IS
cursor_id INTEGER;
BEGIN
/*
|| Open a cursor which will handle the dynamic SQL statement.
|| The function returns the pointer to that cursor.
*/
cursor_id := DBMS_SQL.OPEN_CURSOR;
/*
|| Parse and execute the drop command which is formed through
|| concatenation of the arguments.
*/
DBMS_SQL.PARSE
(cursor_id,
'DROP ' || object_type_in || ' ' || object_name_in,
DBMS_SQL.NATIVE);
/* Close the cursor. */
DBMS_SQL.CLOSE_CURSOR (cursor_id);
EXCEPTION
/* If any problem arises, also make sure the cursor is closed. */
WHEN OTHERS
THEN
DBMS_SQL.CLOSE_CURSOR (cursor_id);
END;