我想创建一个名为VALIDATE_EMP的函数，它接受employeeNumber作为参数，根据存在返回TRUE或FAL

Question

样本数据

create table Employees (emp_id number, emp_name varchar2(50), salary number, department_id number) ;
insert into Employees values(1,'ALex',10000,10);
insert into Employees values(2,'Duplex',20000,20);
insert into Employees values(3,'Charles',30000,30);
insert into Employees values(4,'Demon',40000,40);

代码:

create or replace function validate_emp(empno in number)
return boolean
is lv_count number
begin

select count(employee_id) into lv_count from hr.employees where employee_id = empno;
if lv_count >1 then
return true;
else
return false;
end;

我想创建一个名为VALIDATE_EMP的函数，它接受empno作为参数，如果指定的雇员存在于表名"Employeee"中，则返回TRUE，否则返回FALSE。

Answer 1

• 缺少分号作为局部变量声明的结束符
• 如果你连接的用户不是hr，删除它(否则，保持原样)
• 列名称是emp_id，而不是employee_id
• 缺少end if

修复后，代码编译为:

SQL> CREATE OR REPLACE FUNCTION validate_emp (empno IN NUMBER)
2     RETURN BOOLEAN
3  IS
4     lv_count  NUMBER;
5  BEGIN
6     SELECT COUNT (emp_id)
7       INTO lv_count
8       FROM employees
9      WHERE emp_id = empno;
10
11     IF lv_count > 1
12     THEN
13        RETURN TRUE;
14     ELSE
15        RETURN FALSE;
16     END IF;
17  END;
18  /
Function created.
SQL>

---

怎么称呼?通过PL/SQL作为Oracle的SQL没有布尔数据类型

SQL> set serveroutput on
SQL> declare
2    result boolean;
3  begin
4    result := validate_emp(1);
5
6    dbms_output.put_line(case when result then 'employee exists'
7                                 else 'employee does not exist'
8                            end);
9  end;
10  /
employee does not exist
PL/SQL procedure successfully completed.
SQL>

---

也许你宁愿返回VARCHAR2;然后你会模仿布尔值，但你可以在普通SQL中使用它:

SQL> CREATE OR REPLACE FUNCTION validate_emp (empno IN NUMBER)
2     RETURN VARCHAR2
3  IS
4     lv_count  NUMBER;
5  BEGIN
6     SELECT COUNT (emp_id)
7       INTO lv_count
8       FROM employees
9      WHERE emp_id = empno;
10
11     IF lv_count > 1
12     THEN
13        RETURN 'TRUE';
14     ELSE
15        RETURN 'FALSE';
16     END IF;
17  END;
18  /
Function created.
SQL> select validate_emp(1) from dual;
VALIDATE_EMP(1)
-------------------------------------------------------------------
FALSE
SQL>