Dynamic or Static
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