MySQL Stored procedure Instance tutorial

MySQL Stored procedure Instance tutorial

The MySQL 5.0 version begins to support stored procedures with consistency, efficiency, security, and architecture, and this section explains how PHP manipulates MySQL stored procedures with specific examples.

Instance 261: Creation of stored procedures
This is an instance of creating a stored procedure
Video Location: cd mingrisoft9?lt;/p>

Example description

In order to ensure the integrality and consistency of the data and improve the performance of the application, stored procedure technology is often used. MySQL 5.0 version does not support the stored procedures, as the MySQL technology is becoming more and more sophisticated, stored procedures will be widely used in future projects. This example will introduce the creation of a stored procedure in a later version of MySQL 5.0.

Technical points

A stored procedure includes a name, a list of arguments, and a set of SQL statements that can include many SQL statements. The following defines a procedure for a stored procedure: CREATE PROCEDURE Proc_name (in parameter integer) begindeclare variable varchar; if parameter=1 Thenset variable= ' MySQL '; elseset variable= ' PHP '; end If;insert into TB (name) values (variable);
The establishment of a stored procedure in MySQL starts with the keyword CREATE PROCEDURE, followed by the name and parameters of the stored procedure. MySQL stored procedure names are case-insensitive, for example, PROCE1 () and Proce1 () represent the same stored procedure name. The stored procedure name cannot be the same as the built-in function in the MySQL database.

The parameters of a stored procedure are generally composed of 3 parts. The first part can be in, out, or inout. In indicates that parameters are passed in to a stored procedure, out represents an outgoing parameter, inout represents a defined parameter that can be passed in to a stored procedure, and can be modified by a stored procedure, and the stored procedure defaults to an incoming parameter, so parameter in can be omitted. The second part is the name of the parameter. The third part is the type of the parameter, which is the type of all the fields available in the MySQL database, and can be separated by commas if there are multiple parameters.

The statement block of the MySQL stored procedure begins with the beginning and ends with end. The statement body can contain declarations of variables, control statements, SQL query statements, and so on. Because the internal statement of the stored procedure ends with a semicolon, the statement end flag ";" should be used before the stored procedure is defined. Change to a different character, and the chance that the character will appear in the stored procedure should be lower and can be changed with the keyword delimiter. For example: Mysql>delimiter//
After the stored procedure is created, it can be deleted with the following statement, which refers to the stored procedure name (proc_name). drop procedure Proc_name
Implementation process

(1) The MySQL stored procedure is created under the command prompt, so you should first open the Command Prompt window.
(2) After entering the command prompt window, you should first log on to the MySQL database server, and at the command prompt, enter the following command:

Mysql–u User name –p user password
(3) Change the statement closing symbol, this instance changes the statement terminator to "//." The code is as follows:

Delimiter//
(4) A database should be selected before the stored procedure is created. The code is as follows:

Use database name
(5) Create a stored procedure.
(6) Invoking the stored procedure through the call statement.

The key technology of an instance is how to create a stored procedure for incoming parameters, which is implemented as follows: Delimiter//create procedure Pro_reg (in NC varchar (s), in pwd varchar (x), in email Varc Har (at address varchar) Begininsert to Tb_reg (name, pwd, email, address) VALUES (NC, PWD, email, address); end;//

The "delimiter//" function is to change the statement terminator to "//".
"In NC varchar ... in address varchar (50)" indicates the parameters to be passed in to the stored procedure.

Implementation process

(1) Through the PHP predefined class mysqli, realizes with the MySQL database connection. The code is as follows: $conn =new mysqli ("localhost", "root", "root", "db_database09"); $conn->query ("Set names gb2312");

(2) Call the stored procedure Pro_reg to save the registration information of the user input to the database. The code is as follows: if ($sql = $conn->query ("Call Pro_reg", "", "", $pwd. "', '". $email. "', '". $address. ")) {echo "<script>alert (' User registration succeeded! '); </SCRIPT> ";} Else{echo "<script>alert (' User registration failed! '); </SCRIPT> ";

A description of the syntax for creating stored procedures in the MySQL 5.0 reference manual:

CREATE
[definer = {User | Current_User}]
PROCEDURE sp_name ([proc_parameter[,...]])
[Characteristic ...] Routine_body

Proc_parameter:
[In | Out | INOUT] Param_name Type

Type
Any valid MySQL data type

Characteristic:
LANGUAGE SQL
| [NOT] Deterministic
| {CONTAINS SQL | NO SQL | Reads SQL DATA | Modifies SQL DATA}
| SQL Security {Definer | Invoker}
| COMMENT ' String '

Routine_body:
Valid SQL PROCEDURE Statement

If you are not familiar with MySQL, just look at this syntax structure is certainly not enough for MySQL stored procedure programming. I used to basically use MS SQL Server, so the following records the process of my familiarity with MySQL stored procedures, and also highlights the difference between MS SQL Server and MySQL.

The first step, of course, is to write a hello Word stored procedure, as follows:

CREATE PROCEDURE Phelloword ()

BEGIN

SELECT ' Hello word! ' As F;

End;

The statement that creates the Phelloword stored procedure above is executed in phpMyAdmin, and the following error is reported:

#1064-you have an error in your SQL syntax; Check the manual that corresponds to your MySQL server version for the right syntax to use near ' at line 3

I've been obsessing about this for a long time, and it's not as successful in MySQL's command-line tools, but it's possible to know the execution in SELECT ' Hello word! ' As F; ends, trailing end; This will obviously result in an error.

Here you need to select a delimiter, the syntax is as follows: DELIMITER//

The separator is a symbol that notifies the MySQL client that the completion has been entered. Always use ";", but not in stored procedures, because many of the statements in the stored procedure need to use a semicolon.

Therefore, the above stored procedure should read:

CREATE PROCEDURE ptest ()

BEGIN

SELECT ' Hello word! ' As F;

End//

In addition, when executing in phpMyAdmin, fill in the Delimiter text box//, the stored procedure can be created successfully.

The second step is to write a complete stored procedure that includes parameters, variables, variable assignments, conditional judgments, UPDATE statements, and select returns the result set, as follows:

CREATE PROCEDURE Plogin

(

P_username Char (15),

P_password Char (32),

P_ip Char (18),

P_logintime datetime

)

Label_proc:

BEGIN

DECLARE V_uid Mediumint (8);

DECLARE V_realpassword Char (32);

DECLARE v_nickname varchar (30);

DECLARE v_oltime smallint (6);

SELECT U.uid, U.password, F.nickname, u.oltime into V_uid, V_realpassword, V_nickname, V_oltime

From Cdb_members u INNER JOIN cdb_memberfields f on f.uid = U.uid WHERE u.username = p_username;

IF (V_uid is NULL) THEN

SELECT 2 as ErrorCode;

LEAVE Label_proc;

End IF;

IF (P_password <> V_realpassword) THEN

SELECT 3 as ErrorCode;

LEAVE Label_proc;

End IF;

UPDATE ipsp_userexpands SET lastloginip = p_ip, lastlogintime = p_logintime WHERE uid = v_uid;

SELECT 0 as ErrorCode, v_uid as UID, v_nickname as nickname, V_oltime as Oltime;

End Label_proc//

The first thing to say is the syntax for assigning values to variables, using select U.uid in MySQL, U.password, F.nickname, u.oltime into V_uid, V_realpassword, V_nickname, V_oltime From Cdb_members u INNER JOIN cdb_memberfields f on f.uid = U.uid WHERE u.username = p_username; this way, assign a value to a variable.

The second is the grammatical structure of conditional judgments, as follows:

IF ... THEN

...;

ELSE

IF ... THEN

...;

ELSEIF

...;

ELSE

...;

End IF;

End IF;

Finally, the use of leave grammar. When certain conditions are met, the return syntax is used in MS SQL server when you do not continue with the following SQL, and I do not find the corresponding keyword in mysql, but here you can use the leave syntax to satisfy the requirement and define a label before the begin of the stored procedure, such as: " Label_proc: "Then you need to use the place where return is interrupted to execute" LEAVE Label_proc. Can.

The third step is to create a stored procedure that executes dynamic SQL.

CREATE PROCEDURE Ipsp_getresourcedir

(

P_hashcode Char (40)

)

Label_proc:

BEGIN

DECLARE v_sql varchar (200);

SET v_sql = CONCAT (' SELECT filedir from ipsp_resources WHERE hashcode = ', P_hashcode, ' LIMIT 0, 1 ');

SET @sql = V_sql;

PREPARE SL from @sql;

EXECUTE SL;

DEALLOCATE PREPARE SL;

End Label_proc//

MySQL stored procedure creation, deletion, invocation and other common commands

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 basic information about stored procedures stored in the database, including the owning database, stored procedure name, creation time, and so on
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)//return substring for the first time in string , no presence returned 0
LCASE (string2)//Convert lowercase
Left (string2, length)//to a length character
Length (string)//st Ring length
Load_file (file_name)/read content from File
LOCATE (substring, string  [, Start_position]) is the same as InStr, but you can specify the start position
Lpad (string2, length, pad)///repeat pad at beginning of string until length
LTRIM (string2)//Remove front space
REPEAT (string2, Count)/ /Repeat Count times
Replace (str, SEARCH_STR, REPLACE_STR)////replacement with REPLACE_STR in str search_str
Rpad (string2, length, pad)//In After STR is supplemented with pad until length is
RTRIM (string2)//Remove back-end space
STRCMP (string1, string2)//character comparison two string sizes,
SUBSTRING (str, POS ition  [, Length])//starts with the position of STR, takes the length character,
Note: When processing a string in MySQL, the default first character is subscript 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 the specified character from the specified position
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