创建存储函数的语法形式为

存储函数（一）—— 创建存储函数

2020-07-09 14:21:25

之前，我们列举不少mysql自带的函数，但是有些时候自带函数并不能很好满足我们的需求，此时就需要自定义存储函数了，存储函数与存储过程有些类似，简单来说就是封装一段sql代码，完成一种特定的功能，并返回结果。...

 

之前，我们列举不少mysql自带的函数，但是有些时候自带函数并不能很好满足我们的需求，此时就需要自定义存储函数了，存储函数与存储过程有些类似，简单来说就是封装一段sql代码，完成一种特定的功能，并返回结果。其语法如下：

 

CREATE FUNCTION 函数([参数类型 数据类型[,….]]) RETURNS 返回类型

　　BEGIN

　　　  SQL语句.....

　　　　RETURN (返回的数据)

　　END

 

 

与存储过程不同的是，存储函数中不能指定输出参数(OUT)和输入输出参数(INOUT)类型。存储函数只能指定输入类型而且不能带IN。同时存储函数可以通过RETURN命令将处理的结果返回给调用方。注意必须在参数列表后的RETURNS（ 该值的RETURNS多个S，务必留意）命令中预先指定返回值的类型。如下创建一个计算斐波那契数列的函数



 

 

这里命名存储函数时使用了【fn_】作为开头，这样可以更容易区分与【sp_】开头的存储过程，从上述语句可以看出前面在存储过程分析的流程语句也是可以用于存储函数的，同样的，DECLARE声明变量和SET设置变量也可用于存储函数，当然包括定义异常处理语句也是适应的,请注意执行存储函数使用的是select关键字，可同时执行多个存储函数，嗯，存储函数就这样定义，是不是跟存储过程很相似呢？但还是有区别的，这点留到后面分析。ok~，为了进一步熟悉存储函数，下面编写一个用于向user插入用户的存储函数：



 

 



 





 

 

 

create function fn_get_bom_child(pid VARCHAR(10))

returns varchar(4000)

BEGIN

DECLARE temp VARCHAR(10000);

DECLARE tempChild VARCHAR(4000);

SET temp = '';

SET tempChild = pid;

WHILE tempChild IS NOT NULL

DO

SET temp = CONCAT(temp,',',tempChild);

SELECT GROUP_CONCAT(comp_material_id) into tempChild from t_bom_info where FIND_IN_SET(parent_material_id,tempChild);

END WHILE;

RETURN temp;

END

 

 

select * from t_bom_info where FIND_IN_SET(parent_material_id,fn_get_bom_child('K00123'))

收起

展开全文

mysql 程序人生经验分享

MySQL - 存储过程和函数_创建存储过程(创建语法详解)

2020-07-13 21:41:50

创建存储过程 创建存储过程和函数是指将经常使用的一组SQL语句组合在一起，并将这些...其语法形式如下： CREATE PROCEDURE procedure_name([proc_param[,…]]) routine_body 在上述语句中，参数procedure_name表示所

创建存储过程

创建存储过程和函数是指将经常使用的一组SQL语句组合在一起，并将这些SQL语句当作一个整体存储在MySQL服务器中。存储程序可以分为存储过程和函数。在MySQL中创建存储过程使用的语句CREATE PROCEDURE。其语法形式如下：

CREATE PROCEDURE procedure_name([proc_param[,…]])    
routine_body

在上述语句中，参数procedure_name表示所要创建的存储过程名字，参数proc_param表示存储过程的参数，参数routine_body表示存储过程的SQL语句代码，可以用BEGIN…END来标志SQL语句的开始和结束。

提示: 在具体创建存储过程时，存储过程名不能与已经存在的存储过程名重名，实战中推荐存储过程名命名为procedure_xxx或者proc_xxx。
proc_param中每个参数的语法形式如下：

[IN|OUT|INOUT] param_name type

在上述语句中，每个参数由三部分组成，分别为输入/输出类型、参数名和参数类型。其中，输入/输出类型有三种类型，分别为IN（表示输入类型）、OUT（表示输出类型）、INOUT（表示输入/输出类型）。param_name表示参数名；type表示参数类型，可以是MySQL软件所支持的任意一个数据类型。

【示例11-1】

mysql>  use school;   #选择数据库school                                             
mysql> DELIMITER $$                                                                                
mysql> create PROCEDURE  proc_delete_student (IN sid int )                                   
        BEGIN 
         declare cid  int ;   #定义变量cid                            
         Select class_id into cid from student where id = sid;    #通过查询语句设置变量                                                                       
         delete from grade where id = sid;  #删除成绩表中的记录  
         delete from student where id = sid;   #删除学生表中的记录                                                    
         update class set count=count-1 where id = cid; #更新班级表中的记录   
        END;                                                                        
        $$
        DELIMITER ;                                                                 
mysql>  call proc_delete_student(2);    #调用存储过程

具体操作

在这里插入图片描述
这样就比较麻烦, 就要用到存储过程

在这里插入图片描述
这说明这段代码有问题, (逻辑问题)

结语:

时间: 2020-07-13

收起

展开全文

mysql 数据库 sql

在mysql中创建存储过程与函数

千次阅读 2018-12-21 17:14:14

1、创建存储过程和函数存储过程是一条或者多条SQL语句的集合，相当于批处理文件，但是作用不... 创建存储过程是通过”CREATE PROCEDURE”语句来创建，语法格式为： CREATE PROCEDURE sp_name ([proc_parame...

1、创建存储过程和函数

    存储过程是一条或者多条SQL语句的集合，相当于批处理文件，但是作用不仅仅限于批处理。使用存储过程将简化操作，减少冗余的步骤，同时还可减少操作过程的失误，提高效率。

（1）创建存储过程

         创建存储过程是通过”CREATE PROCEDURE”语句来创建，语法格式为：

CREATE PROCEDURE sp_name ([proc_parameter])
[characteristics…] routine_body

各选项说明：

         CREATE PROCEDURE：创件存储过程的关键字

         sp_name：存储过程名称

         proc_parameter：指定存储过程的参数列表，列表形式为：[ IN | OUT | INOUT ] param_name type

         characteristics：用于指定存储过程的特性

         routine_body：mysql sql语句内容，使用BEGINE…END来表示SQL代码的开始和结束。

# 创建存储过程前先将sql语句结束符改为//，以防止和默认的结束符冲突， 
mysql> delimiter //
mysql> create procedure proc()
    -> begin
    -> select * from course;
    -> end //
Query OK, 0 rows affected (0.08 sec)
# 存储过程创建完成后将结束符改为默认的结束符
mysql> delimiter ;
# 调用创建的存储过程
mysql> call proc();
+----+-------------+------------+
| id | course_name | teacher_id |
+----+-------------+------------+
|  1 | math        |          3 |
|  2 | english     |          2 |

（2）创建存储函数

         函数与存储过程最大的区别就是函数调用有返回值，调用存储过程用call语句，而调用函数就直接引用函数名+参数即可，创建存储函数使用”CREATE FUNCTION”语句来创建，语法格式为：

CREATE FUNCTION func_name ( param_name type )
RESTURNS type
[characteristic……] routine_body

主要参数说明：

         Param_name：参数名称

         Type：参数类型

         RETURNS type：函数返回数据的类型

         Characteristic：指定存储函数的特性

mysql> delimiter //
# 创建一个函数
mysql> create function stubak_update(param1 int)
    -> returns int
    -> begin
    -> update student_bak set gender=1 where sid=param1;
    -> select count(*) into @a from student_bak where sid>param1;
    -> return @a;
    -> end;
    -> //
mysql> delimiter ;
# 调用函数，并传入一个参数
mysql> select stubak_update(1); 
+------------------+
| stubak_update(1) |
+------------------+
|                7 |
+------------------+

2、存储过程中使用变量

（1）定义变量

         在存储过程中通过DECLARE语句定义变量，定义变量的格式如下：

         DECLARE var_name[,varname]… date_type [DEFAULT value];

         选项说明：

                   var_name：局部变量名称

                   DEFAULT value：用于给变量提供一个默认值

                   Type：用于指定变量的默认类型

（2）为变量赋值

         声明后的变量可以通过select … into var_list进行赋值，或者通过set语句赋值，或者通过定义游标并使用fetch … into var_list赋值,使用set赋值的方式如下：

  SET var_name = expr [, var_name = expr] ……;

# mysql存储过程中使用变量
mysql> delimiter //
mysql> create procedure sp1(v_sid int)
    -> begin
    -> declare xname varchar(10) default 'dayi123';
    -> declare xsex int;
    -> select sname,gender into xname,xsex from student_bak where sid=v_sid;
    -> select xname,xsex; 
    -> end;
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call sp1(1);
+--------+------+
| xname  | xsex |
+--------+------+
| Andrew |    1 |
+--------+------+    

 

3、定义条件和处理程序

         定义条件是事先定义程序执行过程中遇到的问题，处理程序定义了在遇到这些问题是应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。

（1）定义条件

         定义条件使用DECLARE语句，语法格式如下

  DECLARE condition_name CONDITION FOR [condition_type]
  [condition_type]:
  SQLSTATE [VALUE] sqlstate_value | mysql_error_code

各选项说明：

         Condition_name：表示条件名称

         Condition_type：表示条件类型

         sqlstate_value | mysql_error_code：mysql中的错误，sqlstate_value为长度为5的字符串类型错误代码，mysql_error_code为数值类型的错误代码

（2）定义处理程序

         定义处理程序时，使用DECLARE语句实现：

 DECLARE handler_type HANDLER FOR condition_value[,…] sp_statement handler_type:
          CONTINUT | EXIT | UNDO
  Condition_value：
          SQLSTATE [VALUE] sqlstate_value | condition_name
  | SQLWARNING | NOT FOUND | SQLEXCEPTION | mysql_error_code

各选项说明：

         Handler_type：错误处理方式，continue表示遇到错误不处理，exit为退出，undo为撤回之前操作。

         Condition_value表示错误类型，有以下的取值

                   SQLSTATE [VALUE] sqlstate_value：包含5个字符的字符串错误值

                   condition_name：declare condition定义错误的错误条件名称

                   SQLWARNING：匹配所有以01开头SQLSTATE错误代码

                   NOT FOUND：匹配所有以02开头的SQLSTATE错误代码

                   SQLEXCEPTION：匹配所有没有被SQLWARENING或NOT FOUNT捕获的SQLSTATE的错误代码

                   mysql_error_code：匹配数值类型的错误代码

         sp_statement：在遇到定义的错误时，需要执行的存储过程或函数。

当condition发生但没有声明handler时，则存储过程和函数依照如下规则处理：

         发生SQLEXCEPTION错误，则执行exit退出

         发生SQLWARNING警告，则执行contine继续执行

         发生NOT FOUND情况，则执行continue继续执行

# 创建一个存储过程并定义处理程序当错误吗为23000时跳过继续执行
# 创建一张用于测试的表
mysql> delimiter //
mysql> create procedure handlerdemo()
    -> begin
    -> declare continue handler for sqlstate '23000' set @x2=1;
    -> set @x=1;
    -> insert into t values(1);
    -> set @x=2;
    -> insert into t values(1);
    -> set @x=3;
    -> end;
    -> //
mysql> delimiter ;
# 调用存储过程
mysql> CALL handlerdemo();
# 插看变量的值
mysql> select @x;
+------+
| @x   |
+------+
|    3 |
+------+

4、存储过程中的流程控制语句

    MySQL支持if,case,iterate,leave,loop,while,repeat语句作为存储过程和函数中的流程控制语句，另外return语句也是函数中的特定流程控制语句。

（1）case语句

         Case语句有两种语句格式分别如下：

         格式一：

 CASE case_value
          WHEN when_value THEN statement_list
          [WHEN when_value THEN statement_list]……
          [ELSE statement_list]
  END CASE

         格式二：

 CASH 
          WHEN search_condition THEN statement_list
          [WHEN search_condition THEN statement_list]……
          [ELSE statement_list]
  END CASE

         创建存储过程时使用case语句

mysql> delimiter //
mysql> CREATE PROCEDURE exp_case(v_sid int)
    ->   BEGIN
    ->     DECLARE v INT DEFAULT 1;
    ->     select gender into v from student_bak where sid=v_sid;
    ->     CASE 
    ->       WHEN v=0 THEN update student_bak set gender=1 where sid=v_sid;
    ->       WHEN v=1 THEN update student_bak set gender=0 where sid=v_sid;
    ->       ELSE
    ->           update student_bak set gender=-1 where sid=v_sid;
    ->     END CASE;
    ->   END;
    -> //
mysql> delimiter ;

（2）if语句

         If语句包含多个条件判断，根据条件判断的结果为TRUE或FALSE执行相应的语句，语法格式为：

IF search_condition THEN statement_List
  [ELSEIF search_condition THEN statement_list] …
  [ELSE statement_list]
END IF

         创建一个函数，使用if语句判断两个数的大小

mysql> CREATE FUNCTION exp_if(n INT, m INT)
    ->   RETURNS VARCHAR(20)
    ->   BEGIN
    ->     DECLARE s VARCHAR(20);
    ->     IF n > m THEN SET s = '>';
    ->     ELSEIF n = m THEN SET s = '=';
    ->     ELSE SET s = '<';
    ->     END IF;
    ->     SET s = CONCAT(n, ' ', s, ' ', m);
    ->     RETURN s;
->   END //
mysql> DELIMITER ;
mysql> select exp_if(1,2);
+-------------+
| exp_if(1,2) |
+-------------+
| 1 < 2       |
+-------------+

         If语句也可在创建存储过程及函数时嵌套使用，

mysql> CREATE FUNCTION exp_if02 (n INT, m INT)
    ->   RETURNS VARCHAR(50)
    ->   BEGIN
    ->     DECLARE s VARCHAR(50);
    ->     IF n = m THEN SET s = 'equals';
    ->     ELSE
    ->       IF n > m THEN SET s = 'greater';
    ->       ELSE SET s = 'less';
    ->       END IF;
    ->       SET s = CONCAT('is ', s, ' than');
    ->     END IF;
    ->     SET s = CONCAT(n, ' ', s, ' ', m, '.');
    ->     RETURN s;
    ->   END //
mysql> delimiter ;

（3）iterate语句

         Iterate语句仅出现在loop,repeat,while循环语句中，其含义表示重新开始此循环，格式如下：

ITERATE label

         Label表示自定义的标签名

（4）leave语句

         Leave语句表明退出指定标签的流程控制语句块，通常会用在begin…end，以及loop,repeat,while的循环语句中，格式如下：

LEAVE label

         Label表示要退出的标签名

（5）LOOP语句

         Loop语句是存储过程或函数中表达循环执行的一种方式，LOOP内的语句一直重复执行直到循环被退出，跳出循环时使用LEVAVE子句，LOOP语句的基本格式如下：

[begin_label:] LOOP
  Statement_list
END LOOP [ END_LABEL ]

         创建存储过程使用loop循环语句实现变量的自增

mysql> DELIMITER //
# 创建存储过程
mysql> CREATE PROCEDURE exp_loop(p1 INT)
    -> BEGIN
    ->   label1: LOOP
    ->     SET p1 = p1 + 1;
    ->     IF p1 < 10 THEN
    ->       ITERATE label1;
    ->     END IF;
    ->     LEAVE label1;
    ->   END LOOP label1;
    ->   SET @x = p1;
    -> END //
mysql> DELIMITER ;
# 调用存储过程
mysql> call exp_loop(1);
# 查看执行后变量的值
mysql> select @x;
+------+
| @x   |
+------+
|   10 |
+------+

（6）repeat语句

         Repeat语句用于创建一个带条件判断的循环过程，每次语句执行完毕之后，会对条件表达式进行判断，如果表达式为真，则循环结束；否则重复执行循环中的语句。

[repeat_label:] REPEAT
  Statement_List
UNTIL search_condition
END REPEAT [repeat_label]

         Repeat_label为REPEAT语句的标注名称，该参数可省略

mysql> delimiter //
# 创建基于repeat循环的存储过程
mysql> CREATE PROCEDURE exp_rep(p1 INT)
    -> BEGIN
    ->   SET @x = 0;
    ->   REPEAT
    ->     SET @x = @x + 1;
    ->   UNTIL @x > p1 END REPEAT;
    -> END
    -> //
mysql> delimiter ;
# 调用存储过程，并查看变量@x的最终值
mysql> call exp_rep(100);
mysql> select @x;
+------+
| @x   |
+------+
|  101 |
+------+

（7）while语句

         While语句也是用于创建一个带条件判断的存储过程，与REPEAT不同的是while在执行语句时先对指定的表达式进行判断，如果为真，则执行循环内的语句，否则退出，语句格式如下：

[while_label:] WHILE expr_condition DO
  Statement_list
END WHILE [while_label]

         创建基于while循环的存储过程，相对于repeat循环是先判断在执行。

mysql> DELIMITER //
mysql> CREATE PROCEDURE exp_whi(p1 INT)
    -> BEGIN
    ->   SET @b = 0;
    ->   WHILE @b < p1 DO
    ->     SET @b = @b + 1;
    ->   END WHILE;
    -> END;
    -> //
mysql> DELIMITER ;
mysql> call exp_whi(100);
mysql> select @b;
+------+
| @b   |
+------+
|  100 |
+------+

5、游标

         Mysql查询语句可能返回多条记录，如果数据量大则需要在存储过程中和储存函数中使用游标来逐条读取查询结果中的记录。应用程序可以根据需要滚动或浏览其中的数据。

（1）声明游标

         游标必须在声明处理程序之前被声明，并且变量和条件必须在声明游标或处理程序之前被声明。声明游标的语句如下：

DECLARE cursor_name CURSOR FOR select_statement

（2）打开游标

         打开游标的语句如下：

OPEN cursor_name

（3）使用游标

         使用游标语句如下

FETCH cursor_name INTO var_name [,var_name] ……

         var_name：表示将光标中的SELECT 语句查询出来的信息存入该参数中，var_name必须在声明光标之前就定义好。

         使用游标时，数据集中的字段需要和INTO语句中定义的变量一一对应，数据集中的数据都fetch完之后，则返回NOT FOUND。

（4）关闭游标

         关闭游标语句如下：

CLOSE cursor_name 

（5）游标的使用

mysql> DELIMITER //
mysql> CREATE PROCEDURE exp_cur()
    -> BEGIN
    ->   DECLARE done INT DEFAULT FALSE;
    ->   DECLARE a CHAR(16);
    ->   DECLARE b, c INT;
    ->   DECLARE cur1 CURSOR FOR SELECT sname,dept_id FROM student;
    ->   DECLARE cur2 CURSOR FOR SELECT id FROM course;
    ->   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    ->   OPEN cur1;
    ->   OPEN cur2;
    ->   read_loop: LOOP
    ->     FETCH cur1 INTO a, b;
    ->     FETCH cur2 INTO c;
    ->     IF done THEN
    ->       LEAVE read_loop;
    ->     END IF;
    ->     IF b = c THEN
    ->       INSERT INTO test VALUES (a,b);
    ->     ELSE
    ->       INSERT INTO test VALUES (a,c);
    ->     END IF;
    ->   END LOOP;
    ->   CLOSE cur1;
    ->   CLOSE cur2;
-> END //
mysql> DELIMITER ;

6、查看创建的存储过程和函数

         在创建和存储过程和函数后可以通过”show status”语句或”show create”语句来查看创建的存储过程和函数，也可直接从系统的”information_schema”数据库中查询。

         使用”show status”语句查看存储过程和函数状态语句结构如下：

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE ‘pattern’]

# 查看创建的所有以”exp”开头的存储过程

mysql> show procedure status like 'exp%' \G
*************************** 1. row ***************************
                  Db: course
                Name: exp_case
                Type: PROCEDURE
             Definer: root@localhost
……

         使用”show create”查看存储和函数状态语句如下：

SHOW CREATE {PROCEDURE | FUNCTION} sp_name

         从”information_schema.routines”表中查看存储过程和函数信息的语句结构如下：

Select * from information_schema.routines where routing_name=’sp_name’;

7、修改创建的存储过程和函数

         存储过程及函数穿件完成后可以通过”alter”语句来修改存储过程或函数的特性，修改的语句如下：

ALTER {PROCEDURE|FUNCTION} sp_name [characteristic …]

Characteristic参数用于指定存储函数的特性，取值有：

         CONTAINS SQL：表示子程序包含SQL语句，但不包含读或写数据的语句。

         NO SQL:表示子程序中不包含SQL语句

         READS SQL DATA：表示子程序中包含读数据的语句

         MODIFIES SQL DATA：表示子程序中包含写数据的语句

         SQL SECURITY {DEFINER|INVOKER}：指明谁有权限来执行

         DEFINER：表示只有定义者自己才能够执行

         INVOKER：表名调用者可以执行

         COMMENT ‘string’：表示注释信息

8、删除存储过程及存储函数

         删除存储过程及存储函数使用”drop”语句来删除，语法格式如下：

DROP {PROCEDURE|FUNCTION} [IF EXISTS] sp_name

         删除一个创建的存储过程：

mysql> drop procedure exp_whi;
Query OK, 0 rows affected (0.00 sec)

9、mysql触发器

         触发器是一个特殊的存储过程，触发器的作用是当表上有对应SQL语句发生时，则触发执行。

（1）创建触发器

         创建触发器的语句如下：

CREATE
  [DEFINER = {user | CURRENT_USER }]
  TRIGGER trigger_name
  Trigger_time trigger_event
  ON tbl_name FOR EACH ROW
  [trigger_order]
  trigger_body

各选项说明：

         Definer：用来指定trigger的安全环境

         trigger_name：标识触发器的名称

         Trigger_time：指定触发器的执行时间，BEFORE代表在数据修改前执行，AFTER代表在修改后执行。

         Trigger_event：指定触发该触发器的具体事件，INSERT当新的一行数据插入表中时触发，UPDATE当表的一行数据被修改时触发，DELETE当表的一行数据被删除时触发，当执行insert into … on duplicate key update语句时，当碰到重复行执行update时，则触发update下的触发器

         tbl_name：标识建立触发器的表名

         Trigger_body：表示触发器触发之后要执行的一个或多个语句，在内部可以引用涉及表的字段，OLD.col_name表示行数据被修改或删除之前的字段数据，NEW.col_name表示行数据被插入或修改之后的字段数据

# 先创建一张表用于在触发触发器时备份要修改的数据
mysql> create table student_back(sid int,old_sname varchar(12),sname varchar(12),old_gender int,gender int,update_time time)//
mysql> delimiter //
# 创建触发器
mysql> create trigger update_trigger
    -> after update
    -> on student for each row
    -> begin
    -> insert into student_back values(old.sid,old.sname,new.sname,old.gender,new.gender,now());
    -> end;
    -> //
mysql> delimiter ;
mysql> update student set gender=1 where sid=1;
# 更新数据后查看备份的数据
mysql> select * from student_back;
+------+-----------+--------+------------+--------+-------------+
| sid  | old_sname | sname  | old_gender | gender | update_time |       
+------+-----------+--------+------------+--------+-------------+
|    1 | Andrew    | Andrew |          0 |      1 | 19:12:29    |
+------+-----------+--------+------------+--------+-------------+

（2）查看触发器

         触发器创建好后可以通过”show triggers”命令查看，也可在”triggers”表中查看触发器信息。

         通过命令查看：show triggers;

         通过表查看语句：select * from information_schema.triggers where trigger_name=' trigger_name ' \G

（3）删除触发器

         触发器可以通过”drop trigger”语句来删除，删除触发器的语句格式为：

DROP TRIGGER [schema_name.] trigger_name

         schema_name表示数据库的名称为可选参数

# 删除触发器
mysql> drop trigger update_trigger;                                                             
Query OK, 0 rows affected (0.00 sec)

收起

展开全文

MySQL存储函数

千次阅读 2019-01-11 17:38:43

MySQL存储函数（自定义函数），函数一般...在MySQL中，创建存储函数使用CREATE FUNCTION关键字，其基本形式如下： CREATE FUNCTION func_name ([param_name type[,...]]) RETURNS type [characteristic ...] B...

MySQL存储函数（自定义函数），函数一般用于计算和返回一个值，可以将经常需要使用的计算或功能写成一个函数。

1、创建存储函数

在MySQL中，创建存储函数使用CREATE FUNCTION关键字，其基本形式如下：

CREATE FUNCTION func_name ([param_name type[,...]])
RETURNS type
[characteristic ...] 
BEGIN
	routine_body
END;

参数说明：

（1）func_name ：存储函数的名称。

（2）param_name type：可选项，指定存储函数的参数。type参数用于指定存储函数的参数类型，该类型可以是MySQL数据库中所有支持的类型。

（3）RETURNS type：指定返回值的类型。

（4）characteristic：可选项，指定存储函数的特性。

（5）routine_body：SQL代码内容。

2、调用存储函数

在MySQL中，存储函数的使用方法与MySQL内部函数的使用方法基本相同。用户自定义的存储函数与MySQL内部函数性质相同。区别在于，存储函数是用户自定义的。而内部函数由MySQL自带。其语法结构如下：

SELECT func_name([parameter[,…]]);

示例：创建存储函数，实现根据用户编号，获取用户姓名功能。

（1）先创建tb_user（用户信息表），并添加数据。

-- 创建用户信息表
CREATE TABLE IF NOT EXISTS tb_user
(
	id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户编号',
	name VARCHAR(50) NOT NULL COMMENT '用户姓名'
) COMMENT = '用户信息表';

-- 添加数据
INSERT INTO tb_user(name) VALUES('pan_junbiao的博客');
INSERT INTO tb_user(name) VALUES('KevinPan');
INSERT INTO tb_user(name) VALUES('pan_junbiao');
INSERT INTO tb_user(name) VALUES('阿标');
INSERT INTO tb_user(name) VALUES('panjunbiao');
INSERT INTO tb_user(name) VALUES('pan_junbiao的CSDN博客');
INSERT INTO tb_user(name) VALUES('https://blog.csdn.net/pan_junbiao');

查询数据结果：



（2）创建存储函数

-- 创建存储函数
DROP FUNCTION IF EXISTS func_user;
CREATE FUNCTION func_user(in_id INT)
RETURNS VARCHAR(50)
BEGIN
	DECLARE out_name VARCHAR(50);

	SELECT name INTO out_name FROM tb_user
	WHERE id = in_id;

	RETURN out_name;
END;

（3）调用存储函数

-- 调用存储函数
SELECT func_user(1);
SELECT func_user(2);
SELECT func_user(3);
SELECT func_user(4);
SELECT func_user(5);
SELECT func_user(6);
SELECT func_user(7);

执行结果：



3、修改存储函数

MySQL中，通过ALTER FUNCTION 语句来修改存储函数，其语法格式如下：

ALTER FUNCTION func_name [characteristic ...]
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

上面这个语法结构是MySQL官方给出的，修改的内容可以包含SQL语句也可以不包含，既可以是读数据的SQL也可以是修改数据的SQL还有权限。此外在修改function的时候还需要注意你不能使用这个语句来修改函数的参数以及函数体，如果你想改变这些的话你就需要删除掉这个函数然后重新创建。

4、删除存储函数

MySQL中使用DROP FUNCTION语句来删除存储函数。

示例：删除存储函数。

DROP FUNCTION IF EXISTS func_user;

 

其它补充：

如果你在创建存储函数时提示以下错误：

[Err] 1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is

enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

这是我们开启了bin-log, 我们就必须指定我们的函数是否是

1 DETERMINISTIC 不确定的

2 NO SQL 没有SQl语句，当然也不会修改数据

3 READS SQL DATA 只是读取数据，当然也不会修改数据

4 MODIFIES SQL DATA 要修改数据

5 CONTAINS SQL 包含了SQL语句


其中在function里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数。

解决方法：

解决办法也有两种， 第一种是在创建子程序(存储过程、函数、触发器)时，声明为DETERMINISTIC或NO SQL与READS SQL DATA中的一个， 例如: CREATE DEFINER = CURRENT_USER PROCEDURE `NewProc`()     DETERMINISTIC BEGIN #Routine body goes here... END;;

第二种是信任子程序的创建者，禁止创建、修改子程序时对SUPER权限的要求，设置log_bin_trust_routine_creators全局系统变量为1。

设置方法有三种：

（1）在客户端上执行 SET GLOBAL log_bin_trust_function_creators = 1。

（2）MySQL启动时，加上--log-bin-trust-function-creators选贤，参数设置为1。

（3）在MySQL配置文件my.ini或my.cnf中的[mysqld]段上加log-bin-trust-function-creators=1。

收起

展开全文

MySQL 自定义函数

MySQL——创建存储过程和函数
千次阅读 2018-05-22 09:34:00
简单地说，存储过程就是一条或多条 SQL 语句的集合，可视为批文件，但是其作用不仅限于批处理。...1. 创建存储过程语法格式： CREATE PROCEDURE SP_name ( [ proc_parameter ] ) [ characteristi...
收起
从头开始学MySQL-------存储过程与存储函数（1）
万次阅读 多人点赞 2018-11-03 17:00:19
10.1.1 创建存储过程存储过程就是一条或者多条SQL语句的集合... 创建存储过程的语句为 CREATE PROCEDURE，创建存储函数的语句为CREATE FUNCTION。调用存储过程的语句为CALL。调用存储函数的形式就像调用MyS...
收起
MySQL存储过程和存储函数详细使用教程
千次阅读 2019-04-15 17:30:25
开始之前首先创建如下两张表,并初始化一些数据。 创建存储过程(CREATE PROCEDURE) ...基本语法格式如下： ...其中：CREATE PROCEDURE为创建存储过程的关键字；sp_name为存储过程的名称(唯一性，没有所谓的重...
收起
mysql 存储过程
Arduino 函数语法详解（含示例）三
千次阅读 多人点赞 2019-02-01 18:17:13
补救的办法是使用pinMode()函数设置为输出引脚。注意：数字13号引脚难以作为数字输入使用，因为大部分的控制板上使用了一颗LED与一个电阻连接到他。如果启动了内部的20K上拉电阻，他的电压将在1.7V左右，而不是...
收起
Arduino 串口通信基本函数
MySQL优化之——自定义存储过程和函数
千次阅读 2015-06-24 00:04:32
MYSQL中创建存储过程和函数分别使用CREATE PROCEDURE和CREATE FUNCTION 使用CALL语句来调用存储过程，存储过程也可以调用其他存储过程函数可以从语句外调用，能返回标量值 创建存储过程语法 CREATE PROCEDURE sp...
收起
mysql 优化
MYSQL存储过程和存储函数入门
万次阅读 多人点赞 2015-09-08 17:43:19
储存过程是一个可编程的函数，它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程...
收起
mysql 存储过程
oracle 存储过程函数创建删除参数传递函数查看包系统包
千次阅读 2011-10-27 17:31:07
认识存储过程和函数存储过程和函数也是一种PL/SQL块，是存入数据库的PL/SQL块。...* 存储过程和函数以命名的数据库对象形式存储于数据库当中。存储在数据库中的优点是很明显的，因为代码不保存在本
收起
oracle sql exception list
mysql中常用函数与存储过程的创建
千次阅读 2020-07-11 16:48:29
mysql中常用函数与存储过程的创建常用函数汇总数学函数字符串函数日期和时间函数条件判断函数系统函数加密函数其他函数自定义函数自定义变量的声明和赋值基本语法实例存储过程事务基本语法实例函数和存储过程的区别 ...
收起
mysql 数据库
数据库Oracle与Mysql语法对比：函数
2016-11-11 18:23:23
Oracle创建和删除存储函数创建函数，需要有 CREATE PROCEDURE或...创建存储函数的语法和创建存储过程的类似，即 CREATE [OR REPLACE] FUNCTION 函数名[(参数[IN] 数据类型…)] RETURN 数据类型 {AS|IS} [说明部
收起
数据库 mysql oracle
MySql存储过程与函数
千次阅读 2015-08-06 08:21:26
存储过程和函数是在数据库中定义一些SQL语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复的编写相同的SQL语句。而且，存储过程和函数是在MySQL服务器中...
收起
MySql
Python基本语法_函数属性 & 参数类型 & 偏函数的应用
千次阅读 2015-10-20 20:59:31
Python函数的创建函数的参数必备参数缺省参数命名参数不定长参数匿名参数最后前言Python除了有丰富的标准库之外，还可以自己定义满足自身需求的类和函数。本篇主要介绍如何创建Python自定义函数。软件环境 ...
收起
python
数据库存储过程和函数
千次阅读 2018-04-15 12:55:24
存储过程和函数的引入存储过程和函数是在数据库中定义一些 SQL 语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的 SQL 语句。...创建存储过程和函数 1，创建存储过程 CREATE PROCEDURE sp_name(...
收起
数据库
自定义存储过程和函数
千次阅读 2015-04-16 11:06:33
MYSQL中创建存储过程和函数分别使用CREATE PROCEDURE和CREATE FUNCTION 使用CALL语句来调用存储过程，存储过程也可以调用其他存储过程函数可以从语句外调用，能返回标量值 创建存储过程语法 ...
收起
mysql
MySQL优化系列（六）--存储过程和存储函数
千次阅读 2017-07-04 20:29:33
当一个大型系统在建立时，会发现，很多的SQL操作是有重叠的，个别计算是相同的，比如：业务系统中，计算一张工单的计算方式。当遇到这些情况时，我们运用存储过程就是...（3）存储函数概述；（4）存储函数使用、优化。
收起
mysql 数据库存储过程优化
学习SQL SERVER的存储过程-之一认识存储过程语法
千次阅读 2007-11-07 15:57:00
CREATE PROCEDURE创建存储过程，存储过程是保存起来的可以接受和返回用户提供的参数的 Transact-SQL 语句的集合。可以创建一个过程供永久使用，或在一个会话中临时使用（局部临时过程），或在所有会话中临时使用...
收起
sql server encryption output go
Oracle 存储过程与函数的创建以及二者区别
千次阅读 2018-06-14 23:17:37
--创建过程 ... --语法形式如下 CREATE [OR REPLACE] PROCEDURE pname [ (param1 [mode1] type1, param2 [mode2] type2, …)] IS | AS [variable1 type3 :=0; variable2 ...
收起
SQL语句 Oracle 过程
PL-SQL 存储函数和存储过程
千次阅读 2014-11-18 13:54:22
PL-SQL 存储函数和存储过程 ORACLE 提供可以把PL/SQL 程序存储在数据库中，并可以在任何地方来运行它。这样就叫存储过程或函数。过程和函数统称为PL/SQL子程序，他们是被命名的PL/SQL块，均存储在数据库中，并...
收起
oracle plsql procedure function in out
04MySQL----查看、修改存储过程和存储函数
千次阅读 2019-01-30 15:33:25
注意：关于示例所讲到的存储过程和函数及数据库表的创建，可通过查看之前的文章有进行介绍。...存储过程和存储函数创建以后，用户可以查看存储过程和存储函数的状态和定义。用户可以通过SHOW STATUS语句查看...
收起
SQL 创建索引，语法
千次阅读 2014-06-07 16:10:16
主键是唯一的，所以创建了一个主键的同时，也就这个字段创建了一个唯一的索引。SQL SERVER将主键默认定义为聚集索引，事实上，索引是否唯一与是否聚集是不相关的，聚集索引可以是唯一索引，也可以是非唯一索引； ...
收起
MySQL自定义函数与存储过程
千次阅读 2017-04-11 11:22:58
一、 Mysql 自定义函数自定义函数 (user-defined function UDF)是一种对mysql扩展的途径，其用法和内置函数相同。自定义函数的两个必要条件：1、参数（不是必有的，例如select version...创建自定义函数语法: 　
收起
mysql
Oracle--函数（创建、调用和删除）
2020-01-04 22:46:34
函数函数一般用于计算和返回一个值，可以将经常需要进行的计算写成函数。函数的调用是表达式的一部分，而过程的...创建函数的语法和创建存储过程的语法也很相似。它们在数据库内采用相同的规则，参数的传递也相同。...
收起
Oracle 调用删除
MySQL5.6存储过程和函数
千次阅读 2016-03-13 22:18:17
1.存储程序可以分为存储过程和函数，MySQL中创建存储过程和函数使用的语句分别是create procedure和create function,使用call语句来调用存储过程，只能用输出变量返回值。函数可以从语句外调用（通过引用函数名），...
收起
mysql
SQLServer之创建标量函数
千次阅读 2018-11-19 14:19:38
用户定义函数是接受参数、执行操作（例如复杂计算）并将操作结果以值的形式返回的 Transact-SQL 或公共语言运行时 (CLR) 例程。返回值可以是标量（单个）值或表。使用此语句可以创建可通过以下方式使用的...
收起
SQL中的存储过程和函数
千次阅读 2017-03-04 11:59:35
存储过程和函数 ...简单的说，存储过程就是一条或者多条SQL语句的集合。可以视为批文件，但是其作用不仅仅局限于批处理。本文主要介绍如何创建...Mysql创建存储过程和存储函数的语句分别为CREATE PROCEDURE和CR
收起
Oracle存储过程和自定义函数
万次阅读 2016-06-03 23:29:09
概述PL/SQL中的过程和函数（通常称为子程序）是PL/SQL块的一种特殊的类型，这种类型的子程序可以以编译的形式存放在数据库中，并为后续的程序块调用。相同点：完成特定功能的程序不同点：是否用return语句返回值...
收起
oracle
Oracle创建存储过程实例
千次阅读 2018-04-24 23:15:05
认识存储过程和函数存储过程和函数也是一种PL/SQL块，是存入数据库的PL/SQL块。...* 存储过程和函数以命名的数据库对象形式存储于数据库当中。存储在数据库中的优点是很明显的，因为代码不保存在本地，用
收起
oracle 数据库储存过程

存储函数（一）—— 创建存储函数

MySQL - 存储过程和函数_创建存储过程(创建语法详解)

创建存储过程

具体操作

结语:

在mysql中创建存储过程与函数

1、创建存储过程和函数

2、存储过程中使用变量

3、定义条件和处理程序

4、存储过程中的流程控制语句

5、游标

6、查看创建的存储过程和函数

7、修改创建的存储过程和函数

8、删除存储过程及存储函数

9、mysql触发器

MySQL存储函数

1、创建存储函数

2、调用存储函数

3、修改存储函数

4、删除存储函数

其它补充：

MySQL——创建存储过程和函数

从头开始学MySQL-------存储过程与存储函数（1）

MySQL存储过程和存储函数详细使用教程

Arduino 函数语法详解（含示例）三

MySQL优化之——自定义存储过程和函数

MYSQL存储过程和存储函数入门

oracle 存储过程 函数 创建 删除 参数 传递 函数 查看 包 系统包

mysql中常用函数与存储过程的创建

数据库Oracle与Mysql语法对比：函数

MySql存储过程与函数

Python基本语法_函数属性 & 参数类型 & 偏函数的应用

数据库 存储过程和函数

自定义存储过程和函数

MySQL优化系列（六）--存储过程和存储函数

学习SQL SERVER的存储过程-之一认识存储过程语法

Oracle 存储过程与函数的创建 以及二者区别

PL-SQL 存储函数和存储过程

04MySQL----查看、修改存储过程和存储函数

SQL 创建索引，语法

MySQL自定义函数与存储过程

Oracle--函数（创建、调用和删除）

MySQL5.6存储过程和函数

SQLServer之创建标量函数

SQL中的存储过程和函数

Oracle存储过程和自定义函数

Oracle创建存储过程实例

创建存储函数的语法形式为

oracle 存储过程函数创建删除参数传递函数查看包系统包

数据库存储过程和函数

Oracle 存储过程与函数的创建以及二者区别