In article <8c3843$ns1$1_at_nnrp1.deja.com>,
Sean <dolans_at_my-deja.com> wrote:
> Please excuse the inexperience with this question, but I am looking to
> perform the following: I have a table called foobar with columns A B C
> and D
>
> foobar
> -----------
> A
> B
> C
> D
>
> I want to be able to call the same package/procedure and with it
return
> the column values I want. For example, I want columns A and C this
> time, but next time the user wants A C and D. I could create a bunch
> of cursors for each combination I could expect the user to ask and
then
> do an IF statement on the parameters they pass in, but is there a
> better way to do it?
>
> I ask this because I am trying to create a C component that returns
the
> data based on what parameters are passed in. If the user only wants to
> see two columns and not four, I am wondering if it is inefficent to do
> a search on all the columns and return all the data back to the
object.
>
> Sorry if this is "off-the-wall".
> Thanks,
> Sean
>
In Oracle8i, release 8.1.5 and up this would look like this:
ops$tkyte_at_8i> create or replace package my_pkg
2 as
3 type refcur is ref cursor;
4
5 procedure do_query( p_clist in varchar2,
6 p_cursor in out refcur,
7 p_rows in number );
8 end;
9 /
Package created.
ops$tkyte_at_8i>
ops$tkyte_at_8i>
ops$tkyte_at_8i> create or replace package body my_pkg
2 as
3
4 procedure do_query( p_clist in varchar2,
5 p_cursor in out refcur,
6 p_rows in number )
7 is
8 begin
9 open p_cursor for 'select ' || p_clist ||
10 ' from emp
11 where rownum < :r' using p_rows;
12 end;
13
14 end;
15 /
Package body created.
ops$tkyte_at_8i>
ops$tkyte_at_8i>
ops$tkyte_at_8i> variable x refcursor
ops$tkyte_at_8i> set autoprint on
ops$tkyte_at_8i>
ops$tkyte_at_8i> exec my_pkg.do_query( 'ename, empno', :x, 2 );
PL/SQL procedure successfully completed.
ENAME EMPNO
---------- ----------
SMITH 7369
ops$tkyte_at_8i> exec my_pkg.do_query( 'empno, job, hiredate', :x, 5 );
PL/SQL procedure successfully completed.
EMPNO JOB HIREDATE
---------- --------- ---------
7369 CLERK 17-DEC-80
7499 SALESMAN 20-FEB-81
7521 SALESMAN 22-FEB-81
7566 MANAGER 02-APR-81
In Oracle8.0 and before you would have to use DBMS_SQL. Its more
complicated and doesn't return a CURSOR but rather a PLSQL handle to a
result set (you cannot map this to a recordset in odbc or a resultset
in jdbc as you can with the ref cursor above).
> PS How much performance degredation occurs when you use the dynamic
> sql package when specifying a SQL statement that the WHERE clause
could
> be different each time? Thanks again!
>
Well -- thats the worst case. If you use bind variables -- you stand a
chance of good performance. You really want to reuse query plans as
much as possible (in many systems -- the time to parse and optimize a
query is longer/harder then actually executing the query itself).
If you use bind variables as I did above, at least when 2 people use
the same set of columns to where on (and select on), you will reuse the
already developed query plans stored in the shared sql area. avoiding
hard coding (concatenating) in values in a query. For example -- do
not code:
open x for 'select * from emp where ename = ''KING'' ';
rather code:
open x for 'select * from emp where ename = :x' using 'KING';
(where 'KING' is probably in a plsql variable some where). If you do
not know the NUMBER of bind variables at compile time -- look at using
an application context in Oracle8i (when using dbms_sql in 8.0 and
before -- you have a more API driven approach to binding and so not
knowing the number of bind variables at compile time is OK -- you'll
just call bind N times at run time. With 8i, the using clause would
have you know the number of bind variables at compile time). That
might look more like this:
ops$tkyte_at_8i> create or replace package my_pkg
2 as
3 type refcur is ref cursor;
4
5 procedure do_query( p_clist in varchar2,
6 p_cursor in out refcur,
7 p_rows in number,
8 p_ename_like in varchar2 DEFAULT NULL,
9 p_job_like in varchar2 DEFAULT NULL );
10 end;
11 /
Package created.
ops$tkyte_at_8i>
ops$tkyte_at_8i> create or replace CONTEXT my_pkg_ctx using MY_PKG;
Context created.
ops$tkyte_at_8i>
ops$tkyte_at_8i>
ops$tkyte_at_8i> create or replace package body my_pkg
2 as
3
4 procedure do_query( p_clist in varchar2,
5 p_cursor in out refcur,
6 p_rows in number,
7 p_ename_like in varchar2 DEFAULT NULL,
8 p_job_like in varchar2 DEFAULT NULL )
9 is
10 l_query long;
11 begin
12
13 dbms_session.set_context( 'MY_PKG_CTX', 'ENAME_LIKE', '%'
|| upper(p_ename_like) || '%' );
14 dbms_session.set_context( 'MY_PKG_CTX', 'JOB_LIKE', '%'
|| upper(p_job_like) || '%' );
15
16 l_query := 'select ' || p_clist || ' from emp where rownum
< :r ';
17 if ( p_ename_like is not null ) then
18 l_query := l_query || ' and ename like sys_context
( ''MY_PKG_CTX'', ''ENAME_LIKE'' ) ';
19 end if;
20 if ( p_job_like is not null ) then
21 l_query := l_query || ' and job like sys_context
( ''MY_PKG_CTX'', ''JOB_LIKE'' ) ';
22 end if;
23 open p_cursor for l_query using p_rows;
24 end;
25
26 end;
27 /
Package body created.
ops$tkyte_at_8i>
ops$tkyte_at_8i>
ops$tkyte_at_8i> variable x refcursor
ops$tkyte_at_8i> set autoprint on
ops$tkyte_at_8i>
ops$tkyte_at_8i> REM first 3 emps we see in the table:
ops$tkyte_at_8i> exec my_pkg.do_query( 'ename, empno', :x, 3 );
PL/SQL procedure successfully completed.
ENAME EMPNO
---------- ----------
SMITH 7369
ALLEN 7499
ops$tkyte_at_8i>
ops$tkyte_at_8i> REM emps where ename like '%A%':
ops$tkyte_at_8i> exec my_pkg.do_query( 'ename, empno', :x, 100, 'a' );
PL/SQL procedure successfully completed.
ENAME EMPNO
---------- ----------
ALLEN 7499
WARD 7521
MARTIN 7654
BLAKE 7698
CLARK 7782
ADAMS 7876
JAMES 7900
7 rows selected.
ops$tkyte_at_8i>
ops$tkyte_at_8i> REM emps where ename like '%L%' and job like '%ER%':
ops$tkyte_at_8i> exec my_pkg.do_query( 'ename, empno', :x,
100, 'l', 'er' );
PL/SQL procedure successfully completed.
ENAME EMPNO
---------- ----------
BLAKE 7698
CLARK 7782
MILLER 7934
ops$tkyte_at_8i>
ops$tkyte_at_8i> REM emp where job like '%PRES%':
ops$tkyte_at_8i> exec my_pkg.do_query( 'ename, empno', :x, 100, p_job_like
=> 'pres' );
PL/SQL procedure successfully completed.
ENAME EMPNO
---------- ----------
KING 7839
ops$tkyte_at_8i>
ops$tkyte_at_8i> REM shows the values of the context as they are currently
set:
ops$tkyte_at_8i> select * from v$context;
NAMESPACE ATTRIBUTE VALUE
--------------- ---------- -------
MY_PKG_CTX ENAME_LIKE %%
MY_PKG_CTX JOB_LIKE %PRES%
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
--
Thomas Kyte tkyte_at_us.oracle.com
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Sat Apr 01 2000 - 07:47:29 CST
