SQL Base Notebook 2 View Stored Procedure

Keywords: MySQL Stored Procedure Database SQL

 

View

  • CREATE/ALTER/DROP VIEW ViewName as SELECT(...)
    
  • You can continue to create a view based on it, that is, you can place the previously created view as a table name in the select statement of the new view

  • Format data using views (that is, reassemble and stitch fields)

    CREATE VIEW View_player_team AS 
    SELECT CONCAT(player_name, '(' , team.team_name , ')') AS player_team
    FROM player JOIN team WHERE player.team_id = team.team_id
    
  • Views are secure because they are virtual tables and do not affect the underlying data even if the view is modified.

 

PROCEDURE

  • A stored procedure is like a "custom function". It is a collection of SQL and flow control statements that can accept or return parameters.

  • Stored procedures can (not necessarily) operate directly on underlying data tables

  • CREATE PROCEDURE name (parameter)
    BEGIN
    	Processes to be executed
    END
    

    Example 1,

    DELIMITER //
    
    CREATE PROCEDURE `Stored procedure name'(parameter)
    BEGIN
    	Circulatory body
    END //
    
    DELIMITER;
    

    Examples of calling stored procedures

    CALL `Stored procedure name'(argument);
    select @(out parameter)
    
    • DELIMITER changes the delimiter to // (or something else) because the statement in the flow control statement requires it; split, which avoids confusion

    • DELIMITER starts and ends with a new line of mysql shell

    • Stored procedure names are not single quotes, they are English "pauses"

    • Flow Control Statement

      DECLIARE --- Declare Variables
      SET --- Initialization, assignment
      IF...ELSE...
      
      REPEAT...UNTIL...END REPEAT --- satisfy UNTIL The following expression exits the loop
      WHILE...DO...END WHILE --- Judges the condition first, satisfies then loops
      CONTINUT;
      BREAK;
      RETURN;
      GOTO (label)
      WAITFOR TIME 'time' --- Execute at a time
      WAITFOR DELAY 'time' --- Delay execution for a time
      SELECT...INTO... --- Put results from query tables into variables
      
  • Parameter type

    • IN - Incoming parameter, cannot be returned, no write parameter type defaults to IN
    • OUT -- Puts the result of a stored procedure's calculation into the OUT parameter and returns it
    • INOUT -- Used for both passing in a parameter and storing the result in it for return
  • Advantages of PROCEDURE

    • Strong security, you can set permissions for users when setting stored procedures
    • Fast execution, executed on the MySQL database server side
    • Reduce network traffic through encapsulation
  • Disadvantages of PROCEDURE - Reasons why companies such as Ali do not recommend it

    • Debugging is difficult and only a few DBMS s support debugging
    • Poor portability
    • Version management difficulties
    • Not suitable for high concurrency scenarios - high concurrency requires less database pressure, may be in a database-based and tabular manner, and requires high scalability.In this case, stored procedures become difficult to maintain and can increase the pressure on the database

 

procedure example 1 Query a table by a field

  • View existing test tables

    mysql> SELECT * FROM t; 
    +----+------+
    | Id | nums |
    +----+------+
    |  1 |    2 |
    +----+------+
    1 row in set (0.00 sec)
    

    Create Stored Procedures

    mysql> DELIMITER //
    mysql> CREATE PROCEDURE `findByNums`(IN n int)
        -> BEGIN
        -> SELECT * FROM t WHERE nums = n;
        -> END //
    Query OK, 0 rows affected (0.03 sec)
    

    test

    mysql> DELIMITER ;
    mysql> CALL findByNums(2);
    +----+------+
    | Id | nums |
    +----+------+
    |  1 |    2 |
    +----+------+
    1 row in set (0.03 sec)
    
    Query OK, 0 rows affected (0.03 sec)
    

    (different parameters passed in)

    mysql> CALL findByNums(1);
    Empty set (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    

     

  • Query Stored Procedures

    mysql> SELECT * FROM mysql.proc WHERE db = 'test' AND type = 'procedure';
    

 

procedure example 2 calculates the summation result

  • Query output using stored procedure incoming/outgoing parameters

    mysql> delimiter //
    mysql> create procedure `add_sum`(in n int, out result int)
        -> begin
        -> declare i int;
        -> declare sum int;
        -> set i = 1;
        -> set sum = 0;
        -> while i < n do
        -> set sum = sum + i;
        -> set i = i + 1;
        -> end while;
        -> end //
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> delimiter ;
    mysql> call add_sum(50,@result);
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select @result;
    +---------+
    | @result |
    +---------+
    |    1225 |
    +---------+
    1 row in set (0.00 sec)
    
    
  • Variables in stored procedures cannot be directly @

    mysql> create procedure `add_sum_procedure`(in n int)
        -> begin
        -> declare i int;
        -> declare sum int;
        -> set i = 1;
        -> set sum = 0;
        -> while i < n do
        -> set sum = sum + i;
        -> set i = i + 1;
        -> end while;
        -> end //
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> delimiter ;
    mysql> call add_sum_procedure(10);
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> select @sum;
    +------+
    | @sum |
    +------+
    | NULL |
    +------+
    1 row in set (0.01 sec)
    

Query all stored procedures/views, etc.

Posted by Izzy1979 on Fri, 26 Jun 2020 09:58:46 -0700