[MySQL learning articles] — Stored procedure

Article directory

[MySQL learning articles] – stored procedures

What is a stored procedure?

A stored procedure is a collection of operations of a group of [SQL statements] , which is equivalent to encapsulating a group of SQL statements into a “function”, and the result can be obtained by calling it directly without knowing the table structure.

For example: giving employees a raise

#Raise salary for employees: (salary amount of salary increase, employee number id) 
UPDATE emp SET salary=salary+ 500  WHERE  id = 3 ;

Stored procedure syntax

#Stored procedure syntax: create procedure stored procedure name (parameter name 1 parameter type 1, parameter name 2...)

DELIMITER // #Delimiter, indicating a whole, to the next // end 
CREATE  PROCEDURE stored procedure name ( IN input parameter name parameter type, OUT output parameter name parameter type)
 BEGIN
    code block;
END//
DELIMITER;

#Delete the stored procedure (judging whether the stored procedure exists before deleting): 
DROP  PROCEDURE [ IF  EXISTS ] Name of the stored procedure;

#Call stored procedure: call stored procedure name (argument);

Get a raise

DELIMITER //
CREATE PROCEDURE addSalary(money FLOAT,idd BIGINT)
BEGIN
    UPDATE emp SET salary=salary+money WHERE id=idd;
END//
DELIMITER;
#call call 
addSalary ( 500 , 3 );

Stored procedure with return value

#getSum implements the summation function out indicates the return value
DELIMITER //
CREATE PROCEDURE getSum(IN i FLOAT,IN j FLOAT,OUT result FLOAT)
BEGIN
    SET result=i+j;
END//
DELIMITER ;

Stored procedure with if…else if…

DELIMITER //
CREATE PROCEDURE buy(money FLOAT)
BEGIN

    IF money>500 THEN
        SELECT 'buy a Porsche'  as  'what to buy' ;
     ELSEIF money> 300 THEN
        SELECT 'Buy BMW' ;
     ELSEIF money> 10 THEN
        SELECT 'Buy Alto' ;
     ELSE SELECT 'Mobike' ;
    END IF;

END//
DELIMITER;

Select branch case

DELIMITER //
CREATE PROCEDURE pro_case(i INT)
BEGIN

    CASE i 
         WHEN  1  THEN 
            SELECT  'Monday' ;
        WHEN 2 THEN
            SELECT  'Tuesday' ;
        ELSE 
            SELECT  'Guess what day of the week' ;
     END  CASE ;

END//
DELIMITER;

while loop

#Add data to the table loop 
DELIMITER //
CREATE PROCEDURE proSum(i INT)
BEGIN

    DECLARE a INT DEFAULT  1 ; #declare a variable 
    SET a= 2 ;
     WHILE a<=i DO  
        INSERT INTO {{EJS0}} SET {{EJS1}}= 'test' ,weight= 10 ;
         SET a = a+ 1 ; #Add 1 to achieve 
    END WHILE ;

END//
DELIMITER;

loop loop

CREATE  PROCEDURE stored procedure name ()
 BEGIN 
    loop loop alias: LOOP
        loop body

        LEAVE loop loop alias
     END  LOOP ;
 END ;

#Add data to the table loop 
DELIMITER  // 
CREATE  PROCEDURE testLoop(i INT) 
BEGIN

    DECLARE a INT DEFAULT 1;
    testLoop1:LOOP
        INSERT INTO person SET {{EJS2}} ='admin',weight =200;
        SET a=a+1;
        IF a>i  THEN 
            LEAVE testloop1;
        END IF;
    END LOOP;

END//
DELIMITER;

Leave a Comment

Your email address will not be published. Required fields are marked *