--1. 创建用户kaifa(密码亦为kaifa),并分配connect,create table,resource权限
create user kaifa identified by kaifa;
grant connect ,create table ,resource to kaifa
--2
create table CCB_GYB
(
ACCOUNTING_DATE DATE,
RMB_YTD_BALANCE NUMBER,
CNY_YTD_BALANCE NUMBER,
USD_YTD_BALANCE NUMBER
)
create unique index CCB_GYB_IDX on CCB_GYB (ACCOUNTING_DATE)
create or replace function GetCurBal(grp_rq in varchar2,grp_code in varchar2)
return varchar2
as
res number;
begin
if grp_code='RMB' then
select RMB_YTD_BALANCE into res from CCB_GYB where ACCOUNTING_DATE=grp_rq;
return res;
elsif grp_code='CNY' then
select CNY_YTD_BALANCE into res from CCB_GYB where ACCOUNTING_DATE=grp_rq;
return res;
elsif grp_code='USD' then
select USD_YTD_BALANCE into res from CCB_GYB where ACCOUNTING_DATE=grp_rq;
return res;
else
res :=0;
return res;
end if;
end;
declare
a varchar2(20);
begin
a:=GetCurBal('sysdate','ere');
dbms_output.put_line('余额为:'||a);
end;
--