Mysql Stored Procedure learning notes-create a simple stored procedure, mysql Stored Procedure

Mysql Stored Procedure learning notes-create a simple stored procedure, mysql Stored Procedure

I. Stored Procedure

Stored Procedures (Stored Procedure) are a set of SQL statements for specific functions in large database systems. They are compiled and Stored in the database.
You can run a stored procedure by specifying its name and giving a parameter (if the stored procedure has a parameter. While SQL statements are executed in Common Database operating languages
Compilation and execution are required first, so the execution efficiency is not as high as that of the stored procedure.

The advantages of stored procedures are as follows:

Reuse. Stored procedures can be reused to reduce the workload of database developers. Improve performance. The stored procedure is compiled during creation and will not be re-translated for future use. A general SQL statement needs to be compiled every time it is executed, so the efficiency is improved by using the stored procedure. Reduce network traffic. The stored procedure is stored on the server. You only need to pass the name and parameters of the stored procedure when calling the procedure. This reduces the amount of data transmitted over the network. Security. Parameterized stored procedures can prevent SQL injection attacks and apply Grant, Deny, and Revoke permissions to stored procedures.

Simple syntax of the stored procedure:

Create procedure stored PROCEDURE name (input/output type variable name type, input/output type variable name type) BEGIN -- Declaration, add, delete, modify, and query operations to be completed by the statement... END

Ii. Instances

The stored procedure in this example uses mysql as an example.
The table structure is as follows:

DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

1. Stored Procedures with only IN (input parameters)

Indicates that the value of this parameter must be specified when the stored procedure is called. Modifying the value of this parameter in the stored procedure cannot be returned, which is the default value.

DROP PROCEDURE IF EXISTS proc_person_findById;

-- Create a stored procedure
Create procedure proc_person_findById (
In n int
)
BEGIN
SELECT * FROM person where id = n;
END

-- Define variables
SET @ n = 2;
-- Call a stored procedure
CALL proc_person_findById (@ n );

The call result is as follows:

2. Stored Procedures with only OUT parameters

This value can be changed within the stored procedure and can be returned.

DROP PROCEDURE IF EXISTS proc_person_getCount

-- Create a stored procedure
Create procedure proc_person_getCount (
Out n int (11)
)
BEGIN
Select count (*) INTO n FROM person;
END

-- Call a stored procedure
CALL proc_person_getCount (@ n );
SELECT @ n as 'Total ';

The call result is as follows:

 

3. With IN (input parameter) and OUT (output parameter)

Can be changed and returned.

DROP PROCEDURE IF EXISTS proc_person_findInfoById; 

-- Create a stored procedure
Create procedure proc_person_findInfoById (
IN n INT (11 ),
OUT pusername VARCHAR (255 ),
OUT page INT (11)
)
BEGIN
SELECT username, age INTO pusername, page FROM person WHERE id = n;
END

-- Define variables
SET @ id = 2;
-- Call a stored procedure
CALL proc_person_findInfoById (@ id, @ username, @ age );
SELECT @ username as 'username', @ age ';

The call result is as follows:

　　

4. Stored Procedures with INOUT Parameters

-- Input and output drop procedure if exists proc_person_get_age; -- create procedure proc_person_get_age (INOUT n INT (11) begin select age into n from person WHERE id = n; ENDSET @ id = 1; CALL proc_person_get_age (@ id); SELECT @ id;

The call result is as follows:

 

5. Input and Output Parameters

The IN parameter is input. If the IN parameter is not added, the input parameter is used by default. OUT is the output. When defining parameters, it must be added. INOUT is input and output and must be added. This parameter can be input or output after processing.

How does mysql CREATE a stored procedure?

1. Log On with the mysql client
2. Select a database
Mysql> use test
3. query the stored procedures of the current database.
Mysql> show procedure status where Db = 'test'
4. Create a simple Stored Procedure
Mysql> create procedure hi () select 'hello ';
5. After the storage process is created, see how to call it.
Mysql> call hi ();
Display Results mysql> call hi ();
+ ------- +
| Hello |
+ ------- +
| Hello |
+ ------- +
1 row in set (0.00 sec) Query OK, 0 rows affected (0.01 sec)
6. A simple storage process is successful.

Usage of stored procedures, triggers, and views in mysql

Create a stored procedure

Create procedure, Create function

Below are their formats:
Create proceduresp_Name ([proc_parameter])
Routine_body

Here, the parameter type can be in out inoutt, which means the same as the word, and IN indicates the passed parameter,
"OUT" indicates the outgoing parameters, and "INOUT" indicates the parameters passed in but finally returned.
Create functionsp_Name ([func_parameter])
Returns type

Routine_body
The Returns type specifies the returned type. The given type is the same as the returned type. Otherwise, an error is returned.

The following are two simple examples:

1. display the current Mysql version

Execution result
Mysql> use welefen;
Database changed
Mysql> delimiter // # define // as the end mark
Mysql> create procedure getversion (out param1 varchar (50) # param1 is the outgoing Parameter
-> Begin
-> Select version () into param1; # assign version information to param1
-> End
-> //
Query OK, 0 rows affected (0.00 sec)

Mysql> call getversion (@ a); # call the getversion () Stored Procedure
-> //
Query OK, 0 rows affected (0.00 sec)

Mysql> select @;
-> //
+ -------------------------- +
| @ A |
+ -------------------------- +
| 5.1.14-beta-community-nt |
+ -------------------------- +
1 row in set (0.00 sec)

2. display "hello world"

Execution result
Mysql> delimiter //
Mysql> create function display (w varchar (20) returns varchar (50)
-> Begin
-> Return concat ('hello', w );
-> End
... The remaining full text>