Stored Procedure in Oracle

Posted Date: 04 Jul 2006	Resource Type: Articles	Category: .NET Framework
Author: Azamat	Member Level: Gold
Rating:	Points: 13

Stored Procedures (Oracle)

create or replace procedure(var in number) as //do not specify the
length in parameters

// local variables declaration
// three types of local var
// in - only accept form parent program
// out - only send to parent program
// inout - either send or accept from parent program

BEGIN

// select * wont work here
//here goes the PL/SQL block required

select ,att2,att3..
into ,var2,var3...
from y where y.aabc=1;

END

================================================================================

// to create a row type , single row retrival
---------------------------------------------
create or replace procedure(var in number) as //do not specify the
length in parameters

// var %rowtype;

BEGIN

select *
into var
from y where y.aabc=1;

END

================================================================================

// to multiple row retrival use a cursor - USING LOOP
---------------------------------------------
create or replace procedure(var in number) as //do not specify the
length in parameters

cursor item(pramItemID number) is select * from table table where
field=paramItemID; //this is the cursor declaration

// now declare a cursor type & row type variable
// varC item%type; // the name preceding the % should be the cursor
name

// varR table%rowtype;

BEGIN

open varC(parameter)
fetch varC into varR

loop

exit when varC%NOTFOUND;

update tablename
set field=varR.fieldname
where field=varR.fieldname;

fetch varC into varR;

end loop;

close item;

END

================================================================================

// to multiple row retrival use a cursor - USING FOR
---------------------------------------------
create or replace procedure(var in number) as //do not specify the
length in parameters

cursor item(pramItemID number) is select * from table table where
field=paramItemID; //this is the cursor declaration

BEGIN
for varC in ityem(var)
if varC%ISOPEN //check if the cursor has successfully or not. returns
true or false
if varC%FOUND | NOTFOUND //check if data has been returned using the
where condition or not
if varC%ROWCOUNT //the row count returned
end for
END

Hope this Helps you people. Please write back to me to give me the feedback on how was the article. I am looking forward for an Article on Oracle queries. Will write that article next in case I get good Feedback from people at dotnetspider.

Az.

Responses
Author: Ganesh 05 Jul 2006		Member Level: Bronze Points : 0
It was indeed a good article. You can improve this page by adding use cases so that it can give a clear picture of using it depending on the situations. Thank you.
Author: Ganesh 05 Jul 2006		Member Level: Bronze Points : 0
It was indeed a good article. You can improve this page by adding use cases so that it can give a clear picture of using it depending on the situations. Thank you.

Feedbacks

Popular Tags

What are tags ?

Search Tags

(No tags found.)

Post Feedback

This is a strictly moderated forum. Only approved messages will appear in the site. Please use 'Spell Check' in Google toolbar before you submit.
You must Sign In to post a response.

	Next Resource: Tracing in VB.NET
	Previous Resource: How to validate textboxes in a simple way
	Return to Discussion Resource Index
	Post New Resource
	Category: .NET Framework

Post resources and earn money!

More Resources

dotNet Slackers

About Us Contact Us Privacy Policy Terms Of Use

Stored Procedure in Oracle

Responses

Post Feedback