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,
resultCursor 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.
As Justin Cave pointed out, what you're doing looks like an attempt to mimic behaviour in another database system. It isn't obvious that you really need a (possibly global temporary) table; you can query v$sql directly when you open the cursor:
create or replace procedure buildreport_sql_performance
(
userchoice in int,
resultcursor out sys_refcursor
) as
begin
open resultcursor for
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;
end buildreport_sql_performance;
/
Or without duplicating the to_date() call:
create or replace procedure buildreport_sql_performance
(
userchoice in int,
resultcursor out sys_refcursor
) as
begin
open resultcursor for
select sql_text, sql_fulltext, cpu_time, last_load_time from v$sql
where to_date(substr(last_load_time, 1, 10),'YYYY-MM-DD') > case
when userchoice = 3 then add_months(trunc(sysdate), -12) --One Year
when userchoice = 2 then add_months(trunc(sysdate), -1) --One Month
when userchoice = 1 then trunc(sysdate -7) --One Week
when userchoice = 0 then trunc(sysdate -1) --one Day
else null
end;
end buildreport_sql_performance;
/
