1-Stored procedure is a group of SQL
2statements that has been created
3and stored in the database.
4-A stored procedure will accept input
5parameters so that a single procedure
6can be used over the network by several
7clients using different input data.
8-A stored procedures will reduce
9network traffic and increase the performance.
10If we modify a stored procedure all the
11clients will get the updated stored procedure.
12
13Sample of creating a stored procedure
14CREATE PROCEDURE test_display AS
15SELECT FirstName, LastName FROM tb_test
16EXEC test_display
1IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL
2 DROP PROCEDURE Production.uspGetList;
3GO
4CREATE PROCEDURE Production.uspGetList @Product varchar(40)
5 , @MaxPrice money
6 , @ComparePrice money OUTPUT
7 , @ListPrice money OUT
8AS
9 SET NOCOUNT ON;
10 SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'
11 FROM Production.Product AS p
12 JOIN Production.ProductSubcategory AS s
13 ON p.ProductSubcategoryID = s.ProductSubcategoryID
14 WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;
15-- Populate the output variable @ListPprice.
16SET @ListPrice = (SELECT MAX(p.ListPrice)
17 FROM Production.Product AS p
18 JOIN Production.ProductSubcategory AS s
19 ON p.ProductSubcategoryID = s.ProductSubcategoryID
20 WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);
21-- Populate the output variable @compareprice.
22SET @ComparePrice = @MaxPrice;
23GO
24
1CREATE OR REPLACE PROCEDURE my_scheme.my_procedure(param1 IN VARCHAR2) IS
2 cnumber NUMBER;
3BEGIN
4 cnumber := 10;
5 INSERT INTO my_table (num_field) VALUES (param1 + cnumber);
6 COMMIT;
7EXCEPTION
8 WHEN OTHERS THEN
9 raise_application_error(-20001, 'An error was encountered - '
10 || sqlcode || ' -ERROR- ' || sqlerrm);
11END;
1CREATE PROCEDURE nome_procedura [(parametri)] IS
2 Definizioni;
3BEGIN
4 Corpo procedura;
5END;