在PL/SQL中使用布尔值 [英] Use of boolean in PL/SQL
问题描述
我在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 returns more than requested number of rows
推荐答案
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;
这篇关于在PL/SQL中使用布尔值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!