oracle 多表存储过程,oracle多表插入的存储过程

今天花了一点时间实现了三个表的同时插入，即要么都插入成功，要么都不执行成功，所以用到了事务。

存储过程如下：split是一个自定义分割字符串的函数，splittable是定义的一个table类型的类型

create or replace procedure P_User( UserId in varchar,

UserName in varchar,

PassWord in varchar,

ExId in varchar,

TrueName in varchar,

Tel in varchar,

Email in varchar,

State in varchar,

Department in varchar,

MapX in number,

MapY in number,

DicCode in varchar,

RoleId in varchar)

is

begin

INSERT INTO S_User VALUES(UserId,UserName,PassWord,SYSDATE);

INSERT INTO s_userextend VALUES(ExId,TrueName,Tel,Email,State,SYSDATE,Department,MapX,MapY,DicCode,RoleID);

declare cursor my_cur

is select column_value FROM TABLE(CAST(split(RoleId, ',') as splittable));

v_stuname varchar(20);

begin

open my_cur;

fetch my_cur into v_stuname;

while my_cur%found

loop

INSERT INTO S_UserLinkRole(UserID,RoleID)values(UserId,v_stuname);

fetch my_cur into v_stuname;

end loop;

close my_cur;

end;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

end;