Mysql stored procedure details and complete instance download, mysql Stored Procedure instance

Mysql stored procedure details and complete instance download, mysql Stored Procedure instance

I. Concept of Stored Procedure
1. Stored Procedure is a set of SQL statements to complete specific functions. Compiled and stored in the database.
2. stored procedure is an important object in the database. You can specify the name of the stored procedure and provide parameters (if the stored procedure has parameters) to execute it.
3. The stored procedure is written by flow control and SQL statements, which are compiled and optimized and stored on the database server.
4. The stored procedure can be executed by an application through one call, and the user is allowed to declare variables.
5. At the same time, stored procedures can receive and output parameters, return the status values of execution stored procedures, and can also be nested calls.
Ii. Advantages of Stored Procedures
1. Enhanced Functions and flexibility of SQL statements
2. A series of processing steps are not required to ensure data integrity.
3. The network traffic is reduced. To call a stored procedure on the client, you only need to pass the stored procedure name and related parameters. Compared with transmitting SQL statements, the amount of natural data is much less.
4. Enhanced security. The Stored Procedure allows unauthorized users to indirectly access the database under control to ensure data security.
5. Centralized control can be implemented. When the rules change, you only need to modify the stored procedure .. ,
Iii. disadvantages of Stored Procedures
1. debugging is not very convenient.
2. You may not have the right to create a stored procedure.
3. Re-compile the problem.
4. Portability issues.

Iv. Variables

1. User variable: Start with "@", in the form of "@ variable name ." User variables are bound to the MySQL client. The configured variables only apply to the client currently in use.
2. GLOBAL variables: The global variables are defined in the following two forms: set GLOBAL variable name or set @ global. variable name. Show global variables; effective for all clients. Only the super permission can be used to set global variables.
3. session variable: valid only for connected clients. Once the client loses connection, the variable becomes invalid. Show session variables;
4. Local variable: The scope of the variable is between the in and end statement blocks.

4.1 The declare statement set in the statement block is used to define local variables. Declare numeric number (9.95) [MySQL Data type, such as int, float, date, varchar (length)] default;

4.2 Variable assignment: SET variable name = expression value [, variable_name = expression...], set numeric = 1.2 or SELECT 2.3 into @ x;

V. mysql storage Program

1. Basic Syntax: create procedure process name ([process parameter [,...]) [feature...] process body; first look at the basic example

First:

delimiter ;;create procedure proc_on_insert()beginend;;delimiter

Second:

delimiter //create procedure proc_on_insert()beginend//delimiter ;;

Note:

1 ). note that delimiter // and delimiter; indicates the delimiter, because MySQL uses the separator ";" by default. If we do not declare the delimiter, then the compiler treats the stored procedure as an SQL statement, and the compilation process of the stored procedure reports an error. Therefore, you must first declare the current segment separator with the delimiter keyword so that MySQL can "; "As the code in the stored procedure.
2 ). the stored procedure may have input, output, and input and output parameters as needed. Here, there is an output parameter s, whose type is int type. If there are multiple parameters, separate them.
3) start and end of the Process body are identified by begin and emd.

2. call the stored procedure basic syntax: call sp_name ()

3. parameters: MySQL stored procedure parameters are used IN the definition of stored procedures. There are three parameter types: IN, OUT, And INOUT. The format is as follows:

Create procedure ([[in | out | inout] Parameter Name Data class...])
In input parameter: indicates that the value of this parameter must be specified when the stored procedure is called. Modifying the value of this parameter in the stored procedure cannot be returned, which is the default value.
Out output parameter: The value can be changed within the stored procedure and can be returned.
Inout input and output parameters: this parameter is specified during the call and can be changed or returned.

3.1in parameter example:

drop procedure if exists prc_on_in;delimiter ;;create procedure prc_on_in(in num int)begindeclare number int ;set number=num;select number;end;;delimiter ;;set @num=1;call prc_on_in(@num);

3.2out parameter creation example

drop procedure if exists prc_on_out;delimiter ;;create procedure prc_on_out(out out_num int)beginselect out_num;set out_num=78;select out_num;end;;delimiter ;;set @number=6;call prc_on_out(@number);

3.3inout parameter creation example

drop procedure if exists prc_on_inout;delimiter ;;create procedure prc_on_inout(inout p_inout int)beginselect p_inout;set p_inout=100;select p_inout;end;;delimiter ;;set @p_out=90;call prc_on_inout(@p_out);

3.4 IF statement in the Stored Procedure (if then elseif then else end if)

drop procedure if exists p_else;create procedure p_else(in id int)begin    if (id > 0) then        select '> 0' as id;    elseif (id = 0) then        select '= 0' as id;    else        select '< 0' as id;    end if;end;set @p=-10;call p_else(@p);

3.5 case when then in the Stored Procedure

drop procedure if exists p_case;delimiter ;;create procedure p_case(      id int  )  begin      case id      when 1 then         select 'one' as trans;      when 2 then      select 'two' as trans;      when 3 then       select 'three' as trans;      else      select 'no trans' as trans;      end case;  end;  ;;delimiter ;;set @id=1;call p_case(@id);

3.6 while do... End while statement

drop procedure if exists p_while_do;  create procedure p_while_do()  begin      declare i int;          set i = 1;          while i <= 10 do              select concat('index : ', i) ;              set i = i + 1;          end while;  end;  call p_while_do(); 

3.7 repeat... Until end repeat statement

drop procedure if exists p_repeat;delimiter ;;create procedure p_repeat(in parameter int)BEGIN     declare var int;       set var = parameter;      REPEAT     set var = var - 1;      set parameter = parameter -2;      UNTIL var<0     end REPEAT;     select parameter;END;;delimiter ;; set @parameter=1;call p_repeat(@parameter);

The REPEAT loop has the same function as the WHILE loop. The difference is that it checks whether the loop conditions are met after execution (until I> = 5 ), WHILE while is the pre-check (WHILE I <5 do ).
However, note that until I> = 5 is not followed by a plus sign. If the plus sign is used, a syntax error is prompted.

3.8 loop · end loop statement in the Stored Procedure

　　　　

drop procedure if exists p_loop;delimiter;;create procedure p_loop(in parameter int)BEGIN     declare var int;       set var = parameter;      LOOP_LABLE:loop     set var = var - 1;      set parameter = parameter -2;      if var<0 THEN   LEAVE LOOP_LABLE;     END IF;     end LOOP;     select parameter;END;;delimiter;;set @parameter=4;call p_loop(@parameter);

Using LOOP to write the same LOOP control statement is more complicated than using while and repeat: adding IF ...... The end if statement adds the LEAVE statement to the IF statement. The LEAVE statement means to exit the loop. The LEAVE format is the LEAVE loop label.

4. Use a cursor: defines a cursor, opens a cursor, uses a cursor, and closes a cursor.

Drop table if exists person; create table 'person '('id' int (11) not null default '0', 'age' int (11) default null, primary key ('id') ENGINE = InnoDB default charset = utf8; insert into person (age) value (1); drop procedure if exists prc_test1; delimiter ;; create definer = root @ localhost procedure prc_test1 () BEGIN declare var int;/** jump out of loop ID **/declare done int default false; /** declare the cursor **/declare cur cursor for select age from person;/** the exit ID for loop end setting **/declare continue handler for not FOUND set done = true; /** open cursor **/open cur; LOOP_LABLE: loop FETCH cur INTO var; select var; if done then leave LOOP_LABLE; end if; end LOOP; /** CLOSE cursor **/CLOSE cur; END; delimiter; call prc_test1 ();

5. query MySQL stored procedures

. View the stored procedure under a database

Select name from mysql. proc where db = 'database name ';
Or
Select routine_name frominformation_schema.routines where routine_schema = 'database name ';
Or
Show procedure status where db = 'database name ';

. View the stored procedure details

Show create procedure database. Name of the stored procedure;

6. Modify the MySQL Stored Procedure

Alter procedure: changes a pre-specified stored PROCEDURE created with create procedure without affecting the stored PROCEDURE or function.

7. delete a stored procedure
Drop procedure sp_name // annotate the function name

Mysql storage function instance: http://pan.baidu.com/s/1gf1Swk7 password: 282i