需要审查SQL查询 [英] Review of SQL Query needed

问题描述

它显示所有具有经理ID的员工:

It shows all employees that has manager ID:

DECLARE 
	input NUMBER := &ManagerID;
	CURSOR emp_cursor (emp_id NUMBER) IS
		SELECT first_name,
		       last_name,
		       manager_id
		FROM employees
		WHERE manager_id = emp_id;
			fname employees.first_name%TYPE;
			lname employees.last_name%TYPE;
			manid employees.manager_id%TYPE;
BEGIN
	OPEN emp_cursor(input);
		LOOP
		  FETCH emp_cursor INTO fname, lname, manid;
		  EXIT WHEN emp_cursor%NOTFOUND;
		  DBMS_OUTPUT.PUT_LINE('Name  :' || fname || ' '|| lname);
		  DBMS_OUTPUT.PUT_LINE('Employee: '|| manid);
		END LOOP;
		  DBMS_OUTPUT.PUT_LINE(emp_cursor%ROWCOUNT || 'num of rows retrieved');
	CLOSE emp_cursor;
END;

推荐答案

为什么在使用简单查询就可以使用游标时为什么要使用游标?
使用自我加入"(与内部加入"一起显示那些拥有经理的人！)怎么样:

Why use cursors when we can have that using simple query?
Use Self join (with Inner join to just show those who have managers!)What about something like:

SELECT 
    E1.[NAME] AS 'Employee Name',
    E2.[NAME] AS 'Manager Name'
FROM 
    EMPLOYEE E1
INNER JOIN EMPLOYEE E2 
    ON E2.EMPLOYEEID=E1.MANAGERID

在此处详细了解. [ ^ ].

Read more about it here[^].

这篇关于需要审查SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！