问题描述:

I have a pl/sql script name test

and a table name testtable

//testtable

a#

-----------

1

2

3

4

Inside my test script:

CREATE OR REPLACE FUNCTION test(a_num IN NUMBER )

RETURN NUMBER;

IS

check NUMBER := 0;

match NUMBER := 0;

BEGIN

SELECT COUNT(*) into check FROM TESTTABLE WHERE a_num = a#;

IF check > 0 THEN

match := match + 1;

END IF;

IF match = 0 THEN

DBMS_OUTPUT.PUT_LINE( 'not found' );

RETURN(-1);

ELSE

DBMS_OUTPUT.PUT_LINE( 'found' );

RETURN(0);

END IF;

END test;

/

for this script, when i enter following query:

select test(5) from testtable;

the result return will be 4 times :

not found

not found

not found

not found

The following query:

select (test2) from testtable;

returns:

found

found

found

found

how to make it show only 1 message?

网友答案:

If you want a single result, don't select from testtable, select from a single-row table. Oracle provides the dual table for just that purpose

SELECT test(5)
  FROM dual

and

SELECT test(2)
  FROM dual

will each return a single row of results.

Of course, this assumes that your test function is actually valid and that it actually returns results. The code you posted won't compile because you have a function that does not return anything. There is no return clause in the specification of the function that tells the compiler what data type the function returns. And there is nothing in the function that returns a result. So the function cannot possibly compile. My assumption is that your actual function does compile and that it does return something rather than just writing to dbms_output.

网友答案:

Try to use this code:-

CREATE OR REPLACE FUNCTION test(a_num IN NUMBER )
     RETURN NUMBER;
IS
    check NUMBER := 0;
    match NUMBER := 0;
BEGIN
    SELECT COUNT(*) into check FROM TESTTABLE WHERE a# =a_num;

    IF check > 0 THEN
         match := match + 1;
    END IF;

    IF match = 0 THEN
         DBMS_OUTPUT.PUT_LINE( 'not found' );
         RETURN(-1);
    ELSE
         DBMS_OUTPUT.PUT_LINE( 'found' );
         RETURN(0);
    END IF;
END test;
/

Hope this will solve your problem.

相关阅读:
Top