Stored Procedure and cursor
Core:# Variables must be declared at the beginning
Declare Nomorerows bool Default False;
Declare _ Id Int ;
Declare _ Name Varchar ( 25 );
# Declare a cursor
Declare Xxrows Cursor For Select * From XX;
# Set nomorerows to true when the cursor moves to the last empty row
Declare Continue Handler For Not Found Set Nomorerows = True;
# Open a cursor
Open Xxrows;
# Creating a temporary memory table
Drop Table If Exists Xxtem;
Create Temporary Table Xxtem
(ID Int , Name Varchar ( 25 ), Primary Key (ID ))
Engine = Memory;
Truncate Table Xxtem;
# Traversal cursor
Repeat
# Retrieving data to variables
Fetch Xxrows Into _ Id, _ name;
# Filter out duplicate rows and insert the cursor results to the temporary table
If Not Exists ( Select 1 From XxtemWhere ID = _ Id) Then
Insert Into Xxtem (ID, name) Values (_ Id, _ name );
End If ;
Until nomorerows = 1
End Repeat;
# Closing a cursor
Close Xxrows;
# Cropping a temporary table based on parameters
Select * From Xxtem limit 'skip ',' Count ';
Complete:Complete code
Create Table 'Xx '(
'Id' Int ( 11 ) Not Null ,
'Name' Varchar ( 45 ) Null Default Null ,
Primary Key ('Id ')
)
Collate = ' Utf8_general_ci '
Engine = InnoDB;
Create Definer = 'Root' @ 'localhost' Procedure 'Proc1 '( In 'Skip' Int , In ' Count ' Int )
Language SQL
Not Deterministic
Contains SQL
SQL Security definer
Comment''
Begin
# Variables must be declared at the beginning
Declare Nomorerows bool Default False;
Declare _ Id Int ;
Declare _ Name Varchar ( 25 );
# Declare a cursor
Declare XxrowsCursor For Select * From XX;
# Set nomorerows to true when the cursor moves to the last empty row
Declare Continue Handler For Not Found Set Nomorerows = True;
# Open a cursor
Open Xxrows;
# Creating a temporary memory table
Drop Table If Exists Xxtem;
Create Temporary Table Xxtem
(ID Int , Name Varchar ( 25 ), Primary Key (ID ))
Engine= Memory;
Truncate Table Xxtem;
# Traversal cursor
Repeat
# Retrieving data to variables
Fetch Xxrows Into _ Id, _ name;
# Filter out duplicate rows and insert the cursor results to the temporary table
If Not Exists ( Select 1 From Xxtem Where ID = _ Id) Then
Insert Into Xxtem (ID, name) Values (_ Id, _ name );
End If ;
Until nomorerows = 1
End Repeat;
# Closing a cursor
Close Xxrows;
# Cropping a temporary table based on parameters
Select * From Xxtem limit 'skip ',' Count ';
End
Dynamic SQL: Random query of several rows in a table
Below is the Stored Procedure Code . The process contains three parameters: Table Name, row number, and primary key ID. Only tables with single-Primary keys of the int type are supported. The primary key can be omitted as null. In this way, the primary key is automatically searched, but this is slow. Rand_data
1 Create Definer= 'Root '@' % ' Procedure 'Rand _ data '( In 'Tbname' Varchar ( 50 ), In 'Rowcnt' Int , In 'Tbkey' Varchar ( 50 ))
2 Language SQL
3 Not Deterministic
4 Contains SQL
5 SQL Security definer
6 Comment ' Random acquisition of several records, only applicable to a single primary key table '
7 Begin
8
9 # Obtain the primary key name
10 If Tbkey Is Not Null Then
11 Set @ Tbkey = Tbkey; # The parameter already exists. This situation is faster.
12 Else
13 # No In the parameter. It is time consuming to query the primary key from the system table.
14 Select @ Tbkey : = C. column_name
15 From
16 Information_schema.table_constraints As T,
17 Information_schema.key_column_usage As C
18 Where
19 T. table_name = C. table_name
20 And T. table_schema = C. table_schema
21 And T. table_schema = Database ()
22 And T. table_name = Tbname
23 And T. constraint_type = ' Primary Key ' ;
24 End If ;
25
26 # Obtain the maximum ID, minimum ID, and number of records
27 Set @ Getmaxidsql = Concat ( ' Select @ maxid: = max ( ' , @ Tbkey , ' ), ' ,
28 ' @ Minid: = min ( ' , @ Tbkey , ' ), ' ,
29 ' @ Totalcnt: = count ( ' , @ Tbkey , ' ) ' ,
30 ' From' ' , Tbname, ' '; ' );
31 Prepare Getmaxid From @ Getmaxidsql ;
32 Execute Getmaxid;
33 Deallocate Prepare Getmaxid;
34
35 # Creating a temporary table
36 Drop Table If Exists Rand_tt;
37 Set @ Temtbsql = Concat ( ' Create temporary table rand_tt select 0 aid, TB. * from' ' , Tbname, ' 'Tb limit 0; ' );
38 Prepare Temtb From @ Temtbsql ;
39 Execute Temtb;
40 Deallocate Prepare Temtb;
41
42 # Construct an SQL statement to obtain a record
43 Set @ Randrowsql = Concat ( ' Insert into rand_tt select @ CNT: = @ CNT + 1 aid, TB. * from ' ,
44 Tbname, ' TB where TB. ' , @ Tbkey , ' = ?; ' );
45 Prepare Addrow From @ Randrowsql ;
46
47 # Generate random records
48 Set @ CNT = 0 ;
49 Insertloop: loop
50 Set @ ID = Floor ( Rand () * ( @ Maxid - @ Minid ) + @ Minid );
51 If Not Exists ( Select ID From Rand_tt Where ID = @ ID ) Then
52 Execute Addrow using @ ID ;
53 If @ CNT > = Rowcnt Or @ CNT > = @ Totalcnt Then
54 Leave insertloop;
55 End If ;
56 End If ;
57 End Loop insertloop;
58 Deallocate Prepare Addrow;
59
60 # Returned data
61 Alter Table Rand_ttDrop Column Aid;
62 Select * From Rand_tt;
63 End