MySQL Stored Procedure Programming Tutorial Reading Notes-Chapter 2 MySQL Stored Procedure programming guide-Stored Procedure Section

MySQL Stored Procedure programming is a complex topic. In this chapter, we will provide you with the content to complete basic tasks, including:

1. How to Create a stored procedure

2. How to input and output stored procedures

3. How to interact with the database

4. How to Use the MySQL storage programming language to create processes, functions, and triggers

The first Stored Procedure instance:

Delimiter $ drop procedure if exists HelloWorld $ create procedure HelloWorld () begin select "Hello World"; end $ variable

Local variables can be declared using the declare statement. The variable name must follow the MySQL listing rules, and any data types built in MySQL can be used. You can use the default clause to give the variable an initial value and use the SET statement to assign a new value to the variable.

delimiter $$drop procedure if exists variable_demo$$begin     declare my_integer int;    declare my_big_integer bigint;    declare my_currency numeric(8, 2);    declare my_pi float        default 3.1415926;    declare my_text text;    declare my_dob date        default '1960-06-21';    declare my_varchar varchar(30)        default "Hello World!";    set my_integer = 20;    set my_big_integer = power(my_integer, 3);end $$delimiter;

Parameters

Parameters can make our storage programs more flexible and practical. Parameters include IN (read-only mode), INOUT (read-write mode), and OUT (write-only mode ). The IN mode is used as the default parameter mode.

IN: Any modification to this parameter will not be returned to the program that calls it.

OUT: This mode means that the stored procedure can assign values to the parameters (modify the value of the parameter), and the modified value will be returned to the calling program of the stored procedure.

INOUT: This mode means that the stored procedure can read the passed parameters, and any modifications to this parameter are visible to its calling program.

For storage functions, you can only use the IN mode.

delimiter $$drop procedure if exists my_sqrt$$create procedure my_sqrt(input_number int, out out_number float)begin    set out_number = SQRT(input_number);end $$delimiter;

Create and execute the stored procedure using the OUT Parameter

Call my_sqrt (12, @ out_value) $ select @ out_value $ condition execution

The storage program calculates the discount rate based on the number of purchases. If the number of purchases exceeds $500, 20% is returned. If the number of purchases exceeds $100, 10% is returned.

Delimiter $ drop procedure if exists discounted_price $ create procedure discounted_price (normal_price NUMERIC (8, 2), out discount_price NUMBERIC (8, 2) begin if (normal_price> 500) then set discount_price = normal_price *. 8; else if (normal_price> 100) then set discount_price = normal_price *. 9; else set discount_price = normal_price; end if; end $ delimiter; Loop

The MySQL stored procedure language provides three types of loops

Simple LOOP using LOOP and END LOOP

When the loop condition is true, the WHILE and end while statements are used.

Use REPEAT and UNTIL statements UNTIL the condition is true.

In these three loops, you can use the LEAVE clause to terminate the loop.

DELIMITER $ drop procedure if exists simple_loop $ create procedure simple_loop () begin declare counter int default 0; my_simple_loop: loop set counter = counter 1; IF counter = 10 then leave my_simple_loop; end if; end loop my_simple_loop; select "I can count to 10"; END $ DELIMITER; error handling

1. If you think that the embedded SQL statement will return an empty record, or you want to use a cursor to capture the record returned by the SELECT statement, a NO FOUND error can prevent premature termination of the stored procedure.

2. If you think that an SQL statement may return an error (for example, violating the constraints), you can create an error handler to prevent the program from terminating. This process replaces your default error processing and continues program execution.

Interaction with databases

Most stored procedures involve various interactions with database tables, including four main interactions:

1. Place a single record returned by an SQL statement into a local variable.

2. Create a "cursor" to iterate the result set returned by the SQL statement.

3. Execute an SQL statement and return the execution result set to its calling program.

4. embed an SQL statement that does not regret the result set, such as INSERT, UPDATE, and DELETE.

Use select into for local variables

When you need to obtain query information from a single record data, you can use the select into syntax (whether using a single record, mixed data of multiple records, or multiple table connections ). In this case, you can follow an INTO clause in the SELECT statement to tell MySQL to whom the query data is returned.

DELIMITER $$ DROP PROCEDURE IF EXISTS customer_sales $$ CREATE PROCEDURE customer_sales(int_customer_id INT) READS SQL DATA BEGIN DECLARE total_sales NUMERIC(8, 2); SELECT SUM(sale_value) INTO total_sales FROM sales WHERE customer_id = in_customer_id; SELECT CONCAT('Total sales for ', in_customer_id, 'is', 'total_sales'); END; $$

Use cursor

Select into defines a single record query, but many applications require querying multi-record data. You can use the cursor in MySQL to achieve this, the cursor allows you to put one or more SQL result sets into the storage program variables, which is usually used to process individual records in the result set.

DELIMITER $$ DROP PROCEDURE cursor_example() READ SQL DATA BEGIN DECLARE l_employee_id INT; DECLARE l_salary NUMERIC(8, 2); DECLARE l_department_id INT; DECLARE done INT DEFAULT 0; DECLARE curl CURSOR FOR SELECT employee_id, salary, department_id FROM employees; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN curl; emp_loop : LOOP FETCH curl INTO l_employee_id, l_salary, l_department_id; IF done = 1 THEN LEAVE emp_loop; END IF; END LOOP emp_loop;

CLOSE curl;
END; $