oralce 存储过程 带有参数 返回多条记录 并且能够调用

CREATE TABLE his_bi.employees (
    employee_id   NUMBER PRIMARY KEY,
    first_name    VARCHAR2(50),
    last_name     VARCHAR2(50),
    salary        NUMBER
);

CREATE OR REPLACE PROCEDURE his_bi.get_employee_salary (
     --p_employee_id IN employees.employee_id%TYPE,
     p_first_name  IN employees.FIRST_NAME%TYPE,
    p_salary OUT employees.salary%TYPE
) AS
BEGIN
    SELECT salary
    INTO p_salary
    FROM his_bi.employees
    WHERE FIRST_NAME = p_first_name;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_salary := NULL; -- 如果没有找到员工，返回NULL
END;
/

INSERT INTO his_bi.employees (employee_id, first_name, last_name, salary) VALUES (100, 'John', 'Doe', 50000);
COMMIT;

INSERT INTO his_bi.employees (employee_id, first_name, last_name, salary) VALUES (101, 'John', 'DoeDoe', 100000);
COMMIT;


delete  from   his_bi.employees  where  employee_id ='101'

select * from his_bi.employees  ;


DECLARE
    v_salary employees.salary%TYPE;
BEGIN
    -- 调用存储过程，传入employee_id为100
     get_employee_salary( p_first_name => 'John', p_salary => v_salary);
    -- 输出薪水
    DBMS_OUTPUT.PUT_LINE('Employee Salary: ' || NVL(TO_CHAR(v_salary), 'Not Found'));
END;

--ORA-01422:实际返回的行数超出请求的行数
在 Oracle 中，存储过程可以通过 OUT 参数返回结果集。通常，我们使用 SYS_REFCURSOR 类型的游标来返回多条记录集。以下是一个示例，展示如何创建一个带参数的存储过程，并返回多条记录集

CREATE OR REPLACE PROCEDURE get_employee_salary (
     p_first_name  IN employees.FIRST_NAME%TYPE,
      p_employee_cursor OUT  SYS_REFCURSOR
) AS
BEGIN
  OPEN p_employee_cursor FOR
    SELECT   FIRST_NAME, SALARY
    FROM his_bi.employees
    WHERE FIRST_NAME = p_first_name;
END;


DECLARE
    v_employee_cursor SYS_REFCURSOR;  --必须申明游标
    v_first_name employees.FIRST_NAME%TYPE;
    v_salary     employees.salary%TYPE;
BEGIN
-- 调用存储过程，传入员工姓名
    his_bi.get_employee_salary  (   p_first_name => 'John', p_employee_cursor => v_employee_cursor);  --p_first_name 不能写v_first_name
     -- 循环读取游标中的数据
    LOOP
      FETCH v_employee_cursor INTO  v_first_name,  v_salary;
        EXIT WHEN v_employee_cursor%NOTFOUND;
    -- 输出
    DBMS_OUTPUT.PUT_LINE( 'First Name: ' || v_first_name ||  TO_CHAR(v_salary)   );
    end loop ;
    -- 关闭游标
    CLOSE v_employee_cursor;
END;