17,382
社区成员




create or replace function GetCurrBal(qrp_rq IN VARCHAR2, --报表日期
qrp_code IN VARCHAR2 --币种
) return number IS
v_ye NUMBER;
begin
if qrp_rq is null OR UPPER(qrp_code) NOT IN ('RMB', 'CNY', 'USD') THEN
return null;
end if;
execute immediate 'select ' || upper(qrp_code) ||
'_YTD_BALANCE from CCB_GYB t WHERE TO_CHAR(T.ACCOUNTING_DATE,''YYYYMMDDHH24MISS'') =' || '''' ||
qrp_rq || ''''
INTO V_YE;
return(v_ye);
exception when no_data_found then return null;
end GetCurrBal;
2.
create or replace function GetCurrBal(qrp_rq IN VARCHAR2,qrp_code IN VARCHAR2)
return number
is
Result number;
begin
if qrp_code='RMB' then
begin
select RMB_YTD_BALANCE INTO Result FROM CCB_GYB WHERE ACCOUNTING_DATE=TO_DATE(qrp_rq,'yyyy-mm-dd');
end;
elsif qrp_code='CNY' then
begin
select CNY_YTD_BALANCE INTO Result FROM CCB_GYB WHERE ACCOUNTING_DATE=TO_DATE(qrp_rq,'yyyy-mm-dd');
end;
elsif qrp_code='USD' then
begin
select USD_YTD_BALANCE INTO Result FROM CCB_GYB WHERE ACCOUNTING_DATE=TO_DATE(qrp_rq,'yyyy-mm-dd');
end;
end if;
return(Result);
end GetCurrBal;
(1).select case when score<60 then '0<score<60'
when score>=60 and score<80 then '60<score<80'
when score>=80 then '80<score<100' end 成绩,
count(score) 人数
from chengji
group by case when score<60 then '0<score<60'
when score>=60 and score<80 then '60<score<80'
when score>=80 then '80<score<100' end;
--如果数据库没有<60的记录,就没有'0<score<60'这一行?
(2).select t.name,t.语文 语文,t.数学 数学,t.英语 英语,t.语文+t.数学+t.英语 总分
from (select name ,sum(decode(course,'语文',score,0)) as 语文,
sum(decode(course,'数学',score,0)) as 数学,
sum(decode(course,'英语',score,0)) as 英语
from chengji
group by name) t
--'总分'这一行怎么加上呢