create or replace
PROCEDURE TEST3
(
v_startDate IN DATE DEFAULT NULL ,
v_endDate IN DATE DEFAULT NULL ,
v_orgUnitID IN VARCHAR2 DEFAULT NULL ,
cv_1 OUT SYS_REFCURSOR
)
AS
-----------------2.创建日期表#t_date--------------------------------------
--循环插入查询期间每天日期
v_dt DATE;
beginEXECUTE IMMEDIATE ' TRUNCATE TABLE tt_t_2 ';
EXECUTE IMMEDIATE ' TRUNCATE TABLE tt_t1_2 ';
EXECUTE IMMEDIATE ' TRUNCATE TABLE tt_t2_2 ';
EXECUTE IMMEDIATE ' TRUNCATE TABLE tt_t3_2 ';
EXECUTE IMMEDIATE ' TRUNCATE TABLE tt_t4_2 ';
------------------8.删除临时表---------------------------------------
EXECUTE IMMEDIATE ' TRUNCATE TABLE tt_t_date_12 ';
--DELETE FROM tt_t_2;
declare strSql varchar2(9999);
begin
strSql:='insert into tt_t_1
SELECT TO_DATE(sysdate) FPeriodDate ,
TAB.FOrgUnitID ,
TAB.FAccountID ,
TAV.FNumber AccountNo ,
TAV.FName_L2 AccountName ,
(CASE
WHEN TAV.FNumber = ''1472'' THEN TAB.FBeginBalanceFor
ELSE -1*TAB.FBeginBalanceFor
END) FBeginBalance ,
0 FDebitFOR ,
0 FCreditFOR ,
TAHG.FCustomerID ,
TC.FName_L2 CustomerName
FROM T_GL_AssistBalance TAB
JOIN T_BD_AssistantHG TAHG
ON TAB.FAssistGrpID = TAHG.FID
JOIN T_BD_Customer TC
ON TAHG.FCustomerID = TC.FID
JOIN T_BD_AccountView TAV
ON TAB.FAccountID = TAV.FID
WHERE TAB.FOrgUnitID = ''Uz+yv9PkRsulkySEbAxX1MznrtQ=''
AND TC.FUsedStatus IS NOT NULL
AND TC.FUsedStatus <> 2
AND TC.FBrowseGroupID = ''62+x43EkToayLR4lG0upIXolaaI=''
AND ( TAV.FNumber = ''1472''
OR TAV.FNumber = ''2402'' )
and TAB.fperiodyear = to_char('''||v_startDate||''',''yyyy'')
AND TAB.fperiodnumber = to_char(sysdate,''mm'')
AND TAB.FBalType = 1
AND TAB.FCurrencyID = ''11111111-1111-1111-1111-111111111111DEB58FDC'' ';
EXECUTE IMMEDIATE strSql;
end;其中:to_char('''||v_startDate||''',''yyyy''),将参数换成to_char(sysdate,''yyyy''),就不会报错,请问 参数格式怎么样才正确?
解决方案 »
- oracle恢复到另一台server
- oracle数据迁移问题
- 关于SQL字符串拼接
- 在PLSQL里写存储过程中怎么传参数
- 关于用户下的表的复制,谁来帮我看看,原题目是英文的,不是很难,大家来帮忙谢谢了
- Oracle能否直接和MSSQL交互?
- OCI的问题
- 小弟,这周终于开始学oracle了,为什么我在 SQL plus 里边创建数据库和创建表空间,提示说没有权限.
- 各位大侠,请教“未指定错误”怎么解决????急。
- 有什么工具做SQL SERVER与Oracle之间转换数据,可以直接把sql server的text类型转换成Oracle的CLOB类型
- oracle触发器问题,,急~~
- Oracle安装-实例化 EM 配置文件时出错
to_char('||v_startDate||',''yyyy'')
应该改为:
to_char(v_startDate,''yyyy'')
变量这里不需要单引号,可以用chr(39)来替换单引号,看着方便点SQL> -- Created on 2013/9/26 by ZHOUXX
SQL> declare
2 -- Local variables here
3 i integer;
4 v_startDate DATE DEFAULT NULL ;
5 v_date varchar2(4000);
6 v_date1 varchar2(32);
7 begin
8 -- Test statements here
9 v_startDate:=sysdate;
10
11 select 'select to_char('||v_startDate||','||chr(39)||'yyyy'||chr(39)||') from dual' into v_date from dual;
12 dbms_output.put_line('v_date=='||v_date);
13
14 EXECUTE IMMEDIATE v_date into v_date1;
15 dbms_output.put_line('v_date1=='||v_date1);
16 end;
17 /
v_date==select to_char(26-9月 -13,'yyyy') from dual
declare
-- Local variables here
i integer;
v_startDate DATE DEFAULT NULL ;
v_date varchar2(4000);
v_date1 varchar2(32);
begin
-- Test statements here
v_startDate:=sysdate; select 'select to_char('||v_startDate||','||chr(39)||'yyyy'||chr(39)||') from dual' into v_date from dual;
dbms_output.put_line('v_date=='||v_date); EXECUTE IMMEDIATE v_date into v_date1;
dbms_output.put_line('v_date1=='||v_date1);
end;
ORA-00907: missing right parenthesis
ORA-06512: at line 15
SQL>
SQL>
你的v_startDate本身为date类型,你写成'''||v_startDate||'''就被解析成字符类型了,to_char()肯定报错,如1楼直接变量解析就可以了