我要定义一个变量,这个变量保存的是一句SQL中的一段,比如 where id = '1';然后下面的SQL组合这个变量的SQL查询,该怎么写?类似于如下这样:String tmpSql = " where id = '1' ";
String sql = "select * from table "+tmpSql ; //执行这个SQL
String sql = "select * from table "+tmpSql ; //执行这个SQL
v_sql := 'select * from table' || v_tmp;execute immdeiate v_sql;
SQL> declare
2 v_where_sql varchar2(500);
3 v_sql varchar2(4000);
4 v_ename varchar2(100);
5 begin
6 v_sql := 'select ename from emp';
7 v_where_sql := ' where empno=7499';
8 v_sql := v_sql || v_where_sql;
9 execute immediate v_sql
10 into v_ename;
11 dbms_output.put_line(v_ename);
12 end;
13 /
ALLEN
PL/SQL procedure successfully completed
into rest
using noUsedSiteId, siteTypeOfSp;
DBMS_OUTPUT.PUT_LINE(sql_siteOfSmartphone);怎么把执行的动态SQL输出出来呢?需要把传入的变量全输出出来,好跟踪执行的状态
你想输出整条的话直接拼接sql, 不要用绑定变量。或者你把sql语句replace下!个人愚见SQL> declare
2 v_where_sql varchar2(500);
3 v_sql varchar2(4000);
4 v_ename varchar2(100);
noUsedSiteId number;
5 begin
noUsedSiteId :=1;
6 v_sql := 'select ename from emp';
7 v_where_sql := ' where empno=:1';
8 v_sql := v_sql || v_where_sql;
9 execute immediate v_sql using noUsedSiteId;
v_sql := replace (v_sql,':1', noUsedSiteId);
11 dbms_output.put_line(v_sql);
12 end;
13 /