Shulou(Shulou.com)06/01 Report--

Functions, stored procedures and triggers in MySQL are not much used now, just understand it, because now database performance is the bottleneck of many systems, these operations can be done outside the database by professional programming languages such as java, to reduce the burden of the database, the database is only responsible for data storage.

Function

System functions and custom functions

System function: https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html

Custom function (user-defined function UDF)

Save in mysql.proc table

Create a UDF:

CREATE [AGGREGATE] FUNCTION function_name (parameter_name type, [parameter_name type,...])

RETURNS {STRING | INTEGER | REAL}

Runtime_body

Description:

There can be multiple parameters or no parameters

Must have one and only one return value

Custom function

View a list of functions:

SHOW FUNCTIOIN STATUS

View function definition

SHOW CREATE FUNCTION function_name

Delete UDF:

DROP FUNCTION function_name

Call custom function syntax:

SELECT function_name (parameter_value,...)

Example: no parameter UDF

CREATE FUNCTION simpleFun () RETURNS VARCHAR (20) RETURN "Hello World!"

Example: UDF with parameter

DELIMITER / /

CREATE FUNCTION deleteById (uid SMALLINT UNSIGNED) RETURNS VARCHAR (20)

BEGIN

DELETE FROM students WHERE stuid = uid

RETURN (SELECT COUNT (uid) FROM students)

END//

DELIMITER

Define local variable syntax in a custom function:

DECLARE variable 1 [, variable 2J.] variable type [DEFAULT default]

Description: the scope of local variables is in BEGIN...END programs, and the statement that defines local variables must be defined on the first line of BEGIN...END.

Example:

DELIMITER / /

CREATE FUNCTION addTwoNumber (x SMALLINT UNSIGNED, Y SMALLINT UNSIGNED)

RETURNS SMALLINT

BEGIN

DECLARE a, b SMALLINT UNSIGNED DEFAULT 10

SET a = x, b = y

RETURN aquib

END//

Syntax for assigning values to variables

SET parameter_name = value [, parameter_name = value...]

SELECT INTO parameter_name

Example:

...

DECLARE x int

SELECT COUNT (id) FROM tdb_name INTO x

RETURN x

END//

Stored procedure

Stored procedures are saved in the mysql.proc table

Create a stored procedure

CREATE PROCEDURE sp_name ([proc_parameter [, proc_parameter...]])

Routime_body

Where: proc_parameter: [IN | OUT | INOUT] parameter_name type

Where IN stands for input parameters, OUT for output parameters, INOUT for both input and output, param_name for parameter name, and type for parameter type

View a list of stored procedures

SHOW PROCEDURE STATUS

View stored procedure definition

SHOW CREATE PROCEDURE sp_name

Call the stored procedure:

CALL sp_name ([proc_parameter [, proc_parameter...]])

CALL sp_name

Note: when there is no parameter, the "()" can be omitted. When there are parameters, the "()" can not be omitted.

Stored procedure modification:

ALTER statement to modify stored procedures can only modify unimportant things such as the comments of stored procedures, not the body of stored procedures, so the way to modify stored procedures is to delete and rebuild them.

Delete a stored procedure:

DROP PROCEDURE [IF EXISTS] sp_name

Create a no-parameter stored procedure:

Delimiter / /

CREATE PROCEDURE showTime ()

BEGIN

SELECT now ()

END//

Delimiter

CALL showTime

Create a stored procedure with parameters: only one IN parameter

Delimiter / /

CREATE PROCEDURE seleById (IN id SMALLINT UNSIGNED)

BEGIN

SELECT * FROM students WHERE stuid = id

END//

Delimiter

Call seleById (2)

Example

Delimiter / /

CREATE PROCEDURE dorepeat (p1 INT)

BEGIN

SET @ x = 0

REPEAT SET @ x = @ x + 1; UNTIL @ x > p1 END REPEAT

END

/ /

Delimiter

CALL dorepeat (1000)

SELECT @ x

Create a stored procedure with parameters: contains IN parameters and OUT parameters

Delimiter / /

CREATE PROCEDURE deleteById (IN id SMALLINT UNSIGNED, OUT num SMALLINT UNSIGNED)

BEGIN

DELETE FROM students WHERE stuid = id

SELETE row_count () into num

END//

Delimiter

Call seleById (2 famous Line)

SELETE @ Line

Description: create a stored procedure deleteById, including an IN parameter and an OUT parameter. When called, the number of rows affected is passed in the deleted ID and the user variable @ Line,select @ Line; that holds the value of the modified row

Advantages of stored procedures:

Stored procedures encapsulate frequently used SQL statements or business logic, precompile and store them in the database, and call them directly from the database when needed, eliminating the process of compilation

Improve the running speed

At the same time, reduce the amount of network data transmission.

The difference between stored procedures and custom functions:

The process implemented by the stored procedure is more complex, and the function is more targeted.

Stored procedures can have multiple return values, while custom functions have only one return value

Stored procedures are generally executed independently, while functions are often used as part of other SQL statements

Process control

Process control can be used in stored procedures and functions to control the execution of statements

Process control:

IF: used to judge conditions. Execute different statements depending on whether the conditions are met

CASE: used for conditional judgment, which is more complex than IF statement.

LOOP: repeat specific statements to implement a simple loop

LEAVE: for jumping out of loop control

ITERATE: jump out of this cycle and go straight to the next cycle

REPEAT: loop statements with conditional control. When certain conditions are met, the statement will jump out of the loop

WHILE: conditional Loop statement

Trigger

The execution of the trigger is not called by the program, nor started manually, but triggered and activated by events to achieve execution.

Create trigger

CREATE

[DEFINER = {user | CURRENT_USER}]

TRIGGER trigger_name

Trigger_time trigger_event

ON tbl_name FOR EACH ROW

Trigger_body

Description:

Trigger_name: name of the trigger

Trigger_time: {BEFORE | AFTER}, indicating that it is triggered before or after the event (if triggered before the event, the event will be replaced)

Trigger_event:: {INSERT | UPDATE | DELETE}, the specific event triggered

Tbl_name: this trigger acts on the table name

Trigger exampl

CREATE TABLE student_info (

Stu_no INT (11) NOT NULL AUTO_INCREMENT

Stu_name VARCHAR (255) DEFAULT NULL

PRIMARY KEY (stu_no)

);

CREATE TABLE student_count (

Student_count INT (11) DEFAULT 0

);

INSERT INTO student_count VALUES (0)

Example: create a trigger

When presenting INSERT data to students, the number of students increases and decreases when DELETE students

CREATE TRIGGER trigger_student_count_insert

AFTER INSERT

ON student_info FOR EACH ROW

UPDATE student_count SET student_count=student_count+1

CREATE TRIGGER trigger_student_count_delete

AFTER DELETE

ON student_info FOR EACH ROW

UPDATE student_count SET student_count=student_count-1

View trigger

SHOW TRIGGERS

The way to query the system table information_schema.triggers specifies the query conditions and views the specified trigger information.

Mysql > USE information_schema

Database changed

Mysql > SELECT * FROM triggers WHERE trigger_name='trigger_student_count_insert'

Delete trigger

DROP TRIGGER trigger_name

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.