View com Parametro: É possível ?

[moacirm]

Tenho um form's que utiliza como tabela base uma view.
É possível fazer uma view que receba parâmetros?

[dr_gori]

Não.
Views não recebem parâmetros.
Mas existe uma forma de fazer isso, usando uma tabela e fazendo um JOIN nela. Daí basta você colocar nessa tabela o que você está querendo buscar.

Outra forma, é usar os CONTEXTOS, que são as "variáveis globais" do SQL. Vou deixar abaixo uma dica do Chiappa sobre o assunto.

Selecionar tudo

Pode consultar o pai da matéria, o manual "SQL Reference", você vai
confirmar que views NÂO PODEM ter parâmetros. O que você PODE fazer é
colocar os valores num "lugar" público do banco, que o SELECT da view
possa recuperar : se fossem muitos valores seria uma tabela (uma GTT,
muito provavelmente!!), mas se forem poucos valores, ou um só,
realmente não faz sentido tabela, é um desperdício, você usa as
"variáveis globais" de SQL, os CONTEXTOS. Exemplo :

system@O8IR3:SQL>create or replace context my_ctx USING
2 my_procedure;

Contexto criado.

system@O8IR3:SQL>create or replace procedure my_procedure(
2 p_name in varchar2, p_value in number)
3 as
4 begin
5 dbms_session.set_context( 'my_ctx', p_name, p_value );
6 end;
7 /

Procedimento criado.

system@O8IR3:SQL>grant execute on MY_PROCEDURE to scott;

Operação de Grant bem-sucedida.

scott@O8IR3:SQL>create or replace view V_TESTE as
2 select deptno, dname from dept where deptno > sys_context(
'my_ctx', 'deptno')
3 UNION ALL
4 select deptno, dname from dept where deptno < sys_context(
'my_ctx', 'deptno')
5 ;

View criada.

scott@O8IR3:SQL>exec system.MY_PROCEDURE('deptno', 30);

Procedimento PL/SQL concluído com sucesso.

scott@O8IR3:SQL>select * from V_TESTE;

DEPTNO DNAME
------------------ --------------
40 OPERATIONS
10 ACCOUNTING
20 RESEARCH

scott@O8IR3:SQL>


[]s

Chiappa 

Aqui tem outro texto em ingles sobre o assunto vindo do GURU Tom Kyte:
http://asktom.oracle.com/~tkyte/article2/index.html
http://asktom.oracle.com/pls/ask/f?p=49 ... 8401763279

Selecionar tudo

Hi Tom,
    I want to declare multiple cursors based on the values
passed through a procedure ie, only where conditions of the
cursors will change. The body of the procedure is
same for all the cursors.
  Pls. suggegst a solution.

Thanking you,

regards,
Pratty.
 


and we said...

Sounds like a good use of REF CURSORS to me.  Lets say you wanted to build a
generic routine that would look at the inputs passed to it and build a WHERE
clause for each NON-NULL parameter passed.  This would result in a large number
of statically defined cursors so we'll use a ref cursor instead. 

I'll demonstrate below.  I'll write a write that will print out some EMP data. 
This routine will take upto 3 inputs to constrain the result set.  I want to
have upto 8 different cursors possible here

o 1 with NO where clause (all inputs null)
o 3 with a single predicate
o 3 with "pairs" of predicate conditions
o 1 with all three predicate conditions

Additionally, since the use of BIND VARIABLES is one of the MOST important
things in programming Oracle -- I'll want to make sure I use them as well.  This
will be trickly since I don't know if I'll have 0, 1, 2, or 3 of them until
runtime.  I'll use an application context to solve that problem.

Here is a sample implementation:


ops$tkyte@ORA8I.WORLD> create or replace context MY_CTX using MY_PROCEDURE
  2  /

Context created.

That created our application context and bound it to our yet to be created
procedure "MY_PROCEDURE".  Note that only MY_PROCEDURE will be able to set
values in this context.  See

http://asktom.oracle.com/~tkyte/article2/index.html

for more info on application contexts and their use

ops$tkyte@ORA8I.WORLD> create or replace
     procedure p ( p_str in varchar2 )
  2  is
  3     l_str   long := p_str || chr(10);
  4     l_piece long;
  5     n       number;
  6  begin
  7      loop
  8          exit when l_str is null;
  9          n := instr( l_str, chr(10) );
 10          l_piece := substr( l_str, 1, n-1 );
 11          l_str   := substr( l_str, n+1 );
 12             loop
 13                exit when l_piece is null;
 14                dbms_output.put_line( substr( l_piece, 1,
                                                        250 ) );
 15                l_piece := substr( l_piece, 251 );
 16          end loop;
 17     end loop;
 18  end;
 19  /

Procedure created.


P is just a little procedure I use to print things out nicer then dbms_output
would.  I use it below to dump the dynamically generated query so we can see
what was built for each execution.  It is not really relevant to the example,
just part of the demonstration...

ops$tkyte@ORA8I.WORLD> create or replace
  2  procedure my_procedure( p_ename   in varchar2 default NULL,
  3                          p_hiredate  in date default NULL,
  4                          p_sal       in number default NULL)
  5  as
  6      type rc is REF CURSOR;
  7 
  8      l_cursor rc;
  9      l_query  varchar2(512)
 10               default 'select * from emp where 1 = 1 ';
 11 
 12      cursor l_template is select * from emp;
 13      l_rec  l_template%rowtype;
 14 

Here I use what I call a "TEMPLATE" cursor.  I like to use these with my ref
cursors.  I use them to define a record to fetch into.  Here, in this simple
example, I could have skipped it and just defined l_rec as EMP%rowtype -- but I
wanted to show how this would work if you didn't select * from a single table
but had many columns from many tables.  This just helps me create a nice record.
 The template query ONLY has a SELECT and a FROM.  I never put a WHERE clause on
it (even when joining) since I never use it any where.  I just use it to get the
default datatypes, names and so on for a record definition right below it.

 15  begin
 16 
 17      if ( p_ename is NOT NULL ) then
 18          dbms_session.set_context( 'MY_CTX', 'ENAME',
 19                                    '%'||upper(p_ename)||'%');
 20          l_query := l_query ||
 21               ' and ename like
 22                 sys_context( ''MY_CTX'', ''ENAME'' ) ';
 23      end if;
 24 

for each input -- i'm inspecting it to see if it is non-null.  If it is, I add
to the where clause and set the value in the context.  Notice how in the where
clause -- I always use the SYS_CONTEXT function.  I NEVER put the literal value
in to the query (that would be very bad and would trash the shared pool -- very
extremely important to use bind variables).  Note also the use of '' to get a
single ' into the where clause!

 25      if ( p_hiredate is NOT NULL ) then
 26          dbms_session.set_context( 'MY_CTX', 'HIREDATE',
 27                    to_char(p_hiredate,'yyyymmddhh24miss'));
 28          l_query := l_query ||
 29                ' and hiredate >
 30                    to_date(
 31                       sys_context( ''MY_CTX'',

 32                                    ''HIREDATE'' ),
 33                       ''yyyymmddhh24miss'') ';
 34      end if;

Note here how I am careful to preserve the date and time component -- if
necessary!  Always wrap the sys_context in a TO_DATE call if you are comparing
to a DATE to avoid implicit conversions in the query at runtime!


 35 
 36      if ( p_sal is NOT NULL ) then
 37          dbms_session.set_context( 'MY_CTX', 'SAL', p_sal);
 38          l_query := l_query ||
 39                ' and sal >
 40                   to_number(
 41                       sys_context( ''MY_CTX'',
 42                                    ''SAL'' )
 43                             ) ';
 44      end if;
 45 

Same caveat for the NUMBER here.  Use TO_NUMBER to avoid IMPLICIT
conversions

 46      p( l_query );
 47 
 48      open l_cursor for l_query;
 49 
 50      loop
 51          fetch l_cursor into l_rec;
 52          exit when l_cursor%notfound;
 53 
 54          dbms_output.put_line( l_rec.ename || ',' ||
 55                                l_rec.hiredate || ',' ||
 56                                l_rec.sal );
 57      end loop;
 58 
 59      close l_cursor;
 60  end;
 61  /

Procedure created.


 and that is it.  I now have a routine that will open 1 of 8 possible
different cursors.  Here is a small test run just to see how it works

ops$tkyte@ORA8I.WORLD>
ops$tkyte@ORA8I.WORLD> exec my_procedure
select * from emp where 1 = 1
SMITH,17-dec-1980 00:00:00,800
ALLEN,20-feb-1981 00:00:00,1600
WARD,22-feb-1981 00:00:00,1250
JONES,02-apr-1981 00:00:00,2975
MARTIN,28-sep-1981 00:00:00,1250
BLAKE,01-may-1981 00:00:00,2850
CLARK,09-jun-1981 00:00:00,2450
SCOTT,09-dec-1982 00:00:00,3000
KING,17-nov-1981 00:00:00,5000
TURNER,08-sep-1981 00:00:00,1500
ADAMS,12-jan-1983 00:00:00,1100
JAMES,03-dec-1981 00:00:00,950
FORD,03-dec-1981 00:00:00,3000
MILLER,23-jan-1982 00:00:00,1300
KING,,5

PL/SQL procedure successfully completed.

ops$tkyte@ORA8I.WORLD> exec my_procedure( p_ename => 'a' )
select * from emp where 1 = 1  and ename like
sys_context( 'MY_CTX', 'ENAME' )
ALLEN,20-feb-1981 00:00:00,1600
WARD,22-feb-1981 00:00:00,1250
MARTIN,28-sep-1981 00:00:00,1250
BLAKE,01-may-1981 00:00:00,2850
CLARK,09-jun-1981 00:00:00,2450
ADAMS,12-jan-1983 00:00:00,1100
JAMES,03-dec-1981 00:00:00,950

PL/SQL procedure successfully completed.

ops$tkyte@ORA8I.WORLD> exec my_procedure( p_sal => 1000 )
select * from emp where 1 = 1  and sal >
to_number(
sys_context( 'MY_CTX',
'SAL' )
)
ALLEN,20-feb-1981 00:00:00,1600
WARD,22-feb-1981 00:00:00,1250
JONES,02-apr-1981 00:00:00,2975
MARTIN,28-sep-1981 00:00:00,1250
BLAKE,01-may-1981 00:00:00,2850
CLARK,09-jun-1981 00:00:00,2450
SCOTT,09-dec-1982 00:00:00,3000
KING,17-nov-1981 00:00:00,5000
TURNER,08-sep-1981 00:00:00,1500
ADAMS,12-jan-1983 00:00:00,1100
FORD,03-dec-1981 00:00:00,3000
MILLER,23-jan-1982 00:00:00,1300

PL/SQL procedure successfully completed.

ops$tkyte@ORA8I.WORLD> exec my_procedure( p_hiredate => add_months(sysdate,-240)
)
select * from emp where 1 = 1  and hiredate >
to_date(
sys_context( 'MY_CTX',
'HIREDATE' ),
'yyyymmddhh24miss')
ALLEN,20-feb-1981 00:00:00,1600
WARD,22-feb-1981 00:00:00,1250
JONES,02-apr-1981 00:00:00,2975
MARTIN,28-sep-1981 00:00:00,1250
BLAKE,01-may-1981 00:00:00,2850
CLARK,09-jun-1981 00:00:00,2450
SCOTT,09-dec-1982 00:00:00,3000
KING,17-nov-1981 00:00:00,5000
TURNER,08-sep-1981 00:00:00,1500
ADAMS,12-jan-1983 00:00:00,1100
JAMES,03-dec-1981 00:00:00,950
FORD,03-dec-1981 00:00:00,3000
MILLER,23-jan-1982 00:00:00,1300

PL/SQL procedure successfully completed.

ops$tkyte@ORA8I.WORLD> exec my_procedure( p_ename => 'a', p_sal => 2000 )
select * from emp where 1 = 1  and ename like
sys_context( 'MY_CTX', 'ENAME' )  and sal >
to_number(
sys_context( 'MY_CTX',
'SAL' )
)
BLAKE,01-may-1981 00:00:00,2850
CLARK,09-jun-1981 00:00:00,2450

PL/SQL procedure successfully completed.

ops$tkyte@ORA8I.WORLD>