Trex87 Trex87 - 1 month ago 4
SQL Question

How many queries I can implement inside the procedure in oracle sql?

create or replace PROCEDURE BUILDREPORT_sql_performance
(
userChoice IN INT
) AS
BEGIN
DELETE FROM TMP_sql_performance;

INSERT INTO TMP_sql_performance
(sql_text,
sql_fulltext,
cpu_time,
last_load_time)

(SELECT sql_text, sql_fulltext, cpu_time,
last_load_time from v$sql
WHERE (CASE
WHEN userChoice = 3 AND TO_DATE(substr(last_load_time, 1, 10),'YYYY-MM-DD') > ADD_MONTHS(TRUNC(SYSDATE), -12) THEN 1 --One Year
WHEN userChoice = 2 AND TO_DATE(substr(last_load_time, 1, 10),'YYYY-MM-DD') > ADD_MONTHS(TRUNC(SYSDATE), -1) THEN 1 --One Month
WHEN userChoice = 1 AND TO_DATE(substr(last_load_time, 1, 10),'YYYY-MM-DD') > TRUNC(SYSDATE -7) THEN 1 --One Week
WHEN userChoice = 0 AND TO_DATE(substr(last_load_time, 1, 10),'YYYY-MM-DD') > TRUNC(SYSDATE -1) THEN 1 --one Day
ELSE 0
END) = 1);

select * from tmp_sql_performance;

END BUILDREPORT_sql_performance;


And it gives me: Error(24,3): PLS-00428: an INTO clause is expected in this SELECT statement. Probably I can query only once or twice in the procedure

Answer

You have to select into something, which has to be a variable, record or collection (depending on the data you're getting - number of columns and rows). Or you can have a cursor that you iterate over and do something with (including possibly, for debugging purposes only, logging with dbms_output).

It sounds like you want to return the query result to the procedure caller, which you can do with a ref cursor OUT parameter:

create or replace PROCEDURE BUILDREPORT_sql_performance 
(
  userChoice IN INT,
  resultCursot OUT SYS_REFCURSOR
) AS 
BEGIN 
DELETE FROM TMP_sql_performance;

INSERT INTO TMP_sql_performance
(sql_text,
sql_fulltext,
cpu_time,
last_load_time)

   (SELECT sql_text, sql_fulltext, cpu_time,
last_load_time from v$sql 
WHERE (CASE 
        WHEN userChoice = 3 AND TO_DATE(substr(last_load_time, 1, 10),'YYYY-MM-DD') > ADD_MONTHS(TRUNC(SYSDATE), -12) THEN 1    --One Year
        WHEN userChoice = 2 AND TO_DATE(substr(last_load_time, 1, 10),'YYYY-MM-DD') > ADD_MONTHS(TRUNC(SYSDATE), -1)  THEN 1    --One Month
        WHEN userChoice = 1 AND TO_DATE(substr(last_load_time, 1, 10),'YYYY-MM-DD') > TRUNC(SYSDATE -7) THEN 1                  --One Week
        WHEN userChoice = 0 AND TO_DATE(substr(last_load_time, 1, 10),'YYYY-MM-DD') > TRUNC(SYSDATE -1) THEN 1                  --one Day
      ELSE 0
    END) = 1);

OPEN resultCursor FOR
  select * from tmp_sql_performance;

END BUILDREPORT_sql_performance;
/

The call to the procedure then needs to pass a sys_refcursor variable, and do something with the resulting cursor.

For testing purposes you can use an anonymous block, and - at leats in SQL*Plus and SQL Developer - a client-defined bind variable:

variable rc refcursor

exec buildreport_sql_performance(42, :rc);

print rc

The exec is a shorthand for an anonymous block, so it's the same as doing:

begin
  buildreport_sql_performance(42, :rc);
end;
/

Notice the colon before :rc in the procedure call. That indicated you're passing a bind variable that is defined outside the PL/SQL block.

The variable and print commands are Oracle client commands, not SQL. Other client might not recognise them, though some mimic the Oracle behaviour.


You said "whenever I execute that procedure, it must show me the updated table". That is up to whoever or whatever executes it. You can't display anything directly from PL/SQL - it isn't really designed as an interactive language. You may end up calling this from, say, a Java program via JDBC, and will then have Java code that iterates over the result set and does something with the values in each row. Or it may be called from other PL/SQL, or a scheduled job, or somewhere else there is no logical place for output to be displayed. In the example above the client program is calling the procedure and displaying the OUT cursor, but only if explicitly told to.

Comments