Shulou(Shulou.com)06/01 Report--

1 stored procedure

1.1 what is a stored procedure

A stored procedure is a set of sql statements in order to accomplish a specific function, which is essentially a piece of code stored in the database. It can be composed of declarative sql statements (such as CREATE,UPDATE,SELECT statements) and procedural sql statements (such as IF...THEN...ELSE control structure statements). The idea of stored procedure is very simple, which is the code encapsulation and reuse at the level of database SQL language.

1.2 advantages and disadvantages of stored procedures

Advantages:

1. Enhances the functionality and flexibility of the sql language

Stored procedures can be written in process control language, which has strong flexibility and can complete complex judgments and complex operations.

two。 Good encapsulation

After a stored procedure is created, it can be called many times in the program without having to worry about rewriting the sql statement that wrote the stored procedure.

3. High performance

After the stored procedure is executed once, its execution plan resides in the cache, and the later operations only need to call the compiled binary code from the cache for execution, thus improving the system performance.

Disadvantages:

Stored procedures are often customized on specific databases because of the different programming languages supported. When switching to another vendor's database system, you need to rewrite the original stored procedure.

1.3 create stored procedures

1.3.1 DELIMITER delimiter

In sql, the server processes sql statements with a semicolon as the end of the statement by default. However, when creating a stored procedure, the stored procedure body may contain multiple sql statements. If these sql statements still use the semicolon as the statement Terminator, then the server will use the semicolon at the first sql statement as the Terminator of the whole program instead of dealing with the following sql.

To solve this problem, the DELIMITER command is usually used to temporarily change the Terminator of a sql statement to another symbol.

DELIMITER syntax format:

DELIMITER $$

$is a user-defined Terminator, and usually this symbol can be some special symbol. In addition, you should avoid using a backslash because it is an escaped character.

If you want to change back to the default semicolon as the closing tag, simply type the following sql statement on the command line.

DELIMITER

1.3.2 stored procedure creation

In Mysql, use CREATE PROCEDURE statements to create stored procedures.

CREATE PROCEDURE p_name ([proc_parameter [,...]]) routine_body

Among them, the grammatical format of the grammar item "proc_parameter" is:

[IN | OUT | INOUT] parame_name type

1. "p_name" is used to specify the name of the stored procedure.

2. "proc_parameter" is used to specify the list of parameters in the stored procedure. Where the grammar item "parame_name" is the parameter name and "type" is the type of the parameter (the type can be any valid data type in Mysql). The stored procedure of Mysql supports three types of parameters, namely the input parameter IN, the output parameter OUT, and the input and output parameter INOUT. The input parameter is so that the data can be passed to a stored procedure; the output parameter is an operation result that needs to be returned by the stored procedure; the input and output parameters can act as both input parameters and output results.

The name of the parameter should not be the same as the column name in the table, otherwise, although the error message will not be returned, the sql statement during storage will treat the parameter name as the column name, causing an unpredictable error.

3. The grammar item "rountine_body" represents the body of the stored procedure and also becomes the body of the stored procedure, which contains the sql that needs to be executed. The process body begins with the keyword BEGIN and ends with the keyword END. If there is only one sql, you can ignore the BEGIN....END flag.

1.3.3 Local variable

Local variables can be declared in the body of a stored procedure to store temporary results in the body of the process. Use the declare statement in Mysql to declare local variables.

DECLARE var_name type [DEFAULT value]

"var_name" is used to specify the name of the local variable; "type" is used to declare the type of variable; "DEFAULT" is used to specify the default value, or NULL if not specified.

Note: the local variable can only be in the BEGIN...END statement block of the stored procedure body; the local variable must be declared at the beginning of the stored procedure body; the scope of the local variable is limited to the BEGIN...END statement block in which it is declared, and statements in other statement blocks cannot use it.

1.3.4 user variabl

User variables usually start with @.

Note: misuse of user variables can make the program difficult to understand and manage.

1.3.5 SET statement

Assign values to local variables through SET statements in Mysql in the following format:

SET var_name = expr [, var_name2 = expr].

1.3.6 SELECT....INTO statement

In Mysql, you can use the select.. int statement to store the value of the selected column in a local variable. The format is:

SELECT col_name [,..] INTO var_name [,....] Table_expr

Where "col_name" is used to specify the column name; "var_name" is used to specify the name of the variable to be assigned; and "table_expr" represents the part of the SELECT statement that follows FROM.

Note: the result set returned by the SELECT...INTO statement can only have a row of data.

1.3.7 process control statement

Conditional judgment statement

If-then-else statement:

Mysql > DELIMITER & & mysql > CREATE PROCEDURE proc2 (IN parameter int)-> begin-> declare var int;-> set var=parameter+1;-> if var=0 then-> insert into t values (17);-> end if;-> if parameter=0 then-> update t set S1 / S1 / 2;-> else-> update t set S1 / S1 / 2;-> end if;-> end;-> & & mysql > DELIMITER

Case statement:

Mysql > DELIMITER & & mysql > CREATE PROCEDURE proc3 (in parameter int)-> begin-> declare var int;-> set var=parameter+1;-> case var-> when 0 then-> insert into t values (17);-> when 1 then-> insert into t values (18);-> else-> insert into t values (19);-> end case;-> end;-> & mysql > DELIMITER

Loop statement

While end while:

Mysql > DELIMITER & & mysql > CREATE PROCEDURE proc4 ()-> begin-> declare var int;-> set var=0;-> while var insert into t values (var);-> set var=var+1;-> end while;-> end;-> & & mysql > DELIMITER

Repeat end repea:

It checks the results after the operation is performed, while the while checks before execution.

Mysql > DELIMITER & & mysql > CREATE PROCEDURE proc5 ()-> begin-> declare v int;-> set vault 0;-> repeat-> insert into t values (v);-> set vault 1;-> until v > = 5-> end repeat;-> end;-> & & mysql > DELIMITER; repeat-cyclic body until cycle condition end repeat

Loop endloop:

The loop loop does not need an initial condition, which is similar to the while loop, and like the repeat loop, there is no end condition, and the meaning of the leave statement is to leave the loop.

Mysql > DELIMITER & & mysql > CREATE PROCEDURE proc6 ()-> begin-> declare v int;-> set vault 0;-> LOOP_LABLE:loop-> insert into t values (v);-> set vault 1;-> if v > = 5 then-> leave LOOP_LABLE;-> end if;-> end loop;-> end;-> & & mysql > DELIMITER

ITERATE iteration:

Mysql > DELIMITER & & mysql > CREATE PROCEDURE proc10 ()-> begin-> declare v int;-> set vault 0;-> LOOP_LABLE:loop-> if vault 3 then-> set vault 1;-> ITERATE LOOP_LABLE;-> end if;-> insert into t values (v);-> set vault 1;-> if v > = 5 then-> leave LOOP_LABLE;-> end if;-> end loop;-> end;-> & mysql > DELIMITER

1.3.8 Vernier

The cursor in MySQL can be understood as an iterable object (such as list and dictionary in Python). It can be used to store the result set queried by select statements, which can contain multiple rows of data, so that we can use iterative method to extract each row of data from the cursor in turn.

Features of MySQL cursors:

1. Read-only: the data in the underlying table cannot be updated with the cursor.

two。 Non-scrollable: rows can only be fetched in the order determined by the select statement. You cannot get rows in reverse order. In addition, you cannot skip rows or jump to specific rows in the result set.

3. Sensitive: there are two types of cursors: sensitive and insensitive. Sensitive cursors point to actual data, while insensitive cursors use temporary copies of data. A sensitive cursor executes faster than an insensitive cursor because it does not need to temporarily copy data. MySQL cursors are sensitive.

1. Declare a cursor

The cursor declaration must be after the variable declaration. If the cursor is declared before the variable declaration, MySQL will issue an error. Cursors must always be associated with select statements.

Declare cursor_name cursor for select_statement

two。 Open the cursor

Use the open statement to open the cursor, and you must open the cursor before you can read the data.

Open cursor_name

3. Read cursor

Use the fetch statement to retrieve the row of data pointed to by the cursor and move the cursor to the next row in the result set.

Fetch cursor_name into var_name

4. Close the cursor

Use the close statement to close the cursor.

Close cursor_name

When the cursor is no longer in use, it should be closed. When using MySQL cursors, you must also declare a notfound handler to handle situations when the cursor cannot find any rows. Because each time the fetch statement is called, the cursor attempts to read each row of data in the result set in turn. When the cursor reaches the end of the result set, it will not be able to get the data and will produce a condition. Handlers are used to handle this situation.

Declare continue handler for not found set type = 1

Type is a variable that indicates that the cursor reaches the end of the result set.

Delimiter $$create PROCEDURE phoneDeal () BEGIN DECLARE id varchar (64);-- id DECLARE phone1 varchar (16);-- phone DECLARE password1 varchar (32);-- password DECLARE name1 varchar (64);-- id-- traversal data end flag DECLARE done INT DEFAULT FALSE;-- cursor DECLARE cur_account CURSOR FOR select phone,password,name from account_temp;-- bind end flag to cursor DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;-- Open cursor OPEN cur_account -- iterate through read_loop: LOOP-- take multiple fields FETCH NEXT from cur_account INTO phone1,password1,name1; IF done THEN LEAVE read_loop; END IF;-- insert into account (id,phone,password,name) value (UUID (), phone1,password1,CONCAT (parent of name1,')) that you want to do; END LOOP;-- close the cursor CLOSE cur_account;END $$

1.3.7 call stored procedure

To call a stored procedure using a call statement

Call sp_name [(passing parameters)]

1.3.8 deleting stored procedures

Delete a stored procedure using the drop statement

DROP PROCEDURE sp_name

2 storage function

2.1 what is a storage function

Stored functions, like stored procedures, are blocks of code made up of sql and statements.

The storage function cannot have input parameters and can be called directly, does not require a call statement, and must have a RETURN statement.

2.2 create a storage function

Use the CREATE FUNCTION statement in Mysql to create:

CREATE FUNCTION fun_name (par_name type [,...]) RETURNS type [characteristics] fun_body

Where fun_name is the function name and the name is unique and cannot be duplicated with the stored procedure. Par_name is the specified parameter and type is the parameter type; the RETURNS clause is used to declare the return value and the return value type. Fun_body is the body of a function, and sql in all stored procedures can also be used in stored functions. However, the body of the storage function must contain a RETURN statement.

Characteristics specifies the properties of the stored procedure, with the following values:

LANGUAGE SQL: indicates that the routine_body part is made up of SQL statements. The current language supported by the system is that SQL,SQL is the only value of the LANGUAGE feature. [NOT] DETERMINISTIC: indicates whether the result of the stored procedure execution is determined. DETERMINISTIC indicates that the result is certain, the same input will get the same output each time the stored procedure is executed, NOT DETERMINISTIC indicates that the result is uncertain, and the same input may get different output. If no value is specified, the default is NOT DETERMINISTIC. [CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA]: specifies the restrictions on the use of SQL statements by subroutines. CONTAINS SQL indicates that the subroutine contains SQL statements, but does not contain read and write data statements; NO SQL indicates that the subroutines do not contain SQL statements; READS SQL DATA indicates that the subroutines contain statements to read data; and MODIFIES SQL DATA table name subroutines contain statements to write data. By default, the system is specified as CONTAINS SQL. SQL SECURITY [DEFINER | INVOKER]: indicates who has permission to execute. DEFINER means that it can only be executed if it is defined. INVOKER indicates that callers with friend permissions can execute. By default, the system is designated as DEFINER. COMMENT 'string': comment information, used to describe stored procedures or functions. Delimiter $$create function getAnimalName (animalId int) RETURNS VARCHAR (50) DETERMINISTICbegin declare name VARCHAR (50); set name= (select name from animal where id=animalId); return (name); end$$delimiter;-- call select getAnimalName (4)

The above is the whole content of this article, I hope it will be helpful to your study, and I also hope that you will support it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.