MySQL Forums
Forum List  »  Oracle

mySQL to PL/SQL
Posted by: Nelson Padilla
Date: July 18, 2007 08:29PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
mySQL to PL/SQL
13398
July 18, 2007 08:29PM
4804
July 18, 2007 09:17PM
4628
April 24, 2009 10:17PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.