下面是我写的一个简单例子,可以动态执行SQL语句,主要是EXECUTE IMMEDIATE的使用,你修改一下就行了,但此句必须在oracle8.0.5以上的版本才能运行。CREATE OR REPLACE PROCEDURE "UPDATEDATA" ( tablename IN VARCHAR2, sqlstring IN VARCHAR2, constring IN VARCHAR2, execresult OUT VARCHAR2 ) AS tstr VARCHAR2 (500); BEGIN IF (sqlstring IS NULL) THEN execresult := 'F'; ELSE IF (constring IS NULL) THEN tstr := 'update ' || tablename || ' set ' || sqlstring; ELSE tstr := 'update ' || tablename || ' set ' || sqlstring || ' where ' || constring; END IF; EXECUTE IMMEDIATE tstr; COMMIT; execresult := 'T'; END IF; EXCEPTION WHEN OTHERS THEN ROLLBACK; execresult := 'F' || SQLCODE; END "UPDATEDATA"; /
SQL> create or replace procedure sp_view (p_out out varchar2) 2 is 3 str varchar2(200); 4 begin 5 str := 'create or replace view v_test as select * from cat'; 6 execute immediate str; 7 p_out := 'ok'; 8 end; 9 /过程已创建。SQL> variable p_out varchar2(10) SQL> exec sp_view(:p_out);PL/SQL 过程已成功完成。SQL> print p_outP_OUT -------------------------------- okSQL> select * from v_test;TABLE_NAME TABLE_TYPE ------------------------------ ----------- TEST TABLE TEST1 TABLE TEST2 TABLE V_TEST VIEWSQL>
2.例如: procedure pro_name(参数1 in varchar2, 参数2 out varchar2) 调用:
declare canshu varchar2(100);
begin
pro_name('XXXXXXXXX',canshu);
---变量canshu就得到程序返回值end;HQ.Wang 海清
tablename IN VARCHAR2,
sqlstring IN VARCHAR2,
constring IN VARCHAR2,
execresult OUT VARCHAR2
)
AS
tstr VARCHAR2 (500);
BEGIN
IF (sqlstring IS NULL)
THEN
execresult := 'F';
ELSE
IF (constring IS NULL)
THEN
tstr := 'update ' || tablename || ' set ' || sqlstring;
ELSE
tstr :=
'update '
|| tablename
|| ' set '
|| sqlstring
|| ' where '
|| constring;
END IF;
EXECUTE IMMEDIATE tstr;
COMMIT;
execresult := 'T';
END IF;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
execresult := 'F' || SQLCODE;
END "UPDATEDATA";
/
2 is
3 str varchar2(200);
4 begin
5 str := 'create or replace view v_test as select * from cat';
6 execute immediate str;
7 p_out := 'ok';
8 end;
9 /过程已创建。SQL> variable p_out varchar2(10)
SQL> exec sp_view(:p_out);PL/SQL 过程已成功完成。SQL> print p_outP_OUT
--------------------------------
okSQL> select * from v_test;TABLE_NAME TABLE_TYPE
------------------------------ -----------
TEST TABLE
TEST1 TABLE
TEST2 TABLE
V_TEST VIEWSQL>