Dynamic or Static

From: <foredstest_at_gmail.com>
Date: Wed, 11 Jul 2012 05:26:41 -0700 (PDT)
Message-ID: <415cfd90-7bb4-4626-ab9e-1097a01c1c43_at_googlegroups.com>

Good day.
Please response your opinion. Assume a function should return result set based on given parameters, but not all parameters can be given (NULL valued). Which method is better and why?

CREATE OR REPLACE FUNCTION dynamic_sql( pparameter table_name.col_name%TYPE ) RETURN SYS_REFCURSOR IS

    RESULT SYS_REFCURSOR;
    vSql VARCHAR2( 100 );
    vCursor NUMBER;
    vNumber NUMBER;
BEGIN
    vSql := 'SELECT * FROM table_name';
    IF pparameter IS NOT NULL THEN

        vSql := vSql || ' WHERE col_name LIKE :pparameter';     END IF;
    vCursor := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE( vCursor, vSql, DBMS_SQL.NATIVE );     IF pparameter IS NOT NULL THEN

        DBMS_SQL.BIND_VARIABLE( vCursor, 'pparameter', pparameter );     END IF;
    vNumber := DBMS_SQL.EXECUTE( vCursor );     RESULT := DBMS_SQL.TO_REFCURSOR( vCursor );     RETURN ( RESULT );
END dynamic_sql;

CREATE OR REPLACE FUNCTION static_sql( pparameter table_name.col_name%TYPE ) RETURN SYS_REFCURSOR IS

    RESULT SYS_REFCURSOR;
BEGIN
    OPEN RESULT FOR SELECT * FROM table_name WHERE pparameter IS NULL OR col_name LIKE pparameter;     RETURN ( RESULT );
END static_sql; Received on Wed Jul 11 2012 - 07:26:41 CDT