error while creating mysql procedure having SYS_REFCURSOR as out param

Question

I'm creating procedure which is having two parameters , one is p_cursor of type SYS_REFCURSOR (OUT param) and the other one is p_rank of type INT(IN param). But it showing an error.

DELIMITER $$
CREATE  PROCEDURE sp_student(p_cursor OUT SYS_REFCURSOR,p_rank IN INT) 
    BEGIN
    OPEN p_cursor FOR SELECT  * FROM student WHERE   rank = p_rank;
    END$$
DELIMITER ;

the error what I'm getting is,

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OUT SYS_REFCURSOR,p_rank IN INT) 
    BEGIN
    OPEN p_cursor FOR SELECT  * FROM st' at line 1

I think I'm syntactically wrong for SYS_REFCURSOR.. please check my code and let me realise my mistake. thanks in advance

MySql does not have REFCURSOR like in Oracle, see this answer: http://stackoverflow.com/questions/7155790/what-is-the-equivalent-of-oracles-ref-cursor-in-mysql — krokodilko, Sep 30 '13 at 17:19
in mysql,if we want to return record set from the procedure, we no need to maintain out params and sys_refcursor rather we directly write select statement in procedure body. is nt it Mr.Kordiko? — Arun Kumar Mudraboyina, Oct 01 '13 at 06:02
Read this link: http://dev.mysql.com/doc/refman/5.5/en/faqs-stored-procs.html#qandaitem-B-4-1-14 . The resultset from the procedure is returned `directly to the client` (MySQL, MySlqWorkbench, PHP, JDBC, ODBC etc.). But if the procedure is called from another procedure, the calling procedure cannot read this resultset. A temporary table can be used to pass many rows from one procedure to another. — krokodilko, Oct 01 '13 at 17:51

score 2 · Answer 1 · answered Apr 02 '14 at 14:41

mysql doesnt have refcursor like oracle, if u r planning to write a stored procedure that returns multiple rows/result set in mysql just do

DROP procedure IF EXISTS `sample`;
DELIMITER $$
CREATE  PROCEDURE `sample`(p_rank IN INT)
BEGIN
select * from MyTable where id=p_rank;
END$$
DELIMITER ;

call sample(); this will return a result set. which u can use.

error while creating mysql procedure having SYS_REFCURSOR as out param

1 Answers1