数据库 实验六 存储过程

建库建表

查看数据库

语句

show datebases;
-- 查看所有数据库

效果

【以我的为例，以下都是】

mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| sqlfortextbook |
| studb |
| sys |
±-------------------+
6 rows in set (0.02 sec)

说明

只有 sqlfortextbook，studb是我建的数据库，别的都是系统的。

创建数据库

语句【创建studb数据库】

create database studb
-- 在你的sql里创建这个数据库，前提是没有同名数据库
using studb
-- 进入数据库

建表

要建的表是…

在studb数据库里创建要用的三个表BankT，LET，LoanT，对应列下是其属性。

建表语句

CREATE TABLE BankT(
Bno CHAR(12) PRIMARY KEY ,
Bname VARCHAR(100),
Tel VARCHAR(20)
);
CREATE TABLE LET(
Eno CHAR(7) PRIMARY KEY ,
Ename VARCHAR(100),
Enature VARCHAR(10),
Ecapital INT,
Erep VARCHAR(10)
);
CREATE TABLE LoanT(
Eno CHAR(7),
Bno CHAR(12),
Ldate DATE,
Lamount INT,
Lterm INT,
FOREIGN KEY (Eno) REFERENCES LET(Eno),
FOREIGN KEY (Bno) REFERENCES BankT(Bno),
PRIMARY KEY LoanT(Bno,Eno,Ldate)
);

检查建表是否正确

show tables;
-- 查看当前数据库下所有表
DESC loant;
-- 查看loant表的结构【SQL语句不分大小写】

待插入的表的内容：

BankT 表

LET 表

Loant 表

插入内容的语句

向BankT表插入

insert into
bankt(Bno,Bname,Tel)
value
(' 102100000634 ',' 中国工商银行股份有限公司北京惠新支行',' 010-66383723 '),
(' 102100011308 ' ,'中国工商银行股份有限公司阿根廷人民币清算行',' 010-69025556 '),
(' 102100015171 ' ,'中国工商银行股份有限公司北京新发地支行',' 010-66287547 '),
(' 102100020655 ' ,'中国工商银行股份有限公司北京大山子支行',' 010-66413881 '),
(' 102100020778 ', '中国工商银行股份有限公司北京西四环支行',' 010-68153318 '),
(' 102100024914 ', '中国工商银行股份有限公司北京福熙大道支行',' 010-67749117 '),
(' 102100028292 ', '中国工商银行股份有限公司北京学院南路支行',' 010-69662555 '),
(' 102100028469 ', '中国工商银行股份有限公司北京望京新城支行',' 010-68303695 '),
(' 102100077776 ', '中国工商银行股份有限公司俄罗斯人民币业务清算行 ',' 010-68887973 '),
(' 103100009023 ', '中国农业银行股份有限公司北京新华大街支行',' 010-69541044 '),
(' 103100009031 ', '中国农业银行股份有限公司北京富河园支行',' 010-69399485 '),
(' 103100009040 ', '中国农业银行股份有限公司北京永乐店支行',' 010-68365688 '),
(' 103100009111 ', '中国农业银行股份有限公司北京通州东关支行',' 010-69903089 '),
(' 103100009138 ', '中国农业银行股份有限公司北京武夷花园支行',' 010-68246637 '),
(' 103100009146 ', '中国农业银行股份有限公司北京宋庄支行',' 010-67039559 '),
(' 103100009162 ', '中国农业银行股份有限公司北京西集支行',' 010-66709805 '),
(' 103100009179 ', '中国农业银行股份有限公司北京张家湾支行',' 010-68937684 '),
(' 103100009187 ', '中国农业银行股份有限公司北京金海街支行',' 010-69518636 '),
(' 103100009211 ', '中国农业银行股份有限公司北京A支行',' NULL         '),
(' 103100040103 ', '中国农业银行股份有限公司北京大郊亭支行','010-66268599 ');

向let表中插入

insert into
let(Eno, Ename, Enature, Ecapital)
values
(' M052001 ',' 安徽到喜信息技术有限公司         ',' 私营企业     ','      200 ',' 徐欣安 '),
(' M052002 ', '上海思强科技有限公司             ',' 私营企业     ','      150 ',' 朱汀一 '),
(' M052003 ',' 重庆旺达汽车服务有限公司         ','私营企业     ','      240 ',' 金晶   '),
(' M052004 ',' 大连江森投资服务有限公司         ',' 私营企业     ', '     300 ',' 蔡辰杰 '),
(' M052005 ',' 青岛博一机电有限公司             ',' 集体企业     ','      200 ',' 磨景源 '),
(' M052006 ',' 广州美能达电器有限公司           ',' 私营企业     ','      150 ',' 边远   '),
(' M052007 ',' 成都深度视觉科技有限公司         ',' 私营企业     ', '     180 ',' 乐超   '),
(' M052008 ',' 上海荣昇广告有限公司            ', ' 私营企业     ,''      200 ',' 徐冠中 '),
(' M052009 ',' 重庆旭晨电子科技有限公司         ',' 私营企业     ', '     210 ',' 邓越   '),
(' M052010 ',' 大连瀚辉菜市场经营管理有限公司   ' ,'集体企业     ',  '     70 ',' 沈静云 '),
(' M052011 ', '青岛启迪特温暖生态科技有限公司   ',' 私营企业     ',  '    300 ',' 唐云清 '),
(' M052012 ',' 安徽凯帝矿业有限责任公司         ',' 私营企业     ', '     500 ',' 林杉   '),
(' M052013 ',' 北京民福投资有限公司            ', ' 私营企业     ','      300', ' 王哲彦 '),
(' M052014 ',' 深圳万顺云信息技术有限公司       ',' 私营企业     ', '     100 ',' 王颖慧 '),
(' M052015 ',' 杭州嘉源五金建材有限公司         ',' 个人独资企业 ', '     220 ',' 强盛   '),
(' M052016 ',' 广州民泽农垦集团有限公司         ',' 集体企业     ','      310', ' 张成   '),
(' M052017 ',' 成都万丛绿农业发展有限公司       ',' 私营企业     ','      230 ',' 周园   '),
(' M052018 ',' 上海桑园生物科技有限公司         ',' 私营企业     ','      100 ',' 陆丛凡 '),
(' M052019 ',' 重庆迈尔管理咨询有限公司         ',' 私营企业     ','      100 ',' 刘智   '),
(' M052020 ',' 大连久加久商贸有限公司           ',' 私营企业     ', '     120 ',' 肖冰峻 '),
(' M052021 ',' 青岛意佳汽车配件有限责任公司     ',' 个人独资企业 ','      300 ',' 陈昊   '),
(' M052022 ', '广州信泰鞋业有限公司             ',' 私营企业     ','      180 ',' 王秋晟 '),
(' M052023 ',' 成都光辉鸟鞋服有限公司           ',' 个人独资企业 ',  '     80 ',' 翁东旭 '),
(' M052024 ',' 北京金开培训学校                ', ' 私营企业     ','      500', ' 金睿璋 '),
(' M052025 ',' 上海中公教育科技有限公司         ',' 私营企业     ','      100 ',' 金文超 '),
(' M052026 ','珉申电商控股有限公司             ',' 私营企业     ','      100 ',' 张子立 '),
(' M052027 ',' 杭州天兔创业投资有限公司         ',' 私营企业     ', '     500 ',' 应鲍龙 '),
(' M052028 ',' 深圳国信科技股份有限公司         ',' 私营企业     ','      100 ',' 姜子麟 '),
(' M052029 ',' 上海魂之科技有限公司             ',' 私营企业     ','       50 ',' 李禄俊 '),
(' M052030 ', '重庆凯因科技股份有限公司         ',' 股份企业     ', '     200 ',' 王辰晟 '),
(' M052031 ',' 北京九天机械有限公司             ',' 私营企业     ','      380 ',' 杨珏慜 '),
(' M052032 ',' 深圳鹤勋科技股份有限公司         ',' 股份企业     ','      100 ',' 施超   '),
(' M052033 ',' 杭州祥御机械有限公司             ',' 私营企业     ','      100 ',' 张宁   '),
(' M052034 ',' 广州德聚财投资有限公司           ',' 私营企业     ','     1000 ',' 陆自清 '),
(' M052035 ',' 成都文鑫源商贸有限公司           ',' 私营企业     ','       70 ',' 朱辰舟 '),
(' M052036 ', '上海文俊鞋业有限公司             ', '个人独资企业 ', '      80 ',' 禹仲俊 '),
(' M052037 ',' 重庆文懋餐饮有限责任公司         ',' 个人独资企业 ','       50 ', '刘仁直 '),
(' M052038 ',' 大连唯思科技有限公司             ',' 私营企业     ','       80 ',' 刘枭烨 '),
(' M052039 ',' 青岛优品视界广告有限公司         ',' 私营企业     ', '     120 ',' 龚墨   '),
(' M052040 ',' 广州天九幸福商务咨询集团有限公司 ',' 私营企业     ', '      50 ', '张一凡 '),
(' M052041 ',' 成都精品风华广告有限公司         ',' 私营企业     ','       60 ',' 朱琪安 '),
(' M052042 ',' 上海桔叶信息科技有限公司         ',' 私营企业     ','      100 ',' 王晨辰 '),
(' M052043 ',' 重庆信林商务咨询集团有限公司     ',' 私营企业     ', '     100 ',' 崔正霖 '),
(' M052044 ',' 大连联达科技股份有限公司         ',' 股份企业     ','      160 ',' 张健   '),
(' M052045 ',' 青岛现代药物开发有限公司         ',' 私营企业     ','      300 ', '戴光辰 ');

向loant表中插入

insert into
loant(Eno, Bno, Ldate, Lamount, Lterm)
values
(' M052028 ',' 102100000634 ',' 2014-11-01 ','      31 ','     3 '),(
' M052032 ',' 102100011308 ',' 2028-06-01 ','      42 ','     5 '),(
' M052015 ',' 102100015171 ',' 2020-01-01 ','      40 ','    14 '),(
' M052027 ',' 102100015171 ',' 2029-12-01 ','      73 ','     6 '),(
' M052042 ',' 102100015171 ',' 2011-05-01 ','      22 ','     8 '),(
' M052007 ',' 102100020655 ',' 2016-06-01 ','      76 ','    10 '),(
' M052012 ',' 102100020655 ',' 2020-01-01 ','      41 ','    17 '),(
' M052030 ',' 102100020655 ',' 2022-12-01 ','      37 ','     8 '),(
' M052014 ',' 102100020778 ',' 2019-01-01 ','      55 ','    13 '),(
' M052024 ',' 102100020778 ',' 2027-05-01 ','      95 ','    14 '),(
' M052003 ',' 102100022525 ',' 2016-09-01 ','      58 ','    13 '),(
' M052005 ',' 102100022525 ',' 2021-09-01 ','      78 ','    11 '),(
' M052009 ',' 102100022525 ',' 2013-04-01 ','      42 ','    18 '),(
' M052021 ',' 102100022525 ',' 2024-11-01 ','      83 ','    13 '),(
' M052033 ',' 102100024914 ',' 2013-09-01 ','      48 ','    11 '),(
' M052011 ',' 102100028292 ',' 2016-06-01 ','      66 ','     6 '),(
' M052028 ',' 102100028292 ',' 2022-02-01 ','      57 ','     5 '),(
' M052038 ',' 102100028292 ',' 2026-02-01 ','      26 ','    12 '),(
' M052031 ',' 102100028469 ',' 2022-01-01 ','      64 ','    10 '),(
' M052033 ',' 102100028469 ',' 2013-04-01 ','      24 ','    10 '),(
' M052002 ',' 102100077776 ',' 2013-04-01 ','      77 ','    10 '),(
' M052022 ',' 102100077776 ',' 2015-02-01 ','      60 ','     6 '),(
' M052044 ',' 102100077776 ',' 2014-04-01 ','      69 ','    15 '),(
' M052001 ',' 103100009023 ',' 2011-12-01 ','      44 ','    12 '),(
' M052010 ',' 103100009023 ',' 2023-10-01 ','      67 ','    12 '),(
' M052016 ',' 103100009023 ',' 2021-11-01 ','      85 ','     3 '),(
' M052037 ',' 103100009023 ',' 2017-08-01 ','      25 ','     3 '),(
' M052038 ',' 103100009023 ',' 2020-11-01 ','      20 ','    13 '),(
' M052045 ',' 103100009023 ',' 2017-11-01 ','      22 ','     7 '),(
' M052030 ',' 103100009031 ',' 2020-02-01 ','      39 ','    14 '),(
' M052028 ',' 103100009040 ',' 2021-02-01 ','      88 ','     8 '),(
' M052037 ',' 103100009040 ',' 2027-08-01 ','      72 ','    12 '),(
' M052003 ',' 103100009111 ',' 2011-05-01 ','      25 ','     8 '),(
' M052004 ',' 103100009111 ',' 2017-06-01 ','      81 ','    11 '),(
' M052038 ',' 103100009111 ',' 2020-01-01 ','      64 ','    17 '),(
' M052019 ',' 103100009138 ',' 2012-11-01 ','      47 ','     6 '),(
' M052023 ',' 103100009138 ',' 2019-09-01 ','      74 ','    12 '),(
' M052019 ',' 103100009146 ',' 2011-06-01 ','      96 ','    16 '),(
' M052038 ',' 103100009162 ',' 2029-03-01 ','      62 ','     9 '),(
' M052006 ',' 103100009179 ',' 2024-07-01 ','      78 ','     8 '),(
' M052014 ',' 103100009179 ',' 2023-09-01 ','      60 ','     8 '),(
' M052029 ',' 103100009179 ',' 2015-09-01 ','      99 ','     8 '),(
' M052034 ',' 103100009179 ',' 2015-09-01 ','      80 ','    17 '),(
' M052017 ',' 103100009187 ',' 2020-07-01 ','      54 ','     8 '),(
' M052025 ',' 103100009187 ',' 2018-10-01 ','      84 ','    12 '),(
' M052027 ',' 103100009187 ',' 2013-12-01 ','      37 ','     9 '),(
' M052008 ',' 103100040103 ',' 2012-08-01 ','      97 ','     7 '),(
' M052020 ',' 103100040103 ',' 2022-09-01 ','      76 ','    11 ');

SQL 存储过程 简介

sql是高度非过程化的语句。过程化sql就需要提供流程空间和变量定义等功能。
过程化sql在不同的数据库和数据库管理软件中声明和调用的差别很大。
这里都是我在Navicat 15上使用MySQL 8的语句。

-- 顺序结构
begin
	...
end
-- 分支结构
IF search_condition THEN
	statement_list
ELSE
	statement_list
END IF;
-- 循环结构
WHILE search_condition DO
	statement_list
END WHILE;

存储过程实验

【实验目的】
1、 掌握存储过程的定义
2、 掌握存储过程的使用
【实验性质】 验证性

【实验内容】

（1） 删除指定法人在指定日期前的贷款记录。要求法人名称和日期为输入参数。
（2） 查询在指定银行贷款、且贷款金额高于此银行的平均贷款金额的法人名称、贷款日期和贷款金额。要求银行名称为输入参数，如果指定的银行不存在，则显示“指定的银行不存在”。
（3） 查询指定法人的总贷款金额。要求法人名称为输入参数，总贷款金额为输出参数，法人名称的默认值为“重庆旭晨电子科技有限公司”。

-- （1） 
-- 删除指定法人在指定日期前的贷款记录。
-- 要求法人名称和日期为输入参数。

delimiter $$

CREATE PROCEDURE p1(in fr_name char(10), in dk_date char(20))
begin 
	SET @frname = fr_name;
	SET @dkdate = dk_date;
	delete 
	loant
	from loant	,let 
	WHERE loant.Eno = let.Eno
	and Ename = @frname and Ldate < @dkdate ;
end $$

call p1('新法人',2028-06-01) $$

说明：

1. delimiter 意为分界符。在sql中，允许多个sql语句时就要以 ; 作为一个语句结束的标识。而如begin…end…等都不是sql语句，就需要先声明某个标识作为结束标识。
   2. CREATE PROCEDURE p1(in fr_name char(10), in dk_date char(20))是创建存储过程的语句，fr_name，dk_name都是 p1的参数，in表明他们都是入参参数，需要在调用时传入实参。
   3.SET @frname = fr_name; SET 是赋值语句，这里将入参 fr_name的值传给@frname。@frname是用户自定义变量，它和fr_name 局部变量的不同是，你可以在过程sql语句之外使用它。比如使用show tables;就能看到它，使用select frname;能看到它的值。
   4.call p1(‘新法人’,2028-06-01),是调用存储过程的语句。正确执行的话会返回OK.
   5.删除语句是dorp procedure p1;

-- （2） 查询在指定银行贷款、且贷款金额高于此银行的平均贷款金额的法人名称、贷款日期和贷款金额。 
-- 要求银行名称为输入参数，如果指定的银行不存在，则显示“指定的银行不存在”。
delimiter $

CREATE PROCEDURE p2
(in bank_name varchar(32))
begin 

	IF bank_name in (SELECT Bname FROM bankt) THEN
	
	SELECT Ename 法人名称, Ldate 贷款日期, Lamount 贷款金额
	
	FROM loant LEFT JOIN let 
	ON loant.Eno = let.Eno
	LEFT JOIN bankt
	ON loant.Bno = bankt.Bno
	
	WHERE Bname = bank_name
	AND Lamount > 
	(SELECT AVG(Lamount) FROM loant LEFT JOIN bankt ON loant.Bno = bankt.Bno
	WHERE Bname = bank_name);
	
ELSE
	SELECT '指定的银行不存在';
	
END IF;

end $
CALL p2('中国工商银行股份有限公司北京新发地支行')$

-- （3） 查询指定法人的总贷款金额。要求法人名称为输入参数，总贷款金额为输出参数，法人名称的默认值为“重庆旭晨电子科技有限公司”。

delimiter $
CREATE PROCEDURE p3(in fr_name VARCHAR(32), out sum_amount INT)
BEGIN
	-- mysql不支持默认参数！！！
	IF fr_name in (SELECT Ename FROM let) THEN -- 输入法人名存在
	SELECT SUM(Lamount) into sum_amount
	FROM loant 
		LEFT JOIN let ON loant.Eno = let.Eno 
	WHERE 
		Ename = fr_name;

	ELSE
	SET fr_name = '重庆旭晨电子科技有限公司'; -- 输入法人不存在则使用默认值
	SELECT SUM(Lamount) into sum_amount
	FROM loant 
		LEFT JOIN let ON loant.Eno = let.Eno 
	WHERE 
		Ename = fr_name;
	
	END IF;

END $

CALL p3('杭州天兔创业投资有限公司',@sum_amount);
SELECT @sum_amount 贷款金额;

创作不易，多多投币