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.

• https://www.cnblogs.com/G-Aurora/p/13196812.html