CODEWITHSUNDEEP
plsqlparametersoracle11g
UcanDoIt
UcanDoIt

Reputation: 1845

Using parameters in procedures

I need to create a procedure that receives as a parameter a month and a year.

Inside the procedure I need to have a query to retrieve some values, that will have to take in account the parameters received.

create or replace procedure GET_REVS(MONTH in VARCHAR2,YEAR in varchar2) is
SELECT
  *                 
FROM
  REVENUS_TABLE
  WHERE
      Y_CODE IN ('YEAR')  
  AND M_CODE  IN ()

Now, M_CODE should have the values since the start of the year until the month received by parameter.

Example if i receive in as parameter for the month a 4 i want my select to like this AND M_CODE IN ('1','2','3','4')

But if i receive MONTH = 3.. i need the select to have AND M_CODE IN ('1','2','3')

So what is the best way to do the procedure in order to be able to do that?

Thanks a lot

Upvotes: 0

Views: 47

Answers (3)

Avrajit Roy
Avrajit Roy

Reputation: 3303

Just an alternative to think about.

CREATE OR REPLACE PROCEDURE get_revs(month IN VARCHAR2, year IN VARCHAR2) IS
lv_in_clause VARCHAR2(100 CHAR);
p_ref sys_refcursor;
BEGIN
  SELECT 'IN ('
    ||WMSYS.WM_CONCAT(A.NUM)
    ||')'
  INTO lv_in_clause
  FROM
    (SELECT ''''
      ||LEVEL
      ||'''' NUM,
      1 ID1
    FROM DUAL
      CONNECT BY LEVEL < to_number(MONTH)
    )A
  GROUP BY a.ID1;
  OPEN p_ref FOR 'SELECT *  
FROM revenus_table  
WHERE y_code = '|| year  
||' AND m_code '||lv_in_clause; 

END;

Upvotes: 0

Indiecoder
Indiecoder

Reputation: 186

You can do some thing like below example to make your procedure more dynamic

CREATE OR REPLACE PROCEDURE get_revs (
   MONTH   IN   VARCHAR2,
   YEAR    IN   VARCHAR2
)
IS
   variable_name   table_name%ROWTYPE;
   v_sql           VARCHAR2 (1000)
      :=    'SELECT  * FROM   REVENUS_TABLE   WHERE      Y_CODE IN ('
     || MONTH
     || ')';
BEGIN
   IF MONTH = 4 THEN
      v_sql := v_sql || ' and M_CODE  IN (''1'',''2'',''3'',''4'')';
   END IF;

   IF MONTH = 3 THEN
      v_sql := v_sql || 'and M_CODE  IN (''1'',''2'',''3'')';
   END IF;

   EXECUTE IMMEDIATE v_sql
   INTO              variable_name;
END;

Upvotes: 1

pablomatico
pablomatico

Reputation: 2242

You could cast both M_CODE and MONTH as numbers and use BETWEEN operator or just <=:

CREATE OR REPLACE PROCEDURE get_revs(month IN VARCHAR2, year IN VARCHAR2) IS
BEGIN
  ....
  SELECT *
  FROM revenus_table
  WHERE y_code = year
  AND TO_NUMBER(m_code) BETWEEN 1 AND TO_NUMBER(month);
  ...
END;

Upvotes: 2

Related Questions