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 PROCEDURE and invoked（调用） using the CALL statement. 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 FUNCTION and 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 TRIGGER that 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 EVENT and invoked by（调用） the server according to schedule.事件由CREATE EVEN创建，服务器根据计划任务调用。
View: An object created with CREATE VIEW that 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)

posted @ 2024-01-25 14:30 中仕阅读(8) 评论(0) 编辑收藏举报

刷新页面返回顶部

Stored Objects

公告