oracle 转 mysql 存储过程_oracle 存储过程转成 mysql

CREATEORREPLACEPROCEDUREP_PUBLISH_TASK_PROC(P_CURSOROUTTYPES.HUILAN_CURSOR,/**静态化获取任务的模式1:获取全部站点的任务2:获取某个站点任务3:不获取某个站点的数据**/V_MODEINI...

CREATE OR REPLACE PROCEDURE P_PUBLISH_TASK_PROC

(

P_CURSOR OUT TYPES.HUILAN_CURSOR,

/** 静态化获取任务的模式 1:获取全部站点的任务 2:获取某个站点任务 3:不获取某个站点的数据 **/

V_MODE IN INTEGER,

/** 站点ID 当V_MODE的值为(2、3)时本字段有效,多个站点用','分割 例如: main,site1 **/

V_SITEIDS IN STRING,

/** 最大返回条数 **/

V_IMAXTASKNUMBERDB IN INTEGER,

/** 系统时间 yyyy-MM-dd HH:mm:ss **/

V_SYSTEMDATE IN STRING,

/** 标志位 **/

V_INFLAG IN STRING,

/** 程序标识 **/

V_PROJECTFLAG IN STRING

) AS

--唯一标识

V_SYS_GUID VARCHAR2(50);

SYSDATE DATE;

BEGIN

--产生一个唯一标识

V_SYS_GUID := SYS_GUID();

/**锁定该存储过程**/SELECT SYSDATE INTO SYSDATE FROM DUAL FOR UPDATE;

--将符合静态化条件的数据设置一个"唯一标识"

IF V_MODE = 1 THEN

UPDATE P_PUBLISH_TASK T SET T.GUID = V_SYS_GUID,T.PROJECTFLAG = V_PROJECTFLAG,t.inflag=V_INFLAG WHERE T.BEGINTIME<= V_SYSTEMDATE AND (t.projectflag is null or (t.projectflag = V_PROJECTFLAG AND t.inflag<>V_INFLAG)) AND ROWNUM <= V_IMAXTASKNUMBERDB;

END IF;

IF V_MODE = 2 THEN

UPDATE P_PUBLISH_TASK T SET T.GUID = V_SYS_GUID,T.PROJECTFLAG = V_PROJECTFLAG,t.inflag=V_INFLAG WHERE T.BEGINTIME<= V_SYSTEMDATE AND (t.projectflag is null or (t.projectflag = V_PROJECTFLAG AND t.inflag<>V_INFLAG)) AND T.CHILDWEB IN (V_SITEIDS) AND ROWNUM <= V_IMAXTASKNUMBERDB;

END IF;

IF V_MODE = 3 THEN

UPDATE P_PUBLISH_TASK T SET T.GUID = V_SYS_GUID,T.PROJECTFLAG = V_PROJECTFLAG,t.inflag=V_INFLAG WHERE T.BEGINTIME<= V_SYSTEMDATE AND (t.projectflag is null or (t.projectflag = V_PROJECTFLAG AND t.inflag<>V_INFLAG)) AND T.CHILDWEB NOT IN (V_SITEIDS) AND ROWNUM <= V_IMAXTASKNUMBERDB;

END IF;

--获取数据结果集

OPEN P_CURSOR FOR SELECT * FROM P_PUBLISH_TASK T WHERE T.GUID = V_SYS_GUID;

COMMIT;

END;

要改哪些地方额

展开