Introduction to Database Systems - Essay Example

Insert SQL Stored Procedure and Functions Stored procedures in SQL is a collection of controls, condition handling and SQL statements that provide an interface to the database. Stored procedure is an SQL code or object that is executed like a query several times in order to achieve different functions or purposes. Stored procedures are usually precompiled and can be simple selection query or complex ones to do several tasks like selection, joins, insertion or deletion simultaneously. Stored procedures contain a procedure name, input/output parameters, and a procedure body.

User defined functions are routines that encapsulate SQ logic inside them. User defined functions accept input and are compiled and executed at runtime. Execution at runtime makes them slower that stored procedures. Functions are divided into scalar function (return a single value), in-line functions ( return a table) and table value function for complex logic and multiple operations.AdvantagesStored procedures offer a great maintainability because scripts are found in one location. Therefore, tracking dependencies based on schema changes and update becomes easier.

Stored procedures increase security features of the database. They limit direct access to tables through use of defined roles in the database. Stored procedures provide a shield to underlying data structure through creation of an interface so that data and executions are shielded. Clients who access stored procedures and functions can be controlled by the database administrators. Securing access code and data of a database is easier than applying security features inside the application code itself.

Increases speed and database optimization. Stored procedures implement this since they are cached on the server. Execution for the processes is re-viewable easily without running the application.Works CitedHambrick PJ. Advantages and Drawbacks of Using Stored Procedures for Processing Data. 17 NOVEMBER 2014.