In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-04-07 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
Day06 MySQL database stored procedures and functions
An overview of stored procedures and functions:
Database stored procedures and stored functions refer to the collection of sql statements defined in the database. These sql can be directly used by calling the names of these stored procedures or stored functions directly. Can avoid developers to write a large number of sql statements to operate data, can reduce the client-side and server-side data transmission. Therefore, it is also important to learn stored procedures and stored functions well.
2. Stored procedure [procedure]
1. Create a stored procedure
Create procedure customizes a storage name () begin select statement end; call storage name (); calls stored procedures
2. Delete stored procedures
Drop procedure storage name
3. Stored procedures with parameters
3.1:in parameter, that is, input parameter
Create procedure storage name (in column name data type) begin select statement end; call storage name (value of column name)
3.2:out parameter, that is, output parameter
Create procedure storage name (out column name data type) begin select * into column name from table name; end; call storage name (@ column name)
3.3:inout parameters, that is, input and output parameters
Create procedure storage name (inout column name data type) begin select * into column name from table name; end; set @ column name = a value / / define a variable and assign a call storage name (@ column name)
4. Define variables
4.1: define local variables
Declare an int / / define set astat10; / / assign select a; / / output
4.2: define the global variable "@ as the starting character"
Set @ bread20; / / define the session variable select @ b; / / output
3. Storage function [function]
1. Create an instance of the storage function definition.
Create function function name (an int, b int) returns int begin declare c int; set return c; end; select function name (a numeric value, b numeric value) / / call storage function
Fourth, the use of the cursor
In the MySQL query database is swollen, stored procedures and stored functions can be used to read the records in the result set one by one. It mainly includes the following steps:
1. Declare the cursor:
Declare cursor name cursor select_name translation; select_name: is a select statement that returns one or more rows of data.
Example:
Declare info_student cursor for select sid,name,age from student where sid=1
2. Open the cursor
Open cursor name
3. Use the cursor
Fetch cursor name into column name
4. Close the cursor
Close cursor name
View stored procedures and functions
1. Use the show status statement to view
Show {procedure | function} status [like 'pattern'] translation: procedure: query stored procedure function: query stored function like' pattern': is used to match stored procedure or function name.
2. Use the show create statement to view
Show create {procedure | function} sp_name; translation; procedure: query stored procedure function: query stored function sp_name: indicate stored procedure or function name
3. Modify stored procedures and stored functions
Alter {procedure | function} sp_name {contains sql | no sql | reads sql data | modifies sql data} | sql security {definer | invoker} | comment 'string' translation: procedure | function: indicates whether it is a stored procedure or a stored function sp_name: indicates the name contains sql: indicates that the subroutine contains sql statements But the statement no sql that does not contain read and write data: indicates that the subroutine contains sql statements reads sql data: indicates that the subroutine contains read data modifies sql data: indicates that the subroutine contains write data definer | invoker: indicates that the permission is executed. The former means that the definer can execute it himself. The latter is that the caller can execute comment 'string': comment information
4. Delete stored procedures and stored functions
Drop {procedure | function} sp_name
VI. Concluding remarks:
At this point, stored procedures and stored functions have been learned! Digest it well!
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.