create or replace function get_fun_opposites(years nvarchar2,
mouth nvarchar2,
orgids nvarchar2,
datesources number,
mz_qu nvarchar2,
table_name nvarchar2)
return number is
q number(7);
sSqlStr nvarchar2(2000);
f number(7);
sSqlStrs nvarchar2(2000);
t number(7);
w number(7);
BEGIN sSqlStr := 'SELECT sum(' || mz_qu || ') FROM' || ' ' || table_name || '
LEFT JOIN (SELECT DISTINCT ddt.month_code,
substr(ddt.month_medium_desc,0,2) as yue,
ddt.quarter_medium_desc,
ddt.half_medium_desc,
substr(ddt.year_medium_desc,0,4) as nian
FROM DW_DIM_TIME ddt) ddt
ON
--ddt.nian = substr(powm.day_code,0,4)--年
ddt.nian =' || years || 'and ddt.yue=' || mouth ||
'LEFT JOIN (SELECT DISTINCT bds.data_source_code, bds.data_source_funame
FROM B_DATA_SOURCE bds) bds
ON bds.data_source_code = ' || datesources || '
LEFT JOIN (SELECT bdv.origid,
bdv.orgid,
NVL(bda.orgname, ''其他'') as orgname,
bdv.data_source
FROM B_DEPARTMENT_V bdv
LEFT JOIN B_DEPARTMENT bd
ON bd.orgid = bdv.origid
AND bd.data_source = bdv.data_source
LEFT JOIN B_DEPARTMENT_AS bda
ON bda.orgid = bdv.orgid
AND bda.data_source = bdv.data_source) dep
ON dep.data_source =' || datesources ||
'AND dep.origid =' || orgids; -- execute immediate sSqlStr into q EXECUTE IMMEDIATE
sSqlStr INTO q using mz_qu,table_name,years,mouth,datesources,orgids; sSqlStrs := 'SELECT sum(' || mz_qu || ') FROM' || ' ' || table_name || '
LEFT JOIN (SELECT DISTINCT ddt.month_code,
substr(ddt.month_medium_desc,0,2) as yue,
ddt.quarter_medium_desc,
ddt.half_medium_desc,
substr(ddt.year_medium_desc,0,4) as nian
FROM DW_DIM_TIME ddt) ddt
ON
--ddt.nian = substr(powm.day_code,0,4)--年
ddt.nian =' || (years - 1) ||
--and ddt.yue=substr(powm.day_code,5,6)--月
'and ddt.yue=' || mouth || 'LEFT JOIN (SELECT DISTINCT bds.data_source_code, bds.data_source_funame
FROM B_DATA_SOURCE bds) bds
ON bds.data_source_code = ' || datesources || '
LEFT JOIN (SELECT bdv.origid,
bdv.orgid,
NVL(bda.orgname,''其他'') as orgname,
bdv.data_source
FROM B_DEPARTMENT_V bdv
LEFT JOIN B_DEPARTMENT bd
ON bd.orgid = bdv.origid
AND bd.data_source = bdv.data_source
LEFT JOIN B_DEPARTMENT_AS bda
ON bda.orgid = bdv.orgid
AND bda.data_source = bdv.data_source) dep
ON dep.data_source =' || datesources ||
'AND dep.origid =' || orgids; EXECUTE IMMEDIATE
sSqlStrs
INTO f using mz_qu,table_name,years,mouth,datesources,orgids;
t := (q - f) / f; return t;
end;
出现
EXECUTE IMMEDIATE
sSqlStr INTO q using mz_qu,table_name,years,mouth,datesources,orgids;
表达式类型错误 !
请指导。
mouth nvarchar2,
orgids nvarchar2,
datesources number,
mz_qu nvarchar2,
table_name nvarchar2)
return number is
q number(7);
sSqlStr nvarchar2(2000);
f number(7);
sSqlStrs nvarchar2(2000);
t number(7);
w number(7);
BEGIN sSqlStr := 'SELECT sum(' || mz_qu || ') FROM' || ' ' || table_name || '
LEFT JOIN (SELECT DISTINCT ddt.month_code,
substr(ddt.month_medium_desc,0,2) as yue,
ddt.quarter_medium_desc,
ddt.half_medium_desc,
substr(ddt.year_medium_desc,0,4) as nian
FROM DW_DIM_TIME ddt) ddt
ON
--ddt.nian = substr(powm.day_code,0,4)--年
ddt.nian =' || years || 'and ddt.yue=' || mouth ||
'LEFT JOIN (SELECT DISTINCT bds.data_source_code, bds.data_source_funame
FROM B_DATA_SOURCE bds) bds
ON bds.data_source_code = ' || datesources || '
LEFT JOIN (SELECT bdv.origid,
bdv.orgid,
NVL(bda.orgname, ''其他'') as orgname,
bdv.data_source
FROM B_DEPARTMENT_V bdv
LEFT JOIN B_DEPARTMENT bd
ON bd.orgid = bdv.origid
AND bd.data_source = bdv.data_source
LEFT JOIN B_DEPARTMENT_AS bda
ON bda.orgid = bdv.orgid
AND bda.data_source = bdv.data_source) dep
ON dep.data_source =' || datesources ||
'AND dep.origid =' || orgids; -- execute immediate sSqlStr into q EXECUTE IMMEDIATE
sSqlStr INTO q using mz_qu,table_name,years,mouth,datesources,orgids; sSqlStrs := 'SELECT sum(' || mz_qu || ') FROM' || ' ' || table_name || '
LEFT JOIN (SELECT DISTINCT ddt.month_code,
substr(ddt.month_medium_desc,0,2) as yue,
ddt.quarter_medium_desc,
ddt.half_medium_desc,
substr(ddt.year_medium_desc,0,4) as nian
FROM DW_DIM_TIME ddt) ddt
ON
--ddt.nian = substr(powm.day_code,0,4)--年
ddt.nian =' || (years - 1) ||
--and ddt.yue=substr(powm.day_code,5,6)--月
'and ddt.yue=' || mouth || 'LEFT JOIN (SELECT DISTINCT bds.data_source_code, bds.data_source_funame
FROM B_DATA_SOURCE bds) bds
ON bds.data_source_code = ' || datesources || '
LEFT JOIN (SELECT bdv.origid,
bdv.orgid,
NVL(bda.orgname,''其他'') as orgname,
bdv.data_source
FROM B_DEPARTMENT_V bdv
LEFT JOIN B_DEPARTMENT bd
ON bd.orgid = bdv.origid
AND bd.data_source = bdv.data_source
LEFT JOIN B_DEPARTMENT_AS bda
ON bda.orgid = bdv.orgid
AND bda.data_source = bdv.data_source) dep
ON dep.data_source =' || datesources ||
'AND dep.origid =' || orgids; EXECUTE IMMEDIATE
sSqlStrs
INTO f using mz_qu,table_name,years,mouth,datesources,orgids;
t := (q - f) / f; return t;
end;
出现
EXECUTE IMMEDIATE
sSqlStr INTO q using mz_qu,table_name,years,mouth,datesources,orgids;
表达式类型错误 !
请指导。
解决方案 »
- 关于ORA-00054错误
- 用SQL Developer迁移SQL server 2000数据生成的SQL脚本错误问题
- mssql转oc的一个简单语句
- 这样的触发器和JOB如何建立
- 测试oracle连接的问题
- SQL求救!
- 用Oracle一定装客户端吗
- 请教 数据库update问题
- 请教:win2000 专业版上安装oracle9i 后配置监听程序为何不成功?
- 见数据库时有个初始的连接,,extproc_conection_data ,使用来做什么的,,?
- oracle 11g R2 ORA-12154:TNS:无法解析指定的连接标识符
- EXP-00056: 遇到 ORACLE 错误 12170导出数据库遇到错误,新手,希望遇到大神指点。
sSqlStr INTO q using mz_qu,table_name,years,mouth,datesources,orgids要么使用拼凑SQL,要么使用绑定变量,你现在是二者的大杂烩