str2 varchar2(100);
begin
str2:='CLTX';--表名
if cltxpro%isopen =false then
open cltxpro for select HPHM,HPYS from ||str2;
fetch cltxpro into hphms,hpyss;
end if;
......
end;
表名作为变量,为什么编译出错?怎么样把str2作为表名加上去
begin
str2:='CLTX';--表名
if cltxpro%isopen =false then
open cltxpro for select HPHM,HPYS from ||str2;
fetch cltxpro into hphms,hpyss;
end if;
......
end;
表名作为变量,为什么编译出错?怎么样把str2作为表名加上去
错了,
应该是
open cltxpro for 'select HPHM,HPYS from' ||str2;
改为
open cltxpro for 'select HPHM,HPYS from '||str2;
sql_where := ' JGSJ between to_date('||vcBeginTime||',yyyy-mm-dd hh24:mi:ss) and to_date('||vcEndTime||',yyyy-mm-dd hh24:mi:ss)';
为什么总是报缺少右括号?missing right parenthesis.是格式化要加单引号转义吗,怎么加?
字符串的拼接,两个单引号表示一个单引号你SQL里需要用到单引号在时间格式的设定上,即yyyy-mm-dd hh24:mi:ss
sql_where := ' JGSJ between to_date('||vcBeginTime||','||'''yyyy-mm-dd hh24:mi:ss'''||') and to_date('||vcEndTime||','||'''yyyy-mm-dd hh24:mi:ss'''||')';
v_sql varchar2(1000):='select count(*) from product where product_date between :1 and :2';
v_cnt number;
begin
execute immediate v_sql into v_cnt
using to_date('201005','yyyymm'),to_date('20100601','yyyymmdd');
dbms_output.put_line(v_cnt);
end;
declare
v_sql varchar2(1000):='select count(*) from product';
v_cnt number;
v_start varchar2(10):='201005';
v_end varchar2(10):='201006';
v_whr varchar2(1000):=' where product_date between to_date('||v_start||','
||chr(39)||'yyyymm'||chr(39)||') and to_date(' ||v_end||','
||chr(39)||'yyyymm'||chr(39)||')';
begin
execute immediate v_sql||v_whr into v_cnt;
dbms_output.put_line(v_cnt);
end;