MySql创建函数与过程，触发器， shell脚本与sql的相互调用。

2024-03-30 02:36•linux•阅读 1341

一：函数

1：创建数据库和表deptartment，

mysql> use DBSC;
Database changed
mysql> create table deptartment(dept_name varchar(20),
    -> budget bigint(20),
    -> building varchar(20));
Query OK, 0 rows affected

mysql> insert into deptartment values('电子系',10000,'2号楼');
Query OK, 1 row affected

mysql> insert into deptartment values('通信系',40000,'3号楼');
Query OK, 1 row affected

mysql> insert into deptartment values('计算机系',100000,'6号楼');

2:创建表 instructor

create table instructor(id int,
salary int,
dept_name varchar(20),
foreign key(dept_name) references deptartment(dept_name)) ENGINE=innodb  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

提示错误：1005 - Can't create table 'dbsc.instructor' (errno: 150)

修改数据表deptartment 的执行引擎：

alter table deptartment engine=innodb;

依然提示错误，因为外键约束的字段必须为被应用的表的主键。修改 deptartment 中dept_name的定义。

alter table deptartment modify dept_name varchar(20) primary key;

3：向表instructor中添加数据。

mysql> insert into instructor values(1,1000,'电子系');
Query OK, 1 row affected

mysql> insert into instructor values(2,1000,'电子系');
Query OK, 1 row affected

mysql> insert into instructor values(3,1000,'电子系');
Query OK, 1 row affected

mysql> insert into instructor values(4,1000,'电子系');
Query OK, 1 row affected

mysql> insert into instructor values(5,1000,'电子系');
Query OK, 1 row affected

mysql> insert into instructor values(1,1000,'通信系');
Query OK, 1 row affected

mysql> insert into instructor values(2,1000,'通信系');
Query OK, 1 row affected

mysql> insert into instructor values(3,1000,'通信系');
Query OK, 1 row affected

mysql> insert into instructor values(1,1000,'计算机系');
Query OK, 1 row affected

4：创建函数

创建函数提示错误： https://blog.csdn.net/topasstem8/article/details/8216740/

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)

修改

mysql> set global log_bin_trust_function_creators=TRUE;

创建有参函数：数据库默认语句分隔符为;，DELIMITER // 将数据库语句执行分隔符改为//。

DELIMITER //
create function dept_count(deptName varchar(20))
    returns integer
    begin
    return(
        select count(*)
        from instructor
        where instructor.dept_name = deptName);
    end//
DELIMITER ;

5：使用函数，求出instructor中院系教师大于2的deptartment

mysql> select * from deptartment where dept_count(dept_name)>2;
+-----------+--------+----------+
| dept_name | budget | building |
+-----------+--------+----------+
| 电子系    |  10000 | 2号楼    |
| 通信系    |  40000 | 3号楼    |
+-----------+--------+----------+
2 rows in set

https://www.cnblogs.com/taiguyiba/p/6619027.html

6：在函数中定义变量，返回赋值后的变量。

mysql> DELIMITER //
create function dept_count(deptName varchar(20))
    returns integer
    begin
    declare d_count integer ;
        select count(*) into d_count
        from instructor
        where instructor.dept_name = deptName;
    return d_count;
    end//
DELIMITER ;
Query OK, 0 rows affected

mysql> select dept_count('电子系');
+----------------------+
| dept_count('电子系') |
+----------------------+
|                    5 |
+----------------------+
1 row in set

7：创建无参函数

mysql> CREATE FUNCTION simpleFun()RETURNS VARCHAR(20) RETURN "电子系";
1304 - FUNCTION simpleFun already exists
mysql> select * from deptartment where dept_name=simpleFun();
+-----------+--------+----------+
| dept_name | budget | building |
+-----------+--------+----------+
| 电子系    |  10000 | 2号楼    |
+-----------+--------+----------+
1 row in set

二：过程：使用一中的表 https://www.cnblogs.com/oskyhg/p/7679962.html

1：创建无参数的过程，查询的值直接返回。

drop procedure if exists pro1;
mysql> create procedure pro1()
    select 5
    -> ;
Query OK, 0 rows affected
mysql> call pro1();
+---+
| 5 |
+---+
| 5 |
+---+
1 row in set

Query OK, 0 rows affected

mysql>

mysql> drop procedure if exists pro1;
mysql> create procedure pro1()
    select * from instructor;
Query OK, 0 rows affected

mysql> call pro1();
+----+--------+-----------+
| id | salary | dept_name |
+----+--------+-----------+
|  1 |   1000 | 电子系    |
|  2 |   1000 | 电子系    |
|  3 |   1000 | 电子系    |
|  4 |   1000 | 电子系    |
|  5 |   1000 | 电子系    |
|  1 |   1000 | 通信系    |
|  2 |   1000 | 通信系    |
|  3 |   1000 | 通信系    |
|  1 |   1000 | 计算机系  |
+----+--------+-----------+
9 rows in set

Query OK, 0 rows affected

mysql>

2：创建有输入输出参数的过程。 mysql中的变量 https://blog.csdn.net/qq_34531925/article/details/79483312

mysql> -- 定义执行语句的分割符，遇到//就执行。
DELIMITER //
drop procedure if exists pro3 //
-- in输入参数和类型，out输出参数和类型
create procedure pro3(in parm1 int, out parm2 int)
begin
-- 定义自定义变量，局部变量
declare parm3 int;   
-- 对输入参数值进行判断
if parm1=10 then
set parm3=parm1; -- 对parm3进行赋值
else
set parm3=20;
end if;
-- 插入操作，自定义变量的值做为插入值
insert into instructor(id) values(parm3);
-- 查询结果赋值给输出参数
select count(*) into parm2 from instructor;
end //
-- 调用过程，将输出值，赋值给outValue变量
call pro3(10,@outValue) //
-- 查询输出值，@outValue 是用户变量
select @outValue //
Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 1 row affected

+-----------+
| @outValue |
+-----------+
|        12 |
+-----------+
1 row in set

mysql>

3：当没有输出参数时，会将过程中最后的select查询结果作为过程的结果

mysql> create procedure pro1(name varchar(20))
    select * from instructor where dept_name=name;//
Query OK, 0 rows affected

mysql> call pro1('电子系');//
+----+--------+-----------+
| id | salary | dept_name |
+----+--------+-----------+
|  1 |   1000 | 电子系    |
|  2 |   1000 | 电子系    |
|  3 |   1000 | 电子系    |
|  4 |   1000 | 电子系    |
|  5 |   1000 | 电子系    |
+----+--------+-----------+
5 rows in set

Query OK, 0 rows affected

三：函数和过程支持for while语句。

创建工资表

mysql> create table salarie(name varchar(20),salary int(11));
mysql> insert into salarie values('zhangsan',2000);
mysql> insert into salarie values('lisi',2500);
mysql> insert into salarie values('wangwu',3000);

1：使用过程中的while语句向sql表中添加数据

mysql> delimiter //
drop procedure if exists salary //
create procedure salary()
         begin
         declare i int default 0;
         while i<10 do
               insert into salarie values('wangwu',100); 
               set i=i+1;
        end while;
       end //
Query OK, 0 rows affected

Query OK, 0 rows affected

mysql> call salary();//
Query OK, 1 row affected

mysql> select * from salarie; //
+----------+--------+
| name     | salary |
+----------+--------+
| zhangsan |   2000 |
| lisi     |   2500 |
| wangwu   |   3000 |
| wangwu   |    100 |
| wangwu   |    100 |
| wangwu   |    100 |
| wangwu   |    100 |
| wangwu   |    100 |
| wangwu   |    100 |
| wangwu   |    100 |
| wangwu   |    100 |
| wangwu   |    100 |
| wangwu   |    100 |
+----------+--------+
13 rows in set

2:使用repeat语句向数据库中添加数据

delimiter //
drop procedure if exists salary //
create procedure salary()
         begin
         declare i int default 0;
         repeat
               insert into salarie values('lisi',100); 
               set i=i+1;
        until i>5
        end repeat;
       end //

3: 使用loop循环插入。 read_loop为起的loop名字，可以为任意名。

delimiter //
drop procedure if exists StatisticStore; //
CREATE PROCEDURE StatisticStore()
BEGIN 
    declare i int default 0;
    read_loop:loop            
    if i>6 then
        leave read_loop;
    end if;
                insert into salarie values('zhangsan',300);
    set i=i+1;
    end loop;
END; //

四：使用游标，对查询出来的记录进行迭代操作。游标只能在存储过程中使用。使用三中的表 https://blog.csdn.net/liguo9860/article/details/50848216

向表中添加一个自增的字段，用于区分各个记录。

alter table `salarie` add `id` int AUTO_INCREMENT UNIQUE;//

mysql> select * from salarie;
+----------+--------+----+
| name     | salary | id |
+----------+--------+----+
| zhangsan |   2000 |  1 |
| lisi     |   2500 |  2 |
| wangwu   |   3000 |  3 |
| wangwu   |    100 |  4 |
| wangwu   |    100 |  5 |
| wangwu   |    100 |  6 |
| wangwu   |    100 |  7 |
| wangwu   |    100 |  8 |
| wangwu   |    100 |  9 |
| wangwu   |    100 | 10 |
| wangwu   |    100 | 11 |
| wangwu   |    100 | 12 |
| wangwu   |    100 | 13 |
| lisi     |    100 | 14 |
| lisi     |    100 | 15 |
| lisi     |    100 | 16 |
| lisi     |    100 | 17 |
| lisi     |    100 | 18 |
| lisi     |    100 | 19 |
| zhangsan |    300 | 20 |
| zhangsan |    300 | 21 |
| zhangsan |    300 | 22 |
| zhangsan |    300 | 23 |
| zhangsan |    300 | 24 |
| zhangsan |    300 | 25 |
| zhangsan |    300 | 26 |
+----------+--------+----+
26 rows in set

向工资低于2700的员工每人加一百块钱工资的工程如下，并将工资低于2700的员工的总额统计出来。

mysql>  delimiter //
drop procedure if exists addMoney; //
CREATE PROCEDURE addMoney()
BEGIN
    -- 定义的游标变量，用于接收查询出来的记录
    declare oldSalary int;
    declare nid int;
    declare total int default 0;
    declare done int default false;
    declare cur cursor for select salary,id from salarie where salary<2700;
    -- 定义的标记符done, 直到最后将游标中的数据全部取出，设置done为true
    declare continue HANDLER for not found set done = true;
    set total = 0;
    open cur;
    read_loop:loop
    fetch cur into oldSalary,nid;
    if done then
        leave read_loop;
    end if;
                update salarie set salary=salary+100 where id=nid;
    set total = total + oldSalary;
    end loop;
    close cur;
    select total;
END; //
Query OK, 0 rows affected

Query OK, 0 rows affected

mysql> call addMoney;//
+-------+
| total |
+-------+
|  8200 |
+-------+
1 row in set

Query OK, 0 rows affected

mysql> select * from salarie;
    -> //
+----------+--------+----+
| name     | salary | id |
+----------+--------+----+
| zhangsan |   2100 |  1 |
| lisi     |   2600 |  2 |
| wangwu   |   3000 |  3 |
| wangwu   |    200 |  4 |
| wangwu   |    200 |  5 |
| wangwu   |    200 |  6 |
| wangwu   |    200 |  7 |
| wangwu   |    200 |  8 |
| wangwu   |    200 |  9 |
| wangwu   |    200 | 10 |
| wangwu   |    200 | 11 |
| wangwu   |    200 | 12 |
| wangwu   |    200 | 13 |
| lisi     |    200 | 14 |
| lisi     |    200 | 15 |
| lisi     |    200 | 16 |
| lisi     |    200 | 17 |
| lisi     |    200 | 18 |
| lisi     |    200 | 19 |
| zhangsan |    400 | 20 |
| zhangsan |    400 | 21 |
| zhangsan |    400 | 22 |
| zhangsan |    400 | 23 |
| zhangsan |    400 | 24 |
| zhangsan |    400 | 25 |
| zhangsan |    400 | 26 |
+----------+--------+----+
26 rows in set

五：触发器

FOR EACH ROW，可以迭代取出每一行中的列的数据。

创建触发器，并使用。创建插入触发器，当新加入的薪水大于1000时，减去200。

mysql> drop trigger if exists BeforeInsert; //

CREATE TRIGGER BeforeInsert BEFORE insert ON salarie
FOR EACH ROW
BEGIN  
    IF new.salary> 1000 THEN  
        SET new.salary = new.salary-200;  
    END IF;
END; //
Query OK, 0 rows affected

Query OK, 0 rows affected

mysql> insert into salarie(name,salary) values('zhaoliu',1500);//
Query OK, 1 row affected

mysql> select * from salarie;//
+----------+--------+----+
| name     | salary | id |
+----------+--------+----+
| zhangsan |   2100 |  1 |
| lisi     |   2600 |  2 |
| wangwu   |   3000 |  3 |
| wangwu   |    200 |  4 |
| wangwu   |    200 |  5 |
| wangwu   |    200 |  6 |
| wangwu   |    200 |  7 |
| wangwu   |    200 |  8 |
| wangwu   |    200 |  9 |
| wangwu   |    200 | 10 |
| wangwu   |    200 | 11 |
| wangwu   |    200 | 12 |
| wangwu   |    200 | 13 |
| lisi     |    200 | 14 |
| lisi     |    200 | 15 |
| lisi     |    200 | 16 |
| lisi     |    200 | 17 |
| lisi     |    200 | 18 |
| lisi     |    200 | 19 |
| zhangsan |    400 | 20 |
| zhangsan |    400 | 21 |
| zhangsan |    400 | 22 |
| zhangsan |    400 | 23 |
| zhangsan |    400 | 24 |
| zhangsan |    400 | 25 |
| zhangsan |    400 | 26 |
| zhaoliu  |   1300 | 28 |
+----------+--------+----+
27 rows in set

创建更新触发器，new 代表更新的数据，准备插入的，old代表原来的数据。

当给一个员工改变薪水时，如果改变的薪水大于4000，那么还按原来的薪水。

mysql> drop trigger if exists BeforeUpdate; //

CREATE TRIGGER BeforeUpdate BEFORE update ON salarie
FOR EACH ROW
BEGIN  
    IF new.salary> 4000 THEN  
        SET new.salary = old.salary;  
    END IF;
END; //
Query OK, 0 rows affected

Query OK, 0 rows affected

mysql> update salarie set salary=7000 where id=28;//
Query OK, 0 rows affected
Rows matched: 1  Changed: 0  Warnings: 0

mysql> select * from salarie;//
+----------+--------+----+
| name     | salary | id |
+----------+--------+----+
| zhangsan |   2100 |  1 |
| lisi     |   2600 |  2 |
| wangwu   |   3000 |  3 |
| wangwu   |    200 |  4 |
| wangwu   |    200 |  5 |
| wangwu   |    200 |  6 |
| wangwu   |    200 |  7 |
| wangwu   |    200 |  8 |
| wangwu   |    200 |  9 |
| wangwu   |    200 | 10 |
| wangwu   |    200 | 11 |
| wangwu   |    200 | 12 |
| wangwu   |    200 | 13 |
| lisi     |    200 | 14 |
| lisi     |    200 | 15 |
| lisi     |    200 | 16 |
| lisi     |    200 | 17 |
| lisi     |    200 | 18 |
| lisi     |    200 | 19 |
| zhangsan |    400 | 20 |
| zhangsan |    400 | 21 |
| zhangsan |    400 | 22 |
| zhangsan |    400 | 23 |
| zhangsan |    400 | 24 |
| zhangsan |    400 | 25 |
| zhangsan |    400 | 26 |
| zhaoliu  |   1300 | 28 |
+----------+--------+----+
27 rows in set

mysql中不用给新的数据或旧的数据起别名，默认为new，old

其它数据库的更新或插入：

CREATE TRIGGER TestField1_BeforeInsert BEFORE INSERT ON salarie  
-- 新插入的行或更新的行的别名为nrow, 相当于mysql中的new 。 例如：update salarie set salary=7000 where id=28;//
referencing new row as nrow

-- 数据库中原来的旧数据别名为orow，相当于mysql中的old 。例如：| zhaoliu  |   1300 | 28 |
referencing old row as orow
FOR EACH ROW  
BEGIN  
    IF nrow.salary> 1000 THEN  
        SET nrow.salary = orow.salary-200;  
    END IF;  
END;

六：递归mysql不支持with recursive 递归查询语句，只能自己写循环语句迭代 https://blog.csdn.net/wickedvalley/article/details/78925041

七：sql与shell脚本的相互调用。

window上面sql与c++的相互调用。 https://blog.csdn.net/swotcoder/article/details/18524

https://blog.csdn.net/shaoyiwenet/article/details/53256103

触发器调用shell文件，shell读取mysql中最新插入的一行，读取邮箱用户名，然后发送邮件给客户。

触发器中执行shll命令， https://zhidao.baidu.com/question/2271230050936210028.html

八：循环中使用union

delimiter //
drop procedure if exists tile;//
drop table if exists tempTable;//
create temporary table tempTable as select * from salarie where id<3;//
create procedure tile(n int)
    -- set @tb:=table(id int(11),salary int(11), name varchar(20));
    begin
      while(n>0) do
         set n=n-1;
         select * from ((select * from tempTable) union (select * from salarie where id<(10+n)))t3;
      end while;
      -- select * from tempTable;
    end//
delimiter;

mysql> call tile(3);
+----------+--------+----+
| name     | salary | id |
+----------+--------+----+
| zhangsan |   2100 |  1 |
| lisi     |   2600 |  2 |
| wangwu   |   3000 |  3 |
| wangwu   |    200 |  4 |
| wangwu   |    200 |  5 |
| wangwu   |    200 |  6 |
| wangwu   |    200 |  7 |
| wangwu   |    200 |  8 |
| wangwu   |    200 |  9 |
| wangwu   |    200 | 10 |
| wangwu   |    200 | 11 |
+----------+--------+----+
11 rows in set

+----------+--------+----+
| name     | salary | id |
+----------+--------+----+
| zhangsan |   2100 |  1 |
| lisi     |   2600 |  2 |
| wangwu   |   3000 |  3 |
| wangwu   |    200 |  4 |
| wangwu   |    200 |  5 |
| wangwu   |    200 |  6 |
| wangwu   |    200 |  7 |
| wangwu   |    200 |  8 |
| wangwu   |    200 |  9 |
| wangwu   |    200 | 10 |
+----------+--------+----+
10 rows in set

+----------+--------+----+
| name     | salary | id |
+----------+--------+----+
| zhangsan |   2100 |  1 |
| lisi     |   2600 |  2 |
| wangwu   |   3000 |  3 |
| wangwu   |    200 |  4 |
| wangwu   |    200 |  5 |
| wangwu   |    200 |  6 |
| wangwu   |    200 |  7 |
| wangwu   |    200 |  8 |
| wangwu   |    200 |  9 |
+----------+--------+----+
9 rows in set

Query OK, 0 rows affected

九：事件，事件可以定时执行一些任务等。

如下：创建一个事件e1，每周执行一次过程procedure1

create event e1 on schedule every 1 week
    do
    call procedure1('hello');

mysql 误操作之后的回滚，当mysql误操作之后，又没有开启事务，可以用一下方法回滚。就是生成与误操作相反的语句。

https://www.jb51.net/article/99553.htm

MySql创建函数与过程，触发器， shell脚本与sql的相互调用。

相关推荐

[MySQL+PHP] 触发器及存储过程等MySQL功能在PHP中实现的坑

Python脚本的编写过程，例子--备份文件

php自动生成mysql的触发代码。

Oracle 11g R2性能优化 SQL TRACE

Oracle9i + PL_SQL学习笔记

当Shell遇上了NodeJS

C# Sql 触发器

VB与数据库的交互经典例子总结