create or replace procedure pro_sys_userrole(
v_userid in varchar,--传入参数
v_roleid in varchar,
v_num OUT VARCHAR2--返回参数
)
is
v_sql varchar2(10000);--变量
begin
begin
v_sql:='insert into sys_user_role(userid,roleid) values (:1,:2)';--:1占位符
execute immediate v_sql using v_userid,v_roleid;--执行语句using后面对应占位符
commit;
end;
begin
v_sql:='insert into sys_user_role@dblink_sys_userinfo(userid,roleid) values (:1,:2)';
execute immediate v_sql using v_userid,v_roleid;
commit;
end;
v_num := 1;
EXCEPTION--捕获异常
WHEN OTHERS THEN
BEGIN
v_num := 0;
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('数据处理失败' || SQLERRM);
END;
end pro_sys_userrole;