Reputation: 1092
I have a procedure which receives a record as an IN parameter (the procedure also has an OUT parameter.)
CREATE OR REPLACE PACKAGE p
PROCEDURE p_select( p_filter_params IN t_filter_params
, p_order_list OUT SYS_REFCURSOR);
END p;
CREATE OR REPLACE PACKAGE BODY p
PROCEDURE p_select( p_filter_params IN t_filter_params
, p_order_list OUT SYS_REFCURSOR) AS
BEGIN
IF p_filter_params IS NULL
THEN
NULL;
END IF;
END p_select,
END p;
executable section
DECLARE
TYPE t_filter_params IS RECORD
( f_name customers.cust_first_name%TYPE
, l_name customers.cust_last_name%TYPE
, city customers.cust_address.city%TYPE
, from_date orders.order_date%TYPE
, to_date orders.order_date%TYPE);
vr_params t_filter_params;
TYPE ref_cursor IS REF CURSOR;
v_cursor ref_cursor;
BEGIN
/*vr_params.f_name := 'john';
vr_params.l_name := 'smith';
vr_params.city := 'Dallas';
vr_params.from_date := SYSDATE - 100;
vr_params.to_date := SYSDATE;*/
--p.p_select(vr_params, v_cursor);
p.p_select(null, v_cursor);
end;
Can someone tell me how to make it work the IF statement from procedure, please?
Thank you very much, Mikcutu.
Upvotes: 3
Views: 8215
Reputation: 11
One solution would be to work with reverse logic. It is e.g. not possible to ask:
IF my_record IS NULL THEN
do something;
END IF;
or
IF my_record.my_attibute IS NULL THEN
do something;
END IF;
Instead it is possible to ask:
IF my_record.my_attibute IS NOT NULL THEN
go on processing;
ELSE
do something;
END IF;
Upvotes: 1
Reputation: 168806
Don't complicate things passing a record, just pass the individual parameters:
CREATE OR REPLACE PACKAGE p
AS
PROCEDURE p_select(
in_f_name customers.cust_first_name%TYPE
, in_l_name customers.cust_last_name%TYPE
, in_city customers.cust_address.city%TYPE
, in_from_date orders.order_date%TYPE
, in_to_date orders.order_date%TYPE
, out_order_list OUT SYS_REFCURSOR
);
END p;
CREATE OR REPLACE PACKAGE BODY p
AS
PROCEDURE p_select(
in_f_name customers.cust_first_name%TYPE
, in_l_name customers.cust_last_name%TYPE
, in_city customers.cust_address.city%TYPE
, in_from_date orders.order_date%TYPE
, in_to_date orders.order_date%TYPE
, out_order_list OUT SYS_REFCURSOR
) AS
BEGIN
OPEN out_order_list FOR
SELECT *
FROM orders o INNER JOIN customers c ON ( o.customer_id = c.customer_id )
WHERE ( in_f_name IS NULL OR c.cust_first_name = in_f_name )
OR ( in_l_name IS NULL OR c.cust_last_name = in_l_name )
OR ( in_city IS NULL OR c.cust_address.city = in_city )
OR ( in_from_date IS NULL OR o.order_date >= in_from_date )
OR ( in_to_date IS NULL OR o.order_date <= in_to_date );
END p_select,
END p;
Upvotes: 1
Reputation: 22979
I believe you can not:
SQL> create or replace package p is
2 type tType is record ( a number, b number);
3 --
4 procedure proc(pIn IN tType);
5 end;
6 /
Package created.
SQL> create or replace package body p is
2 procedure proc(pIn IN tType) is
3 begin
4 if pIn is null then
5 dbms_output.put_line('NULL');
6 else
7 dbms_output.put_line('NOT NULL');
8 end if;
9 end;
10 end;
11 /
Warning: Package Body created with compilation errors.
SQL> sho err
Errors for PACKAGE BODY P:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/9 PL/SQL: Statement ignored
4/12 PLS-00306: wrong number or types of arguments in call to 'IS
NULL'
With a slightly different approach, you could use a schema-level type:
SQL> create or replace type tType is object ( a number, b number);
2 /
Type created.
SQL> create or replace package p2 is
2 --
3 procedure proc(pIn IN tType);
4 end;
5 /
Package created.
SQL> create or replace package body p2 is
2 procedure proc(pIn IN tType) is
3 begin
4 if pIn is null then
5 dbms_output.put_line('NULL');
6 else
7 dbms_output.put_line('NOT NULL');
8 end if;
9 end;
10 end;
11 /
Package body created.
SQL>
Upvotes: 1
Reputation: 24591
Records are somehow special. If you set a record to null
all the record properties are set to null, but the record itself still exists.
The first thing that comes to my mind is either checking if some sort of id
is null on the record - that would mean the record is empty.
You don't have an id, but this could lead to another solution: adding another property which is null whenever it is not filled.
The third approach: just go through every property and check whether every property is null
.
And finally, the best, reject using RECORD
and use OBJECT
instead: it works exactly as you expect it to work.
Upvotes: 2