Home » Open Source » MySQL » MySQL to PL/SQL
| MySQL to PL/SQL [message #252456] |
Wed, 18 July 2007 21:58  |
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( , 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  |
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 |
|
|
|
|
|
Goto Forum:
Current Time: Fri Jan 24 00:36:08 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
