MySQL Advanced learning Note four: the definition of MySQL stored procedures, processing procedures and management of stored procedures! (Video serial Number: Advanced _11,12)

Knowledge Point five: definition of MySQL stored procedure conditions and process and management of stored procedures (11,12)

Define conditions and handling:

The definition and processing of a condition can be used to define the corresponding processing steps when a problem is encountered during processing.

DECLARE CONTINUE HANDLER for SQLSTATE ' error code value ' SET @ variable = variable Value

Management of stored procedures:

Changes to Stored procedures:
Alter{procedure | FUNCTION} sp_name

[Characteristic ...]

Characteristic:

{CONTAINS SQL | NO SQL | READS SQL DATA | Modifies SQL DATA}

| SQL SECURITY {definer| Invokey}
| COMMENT ' string ';

　　　　Note: No SQL indicates that the subroutine does not contain SQL statements,

READS SQL data represents a statement in a subroutine that contains read data.

Modifies SQL data represents a statement in a subroutine that contains write data

SQL SECURITY {definer | Invokey} indicates who has permission to execute

Definer that only the definition can be executed by himself;

Invokey indicates that the caller can execute

COMMENT ' String ' is a comment message

　　To view stored procedures under a database:

SHOW PROCEDURE STATUS WHERE db= ' database name ';

　　　　View the list of stored procedures under the current database:

SELECT specific_name from Mysql.proc;

　　　　To view the contents of a stored procedure:
SELECT specific_name,body from Mysql.procwhere specific_name = stored procedure name;

Or: SHOW CREATE PROCEDURE Stored procedure name.
　　To delete a stored procedure:

DROP PROCEDURE IF exists stored procedure name

1 --defining conditions and handling2 --join is right, but execute an error, because if is one of userinfo and exists3DELIMITER//4 CREATE PROCEDUREP_insertdemo ()5 6 BEGIN7     INSERT  intoUserInfoVALUES(1,'UI','123');8     INSERT  intoEmployeesVALUES(5098,'1990-11-11','Uim','Jum','2000-11-12');9 END Ten // One  A DELIMITER; - Call P_insertdemo2 (); -  the  - --right. -DELIMITER// - CREATE PROCEDUREP_insertdemo2 () +  - BEGIN +     DECLARE CONTINUEHANDLER forSQLSTATE'23000' SET @x=1; A     INSERT  intoUserInfoVALUES(1,'UI','123'); at     INSERT  intoEmployeesVALUES(5098,'1990-11-11','Uim','Jum','M','2000-11-12'); - END  - // -  - DELIMITER; -Call P_insertdemo2 ();

defining conditions and processing process tests

MySQL Advanced learning Note four: the definition of MySQL stored procedures, processing procedures and management of stored procedures! (Video serial Number: Advanced _11,12)