Procedures

PROCEDURE

It is logically grouped set of sql statements that perform a specific task, when they are being called upon in any situation.

• A procedure is a database object that accomplishes tasks as and when invoked by the user.
• It is evoked manually
• It only works for local module
• It’s permanently stored in db
• Its work is similar to trigger

Procedures have three parts:

1. DECLARATIVE PART - contains declaration of variable, constants, exceptions or subprograms.
2. EXECUTABLE PART – consists of statement that assigns values, control execution and manipulate data. Action is coded here.
3. OPTIONAL EXCEPTION- HANDLING PART –deals with the exceptions which may generate during executions of code in the executable part.

How to create Procedure?

CREATE PROCEDURE procedure_name (IN/ OUT/ INOUT parameter_name datatype)

BEGIN

{

        - statements- -

}

END;

TO CALL A PROCEDURE-

CALL procedure_name (@parapeter_name, @parameter_name);

 eg: delimiter/

CREATE procedure first (OUT x int)

BEGIN

SELECT COUNT(*) FROM student;

END;

/

//to call

/

CALL first (@x);

/

STORED PROCEDURE

It is a sub routine available to applications assumption the rdbms. Stored procedure is actually stored in db data dictionary. Typically, uses of stored procedure includes data validation or access control mechanism, those are similar to user defined functions.

Stored Procedure Advantages:

1. Increases performance of application: once created, it is complied and stored in the database catalog. It runs faster than un-complied sql commands which are sent from application.
2. It reduces traffic between application and db server because instead of sending multiple un-complied lengthy sql commands. Application only has to send the stored procedure name & get the data back to manipulate it further.
3. It is reusable and transparent to any application so developers don’t have to program the functions which are already supported in this.
4. It is secured. DBA can grant access right to application which wants to access stored procedures in db catalog without granting any permission on the underlying db tables.

Stored Procedure Disadvantages:

1. It makes the db highly loaded in both memory and processors.
2. It only contains declarative sql so, it is very difficult to write a procedure with complexity of business logic like other languages in application layer such as java, c# etc.
3. It can’t be debugged in almost every RDBMSs and in MySql too.
4. Writing and manipulating stored procedure usually required specialized skills set that all developers process.