数据库之存储过程

作者:神秘网友 发布时间:2021-02-27 21:20:05

数据库之存储过程

存储过程

1.什么是存储过程

存储过程就类似于python中的自定义的函数,它的内部包含了一系列可以执行的sql语句,存储过程存放于mysql服务端中,你可以直接通过调用存储过程触发内部sql语句的执行

2.存储过程的基本使用

procedure /pr??si?d??(r) 程序, 过程

-- 创建语法
delimiter //
create procedure 存储过程的名字([形参1,形参2,...])
begin
	sql代码
end //
delimiter ;

-- mysql 中调用存储过程语法
call 存储过程的名字([实参1,实参2,...]);

-- python中基于pymysql调用
cursor.callproc("存储过程的名字")
print(cursor.fetchall())

3.三种开发模式: 程序与数据库结合开发

# 第一种
"""  
应用程序: 程序员写业务代码开发
mysql: dba提前编写好存储过程,供应用程序调用
好处: 开发效率提升了,执行效率也上去了
缺点: 考虑到人为因素,需要跨部门沟通问题,后续的存储过程的扩展性差
"""

# 第二种
"""
应用程序: 程序员写代码开发之外 涉及到数据库操作也要自己手写
优点: 解决了跨部门不方便扩展的问题
缺点: 开发效率低,都是程序员干活.程序员不仅要会多种编程语言,还要考虑sql语句的执行效率问题
"""

# 第三种
"""
应用程序: 程序员只写程序代码 不写sql语句了.而是用别人写好的操作sql的框架即可
比如: 后面要讲的ORM框架对象关系映射
优点: 开发效率比前俩种开发效率都要高
缺点: 语句的扩展性差,可能会出现效率低下的问题.
"""
# 第一种基本不用,一般都是第三种方法,当出现效率低下的情况,再去手动的写一些sql语句

4.创建存储过程并调用(无参)

创建库并准备表和数据

create database procedure_test charset utf8;
use procedure_test;

CREATE TABLE blog (
    id INT PRIMARY KEY auto_increment,
    NAME CHAR (32),
    sub_time datetime
);

INSERT INTO blog (NAME, sub_time)
VALUES
    ('第1篇','2015-03-01 11:31:21'),
    ('第2篇','2015-03-11 16:31:21'),
    ('第3篇','2016-07-01 10:21:31'),
    ('第4篇','2016-07-22 09:23:21'),
    ('第5篇','2016-07-23 10:11:11'),
    ('第6篇','2016-07-25 11:21:31'),
    ('第7篇','2017-03-01 15:33:21'),
    ('第8篇','2017-03-01 17:32:21'),
    ('第9篇','2017-03-01 18:31:21');

mysql种创建及调用

delimiter //
create procedure p1()
begin
	select * from blog;
	insert into blog(name,sub_time) values('ldsb',now());
end //
delimiter ;

# 调用
call p1();

python中基于pymysql调用

import pymysql

conn = pymysql.connect(
	host = '127.0.0.1',
    port=3306,
    user='root',
    password='jzd123',
    database='procedure_test',
    charset='utf8'
)

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 在python中 通过游标下的callproc方法调用存储过程p1
cursor.callproc('p1')
print(cursor.fetchall())

cursor.close()
conn.close()

5.创建存储过程并调用(有参)

对于存储过程, 可以接受参数,其参数有三类

in , out 和 inout

-- n n int      --- 声明变量n为外部传入的值, 类型为int. 可以为存储过程定义的形参in直接传值,
-- out res int  --- 声明变量res为返回值, 类型为int. 为存储过程定义的形参out传值, 需要实现使用set声明才能传.
-- inout x int  ---  声明变量x既能收值又能返回值. 虽然inout既能接收又能充当返回值, 但是为了保证int和out的传值的统一性, 因此也需要事先使用set声明才能传.
  • in: 传入参数
Copydelimiter //
create procedure p2(
    in n1 int,
    in n2 int
)
BEGIN
    select * from blog where id  n1;
END //
delimiter ;


# 在mysql中调用
call p2(3,2)

# 在python中基于pymysql调用
cursor.callproc('p2',(3,2))
print(cursor.fetchall())
  • out:返回值
Copydelimiter //
create procedure p3(
    in n1 int,
    out res int
)
BEGIN
    select * from blog where id  n1;
    set res = 1;
END //
delimiter ;

# 在mysql中调用
set @res=0;       # 定义全局变量用于给out定义的res形参. 0代表假(执行失败),1代表真(执行成功).
call p3(3,@res);  # 调用存储过程
select @res;      # 查看存储过程

# 在python中基于pymysql调用
cursor.callproc('p3',(3,0)) # 0相当于set @res=0
print(cursor.fetchall())    #查询select的查询结果

cursor.execute('select @_p3_0,@_p3_1;') # @p3_0代表第一个参数,@p3_1代表第二个参数,即返回值
print(cursor.fetchall()
  • inout: 既可以传入又可以返回
Copydelimiter //
create procedure p4(
    inout n1 int
)
BEGIN
    select * from blog where id  n1;
    set n1 = 1;
END //
delimiter ;

#在mysql中调用
set @x=3;
call p4(@x);
select @x;


# 在python中基于pymysql调用
cursor.callproc('p4',(3,))
print(cursor.fetchall()) # 查询select的查询结果

cursor.execute('select @_p4_0;') 
print(cursor.fetchall())

6. 执行存储过程: int+out

  • 创建库并准备表和数据
Copydrop database procedure_test;  # 小心
create database procedure_test charset utf8;
use procedure_test;

create table emp(
  id int not null unique auto_increment,
  name varchar(20) not null,
  sex enum('male','female') not null default 'male', #大部分是男的
  age int(3) unsigned not null default 28,
  hire_date date not null,
  post varchar(50),
  post_comment varchar(100),
  salary double(15,2),
  office int, #一个部门一个屋子
  depart_id int
);

#插入记录
#三个部门:教学,销售,运营
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
('jason','male',18,'20170301','张江第一帅形象代言',7300.33,401,1), #以下是教学部
('tom','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tony','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','male',48,'20101111','teacher',10000,401,1),
('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('呵呵','female',38,'20101101','sale',2000.35,402,2),
('西西','female',18,'20110312','sale',1000.37,402,2),
('乐乐','female',18,'20160513','sale',3000.29,402,2),
('拉拉','female',28,'20170127','sale',4000.33,402,2),
('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3);
  • mysql中创建及调用
Copydrop procedure p1;

# 定义存储过程
delimiter $$
create procedure p1(
    in m int,  # 该形参m只负责接收值, m不能当作参数返回。
    in n int,  
    out res int  # 该形参res只负责返回值,不能直接接收参数,需要使用set先定义变量。 针对out存储过程的返回值参数一定要设置一个变量, 通常用返回值判断你这天sql语句运行成功与否, 返回值为0通常代表成功
)
begin
    select tname from teacher where tidm and tidn;
    set res=0;  # 将res变量修改, 用来标识当前的存储过程代码确实执行了。(修改成啥都行)
end  $$
delimiter ;

# 定义变量
set @xxx=10;  # 争对形参res不能直接传数据,因该传一个变量命。

# 查看变量对应得值
set @xxx;

# 调用存储过程
"""
第一个第二个参数: 为存储过程in定义的参数传值
第二个参数: 为存储过程out定义的返回值传值, out需要使用set声明才能传
"""
call p1(1, 10, @xxx);

# 删除存储过程
drop procedure p1;
  • pymysql模块使用存储过程
Copyimport pymysql

conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='jzd123',
    database='procedure_test',
    charset='utf8',
)

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 调用存储过程
cursor.callproc('p1', (1, 5, 10))
"""
pymysql底层实现. 
set @_p1_0 = 1
set @_p1_1 = 5
set @_p1_2 = 10
"""
print(cursor.fetchall())  

# 执行select语句验证
cursor.execute('select @_p1_0')  
print(cursor.fetchall())  # [{'@_p1_0:1'}]

cursor.close()
conn.close()

7. 删除存储过程

Copydrop procedure 存储过程的名字;

数据库之存储过程 相关文章

  1. 数据库之触发器

    触发器 1.什么是触发器 -- 在满足对表数据进行增,删,改的情况下,自动触发的功能就叫触发器-- 注意没有查 2.为什么要有触发器 -- 使用触发器可以帮助我们实现监控,添加日志... 3.触发器的使用 知识储备: 3.1 mysql默认的语句结束符是 ; ,我们可以修改这种语句

  2. 数据库之视图

    视图 1.什么是视图 -- 视图就是通过查询得到的一张虚拟表,然后将其保存下来,方便下次可以直接使用.视图的本质也是一张表. 2.为什么要有视图 -- 如果频繁的操作一张虚拟表(拼表组成的),那么就可以将这个虚拟表建成一个视图,方便后续查询. 3.视图的使用 -- 固

  3. 数据库读现象

    数据库读现象 一. 数据库读现象 数据库管理软件的 "读现象" 指的是当多个事务并发执行时,在读取数据方面可能碰到的问题,包括又脏读,不可重复读和幻读. ps: 对于一些数据库软件会自带相应的机制去解决脏读,不可重复读,幻读等问题, 因为这些自带的机制,下述的

  4. 数据库索引

    索引 一 索引的介绍 随堂总结(上课笔记) 什么是索引 索引是存储引擎中一种数据结构,或者说数据的组织方式,又称之为键key 为数据建立索引就好比是为书建目录为何要用索引 为了优化查询效率 ps:创建完索引后会降低增、删、改的效率 好就好在读写比例10:1如

  5. 数据库之流程控制

    流程控制 1. 条件语句 Copydelimiter //CREATE PROCEDURE proc_if ()BEGIN declare i int default 0; if i = 1 THEN SELECT 1; ELSEIF i = 2 THEN SELECT 2; ELSE SELECT 7; END IF;END //delimiter ; 2. 循环语句 while循环 Copydelimiter //CREATE PROCEDU

  6. 存储引擎介绍

    一. 存储引擎介绍 1. 什么是存储引擎 存储引擎就是表的类型, 针对不同类型的表, mysql使用不同的存取机制 Copy现实生活中我们用来存储数据的文件有不同的类型,每种文件类型对应各自不同的处理机制:比如处理文本用txt类型,处理表格用excel,处理图片用png

  7. 库的相关操作

    库的相关操作 一. 系统数据库介绍(安装版本5.6.xx版本自带的库名) information_schema : 虚拟库, 不占磁盘空间,存储的是数据库启动后的一些参数,如用户表信息,列信息,权限信息,字符信息等. performance_schema : mysql 5.5 版本开始新增的一个数据库, 主要用

  8. 初始数据库

    初识数据库 一. 什么是数据库 数据库是指按照数据结构 来组织.存储和管理数据的仓库. 是一个长期存储在计算机内的,有组织的,可共享的,统一管理的大量数据的集合. 二. 为什么要有数据库呢 2.1 数据库的由来 基于我们之前所学的,数据要想永久保存的话,只能保存

  9. JavaEE课程复习1--数据库相关操作

    〇、本模块内容简介 30=(DB5+前端5+Web Core15+Project5) Junit、注解 MySQL、JDBC、JDBCUtils、c3p0、Druid连接池及工具类、JDBCTemplate --------------------------------------------------------------------------------------------- HTML、CSS、Ja

  10. mysql存储过程调试方法

    CREATE PROCEDURE `p_next_id`(kind_name VARCHAR(30), i_length int,currentSeqNo VARCHAR(3),OUT o_result INT)BEGIN SET @a= NULL; SET @b= NULL; SELECT id INTO @a FROM t_seq WHERE number= currentSeqNo and length= i_length ; IF (@a is null ) THE

每天更新java,php,javaScript,go,python,nodejs,vue,android,mysql等相关技术教程,教程由网友分享而来,欢迎大家分享IT技术教程到本站,帮助自己同时也帮助他人!

Copyright 2020, All Rights Reserved. Powered by 跳墙网(www.tqwba.com)|网站地图|关键词