Home » SQL & PL/SQL » SQL & PL/SQL » Variables
icon3.gif  Variables [message #219440] Wed, 14 February 2007 07:08 Go to next message
abis123
Messages: 31
Registered: February 2007
Member
I have some code shown below, what I want to do is replace all instances of the number 9 with a variable, which holds a count of results. If someone could inform me how to use declare and use variables and where in my code to put them I would be very grateful.

Procedure GET_TIMES (
o_cursor OUT T_CURSOR
)
IS

BEGIN

OPEN o_cursor FOR

SELECT TRUNC(((SELECT COUNT(DATE_OF_ALLEGATION - DATE_OF_CALL) FROM tablename.columnname
WHERE (DATE_OF_ALLEGATION - DATE_OF_CALL) = 0)/9)*100) AS NO_DAYS,
TRUNC(((SELECT COUNT(DATE_OF_ALLEGATION - DATE_OF_CALL) FROM tablename.columnname
WHERE (DATE_OF_ALLEGATION - DATE_OF_CALL) = 1)/9)*100) AS ONE_DAY,
TRUNC(((SELECT COUNT(DATE_OF_ALLEGATION - DATE_OF_CALL) FROM tablename.columnname
WHERE (DATE_OF_ALLEGATION - DATE_OF_CALL) = 2)/9)*100) AS TWO_DAYS,
TRUNC(((SELECT COUNT(DATE_OF_ALLEGATION - DATE_OF_CALL) FROM tablename.columnname
WHERE (DATE_OF_ALLEGATION - DATE_OF_CALL) = 3)/9)*100) AS THREE_DAYS,
TRUNC(((SELECT COUNT(DATE_OF_ALLEGATION - DATE_OF_CALL) FROM tablename.columnname
WHERE (DATE_OF_ALLEGATION - DATE_OF_CALL) = 4)/9)*100) AS FOUR_DAYS,
TRUNC(((SELECT COUNT(DATE_OF_ALLEGATION - DATE_OF_CALL) FROM tablename.columnname
WHERE (DATE_OF_ALLEGATION - DATE_OF_CALL) = 5)/9)*100) AS FIVE_DAYS,
TRUNC(((SELECT COUNT(DATE_OF_ALLEGATION - DATE_OF_CALL) FROM tablename.columnname
WHERE (DATE_OF_ALLEGATION - DATE_OF_CALL) > 5)/9)*100) AS MORE_THAN_FIVE_DAYS
FROM DUAL;
END GET_TIMES;
end;
Re: Variables [message #219443 is a reply to message #219440] Wed, 14 February 2007 08:02 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Procedure GET_TIMES (var1 IN number, o_cursor OUT T_CURSOR) IS 
...


Then just use var1 anywhere you'd like. Of course, you'd have the edit your procedure and change every occurrence of '9' with 'var1.'
Re: Variables [message #219445 is a reply to message #219443] Wed, 14 February 2007 08:06 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

This is noway best practice(even ugly) but it's an option as well.


CREATE OR REPLACE PROCEDURE x (p_return_cur OUT SYS_REFCURSOR)
is
l_numrecs number default 9;
BEGIN
OPEN p_return_cur FOR
'SELECT table_name FROM all_tables WHERE rownum <= ' || l_numrecs ;
END x;

icon14.gif  Re: Variables [message #219447 is a reply to message #219443] Wed, 14 February 2007 08:11 Go to previous message
abis123
Messages: 31
Registered: February 2007
Member
Thanks very much for both of your answers! After some MAJOR messing around, and probably the wrong way I have found a way as well, pretty much the exact same as one of yours. Its:

as
Procedure GET_TIMES (
o_cursor OUT T_CURSOR,
total_count OUT NUMBER <-- i created another out number
)
IS

BEGIN

SELECT COUNT(REFERENCE) INTO total_count FROM tablename.columnname; <-- then i can call total_count anywhere

[Updated on: Wed, 14 February 2007 08:12]

Report message to a moderator

Previous Topic: dbms_utility.get_parameter_vaue
Next Topic: Add a % at the end of all my results.
Goto Forum:
  


Current Time: Mon Dec 05 21:02:06 CST 2016

Total time taken to generate the page: 0.05606 seconds