The meaning of the leave statement is to leave the loop. At the same time, like the repeat loop, it does not need an end condition. The loop loop loop does not need an initial condition, which is similar to the while loop. Process control statement Branch statement SELECT count(*) into v_count FROM book where bookid = v_bookid use select into Assign values to variables. Local variables must be placed at the beginning of the stored procedure.Method 2: use select into select count(*) into v_count from book There are two ways to assign values to variables: SELECT * FROM book where bookid = v_bookid Declarative variable v_bookid And the default value is 1. Here we focus on the use of stored procedures, only local variables for the time being.Ī local variable is a variable declared between BEGIN and END, and the scope is between BEGIN and END.ĭECLARE variables: use the DECLARE keyword DECLARE var_name var_type Ĭase: CREATE PROCEDURE PROC_SELECT_BOOK() MySQL variables include: global variables, callback variables, user variables, system variables, and local variables. This is also the biggest disadvantage of stored procedures mentioned earlier. Of course, what makes us sick is that the syntax of stored procedures in each different database is different. Tips: as long as you have learned programming, these are very simple. Therefore, if we want to write a certain quality stored procedure, we need to understand some basic syntax in the stored procedure.
If there can be business logic in stored procedures, basic process control is required. Of course, the stored procedure above only executes a query statement. Tips: of course, you can define this symbol yourself (don't be too casual).Ĭome on! Take a look at the case: - Declaration end symbol -Īfter execution, view the structure viewport on the left:Įxecute stored procedure: CALL PROC_SELECT_BOO ĭelete stored procedure: DROP PROCEDURE PROC_SELECT_BOOK In order to prevent Navicat from using " " as the end symbol when creating the stored procedure, we need to re declare the end symbol: DELIMITER $$ However, we may have multiple statements in the stored procedure, and the end of each statement is". The default statement end symbol in Navicat is ". Let's learn the basic syntax of stored procedures through a simple exampleīasic syntax: CREATE PROCEDURE produce_name(parameter list)īeign - Indicates the beginning of the process body -Įnd - Indicates the end of the process body.
#Mysql stored procedure code
The idea of stored procedure is very simple, that is, code encapsulation and reuse at the level of database SQL language. Users can call and execute it by specifying the name of the stored procedure and giving parameters (when necessary). It is compiled, created and saved in the database. Stored procedure is a set of SQL statements to complete specific functions. Stored Procedure is a database object that stores complex programs in a database for external programs to call. MySQL version 5.0 supports stored procedures.
0 kommentar(er)
