Home » SQL & PL/SQL » SQL & PL/SQL » how do i write a procedure (11g enterprise version)
how do i write a procedure [message #412770] Sat, 11 July 2009 10:51 Go to next message
cmrhema
Messages: 1
Registered: July 2009
Junior Member
Hi,

Can anyone please help me in the below scenario

I want to pass some three parameters to a stored procedure
eg say I want to pass the values A, B and C
inside the stored procedure I want to find whether the title(ook title) belongs to A.

if (booktitle =='A')
then select * from author where booktitle like 'A'
else if (booktitle=='A' and booktitle=='B')
then select * from author where booktile like 'A'+'B'
and so on

Now I want the values 'A' and 'B' should be passed as parameters
and also the select statement should be stored in a string and then executed.

Kindly excuse my ignorance in pl/sql but i want something like sp_executesql(the one we find in SQL SERVER)

Regards
cmrhema
Re: how do i write a procedure [message #412771 is a reply to message #412770] Sat, 11 July 2009 10:57 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b31695/toc.htm

many fine coding examples can be found at http://asktom.oracle.com
Re: how do i write a procedure [message #412772 is a reply to message #412770] Sat, 11 July 2009 11:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
if (booktitle =='A')
then select * from author where booktitle like 'A'
else if (booktitle=='A' and booktitle=='B')
then select * from author where booktile like 'A'+'B'
and so on

This pseudo-code is meaningless.
Try to explain it with words.

And don't forget to post a working Test case: create table and insert statements along with the result you want with these data.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: how do i write a procedure [message #412896 is a reply to message #412770] Mon, 13 July 2009 05:26 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
CREATE OR REPLACE PROCEDURE proc (p_1  in varchar2
                                       ,p_2  in varchar2
                                       ,p_3  in varchar2) is
  r_row     author%rowtype;
BEGIN
  if booktitle = p_1 then 
    select * 
    into   r_row
    from author 
     where booktitle like p_1
  elsif booktitle = p_1 and booktitle = p_2 then s
    select *
    into   r_row
    from author 
    where booktile like p_1||p_2;
  end if;
END;

Your logic has a few problems - the elsif clause you specified is only going to work if p_1 = p_2 (or 'A' = 'B' in your original)
Previous Topic: When should I use correlated subqueries? (merged)
Next Topic: sql query tunning
Goto Forum:
  


Current Time: Tue Dec 06 14:09:33 CST 2016

Total time taken to generate the page: 0.10451 seconds