oracle函数怎么遍历二维数组,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;

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