A procedure allows you to group the SQL statement into a single block. Stored Procedures are used to achieve certain result across applications. The set of SQL statements and the logic that is used to perform some specific task are stored in SQL Stored Procedures. These stored procedures are executed by applications to perform that task.
Stored Procedures can return data in the form of output parameters (integer or character) or a cursor variable. It can also result in set of Select statements, which are used by other Stored Procedures.
Stored Procedures are also used for performance optimization as it contains series of SQL statements and results from one set of statement determines next set of statements to be executed. Stored procedures prevent users to see the complexity and details of tables in a database. As Stored procedures contain certain business logic, so users need to execute or call the procedure name.
No need to keep reissuing the individual statements but can refer to the database procedure.
Sample Statement to Create Procedures
Create procedure prc_name (in inp integer, out opt "EFASION"."ARTICLE_LOOKUP") as begin opt = select * from "EFASION"."ARTICLE_LOOKUP" where article_id = :inp ; end;
