调用存储过程

declare

　　num number;

　　msg varchar(200);

begin　　

　　gk_sys_org_user(num,msg);

end;

 

 

存储过程：

CREATE OR REPLACE PROCEDURE DCP.GK_SYNC_ORG_USER_INFO(
o_result out number,
o_msg out varchar2) AUTHID CURRENT_USER AS
/*********************************************************************
* 系统名称: 厦门国际银行门户系统
* 作业名称: GK_SYNC_ORG_INFO
* 功能说明: 从门户同步机构信息
* 编写日期: 2018/03/23
* 编写说明: 适用Oracle数据库
* 开发人员: 周春
* 修改说明:
*********************************************************************/
V_SQL VARCHAR(2000);--动态sql语句
BEGIN

--更新已有机构相关信息
V_SQL:= 'update dcp.t_sm_org_info t set (org_id,p_org_id,org_shname,org_name)
=(select t2.id,t2.pid,t2.simplename,t2.fullname from portal.t_portal_dept t2 where t.org_id = t2.id)
where exists (select 1 from portal.t_portal_dept t3 where t.org_id = t3.id)';
EXECUTE IMMEDIATE V_SQL;

--插入新增的机构信息
V_SQL:= 'insert into dcp.t_sm_org_info t (org_id,p_org_id,org_shname,org_name)
(select t2.id,t2.pid,t2.simplename,t2.fullname from portal.t_portal_dept t2
where not exists (select 1 from dcp.t_sm_org_info t3 where t3.org_id = t2.id))';
EXECUTE IMMEDIATE V_SQL;

--更新已有用户相关信息
V_SQL:= 'update dcp.t_sm_user t set (user_id,org_id,name,alias,user_type,user_stat,logon_pwd,cont_tel,cont_email)
=(select t2.id,t2.deptid,t2.account,t2.name,0 as type,1 as status,t2.password,t2.phone,t2.email from portal.t_portal_user t2 where t.user_id = t2.id)
where exists (select 1 from portal.t_portal_user t3 where t.user_id = t3.id)';
EXECUTE IMMEDIATE V_SQL;

--插入新增的用户
--默认密码111111,默认启用 096e79218965eb72c92a549dd5a330112
V_SQL:= 'insert into dcp.t_sm_user (user_id,org_id,name,alias,user_type,user_stat,logon_pwd,cont_tel,cont_email)
(select t2.id,t2.deptid,t2.account,t2.name,0 as type,1 as status,''096e79218965eb72c92a549dd5a330112'',t2.phone,t2.email from portal.t_portal_user t2
where not exists (select 1 from dcp.t_sm_user t3 where t3.user_id = t2.id) and t2.account <> ''admin'')';
EXECUTE IMMEDIATE V_SQL;

--赋默认角色给用户
--默认角色为init
V_SQL:= 'insert into DCP.t_sm_user_role (f_id,user_id,role_id)
(select sys_guid(),t2.user_id,(select role_id from dcp.t_sm_role t where t.role_name = ''NORMAL_USER_ROLE'') from DCP.t_sm_user t2
where not exists (select 1 from dcp.t_sm_user_role t3,dcp.t_sm_role t4 where t2.user_id = t3.user_id and t3.role_id = t4.role_id) )';
EXECUTE IMMEDIATE V_SQL;

--提交
COMMIT;

o_result := 0;
o_msg :='Run_Success';

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlcode||sqlerrm);
o_result := -1;
o_msg := SUBSTR(SQLERRM, 1, 200);
ROLLBACK;

END GK_SYNC_ORG_USER_INFO;

转载于:https://www.cnblogs.com/zhouchunDIY/p/9073769.html