oracle存储过程之游标查询,一维和二维数组,循环,条件判断例子

create or replace procedure init_permission(custId   IN varchar) is

  permId varchar(20);
  counter BINARY_INTEGER;
begin

  ---------------------------------------------

  DECLARE

--定义许可记录类型
TYPE type_permission IS RECORD
( appid varchar(1),domain varchar(20),action varchar(40),descript varchar(100));

--定义许可二维数组类型
TYPE type_permission_array IS TABLE OF type_permission
INDEX BY BINARY_INTEGER;

--定义字符串一维数组类型
TYPE type_str_array IS TABLE OF varchar(30) INDEX BY BINARY_INTEGER;


--创建许可二维数组
permArr type_permission_array;
--创建许可记录
perm type_permission;

--创建账户ID数组
acctIdArr type_str_array;

--创建用户ID数组
userIdArr type_str_array;


--初始化数组
begin

--初始化许可二维数组
---
perm.appid:=2;
perm.domain:='transfer';
perm.action:='00901';
perm.descript:='付款到关联账户';

permArr(1) := perm;

---
perm.appid:=2;
perm.domain:='transfer';
perm.action:='00700';
perm.descript:='付款到银行-单笔发起';

permArr(2) := perm;


--初始化账户ID数组
--定义查询账户ID游标
DECLARE cursor acctCursor(custId varchar) is
select  a.acct_id,a.acct_name from acps_acct_mast a where a.cust_id=custId;
begin

--循环读取账户ID游标,并初始化账户ID数组
counter:=1;
for acct in acctCursor(custId) loop
acctIdArr(counter):=acct.acct_id;
counter:=counter+1;
end loop;
--结束循环

end;
--结束定义账户ID游标



--初始化用户ID数组
--定义查询用户ID游标
DECLARE cursor userCursor(custId varchar) is
select t.user_id,t.oper_id from user_info t where t.cust_id=custId;
begin

--循环读取用户ID游标,并初始化用户ID数组
counter:=1;
for usr in userCursor(custId) loop
userIdArr(counter):=usr.user_id;
counter:=counter+1;
end loop;
--结束循环

end;
--结束定义用户ID游标









--循环许可二维数组
FOR i IN 1 .. permArr.count LOOP

--循环账户ID数组
FOR j IN 1 .. acctIdArr.count LOOP

--获取许可ID
select security_permission_id_seq.nextval into permId from dual;


--插入该账户的许可
insert into security_permission
  (PERMISSION_ID,
   ACTION,
   INSTANCE,
   APPID,
   DOMAIN,
   DESCRIPTION,
   CREATED_TS,
   LAST_UPD_TS,
   LAST_UPD_TRANCODE)
values
  (permId,
   permArr(i).action,
   acctIdArr(j),
   permArr(i).appid,
   permArr(i).domain,
   permArr(i).descript,
   sysdate,
   sysdate,
   '');


--循环用户ID数组
FOR k IN 1 .. userIdArr.count LOOP

--插入用户与许可关联记录
insert into security_user_permission (SQ, USER_ID, PERMISSION_ID, DESCRIPTION, CREATED_TS, LAST_UPD_TS, LAST_UPD_TRANCODE)
values (SECURITY_USER_PERMISSION_SEQ.Nextval, userIdArr(k), permId, '', sysdate, sysdate, '');



end loop;
--结束循环用户ID数组

end loop;
--结束循环账户ID数组

end loop;
--结束循环许可二维数组







end;
--结束数组初始化





  ---------------------------------------------

end;






-----------------------------
--call init_permission('fhwc2');

 

和

create or replace procedure init_corp(custId   IN varchar,
                                      custName IN varchar) is

  acctId varchar(20);
  email  varchar(30);
begin

  ---------------------------------------------
  --企业邮箱
  email := custId || '@sys.com';

  --创建1个企业客户
  register_cust_corp(custId, custName, email);

  --创建3个不同角色的企业用户
  register_user(email,
                '001',
                custId || '业务员',
                '22',
                custId);
  register_user(email,
                '101',
                custId || '财务员',
                '21',
                custId);
  register_user(email,
                '999',
                custId || '管理员',
                '20',
                custId);

  --定义一维数组
  declare

    TYPE my_array IS TABLE OF varchar(30) INDEX BY BINARY_INTEGER;
    --创建权限数组
    txnCds my_array;

  BEGIN
    --初始化权限数组
    txnCds(1) := '00700';
    txnCds(2) := '00900';
    txnCds(3) := '00901';
    txnCds(4) := '00100';
    txnCds(5) := '00500';
    txnCds(6) := '00800';

    --创建账户

    --循环创建三个账户
    FOR i IN 1 .. 3 LOOP

      --初始化账户ID
      select lpad(ACPS_TRAN_SQ_SEQ.Nextval, 19, '0') into acctId from dual;

      --创建1个普通人民币账户
      register_acct(acctId, 'SYS普通人民币账户' || i, custId);

      --循环开通交易权限
      FOR i IN 1 .. txnCds.count LOOP
        --开通账户交易权限
        open_acct_tran_pri(acctId, txnCds(i));
      END LOOP;
      --结束循环

    END LOOP;
    --结束循环创建三个账户

  END;

  ---------------------------------------------

end;

  ---------------------------------------------
  --call init_corp('f2','方红文2');
  ----------------------------------------------------

 

还有

 

create or replace procedure register_user(email    IN varchar,
                                          operId   IN varchar,
                                          operName IN varchar,
                                          userType IN varchar,
                                          custId   IN varchar) is
 userId varchar(20);
begin

--获取用户ID
select USER_INFO_SEQ.Nextval into userId from dual;

--如果是财务员和管理员,则打开工作流审批权限
 if(userType='20' or userType='21') then

 --创建工作流审批权限
 insert into SECURITY_USER_ROLE(SQ,USER_ID,ROLE_ID,APPID,CREATED_TS,LAST_UPD_TS)
 values(SECURITY_USER_ROLE_SEQ.Nextval,userId,'2','2',sysdate,sysdate);

 end if;


end;

---------------------------------------------