Mysql Stored Procedure Basics

Mysql Stored Procedure Basics

[SQL] #1. view all stored procedure names in the database # -- this statement is used to remove a stored procedure. You cannot delete another stored procedure in one stored procedure. You can only call another Stored Procedure # select name from mysql. proc WHERE db = 'database name ';

 

[SQL] #2. List all stored procedures # SHOW PROCEDURE STATUS; #3. View stored PROCEDURE details # SHOW CREATE PROCEDURE database name. Stored PROCEDURE name;

[SQL] #4. The parameter columns enclosed by parentheses in a stored procedure must always exist. If no parameter exists, use an empty parameter column (). # Each parameter is an IN parameter by default. To specify other parameters, you can use the keyword OUT or INOUT # create procedure stored PROCEDURE name (parameter list) before the parameter name; Use BEGIN... END compound statement to contain multiple statements # BEGIN # SQL statement code block # END

 

[SQL] #5, DECLARE Statement (used to DECLARE local variables); # To provide a DEFAULT value for a variable, you must include a DEFAULT clause. If no DEFAULT clause exists, the initial value is NULL. The scope of a local variable is within the declared BEGIN... END block. # DECLARE var_name [,...] type [DEFAULT value]

 

[SQL] #6, variable SET statement # SET var_name = expr [, var_name = expr] [SQL] #7, SELECT... INTO statement; this SELECT syntax stores the selected columns directly to variables. Therefore, only a single row can be retrieved. # SELECT col_name [,...] INTO var_name [,...] table_expr [SQL] #8. modify stored PROCEDURE # alter procedure stored PROCEDURE name SQL statement code block; [SQL] #9. delete stored PROCEDURE # DROP PROCEDURE IF EXISTS stored PROCEDURE name; # Call a stored procedure. The name of the stored procedure must be enclosed in parentheses, even if the stored procedure has no parameters. [SQL] #10, CALL stored procedure name (parameter list); [SQL] #11, MySQL stored procedure parameters (in ;) # similar to the value transfer of function parameters in C language, this parameter may be modified within the MySQL stored procedure, but modification to the in type parameter may be performed on the caller (caller) is not visible ). [SQL] #12, MySQL Stored Procedure Parameter (out) # MySQL stored procedure "out" parameter: Pass the value from the stored procedure to the caller. In a stored procedure, the initial value of this parameter is null, regardless of whether the caller sets the value for the stored procedure parameter [SQL] #13, MySQL Stored Procedure Parameter (inout) # The inout parameter of the MySQL stored procedure is similar to the out parameter, and can be passed to the caller from the stored procedure. The difference is that the caller can also pass the value to the stored procedure through the inout parameter. # Note: If you only want to pass data to the MySQL stored procedure, use the "in" type parameter; # If you only return values from the MySQL stored procedure, use the "out" type parameter; # If you need to pass the data to the MySQL stored procedure, you need to pass it back to us after some computation. In this case, you need to use the "inout" type parameter. # Demo1: create a simple Stored Procedure (obtain the total number of users) with output parameters. # -- Delete the Stored procedure drop procedure if exists proc_users_getCount # -- CREATE the stored procedure create procedure proc_users_getCount (OUT n INT) begin select count (*) FROM users; END # -- mysql call the Stored Procedure CALL proc_users_getCount (@ n); # demo2: create a simple Stored Procedure (obtain user information based on the user ID) with input parameters. # -- Delete the Stored procedure drop procedure if exists proc_users_findById; # -- create procedure proc_users_findById (IN n INT) begin select * FROM users WHERE id = n; END # -- Define the variable SET @ n = 1; # -- CALL the Stored Procedure CALL proc_users_findById (@ n); # Last: note the following when operating the stored procedure: #1. when deleting a stored procedure, you only need to specify the name of the stored procedure without parentheses; #2. when creating a stored procedure, no matter whether the stored procedure has any parameters, brackets are required; #3. when using SET to define variables, you should follow the SET syntax rules; # SET @ variable name = initial value; #4. when defining the Stored Procedure Parameter List, note that the parameter name is different from the field name in the database. Otherwise, unexpected results may occur. # END: java calls the Stored Procedure ut Il # // execute the Stored Procedure # @ Override # public ResultSet execProc (final String procName, final Object [] params) {# return (ResultSet) gethibernatetemplate(.exe cute (# new HibernateCallback () {# public Object doInHibernate (Session session) # throws HibernateException, SQLException {# CallableStatement cstmt = session. connection ()#. prepareCall (procName); # if (params! = Null) {# for (int I = 0; I <params. length; I ++) {# cstmt. setObject (I + 1, params [I]) ;#}## ResultSet rs = cstmt. getResultSet (); # return rs ;#}#});#}