-
CREATE PROCEDURE
Create a stored procedure that stores a table in a MySQL database.
CREATE FUNCTION
Create a user-defined function, especially a stored procedure that returns data.
ALTER PROCEDURE
Changes to a prespecified stored procedure created with CREATE PROCEDURE do not affect related stored procedures or stored functionality. .
ALTER FUNCTION
Changes to a prespecified stored procedure created with CREATE FUNCTION do not affect related stored procedures or stored functions. .
DROP PROCEDURE
Delete one or more stored procedures from a MySQL table.
DROP FUNCTION
Delete one or more stored functions from a MySQL table.
SHOW CREATE PROCEDURE
Returns the text of a prespecified stored procedure created using CREATE PROCEDURE. This declaration is a MySQL extension to the SQL:2003 specification.
SHOW CREATE FUNCTION
Returns the text of a prespecified stored procedure created using CREATE FUNCTION. This declaration is a MySQL extension to the SQL:2003 specification.
SHOW PROCEDURE STATUS
Returns the characteristics of a prespecified stored procedure, including name, type, creator, creation date, and modification date. This declaration is a MySQL extension to the SQL:2003 specification.
SHOW FUNCTION STATUS
Returns the characteristics of a prespecified stored function, including name, type, creator, creation date, and change date. This declaration is a MySQL extension to the SQL:2003 specification.
CALL
Call a prespecified stored procedure created using CREATE PROCEDURE.
BEGIN ... END
Contains a set of multiple statements executed.
DECLARE
Used to specify local variables, environments, processors, and pointers.
SET
Used to change the values of local and global server variables.
SELECT ... INTO
Column used to store display variables.
OPEN
Used to open a pointer.
FETCH
Use a specific pointer to get the next column.
CLOSE
Used to close and open the pointer.
IF
An if-then-else-end if statement.
CASE...WHEN
The structure of a case statement
LOOP
A simple loop structure; it can be exited using the LEAVE statement.
LEAVE
Used to exit IF, CASE, LOOP, REPEAT and WHILE statements.
ITERATE
Used to restart the cycle.
REPEAT
The loop is tested at the end.
WHILE
A loop that is tested at the beginning.
RETURNS
Returns the value of a stored procedure.
MySQL 5.0 supports stored procedure statements.
1. Create a stored procedure
1.Basic grammar:
create procedure sp_name()
begin
.........
end
2.Parameter passing
2. Call the stored procedure
1.Basic syntax: call sp_name()
Note: Parentheses must be added after the stored procedure name, even if the stored procedure has no parameters passed
3. Delete stored procedures
1.Basic grammar:
drop procedure sp_name//
2. Precautions
(1) You cannot delete another stored procedure in one stored procedure, you can only call another stored procedure
4. Blocks, conditions, loops
1. Block definition, commonly used
begin
...
end;
You can also give the block an alias, such as:
label:begin
...........
end label;
You can use leave label; to jump out of the block and execute the code after the block.
2.Conditional statement
if condition then
statement
else
statement
end if;
3. Loop statement
(1).while loop
[label:] WHILE expression DO
statements
END WHILE [label];
(2).loop loop
[label:] LOOP
statements
END LOOP [label];
(3).repeat until loop
[label:] REPEAT
statements
UNTIL expression
END REPEAT [label];
5. Other commonly used commands
1.show procedure status
Displays the basic information of all stored procedures in the database, including the database to which it belongs, the name of the stored procedure, creation time, etc.
2.show create procedure sp_name
Stored procedure creation syntax:
CREATE PROCEDURE procedure_name ([parameter[,...])
[LANGUAGE SQL]
[ [NOT] DETERMINISTIC ]
[{CONTAINS SQL|MODIFIES SQL DATA|READS SQL DATA|NO SQL}]
[SQL SECURITY {DEFINER|INVOKER}]
[COMMENT comment_string]
procedure_statements
You can use SHOW PROCEDURE STATUS or SHOW CREATE PROCEDURE to view stored procedure information. In addition, the system table INFORMATION_SCHEMA.ROUTINES also contains some information about stored procedures. Similarly, functions can also be viewed in the same way (SHOW FUNCTION STATUS)
Creation of functions
CREATE FUNCTION function_name (parameter[,...])
RETURNS datatype
[LANGUAGE SQL]
[ [NOT] DETERMINISTIC ]
[ {CONTAINS SQL | NO SQL | MODIFIES SQL DATA | READS SQL DATA} ]
[SQL SECURITY {DEFINER|INVOKER}]
[COMMENT comment_string]
Statement body
Functions are basically the same as stored procedures. The main differences are:
1. To use RETURNS to specify the return type
2. The function must return a value and use RETURN in the statement body (note: use RETURNS to specify the return type and RETURN for the return value)
3. Parameters do not distinguish between IN and OUT, all are of type IN.
example:
CREATE FUNCTION cust_status(in_status CHAR(1))
RETURNS VARCHAR(20)
BEGIN DECLARE long_status VARCHAR(20);
IF in_status="O" THEN SET long_status="Overdue";
ELSEIF in_status="U" THEN SET long_status="Up to date";
ELSEIF in_status="N" THEN SET long_status="new";
END IF;
RETURN(long_status);
END;
Call:
SELECT cust_status('O');
trigger
CREATE [DEFINER={user|CURRENT_USER}] TRIGGER trigger_name
{BEFORE|AFTER} {UPDATE|INSERT|DELETE}
ON table_name
FOR EACH ROW
trigger_statements
Meaning: The statement trigger_statements operation is triggered before (before) or after (after) the update, insert, and delete operations are performed on the table table_name.
example:
mysql> CREATE TRIGGER account_balance_au
AFTER UPDATE ON account_balance FOR EACH ROW
BEGIN
DECLARE dummy INT;
IF NEW.balance<0 THEN
SET NEW.balance=NULL;
END IF;
END
The above trigger means: after updating the table account_balance, if the updated value balance is less than 0, change it to NULL.
Note: If it is OLD.balance, it means the original value before update