Cursor expression in MySQL

Question

How do I achieve the same as an Oracle cursor expression does in a MySQL database (version from 5.6 onwards) below is a sample query of an Oracle cursor expression

SELECT department_name, CURSOR(SELECT salary, commission_pct 
   FROM employees e
   WHERE e.department_id = d.department_id)
   FROM departments d;

How can I achieve the same as this with a MySQL database?

if i execute this query on oracle below output i will be produced, depart_name cursor result

MCA { < SALARY=20000 , COMMISSION_PCT=2 > , < SALARY=40000,COMMISSION_PCT=20> ,}

BE {< SALARY=20000,COMMISSION_PCT=2 >,}

Please provide sample data, desired results, and a clear explanation of what you want to do. — Gordon Linoff
– Gordon Linoff, Commented Jun 12, 2020 at 13:36
AFAIK - There is no equivalent in MySQL for CURSOR expressions. But specific problems can probably also be solved in MySQL in a different way. — Paul Spiegel
– Paul Spiegel, Commented Jun 12, 2020 at 15:19
@GordonLinoff sorry for the delay, if i execute it on oracle below output i will get,MCA {<SALARY=20000,COMMISSION_PCT=2>,<SALARY=40000,COMMISSION_PCT=20>,} BE {<SALARY=20000,COMMISSION_PCT=2>,} — Vasanth
– Vasanth, Commented Jun 15, 2020 at 5:52

Dadinaks · Accepted Answer · 2020-06-12 14:55:16Z

1

I don't know what a CURSOR() does in oracle because I've never touched oracle, but I don't know if it would help you but I think you wanted to join like this:

SELECT d.department_name, e.salary, e.commission_pct. 
FROM departments d
INNER JOIN employees e
ON (e.department_id = d.department_id);

I give you this link for more information on joints: https://sql.sh/cours/jointures and according to sql.sh:

There are several methods to associate 2 tables together. Here is the list of the different techniques that are used:

INNER JOIN: internal join to return the records when the condition is true in both tables. This is one of the most common
joins.

CROSS JOIN: cross join to make the Cartesian product of 2 tables. In other words, allows to join each row of a table with each row of a second table. Attention, the number of results is generally very high.

LEFT JOIN (or LEFT OUTER JOIN): external join to return all the records of the left table (LEFT = left) even if the condition is not
checked in the other table.

RIGHT JOIN (or RIGHT OUTER JOIN): External join to return all records in the right-hand table (RIGHT = right) even if the condition is not checked in the other table.

FULL JOIN (or FULL OUTER JOIN) : external join to return the results when the condition is true in at least one of the 2 tables.

SELF JOIN : allows to join a table with itself as if it were another table.

NATURAL JOIN : natural join between 2 tables if there is at least one column with the same name between the 2 SQL tables.

UNION JOIN: joint of union.

if you have any questions, I am available to answer them.

answered Jun 12, 2020 at 14:55

Dadinaks

938 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vasanth Over a year ago

thanks @Dadinks but my required output is , f i execute it on oracle below output i will get ** MCA {<SALARY=20000,COMMISSION_PCT=2>,<SALARY=40000,COMMISSION_PCT=20>,} BE {<SALARY=20000,COMMISSION_PCT=2>,} **

Collectives™ on Stack Overflow

Cursor expression in MySQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related