直接改成变量就行了,关键你变量是怎么传入呢。在哪用呢,查询窗口中,还是程序中,还是存储过程、函数中在查询窗口直接执行的
用了 declare声明变量,报SQL错误
用了 declare声明变量,报SQL错误
解决方案 »
- 求个例子--------oracle读写xml
- oracle 10g中create function出现Statement ignored错误,大家帮下忙啊!
- Oracle读取外部文件数据
- oracle 9i在哪里下载?
- Orcale8i我怎么老装不上去啊,即便装上去也用不了,请教高手!!(解决方法总汇)
- 求职
- 我想写一个能把任意字符串截去掉最后2位并输出的过程,只有用动态SQL才能实现吗?
- 紧急求助oracle7问题!!!谢谢!!!
- 怎样用PRO*C从ORACLE数据库中按一定条件提出一些记录并写到一个格式化的文本里?
- 如何才能提取txt文件中的数据,一个表格最后要转化成txt如何实现?
- 高分求教多条变成一条的sql语句,答对立即给分
- 提问:关于关联表更新的问题!
用了 declare声明变量,报SQL错误
我用plsql developer。查询窗口使用下面sql试试
select h.cbilltypecode, h.vnote, h.pk_corp, h.ts, '上游单据关闭' yuanyin
from nc_ic_general_h h
where h.cgeneralhid in (select datahid
from nc_synclog l
where l.synclog like '%关闭%'
and l.ts >= &开始时间
and l.ts <= &结束时间
and l.unitcode = &类型
and l.syncflag = '2')
union allselect h.cbilltypecode, h.vnote, h.pk_corp, h.ts, '上游单据不存在' yuanyin
from nc_ic_general_h h
where h.cgeneralhid in (select datahid
from nc_synclog l
where l.synclog like '%不存在%'
and l.ts >= &开始时间
and l.ts <= &结束时间
and l.unitcode = &类型
and l.syncflag = '2')
大体写了下
create global temporary table temp(cbilltypecode VARCHAR2(20),vnote VARCHAR2(20),pk_corp VARCHAR2(20),ts VARCHAR2(20),yuanyin VARCHAR2(20)) ON COMMIT DELETE ROWS;
declare
str_beg varchar2(20);
str_end varchar2(20);
str_uc varchar2(20);
str_sql varchar(1000);
begin
str_beg:='2014-03-26 00:00:00';
str_end:='2014-04-15 00:00:00';
str_uc:='1002';
str_sql='insert into temp select h.cbilltypecode, h.vnote, h.pk_corp, h.ts, ''上游单据关闭'' yuanyin
from nc_ic_general_h h
where h.cgeneralhid in (select datahid
from nc_synclog l
where l.synclog like ''%关闭%''
and l.ts >=:1
and l.ts <=:2
and l.unitcode =:3
and l.syncflag = '2')
union all
select h.cbilltypecode, h.vnote, h.pk_corp, h.ts, ''上游单据不存在'' yuanyin
from nc_ic_general_h h
where h.cgeneralhid in (select datahid
from nc_synclog l
where l.synclog like ''%不存在%''
and l.ts >=:1
and l.ts <=:2
and l.unitcode =:3
and l.syncflag = '2') ';
execute immediate str_sqlusing str_beg,str_end,str_uc;
end;SELECT * FROM temp;
其他简单方法我也不知道了,不建议使用这个。
相比之下,sqlserver中满足你这需求就很简单了。
declare
str_beg varchar2(20);
str_end varchar2(20);
str_uc varchar2(20);
str_sql varchar2(1000);
begin
str_beg:='2014-03-26 00:00:00';
str_end:='2014-04-15 00:00:00';
str_uc:='1002';
str_sql='insert into temp select h.cbilltypecode, h.vnote, h.pk_corp, h.ts, ''上游单据关闭'' yuanyin
from nc_ic_general_h h
where h.cgeneralhid in (select datahid
from nc_synclog l
where l.synclog like ''%关闭%''
and l.ts >=:1
and l.ts <=:2
and l.unitcode =:3
and l.syncflag = ''2'')
union all
select h.cbilltypecode, h.vnote, h.pk_corp, h.ts, ''上游单据不存在'' yuanyin
from nc_ic_general_h h
where h.cgeneralhid in (select datahid
from nc_synclog l
where l.synclog like ''%不存在%''
and l.ts >=:1
and l.ts <=:2
and l.unitcode =:3
and l.syncflag = ''2'') ';
execute immediate str_sql using str_beg,str_end,str_uc;
end;
用了 declare声明变量,报SQL错误
我用plsql developer。查询窗口使用下面sql试试
select h.cbilltypecode, h.vnote, h.pk_corp, h.ts, '上游单据关闭' yuanyin
from nc_ic_general_h h
where h.cgeneralhid in (select datahid
from nc_synclog l
where l.synclog like '%关闭%'
and l.ts >= &开始时间
and l.ts <= &结束时间
and l.unitcode = &类型
and l.syncflag = '2')
union allselect h.cbilltypecode, h.vnote, h.pk_corp, h.ts, '上游单据不存在' yuanyin
from nc_ic_general_h h
where h.cgeneralhid in (select datahid
from nc_synclog l
where l.synclog like '%不存在%'
and l.ts >= &开始时间
and l.ts <= &结束时间
and l.unitcode = &类型
and l.syncflag = '2')
原来是这么用的……
我还以为一定要和SQL SERVER一样先声明变量
额,报的缺失右括号,还少了什么吗……
我用union之前的单条执行,也是确实右括号,错误的红线在变量后边