Create a stored procedure for MySQL

Create a stored procedure for MySQL
"Pr_add" is a simple MySQL stored procedure, which has two int-type input parameters.

"A" and "B" return the sum of the two parameters.

Drop procedure if exists pr_add;

-- Calculate the sum of two numbers

Create procedure pr_add
(
A int,
B int
)
Begin
Declare c int;

If a is null then
Set a = 0;
End if;

If B is null then
Set B = 0;
End if;

Set c = a + B;

Select c as sum;

/*
Return c;-it cannot be used in MySQL stored procedures. Return can only appear in functions.
/
End;
Ii. Call the MySQL Stored Procedure
Call pr_add (10, 20 );
Execute the MySQL stored procedure. The stored procedure parameter is a MySQL user variable.

Set @ a = 10;
Set @ B = 20;

Call pr_add (@ a, @ B );
Iii. MySQL stored procedure features
The simple syntax for creating a MySQL stored procedure is:

Create procedure stored procedure name ()
(
[In | out | inout] parameter datatype
)
Begin
MySQL statement;
End;
If "in", "out", and "inout" are not explicitly specified for MySQL stored procedure parameters, the default value is "in ".

Traditionally, we will not explicitly specify the "in" parameter.

1. The "()" after the MySQL stored procedure name is required. Even if there is no parameter, "()" is required.

2. For MySQL stored procedure parameters, you cannot add "@" before the parameter name, for example, "@ a int ". The following creates a storage

The stored procedure syntax is incorrect in MySQL (correct in SQL Server ). MySQL Stored Procedure

You do not need to add "@" before the variable name, although the MySQL client user variable must add "@".

Create procedure pr_add
(
@ A int,-Error
B int-correct
)
3. The default value cannot be specified for MySQL stored procedure parameters.

4. You do not need to add "as" before procedure body to the MySQL stored procedure ". SQL Server stores

The "as" keyword must be added to the process.

Create procedure pr_add
(
A int,
B int
)
As-error. MySQL does not need ""
Begin
Mysql statement ...;
End;
5. If the MySQL Stored Procedure contains multiple MySQL statements, the begin end keyword is required.

Create procedure pr_add
(
A int,
B int
)
Begin
Mysql statement 1 ...;
Mysql statement 2 ...;
End;
6. Add a semicolon (;) to the end of each statement in the MySQL stored procedure.

...

Declare c int;

If a is null then
Set a = 0;
End if;

...
End;
7. Notes in the MySQL stored procedure.

/*
This is
Comment on multiple MySQL lines.
/

Declare c int;-this is a single-line MySQL comment (Note that at least one space is required after)

If a is null then this is also a single-row MySQL comment
Set a = 0;
End if;

...
End;
8. The "return" keyword cannot be used in MySQL stored procedures.

Set c = a + B;

Select c as sum;

/*
Return c;-it cannot be used in MySQL stored procedures. Return can only appear in functions.
/
End;
9. when calling the MySQL stored procedure, you need to add "()" after the process name, even if there is no parameter

"()" Is required

Call pr_no_param ();
10. because MySQL stored procedure parameters do not have default values, you cannot save

Parameter. It can be replaced by null.

Call pr_add (10, null );
Mysql 5.0 Stored Procedure learning Summary
1. Create a stored procedure

1. Basic Syntax:

Create procedure sp_name ()
Begin
.........
End
2. parameter transfer

Ii. Call the Stored Procedure

1. Basic Syntax: call sp_name ()
Note: The stored procedure name must be enclosed in parentheses, even if the stored procedure has no parameters
Iii. delete stored procedures

1. Basic Syntax:
Drop procedure sp_name //
2. Notes
(1) You cannot delete another stored procedure in one stored procedure. You can only call another stored procedure.
4. blocks, conditions, and loops

1. Block definition, commonly used
Begin
......
End;
You can also create an alias for the block, such:
Lable: begin
...........
End lable;
You can use leave lable to jump out of the block and execute code after the block.
2. conditional statements

If condition then
Statement
Else
Statement
End if;
3. Loop statements
(1). while Loop

[Label:] WHILE expression DO

Statements

End while [label];

(2) loop

[Label:] LOOP

Statements

End loop [label];

(3). repeat until Loop

[Label:] REPEAT

Statements

UNTIL expression

End repeat [label];

5. Other Common commands

1. show procedure status
Displays the basic information about all stored procedures in the database, including the database, stored procedure name, and

Inter
2. show create procedure sp_name
Displays detailed information about a stored procedure.

Operators used in mysql stored procedures

Mysql Stored Procedure learning Summary-Operators
Arithmetic Operators

+ Add SET var1 = 2 + 2; 4
-Subtract SET var2 = 3-2; 1
* Multiply by SET var3 = 3*2; 6
/Except SET var4 = 10/3; 3.3333
DIV Division SET var5 = 10 DIV 3; 3
% Modulo SET var6 = 10% 3; 1

Comparison Operators

> Greater than 1> 2 False
<Less than 2 <1 False
<= Less than or equal to 2 <= 2 True
>=Greater than or equal to 3> = 2 True
BETWEEN is 5 BETWEEN two values BETWEEN 1 AND 10 True
Not between two values 5 not between 1 AND 10 False
IN the set, 5 IN (,) is False.
Not in is not in the set 5 not in (,) True
= Equal to 2 = 3 False
<> ,! = Not equal to 2 <> 3 False
<=> Strictly compare whether two NULL values are equal NULL <=> NULL True
LIKE simple pattern matching "Guy Harrison" LIKE "Guy %" True
REGEXP regular expression match "Guy Harrison" REGEXP "[Gg] reg" False
Is null 0 is null False
Is not null 0 is not null True
Logical operators

AND)

 

 

 

 

 

AND
TRUE
FALSE
NULL
 
TRUE
TRUE
FALSE
NULL
 
FALSE
FALSE
FALSE
NULL
 
NULL
NULL
NULL
NULL
 

OR (OR)

 

OR
TRUE
FALSE
NULL
 
TRUE
TRUE
TRUE
TRUE
 
FALSE
TRUE
FALSE
NULL
 
NULL
TRUE
NULL
NULL
 

Exclusive or (XOR)

 

XOR
TRUE
FALSE
NULL
 
TRUE
FALSE
TRUE
NULL
 
FALSE
TRUE
FALSE
NULL
 
NULL
NULL
NULL
NULL
 

Bitwise operators

| Bit or
& Bit and
<Left shift
> Right shift
~ Bitwise non (single object operation, bitwise inversion)

 

Common functions in mysq stored procedures, string operations, mathematics, and date and time.

Mysql stored procedure basic functions
I. string type

CHARSET (str) // returns the string Character Set
CONCAT (string2 [,...]) // connection string
INSTR (string, substring) // returns the position of the first occurrence of the substring in the string.

Back to 0
LCASE (string2) // converts it to lowercase
LEFT (string2, length) // take the length from the LEFT of string2
LENGTH (string) // string LENGTH
LOAD_FILE (file_name) // read content from the file
LOCATE (substring, string [, start_position]) is the same as INSTR, but the start position can be specified.
LPAD (string2, length, pad) // repeat pad to start with string until the string length is length
LTRIM (string2) // remove leading Spaces
REPEAT (string2, count) // REPEAT count times
REPLACE (str, search_str, replace_str) // REPLACE search_str with replace_str in str
RPAD (string2, length, pad) // use pad after str until the length is length.
RTRIM (string2) // remove backend Spaces
STRCMP (string1, string2) // compare the size of two strings by character,
SUBSTRING (str, position [, length]) // starts from the position of str and takes length characters,
Note: When processing strings in mysql, the default subscript of the first character is 1, that is, the parameter position must be greater than or equal to 1.

Mysql> select substring ('abcd );
+ -------- +
| Substring ('abcd',) |
+ -------- +
|
+ -------- +
1 row in set (0.00 sec)

Mysql> select substring ('abcd', 1, 2 );
+ -------- +
| Substring ('abcd', 1, 2) |
+ -------- +
| AB |
+ -------- +
1 row in set (0.02 sec)
TRIM ([[BOTH | LEADING | TRAILING] [padding] FROM] string2) // removes the specified word at the specified position

Character
UCASE (string2) // converts to uppercase
RIGHT (string2, length) // gets the last length character of string2
SPACE (count) // generate count Spaces

Ii. Mathematics

ABS (number2) // absolute value
BIN (decimal_number) // convert decimal to binary
CEILING (number2) // rounded up
CONV (number2, from_base, to_base) // hexadecimal conversion
FLOOR (number2) // round down
FORMAT (number, decimal_places) // number of reserved decimal places
HEX (DecimalNumber) // convert to hexadecimal
Note: HEX () can input a string, returns its ASC-11 code, such as HEX ('def ') returns 4142143
You can also input a decimal integer to return its hexadecimal encoding. For example, HEX (25) returns 19.
LEAST (number, number2 [,...]) // calculates the minimum value.
MOD (numerator, denominator) // evaluate the remainder
POWER (number, power) // Exponent
RAND ([seed]) // Random Number
ROUND (number [, decimals]) // rounding, decimals is the number of decimal places]

Note: The return type is not an integer, for example:
(1) The default value is integer.
Mysql> select round (1.23 );
+ ----- +
| Round (1.23) |
+ ----- +
| 1 |
+ ----- +
1 row in set (0.00 sec)

Mysql> select round (1.56 );
+ ----- +
| Round (1.56) |
+ ----- +
| 2 |
+ ----- +
1 row in set (0.00 sec)

(2) the number of decimal places can be set to return floating point data.
Mysql> select round (1.567, 2 );
+ ------ +
| Round (1.567, 2) |
+ ------ +
| 1, 1.57 |
+ ------ +
1 row in set (0.00 sec)

SIGN (number2) // return SIGN, positive and negative or 0
SQRT (number2) // Square

 
Iii. Date and Time
 

ADDTIME (date2, time_interval) // Add time_interval to date2
CONVERT_TZ (datetime2, fromTZ, toTZ) // convert the time zone
CURRENT_DATE () // current date
CURRENT_TIME () // current time
CURRENT_TIMESTAMP () // current Timestamp
DATE (datetime) // return the DATE part of datetime
DATE_ADD (date2, INTERVAL d_value d_type) // Add a date or time in date2
DATE_FORMAT (datetime, FormatCodes) // display datetime in formatcodes format
DATE_SUB (date2, INTERVAL d_value d_type) // subtract a time from date2
DATEDIFF (date1, date2) // two date differences
DAY (date) // returns the DAY of the date
DAYNAME (date) // english week
DAYOFWEEK (date) // Week (1-7), 1 is Sunday
DAYOFYEAR (date) // The day of the year
EXTRACT (interval_name FROM date) // EXTRACT the specified part of the date FROM date
MAKEDATE (year, day) // specifies the day of the year and year to generate a date string.
MAKETIME (hour, minute, second) // generate a time string
MONTHNAME (date) // name of the English month
NOW () // current time
SEC_TO_TIME (seconds) // converts seconds to time
STR_TO_DATE (string, format) // convert string to time, which is displayed in format
TIMEDIFF (datetime1, datetime2) // two time difference
TIME_TO_SEC (time) // time to seconds]
WEEK (date_time [, start_of_week]) // WEEK
YEAR (datetime) // YEAR
DAYOFMONTH (datetime) // The day of the month
HOUR (datetime) // HOUR
LAST_DAY (date) // the last date of the Month of date
MICROSECOND (datetime) // MICROSECOND
MONTH (datetime) // MONTH
MINUTE (datetime) // MINUTE
 

Appendix: available types in INTERVAL
DAY, DAY_HOUR, DAY_MINUTE, DAY_SECOND, HOUR, HOUR_MINUTE, HOUR_SECOND

MySQL Stored Procedure examples, including transactions, parameters, nested calls, cursors, loops, etc.

View plaincopy to clipboardprint?
Drop procedure if exists pro_rep_shadow_rs;
Delimiter |
----------------------------------
-- Rep_shadow_rs
-- Used to process information addition, update, and deletion
-- Only data that has not been updated since the last time is updated each time.
-- Based on Different flag Spaces
-- An output parameter is required,
-- If the return value is 0, the call fails and the transaction is rolled back.
-- If the return value is 1, the call is successful and the transaction is committed.
--
-- Test Method
-- Call pro_rep_shadow_rs (@ rtn );
-- Select @ rtn;
----------------------------------
Create procedure pro_rep_shadow_rs (out rtn int)
Begin
-- Declare variables. All declarations must be prior to non-declared statements.
Declare iLast_rep_sync_id int default-1;
Declare iMax_rep_sync_id int default-1;
-- If an exception occurs, or automatic processing and rollback, but no longer notifies the caller
-- If you want the application to get an exception, you need to set the following sentence as well as the statement for starting and committing the transaction.

Remove all
Declare exit handler for sqlexception rollback;
-- Find the last
Select eid into iLast_rep_sync_id from rep_de_proc_log where

Tbl = 'rep _ shadow_rs ';
-- If not, add a row.
If iLast_rep_sync_id =-1 then
Insert into rep_de_proc_log (rid, eid, tbl) values (0, 0, 'rep _ shadow_rs ');

 
Set iLast_rep_sync_id = 0;
End if;

-- Next Digit
Set iLast_rep_sync_id = iLast_rep_sync_id + 1;
-- Set the default return value to 0: Failed
Set rtn = 0;

-- Start the transaction
Start transaction;
-- Find the maximum number
Select max (rep_sync_id) into iMax_rep_sync_id from rep_shadow_rs;
-- New data available
If iMax_rep_sync_id> = iLast_rep_sync_id then
-- Call
Call pro_rep_shadow_rs_do (iLast_rep_sync_id, iMax_rep_sync_id );
-- Update log
Update rep_de_proc_log set

Rid = iLast_rep_sync_id, eid = iMax_rep_sync_id where tbl = 'rep _ shadow_rs ';
End if;

-- No exception occurred while running. Commit the transaction.
Commit;
-- Set the return value to 1.
Set rtn = 1;
End;
|
Delimiter;
Drop procedure if exists pro_rep_shadow_rs_do;
Delimiter |
---------------------------------
-- Process data within the specified number range
-- Two parameters are required.
-- Last_rep_sync_id is the minimum value of the number.
-- Max_rep_sync_id is the maximum number.
-- No Return Value
---------------------------------
Create procedure pro_rep_shadow_rs_do (last_rep_sync_id int, max_rep_sync_id

Int)
Begin
Declare iRep_operationtype varchar (1 );
Declare iRep_status varchar (1 );
Declare iRep_Sync_id int;
Declare iId int;
-- This is used to process the cursor reaching the last row.
Declare stop int default 0;
-- Declare a cursor
Declare cur cursor for select

Id, Rep_operationtype, iRep_status, rep_sync_id from rep_shadow_rs where

Rep_sync_id between last_rep_sync_id and max_rep_sync_id;
-- Declare the exception handling of the cursor and set a termination flag
Declare continue handler for sqlstate '200' SET stop = 1;

-- Open the cursor
Open cur;

-- Read a row of data to a variable
Fetch cur into iId, iRep_operationtype, iRep_status, iRep_Sync_id;
-- This is to determine whether the cursor has reached the end
While stop <> 1 do
-- Various judgments
If iRep_operationtype = 'I 'then
Insert into rs0811 (id, fnbm) select id, fnbm from rep_shadow_rs

Where rep_sync_id = iRep_sync_id;
Elseif iRep_operationtype = 'U' then
Begin
If iRep_status = 'A' then
Insert into rs0811 (id, fnbm) select id, fnbm from

Rep_shadow_rs where rep_sync_id = iRep_sync_id;
Elseif iRep_status = 'B' then
Delete from rs0811 where id = iId;
End if;
End;
Elseif iRep_operationtype = 'D' then
Delete from rs0811 where id = iId;
End if;

-- Read the data of the next row
Fetch cur into iId, iRep_operationtype, iRep_status, iRep_Sync_id;
End while; -- end of the loop
Close cur; -- close the cursor
End;
|