What is Stored Procedure in MySQL?

Thursday 5 January 2017

What is Stored Procedure in MySQL?

Question: What is Stored Procedure?
A Stored procedure is program in a regular computing language, stored in database. It have has a name, a parameter list, and SQL statement(s).

Question: What is the purpose of stored procedure?
A stored procedure is used to retrieve data, modify data, and delete data in database table.

Question: What is Syntax for creating stored procedure?

DELIMITER $$
CREATE    
    PROCEDURE `dbName`.`storedProcedureName`()
    BEGIN

    END$$

Question: Create simple stored procedure?

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `dbname`.`helloArun`() 
    BEGIN
SELECT id,user_nickname,user_email FROM `cmf_users` WHERE user_email="hello@no-spam.ws";   
    END

Question: How to call stored procedure?

call helloArun();

Question: How to list stored procedure?

SHOW PROCEDURE STATUS;

Question: How to delete stored procedure?

DROP PROCEDURE storedProcedureName

Question: How to delete stored procedure If Exist?

DROP PROCEDURE IF EXISTS storedProcedureName

Question: Create simple stored procedure with argument?

CREATE    
    PROCEDURE `dbname`.`helloArun2`(
  IN email VARCHAR(255)    
    )
    BEGIN
 SELECT id,user_nickname,user_email FROM `cmf_users` WHERE user_email=email;      

    END

Question: How to call stored procedure with parameter?

call helloArun2('arunk@no-spam.ws');

Question: How to list of stored PROCEDURE?

SHOW PROCEDURE STATUS

Question: How to remove stored PROCEDURE?

DROP PROCEDURE storedProcedureName

Question: How to remove stored PROCEDURE if exist?

DROP PROCEDURE IF EXISTS storedProcedureName

Thursday 5 January 2017

What is Stored Procedure in MySQL?

Best Related Posts are Following: