Oracle存储过程创建及各存储过程间的简单调用

-- 创建存储过程：SERVICE1_PROC，其中立即执行若干SQL语句，并在最后统一提交

CREATE or REPLACE PROCEDURE SERVICE1_PROC AS

begin

declare

v_Sql varchar2(1000);

begin

Create table CPE_TMP_USER_CITY_CURR as

-- 创建中间表

v_Sql := 'Create table middleTable as SELECT ...... ';

execute immediate v_Sql;

v_Sql := 'Insert into ...... ';

execute immediate v_Sql;

v_Sql := 'update ...... ';

execute immediate v_Sql;

-- 其它业务处理

end;

COMMIT;

end;

/

-- 创建存储过程：SERVICE2_PROC

CREATE or REPLACE PROCEDURE SERVICE2_PROC AS

begin

declare

v_Sql varchar2(1000);

begin

-- 业务处理

end;

COMMIT;

end;

/

-- 存储过程间调用：一个存储过程内执行存储过程SERVICE1_PROC、SERVICE2_PROC

CREATE or REPLACE PROCEDURE MULTI_SERVICES_PROC AS

begin

SERVICE1_PROC;

SERVICE2_PROC;

end;

/

-- 每天凌晨0点执行job任务：执行存储过程SERVICE1_PROC、SERVICE2_PROC

declare

job_num_info number;

begin

dbms_job.submit(job_num_info, 'SERVICE1_PROC;SERVICE2_PROC;', sysdate, 'TRUNC(SYSDATE + 1)' );

end;

/