procedure in sql

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

1SQL Stored Procedure

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;