Mysql_ Basic _ Stored procedures and functions

stored procedures and functions

meaning: A set of pre-compiled SQL statements that understand batch processing statements     1 and improve the    reusability of code 2 , simplified operation     3 , reducing the number of compilations and the number of connections to the database server, increasing efficiency differences:    stored procedures: can have 0 return, can also have multiple returns, suitable for bulk INSERT, bulk update    storage functions: There are only 1 return, Returns a result when processing data is appropriate

Stored ProceduresGrammar

 Creating a procedure syntax: Create PROCEDURE stored procedure name (parameter list) BEGIN stored procedure Body (a set of legitimate SQL statements) End Description:1, the parameter list contains three-part parameter pattern parameter Name argument type example: In Name VARCHAR ( -) Parameter mode: In: input parameter out: output parameter INOUT: input/output parameter2, there is only one sentence in the stored procedure body, you can omit the begin END3, the end of each SQL statement in the stored procedure body must have a semicolon4, at the end of the stored procedure, you can set the closing tag syntax with DELIMITER: DELIMITER end Tag Example: DELIMITER $ Call procedure syntax: Calling stored procedure name (Argument column    Description: Invoke in mode parameter: Call stored procedure name (@ variable);         Call out mode parameter: SET @ variable;        Call stored procedure name (@ variable);    SELECT @ variable; Call inout mode parameter: SET @ variable=value;        Call stored procedure name (@ variable);    SELECT @ variable; View procedure syntax: show CREATE PROCEDURE stored procedure name; Delete procedure syntax: Drop PROCEDURE stored procedure name;

Case

1 , create a stored procedure with the in mode parameter # set the end tag DELIMITER $# Creates a Create PROCEDURE Pro1 (in username VARCHAR (  -), in Password VARCHAR ( -) BEGIN DECLARE result INT DEFAULT0;    # Declare and initialize SELECT COUNT (*into result # assignment from ' user ' u WHERE u.username=username and U.password=password; SELECT IF (Result>0,' LoginSuccess',' Loginfailed'); # Use END $# calls call Pro1 ('Admin','Admin')$2 , creating a stored procedure with an out mode parameter # set end tag DELIMITER $# Create PROCEDURE Pro2 (in ID INT, out Username VARCHAR ( -), Out Password VARCHAR ( -) BEGIN SELECT U.username,u.password to Username,password from ' user ' u WHERE u.id=ID; END $# calls call Pro2 (1001, @username, @password) $SELECT @username, @password $3 , creating a stored procedure with the INOUT mode parameter # set end tag DELIMITER $# Create PROCEDURE Pro3 (INOUT a INT, INOUT b INT) BEGIN SET a= A *2; SET b= b*2; END $#调用SET @m=Ten$SET @n= -$CALL Pro3 (@m,@n) $SELECT @m,@n$

Storage functionsGrammar

creating function Syntax: Create functions    name (argument list) RETURNS return type    BEGIN        function Body    End Description:    1, Parameter list contains two parts parameter    name    argument type 2, function body must have return statement, if no error     3, There is only one sentence in the function body, you can    omit the begin end4, the end of each SQL statement in the function body    must be semicolon 5, End tag syntax can be set using DELIMITER at the end of the function    :        DELIMITER end Tag    Example:        DELIMITER $ Call function syntax:    SELECT functions Name (parameter list) View function syntax:    show CREATE function function name;  Delete function syntax: The    name of the drop functional function;

Case

1 , no parameter return # set end tag DELIMITER $# Create FUNCTION fun1 () RETURNS intbegin DECLARE C INT defaul T0; SELECT COUNT (*) into C from ' user '; RETURN C; END $#调用SELECT fun1 () $2 , have a parameter return # set end tag DELIMITER $# Creates a Create FUNCTION fun2 (username VARCHAR ( -)) RETURNS VARCHAR ( -) BEGIN SET @password='123'; SELECT U.password into @password from ' user ' u WHERE u.username=username; RETURN @password; END $#调用SELECT fun2 ('Admin')$

Mysql_ Basic _ Stored procedures and functions