stored procedure

[nhlanhla]

Can someone tell me, what is a stored procedure?

[chandu031]

Hi,

A stored procedure is an SQL procedure which is basically used to do some back end operations on the database, or when the features provided by conventional ETL tools aren't enough for the kind of operations you want to do. Like , suppose you want a generic application which can generate codes based on some metadata , then the way to do it would be to use Dyanmic SQL in stored procedure. There are many such instances where a stored procedure can be used . For more info refer this link:

http://databases.about.com/od/specificproducts/g/storedprocedure.htm

[nhlanhla]

Thank you!

[debasisdas]

PROCEDURE:-IT IS A COMPILED BLOCK OF CODE WHICH IS STORED AS AN OBJECT WITHIN THE DATABASE. IT MAY OR MAY NOT RETURN ANY VALUE OR MIGHT RETURN MORE THAN ONE VALUE.

like normal SQL it is not recompiled at run time,so increases performance.

basic syntax

CREATE [OR REPLACE] PROCEDURE PROCEDURENAME([PARAMETER PARAMETER MODE DATATYPE [,PARAMETER...,....]])
{IS/AS}
[LOCAL DECLARATION];
BEGIN
EXECUTABLE STATMENT;
[EXCEPTION
EXCEPTION HANDLER]
END [PROCEDURENAME];
 

hope it helps u out