Home » Open Source » MySQL » MySQL to PL/SQL
MySQL to PL/SQL [message #252456] Wed, 18 July 2007 21:58 Go to next message
npadilla
Messages: 9
Registered: July 2007
Junior Member
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(Cool, 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
Re: MySQL to PL/SQL [message #252463 is a reply to message #252456] Wed, 18 July 2007 22:16 Go to previous messageGo to next message
npadilla
Messages: 9
Registered: July 2007
Junior Member
Hi its me again...

whats the equivalent of
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
in Oracle?

What I pasted abbove was too long, I just need to convert this part:

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;

[Updated on: Wed, 18 July 2007 22:18]

Report message to a moderator

Re: MySQL to PL/SQL [message #252514 is a reply to message #252463] Thu, 19 July 2007 01:29 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

PL/SQL User's Guide and Reference is a nice book as well as SQL Reference.

Regards
Michel

[Updated on: Thu, 19 July 2007 01:29]

Report message to a moderator

Previous Topic: mySQL select statement to PL/SQL select statement
Next Topic: mysql sequence regarding
Goto Forum:
  


Current Time: Fri Mar 29 08:25:58 CDT 2024