mySQL to PL/SQL
Good day...
I need help on how to convert this mySQL statement to Oracle (PL/SQL)
Your response would be greatly appreciated. Thanks!
CREATE DEFINER=`root`@`localhost`
PROCEDURE `sp_get_append_batch_list`(IN ver_id VARCHAR(8), IN where_condition TEXT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE var_step VARCHAR(2) DEFAULT '';
DECLARE where_clause TEXT DEFAULT '';
DECLARE SQL_stmt TEXT DEFAULT '';
DECLARE cur1 CURSOR FOR SELECT RIGHT(fld_step_ver_id,2) FROM tbl_step_of_version WHERE LEFT(fld_step_ver_id,8)= ver_id;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO var_step;
IF NOT done THEN
SET where_clause= CONCAT(where_clause, 'MID(fld_step_string_series,', var_step,',1)=3 OR ');
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
IF where_condition= '' THEN
SET @SQL_stmt= CONCAT('SELECT DISTINCT tbl_batch_step_tool_user_submit.fld_id, tbl_batch.fld_batch_id AS BatchID,
tbl_batch.fld_name AS `Batch Name`, tbl_step.fld_name AS Step, tbl_user.fld_name AS `User Name`, tbl_user.fld_user_id AS
UserID, tbl_step.fld_step_id, tbl_batch_step_tool_user_submit.fld_date AS `Date Append`
FROM tbl_batch INNER JOIN tbl_batch_step_tool_user_submit ON tbl_batch.fld_batch_id
=tbl_batch_step_tool_user_submit.fld_batch_id INNER JOIN tbl_step ON
tbl_batch_step_tool_user_submit.fld_step_id=tbl_step.fld_step_id INNER JOIN tbl_user ON
tbl_batch_step_tool_user_submit.fld_user_id=tbl_user.fld_user_id
WHERE tbl_batch.fld_ver_id=', "'",ver_id, "' AND (", where_clause, ' MID(fld_step_string_series, 50,1)=3) AND
tbl_batch_step_tool_user_submit.fld_date< DATE_SUB(Now(), INTERVAL 2 DAY);');
PREPARE stmt FROM @SQL_stmt;
EXECUTE stmt;
ELSE
SET @SQL_stmt= CONCAT('SELECT DISTINCT tbl_batch_step_tool_user_submit.fld_id, tbl_batch.fld_batch_id AS BatchID,
tbl_batch.fld_name AS `Batch Name`, tbl_step.fld_name AS Step, tbl_user.fld_name AS `User Name`, tbl_user.fld_user_id AS
UserID, tbl_step.fld_step_id, tbl_batch_step_tool_user_submit.fld_date AS `Date Append`
FROM tbl_batch INNER JOIN tbl_batch_step_tool_user_submit ON tbl_batch.fld_batch_id
=tbl_batch_step_tool_user_submit.fld_batch_id INNER JOIN tbl_step ON
tbl_batch_step_tool_user_submit.fld_step_id=tbl_step.fld_step_id INNER JOIN tbl_user ON
tbl_batch_step_tool_user_submit.fld_user_id=tbl_user.fld_user_id
WHERE tbl_batch.fld_ver_id=', "'",ver_id, "' AND tbl_batch_step_tool_user_submit.fld_date< DATE_SUB(Now(), INTERVAL 2 DAY)
AND tbl_step.fld_name=", "'", where_condition, "'",';');
PREPARE stmt FROM @SQL_stmt;
EXECUTE stmt;
END IF;
END