MySQL Database Stored Procedure

Introduction

  • A stored procedure is a procedure that is used to store data into database and retrieve data from multiple tables.
  • A stored procedure has a name, a parameter list, and an SQL statement, which can contain many more SQL statements.
  • A stored procedure can take one or more argument that can be IN and OUT and returns a result set. By default store procedure parameters are IN.
  • Procedures and functions are stored in a system table: mysql.proc
  • Now we need a delimiter. The statement we'll use is DELIMITER // . The delimiter is the character or string of characters that you'll use to tell the mysql client that you've finished typing in an SQL statement
  • There are various queries of MySQL Procedure
    • Create Procedure
    Call Procedure
    Drop Procedure

Create Procedure

  • You should need database table to store data, So you can create table in selected database by using this query-

  • There are various queries on funtion:
  • Create PROCEDURE
    • Syntax-
    mysql>DELEMITER //
    ->CREATE PROCEDUREprocedure name(parameter1,parameter2,...parameterN)
    ->BEGIN
    -> < Database query to execute >
    ->END
    ->//

  • Call PROCEDURE
    • Syntax-
    mysql> CALL < procedure name >
    -> //

  • Drop PROCEDURE
    • Syntax-
    mysql> DROP PROCEDURE < procedure name >
    -> //

    <
    Example-
mysql procedure