CREATE OR REPLACE PROCEDURE test
AS
data1 VARCHAR2(100);
data2 VARCHAR2(100);
cursor mycursor is select "FName_L2" from "T_CSL_RptItem";
begin
open mycursor;
loop
fetch mycursor into data1;
exit when mycursor%notfound;
select "FFieldName" into data2 from "T_CSl_ItemFieldMapped" t1 inner join "T_CSL_RptItem" t2 on t1."FItemID"=t2."FID" and t2."FName_L2"='||data1||';
dbms_output.put_line(data2);
end loop;
close mycursor;
end;
存储过程创建成功,但是执行的时候就报ora 01403 no data found错误,
补充:应该是在 select "FFieldName" into data2 from "T_CSl_ItemFieldMapped" t1 inner join "T_CSL_RptItem" t2 on t1."FItemID"=t2."FID" and t2."FName_L2"='||data1||';
这里报错。哪位大侠帮忙解决解决呀!呵呵
AS
data1 VARCHAR2(100);
data2 VARCHAR2(100);
cursor mycursor is select "FName_L2" from "T_CSL_RptItem";
begin
open mycursor;
loop
fetch mycursor into data1;
exit when mycursor%notfound;
select "FFieldName" into data2 from "T_CSl_ItemFieldMapped" t1 inner join "T_CSL_RptItem" t2 on t1."FItemID"=t2."FID" and t2."FName_L2"='||data1||';
dbms_output.put_line(data2);
end loop;
close mycursor;
end;
存储过程创建成功,但是执行的时候就报ora 01403 no data found错误,
补充:应该是在 select "FFieldName" into data2 from "T_CSl_ItemFieldMapped" t1 inner join "T_CSL_RptItem" t2 on t1."FItemID"=t2."FID" and t2."FName_L2"='||data1||';
这里报错。哪位大侠帮忙解决解决呀!呵呵
关键看你想如何处理这个错误
begin
select "FFieldName" into data2 from "T_CSl_ItemFieldMapped" t1 inner join "T_CSL_RptItem" t2 on t1."FItemID"=t2."FID" and t2."FName_L2"='||data1||';
exception
when NO_DATA_FOUND then
--这里写你的没有数据的处理情况。
end;
按照你说的,存储过程创建成功,但执行的时候又报 ora 00904:"inner" invalid identifier错
AS
data1 VARCHAR2(100);
data2 VARCHAR2(100);
strSql varchar2(5000);
cursor mycursor is select "FName_L2" from "T_CSL_RptItem";
begin
open mycursor;
loop
fetch mycursor into data1;
exit when mycursor%notfound;
begin
select "FFieldName" into data2 from "T_CSl_ItemFieldMapped" t1 inner join "T_CSL_RptItem" t2 on t1."FItemID"=t2."FID" and t2."FName_L2"='||data1||';
exception when no_data_found then data2:='F54';
end;
strSql:='insert into TB_Finance_Theme select seq_theme.nextval,
Company."FNumber" "公司编码",b."FNumber" "项目编码",ItemDataHd."FCurrencyID" "原币编码",
ItemDataHd."FTargetCurrencyID" "目标币别编码",ItemDataBd."FYear" "年份",ItemDataBd."FPeriod" "期间",
ItemDataHd."FDataSource" "报表类型(个别、汇总、合并)",'||data2||'"数据", ItemDataHd."FIsAdjusted" "调整状态(调整前、调整后)",
ItemDataBd."FDataElement" "取数类型"
from
"T_CSL_ItemDataEntry001" t1
inner join "T_CSL_ItemDataEntry" ItemDataBd ON ItemDataBd."FID" = t1."FID" and ItemDataBd."FValueType"=1
inner join "T_CSL_ItemData" ItemDataHd ON ItemDataHd."FID" = ItemDataBd."FItemDataID"
left join "T_ORG_BaseUnit" Company ON ItemDataHd."FCompanyID" = Company."FID"
inner join "T_CSl_ItemFieldMapped" a on a."FFieldName"='||data2||'
inner join "T_CSL_RptItem" b on b."FID"=a."FItemID" where ItemDataBd."FYear"=2007';
execute immediate strSql;
end loop;
close mycursor;
end;