在存储过程中构建逻辑以检查员工id的有效性

Question

我需要开发一个数据库存储过程，其中包含存储过程的employee_id输入参数和两个输出参数。一个用于返回代码，另一个用于回复消息。

CREATE OR REPLACE PROCEDURE CHECK_ID (emp_id IN INT,
                                    out_status OUT INT,
                                    out_msg OUT VARCHAR
                                     )
AS 
BEGIN
DECLARE
emp_salary INTEGER;
BEGIN
  SELECT SALARY INTO emp_salary FROM EMPLOYEES
  WHERE EMPLOYEE_ID = emp_id;
  IF EMPLOYEE_ID = emp_id 
  THEN
  out_status := 1;
  out_msg:= 'Employee is valid and his total salary is :'|| emp_salary; 
  ELSE 
  out_status :=0; 
  --out_msg := 
  DBMS_OUTPUT.PUT_LINE('Employee Number' || emp_id || 'is not valid') ;
END IF;
END;

如果员工编号有效，则返回返回代码1，并返回消息为："Employee is valid and his total salary is : "

如果不是，则返回代码需要为-1，返回消息为"Employee Number is not valid:"

Answer 1

如果不获取变量中列的值（EMPLOYEE_ID=emp_ID），就无法将列值与变量值进行比较。此外，您的代码中还有其他语法错误。试试这个。

CREATE OR REPLACE PROCEDURE check_id 
(
emp_id     IN  INTEGER,
out_status OUT INTEGER,
out_msg    OUT VARCHAR2
)
AS 
  emp_salary INTEGER;
  emp_found  BOOLEAN;
BEGIN
   emp_found := FALSE;
   BEGIN
     SELECT salary INTO emp_salary 
     FROM   employees
     WHERE  employee_id = emp_id;
     emp_found := TRUE;
   EXCEPTION
    WHEN NO_DATA_FOUND THEN
       emp_found := FALSE;      
   END;
  IF emp_found = TRUE THEN
     out_status := 1;
     out_msg:= 'Employee is valid and his total salary is : '|| emp_salary; 
  ELSE 
     out_status := 0; 
     out_msg := 'Employee Number ' || emp_id || ' is not valid';
  END IF;
END;

以下是测试方法。将第一个参数替换为员工编号。

DECLARE
  v_status INTEGER;
  v_msg    VARCHAR2(1000);
BEGIN
  check_id (1,v_status,v_msg);
  DBMS_OUTPUT.PUT_LINE(v_status || ' ' || v_msg);
END;