oracle笔记6

--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;
--