| Procedure for counting rows in a table [message #629020] |
Tue, 02 December 2014 05:54  |
 |
internship
Messages: 6
Registered: December 2014
|
Junior Member |
|
|
I need to write a procedure that has these parameters:
p_transaction_id (number),
p_session_id(number),
p_module(varchar2),
p_row_size (number default null),
p_date_from (optional),
p_date_to(optional)
The procedure outputs (dbms_output) rows out of the table1 which has these columns:
transaction_id (number),
session_id(number),
module(varchar2),
entry_date(date)
If the row_size is 0 or null, then it must return maximum of 1000 rows. One of the parameters transaction_id, session_id, module is mandatory, so I have to perform a control on the input parameters and return the right exception if all of the 3 parameters are empty. Those 3 parameters work as a filter in the where clause. If, for example,the parameter transaction_id is null, then that column is not considered in the output. So, if I entered transaction_id, it means that it will perform this:
select * from table1 where transaction_id=p_transaction_id;
Also date_from and date_to are included in the where clause. If for example, date_from is entered, then you get the data from that date and forward, if it is not entered then I must get all the data. The same is applied with date_to only backwards.
Can anyone help?
|
|
|
|
| Re: Procedure for counting rows in a table [message #629021 is a reply to message #629020] |
Tue, 02 December 2014 06:00  |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
Quote:Can anyone help?
Probably, but I would suggest that tyou show that you aren't just looking for other people to do your work for you and show that you have actually attempted to code some of this yourself.
|
|
|
|
|
|
| Re: Procedure for counting rows in a table [message #629023 is a reply to message #629022] |
Tue, 02 December 2014 06:16 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
OK,
1. It's unusual for a normal business requirement to rely on dbms_output, is that definitely hwat is required?
2. Do you have any experience of working with cursors in PL/SQL?
3. Here's a hint, see what the below gives you when you use it in a 'normal' query and replace the P_TRANSACTION_ID with either a null or a specific value.
where nvl(P_TRANSACTION_ID, TRANSACTION_ID)= TRANSACTION_ID
|
|
|
|
|
|
| Re: Procedure for counting rows in a table [message #629116 is a reply to message #629029] |
Wed, 03 December 2014 08:22 |
|
internship
Messages: 6
Registered: December 2014
|
Junior Member |
|
|
The procedure outputs (dbms_output) rows out of the table1 which has these columns:
transaction_id (number), session_id(number), module(varchar2), entry_date(date)
I need to write a procedure that has these parameters:
p_transaction_id (number),p_session_id(number),p_module(varchar2),p_return_rows (number default null),p_date_from (optional),p_date_to(optional)
If p_return_rows is 0 or null, then it must return maximum of 1000 rows. One of the parameters transaction_id, session_id, module is mandatory, so I have to perform a control on the input parameters and return the right exception if all of the 3 parameters are empty. Those 3 parameters work as a filter in the where clause. If for example,the parameter transaction_id is null, then that column is not considered in the output. So, if I entered transaction_id, it means that it will perform this:
select * from table1 where transaction_id=p_transaction_id;
Also date_from and date_to are included in the where clause. If for example, date_from is entered, then you get the data from that date and forward, if it is not entered then I must get all the data. The same is applied with date_to only backwards.
Internet has been down, so i couldn't reply. here are the updates:
First of all I have wrongly asked the community for help, I don't won't the row number I want actual rows to be outputed if the parameters are satisfied.
This is what i have done so far and i need help especially for dbms_output because it gives me number of rows instead of actual rows from the table1.
create or replace PROCEDURE PROC1(
P_TRANSACTION_ID IN NUMBER,
P_SESSION_ID IN NUMBER,
P_MODULE IN VARCHAR2,
P_RETURN_ROWS IN NUMBER DEFAULT NULL,
P_DATE_FROM IN DATE DEFAULT NULL,
P_DATE_TO IN DATE DEFAULT NULL)
IS
type ALL_table1_TYPE
is
TABLE OF table1%ROWTYPE INDEX BY pls_integer;
V_ALL ALL_table1_TYPE;
v_row pls_integer;
BEGIN
SELECT * bulk collect
INTO V_ALL
FROM table1
where
TRANSACTION_ID =P_TRANSACTION_ID
AND (p_transaction_id IS NULL)
OR (p_transaction_id IS NOT NULL
and TRANSACTION_ID = P_TRANSACTION_ID)
AND (P_session_id IS NULL)
or (P_SESSION_ID is not null
and SESSION_ID = P_SESSION_ID)
AND (p_module IS NULL)
or (P_MODULE is not null
and MODULE = P_MODULE)
and (P_DATE_FROM is null)
or (ENTRY_TIME >= P_DATE_FROM);
v_row :=v_all.first;
WHILE (v_row IS NOT NULL)
LOOP
dbms_output.put_line ('Rows are: ' || v_all(v_row));
v_row := v_all.next(v_row);
END LOOP;
END;
|
|
|
|
|
|
| Re: Procedure for counting rows in a table [message #629120 is a reply to message #629119] |
Wed, 03 December 2014 08:37 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
And OR's should always be bracketed, otherwise you'll get results you don't expect.
ie don't do
SELECT *
FROM ...
WHERE ....
AND ....
AND ...
OR ....
do do:
SELECT *
FROM ...
WHERE ....
AND ....
(AND ...
OR ....
)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
