PL/SQL第二课（作业)

作业讲解

1.1、查询语句
SELECT b.first_name
FROM s_emp a,s_emp b
WHERE a.manager_id = b.id--manager_id作为外键，与 b 表里的id 连接
AND a.id = 5;
1.2、存储过程
CREATE OR REPLACE PROCKDURE proc_GetManager(
    p_id IN s_emp.id%TYPE,
    p_name OUT s_emp.first_name%TYPE
)
BEGIN
/*
    SELECT first_name
    FROM s_emp
    WHERE manager_id = NULL;*/
    SELECT b.first_name
        INTO p_name
    FROM  s_emp a, s_emp b
    WHERE a.manager_id = b.id
        AND a.id = p_id;
END
1.3、调用传递
DECLARE
     v_first_name s_emp.first_name%TYPE;
BEGIN
    proc_GetManager(3,v_fisrt_name);
    DBMS_OUTPUT.PUT_LINE('v_fisrt_name = '||v_first_name)
END

2.1、

SELECT a.id, a.first_name, a.salary
    FROM s_emp a, s_dept b
WHERE a.dept_id = b.id
    AND b.region_id = 2;


2.2、
CREATE OR REPLACE FUNCTION fun_getDept(
    p_rid s_region.id%TYPE
    RETURN s_dept.name%TYPE)
AS
    v_dept s_dept.name%TYPE
 CURSOR cur_emp IS
    SELECT name
    from s_emp a, s_dept b
    where a. dept_id = b.id
     and b.region_id = p_ id
    ORDERby salary DESC;
BEGIN
    OPEN cur_emp;
    FETCH cur_emp INTO fun_getDept();
    CLOSE cur_emp;
END
    

2.3
DECLARE
    v_dept s_dept.name%TYPE;
begin
    v_dept := fun_getDept(2);
    DBMS_OUTPUT.PUT_LINE(v_dept);
end