1. Procedural sql and embedded sql
1.1 embedded sql
Embedded SQL refers to including SQL statements into programming language. The embedded programming language, such as C / CPP / Java, is called the host language.
For embedded SQL, DBMS generally adopts the precompiled method, that is, the preprocessor of DBMS scans the source program, identifies the embedded SQL statements and converts them into the statements of the main language, and the compiler of the main language can recognize them and connect other parts of the code for compilation.
1.2. Procedural sql
1.2.1 why is procedural sql introduced?
Basic SQL is a highly non procedural language. Embedded SQL embeds the SQL statement into the programming language and realizes the process with the control function of high-level language (embedded SQL realizes the process with the help of the process control statement of the main language (such as loop statement, conditional control statement, etc.), which itself has no process control function (this method is clumsy and inefficient) Therefore, in order to better meet the work requirements, procedural SQL is introduced.
1.2.2 overview of procedural sql
Procedural Language/SQL (PL/SQL for short) can be regarded as a programming language. PL/SQL is an extension of SQL statements (adding variables, process control statements - loop statements and conditional control statements), and adds the characteristics of programming language to the common SQL statements.
The basic structure of procedural SQL programs is blocks. Blocks can be nested before each other, and each block completes a logical operation.
Let's introduce the variables and process control statements in procedural sql.
2. Variable
Variables can be divided into system variables, session variables and local variables.
2.1 system variables
2.1.1 introduction to system variables
System variables (global) (also known as server system variables and global variables) are predefined by the system and return some system information. System variables are stored in information_ Global of schema system database_ In the variables table, we can query the information provided by the system like a normal table_ schema.GLOBAL_VARIABLES global variable table.
2.1.2. Viewing system variables
There are two ways to view system variables, as follows:
SHOW global VARIABLES [like 'pattern']
SELECT @@global.var_name
Global and global. Cannot be omitted. If omitted, the session level variable will be viewed.
Example 1: view all system variables, show global variables; The command is used to view all global variables:
show global variables; #View all system variables
The execution results are shown in the figure below. You can see many system variables:
Example 2: view a system variable, such as autocommit:
show global variables like 'autocommit'; #View the specified system variables. The syntax is show global variables like 'pattern';
The execution results are as follows:
Or use select @@global.var_name, example 3:
select @@global.autocommit;
The execution results are as follows:
You can see the SHOW global VARIABLES [like 'pattern'] statement and SELECT @@global.var_name is different.
When show global variables like 'pattern' is used, the pattern can contain matching characters. This is actually the use of the like statement, which is also applicable elsewhere. Example 4:
show variables like 'version%'; #Use character matching # %Represents a string of arbitrary length # _ Represents any single character
The execution results are as follows:
You can also use select to view multiple system variables. Example 5:
The execution results are as follows:
2.1.3. Modify system variables globally
Why global modification of system variables? Because there is another way to modify system variables at the session level, which will be introduced in section 2.2.4 of this article. I suggest readers collect this article, read it repeatedly and practice it, so as to deeply understand the knowledge of this article. Learning is like this, try again and again, learn from experience and never stop.
Global modification is effective for all clients at all times.
Two modification forms:
set global variable name = new_value
set @@global. Variable name = new_value
Note: "=" in mysql can mean either assignment or equal comparison operator. Sometimes errors occur because of this situation. Therefore, ": =" is allowed to be used for assignment in mysql for safety reasons)
For example, use set global variable name = new_ Modify the system variable in the form of value:
mysql> select @@global.autocommit; #View the original value +---------------------+ | @@global.autocommit | +---------------------+ | 1 | +---------------------+ 1 row in set (0.00 sec) mysql> set global autocommit:=0; #Modify system variables globally Query OK, 0 rows affected (0.00 sec) mysql> select @@global.autocommit; #View modified variables +---------------------+ | @@global.autocommit | +---------------------+ | 0 | +---------------------+ 1 row in set (0.00 sec)
You can see that the system variable in the new connection has been modified. From the next connection, the session level system variable will become the modified system variable. For the knowledge of session level system variables, please refer to Section 2.2 of this article. After reading this sentence, you can understand it.
For example, use the form of set @@global. Variable name to modify the original value of autocommit based on the previous:
mysql> set @@global.autocommit=1; #After the last modification, change the autocommit global to 1 Query OK, 0 rows affected (0.00 sec) mysql> select @@global.autocommit; #Query modified results +---------------------+ | @@global.autocommit | +---------------------+ | 1 | +---------------------+ 1 row in set (0.00 sec)
2.2. Session variables
2.2.1 introduction to session variables
Session variables (also known as user-defined variables and user variables) are composed of user-defined variables and session level system variables (not real system variables, but the variable names and values are the same as those of system variables, which can be used as a mirror image of system variables and are only valid for the current connection)., Only valid for the current session (connection). Global variables affect the overall operation of the server, while session variables affect the operation of individual client connections.
2.2.2. Define session (user) variables
This is because session variables include two parts: user-defined variables and session level system variables. Therefore, we can only define the user's own variables - user-defined variables.
Next, the user-defined variables are introduced, that is, the user-defined variables in the session variables.
The first syntax uses the set statement:
set @ variable name = expr;
set @ variable name: = expr;
For SET, you can use = or: = as the allocator (don't forget the previous precautions. It's safer to use: = instead of =. It's recommended to use: =) in the future. The expr assigned to each variable can be an integer, a real number, a string, or a NULL value.
The second is to use the SELECT statement:
Refer to the select assignment modification statement in section 2.2.3. It can be understood that assigning a value to a non-existent variable is equivalent to defining a variable, which is allowed. However, it is recommended to use the above syntax definition. In a few cases, variables are defined in this way.
Supplementary knowledge: if the variable does not specify an initial value when declared, by default, the variable is initialized to NULL value. If you use the variable directly without assigning a value to the variable, it will produce unexpected results. If you reference a variable that has not been declared, its value is NULL.
For example, first view a non-existent variable a, which will show that the value of a is null, then define a variable a and assign a value to it, and finally view variable a:
mysql> select @a; +------+ | @a | +------+ | NULL | +------+ 1 row in set (0.00 sec) mysql> set @a='A'; Query OK, 0 rows affected (0.00 sec) mysql> select @a; +------+ | @a | +------+ | A | +------+ 1 row in set (0.00 sec)
If you close this connection and connect again, the session variable a will be cleared.
2.2.3. View session variables
Session variables include two parts: user-defined variables and session level system variables. There are different viewing methods for these two parts:
To view session level system variables:
SHOW [session] VARIABLES [like 'pattern']
SELECT @@[session].var_name
[session]: this part can be omitted. By default, it is the operation of session level system variables.
To view user-defined variables:
select @ variable name 1 [, @ variable name 2,...]
Note: unlike viewing system variables and session level system variables, two @ symbols are used for viewing user-defined variables, and one @ symbol is used for viewing user-defined variables.
2.2.4 assigning values to session variables
1. Assign a value to the session level system variable, that is, modify the system variable at the session level mentioned in the above section
Syntax: set [session] var_name=new_value
[session]: this part can be omitted. By default, it is the operation of session level system variables.
Session level modification of system variables is only valid for the current user's current connection.
One more point: in fact, we will mainly modify session level system variables in stored procedures or other places in the future. (I will point this out in the following articles)
Modify system variables at the session level, for example:
root@localhost:(none) 05:44:22>select @@autocommit; +--------------+ | @@autocommit | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec) root@localhost:(none) 05:45:29>set autocommit=0; #Or use set session autocommit=0; Query OK, 0 rows affected (0.00 sec) root@localhost:(none) 05:45:55>select @@autocommit; +--------------+ | @@autocommit | +--------------+ | 0 | +--------------+ 1 row in set (0.00 sec) #As can be seen from the above execution results, the system variables have been successfully modified at the session level. #But note: use set variable name = new_ The value method is only valid for the current user's current connection. #If we switch users or reconnect, the original value will be changed.
Let's reconnect and check the variable autocommit just modified. You can find that the variable is restored to its original value:
root@localhost:(none) 05:55:19>select @@autocommit; +--------------+ | @@autocommit | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec)
Why?
Because MySQL thinks that opening a client is opening a session. Its essence is to establish a network connection. This network connection is stateful. Once closed, the variables set in this session level will be restored to system variables.
2. Assign values to user-defined variables
set assignment:
SET @var_name = expr;
select assignment:
select @ variable 1: = field 1 [, @ variable 2: = field 2,...] from table name [where statement] limit 1; # ( In this form, the result after assignment will be displayed)
select field 1 [, field 2,...]from Table name [where statement] limit 1 into@var_name1[,@var_name1,...];
select function into @var_name1[,@var_name1,...];
select @ variable 1:=expr1 [, @ variable 2:=expr2,...]; # Note that I use ": =", not "=". The reason is in Note 2 below
Note 1. mysql allows data to be taken from the table and stored in variables, but the queried data must be a row of data. Therefore, use "limit 1" to limit the result to 1 row of data. If there are multiple rows of data, error 1172 (42000) will be reported: result considered of more than one row.
Note 2. When using SELECT to define user variables, you can only use: = as the allocator (because = is regarded as a 'comparison operator' in non SET statements)
For example, first define and initialize a session variable t, then view the value of T, and then use different assignment statements to modify the value of the variable:
mysql> set @t=2; #Define and initialize Query OK, 0 rows affected (0.00 sec) mysql> select @t; #View session variables +------+ | @t | +------+ | 2 | +------+ 1 row in set (0.01 sec) mysql> set @t=5; #Modify the value of the session variable in the form of set @ session variable name = expr Query OK, 0 rows affected (0.00 sec) mysql> select @t; +------+ | @t | +------+ | 5 | +------+ 1 row in set (0.00 sec) mysql> select now() into @t; #Use the select function into @var_name1[,@var_name1,...]; Modify the value of the session variable as Query OK, 1 row affected (0.00 sec) mysql> select @t; +---------------------+ | @t | +---------------------+ | 2021-11-29 09:27:31 | +---------------------+ 1 row in set (0.00 sec) mysql> select @name:=Sname,@age:=Sage from student where Ssex='male' limit 1; #name and age are variables that have not been defined before #This is the definition of variables using assignment statements I mentioned above. +--------------+------------+ | @name:=Sname | @age:=Sage | +--------------+------------+ | Li Yong | 20 | +--------------+------------+ 1 row in set (0.00 sec) mysql> select @name,@age; +--------+------+ | @name | @age | +--------+------+ | Li Yong | 20 | +--------+------+ 1 row in set (0.00 sec) mysql> select Sname,Sage from student mysql> where Ssex='male' mysql> order by Sage desc limit 1 into @name,@age; Query OK, 1 row affected (0.00 sec) mysql> select @name,@age; +--------+------+ | @name | @age | +--------+------+ | Li Yong | 20 | +--------+------+ 1 row in set (0.00 sec)
2.3 local variables
2.3.1 introduction to local variables
The scope is between the begin and end statement blocks. When the statement is executed, the local variables disappear. Local variables usually appear in stored procedures for intermediate calculation results, data exchange, etc. when the stored procedures are executed, the life cycle of variables will end.
2.3.2 defining local variables
You need to declare a local variable before using it. The local variable is declared by the declare statement (the declare declared variable must be at the beginning between begin and end, otherwise an error will occur.):
DECLARE
var_name1 data type # Define variables
var_name2 data type not null # Define non null variables
var_name3 data type default value # Define variables and initialize with default values
var _name4 data type not null default value # Define non null variables and initialize with default values
var_name5 CONSTANT data type DEFAULT value # Constant means that it is declared as a constant. When defining a constant, you need to specify the initial value. Once its value is defined, it can no longer be changed.
NOT NULL: the value used to constrain the variable cannot be empty.
: = value: used to assign an initial value to a variable.
The DEFAULT value is declared by the DEFAULT clause, and the DEFAULT value can also be an expression.
(supplementary note: you can use% TYPE to declare the same TYPE as the database column, such as declare name student.Sname%TYPE;
%Rowtype is similar to% TYPE, which is used to bind to all fields of the database table,% TYPE is only bound to the TYPE of a single database column, while% rowtype is bound to all column types of an entire row. Variables defined with% rowtype can be regarded as a data TYPE, such as: s student%ROWTYPE;)
The scope of the local variable is limited to the declared BEGIN...END block. When the statement is executed, the local variable disappears.
For example, define a local variable:
DECLARE
name VARCHAR2(20), --Define variables without assignment
birthday DATE NOT NULL, --Define non null variables
age smallint default 20, --Define variables and initialize the default value 20
tell varchar(20) NOT NULL DEFAULT 110, --Define non null variables and initialize with default values
hobby constant varchar(20) default "basketball"; #Define constants
BEGIN
NULL; --No code is executed
END;Usage examples for% TYPE and% ROWTYPE:
declare name student.Sname%type, s student%rowtype; begin select Sname into name from student where Ssex='male' limit 1; #Assign values to local variables select * into s from student limit 1; #Assign a value to a variable of type% rowtype begin s.Sname='Zhang San'; #modify end
2.3.3. View local variables
Syntax:
select var_name;
2.3.4 assigning values to local variables
1. Assignment method 1: direct assignment
Variable name :=expression; set Variable name 1:=Expression 1,Variable name 2:=Expression 2,...; set Variable name 1=Expression 1,Variable name 2=Expression 2,...;
2. Assignment method 2: select into statement
select Field 1[,Field 2,..] into Variable name 1[,Variable name 2,...] from Table name [where sentence] limit 1;
3. Process control statement
Process control statements mainly include conditional control statements and loop control statements.
3.1 condition control statement
3.1.1 if statement:
1. The first form:
if condition then
statement; # Statement that is executed only when the condition is true
end if;
2. If else statement syntax:
if condition then
statement1; # Statement that is executed only when the condition is true
else
statement2; # A statement that is executed only when the condition is false
end if;
3. Nested if statements
if condition1 then
statement1;
elseif condition2 then
statement2;
else
statement3;
3.1.2. case statement
There are two forms of case statements:
1. The first form:
case var
when value1 then statement_list1;
[
when value2 then statement_list2;
...]
[else statement_list;]
end case;
2. The second form:
case
when condition1 then statement_list1;
[
when condition2 then statement_list2;
...
]
[else statement_list;]
end case;
Where condition1, condition2,... Represent conditional expressions.
3.2. Loop control statement
3.1 loop loop statement
The loop statement only creates a loop process without conditional judgment. You can use the leave statement to end the whole loop. The syntax format is as follows:
[label:] loop
statement_list;
end loop [label];
label is a flag name used to refer to the whole cycle and can be omitted.
Loop statement is used for loop operation. When the id value is less than or equal to 10, the loop will be executed repeatedly. The code is as follows:
declare id int default 0;
add_loop:loop
set id=id+1;
if id>=10 then leave add_lable; #leave is used to end the whole loop, which is equivalent to break in other programming languages
end if;
end loop add_loop;3.2. while loop statement
The function of the while statement here is the same as that of the while statement in other programming languages. When the conditional expression is true, the loop continues; Otherwise, the cycle ends. However, the syntax format is different. The syntax format is as follows:
[label:] while condition do
statement_list;
end while [label];
label
condition is a conditional expression.
In the example of while statement, when the id value is less than 10, the loop process will be repeated:
declare id int default 0;
while i<10 do
set i=i+1;
end while;3.3. repeat loop statement
Repeat statement is used to create a loop process with conditional judgment. After each statement is executed, the conditional expression will be judged. If the expression is true, the loop ends; Otherwise, repeat the statements in the loop. The syntax format is as follows:
[label:] repeat
statement_list;
until condition
end repeat [label];
Example of repeat statement: when the id value is less than or equal to 10, the loop process will be executed repeatedly. The code is as follows:
declare id int default 0;
repeat
set id:=id+1;
until id>=10 #If the conditional expression is false, execution continues
end repeat;
3.4 leave and iterate
1. leave statement is used to end the whole loop, which is equivalent to break statements in other programming languages. The specific syntax format is as follows:
leave label;
The above statement means to end the loop referred to by the whole label.
2. The iterate statement is used to end this cycle, which is equivalent to the continue statement in other programming languages. The specific syntax format is as follows:
iterate label;
These process control statements can be used not only in procedural sql, but also in embedded sql.
For example:
mysql> select sno,grade,if(grade>=60,"yes","no") as "Pass or not" from sc; +-----------+-------+--------------+ | sno | grade | Pass or not | +-----------+-------+--------------+ | 201215121 | 60 | yes | | 201215121 | 85 | yes | | 201215121 | 88 | yes | | 201215121 | 45 | no | | 201215122 | 90 | yes | | 201215122 | 56 | no | +-----------+-------+--------------+ 6 rows in set (0.00 sec)