MySQL in layman's notes (stored procedure one)

1. Stored procedures and functions ....

Stored procedures and functions are a collection of SQL statements that have been compiled and stored in a database, and calling stored procedures and functions can simplify much of the work of the application developer.

The myth of reducing the data between the database and the application server has many advantages for improving data processing efficiency.

2. Differences between stored procedures and functions

1) The function must have a return value, and the stored procedure has no

2) The parameters of the stored procedure are in,out,inout

And the function is only in

3) The stored procedure is typically executed as a separate part (EXECUTE statement execution), and the function can be invoked as part of a query statement (select Call).

Since a function can return a Table object, it can be in a query statement after the FROM keyword. Stored procedures are not available in SQL statements, and functions can be used.

3. Create a stored procedure

     1) CREATE PROCEDURE F1 (in P1 int,in p2 int, out P3 int.)           READS SQL DATA           BEGIN               Select id from                inview               WH ere p1 = inview_id                 and   p2 = stroe_id and                 m1 (ID);            SELECT found_rows () into P3;          END

2) CREATE PROCEDURE C1_add (a int, b int) begin  declare c int,  if    A is null  then           set a = 0; end I F; If B is null then       set b = 0; end if; set c=a+b; select C as sum;end; Note: Stored procedures cannot be used Return...return can only be used in functions ... Call to the stored procedure Cl_add (10,20); The stored procedure needs to be called using the call function ... set @a=10; Set @b=20; We can also define two user variables ... call cl_add (@a,@b); Pass the value of the user variable past ...

4. The create syntax for stored procedures and functions does not support the use of the Create or
REPLACE changes stored procedures and functions, and if you need to modify an existing stored procedure or function, you need to
executes the ALTER syntax. ALTER PROCEDURE F1 modifies SQL DATA SQL SECURITY INVOKER; 5. Delete a stored procedure or function drop procedure F1; drop function ...; 6. View the definition of a stored procedure or function SHOW CREATE {PROCEDURE | FUNCTION} Sp_nam

7. Application of Variables 1) Definition of a variable: Declare defines a local variable whose scope is begin...end; DECLARE var_name[,...] Type [DEFAULT value] For example, define a variable p1 of type int: DECLARE p1 int; 2) variable assignment set Var_name = constant | | An expression set P1 =1;  

MySQL in layman's notes (stored procedure one)