Tutorial Addendum on SQL - MySQL - Stored Procedures

This affiliate describes:

    

    
• What is a stored procedure?
  
      



    
• How to make a stored procedure.
  
      



    
• How to canyon data through parameters.
  
      



    

All addendum in affiliate are based MySQL 5.0 server.

    

What Is A Stored Procedures?

    

A stored action is an executable program that is stored in the database server

    

waiting to be alleged for execution. A stored action should be accounting in a programming

    

language that is accurate by the database server. Some database servers action appropriate

    

languages that are continued from the accepted SQL. For example:

    

    
• PL/SQL - Stored action accent for Answer database server.
  
      



    
• Transact SQL - Stored action accent for MS SQL server.
  
      



    

Store action languages usually action you statements to:

    

    
• Receive and acknowledgment data from the calling environment.
  
      



    
• Declare and use bounded variables.
  
      



    
• Execute approved SQL statements on the server.
  
      



    
• Loop through a set of statements assorted times.
  
      



    
• Conditionally ascendancy the beheading flow.
  
      



    

Creating Stored Procedures in MySQL

    

MySQL aswell offers an extention of SQL to abutment stored procedures. The first account for

    

stored action is the Make Action statement.

    

To make a stored action of a individual statement, you should use the

    

CREATE Action account with the action physique account amid at the end:

    

    
CREATE Action proc_name() body_statement;

    

To make a stored action of a accumulation of statements, you should use the

    

CREATE Action account with the action physique statements amid

    

in the "BEGIN ... END" block statement. In adjustment to assure the account delimites

    

in the "BEGIN ... END", DELIMITER statements can be acclimated to briefly change

    

the delimiter character:

    

    
DELIMITER  / ;

    
CREATE Action proc_name() 

    
BEGIN

    
   body_statement;

    
   body_statement;

    
   ...

    
END/

    
DELIMITER  ; /

    

To ascertain ambit for the procedure, just account them in the parentheses next

    

to the action name:

    

    
CREATE Action proc_name(parameter, parameter, ...) body_statement;

    

To assassinate a stored procedure, you should use the Alarm statement:

    

    
CALL proc_name(expression, expression, ...);

    

To abolish a stored action from the server, you should the Bead statement:

    

    
DROP Action proc_name;