PL/SQL 中布尔值的使用

2021-12-24 00:00:00 oracle plsql

我在 PL/SQL 中有一个函数，它检查特定的 emp_id 是否存在，即:

I have a function in PL/SQL which checks if a particular emp_id exists or not which is:

CREATE OR REPLACE FUNCTION checkEmpNo(eno numeric)
RETURN boolean IS
    emp_number number;
BEGIN
    SELECT emp_id INTO emp_number
    FROM emp;
    IF eno=emp_number
    THEN
        return true;
    ELSE
        return false;
    END IF;
END checkEmpNo;

该函数编译成功，但是当我尝试将其运行为:

The function compiles successfully, but when I try to run it as:

DECLARE
    exist boolean;
BEGIN
    exist=checkEmpNo(1);
    dbms_output.put_line(exist);
END;

它返回错误:

ORA-06550: line 5, column 1:
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored

3. BEGIN
4. exist:=checkEmpNo(1);
5. dbms_output.put_line(exist);
6. END;

我也试过这个:

DECLARE
    exist boolean:=true;
BEGIN
    if(exist=checkEmpNo(1))
    then
        dbms_output.put_line('true');
    else
        dbms_output.put_line('false');
    end if;
END;

它返回错误:ORA-01422:exact fetch返回超过请求的行数

推荐答案

dbms_output.put_line 未重载以接受布尔参数.你可以做类似的事情

dbms_output.put_line is not overloaded to accept a boolean argument. You can do something like

dbms_output.put_line( case when exist = true 
                           then 'true'
                           else 'false'
                        end );

将布尔值转换为字符串，然后您可以将其传递给 dbms_output.

to convert the boolean into a string that you can then pass to dbms_output.

ORA-01422 错误是一个完全独立的问题.函数 checkEmpNo 包括 SELECT INTO 语句

The ORA-01422 error is a completely separate issue. The function checkEmpNo includes the SELECT INTO statement

SELECT emp_id 
  INTO emp_number
  FROM emp;

如果查询返回除 1 行以外的任何内容，SELECT INTO 将生成错误.在这种情况下，如果 emp 表中有多行，您将收到错误消息.我的猜测是您希望您的函数执行类似

A SELECT INTO will generate an error if the query returns anything other than 1 row. In this case, if there are multiple rows in the emp table, you'll get an error. My guess is that you would want your function to do something like

CREATE OR REPLACE FUNCTION checkEmpNo(p_eno number)
  RETURN boolean 
IS
  l_count number;
BEGIN
  SELECT count(*)
    INTO l_count
    FROM emp
   WHERE emp_id = p_eno;

  IF( l_count = 0 )
  THEN
    RETURN false;
  ELSE
    RETURN true;
  END IF;
END checkEmpNo;

相关文章