Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to understand MySQL stored procedures and triggers. The article is rich in content and analyzed and described from a professional point of view. I hope you can get something after reading this article.

Stored procedures (stored procedure SP) is the biggest innovation in MySQL version 5. 0. They are custom procedures or functions that are stored and executed directly by the MySQL server. The addition of SP expands the SQL language into a programming language, and SP can be used to save part of the logic in a client-server database application software for future use.

A trigger (trigger) is an automatic call to a SQL command or SP before or after an INSERT, UPDATE or DELETE command.

-

/ / all inputs must end with'$$'.

Delimiter $$

Let's first create the simplest function.

The function is to write two numbers and get the sum:

Create FUNCTION addition (v1 int (11), v2 int (11))

RETURNS int (11)

BEGIN

Return (v1+v2)

END$$

/ / change the ending symbol back

Delimiter

Let's call our function:

Select addition (11 ~ (10) 15), and the result is 26 correct.

Let's take a look at those functions in the database

Show function status

How to view the code of addition?

Show create function addition

Now let's delete that function:

Drop function addition

-

It's a little test on it. Now let's take a comprehensive look at stored procedures and triggers in MYSQL.

It is divided into three categories: FUNCTION, PROCEDURE and TRIGGER.

& &

FUNCTION

& &

Syntax:

CREATE FUNCTION function_name (param1 datatype [, param2 datatype,.])

RETURNS datatype

BEGIN

Commands

END

SP comment

"-" begins with comments and continues to the end of the line

-

(1) definition and assignment of local variables in FUNCTION

* definition of variables

Syntax:

DECLARE varname1 datatype1 [DEFAULT value]

DECLARE varname1,varname2.... Datatype [DEFAULT value]; / / the definition of multivariable of the same type

The $variable is defined in BEGIN. Definition between END.

If more than one BEGIN is defined in the FUNCTION body. END is nested, then variables can only be defined in the current BEGIN.. END or subBEGIN... Valid in END.

$subclass BEGIN... The variable definition in END can override the parent class BEGIN. Variables defined in END.

* assignment of variables

There are two ways to assign values to variables.

[1] assign a variable directly to a constant, or assign another variable to the current variable

Set var = value

Set var1 = value1,var2 = value2....

[2] assign SQL query results to variables

SELECT var: = value / / A method of assigning constants or other variables to the current variable using the SELECT method

SELECT nomalvalue INTO var / / another method of assigning constants or other variables to the current variable using the SELECT method

SELECT value FROM TABLE.. INTO var

SELECT value1,value2 FROM TABLE.. INTO var1,var2

The SELECT INTO command is a variant of the SELECT command. It ended the entire command with INTO varname last time.

Required, the SELECT command returns and can only return one record. (multiple records are not allowed)

Example-001:

Use wyd

Delimiter $$

--

Create table person (

Id int primary key auto_increment

Age int

) $

--

Insert into person (age) values (12)

Insert into person (age) values (34)

Insert into person (age) values (42)

Insert into person (age) values (13)

Insert into person (age) values (2) $$

Drop function addtion$$

--

CREATE FUNCTION getage (person_id int)

RETURNS int

BEGIN

DECLARE person_age int default 0

SELECT age FROM person WHERE id = person_id INTO person_age

RETURN person_age

END$$

--

Test the result:

Select getage (1) $$--& gt result = 12

Select getage (2) $$--& gt result = 34

Running normally

& & &

(2) branches in FUNCTION

IF-THEN-ELSE branch

Syntax:

IF comdition THEN

Commands

END IF

-

IF comdition THEN

Commands

ELSE

Commands

END IF

-

Nested IF comdition THEN

Commands

ELSE IF comdition THEN

Commands

[ELSE commands;]

END IF

END IF

-

[2] CASE branch

Syntax:

-

CASE expression

WHEN value1 THEN commands

WHEN value2 THEN commands

.

WHEN value_n THEN commands

ELSE commands

END CASE

-

Example-002:

CREATE FUNCTION personstate (age int)

RETURNS varchar (30)

BEGIN

DECLARE personstate varchar (30) DEFAULT "UNKNOWN"

IF age

< 0 THEN SET personstate = "UNBORN"; ELSE SET personstate = "BORN"; END IF; RETURN personstate; END$$ ----------------- select personstate(-3) ----> result = UNBORN; select personstate(3) ----> result = BORN; ------------------------------------ example-003: DROP FUNCTION personstate$$ CREATE FUNCTION personstate (age int) RETURNS varchar(30) BEGIN DECLARE personstate varchar(30) DEFAULT "UNKNOWN"; IF age < 0 THEN SET personstate = "UNBORN"; ELSE IF age >

0 & age14 & & age 22 & & age60 THEN SET personstate = "OLD"; END IF

END IF

RETURN personstate

END$$

-

Select personstate (- 3) $$result = UNBORN

Select personstate (5) $$result = CHILD

Select personstate (16) $$result = YANG

Select personstate (28) $$result = STRONG

Select personstate (66) $$result = OLD

TEST IS OK.

-

Example-004:

DROP FUNCTION showIn$$

CREATE FUNCTION showIn (valueIn int)

RETURNS VARCHAR (50)

BEGIN

DECLARE str varchar (30) DEFAULT "UNKNOWN"

CASE valueIn

WHEN 1 THEN SET str = "you input is 1"

WHEN 2 THEN SET str = "you input is 2"

WHEN 3 THEN SET str = "you input is 3"

WHEN 4 THEN SET str = "you input is 4"

WHEN 5 THEN SET str = "you input is 5"

ELSE SET str = "you input is not 1, 2, 3, 4, 5"

END CASE

RETURN str

END$$

-

Select showIn (1) $$result = you input is 1

Select showIn (2) $$result = you input is 2

Select showIn (6) $$result = you input is not 1Jing 2jue 3jue 4jue 5

TEST IS OK

-

& &

(3) Loop in FUNCTION

REPEAT-UNTIL cycle

[2] WHILE cycle

[3] LOOP cycle

* REPEAT-UTIL cycle

Syntax:

[loopname:] REPEAT

Commands

UNTIL condition

END REPEAT [loopname]

Description:

It has the same function as the do {} while (condition) statement. Run first, then judge.

Discard the loop when condition is true

* WHILE cycle

Syntax:

[loopname:] WHILE condition DO

Commands

END WHILE [loopname]

Description: it has the same function as while (condition) {commands;} statement. Judge first, then execute.

Discard the loop when condition is false

* LOOP cycle

Syntax:

Loopname: LOOP

Commands

IF condition THEN LEAVE loopname; END IF

END LOOP loopname

Explanation: this is a cycle without conditional judgment. It can be thought of as an endless cycle.

Unless the LEAVE command is executed to jump out of the loop, the loop will be executed forever.

* LEAVE

Syntax:

LEAVE loopname

Description:

The LEAVE loopname command is the execution flow of the program code that jumps out and ends a loop.

The LEAVE loopname command can also be used to exit the BEGIN-END block ahead of time.

The LEAVE loopname command is equivalent to the BREAK command that jumps out of the loop in C or JAVA.

* ITERATE

Syntax:

ITERATE loopname

Description:

The TERATE loopname command is to jump out of the current loop and then execute the next loop.

The TERATE loopname command can only be run inside the loop.

The TERATE loopname command is equivalent to the CONTINUE command that jumps out of the loop in C or JAVA.

-

Example-005:

DROP FUNCTION getString$$

CREATE FUNCTION getString (number int (11))

RETURNS VARCHAR (50)

BEGIN

Declare str varchar (50) default''

Declare i int default 0

Myloop: REPEAT

SET I = iTun1

Set str = concat (str, "*")

UNTIL I > = number

END REPEAT myloop

RETURN str

END $$

-

Select getString (3) $$

Select getString (4) $$

Select getString (8) $$

TEST IS OK

-

Example-006:

DROP FUNCTION getString$$

CREATE FUNCTION getString (number int (11))

RETURNS VARCHAR (50)

BEGIN

Declare str varchar (50) default ""

Declare i int default 0

Myloop: WHILE inumber THEN LEAVE myloop; END IF

Set str= concat (str, "#")

END LOOP myloop

RETURN str

END$$

-

Select getString (3) $$

Select getString (4) $$

Select getString (8) $$

TEST IS OK

-

-

Example-iterate:

DROP FUNCTION getString$$

CREATE FUNCTION getString (number int (11))

RETURNS VARCHAR (50)

BEGIN

Declare str varchar (50) default ""

Declare i int default 0

Myloop:LOOP

Set I = iTun1

IF I% 2 = 0 THEN ITERATE myloop; END IF

IF I > number THEN LEAVE myloop; END IF

Set str= concat (str, "#")

END LOOP myloop

RETURN str

END$$

& &

Basic grammatical rules

& &

Through the understanding of FUNCTION, we have a general understanding of the grammar rules of SP.

Variable definition rules, variable assignment rules, branch rules, and loop rules in FUNCTION also apply to PROCEDURE.

Now let's have a standardized understanding of the grammar rules of SP:

# semicolon (;). The same SP can contain any number of SQL commands. These commands must be opened with a semicolon, and even the control structure of branches and loops must end with a semicolon.

# BEGIN-END. Multiple SQL commands that do not fall between the SP keywords, such as between THEN and END IF, must be placed between the keywords BEGIN and END.

This means that the code for a SP made up of multiple SQL commands must start with BEGIN and end with END.

# newline character. Newline characters have the same semantic effect in SP code as space characters. This means that it is possible to write IF-THEN-ELSE-END-IF structures continuously on the same line or separately on multiple lines.

# variable. Local variables and local parameters for internal use in SP are not prefixed with "@". Normal SQL variables are allowed in SP, but they must be prefixed with "@".

(variables prefixed with "@" are ordinary global variables. For questions about variables, refer to the section "MYSQL variables".)

Case of # letters. SP is defined and called regardless of case. It is written as (for example) shorten, SHORTEN, Shorten all have the same effect.

# special characters. Avoid using special characters in SP. In short, MYSQL's support for special characters is not very good.

# comments. "-" begins with comments and continues to the end of the line

& &

Methods for viewing and deleting SP

& &

(1) View FUNCTION

Show function status

Show CREATE FUNCTION functionname

Select routine_name, routine_type,routine_schema,created from information_schema.routines where routine_schema = 'your database name' and routine_type='FUNCTION'

For example:

Select routine_name, routine_type,routine_schema,created from information_schema.routines where routine_schema = 'WYD' and routine_type='function'

DROP FUNCTION [IF EXISTS] function_name

(2) View PROCEDURE

SHOW PROCEDURE STATUS

Show CREATE PROCEDURE functionname

Select routine_name, routine_type,routine_schema,created from information_schema.routines where routine_schema = 'your database name' and routine_type='PROCEDURE'

For example:

Select routine_name, routine_type,routine_schema,created from information_schema.routines where routine_schema = 'WYD' and routine_type='PROCEDURE'

DROP PROCEDURE [IF EXISTS] procedure_name

(3) View all SP

Desc information_schema.routines

Select routine_name, routine_type,routine_schema,created from information_schema.routines

& &

MYSQL variable

& &

MYSQL allows people to store simple values (discrete values, not collections or lists like SELECT query results) in variables. In everyday applications, MySQL variables are rarely used; but for stored procedures, variables are very important SQL elements. Variables in MySQL can be divided into three categories:

$normal variable.

The flags of such variables start with the character @, and they lose their content when the SQL connection is closed.

System variable and server variable.

The contents of such variables are the working status or properties of the MySQL server, and their flags begin with the "@ @" string.

Local variables in the $stored procedure.

These variables are declared inside the stored procedure and are valid only within the stored procedure. They do not have a uniform special flag, but variable names must be different from data table and data column names.

Local variables must be declared with the DECLARE command before they can be used. The contents of the local variable are lost when the procedure or function exits.

Declaration and assignment of ordinary global variables:

Example: set @ varname = 3

Select @ total: = count (*) from table_a

Select money from book where id = 3 into @ bookmoney

Query:

Example: select @ varname

& &

PROCEDURE

& &

PROCEDURE [databasename.] procedure_name ([IN or OUT or INOUT] parametername datatype)

BEGIN

Commands

END

-

Parameters of the stored procedure:

The keywords IN, OUT, INOUT are used to distinguish whether the use of the relevant parameters is limited to input data, output data or input / output data. (default setting is IN)

-

Definition and assignment of local variables in PROCEDURE

Definition and assignment of local variables similar to FUNCTION

-

Example-007:

Topic: write an additive PROCEDURE and enter two numbers. PROCEDURE outputs their and

Delimiter $$

DROP PROCEDURE IF EXISTS p_addition

CREATE PROCEDURE p_addition (IN v1 int, IN v2 int, OUT sum int)

BEGIN

Set sum = v1+v2

END$$

-

CALL p_addition (127,45,000sum) $

Select @ sum $$

TEST IS OK

-

-

Example-008

Topic: there is a student table with student name field and total score field.

We write a PROCEDURE, just call the PROCEDURE and enter the student name and the total score. It helps us to write the student into the data table.

Delimiter $$

CREATE TABLE student (

Id int (11) primary key auto_increment

Name varchar (50)

Score int (5)

) $

-

DROP PROCEDURE IF EXISTS packs addStudentals $

-

CREATE PROCEDURE p_addStudent (IN p_name VARCHAR (50), IN p_score INT)

BEGIN

Insert into student (name,score) values (paired score)

END$$

-

CALL p_addStudent ('Petter',199) $$

CALL p_addStudent ('Helen',209) $$

CALL p_addStudent ('Jacker',238) $$

Select * from student $$

-TEST IS OK

-

-

Example-009

Topic: there is a person table with multiple fields.

Name, age, state

We write a PROCEDURE, just call the PROCEDURE and enter the person's name and age. It helps us to judge the state and write it to the database.

If age = 10 THEN

LEAVE myloop

END IF

END LOOP myloop

END$$

-

TEST IS OK

$$

Vernier

$$

Speaking of cursors, I think of cursors in Oracle. If you haven't learned cursors in Oracle, it doesn't matter. Because MYSQL cursors are simpler.

CURSOR: a pointer built in MYSQL to query data and get a collection of records. It allows developers to access one row of the result set at a time.

Explicit cursors are the only cursors in MYSQL.

-

How to use the cursor (use process).

(1) declare cursors.

(2) Open the cursor.

(3) get the record from the cursor.

(4) close the cursor.

-

(1) declare cursors.

Syntax:

DECLARE cursorname CURSOR FOR "YOUR SQL"

-

(2) Open the cursor.

OPEN cursorname

-

(3) get the record from the cursor.

FETCH cursorname INTO v1,v2,....

In ORACEL,% FOUND returns a FALSE when there is no value in the cursor.

But in MYSQL, FETCH finally triggers a 1329 error "No data to fetch". The corresponding SQLSTATE is 02000.

This exception is inevitable, so we all use exception traps to catch it. (you can declare a corresponding exception trap directly, or you can declare a NOT FOUND exception trap.)

-

(4) close the cursor.

CLOSE cursorname .

Note: in fact, doing so also increases the logic. In fact, the cursor will automatically turn off when the BEGIN-END block ends. So many programmers don't close cursors manually.

Example exercise:

Example-013

Topic, create a multi-field table student, with id, name, intime three fields. There are multiple lines of records written in it.

Use cursors to get all the records in it, and output one by one.

-

Delimiter $$

-

DROP TABLE IF EXISTS student $$

-

CREATE TABLE student (

Id int primary key auto_increment

Name varchar (50)

Intime timestamp (14)

) $

-

Insert into student (name,intime) values ('s Murray 1century Magazine '1999-08-25 12-12-14-30-30') $$

Insert into student (name,intime) values ('s murmur 2 hundred and twenty-five handlers' 1999-08-25 12-12-12-12-15-30-30) $$

Insert into student (name,intime) values ('s murmur 3 minutes, 1999-08-25, 12-12-12-12-30-15-30-30) $$

Insert into student (name,intime) values ('s murmur 4 minutes, 1999-08-25, 12-12-12-12-30-15-30-30) $

Insert into student (name,intime) values ('s murmur 5 minutes, "1999-08-25, 12-12-12-12-30-15-30-30) $$

Insert into student (name,intime) values ('s murmur 6 minutes, "1999-08-25, 12-12-12-12-12-30-30 -) $$

Insert into student (name,intime) values ('s murmur 7 colors, "1999-08-25, 12-12-12-12-30-15-30-30) $$

Insert into student (name,intime) values ('s Murray 8 colors Magazine 1999-08-25 12-12-12-12-30-15-30) $$

Insert into student (name,intime) values ('s murmur 9 colors, "1999-08-25, 12-12-12-12-30-15-30-30) $$

Commit $$

-

DROP PROCEDURE IF EXISTS p_readcursor $$

-

CREATE PROCEDURE p_readcursor ()

BEGIN

DECLARE p_id int default 0

DECLARE p_name varchar (50) default "unknow"

DECLARE p_intime timestamp (14) default '0000-00-0000: 00 00'

DECLARE student_cursor CURSOR FOR select id,name,intime from student

DECLARE EXIT HANDLER FOR 1329 SELECT "CURSOR IS END-& gt OK" as message

OPEN student_cursor

Myloop: LOOP

FETCH student_cursor into p_id,p_name,p_intime

IF p_id=100 THEN LEAVE myloop; END IF

Select p_id,p_name,p_intime

END LOOP myloop

CLOSE student_cursor

END $$

-

Call p_readcursor () $$

-TEST IS OK-

-

$$

Trigger

$$

The purpose of a trigger is to automatically transfer the SQL command or SP before or after the INSERT, UPDATE, or DELETE commands. For example, you can test whether the modified data meets certain conditions for each UPDATE operation.

Triggers in MYSQL5.0 are still imperfect. Compared with SP, triggers are far from mature enough to be used in real applications. According to the MySQL online documentation, more trigger functions will be available in the MYSQL5.1 version.

Before version 5.1 came out, triggers could only accomplish some very rudimentary tasks.

(1) create a trigger

(2) query triggers in the database

(3) Delete trigger

-

(1) create a trigger

Syntax:

CREATE TRIGGER trigger_name BEFORE | AFTER INSERT | UPDATE | DELETE

ON tablename [FOR EACH ROW]

BEGIN

Commands

END

Note: * you can define up to 6 triggers for the same data table, one before and after each of the INSERT, UPDATE or DELETE commands.

* the name of trigger must be unique in the same database.

* the trigger code body should start with BEGIN and end with END.

Functional limitations:

* No data table can be accessed in the trigger code, not even the data table defined by the trigger. Naturally, you cannot use DELETE,UPDATE,INSERT to modify database tables.

* MySQL does not provide commands or syntax elements that can be used to cancel DELETE,UPDATE,INSERT commands.

* transaction commands cannot be invoked in trigger code.

OLD and NEW

In the trigger code, you can access the fields of the current record in the following ways.

OLD.columname returns the UPDATE,DELETE of an existing record before it was deleted or modified.

NEW.columname returns the new contents of a new record or modified record (INSERT, UPDATE).

-

(2) query triggers in the database

There are no commands to view custom triggers yet. (they did a terrible job, HOHO)

-

(3) Delete trigger

Syntax:

DROP TRIGGER [databasename.] triggername

Note: deleting trigger does not support IF EXISTS variants.

-

Take an example:

Example-014

Delimiter $$

-

DROP TABLE IF EXISTS student_score$$

-

CREATE TABLE student_score (

Id int primary key auto_increment

Name varchar (50)

Score int

) $

-

DROP TRIGGER student_score_insert_before$$

-

CREATE TRIGGER student_score_insert_before

BEFORE INSERT ON student_score FOR EACH ROW

BEGIN

IF NEW.score100 THEN

SET NEW.score = 0

END IF

END$$

-

Insert into student_score (name,score) values ('ZhangSan',12) $$

Insert into student_score (name,score) values ('LiSi',-12) $$

Insert into student_score (name,score) values ('WangWu',112) $$

Mysql > select * from student_score$$

+-- +

| | id | name | score | |

+-- +

| | 1 | ZhangSan | 12 | |

| | 2 | LiSi | 0 | |

| | 3 | WangWu | 0 | |

+-- +

At the end of your notes, congratulations on your happy study.

The above is the editor for you to share how to understand MySQL stored procedures and triggers, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.