| Stored procedures are groupings of related SQL statementscommonly referred to as functions and subprograms that allow ease and flexibility for a programmer. This ease and flexibility are derived from the fact that a stored procedure is often easier to execute than a number of individual SQL statements. Stored procedures can be nested within other stored procedures. That is, a stored procedure can call another stored procedure, which can call another stored procedure, and so on. Stored procedures allow for procedural programming. The basic SQL DDL, DML, and DQL statements (CREATE TABLE, INSERT, UPDATE, SELECT, and so on) allow you the opportunity to tell the database what needs to be done, but not how to do it. By coding stored procedures, you tell the database engine how to go about processing the data.  | A stored procedure is a group of one or more SQL statements or functions that are stored in the database, compiled, and are ready to be executed by a database user . A stored function is the same as a stored procedure, but a function is used to return a value. |  | Stored procedures, functions, and triggers are not support by MySQL. Like views, code cannot currently be stored in the database. |
Functions are called by procedures. When a function is called by a procedure, parameters can be passed into a function like a procedure, a value is computed, and then the value is passed back to the calling procedure for further processing. When a stored procedure is created, the various subprograms and functions (that use SQL) that compose the stored procedure are actually stored in the database. These stored procedures are pre-parsed, and are immediately ready to execute when invoked by the user. The Microsoft SQL Server syntax for creating a stored procedure is as follows :  CREATE PROCEDURE PROCEDURE_NAME [ [(] @ PARAMETER_NAME DATATYPE [(LENGTH) (PRECISION] [, SCALE ]) [ = DEFAULT ][ OUTPUT ]] [, @ PARAMETER_NAME DATATYPE [(LENGTH) (PRECISION [, SCALE ]) [ = DEFAULT ][ OUTPUT ]] [)]] [ WITH RECOMPILE ] AS SQL_STATEMENTS The syntax for Oracle is as follows: CREATE [ OR REPLACE ] PROCEDURE PROCEDURE_NAME [ (ARGUMENT [{IN OUT IN OUT} ] TYPE, ARGUMENT [{IN OUT IN OUT} ] TYPE ) ] {IS AS} PROCEDURE_BODY An example of a very simple stored procedure is as follows:   CREATE PROCEDURE NEW_PRODUCT (PROD_ID IN VARCHAR2, PROD_DESC IN VARCHAR2, COST IN NUMBER) AS BEGIN INSERT INTO PRODUCTS_TBL VALUES (PROD_ID, PROD_DESC, COST); COMMIT; END;  Procedure created. This procedure is used to insert new rows into the PRODUCTS_TBL table. The syntax for executing a stored procedure in Microsoft SQL Server is as follows: EXECUTE [ @RETURN_STATUS = ] PROCEDURE_NAME [[@ PARAMETER_NAME = ] VALUE [@PARAMETER_NAME = ] @VARIABLE [ OUTPUT ]] [WITH RECOMPLIE] The syntax for Oracle is as follows: EXECUTE [ @RETURN STATUS =] PROCEDURE NAME [[ @ PARAMETER NAME = ] VALUE [ @ PARAMETER NAME = ] @VARIABLE [ OUTPUT ]]] [ WITH RECOMPLIE ] Now execute the procedure you have created: EXECUTE NEW_PRODUCT ('9999','INDIAN CORN',1.99); PL/SQL procedure successfully completed. | | You may find that there are distinct differences between the allowed syntax used to code procedures in different implementations of SQL. The basic SQL commands should be the same, but the programming constructs ( variables , conditional statements, cursors , loops ) may vary drastically among implementations. |
Advantages of Stored Procedures and Functions Stored procedures provide several distinct advantages over individual SQL statements executed in the database. Some of these advantages include the following: -
The statements are already stored in the database. -
The statements are already parsed and in an executable format. -
Stored procedures support modular programming. -
Stored procedures can call other procedures and functions. -
Stored procedures can be called by other types of programs. -
Overall response time is typically better with stored procedures. -
Overall ease of use.  | 
