Procedure in SQL

Procedure in SQL and advantage and disadvantages of Procedure are discussed here i this tutorial. Questions based on procedure are general asked in GATE and UGC NET exam.

What is Procedure in SQL ?

PL/SLQ is the advancement over SQL. PL/SQL has the power of combining then procedural statements.

• Procedure is an important concepts in PL/SOL sometime procedure is also known as Stored procedure.
• A store procedure can be defined as  collection of sql statements.
• These statements can be stored in database catalogue.
• Procedure works as similar as function. Like a function a procedure  can also call or invoke to other procedure.
• Trigger can also invoke to the procedure. Procedure can also be invoked by application program like java or php.

Advantage of using the procedure

There are following advantages of using the procedure in SQL

• Overall performance of the application is improved.
• If the procedure is invoking frequently again and again then there is no need to compile the procedure again .second time the compile version can be passed.
• Procedure also helps in reducing the traffic between database and application because there is no need to pass individual statement, a block of statement is passed.
• Procedure also provide the facility of code reusability.

Disadvantages of procedure

There are some disadvantages also od using the procedure.

• Sometime stored procedure creates some memory issues.
• To solve this problem database administrator decide that how many procedure are allowed for a particular application.
• MYSQL database does not provide the facility of debugging the procedure.

Next Tutorial – View in SQL