One 、 stored procedure
1.1、delimiter
MySQL One of the orders is delimiter, The function is to set the end symbol of the command section , That is to say, after encountering the set ending symbol , Press enter , Then the command section can be executed . Usually by default , The end of the command is a semicolon (;), But in stored procedures , The process body may contain semicolons (;), So you need to replace the end of command with another character , Such as $$、// etc. , After the stored procedure is created , You can reset the ending symbol of the command segment to semicolon .
grammar :delimiter Command Terminator
1.2、 Stored procedure example
1) establish :
DELIMITER $$CREATE PROCEDURE SHOW_EMP01()BEGIN SELECT * FROM STUDY11;END$$DELIMITER ;
2) call :
CALL SHOW_EMP01();
1.3、 View stored procedures
1) View all stored procedures
SHOW PROCEDURE STATUS;
2) View the stored procedure of the specified database
SHOW PROCEDURE STATUS WHERE DB='test';
3) View the specified stored procedure source code
SHOW CREATE PROCEDURE SHOW_EMP01;
1.4、 Delete stored procedure
DROP PROCEDURE SHOW_EMP01;
1.5、 Declare variables
DELIMITER $$CREATE PROCEDURE SHOW_EMP02()BEGIN # Variable definitions DECLARE ROWS INT DEFAULT 0; # Variable assignment SELECT COUNT(*) INTO ROWS FROM STUDY11; # The result returned to SELECT ROWS;END$$DELIMITER ;
1.6、 Parameters
1.6.1、IN: Input parameters
1) establish :
DELIMITER $$CREATE PROCEDURE GETSEX (IN PNAME VARCHAR(12))BEGIN SELECT SEX FROM STUDY11 WHERE NAME=PNAME;END$$DELIMITER ;
2) call :
CALL GETSEX ('study01');
1.6.2、OUT: Output parameters
1) establish :
DELIMITER $$CREATE PROCEDURE GETID (IN PNAME VARCHAR(12),OUT PID INT)BEGIN SELECT ID INTO PID FROM STUDY11 WHERE NAME=PNAME;END$$DELIMITER ;
2) call :
CALL GETID ('study01',@PID);SELECT @PID; -- The complete form of this sentence is :SELECT @PID FROM DUAL;
1.6.3、INOUT: Input/output parameter
<.........