MySQL Create Stored procedures

MySQL Create Stored procedures
"Pr_add" is a simple MySQL stored procedure that has two input parameters of type int

"A", "B", returns the and 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;-cannot be used in a MySQL stored procedure. Return can only appear in the function.
/
End
Second, call the MySQL stored procedure
Call Pr_add (10, 20);
Execute the MySQL stored procedure, the stored procedure parameter is the MySQL user variable.

Set @a = 10;
Set @b = 20;

Call Pr_add (@a, @b);
The characteristics of MySQL stored procedure
The simple syntax for creating a MySQL stored procedure is:

Create procedure stored procedure name ()
(
[In|out|inout] Parameter datatype
)
Begin
MySQL statement;
End
MySQL stored procedure Parameters If you do not explicitly specify ' in ', ' Out ', ' inout ', the default is ' in '.

In practice, we do not explicitly specify the parameters that are "in".

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

2. The MySQL stored procedure parameter, cannot add "@" before the parameter name, for example: "@a int". The following creates the Save

The stored procedure syntax is incorrect in MySQL (correct in SQL Server). In the MySQL stored procedure

Variable, do not need to add "@" before the variable name, although the MySQL client user variable should be added "@".

CREATE PROCEDURE Pr_add
(
@a int,-Error
b INT-Correct
)
3. The parameters of the MySQL stored procedure cannot specify a default value.

4. The MySQL stored procedure does not need to add "as" in front of the procedure body. and SQL Server has stored

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

CREATE PROCEDURE Pr_add
(
a int,
b int
)
As-error, MySQL does not need "as"
Begin
MySQL statement ...;
End
5. If the MySQL stored procedure contains more than one MySQL statement, you need the begin end keyword.

CREATE PROCEDURE Pr_add
(
a int,
b int
)
Begin
MySQL statement 1 ...;
MySQL statement 2 ...;
End
6. The end of each statement in the MySQL stored procedure is preceded by a semicolon ";"

...

declare c int;

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

...
End
7. The annotations in the MySQL stored procedure.

/*
This is a
Multi-line MySQL annotation.
/

declare c int; -This is a single line of MySQL annotations (note-At least one space is needed)

If A is null then this is also a single line MySQL annotation
Set a = 0;
End If;

...
End
8. You cannot use the "return" keyword in a MySQL stored procedure.

Set C = a + B;

Select C as Sum;

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

Need "()"

Call Pr_no_param ();
10. Because the MySQL stored procedure parameter has no default value, it is not possible to save the MySQL stored procedure when invoking the

Slightly parameter. can be substituted with null.

Call Pr_add (of NULL);
MySQL 5.0 stored Procedure Learning Summary
I. Creating a stored Procedure

1. Basic grammar:

CREATE PROCEDURE Sp_name ()
Begin
.........
End
2. Parameter transfer

Two. Call the stored procedure

1. Basic syntax: Call Sp_name ()
Note: The stored procedure name must be followed by parentheses, even if the stored procedure has no parameter passing
Three. Delete stored procedures

1. Basic grammar:
drop procedure sp_name//
2. Matters needing attention
(1) cannot delete another stored procedure in one stored procedure, can only call another stored procedure
Four. blocks, conditions, loops

1. Block definition, commonly used
Begin
......
End
You can also alias a block, such as:
Lable:begin
...........
End lable;
You can use leave lable to jump out of blocks and execute code after blocks
2. Conditional statement

If condition Then
Statement
Else
Statement
End If;
3. Circular statements
(1). While loop

[Label:] While expression do

Statements

End while [Label];

(2). Loop loop

[Label:] LOOP

Statements

End LOOP [Label];

(3). Repeat until cycle

[Label:] REPEAT

Statements

UNTIL expression

End REPEAT [Label];

Five. Other common commands

1.show Procedure Status
Displays the basic information of stored procedures stored in the database, including the owning database, stored procedure name, when created

Rooms and other
2.show CREATE PROCEDURE Sp_name
Show details of a stored procedure

The operator to use in the MySQL stored procedure

MySQL Stored procedure Learning summary-operator
Arithmetic operators

+ Add SET var1=2+2; 4
-Minus SET var2=3-2; 1
* by SET Var3=3*2; 6
/except SET VAR4=10/3; 3.3333
div divisible SET var5=10 Div 3; 3
% modulo SET var6=10%3; 1

Comparison operators

> Greater than 1>2 False
< less than 2<1 False
<= is less than or equal to 2<=2 True
>= is greater than or equal to 3>=2 True
BETWEEN 5 BETWEEN 1 and True between two values
Not BETWEEN is not between two values 5 not BETWEEN 1 and False
In the Set 5 in (1,2,3,4) False
Not in the set 5 not in (1,2,3,4) True
= equals 2=3 False
<>!= not equal to 2<>3 False
<=> strictly compare two NULL values for equality Null<=>null True
Like simple pattern matching "guy Harrison" like "guy%" True
REGEXP regular-match "Guy Harrison" REGEXP "[Gg]reg" False
Is Null was NULL 0 is null False
is not NULL isn't NULL 0 is not NULL True
logical operators

With (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
& Position and
<< left Shift
>> Right Shift
~ Bit non (single eye operation, bitwise reverse)

MYSQ functions commonly used in stored procedures, string type operations, mathematical classes, date-time classes.

MySQL stored procedure basic function
A. String class

CHARSET (str)//return string character set
CONCAT (string2 [,...])//connection string
INSTR (string, substring)//Returns the position that substring first appeared in string, no return

Back to 0
LCASE (string2)//Convert to lowercase
Left (string2, length)//The length characters from the left-hand side of the string2
Length (string)//string lengths
Load_file (file_name)//read content from File
LOCATE (substring, string [, Start_position]) with InStr, but you can specify the start position
Lpad (string2, length, pad)//repeat pad at beginning of string until length of string
LTRIM (string2)//Remove front space
REPEAT (string2, count)//Repeat Count times
Replace (str, SEARCH_STR, REPLACE_STR)//replaced with REPLACE_STR in STR search_str
Rpad (string2, length, pad)//after STR supplemented with pad until length
RTRIM (string2)//Remove back-end spaces
STRCMP (string1, string2)//character comparison two string size,
SUBSTRING (str, position [, length])//begins with the position of STR, takes the length character,
Note: When handling strings in MySQL, the default first character subscript is 1, that is, the parameter position must be greater than or equal to 1

mysql> Select substring (' ABCD ', 0,2);
+ ——————— –+
| SUBSTRING (' ABCD ', 0,2) |
+ ——————— –+
| |
+ ——————— –+
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)//Remove specified word at specified position

Character
UCASE (string2)//Convert to uppercase
Right (String2,length)//Fetch string2 last length character
Space (count)//Generate Count spaces

Two. Mathematics class

ABS (NUMBER2)//Absolute value
BIN (Decimal_number)//decimal Turn binary
CEILING (number2)//Upward rounding
CONV (Number2,from_base,to_base)//in-process conversion
FLOOR (NUMBER2)//Down rounding
FORMAT (number,decimal_places)//Reserved Decimal places
HEX (Decimalnumber)//Hex
Note: HEX () can pass in a string, then return its ASC-11 code, such as HEX (' DEF ') return 4142143
You can also pass in a decimal integer, returning its hexadecimal encoding, such as Hex (25) Returning 19
Least (number, number2 [,..])//Minimum value
MOD (numerator, denominator)//remainder
Power (number, Power)//Index
RAND ([seed])//random number
ROUND (number [, decimals])//rounded, decimals to decimal places]

Note: The return type is not all integers, such as:
(1) Change the default to a cosmetic value
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) You can set the number of decimal places, return floating-point data
Mysql> Select round (1.567,2);
+ —————-+
| Round (1.567,2) |
+ —————-+
| 1.57 |
+ —————-+
1 row in Set (0.00 sec)

SIGN (NUMBER2)//return symbol, plus or minus 0
SQRT (NUMBER2)//Open Square

Three. Date Time class

Addtime (Date2, Time_interval)//Add Time_interval to Date2
Convert_tz (DateTime2, Fromtz, Totz)//Convert time zone
Current_date ()//Current date
Current_time ()//Current time
Current_timestamp ()//Current time stamp
Date (datetime)//Return datetime part
Date_add (Date2, INTERVAL d_value d_type)//Add date or time in Date2
Date_format (datetime, Formatcodes)//display datetime using Formatcodes format
Date_sub (Date2, INTERVAL d_value d_type)//Minus one time on date2
DATEDIFF (Date1, Date2)//Two date difference
Day (date)//days of return date
Dayname (date)/English week
DayOfWeek (date)//week (1-7), 1 for Sunday
DayOfYear (date)//day of the year
EXTRACT (interval_name from date)//To extract the specified portion of a date from
Makedate (year, day)//gives the first days of years and years, generating a date string
Maketime (hour, minute, second)//Generate time series
MonthName (date)//English month name
Now ()//Current time
Sec_to_time (seconds)//sec turns into time
Str_to_date (string, format)//String turn to time, displayed in format format
Timediff (datetime1, datetime2)//two time lag
Time_to_sec (time)//times in seconds]
WEEK (Date_time [, Start_of_week])//first few weeks
Year (datetime)/Years
DayOfMonth (DateTime)//day of the month
HOUR (DateTime)//hour
Last date of the month of Last_day (date)//date
Microsecond (datetime)/microsecond
MONTH (DateTime)//month
MINUTE (datetime)/min

Attachment: Types that can be used in interval
Day, Day_hour, Day_minute, Day_second, HOUR, Hour_minute, Hour_second

Examples of MySQL stored procedures, including transactions, parameters, nesting calls, cursors, loops, etc.

View Plaincopy to Clipboardprint?
drop procedure if exists pro_rep_shadow_rs;
Delimiter |
----------------------------------
--Rep_shadow_rs
--Used to process information additions, updates, and deletions
--Update only data that hasn't been done since last time
--According to the different logo bit
--Requires an output parameter,
--if returned to 0, the call fails and the transaction rolls back
--if returned as 1, the call succeeds and the transaction commits
--
--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 precede statements that are not declared
declare ilast_rep_sync_id int default-1;
declare imax_rep_sync_id int default-1;
--If an exception occurs, or is automatically processed and rollback, the caller is no longer notified
--If you want the application to get an exception, you need to include the following sentence, and the statement that starts the transaction and commits the transaction

Remove all
Declare exit handler for SqlException rollback;
--Find the last time
Select Eid into ilast_rep_sync_id from Rep_de_proc_log where

Tbl= ' rep_shadow_rs ';
--Add a row if it does not exist
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 Number
Set ilast_rep_sync_id=ilast_rep_sync_id+1;
--Set the default return value of 0: failed
Set rtn=0;

--Start a transaction
Start transaction;
--Find the maximum number
Select Max (rep_sync_id) into the 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;   
      
   -run no exception, COMMIT transaction   
 & nbsp;  commit;  
   --Set return value to 1 
    set rtn=1;  
end;  
|  
delimiter;  
drop procedure if exists pro_rep_shadow_rs_do;& nbsp; 
Delimiter |  
---------------------------------  
--handle data in a specified number range    
--You need to enter 2 parameters   
--last_rep_sync_id is the minimum value of the number   
--max_rep_sync_id is the maximum number &NBSP;&NB Sp
--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 handle the last line of cursor arrival
declare stop int default 0;
--Declaring 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;  
   -declares exception handling for cursors, setting a stop tag   
    declare CONTINUE HANDLER for SQLSTATE ' 02000 ' SET stop=1;  
 &nbs p;    
   --Open cursor   
    Open cur;  
      
   --reading a row of data to a variable   
    Fetch cur into iid,irep_operationtype,irep_status,irep_sync_id;  
   -- This is to determine if 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 next line of data
Fetch cur into iid,irep_operationtype,irep_status,irep_sync_id;
End while; --End of Loop
Close cur; --Close cursor
End
|