调用存储过程

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 t2where 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,默认启用 096e79218965eb72c92a549dd5a330112V_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 t2where not exists (select 1 from dcp.t_sm_user t3 where t3.user_id = t2.id) and t2.account <> ''admin'')';EXECUTE IMMEDIATE V_SQL;

--赋默认角色给用户--默认角色为initV_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 t2where 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