Geek Answers Handbook

ORACLE PL / SQL: functions and additional parameters, how?

I am looking for the best way to create a function that cannot accept any parameters and return all results, but also accepts parameters and returns these results.

The standard that I had at my work is this:

FUNCTION get_records (
  i_code                                 IN records.code%type := NULL,
  i_type                                 IN records.type%type := NULL
) RETURN results

The problem is that I want to return records that are of type NULL and using:

WHERE type = nvl(i_type, type)

It only returns records with actual types and not with null records .. for obvious reasons. I am just wondering if there is a standard way to do this that can be implemented in all the functions that we use. Coincidentally, if I provide a parameter ... I do not want the NULL value of this field.

+5
source share
5

,

 type = i_type OR (i_type IS NULL AND type IS NULL)

, param null, ( ) . , ...

( 5, )

WITH TESTDATA AS (
        SELECT LEVEL dataId
          FROM DUAL
         CONNECT BY LEVEL <= 100
        UNION
        SELECT NULL 
         from dual
)
SELECT * 
  FROM TESTDATA
 where dataId = :n or (:n is null AND dataId is null) ;

, : n = 6

DATAID                 
---------------------- 
6

( , ) NVL

, sql

http://www.oracle.com/technetwork/issue-archive/2009/09-jul/o49asktom-090487.html

+5

:

FUNCTION get_records (
  i_code                                 IN records.code%type,
  i_type                                 IN records.type%type
) RETURN results;

FUNCTION get_records (
  i_code                                 IN records.code%type
) RETURN results;

FUNCTION get_records RETURN results;

. i_type , , , i_code - .

+4

, , DEFAULT , ? ( .)

  • DEFAULT.

    CREATE OR REPLACE FUNCTION get_records (
    i_code IN records.code%type DEFAULT NULL,
    i_type IN records.type%type DEFAULT NULL
) RETURN results

№ 1

, , i_type NULL. :

CREATE OR REPLACE FUNCTION get_records (
    i_code IN records.code%TYPE DEFAULT NULL,
    i_type IN records.type%TYPE DEFAULT NULL
) RETURN results
BEGIN
    IF (i_type IS NULL) THEN
        select *
            from table
    ELSE
        select *
            from table
            where type = NVL(i_type, type)
    END IF

    EXCEPTION
        WHEN OTHERS THEN
            NULL
END

, , .

№ 2

Oracle, , :

Oracle/PLSQL: NVL Function

, NVL SELECT, WHERE.

, ? ?

+3

: NULL " ", , , NULL.

, , , . , , true, . , , , NULL.

- . "ANY", . , . - , get_records( PKG.ALL_CODES, 'type1' ).

+3

(, p_product - ):

, t.product = decode (p_product, null, t.product, p_product)

procedure get_data(p_product in number := null)
...
select *
from tbl t
where t.product = decode(p_product, null, t.product, p_product);
...

This will create a “where 1 = 1” scenario when the product is not transferred, otherwise use the product. One of the potential drawbacks is that it analyzes the same execution plan regardless of the parameters.

+1
source

More articles:


All Articles