Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

oracle - Use of boolean in PL/SQL

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;

it returns the error:

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;

EDIT:

I also tried this:

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

And it returns the error: ORA-01422: exact fetch returns more than requested number of rows

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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 );

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

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;

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;

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...