Oracle PL/SQL Analysing output of queries, writing to file

I'm working on a project my boss gave me. I don't have much experience but I have to learn it. It's regarding Oracle DB 11g and I'm using SQLPlus and PL/SQL as the query language.

Basically for each query statement, I will need the script to interpret the results and output true/false to the file.

For example,

select id from sample_table where id=3;

/**code needed that will do something like:**/
/**if(id=3), write to file TRUE, else, write to file FALSE**/

select salary from sample2 where id=5;

/**similar code needed as above**/

select employee from sample3 where id=6;

/** another TRUE or FALSE output to the file **/

I have tried using DBMS_OUTPUT.PUT_LINE bu开发者_如何学Got have no idea on how to analyse directly the output from the query statements. Will greatly appreciate any help!

If you are selecting from different tables I would use the following - alternatively a cursor could be used if the same query was being used with only a parameter (like id) varying in which case you could use cursor parameters.

DECLARE
   v_count    PLS_INTEGER;
BEGIN
    SELECT COUNT(*)
    INTO v_count 
    FROM sample_table 
    WHERE id=3;

    CASE v_count
        WHEN 0 THEN dbms_output.put_line('FALSE');
        ELSE dbms_output.put_line('TRUE');
    END CASE;

END;

It is important to use COUNT(*) not count on a field as COUNT(*) still returns 0 if no records exist.

Edit to add: The cursor equivalent is

DECLARE
    v_count    PLS_INTEGER;

    CURSOR count_cur(cp_id  sample_table.id%TYPE)
    IS
        SELECT COUNT(*)
        FROM sample_table
        WHERE id = cp_id;
BEGIN
    OPEN count_cur(3);
    FETCH count_cur INTO v_count;
    CLOSE count_cur;

    CASE v_count
        WHEN 0 THEN dbms_output.put_line('FALSE');
        ELSE dbms_output.put_line('TRUE');
    END CASE;

END;

There are also tests available on cursors such as cursor%FOUND or cursor%NOTFOUND that can be accessed once a cursor is opened. Although if all you are doing is testing whether a record exists that can be more verbose.

Also bear in mind if you are doing a SELECT INTO statement that unless you are doing COUNT(*) which is guaranteed to always return a record it is good form to catch the possible too many or no rows exceptions.

Basically for each query statement, I will need the script to interpret the results and output true/false to the file

By "true false" I assume you mean: "True" if 1 or more rows is returned, "false" if no rows returned.

You don't need a pl/sql wrapper for this, you can just put all of your sql statements into sqlplus with a few settings and spool to a file, something like:

spool "my_tests.dat"
set serveroutput off
set echo off
set head off
set pagesize 60
set linesize 80
set feedback 1

prompt This should return no rows
select dummy from dual where 1=0;

prompt This should return 1 row
select sysdate from dual;

prompt This should return 2 rows
select sysdate from dual
union
select sysdate-1 from dual;

prompt Complete
spool off

Save script to file("my_script.sql"), login to Oracle using sqlplus from same directory as script file, and type: @my_script.sql

Exit from sqlplus and your output file will be in the same directory as the script file.

After each statement, you'll see "no rows selected" or "1 row selected" or "2 rows selected" etc.

You can also add timings (set timing on) and other formatting (titles, etc). I'll leave that to you, check here and here for some options.

This may be a more straightforward way of doing this rather than wrapping all of your SQL inside PL/SQL just to get some basic info such as rows selected