mysql调用过程函数命令_MySql创建函数与过程，触发器， shell脚本与sql的相互调用。...

最新推荐文章于 2023-11-27 12:37:27 发布

TBLa

最新推荐文章于 2023-11-27 12:37:27 发布

阅读量313

点赞数

文章标签： mysql调用过程函数命令

本文链接：https://blog.csdn.net/weixin_35050881/article/details/113132730

版权

一：函数

1：创建数据库和表deptartment，

mysql> useDBSC;Databasechanged

mysql> create table deptartment(dept_name varchar(20),-> budget bigint(20),-> building varchar(20));

Query OK,0rows affected

mysql> insert into deptartment values('电子系',10000,'2号楼');

Query OK,1row affected

mysql> insert into deptartment values('通信系',40000,'3号楼');

Query OK,1row 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,1row affected

mysql> insert into instructor values(2,1000,'电子系');

Query OK,1row affected

mysql> insert into instructor values(3,1000,'电子系');

Query OK,1row affected

mysql> insert into instructor values(4,1000,'电子系');

Query OK,1row affected

mysql> insert into instructor values(5,1000,'电子系');

Query OK,1row affected

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

Query OK,1row affected

mysql> insert into instructor values(2,1000,'通信系');

Query OK,1row affected

mysql> insert into instructor values(3,1000,'通信系');

Query OK,1row affected

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

Query OK,1 row affected

4：创建函数

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(*)frominstructorwhere 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

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

mysql> DELIMITER //

create function dept_count(deptName varchar(20))returns integer

begin

declare d_count integer;select count(*) intod_countfrominstructorwhere instructor.dept_name =deptName;returnd_count;end//DELIMITER ;

Query OK,0rows 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 existsmysql> select * from deptartment where dept_name=simpleFun();+-----------+--------+----------+

| dept_name | budget | building |

+-----------+--------+----------+

| 电子系 | 10000 | 2号楼 |

+-----------+--------+----------+

1 row in set

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

drop procedure if exists pro1;

mysql> create procedurepro1()select 5

->;

Query OK,0rows affected

mysql>call pro1();+---+

| 5 |

+---+

| 5 |

+---+

1 row in setQuery OK,0rows affected

mysql>

mysql> drop procedure if exists pro1;

mysql> create procedurepro1()select * frominstructor;

Query OK,0rows 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 setQuery OK,0rows affected

mysql>

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 frominstructor;end //

--调用过程，将输出值，赋值给outValue变量

call pro3(10,@outValue) //

--查询输出值，@outValue 是用户变量

select @outValue //Query OK,0rows affected

Query OK,0rows affected

Query OK,1row affected+-----------+

| @outValue |

+-----------+

| 12 |

+-----------+

1 row in setmysql>

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

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

mysql> call pro1('电子系');//

+----+--------+-----------+

| id | salary | dept_name |

+----+--------+-----------+

| 1 | 1000 | 电子系 |

| 2 | 1000 | 电子系 |

| 3 | 1000 | 电子系 |

| 4 | 1000 | 电子系 |

| 5 | 1000 | 电子系 |

+----+--------+-----------+

5 rows in setQuery 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 proceduresalary()begin

declare i int default 0;while i<10doinsert into salarie values('wangwu',100);set i=i+1;end while;end //Query OK,0rows affected

Query OK,0rows affected

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

mysql> select * from salarie; //

+----------+--------+

| name | salary |

+----------+--------+

| zhangsan | 2000 |

| lisi | 2500 |

| wangwu | 3000 |

| wangwu | 100 |

+----------+--------+

13 rows in set

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

delimiter //

drop procedure if exists salary //

create proceduresalary()begin

declare i int default 0;

repeatinsert into salarie values('lisi',100);set i=i+1;

until i>5

endrepeat;end //

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

delimiter //

drop procedure if exists StatisticStore; //

CREATE PROCEDUREStatisticStore()BEGIN

declare i int default 0;

read_loop:loopif i>6 thenleave read_loop;end if;insert into salarie values('zhangsan',300);set i=i+1;endloop;END; //

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

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

mysql> select * fromsalarie;+----------+--------+----+

| 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 PROCEDUREaddMoney()BEGIN -- 定义的游标变量，用于接收查询出来的记录

declare oldSalary int;declare nid int;declare total int default 0;declare done int defaultfalse;declare cur cursor for select salary,id from salarie where salary<2700;

-- 定义的标记符done, 直到最后将游标中的数据全部取出，设置done为truedeclare continue HANDLER for not found set done =true;set total = 0;opencur;

read_loop:loopfetch cur intooldSalary,nid;if done thenleave read_loop;end if;update salarie set salary=salary+100 where id=nid;set total = total +oldSalary;endloop;closecur;selecttotal;END; //Query OK,0rows affected

Query OK,0rows affected

mysql> call addMoney;//

+-------+

| total |

+-------+

| 8200 |

+-------+

1 row in setQuery OK,0rows affected

mysql> select * fromsalarie;-> //

+----------+--------+----+

| 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 ONsalarieFOREACH ROWBEGIN

IF new.salary> 1000 THEN

SET new.salary = new.salary-200;END IF;END; //Query OK,0rows affected

Query OK,0rows affected

mysql> insert into salarie(name,salary) values('zhaoliu',1500);//Query OK,1row 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 ONsalarieFOREACH ROWBEGIN

IF new.salary> 4000 THEN

SET new.salary =old.salary;END IF;END; //Query OK,0rows affected

Query OK,0rows affected

mysql> update salarie set salary=7000 where id=28;//Query OK,0rows affected

Rows matched:1 Changed: 0 Warnings: 0mysql> 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 ONsalarie--新插入的行或更新的行的别名为nrow, 相当于mysql中的new 。例如：update salarie set salary=7000 where id=28;//

referencing new row asnrow--数据库中原来的旧数据别名为orow，相当于mysql中的old 。例如：| zhaoliu | 1300 | 28 |

referencing old row asorowFOREACH ROWBEGIN

IF nrow.salary> 1000 THEN

SET nrow.salary = orow.salary-200;END IF;END;

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

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

八：循环中使用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) doset n=n-1;select * from ((select * from tempTable) union (select * from salarie where id

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 setQuery OK,0 rows affected

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

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

create event e1 on schedule every 1week

call procedure1('hello');

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

TBLa

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql调用过程函数命令_MySql创建函数与过程，触发器， shell脚本与sql的相互调用。...

一：函数1：创建数据库和表deptartment，mysql> useDBSC;Databasechangedmysql> create table deptartment(dept_name varchar(20),-> budget bigint(20),-> building varchar(20));Query OK,0rows affectedmysql> ...
复制链接

扫一扫