السلام عليكم
يا إخوان أنا عندي procedure و يوجد بها 2 parameters و هم الyear و اسم الuser
و هذه ترجع القيمة على شكل ref cursor
فكيف أعمل لها run في الtoad
الprocedure
CREATE OR REPLACE procedure MIS_INT.p_get_budget_p(year_v IN varchar2,usr_v IN VARCHAR2, cursor_v OUT Types.cursor_type )
AS
begin
OPEN cursor_v FOR
SELECT ACCTID, ACCTDESC, SUM(BUDGET) AS BUDGET,SUM(ACTUALS) AS ACTUALS,
SUM(AVAILABLE) AS AVAILABLE,SUM(USED) AS USED FROM
(
SELECT "GLAFS"."ACCTID","GLAMF"."ACCTDESC",
("GLAFS"."NETPERD1"+ "GLAFS"."NETPERD2"+
"GLAFS"."NETPERD3"+ "GLAFS"."NETPERD4"+ "GLAFS"."NETPERD5"+
"GLAFS"."NETPERD6"+ "GLAFS"."NETPERD7"+ "GLAFS"."NETPERD8"+
"GLAFS"."NETPERD9"+ "GLAFS"."NETPERD10"+ "GLAFS"."NETPERD11"+
"GLAFS"."NETPERD12") as Budget,
(SELECT "GLAFSA"."NETPERD1"+ "GLAFSA"."NETPERD2"+
"GLAFSA"."NETPERD3"+ "GLAFSA"."NETPERD4"+ "GLAFSA"."NETPERD5"+
"GLAFSA"."NETPERD6"+ "GLAFSA"."NETPERD7"+ "GLAFSA"."NETPERD8"+
"GLAFSA"."NETPERD9"+ "GLAFSA"."NETPERD10"+ "GLAFSA"."NETPERD11"+
"GLAFSA"."NETPERD12"
FROM "CBBD"."GLAFS" "GLAFSA" inner join "CBBD"."GLAMF" "GLAMFA" on
"GLAMFA"."ACCTID" = "GLAFSA"."ACCTID"
WHERE "GLAFSA"."FSCSYR"="GLAFS"."FSCSYR" AND "GLAFSA"."FSCSDSG"='A'
AND "GLAFSA"."CURNTYPE"="GLAFS"."CURNTYPE"
and "GLAFSA"."ACCTID" ="GLAFS"."ACCTID" ) as Actuals,
("GLAFS"."NETPERD1"+ "GLAFS"."NETPERD2"+
"GLAFS"."NETPERD3"+ "GLAFS"."NETPERD4"+ "GLAFS"."NETPERD5"+
"GLAFS"."NETPERD6"+ "GLAFS"."NETPERD7"+ "GLAFS"."NETPERD8"+
"GLAFS"."NETPERD9"+ "GLAFS"."NETPERD10"+ "GLAFS"."NETPERD11"+
"GLAFS"."NETPERD12")-(SELECT "GLAFSA"."NETPERD1"+ "GLAFSA"."NETPERD2"+
"GLAFSA"."NETPERD3"+ "GLAFSA"."NETPERD4"+ "GLAFSA"."NETPERD5"+
"GLAFSA"."NETPERD6"+ "GLAFSA"."NETPERD7"+ "GLAFSA"."NETPERD8"+
"GLAFSA"."NETPERD9"+ "GLAFSA"."NETPERD10"+ "GLAFSA"."NETPERD11"+
"GLAFSA"."NETPERD12"
FROM "CBBD"."GLAFS" "GLAFSA" inner join "CBBD"."GLAMF" "GLAMFA" on
"GLAMFA"."ACCTID" = "GLAFSA"."ACCTID"
WHERE "GLAFSA"."FSCSYR"="GLAFS"."FSCSYR" AND "GLAFSA"."FSCSDSG"='A'
AND "GLAFSA"."CURNTYPE"="GLAFS"."CURNTYPE"
and "GLAFSA"."ACCTID" ="GLAFS"."ACCTID" ) as Available,
(SELECT "GLAFSA"."NETPERD1"+ "GLAFSA"."NETPERD2"+
"GLAFSA"."NETPERD3"+ "GLAFSA"."NETPERD4"+ "GLAFSA"."NETPERD5"+
"GLAFSA"."NETPERD6"+ "GLAFSA"."NETPERD7"+ "GLAFSA"."NETPERD8"+
"GLAFSA"."NETPERD9"+ "GLAFSA"."NETPERD10"+ "GLAFSA"."NETPERD11"+
"GLAFSA"."NETPERD12"
FROM "CBBD"."GLAFS" "GLAFSA" inner join "CBBD"."GLAMF" "GLAMFA" on
"GLAMFA"."ACCTID" = "GLAFSA"."ACCTID"
WHERE "GLAFSA"."FSCSYR"="GLAFS"."FSCSYR" AND "GLAFSA"."FSCSDSG"='A'
AND "GLAFSA"."CURNTYPE"="GLAFS"."CURNTYPE"
and "GLAFSA"."ACCTID" ="GLAFS"."ACCTID" )/("GLAFS"."NETPERD1"+ "GLAFS"."NETPERD2"+
"GLAFS"."NETPERD3"+ "GLAFS"."NETPERD4"+ "GLAFS"."NETPERD5"+
"GLAFS"."NETPERD6"+ "GLAFS"."NETPERD7"+ "GLAFS"."NETPERD8"+
"GLAFS"."NETPERD9"+ "GLAFS"."NETPERD10"+ "GLAFS"."NETPERD11"+
"GLAFS"."NETPERD12") * 100 as Used
FROM "CBBD"."GLAFS" "GLAFS" RIGHT OUTER join "CBBD"."GLAMF" "GLAMF" on
"GLAMF"."ACCTID" = "GLAFS"."ACCTID"
WHERE "GLAFS"."FSCSYR" = year_v AND "GLAFS"."FSCSDSG"='1' AND "GLAFS"."CURNTYPE"='F'
and "GLAMF"."ACSEGVAL02" IN (SELECT BEGINID FROM CBBD.GLGSSEG WHERE
"GLGSSEG"."USER" = usr_v AND SWCANSEE = '1')
UNION ALL
SELECT "GLAMF"."ACCTID","GLAMF"."ACCTDESC",
0 as Budget,
0 as Actuals,
0 as Available,
0 as Used
FROM "CBBD"."GLAMF" "GLAMF"
WHERE "GLAMF"."ACSEGVAL02" IN (SELECT BEGINID FROM CBBD.GLGSSEG WHERE
"GLGSSEG"."USER" = usr_v AND SWCANSEE = '1')
)AA
GROUP BY
ACCTID, ACCTDESC ;
--
exception when others then
raise;
end p_get_budget_p;