返回Oracle过程中2个select语句的值

Question

我被卡住的家庭作业问题

我需要创建一个名为dept_info的过程，将部门id作为IN参数，将该部门经理的部门名称和全名(姓和名的concat)作为OUT参数传递给调用程序。这个过程应该在PL SQL块内。

要使用两个表:保存部门ID、部门名称和经理ID的departments，以及保存员工ID(与经理ID相同)、名字和姓氏的employees。

可执行部分应该打印出部门ID 100的信息。

目前为止我的代码是:

DECLARE
dept_id departments.department_id%TYPE;
dept_name VARCHAR(30);
mgr_id NUMBER(4);
full_name VARCHAR(100);
PROCEDURE dept_info(
dep_id IN departments.department_id%TYPE,
dep_name OUT departments.department_name%TYPE,
full_name OUT employees.first_name%TYPE)
IS
BEGIN
SELECT department_name, manager_id INTO dept_name, mgr_id FROM DEPARTMENTS WHERE department_id = dept_id;
SELECT first_name || ' ' || last_name AS full_name INTO full_name FROM EMPLOYEES WHERE employee_id = mgr_id;
END dept_info;
BEGIN
dept_info(100, dept_name, full_name);
DBMS_OUTPUT.PUT_LINE(dept_id || dept_name || full_name);
END;

返回"未找到数据";这意味着我的SELECT和INTO语句中的数据不能正常工作。如果你能给我指出正确的方向，我将不胜感激。

Answer 1

更清晰的变量命名将会有所帮助，但最后我在SELECT语句中使用了不合适的变量。正确的代码:

DECLARE
dept_id departments.department_id%TYPE := 100;
dept_name VARCHAR(30);
mgr_id NUMBER(4);
full_name VARCHAR(46);
PROCEDURE dept_info(
dep_id IN departments.department_id%TYPE,
dep_name OUT departments.department_name%TYPE,
full_name OUT employees.first_name%TYPE)
IS
BEGIN
SELECT department_name, manager_id INTO dep_name, mgr_id FROM DEPARTMENTS WHERE department_id = dep_id;
SELECT first_name || ' ' || last_name AS full_name INTO full_name FROM EMPLOYEES WHERE employee_id = mgr_id;
END dept_info;
BEGIN
dept_info(100, dept_name, full_name);
DBMS_OUTPUT.PUT_LINE(dept_id || dept_name || full_name);
END;