1 Replies - 4770 Views - Last Post: 18 January 2012 - 11:01 AM

#1 xympa  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 48
  • Joined: 07-February 10

Doing a query based on user input (procedure/function)

Posted 18 January 2012 - 09:45 AM

I know only the basics os SQL but I've recently found myself in need of perfirming a query on oracle 11g based on input from the user, I've never used function or procedures before, and I have a few doubts, should i use a function or a procedure? I have some code I have put together but it doesn't seem to work...

create or replace procedure "NUMEROELEMENTOSSEXO"
(bairro IN VARCHAR2)
is
begin
SELECT
    COUNT(*) AS "NUMERO",
    DESC_PARANTESCO
FROM
    (
        SELECT
            a.desc_parantesco,
            i.contrib
        FROM
            ren.agreg a
        INNER JOIN
            ren.inquil i
        ON
            a.num_inq = i.num_inq
        AND a.cod_b = i.num_bairro
        AND a.contrib = i.contrib
        WHERE
            i.estado != 'D'
        AND a.cod_B = bairro
        UNION ALL
        SELECT
            'CONJUGE' AS desc_parantesco,
            i.contrib
        FROM
            ren.inquil i
        INNER JOIN
            sigmagest12.contrib c
        ON
            c.numero = i.contrib
        WHERE
            i.estado != 'D'
        AND i.cob_b = bairro )
GROUP BY
    DESC_PARANTESCO;
end;​


This doesn't work because it has no INTO, but i don't where to put the data returned from the query, i just want to display the result...

Thanks in advance.

This post has been edited by xympa: 18 January 2012 - 09:46 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Doing a query based on user input (procedure/function)

#2 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5758
  • View blog
  • Posts: 12,573
  • Joined: 16-October 07

Re: Doing a query based on user input (procedure/function)

Posted 18 January 2012 - 11:01 AM

You want a REF CURSOR. They're awful, and messy, and what oracle gives you. A more manageable option is to use temp tables and load results into them.

However, for your example, I'd probably just make a view:
create or replace procedure VW_NUMEROELEMENTOSSEXO
as
SELECT bairro, DESC_PARANTESCO, COUNT(*) AS NUMERO
FROM (
SELECT a.cod_B as bairro, a.desc_parantesco
	FROM ren.agreg a
		INNER JOIN ren.inquil i
			ON a.num_inq = i.num_inq
				AND a.cod_b = i.num_bairro
				AND a.contrib = i.contrib
				AND i.estado != 'D'
UNION ALL
SELECT i.cob_b as bairro, 'CONJUGE' AS desc_parantesco
	FROM ren.inquil i
		INNER JOIN sigmagest12.contrib c
			ON c.numero = i.contrib
	WHERE i.estado != 'D'
)
GROUP BY bairro, DESC_PARANTESCO;
/

select DESC_PARANTESCO, NUMERO from VW_NUMEROELEMENTOSSEXO where bairro=???



Was This Post Helpful? 0
  • +
  • -

Page 1 of 1