Thursday, 5 January 2017

What is Stored Procedure in MySQL?

What is Stored Procedure in MySQL?

Question: What is Stored Procedure?
It is procedure (written in SQL) stored in a database which can be called by the database engine and connected programming languages.


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="[email protected]";   
    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([email protected]');



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


No comments :

Post a Comment