CODEWITHSUNDEEP
oracle-databaseplsql
knt5784
knt5784

Reputation: 129

Create a procedure to print the result of and SQL query

I'm using Oracle SQL developer and I want to create a procedure to print the result of this SQL code:

SELECT Title , Name  
FROM BOOK, BORROWER, BOOK_LOANS
WHERE Due_date = SYSDATE AND Return_date = NULL;

Here's my code. I keep getting "SQL statement ignored, statement ignored, LOOP index variable 'books' is invalid". Please tell me what I am missing here. I tried moving the SQL statement to a cursor, but it also doesn't work.

CREATE OR REPLACE PROCEDURE overdueToday IS   
BEGIN
    FOR books IN (SELECT Title , Name  
                  FROM BOOK, BORROWER, BOOK_LOANS
                  WHERE Due_date = SYSDATE 
                    AND Return_date = NULL)
    LOOP
        DBMS_OUTPUT.put_line(books.Title || ' -- ' || books.Name);
    END LOOP;
END overdueToday;
/

Upvotes: 0

Views: 1758

Answers (4)

user1012506
user1012506

Reputation: 2118

This is the simple way to loop in select statement:

   CREATE OR REPLACE PROCEDURE overdueToday IS  
            CURSOR book_cur is 
               SELECT Title , Name  
            FROM BOOK, BORROWER, BOOK_LOANS
              WHERE Due_date = SYSDATE 
              AND Return_date = NULL;

        BEGIN
            FOR book_rec IN book_cur loop                
                DBMS_OUTPUT.put_line(book_rec .Title || ' -- ' || book_rec .Name);
            END LOOP;
        END overdueToday;

Upvotes: 0

eaolson
eaolson

Reputation: 15094

If you're using SQL Developer, you can just run your query (hit F5 or Ctrl+Enter). You don't need to write a PL/SQL program.

That being said, your query is almost certainly wrong since you have three tables and no join conditions for them. But you should still get some output.

Upvotes: 0

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

Oracle 12c and above, you may use DBMS_SQL.RETURN_RESULT. You require proper join conditions and aliases in your query, which we are not aware of.

CREATE OR replace PROCEDURE OverdueToday
IS
  rc SYS_REFCURSOR;
BEGIN
    OPEN rc FOR
      SELECT title,  --  b.title ?
             name    --  br.name ?
      FROM   book b
             join borrower br
               ON ( 1 = 1 ) --Add proper join condition here
             join book_loans bl
               ON ( 1 = 1 ) --Add proper join condition here
      WHERE  due_date = TRUNC(SYSDATE)
             AND return_date IS NULL;

    DBMS_SQL.RETURN_RESULT(rc);
END overduetoday;

/

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142705

Apart from what you've already been told (cross-join), WHERE condition won't work. SYSDATE is a function that returns both date and time, so there's no chance that it'll return anything. You should use TRUNC function.

Moreover, when dealing with NULL values, they aren't "equal" (=) to anything - you should use IS NULL (or IS NOT NULL, depending on what you do).

The following example is kind of stupid; you didn't provide test case so I'm creating my own tables with absolutely minimal column set, just to be sure that the procedure won't fail. 

SQL> create table book (title varchar2(20));

Table created.

SQL> create table borrower (name varchar2(20));

Table created.

SQL> create table book_loans (due_date date, return_date date);

Table created.

SQL>
SQL> insert into book values ('Pinky');

1 row created.

SQL> insert into borrower values ('Littlefoot');

1 row created.

SQL> insert into book_loans values (trunc(sysdate), null);

1 row created.

SQL>

The procedure; I marked places you should pay attention to:

SQL> set serveroutput on;
SQL> create or replace procedure overduetoday
  2    is
  3  begin
  4    for books in ( select title,
  5                          name
  6                   from book,
  7                        borrower,
  8                        book_loans
  9                   where due_date = trunc(sysdate)   --> trunc!
 10                     and return_date is null         --> is!
 11    ) loop
 12      dbms_output.put_line(books.title ||' -- '|| books.name);
 13    end loop;
 14  end overduetoday;
 15  /

Procedure created.

SQL>
SQL> exec overduetoday;
Pinky -- Littlefoot

PL/SQL procedure successfully completed.

SQL>

As you can see, it works (or, should I rather say, *doesn't fail". If there were more rows in those tables, the result would be really wrong).

Errors you mentioned can't be raised with code you posted. That's why it is important to post exactly what you do, just as I did. Doing so, there's no doubt in what you have, what you did and how Oracle responded. Anything else is just a matter of speculation.

Upvotes: 2

Related Questions