passing a parameter to a select

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Tue, 09 Oct 2007 11:43 pm

I have a very extensive select have to change the mês to execute him. I would like to put a variable to be subtituida at the time of enacting.
Something month = 4/* April */select * from table where extract (month from date) = & month if it was always the current month I just put the extract on current date and ready, but I have to bring data from months different from already Thank the help and sorry if I posted in the wrong place since I signed up today and doesn't know well the forum.
henrique290
Location: Porto Alegre - RS

Poston Wed, 10 Oct 2007 7:46 am

Brother, there are two ways to resolve this situation: the first, would be to create a CURSOR which receives parameters:
Code: Select all

-- Cursor com Parâmetro ------------------------------------------------------

DECLARE
 
  CURSOR cur_empregados (p_empno NUMBER)
    IS
      SELECT *
        FROM emp
       WHERE empno = p_empno;
 

  v_resultado cur_empregados%ROWTYPE;

BEGIN
 
  OPEN cur_empregados(7499);
  FETCH cur_empregados INTO v_resultado;
  CLOSE cur_empregados;
 
  dbms_output.put_line('Empregado: ' || v_resultado.ename);
 
END;   

the second, would be with dynamic CURSOR.

About dynamic cursor, browse here on the Forum this string \"dynamic cursor\" or \"dbms_sql\" that will find interesting topics.
Trevisolli
Location: Araraquara - SP

Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Poston Wed, 10 Oct 2007 10:08 am

And what is the problem with your initial example?
I'm in SQL * Plus, I created a script called data.sql thus:
Code: Select all
select dtexportacao
from planopagamento
where extract (month from dtexportacao)=&mês
/
Now, in SQL * Plus, I run it:
Code: Select all
SQL> @data
Input truncated to 1 characters
Enter value for mês: 10
old   3: where extract (month from dtexportacao)=&mês
new   3: where extract (month from dtexportacao)=10

DTEXPORTACA
-----------
01-OCT-2007
09-OCT-2007
03-OCT-2005
01-OCT-2007
02-OCT-2007
19-OCT-2005
09-OCT-2007
01-OCT-2007
09-OCT-2007
04-OCT-2007
09-OCT-2007
01-OCT-2007

12 rows selected.

SQL> @data
Input truncated to 1 characters
Enter value for mês: 5
old   3: where extract (month from dtexportacao)=&mês
new   3: where extract (month from dtexportacao)=5

DTEXPORTACA
-----------
21-MAY-2007
17-MAY-2005
17-MAY-2005
17-MAY-2005
09-MAY-2007
09-MAY-2007

6 rows selected.

SQL>
as you can see, it only shows the month informed! You can't miss:-D
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered


  • See also
    Replies
    Views
    Last Post


    Return to SQL

    Who is online

    Users browsing this forum: No registered users and 2 guests