简而言之,掌握这三点,就应该能拼装出能用的SQL。至于如果使用绑定变量输入输出,则需要使用into using关键字。
set serveroutput on; declare incoming date:=sysdate-10; outgoing int; begin execute immediate 'select COUNT(*) FROM user_objects where created > :incoming' into outgoing using incoming ; dbms_output.put_line(' count is: ' || outgoing); end;
使用using的好处,就是不用去转date类型为varchar类型,再转回去date类型这种繁琐的操作。
SQL代码如下:
declare incoming date:=sysdate-10; outgoing int; begin execute immediate 'insert into t_object(a) select COUNT(*) FROM user_objects where created > :incoming' into outgoing using incoming ; dbms_output.put_line(' count is: ' || outgoing); end;
ORA-01007: 变量不在选择列表中
ORA-06512: 在 line 6
tom这样解释这个错误:Followup November 24, 2004 - 7am Central time zone:
you have to use DBMS_SQL when the number of outputs is not known until run time.
Sql代码如下:
declare v_cursor number; --定义游标 v_string varchar2(2999); v_row number; begin v_string := 'insert into t_object(a) select COUNT(*) FROM user_objects where created > :incoming';--操作语句,其中:name是语句运行时才确定值的变量 v_cursor:=dbms_sql.open_cursor;--打开处理游标 dbms_sql.parse(v_cursor,v_string,dbms_sql.native);--解释语句 dbms_sql.bind_variable(v_cursor,':incoming',sysdate-30); --给变量赋值 v_row := dbms_sql.execute(v_cursor);--执行语句 dbms_sql.close_cursor(v_cursor);--关闭游标 --dbms_output.put_line(v_row); commit; exception when others then dbms_sql.close_cursor(v_cursor); --关闭游标 rollback; end;