给你一个例子参考吧DECLARE v_SQLString VARCHAR2(200); v_PLSQLBlock VARCHAR2(200); BEGIN -- First create a temporary table, using a literal. Note that -- there is no trailing semicolon in the string. EXECUTE IMMEDIATE 'CREATE TABLE execute_table (col1 VARCHAR(10))'; -- Insert some rows using a string. Again, there is no -- trailing semicolon inside the string. FOR v_Counter IN 1..10 LOOP v_SQLString := 'INSERT INTO execute_table VALUES (''Row ' || v_Counter || ''')'; EXECUTE IMMEDIATE v_SQLString; END LOOP; -- Print out the contents of the table using an anonymous -- PL/SQL block. Here we put the entire block into a single -- string (including the semicolon). v_PLSQLBLock := 'BEGIN FOR v_Rec IN (SELECT * FROM execute_table) LOOP DBMS_OUTPUT.PUT_LINE(v_Rec.col1); END LOOP; END;'; -- And now we execute the anonymous block. EXECUTE IMMEDIATE v_PLSQLBlock; -- Finally, drop the table. EXECUTE IMMEDIATE 'DROP TABLE execute_table'; END; /
我要实现的类似如下: create or replace procedure test(tr_name in varchar2,o in varchar2,t in varchar2) is strsql varchar2(1000); begin strsql:='create or replace trigger '||tr_name|| 'before update on '||t|| 'begin p_tt(sys.login_user,t,''tt''); end;' execute immediate sqlstr; end; / ? 创建的过程带有编译错误。QL> show error ROCEDURE TEST 出现错误:INE/COL ERROR ------- ----------------------------------------------------------------- 1/3 PLS-00103: 出现符号 "EXECUTE"在需要下列之一时: .(*@%&=-+;</>atinmodnot rem<an exponent (**)><> or != or ~=>=<=<>andorlikebetween is nullis not||is dangling
两个地方错误,1、字符串语没有正确结束, 2、sqlstr没有定义。SQL> create or replace procedure test(tr_name in varchar2,o in varchar2,t in varchar2) 2 is 3 strsql varchar2(1000); 4 begin 5 strsql:='create or replace trigger '||tr_name|| 6 'before update 7 on '||t|| 8 'begin 9 p_tt(sys.login_user,t,''tt''); 10 end;'; 11 execute immediate strsql; 12 end; 13 /Procedure created
strsql:='create or replace trigger '||tr_name|| 'before update on '||t|| 'begin p_tt(sys.login_user,t,''tt''); end';
错了,strsql:='create or replace trigger '||tr_name|| 'before update on '||t|| 'begin p_tt(sys.login_user,t,''tt''); end;';
谢谢!:)可是过程是成功创建了,调用出了问题 SQL> exec test('tr_stu','y','s'); BEGIN test('tr_s','y','s'); END;* ERROR 位于第 1 行: ORA-04071: 缺少 BEFORE、AFTER 或 INSTEAD OF 关键字 ORA-06512: 在"YUANLINA.TEST", line 11 ORA-06512: 在line 1我明明写了"before update"呀
看错了,beckhambobo(beckham) 用的也是execute immediate,你看下面的例子吧: create or replace procedure update_stkid_of_fundtbl(v_tbl varchar2,v_col varchar2) as v_stkid number; v_stkcode varchar(10); v_sql varchar2(255); v_Cur integer; v_Ret integer; cursor c_stock is select stock_id,stock_code from stock a where exists ( select seq from JJ001 where rtrim(a.stock_code)=newstock); begin open c_stock; loop fetch c_stock into v_stkid,v_stkcode; exit when c_stock%NOTFOUND; v_sql :='update '||v_tbl||' set stock_id='||to_char(v_stkid)||' where UPPER('||v_col||')='''||to_char(rtrim(v_stkcode))||''''; v_Cur :=dbms_sql.open_cursor; dbms_sql.parse(v_Cur,v_sql,dbms_sql.v7); v_Ret :=dbms_sql.execute(v_Cur); dbms_sql.close_cursor(v_Cur); --dbms_output.put_line(v_sql); end loop; commit; close c_stock; end; /
v_SQLString VARCHAR2(200);
v_PLSQLBlock VARCHAR2(200);
BEGIN
-- First create a temporary table, using a literal. Note that
-- there is no trailing semicolon in the string.
EXECUTE IMMEDIATE
'CREATE TABLE execute_table (col1 VARCHAR(10))'; -- Insert some rows using a string. Again, there is no
-- trailing semicolon inside the string.
FOR v_Counter IN 1..10 LOOP
v_SQLString :=
'INSERT INTO execute_table
VALUES (''Row ' || v_Counter || ''')';
EXECUTE IMMEDIATE v_SQLString;
END LOOP; -- Print out the contents of the table using an anonymous
-- PL/SQL block. Here we put the entire block into a single
-- string (including the semicolon).
v_PLSQLBLock :=
'BEGIN
FOR v_Rec IN (SELECT * FROM execute_table) LOOP
DBMS_OUTPUT.PUT_LINE(v_Rec.col1);
END LOOP;
END;'; -- And now we execute the anonymous block.
EXECUTE IMMEDIATE v_PLSQLBlock; -- Finally, drop the table.
EXECUTE IMMEDIATE 'DROP TABLE execute_table';
END;
/
create or replace procedure test(tr_name in varchar2,o in varchar2,t in varchar2)
is
strsql varchar2(1000);
begin
strsql:='create or replace trigger '||tr_name||
'before update
on '||t||
'begin
p_tt(sys.login_user,t,''tt'');
end;'
execute immediate sqlstr;
end;
/
? 创建的过程带有编译错误。QL> show error
ROCEDURE TEST 出现错误:INE/COL ERROR
------- -----------------------------------------------------------------
1/3 PLS-00103: 出现符号 "EXECUTE"在需要下列之一时:
.(*@%&=-+;</>atinmodnot
rem<an exponent (**)><> or != or ~=>=<=<>andorlikebetween
is nullis not||is dangling
2、sqlstr没有定义。SQL> create or replace procedure test(tr_name in varchar2,o in varchar2,t in varchar2)
2 is
3 strsql varchar2(1000);
4 begin
5 strsql:='create or replace trigger '||tr_name||
6 'before update
7 on '||t||
8 'begin
9 p_tt(sys.login_user,t,''tt'');
10 end;';
11 execute immediate strsql;
12 end;
13 /Procedure created
'before update
on '||t||
'begin
p_tt(sys.login_user,t,''tt'');
end';
'before update
on '||t||
'begin
p_tt(sys.login_user,t,''tt'');
end;';
SQL> exec test('tr_stu','y','s');
BEGIN test('tr_s','y','s'); END;*
ERROR 位于第 1 行:
ORA-04071: 缺少 BEFORE、AFTER 或 INSTEAD OF 关键字
ORA-06512: 在"YUANLINA.TEST", line 11
ORA-06512: 在line 1我明明写了"before update"呀
你看看下面这样用就可以
1 declare
2 str varchar2(100);
3 begin
4 str:='create table test_exec'||
5 '(id number)';
6 execute immediate str;
7* end;
SQL> /PL/SQL 过程已成功完成。
可是一放到命名的过程中就不行了,这是怎么回事啊? 1 create or replace procedure dynamic_t(table_name in varchar2)
2 is
3 str varchar2(100);
4 begin
5 str:='create table'||table_name||
6 '(id number)';
7 execute immediate str;
8* end;
SQL> /过程已创建。SQL> exec dynamic_t('t_1');
BEGIN dynamic_t('t_1'); END;*
ERROR 位于第 1 行:
ORA-00901: 无效 CREATE 命令
ORA-06512: 在"YUANLINA.DYNAMIC_T", line 7
ORA-06512: 在line 1
create or replace procedure update_stkid_of_fundtbl(v_tbl varchar2,v_col varchar2)
as
v_stkid number;
v_stkcode varchar(10);
v_sql varchar2(255);
v_Cur integer;
v_Ret integer;
cursor c_stock is select stock_id,stock_code from stock a where exists ( select seq from JJ001 where rtrim(a.stock_code)=newstock);
begin
open c_stock;
loop
fetch c_stock into v_stkid,v_stkcode;
exit when c_stock%NOTFOUND;
v_sql :='update '||v_tbl||' set stock_id='||to_char(v_stkid)||' where UPPER('||v_col||')='''||to_char(rtrim(v_stkcode))||'''';
v_Cur :=dbms_sql.open_cursor;
dbms_sql.parse(v_Cur,v_sql,dbms_sql.v7);
v_Ret :=dbms_sql.execute(v_Cur);
dbms_sql.close_cursor(v_Cur);
--dbms_output.put_line(v_sql);
end loop;
commit;
close c_stock;
end;
/