MySQL Stored Procedure instance tutorial

MySQL Stored Procedure instance tutorial

MySQL 5.0 and later versions support stored procedures. stored procedures are consistent, efficient, secure, and structured, this section describes how PHP operates the MySQL stored procedure through examples.

Instance 261: Create a stored procedure
This is an instance for creating a stored procedure.
Video location: CD mingrisoft9? Lt;/p>

Instance description

To ensure data integrity and consistency and improve application performance, stored procedure technology is often used. MySQL versions earlier than MySQL 5.0 do not support stored procedures. With the increasingly sophisticated MySQL technology, stored procedures will be widely used in future projects. This example describes how to create a stored procedure in MySQL 5.0 and later versions.

Technical Points

A stored procedure includes a name, a list of parameters, and a set of SQL statements that can contain many SQL statements. The following is a stored procedure definition process: create procedure proc_name (in parameter integer) begindeclare variable varchar (20); if parameter = 1 thenset variable = 'mysql '; elseset variable = 'php'; end if; insert into tb (name) values (variable); end;
The creation 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 () indicate the same stored procedure name. The stored procedure name cannot be the same as the built-in function name in the MySQL database.

Stored procedure parameters are generally composed of three parts. The first part can be in, out, or inout. In indicates that parameters are passed into the stored procedure; out indicates that parameters are passed out; inout indicates that the defined parameters can be passed into the stored procedure, and can be modified by the stored procedure before being passed out, the stored procedure is an input parameter by default, so the in parameter can be omitted. The second part is the parameter name. The third part is the parameter type, which is all available field types in the MySQL database. If there are multiple parameters, the parameters can be separated by commas.

The statement block of the MySQL stored procedure starts with begin and ends with end. The statement body can contain variable declarations, control statements, and SQL query statements. Since the internal statements of a stored procedure should end with a semicolon, you should change the statement ending mark ";" to other characters before defining the stored procedure, the probability of this character appearing in the stored procedure is also low. You can use the keyword delimiter to change it. Example: mysql> delimiter //
After a stored procedure is created, you can use the following statement to delete it. The proc_name parameter indicates the name of the stored procedure. Drop procedure proc_name
Implementation Process

(1) The MySQL stored procedure is created in 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 enter the following command at the "command prompt:

Mysql-u user name-p User Password
(3) change the statement terminator to "//". The Code is as follows:

Delimiter //
(4) Select a database before creating a stored procedure. The Code is as follows:

Use Database Name
(5) create a stored procedure.
(6) call the stored procedure through the call statement.

The key technology of an instance is how to create a stored procedure for input parameters. The specific implementation code is as follows: delimiter // create procedure pro_reg (in nc varchar (50), in pwd varchar (50 ), in email varchar (50), in address varchar (50) begininsert into tb_reg (name, pwd, email, address) values (nc, pwd, email, address); end; //

"Delimiter //" is used to change the statement terminator to "//".
"In nc varchar (50 )...... In address varchar (50) "indicates the parameter to be passed into the stored procedure.

Implementation Process

(1) Use the pre-defined class mysqli in PHP to connect to the MySQL database. 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 entered by the user to the database. The Code is as follows: if ($ SQL = $ conn-> query ("call pro_reg ('". $ nc. "','". $ pwd. "','". $ email. "','". $ address. "') {echo" <SCRIPT> alert ('user registration successful! '); </SCRIPT> ";}else {echo" <SCRIPT> alert ('user registration failed! '); </SCRIPT> ";

The syntax for creating a stored procedure is described in 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, simply reading this syntax structure is certainly not enough for MySQL Stored Procedure programming. I used ms SQL server before, so the following records my familiarity with the MySQL stored procedure are also a major difference between ms SQL SERVER and MySQL.

 

The first step is to write a Hello Word stored procedure as follows:

Create procedure phelloword ()

BEGIN

SELECT 'Hello Word! 'As F;

END;

 

Copy the statement used to create the phelloword stored procedure to phpMyAdmin and run the command. The following error is returned:

#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 have been entangled for a long time in this issue. The execution in MySQL command line tool is also not successful, but according to the prompt information, we can know that the execution in SELECT 'Hello Word! 'As F; ends, followed by END; not executed, which obviously leads to errors.

Select a separator. The syntax is as follows: DELIMITER //

The Delimiter is the symbol that notifies the MySQL client that the input is complete. ";" Is always used, but not in the stored procedure, because many statements in the Stored Procedure require semicolons.

Therefore, the above stored procedure is changed:

Create procedure ptest ()

BEGIN

SELECT 'Hello Word! 'As F;

END //

 

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

 

Step 2: Write a complete stored procedure that includes parameters, variables, variable assignment, condition judgment, UPDATE statements, and SELECT returned 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 talk about is the syntax for assigning values to variables. MySQL uses 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; assign a value to the variable.

 

The second is the syntax structure of condition judgment, as shown below:

IF... THEN

...;

ELSE

IF... THEN

...;

ELSEIF

...;

ELSE

...;

End if;

End if;

 

Finally, let's talk about the use of LEAVE syntax. When a certain condition is met and the following SQL statement is not executed, the RETURN syntax is used in ms SQL SERVER. in MySQL, the corresponding keyword is not found, however, the LEAVE syntax can be used to meet the requirements. Define a label before the BEGIN of the stored procedure, for example, "LABEL_PROC:", and then run "LEAVE LABEL_PROC" where the RETURN is interrupted;.

 

Step 3: Create a stored procedure for executing dynamic SQL statements.

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 //

 

Create, delete, and call mysql stored procedures and other common commands

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 basic information about all stored procedures in the database, including the database, stored procedure name, and creation time.
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. If no position exists, 0 is returned.
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) // remove the specified character FROM the specified position
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