试试这个:
CREATE OR REPLACE PROCEDURE procA (
p_Description IN VARCHAR2) IS
v_strCreate VARCHAR2(100);
v_strDrop VARCHAR2(100);
BEGIN
v_strDrop := 'DROP TABLE AA';
BEGIN
EXECUTE IMMEDIATE v_strDrop;
COMMIT;
v_strCreate := 'CREATE TABLE AA' || p_Description;
EXECUTE IMMEDIATE v_strCreate;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
END ProcAA;
CREATE OR REPLACE PROCEDURE procA (
p_Description IN VARCHAR2) IS
v_strCreate VARCHAR2(100);
v_strDrop VARCHAR2(100);
BEGIN
v_strDrop := 'DROP TABLE AA';
BEGIN
EXECUTE IMMEDIATE v_strDrop;
COMMIT;
v_strCreate := 'CREATE TABLE AA' || p_Description;
EXECUTE IMMEDIATE v_strCreate;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
END ProcAA;
你使用dbms_sql吧
8.05只能用dbms_sql
用这个。
CREATE OR REPLACE PROCEDURE procA (
p_Description IN VARCHAR2) IS
v_strCreate VARCHAR2(100);
v_strDrop VARCHAR2(100);
C_SQL NUMBER;
RET NUMBER;
P_ERROR INTEGER;
BEGIN
v_strDrop := 'DROP TABLE AA';
BEGIN
C_SQL := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE( C_SQL, v_strDrop, DBMS_SQL.v7 ) ;
ret := DBMS_SQL.EXECUTE( C_SQL ) ;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
v_strCreate := 'CREATE TABLE AA' || p_Description;
DBMS_SQL.PARSE( C_SQL, v_strCreate, DBMS_SQL.v7 ) ;
ret := DBMS_SQL.EXECUTE( C_SQL ) ;
DBMS_SQL.CLOSE_CURSOR(C_SQL);
END;
http://gigabase.idi.ntnu.no/oradoc/appdev.901/a88876/adg09dyn.htm#26586
EXECUTE IMMEDIATE好像不能直接使用drop语句!
不要好象啊,自己寫個程序測試一下就知道了。
在oracle8i使用EXECUTE IMMEDIATE 'drop ...';是沒有問題的。