给MySQL增加Sequence管理功能【分分快三全天计划网

作者:分分快三全天计划网站

>RETURNS子句

RETURNS子句指定函数的返回类型。可以使用NULL值来表示返回任意有效数据类型。

如果RETURN子句的返回值类型和此处定义的数据类型不一致会如何?这取决于创建函数的时候,SQL_MODE的影响行为。

如果SQL_MODE为strict模式的值(即指定了STRICT_ALL_TABLES或STRICT_TRANS_TABLES),将报1366错误。

除这种情况,如果返回值类型不一致,则返回值将被强制转换为指定的数据类型。例如,RETURNS子句指定返回一个ENUM或SET数据类型,但RETURN子句返回了一个整型,则返回值将强制转换为ENUM或SET成员对应的字符串(译者注:虽然ENUM允许存储数值,但强烈建议不要存储数值,因为非常容易混淆ENUM的索引值和实际存储的数值,因此这里直接说是字符串)。

MariaDB将在创建routine的时候保留系统变量SQL_MODE的值,以后任何时间调用routine时都使用该SQL_MODE值,而不管当前调用routine时的SQL MODE值是什么。

(4)显示存储过程

SHOW CREATE {PROCEDURE} sp_name

似于 SHOW CREATE TABLE,它返回一个可用来重新创建已命名子程序的确切字符串。

作者 json的博客

>CONTAINS SQL

CONTAINS SQL意味着函数包含了至少一条SQL语句,但是它不会读也不会写数据库。例如函数中包含了SET或DO子句。

存储过程(Stored Procedure)

(1)是一组为了完成特定功能的 SQL语句集,是利用 SQL Server 所提供的 Transact-SQL 语言所编写的程序。经编译后存储在数据库中。存储过程是数据库中的一个重要对象,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是由 流控制和 SQL 语句书写的过程,这个过程经编译和优化后存储在数据库服务器中,存储过程可由应用程序通过一个调用来执行,而且允许用户声明变量 。同时,存储过程可以接收和输出参数、返回执行存储过程的状态值,也可以嵌套调用。

(2) 存储过程的优点:

➢ 存储过程只在创造时进行编译,以后每次执行存储过程都不需再重新编译,而一 般 SQL 语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

➢ 当对数据库进行复杂操作时(如对多个表进行 Update、Insert、Query、Delete 时), 可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。

➢ 存储过程可以重复使用,可减少数据库开发人员的工作量。

➢ 安全性高,可设定只有某此用户才具有对指定存储过程的使用权。

Sequence 管理表 DROP TABLE IF EXISTS sequence; CREATE TABLE sequence ( name VARCHAR(50) NOT NULL, current_value INT NOT NULL, increment INT NOT NULL DEFAULT 1, PRIMARY KEY (na...

语法

CREATE [OR REPLACE]
    [DEFINER = {user | CURRENT_USER | role | CURRENT_ROLE }]
    [AGGREGATE] FUNCTION [IF NOT EXISTS] func_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...]
    RETURN func_body

func_parameter:
    param_name type

type:
    Any valid MariaDB data type

characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

func_body:
    Valid SQL procedure statement

(6)调用存储过程

CALL sp_name([parameter[,...]])

调用一个先前用 CREATE PROCEDURE 创建的程序。

CALL 语句可以用声明为 OUT 或的 INOUT 参数的参数给它的调用者传回值。它也“返回”

受影响的行数,客户端程序可以在 SQL 级别通过调用 ROW_COUNT()函数获得这个数,从

C 中是调用 the mysql_affected_rows() C API 函数来获得。

-- Sequence 管理表
DROP TABLE IF EXISTS sequence;
CREATE TABLE sequence (
         name VARCHAR(50) NOT NULL,
         current_value INT NOT NULL,
         increment INT NOT NULL DEFAULT 1,
         PRIMARY KEY (name)
) ENGINE=InnoDB;
 
-- 取当前值的函数
DROP FUNCTION IF EXISTS currval;
DELIMITER $
CREATE FUNCTION currval (seq_name VARCHAR(50))
         RETURNS INTEGER
         LANGUAGE SQL
         DETERMINISTIC
         CONTAINS SQL
         SQL SECURITY DEFINER
         COMMENT ''
BEGIN
         DECLARE value INTEGER;
         SET value = 0;
         SELECT current_value INTO value
                   FROM sequence
                   WHERE name = seq_name;
         RETURN value;
END
$
DELIMITER ;
 
-- 取下一个值的函数
DROP FUNCTION IF EXISTS nextval;
DELIMITER $
CREATE FUNCTION nextval (seq_name VARCHAR(50))
         RETURNS INTEGER
         LANGUAGE SQL
         DETERMINISTIC
         CONTAINS SQL
         SQL SECURITY DEFINER
         COMMENT ''
BEGIN
         UPDATE sequence
                   SET current_value = current_value increment
                   WHERE name = seq_name;
         RETURN currval(seq_name);
END
$
DELIMITER ;
 
-- 更新当前值的函数
DROP FUNCTION IF EXISTS setval;
DELIMITER $
CREATE FUNCTION setval (seq_name VARCHAR(50), value INTEGER)
         RETURNS INTEGER
         LANGUAGE SQL
         DETERMINISTIC
         CONTAINS SQL
         SQL SECURITY DEFINER
         COMMENT ''
BEGIN
         UPDATE sequence
                   SET current_value = value
                   WHERE name = seq_name;
         RETURN currval(seq_name);
END
$
DELIMITER ;
 
/*
-- 测试
INSERT INTO sequence VALUES ('TestSeq', 0, 1);
SELECT SETVAL('TestSeq', 10);
SELECT CURRVAL('TestSeq');
SELECT NEXTVAL('TestSeq');
*/

>IF NOT EXISTS

如果使用 IF NOT EXISTS 子句,那么当函数存在时,MariaDB将返回一个warning信息而不是直接返回错误。IF NOT EXISTS不能和OR REPLACE一起使用。

(3)删除存储过程

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

不能在一个存储过程中删除另一个存储过程,只能调用另一个存储过程

>NO SQL

NO SQL意味着什么?啥也不意味着。因为MariaDB目前除了SQL语言,不支持任何其他语言。

(1)创建存储过程

CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body

说明:

✓ DEFINER:指明使用存储过程的访问权限。

✓ sp_name: 存储过程名称。

✓ proc_parameter: [ IN | OUT | INOUT ] param_name type

        ☆ in:表示向存储过程中传入参数;存储过程默认为传入参数,所以参数 in 可以省略;

        ☆ out:表示向外传出参数;

        ☆ inout:表示定义的参数可传入存储过程,并可以被存储过程修改后传出存储过程;

        ☆ param_name:参数名;

        ☆ type:参数的类型,可以为 mysql 任何合法得数据类型。

        ☆ 如果有多个参数,参数之间可以用逗号进行分割。

✓ Characteristic: LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string'

        ☆ 这个 LANGUAGE SQL 子句是没有作用的。仅仅是为了说明下面过程的主体 使用 SQL 语言编写。这条是系统默认的。

        ☆ 如果程序或线程总是对同样的输入参数产生同样的结果,则被认为它是“确定 的 ” ( DETERMINISTIC ), 否 则 就 是 “ 非 确 定 ” 的 。 默 认 的 就 是 NOT DETERMINISTIC。         ☆ CONTAINS SQL 表示子程序不包含读或写数据的语句。

        ☆ NO SQL 表示子程序不包含 SQL 语句。

        ☆ READS SQL DATA 表示子程序包含读数据的语句,但不包含写数据的语句。

        ☆ MODIFIES SQL DATA 表示子程序包含写数据的语句。如果这些特征没有明 确给定,默认的是 CONTAINS SQL。

        ☆ SQL SECURITY 特征可以用来指定子程序该用创建子程序者的许可来执行, 还是使用调用者的许可来执行。默认值是 DEFINER。

        ☆ COMMENT 子句是一个 MySQL 的扩展,它可以被用来描述存储程序。这个 信息被 SHOW CREATE PROCEDURE 和 SHOW CREATE FUNCTION 语句来 显示。存储子程序不能使用 LOAD DATA INFILE。

        ☆ 特 征 子 句 也 有 默 认 值 , 如 果 省 略 了 就 相 当 于 : LANGUAGE SQL NOT DETERMINISTIC SQL SECURITY DEFINER COMMENT ''

✓ routine_body: 包含合法的 SQL 过程语句。可以使用复合语句语法, 复合语 句可以包含声明,循环和其它控制结构语句。

原文:https://mariadb.com/kb/en/library/create-function/
我提交到MariaDB官方手册的译文:https://mariadb.com/kb/zh-cn/create-function/

(10)存储过程的条件和异常处理程序

DECLARE handler_type HANDLER FOR condition_value[,...]

sp_statement

handler_type:

CONTINUE | EXIT | UNDO

condition_value:

SQLSTATE [VALUE] sqlstate_value | condition_name |

SQLWARNING | NOT FOUND | SQLEXCEPTION

这个语句指定每个可以处理一个或多个条件的处理程序。如果产生一个或多个条件,

指定的语句被执行。

对一个 CONTINUE 处理程序,当前子程序的执行在执行处理程序语句之后继续。对

于 EXIT 处理程序,当前 BEGIN...END 复合语句的执行被终止。UNDO 处理程序

类型语句还不被支持。

SQLWARNING 是对所有以 01 开头的 SQLSTATE 代码的速记。

NOT FOUND 是对所有以 02 开头的 SQLSTATE 代码的速记。

SQLEXCEPTION 是对所有没有被 SQLWARNING 或 NOT FOUND 捕获的 SQLSTATE

代码的速记。

>Security

要想调用函数,你必须要拥有该函数的EXECUTE权限。

MariaDB会自动为创建函数CREATE FUNCTION的用户授予EXECUTE 和 ALTER ROUTINE权限,即使使用了DEFINER子句。

每个函数都有一个关联的账号(即definer)。默认情况下,definer即为函数的创建者。可以使用DEFINER子句显式指定关联到其他账号上。要使用DEFINER,你必须要拥有SUPER权限。详细信息见:Account Names。

SQL SECURITY子句指定了当调用函数时所使用的权限。如果SQL SECURITY的值为INVOKER,则将使用函数调用者的权限去对比(即评估)函数体中的语句权限。如果SQL SECURITY的值为DEFINER,则总是使用definer用户的权限去评估函数体的权限。默认值为DEFINER。

通过该子句,你可以创建一个只允许某用户访问部分数据的函数。例如,你有一张存储了员工信息的表,并且你已经授予了用户roger对该表某些列(only on certain columns)的SELECT权限。

CREATE TABLE employees (name TINYTEXT, dept TINYTEXT, salary INT);
GRANT SELECT (name, dept) ON employees TO roger;

可以定义一个函数来获取部门中薪水最高的用户,并授予EXECUTE权限:

CREATE FUNCTION max_salary (dept TINYTEXT) RETURNS INT RETURN
  (SELECT MAX(salary) FROM employees WHERE employees.dept = dept);
GRANT EXECUTE ON FUNCTION max_salary TO roger;

由于SQL SECURITY的默认值为DEFINER,无论roger用户何时调用该函数,都会使用你的权限来执行其中的子查询。只要你有查询每个员工薪水的权限,即使函数调用者不具备直接查询薪水的权限,他们也能获取到每个部门的最高薪水。

(5)显示存储过程特征

SHOW {PROCEDURE} STATUS [LIKE 'pattern']

它返回子程序的特征,如数据库,名字,类型,创建者及创建和修改日期。

>OR REPLACE

如果使用了OR REPLACE子句,它的行为等价于:

DROP FUNCTION IF EXISTS function_name;
CREATE FUNCTION function_name ...;

但不会删除该函数已有的权限privileges。

(9)存储过程的注释语法

mysql 存储过程可使用两种风格的注释

➢ 双模杠:--,该风格一般用于单行注释

➢ c 风格:/* 注释内容 */, 一般用于多行注释

>READS SQL DATA

READS SQL DATA意味着函数中包含了从数据库中读取数据的语句,但是不会修改任何数据。例如函数中使用了不包含任何写操作的SELECT语句。

Mysql学习笔记(八)

>MODIFIES SQL DATA

MODIFIES SQL DATA意味着函数中包含了要修改数据库中数据的语句。例如函数中使用了类似于DELETE, UPDATE, INSERT, REPLACE或DDL类的语句。

(7)存储过程的变量:

声明变量:DECLARE var_name[,...] type [DEFAULT value]

变量赋值,SET 语句: SET var_name = expr [, var_name = expr] ...

变量赋值,SELECT ... INTO 语句 SELECT col_name[,...] INTO var_给MySQL增加Sequence管理功能【分分快三全天计划网站】。name[,...] table_expr

>LANGUAGE SQL

LANGUAGE SQL代表的是一个标准的SQL子句,它是为了移植性而存在的。但是,该子句在MariaDB中没有任何意义,因为MariaDB的存储函数中唯一支持的语言只有SQL。

(8)存储过程的语句

BEGIN...END 复合语句 [begin_label:] BEGIN [statement_list] END [end_label] 存储子程序可以使用 BEGIN ... END 复合语句来包含多个语句。statement_list 代表一个或多个语句的 列表。statement_list 之内每个语句都必须用分号(;)来结尾。 复合语句可以被标记。除非 begin_label 存在,否则 end_label 不能被给出,并且如果二者都存在,他们必须是同样的。

本文为mariadb官方手册:CREATE FUNCTION的译文。

(2)修改存储过程

ALTER {PROCEDURE} sp_name [characteristic ...]

说明:

✓ 这个语句可以被用来改变一个存储程序的特征。必须用 ALTER ROUTINE 权限

才可用此子程序。这个权限被自动授予子程序的创建者。

✓ 在 ALTER PROCEDURE 语句中,可以指定超过一个的改变。

>Character sets 和 collations

可以为函数声明使用任意有效的字符集和排序规则character set and collation给MySQL增加Sequence管理功能【分分快三全天计划网站】。。如果定义了它们,COLLATE属性需要定义在CHARACTER SET之后。

如果没有指定字符集和排序规则,则使用函数创建时的系统默认值。即使之后系统默认字符集和排序规则改变了,函数所使用的字符集也不会随之改变。这种情况下,应该重建函数并使用数据库所使用的字符集和排序规则。

>[NOT] DETERMINISTIC

如果函数根据给定的参数列表能够返回一个确定的结果,则该函数是确定的(deterministic)。如果函数的返回值 会因某些数据、变量、随机数或任意不确定的值而受影响,则函数是不确定的。此外,如果存储函数中使用了不确定的函数(如NOW()或CURRENT_TIMESTAMP()),则该存储函数也是不确定的。

如果优化器知道函数是确定的,它会选择一个更快更有效的执行计划。你可以使用DETERMINISTIC关键字来定义这个routine。如果你想显式将函数标记为不确定的(默认就是如此),可以使用NOT DETERMINISTIC关键字。

如果你将一个不确定的函数声明为DETERMINISTIC,将返回一个错误结果。如果你将一个确定的函数声明为NOT DETERMINISTIC,则某些情况下,该查询语句的性能将大幅降低。

[NOT] DETERMINISTIC子句还会影响二进制日志binary logging,因为日志中的语句格式无法 存储或替换不确定的语句。

CONTAINS SQL, NO SQL, READS SQL DATA 以及 MODIFIES SQL DATA是信息类的子句,它们告诉服务器该函数是做什么的。MariaDB不会对这些语句做任何语法检查。如果不指定这些语句,则默认使用CONTAINS SQL。

描述

可以使用CREATE FUNCTION语句创建一个新的存储函数stored function。要使用CREATE FUNCTION语句,必须要具备CREATE ROUTINE权限。

函数可以定义任意数量的参数,在函数体(func_body)部分会返回一个值。函数体部分可以是任意有效的SQL表达式,例如某些select语句。如果你有合适的权限,你完全可以像调用内置函数一样调用存储函数。关于权限的详细信息,见下文:Security。

此外,你也可以使用CREATE FUNCTION语句的变体格式来安装一个用户自定义函数(UDF)。关于UDF,详细信息见:CREATE FUNCTION (UDF)。

你可以使用一个圆括号包围SELECT作为func_body部分,正如使用子查询一样。但注意,SELECT语句必须返回单个值(标量值,即单行且单列的值)。调用函数时,如果SELECT语句返回了多列,则报1241的错误,如果SELECT语句返回了多行,则报1242的错误。为了保险,可以使用LIMIT子句保证只返回单行数据。

你可以使用BEGIN...END语句块替换这里的RETURN子句,但是在语句块中,必须要包含一个RETURN语句。当调用函数时,执行到RETURN子句时将立即返回其结果,在RETURN子句之后的语句都不会再执行。

默认情况下,函数是关联到默认数据库上的。如果要将函数显式关联到一个指定的数据库,可以在创建时使用全称db_name.func_name。如果创建的存储函数名和内置的函数名同名,则必须使用全称来调用它。

定义存储函数时,参数列表可以为空。如果指定参数名,则参数名不区分大小写。

每个参数都可以声明为任意有效的数据类型,但无法使用COLLATE属性。

示例

下面的函数示例使用了一个参数,并在函数中执行了一个SQL内置函数CONCAT(),最后返回结果。

CREATE FUNCTION hello (s CHAR(20))
    RETURNS CHAR(50) DETERMINISTIC
    RETURN CONCAT('Hello, ',s,'!');

SELECT hello('world');
 ---------------- 
| hello('world') |
 ---------------- 
| Hello, world!  |
 ---------------- 

你可以在函数内部使用一个语句块来操作数据(即使用DML),例如INSERT和UPDATE。下面的例子中创建了一个函数计数器,它使用了一个临时表来存储当前的值。因为语句块包含了语句终止符号";",因此必须首先使用DELIMITER语句改变语句的终止符,使得函数体中能够使用分号。更多信息见Delimiters in the mysql client。

CREATE TEMPORARY TABLE counter (c INT);
INSERT INTO counter VALUES (0);
DELIMITER //
CREATE FUNCTION counter () RETURNS INT
  BEGIN
    UPDATE counter SET c = c   1;
    RETURN (SELECT c FROM counter LIMIT 1);
  END //
DELIMITER ;

字符集和排序规则:

CREATE FUNCTION hello2 (s CHAR(20))
  RETURNS CHAR(50) CHARACTER SET 'utf8' COLLATE 'utf8_bin' DETERMINISTIC
  RETURN CONCAT('Hello, ',s,'!');

本文由分分快三计划发布,转载请注明来源

关键词: 分分快三计划 mysql学习笔记