Summary of usage of mysql storage Program

This article is based on Mysql storage program usage

• Storage Functions

The storage function returns the result to the caller.
Create an instance using a storage functionMysql> delimiter $ // set $ as the separator mysql> create function get_carname (car_id int) // The creation function parameter is car_id-> returns varchar (100) // function return value-> reads SQL data-> begin-> return (select name from car_info where id = car_id);-> end $ // Query OK, 0 rows affectedmysql> delimiter; mysql> select get_carname (100); // call the function getcarname (100); + -------------------------------------------------- + | get_carname (100) | + release + | Kia K2 2012 hatchback 1.6L AT Premium commemorative version 111122 | + ---------------------------------------------------- + 1 row in set
View storage functionsShow function status; show create function get_carname; show function status like 'Get _ car % ';
Delete storage functionDrop function get_carname;

• Stored Procedure

Create an instance in a stored procedureMysql> create procedure show_name (car_id int)-> begin-> select name from car_info where id = car_id;-> update car_info set name = '000000' where id = car_id; -> end $1304-PROCEDURE show_name already existsmysql> create procedure show_carname (car_id int)-> begin-> select name from car_info where id = car_id; -> update car_info set name = '000000' where id = car_id;-> end $ Query OK, 0 rows affectedmysql> call show_carname (123456 ); -> $ + release + | name | + ------------------------------------------------ + | Kia K2 2012 hatchback 1.6L AT Premium commemorative version 111122 | + release + 1 row in set
Parameter type of Stored ProcedureIN parameter: The caller transfers the IN parameter value to the OUT parameter of the process: the process assigns the value to the OUT parameter.
Mysql> create procedure get_carname (in car_id int, out car_name varchar (100)-> begin-> select name into car_name from car_info where id = car_id;-> end $ Query OK, 0 rows affectedmysql> delimiter; mysql> call get_carname (100, @ car_name); Query OK, 0 rows affectedmysql> select @ car_name; + ----------- + | @ car_name | + ----------- + | 123456 | + ----------- + 1 row in set
Introduction to Stored Procedure feature values

Language SQL by default, routine_boyd is composed of SQL

[NOT] DETERMINISTIC indicates whether the execution result of the stored procedure is correct. By default, the result is unknown.

Constains SQL subprograms contain SQL statements, but do not contain statements for reading and writing data. The default value is

No SQL subprograms do not contain SQL statements

Reads SQL DATA subprogram contains read DATA statements

The modifies SQL DATA subroutine contains the statement for writing DATA.

SQLSECURITY {DEFINER | INVOKER} indicates who has the permission to execute the task.

DEFINER can be executed only by the DEFINER. The default value is

INVOKER indicates that the caller can execute

Usage of variables in Stored ProceduresVariable definition: declare procedure_temp varchar (100); variable value: set procedure_temp = 'hello'; you can also assign the query result to the variable: select name into procedure_temp;
Mysql> create procedure procedure_test ()-> begin-> declare continue handler for sqlstate '000000' set @ x1 = 1;-> set @ x2 = 2; -> insert into web_car_brands (name) values ('dazhong ');-> set @ x2 = 3; -> the end $ red part indicates that mysql is executed in the form of errors, exceptions, and warnings. Currently, the following error types are supported: sqlwarning, not found, and sqlstate.
The use of process control statements in the stored procedure needs to be improved ......
Usage of optical mark during storageCreate procedure curser_test ()-> begin-> declare count int;-> declare car_name varchar (100);-> declare cur cursor for select name from car_info where id <10; // declare the cursor-> declare exit handler for not found close cur;-> set @ x1 = 0;-> set @ x2 = 0;-> open cur; /open the cursor-> repeat-> fetch cur into car_name; // fetch the cursor-> if (car_name = 'cadillac ')-> then set @ x1 = 1; -> else-> set @ x2 = 1;-> end if;-> until 0 end repeat;-> close cur; // close the cursor-> end $

• Trigger

When a data table is inserted, deleted, or updated, the trigger is automatically executed.
Trigger creation instanceMysql> delimiter $ mysql> create trigger trigger_test // trigger name: trigger_test-> after insert on car_info for each row begin // after the insert operation, execute-> insert into web_car_brands (name) values ('dahzong '); // action to be executed-> end;-> $
The statements triggered by the trigger are as follows: INSERT, DELETE, and UPDATE.
View triggersShow triggers; Delete triggerDrop trigger trigger_test; Trigger usage instructionsThe trigger is executed in the order of BEFORE trigger, row operation, and AFTER trigger. Any operation with an error will not continue to perform the remaining operations. For transaction operations, if errors occur during the transaction, the entire transaction will be rolled back. For non-transactions, if errors occur, the executed parts will not be rolled back.

• View

View instance CreationMysql> create view view_test as // create view view_test-> select name from car_info where id = 100; Query OK, 0 rows affected
ViewYou cannot view views through show views. You can view all tables and views through show tables. show table status like 'view % '; show create view view_test; select * from information_schema.views where table_name = 'view _ test'; // You can view it through INFORMATION_SCHEMA.VIEWS.
View DeletionDrop view view_test;
Mysql view restrictions:

1. The from keyword cannot contain subqueries.
2. The following types of view makeup updates: Aggregate functions (SUM, MIN, MAX, COUNT), DISTINCT, group by, HAVING, UNION, or UNION ALL
3. SELECT contains subqueries
4. JION
5. FORM a makeup update View

Advantages of using a view:

1. Data independence: you can avoid the impact of table result changes on users. Adding columns to the source table does not affect the view. To Modify columns in the source table, you only need to modify the corresponding column of the view.
2. Security: You can only access the result set displayed in the view. You can limit the number of rows and columns.
3. Simple: when using a view, you are not allowed to care about the table results, association conditions, and filtering conditions behind it, and directly present the result set of the corresponding conditions.

• Event

The event scheduler can schedule the database to execute an event at the scheduled time. By default, the event scheduler does not enable the event scheduler. view the event scheduler status: show variables like 'event _ scheduler '; enable event Scheduler: set global event_scheduler = ON;
Event creation instanceMysql> create event expire_web_session // create event expire_web_session-> on schedule every 4 hour // run once every four hours-> do-> delete from car_info where publish_date <current_timestamp-interval 1 day; // Query of the event to be executed OK, 0 rows affected
Stop an eventMysql> alter event expire_web_session disable; Enable an eventAlter event expire_session enable;