Stored Objects
一、Stored Objects
https://dev.mysql.com/doc/refman/5.7/en/stored-objects.html
https://zhuanlan.zhihu.com/p/53401942
This chapter discusses stored database objects that are defined in terms of SQL code that is stored on the server for later execution.
本章讨论存储过程，它用SQL代码定义，存储在服务器端，可以后期执行
Stored objects include these object types:
-
Stored procedure: An object created with
CREATE PROCEDUREand invoked（调用） using theCALLstatement. A procedure does not have a return value but can modify its parameters for later inspection by the caller. It can also generate（产生） result sets to be returned to the client program. 存储过程：使用Create PROCEDURE创建使用CALL调用。一个存储过程没有返回值，但是它可以修改参数供调用这稍后使用。它还可以生产结果集返回给客户端程序。 -
Stored function: An object created with
CREATE FUNCTIONand used much like a built-in function. You invoke it in an expression and it returns a value during expression evaluation（求值）.存储函数：使用CREATE FUNCTION使用和内置函数很相似。你在表达式中调用它，并在表达式求值期间返回一个值。 -
Trigger: An object created with
CREATE TRIGGERthat is associated(相关的) with a table. A trigger is activated when a particular（特定的） event occurs for the table, such as an insert or update.触发器：使用CREATE TRIGGER创建，和一个数据库表关联，一个触发器在这个关联表特定的事件中激活，比如插入和更新。 -
Event: An object created with
CREATE EVENTand invoked by（调用） the server according to schedule.事件由CREATE EVEN创建，服务器根据计划任务调用。 -
View: An object created with
CREATE VIEWthat when referenced produces a result set. A view acts as a virtual table.视图使用CREATE VIEW创建，在调用的时候产生一个结果集。视图相当于一个虚拟表
Terminology used in this document reflects the stored object hierarchy:
这篇文章中应用的专业属于反映了存储过程的层次体系：
Stored routines include stored procedures and functions.存储例程包括存储过程和函数；
Stored programs include stored routines, triggers, and events. 存储程序包括存储例程，触发器，和事件；
Stored objects include stored programs and views.存储对象包括存储程序和视图。
二、存储过程语法
2.1、创建存储过程
create temporary table tbl_proc_test(num bigint); delimiter // create procedure proc_test1 (in total int,out res int) begin declare i int; set i=1; set res=1; if total <= 0 then set total=1; end if; while i<=total DO set res=res*i; insert into tbl_proc_test(num) values (res); set i=i+1; end while; end; // delimiter;
调用存储过程
(root@localhost) [db1]> call proc_test1(10,@a); Query OK, 1 row affected (0.01 sec)
查看结果
(root@localhost) [db1]> select @a;
+---------+
| @a |
+---------+
| 3628800 |
+---------+
1 row in set (0.00 sec)
(root@localhost) [db1]> select * from tbl_proc_test; +---------+ | num | +---------+ | 1 | | 2 | | 6 | | 24 | | 120 | | 720 | | 5040 | | 40320 | | 362880 | | 3628800 | +---------+ 10 rows in set (0.00 sec)
查看存储过程的状态
(root@localhost) [db1]> show procedure status\G *************************** 1. row *************************** Db: db1 Name: proc_test1 Type: PROCEDURE Definer: root@% Modified: 2024-01-23 19:40:32 Created: 2024-01-23 19:40:32 Security_type: DEFINER Comment: character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: latin1_swedish_ci 1 row in set (0.00 sec)
2.2、元数据中查看存储过程
进入information_schema
(root@localhost) [db1]> use information_schema;
查看状态
(root@localhost) [information_schema]> select * from ROUTINES \G *************************** 1. row *************************** SPECIFIC_NAME: proc_test1 ROUTINE_CATALOG: def ROUTINE_SCHEMA: db1 ROUTINE_NAME: proc_test1 ROUTINE_TYPE: PROCEDURE DATA_TYPE: CHARACTER_MAXIMUM_LENGTH: NULL CHARACTER_OCTET_LENGTH: NULL NUMERIC_PRECISION: NULL NUMERIC_SCALE: NULL DATETIME_PRECISION: NULL CHARACTER_SET_NAME: NULL COLLATION_NAME: NULL DTD_IDENTIFIER: NULL ROUTINE_BODY: SQL ROUTINE_DEFINITION: begin declare i int; set i=1; set res=1; if total <= 0 then set total=1; end if; while i<=total DO set res=res*i; insert into tbl_proc_test(num) values (res); set i=i+1; end while; end EXTERNAL_NAME: NULL EXTERNAL_LANGUAGE: NULL PARAMETER_STYLE: SQL IS_DETERMINISTIC: NO SQL_DATA_ACCESS: CONTAINS SQL SQL_PATH: NULL SECURITY_TYPE: DEFINER CREATED: 2024-01-23 19:40:32 LAST_ALTERED: 2024-01-23 19:40:32 SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION ROUTINE_COMMENT: DEFINER: root@% CHARACTER_SET_CLIENT: utf8 COLLATION_CONNECTION: utf8_general_ci DATABASE_COLLATION: latin1_swedish_ci 1 row in set (0.00 sec)
可视化工具查看
information_schema中存储过程的来源，如果要修改存储过程的信息，必须在mysql库下的proc表中修改
三、函数
3.1创建一个函数
(root@localhost) [db1]> create function func_test1(total int) -> returns int -> begin -> declare i int; -> declare res int; -> set i=1; -> set res=1; -> if total <= 0 then -> set total=1; -> end if; -> while i<=total DO -> set res=res*i; -> set i=i+1; -> end while; -> return res; -> end; // Query OK, 0 rows affected (0.01 sec)
调用函数
(root@localhost) [db1]> select func_test1(10); +----------------+ | func_test1(10) | +----------------+ | 3628800 | +----------------+ 1 row in set (0.04 sec)
四、触发器
创建触发器
delimiter // create trigger trg_upd_score before update on stu for each ROW BEGIN if new.score<0 THEN set new.score=0; elseif new.score>100 THEN set new.score=100; end if; end;//
查看触发器的使用情况
(root@localhost) [db1]> select * from stu; +------+--------+-------+ | name | course | score | +------+--------+-------+ | dd | cc | 1000 | | ddc | cc | 1000 | +------+--------+-------+ 2 rows in set (0.00 sec) (root@localhost) [db1]> update stu set score=10000 where name='dd'; Query OK, 1 row affected (0.05 sec) Rows matched: 1 Changed: 1 Warnings: 0 (root@localhost) [db1]> select * from stu; +------+--------+-------+ | name | course | score | +------+--------+-------+ | dd | cc | 100 | | ddc | cc | 1000 | +------+--------+-------+ 2 rows in set (0.00 sec)
五、视图
创建视图
create view v_salary as select emp_no,max(salary) from salaries group by emp_no;
物化视图
六、事件
(root@localhost) [db1]> show variables like 'event%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | OFF | +-----------------+-------+ 1 row in set (0.00 sec)
开启事件
(root@localhost) [db1]> set global event_scheduler=1; Query OK, 0 rows affected (0.00 sec)
