<>1 summary

Stored procedures are like subroutines in a program , Stored in the database . Stored procedures can have names , Parameter list and SQL sentence . use CALL Statement call .

<>2 characteristic

* Fast running speed . Take advantage of cache, etc .
* Portable
benefit :

* Simplify the work of application developers .
* Enhance security .
* Reduce data transmission between database and application server .
<>3 case

Create a stored procedure during actual operation
DELIMITER;; CREATE DEFINER=`root`@`localhost` PROCEDURE `selectitem`() BEGIN
#Routine body goes here... SELECT * from sys_role; END;; DELIMITER;
explain :
Divided into several parts : DELIMITER Set Terminator , set up ;; The reason is to avoid using ; Will directly lead to problems in the creation of stored procedures ; Time will end . create establish DEFINER=`
root`@`localhost` Declaration Definer PROCEDURE Stored procedure keywords `selectitem`() Stored procedure name -- Logical body of execution BEGIN
#Routine body goes here... SELECT * from sys_role; END
Call stored procedure , use call command
call selectitem`
<>4 Characteristics Clauses ( understand )

When creating a stored procedure, you can add features to the procedure , Called characteristic clause . It has the following characteristics

* COMMENT
It describes stored routines

* LANGUAGE SQL
Indicates storage process use SQL to write

* NOT DETERMINISTIC
Uncertainty , The same input parameters produce different results

* CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA
CONTAINS SQL: contain SQL ( Default value )

NO SQL: Not included SQL sentence

READS SQL DATA: Statement with read data , Statement without writing data

MODIFIES SQL DATA: Contains statements that write data

* SQL SECURITY { DEFINER | INVOKER }
Specify security context , The default value is DEFINER, Is the way to execute the stored process .

If it is DEFINER , Validate before executing stored procedures definer Whether the corresponding user exists , Whether you have execution permission .

If it is INVOKER , Verify whether the user who calls the stored procedure has corresponding permission before executing the stored procedure .

<>4.1 give an example
CREATE PROCEDURE p2 () LANGUAGE SQL NOT DETERMINISTIC SQL SECURITY DEFINER
COMMENT '' SELECT * FROM student;
<>5 Compound statement

A compound statement is a block containing execution , variable , Processing procedures for conditions, etc .

5.6 Version start Mysql There are the following compound statements :

*
BEGIN … END

*
Statement Label Statement label

*
DECLARE statement

*
Variables in Stored Programs Variables in stored programs

*
Flow Control Statements Process control statement

*
Cursors cursor

*
Condition Handling Condition handling

<>5.1 BEGIN … END

BEGIN … END Is a very common statement block , It usually represents an operation domain . Used to write compound statements .
CREATE DEFINER=`root`@`localhost` PROCEDURE `userpre`() COMMENT ' Test stored procedure ' BEGIN
#Routine body goes here... SELECT * from user; END
In a BEGIN … END Local variables in blocks are different from others BEGIN … END Local variables of blocks are not shared

<>5.2 DECLARE

DECLARE Can be used to declare variables in stored procedures . Need to be in BEGIN… END Used in compound statements . Declare before use .
CREATE DEFINER=`root`@`localhost` PROCEDURE `user_test_pre`() COMMENT ' Test stored procedure '
BEGIN -- local variable DECLARE fr int; #Routine body goes here... SELECT * from user; END
local variable , Only declare their BEGIN END Valid in block .
CREATE DEFINER=`root`@`localhost` PROCEDURE `user_test_pre`() COMMENT ' Test stored procedure '
BEGIN DECLARE fr int; set fr = 6 + 2; SELECT fr; END

<>6 User variables

Except for local variables , And user variables , Define use @ Variable name . Variables defined by a client cannot be seen or used by other clients . When the client exits , All variables connected by this client will be released automatically .
CREATE DEFINER=`root`@`localhost` PROCEDURE `user_test_pre`() COMMENT ' Test stored procedure '
BEGIN set @a = 34; set @b = @a + 6; SELECT @b,@a; END

<>7 Stored procedure parameters

Use parameters can be passed in when using stored procedures , Conduct logical processing .

There are three kinds of parameters :

* IN( default )
Indicates that this parameter is an input parameter , No output required

* OUT
Indicates that this parameter is an output parameter , This value will be returned after executing the stored procedure

* INOUT
Both input and output

case IN:
CREATE DEFINER=`root`@`localhost` PROCEDURE `user_test_pre`(IN a INT) COMMENT
' Test stored procedure ' BEGIN SELECT a; END

case OUT:
CREATE DEFINER=`root`@`localhost` PROCEDURE `user_test_pre`(OUT o INT) COMMENT
' Test stored procedure ' BEGIN set o = 2 + 1; END

case INOUT
CREATE DEFINER=`root`@`localhost` PROCEDURE `user_test_pre`(INOUT o INT)
COMMENT ' Test stored procedure ' BEGIN set o = o + 1; END

<>8 Flow control statement

Of course, the flow control of statements is indispensable for stored procedures . stay MySQL in support IF, CASE, ITERATE, LEAVE, LOOP, WHILE and REPEAT structure .

<>8.1 Branch statement

<>8.1.1 if

if Statement is a branch statement after learning programming language , But attention is different from SQL of if function .

grammar :
IF condition THEN statement(s) [ELSEIF condition THEN statement(s)] ... [ELSE
statement(s)] END IF
case :
CREATE DEFINER=`root`@`localhost` PROCEDURE `user_test_pre`(IN tname VARCHAR(16
)) COMMENT ' Test stored procedure ' BEGIN if tname = '1' then SELECT ' input 1'; elseif tname = '2'
then SELECT ' input 2'; else select ' wrong 1 wrong 2'; end if; END
input 1 result

<>8.1.2 case

case It is also a conditional branch statement . There are two kinds of grammar .
# grammar 1 CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN
statement_list] ... [ELSE statement_list] END CASE # case_value Can be a value
when_value Judgement and case_value Whether it is equal before executing the branch # grammar 2 CASE WHEN search_condition THEN
statement_list[WHEN search_condition THEN statement_list] ... [ELSE
statement_list] END CASE # search_condition expression such as a >2
case To transform the top if Cases of , Grammar one :
CREATE DEFINER=`root`@`localhost` PROCEDURE `user_test_pre`(IN tname VARCHAR(16
)) COMMENT ' Test stored procedure ' BEGIN case tname when '1' then SELECT ' input 1'; when '2' then
SELECT ' input 2'; else select ' wrong 1 wrong 2'; end case; END
grammar 2:
CREATE DEFINER=`root`@`localhost` PROCEDURE `user_test_pre`(IN tname VARCHAR(16
)) COMMENT ' Test stored procedure ' BEGIN case when tname = '1' then SELECT ' input 1'; when tname =
'2' then SELECT ' input 2'; else select ' wrong 1 wrong 2'; end case; END
<>8.2 loop

The statement keywords related to the loop are ITERATE, LEAVE, LOOP, WHILE,REPEAT .

<>8.2.1 WHILE

grammar :
[begin_label:] WHILE search_condition DO statement_list END WHILE [end_label]
When search_condition When the condition is true , implement statement_list Code block for .

example :

use while Total cyclic stacking
CREATE DEFINER=`root`@`localhost` PROCEDURE `user_test_pre`(IN number INT)
COMMENT ' Test stored procedure ' BEGIN declare i int DEFAULT 0; set @sum = 0; WHILE number > i
DO set i = i + 1; set @sum = @sum + i; END WHILE; SELECT @sum; END
parameter 5 Hour , namely 1+2+3+4+5, result :

<>8.2.2 ITERATE

ITERATE It means to restart Xunhua , Use in circulation LOOP, REPEAT and WHILE Within a statement , usage LEAVE label. analogy Java of continue.

label Is a label Can be used to represent a loop body , The following examples will be used label.

<>8.2.3 LEAVE

LEAVE Statement to exit a loop . analogy Java of break.

stay SQL In addition RETURN keyword , But only for functions , Stored procedures cannot be used .

<>8.2.4 LOOP

Repeat statement

grammar :
[begin_label:] LOOP statement_list END LOOP [end_label]
example :

combination user surface , Write one loop Examples of , The original table is as follows , We need to be right age Insert a random number .

CREATE DEFINER=`root`@`localhost` PROCEDURE `user_test_pre`(IN number INT)
COMMENT ' Test stored procedure ' BEGIN declare i int DEFAULT 1; set @sum = 0; loop_label:LOOP
update user set age = (RAND() * 10) where id = i; set i = i + 1; -- Define exit conditions if
number< i then leave loop_label; end if; end loop loop_label; END
input 5 Is a parameter , result :

<>8.2.5 REPEAT

REPEAT Repeat statement , And LOOP Similar are loop statements that execute first and then judge . Able to use labels .

grammar :
[begin_label:] REPEAT statement_list UNTIL search_condition END REPEAT [
end_label]
example :

and while Sum as an example of
CREATE DEFINER=`root`@`localhost` PROCEDURE `user_test_pre`(IN number INT)
COMMENT ' Test stored procedure ' BEGIN declare i int DEFAULT 1; set @sum = 0; REPEAT set @sum =
i+ @sum; set i = 1 + i; UNTIL i > number end REPEAT; SELECT @sum; END
Parameter transmission 5, The result is still 15.

<>9 Modify stored procedure

It can be used to modify the storage process ALTER sentence , However, the implementation logic and parameter types of stored procedures cannot be modified .

grammar :
ALTER PROCEDURE proc_name [characteristic ...]characteristic: COMMENT 'string'
| LANGUAGE SQL | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
example :

modify comment
mysql> alter procedure user_test_pre COMMENT ' Comments on the modification process '; Query OK, 0 rows
affected (0.00 sec) mysql>
View results :

<>10 Delete stored procedure

Delete stored procedures in the specified database

grammar :
DROP PROCEDURE [ IF EXISTS ] < Process name >
<>11 cursor

Database cursor is a control structure , You can traverse the records in the database . Cursors can operate on the entire result set at the same time .

The cursor has the following properties :

* Asensitive:
There are two types of cursors : Sensitive and insensitive cursors . Sensitive cursor points to actual data , Temporary copy of data used by insensitive cursors . If the data used by sensitive cursors is not updated , Is safer . MySQL Cursor is sensitive .
* Read only: read-only , Cannot update data in the underlying table through cursor
* Nonscrollable: Can only traverse in one direction , Cannot skip row reading or read rows in reverse order .
How to use cursors , Altogether 4 step :

* declare cursor DECLARE cursor_name CURSOR FOR select_statement
* Open cursor OPEN cursor_name
* Cursor extracts data into variables FETCH [[NEXT] FROM] cursor_name INTO var_name [, var_name] ...
Read data from cursor to variable .

* Close cursor CLOSE cursor_name
Note when using cursors , The order of declaration requires that variables be declared first , Declaration conditions , Then declare the cursor , The handler declares this order .

example :

Get all the names , And spliced together to find out .
CREATE DEFINER=`root`@`localhost` PROCEDURE `user_test_pre`() COMMENT ' Comments on the modification process '
BEGIN declare var_name,every_name VARCHAR(255) DEFAULT ""; declare b int DEFAULT
0; declare cursor_a CURSOR FOR (select name from user); declare continue handler
for not FOUND SET b = 1; open cursor_a; read_l:LOOP FETCH cursor_a into var_name
; -- Judge to exit the cycle if b=1 then LEAVE read_l; end if; set every_name = concat(every_name
,",",var_name); end LOOP read_l; CLOSE cursor_a; SELECT SUBSTR(every_name FROM 2
); END
Handler declaration :declare continue handler for not FOUND SET b =
1; Statement because the cursor keeps reading data down , We need a condition to know that it is finished , use NOT FOUND Treatment , When there is no data setting b = 1.

be careful :

* FETCH Then the cursor will go down , So in statistics FETCH If you judge later, you can prevent a cycle .
* The received variable name should not be consistent with the column name of the table , Otherwise, the value may not be obtained .
<>12 Access control of stored programs

Stored programs include stored procedures , function , trigger .

Confirm that you have permission to execute when using stored programs and views , All stored programs ( process , Functions and triggers ) And views can have one DEFINER attribute , Can it be implemented in DEFINER
Attribute control , If any SQL SECURITY definition , priority of use SQL SECURITY Definition in . If omitted in the stored program or view definition DEFINER
attribute , The default account is the user who created the object .

MySQL Use the following rules to control users , Can be in DEFINER Which accounts are specified in the properties :

* Only in possession SUPER Privileged circumstances , You can specify other than your own account DEFINER value
* without SUPER privilege , Then the only legal user value is your own account , Can be specified literally , You can also use CURRENT_USER. Cannot set definer to another account
* Use in object definitions whenever possible SQL SECURITY INVOKER, So that only users with permissions corresponding to the operation performed can use it .
* If in use with SUPER Create a program or view that defines the context store when a privileged account is created , Please specify an explicit DEFINER
attribute , This attribute specifies an account , This account has only the privileges required to perform operations . Assign high privileges only when absolutely necessary DEFINER account
* Administrators can not grant users SUPER Privilege to prevent users from specifying high privileges DEFINER account
* It can be prevented by not granting special permissions to unauthorized users , Other users call unauthorized data
* No, EXECUTE Users with permissions cannot reference stored procedures or functions
* Views cannot be invoked and executed by users without corresponding permissions
<>13 other

<>1. View stored procedures

1.1 View the created stored procedure
SHOW CREATE PROCEDURE xxx( Process name );
1.2 View process status
show procedure status where db=' Database name ';
<>2. Complete syntax of a stored procedure
CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE sp_name
([proc_parameter[,...]]) [characteristic ...] routine_body proc_parameter: [ IN
| OUT | INOUT ] param_name type type: Any valid MySQL data type characteristic:
COMMENT 'string' | LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL
| READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER }
routine_body: Valid SQL routine statement
<>3. Pass parameter execution sql characteristic , Execute in stored procedure sql, Sometimes build sql The parameters of will not take effect . such as :
CREATE DEFINER=`root`@`localhost` PROCEDURE `user_test_pre`(IN tname VARCHAR(16
)) COMMENT ' Test stored procedure ' BEGIN -- Create a database CREATE DATABASE IF NOT EXISTS tname; END
hypothesis : Execution process incoming ta Name will not be created ta Library for , Instead, create tname database .

Modify it :
CREATE DEFINER=`root`@`localhost` PROCEDURE `user_test_pre`(IN tname VARCHAR(16
)) COMMENT ' Test stored procedure ' BEGIN -- Create a database set @cdb = concat("CREATE DATABASE IF NOT
EXISTS ", tname); -- Using precompiled methods PREPARE tempsqsl from @cdb; execute tempsqsl; END
This will solve the problem .

Technology
©2019-2020 Toolsou All rights reserved,
Solve in servlet The Chinese output in is a question mark C String function and character function in language MySQL management 35 A small coup optimization Java performance —— Concise article Seven sorting algorithms (java code ) use Ansible Batch deployment SSH Password free login to remote host according to excel generate create Build table SQL sentence Spring Source code series ( sixteen )Spring merge BeanDefinition Principle of Virtual machine installation Linux course What are the common exception classes ?