Database Experiment 6 stored procedure

Database and table building

view the database

sentence

show datebases;
-- View all databases

effect

[take mine as an example, the following are]

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

explain

Only sqlfortextbook and studb are databases built by me, and others are systems.

Create database

Statement [create studb database]

create database studb
-- In your sql Create this database in, provided that there is no database with the same name
using studb
-- Enter database

Build table

The table to be built is

Create three tables BankT, LET and LoanT to be used in the studb database. The corresponding columns are their attributes.

BankT LET LoanT
Bno Eno Eno
Banme Ename Bno
Tel Enature Ldate
Ecapital Lamount
Lterm

Create table statement

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)
);

Check whether the built table is correct

show tables;
-- View all tables under the current database
DESC loant;
-- see loant Table structure[ SQL [statements regardless of case]

Contents of the table to be inserted:

BankT table
Bno Bname Tel
102100000634 Industrial and Commercial Bank of China Limited Beijing Huixin sub branch 010-66383723
102100011308 Industrial and Commercial Bank of China Limited Argentina RMB clearing bank 010-69025556
102100015171 Industrial and Commercial Bank of China Limited Beijing Xinfadi sub branch 010-66287547
102100020655 Industrial and Commercial Bank of China Limited Beijing Dashanzi sub branch 010-66413881
102100020778 Industrial and Commercial Bank of China Limited Beijing West Fourth Ring sub branch 010-68153318
102100024914 Industrial and Commercial Bank of China Limited Beijing Fuxi Avenue sub branch 010-67749117
102100028292 Industrial and Commercial Bank of China Limited Beijing Xueyuan South Road sub branch 010-6966255
102100028469 Industrial and Commercial Bank of China Limited Beijing Wangjing Xincheng sub branch 010-68303695
102100077776 Industrial and Commercial Bank of China Limited Russian RMB business clearing bank 010-68887973
103100009023 Agricultural Bank of China Limited Beijing Xinhua Street sub branch 010-69541044
103100009031 Agricultural Bank of China Limited Beijing fuheyuan sub branch 010-69399485
103100009040 Agricultural Bank of China Limited Beijing Yongledian sub branch 010-68365688
103100009111 Agricultural Bank of China Limited Beijing Tongzhou Dongguan sub branch 010-69903089
103100009138 Agricultural Bank of China Limited Beijing Wuyi garden sub branch 010-68246637
103100009146 Agricultural Bank of China Limited Beijing Songzhuang sub branch 010-67039559
103100009162 Agricultural Bank of China Limited Beijing Xiji sub branch 010-66709805
103100009179 Agricultural Bank of China Limited Beijing Zhangjiawan sub branch 010-68937684
103100009187 Agricultural Bank of China Limited Beijing Jinhai Street sub branch 010-69518636
103100009211 Agricultural Bank of China Limited Beijing A sub branch NULL
103100040103 Agricultural Bank of China Limited Beijing Dajiaoting sub branch 010-66268599
LET table
Eno Ename Enature Ecapital Erep
M052001 Anhui Zhixi Information Technology Co., Ltd private enterprise 200 Xu Xin'an
M052002 Shanghai Siqiang Technology Co., Ltd private enterprise 150 Zhu Tingyi
M052003 Chongqing Wangda Automobile Service Co., Ltd private enterprise 240 Jin Jing
M052004 Dalian Johnson Investment Service Co., Ltd private enterprise 300 Cai chenjie
M052005 Qingdao Boyi electromechanical Co., Ltd Collective enterprise 200 Mo Jingyuan
M052006 Guangzhou Minolta Electric Appliance Co., Ltd private enterprise 150 remote
M052007 Chengdu deep Vision Technology Co., Ltd private enterprise 180 Lechao
M052008 Shanghai Rongsheng Advertising Co., Ltd private enterprise 200 Xu Guanzhong
M052009 Chongqing Xuchen Electronic Technology Co., Ltd private enterprise 210 Deng Yue
M052010 Dalian Hanhui vegetable market management Co., Ltd Collective enterprise 70 Jing Yun Shen
M052011 Qingdao tus warm Ecological Technology Co., Ltd private enterprise 300 Tang Yunqing
M052012 Anhui Kaidi Mining Co., Ltd private enterprise 500 Lin Shan
M052013 Beijing Minfu Investment Co., Ltd private enterprise 300 Wang zheyan
M052014 Shenzhen wanshunyun Information Technology Co., Ltd private enterprise 100 Wang Yinghui
M052015 Hangzhou Jiayuan Hardware Building Materials Co., Ltd Sole proprietorship 220 Strong and prosperous
M052016 Guangzhou Minze agricultural reclamation Group Co., Ltd Collective enterprise 310 Zhang Cheng
M052017 Chengdu wanconglu Agricultural Development Co., Ltd private enterprise 230 Zhou Yuan
M052018 Shanghai sangyuan Biotechnology Co., Ltd private enterprise 100 Lu Congfan
M052019 Chongqing Maier Management Consulting Co., Ltd private enterprise 100 Liu Zhi
M052020 Dalian jiujiajiu Trading Co., Ltd private enterprise 120 Xiao Bingjun
M052021 Qingdao Yijia Auto Parts Co., Ltd Sole proprietorship 300 Chen Hao
M052022 Guangzhou Xintai Shoes Co., Ltd private enterprise 180 Wang Qiusheng
M052023 Chengdu guangguangniao shoes and Clothing Co., Ltd Sole proprietorship 80 Weng Dongxu
M052024 Beijing Jinkai training school private enterprise 500 Jin ruizhang
M052025 Shanghai Zhonggong Education Technology Co., Ltd private enterprise 100 Jin Wenchao
M052026 Minshen e-commerce Holding Co., Ltd private enterprise 100 Zhang Zili
M052027 Hangzhou Tiantu Venture Capital Co., Ltd private enterprise 500 Ying Baolong
M052028 Shenzhen Guoxin Technology Co., Ltd private enterprise 100 Jiang Zilin
M052029 Shanghai soul Technology Co., Ltd private enterprise 50 Li Lujun
M052030 Chongqing Kaiyin Technology Co., Ltd joint-stock association 200 Wang Chensheng
M052031 Beijing Jiutian Machinery Co., Ltd private enterprise 380 Yang Jue
M052032 Shenzhen Hexun Technology Co., Ltd joint-stock association 100 Shi Chao
M052033 Hangzhou Xiangyu Machinery Co., Ltd private enterprise 100 Ning Zhang
M052034 Guangzhou dejucai Investment Co., Ltd private enterprise 1000 Lu Ziqing
M052035 Chengdu wenxinyuan Trading Co., Ltd private enterprise 70 Zhu Chenzhou
M052036 Shanghai Wenjun Shoes Co., Ltd Sole proprietorship 80 Yu Zhongjun
M052037 Chongqing Wenmao catering Co., Ltd Sole proprietorship 50 Liu Renzhi
M052038 Dalian Weisi Technology Co., Ltd private enterprise 80 Liu Xiaoye
M052039 Qingdao YOUPIN Vision Advertising Co., Ltd private enterprise 120 Gong Mo
M052040 Guangzhou Tianjiu happiness Business Consulting Group Co., Ltd private enterprise 50 Zhang Yifan
M052041 Chengdu boutique Fenghua Advertising Co., Ltd private enterprise 60 Zhu Qian
M052042 Shanghai Juye Information Technology Co., Ltd private enterprise 100 Chen Chen Wang
M052043 Chongqing Xinlin Business Consulting Group Co., Ltd private enterprise 100 Cui Zhenglin
M052044 Dalian Lianda Technology Co., Ltd joint-stock association 160 Jian Zhang
M052045 Qingdao modern drug development Co., Ltd private enterprise 300 Dai Guangchen
Load table
Eno Bno Ldate Lamount Lterm
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

Insert content statement

Insert into BankT table
insert into
bankt(Bno,Bname,Tel)
value
(' 102100000634 ',' Industrial and Commercial Bank of China Limited Beijing Huixin sub branch',' 010-66383723 '),
(' 102100011308 ' ,'Industrial and Commercial Bank of China Limited Argentina RMB clearing bank',' 010-69025556 '),
(' 102100015171 ' ,'Industrial and Commercial Bank of China Limited Beijing Xinfadi sub branch',' 010-66287547 '),
(' 102100020655 ' ,'Industrial and Commercial Bank of China Limited Beijing Dashanzi sub branch',' 010-66413881 '),
(' 102100020778 ', 'Industrial and Commercial Bank of China Limited Beijing West Fourth Ring sub branch',' 010-68153318 '),
(' 102100024914 ', 'Industrial and Commercial Bank of China Limited Beijing Fuxi Avenue sub branch',' 010-67749117 '),
(' 102100028292 ', 'Industrial and Commercial Bank of China Limited Beijing Xueyuan South Road sub branch',' 010-69662555 '),
(' 102100028469 ', 'Industrial and Commercial Bank of China Limited Beijing Wangjing Xincheng sub branch',' 010-68303695 '),
(' 102100077776 ', 'Industrial and Commercial Bank of China Limited Russian RMB business clearing bank ',' 010-68887973 '),
(' 103100009023 ', 'Agricultural Bank of China Limited Beijing Xinhua Street sub branch',' 010-69541044 '),
(' 103100009031 ', 'Agricultural Bank of China Limited Beijing fuheyuan sub branch',' 010-69399485 '),
(' 103100009040 ', 'Agricultural Bank of China Limited Beijing Yongledian sub branch',' 010-68365688 '),
(' 103100009111 ', 'Agricultural Bank of China Limited Beijing Tongzhou Dongguan sub branch',' 010-69903089 '),
(' 103100009138 ', 'Agricultural Bank of China Limited Beijing Wuyi garden sub branch',' 010-68246637 '),
(' 103100009146 ', 'Agricultural Bank of China Limited Beijing Songzhuang sub branch',' 010-67039559 '),
(' 103100009162 ', 'Agricultural Bank of China Limited Beijing Xiji sub branch',' 010-66709805 '),
(' 103100009179 ', 'Agricultural Bank of China Limited Beijing Zhangjiawan sub branch',' 010-68937684 '),
(' 103100009187 ', 'Agricultural Bank of China Limited Beijing Jinhai Street sub branch',' 010-69518636 '),
(' 103100009211 ', 'Agricultural Bank of China Limited Beijing A Sub branch',' NULL         '),
(' 103100040103 ', 'Agricultural Bank of China Limited Beijing Dajiaoting sub branch','010-66268599 ');
Insert into let table
insert into
let(Eno, Ename, Enature, Ecapital)
values
(' M052001 ',' Anhui Zhixi Information Technology Co., Ltd         ',' private enterprise     ','      200 ',' Xu Xin'an '),
(' M052002 ', 'Shanghai Siqiang Technology Co., Ltd             ',' private enterprise     ','      150 ',' Zhu Tingyi '),
(' M052003 ',' Chongqing Wangda Automobile Service Co., Ltd         ','private enterprise     ','      240 ',' Jin Jing   '),
(' M052004 ',' Dalian Johnson Investment Service Co., Ltd         ',' private enterprise     ', '     300 ',' Cai chenjie '),
(' M052005 ',' Qingdao Boyi electromechanical Co., Ltd             ',' Collective enterprise     ','      200 ',' Mo Jingyuan '),
(' M052006 ',' Guangzhou Minolta Electric Appliance Co., Ltd           ',' private enterprise     ','      150 ',' remote   '),
(' M052007 ',' Chengdu deep Vision Technology Co., Ltd         ',' private enterprise     ', '     180 ',' Lechao   '),
(' M052008 ',' Shanghai Rongsheng Advertising Co., Ltd            ', ' private enterprise     ,''      200 ',' Xu Guanzhong '),
(' M052009 ',' Chongqing Xuchen Electronic Technology Co., Ltd         ',' private enterprise     ', '     210 ',' Deng Yue   '),
(' M052010 ',' Dalian Hanhui vegetable market management Co., Ltd   ' ,'Collective enterprise     ',  '     70 ',' Jing Yun Shen '),
(' M052011 ', 'Qingdao tus warm Ecological Technology Co., Ltd   ',' private enterprise     ',  '    300 ',' Tang Yunqing '),
(' M052012 ',' Anhui Kaidi Mining Co., Ltd         ',' private enterprise     ', '     500 ',' Lin Shan   '),
(' M052013 ',' Beijing Minfu Investment Co., Ltd            ', ' private enterprise     ','      300', ' Wang zheyan '),
(' M052014 ',' Shenzhen wanshunyun Information Technology Co., Ltd       ',' private enterprise     ', '     100 ',' Wang Yinghui '),
(' M052015 ',' Hangzhou Jiayuan Hardware Building Materials Co., Ltd         ',' Sole proprietorship ', '     220 ',' Strong and prosperous   '),
(' M052016 ',' Guangzhou Minze agricultural reclamation Group Co., Ltd         ',' Collective enterprise     ','      310', ' Zhang Cheng   '),
(' M052017 ',' Chengdu wanconglu Agricultural Development Co., Ltd       ',' private enterprise     ','      230 ',' Zhou Yuan   '),
(' M052018 ',' Shanghai sangyuan Biotechnology Co., Ltd         ',' private enterprise     ','      100 ',' Lu Congfan '),
(' M052019 ',' Chongqing Maier Management Consulting Co., Ltd         ',' private enterprise     ','      100 ',' Liu Zhi   '),
(' M052020 ',' Dalian jiujiajiu Trading Co., Ltd           ',' private enterprise     ', '     120 ',' Xiao Bingjun '),
(' M052021 ',' Qingdao Yijia Auto Parts Co., Ltd     ',' Sole proprietorship ','      300 ',' Chen Hao   '),
(' M052022 ', 'Guangzhou Xintai Shoes Co., Ltd             ',' private enterprise     ','      180 ',' Wang Qiusheng '),
(' M052023 ',' Chengdu guangguangniao shoes and Clothing Co., Ltd           ',' Sole proprietorship ',  '     80 ',' Weng Dongxu '),
(' M052024 ',' Beijing Jinkai training school                ', ' private enterprise     ','      500', ' Jin ruizhang '),
(' M052025 ',' Shanghai Zhonggong Education Technology Co., Ltd         ',' private enterprise     ','      100 ',' Jin Wenchao '),
(' M052026 ','Minshen e-commerce Holding Co., Ltd             ',' private enterprise     ','      100 ',' Zhang Zili '),
(' M052027 ',' Hangzhou Tiantu Venture Capital Co., Ltd         ',' private enterprise     ', '     500 ',' Ying Baolong '),
(' M052028 ',' Shenzhen Guoxin Technology Co., Ltd         ',' private enterprise     ','      100 ',' Jiang Zilin '),
(' M052029 ',' Shanghai soul Technology Co., Ltd             ',' private enterprise     ','       50 ',' Li Lujun '),
(' M052030 ', 'Chongqing Kaiyin Technology Co., Ltd         ',' joint-stock association     ', '     200 ',' Wang Chensheng '),
(' M052031 ',' Beijing Jiutian Machinery Co., Ltd             ',' private enterprise     ','      380 ',' Yang Jue '),
(' M052032 ',' Shenzhen Hexun Technology Co., Ltd         ',' joint-stock association     ','      100 ',' Shi Chao   '),
(' M052033 ',' Hangzhou Xiangyu Machinery Co., Ltd             ',' private enterprise     ','      100 ',' Ning Zhang   '),
(' M052034 ',' Guangzhou dejucai Investment Co., Ltd           ',' private enterprise     ','     1000 ',' Lu Ziqing '),
(' M052035 ',' Chengdu wenxinyuan Trading Co., Ltd           ',' private enterprise     ','       70 ',' Zhu Chenzhou '),
(' M052036 ', 'Shanghai Wenjun Shoes Co., Ltd             ', 'Sole proprietorship ', '      80 ',' Yu Zhongjun '),
(' M052037 ',' Chongqing Wenmao catering Co., Ltd         ',' Sole proprietorship ','       50 ', 'Liu Renzhi '),
(' M052038 ',' Dalian Weisi Technology Co., Ltd             ',' private enterprise     ','       80 ',' Liu Xiaoye '),
(' M052039 ',' Qingdao YOUPIN Vision Advertising Co., Ltd         ',' private enterprise     ', '     120 ',' Gong Mo   '),
(' M052040 ',' Guangzhou Tianjiu happiness Business Consulting Group Co., Ltd ',' private enterprise     ', '      50 ', 'Zhang Yifan '),
(' M052041 ',' Chengdu boutique Fenghua Advertising Co., Ltd         ',' private enterprise     ','       60 ',' Zhu Qian '),
(' M052042 ',' Shanghai Juye Information Technology Co., Ltd         ',' private enterprise     ','      100 ',' Chen Chen Wang '),
(' M052043 ',' Chongqing Xinlin Business Consulting Group Co., Ltd     ',' private enterprise     ', '     100 ',' Cui Zhenglin '),
(' M052044 ',' Dalian Lianda Technology Co., Ltd         ',' joint-stock association     ','      160 ',' Jian Zhang   '),
(' M052045 ',' Qingdao modern drug development Co., Ltd         ',' private enterprise     ','      300 ', 'Dai Guangchen ');
Insert into the loan table
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 ');

Introduction to SQL stored procedures

sql is a highly non procedural statement. Procedural sql needs to provide functions such as process space and variable definition.
Procedural sql is declared and called differently in different databases and database management software.
Here are the statements I use MySQL 8 on Navicat 15.

-- Sequential structure
begin
	...
end
-- Branching structure
IF search_condition THEN
	statement_list
ELSE
	statement_list
END IF;
-- Cyclic structure
WHILE search_condition DO
	statement_list
END WHILE;

Stored procedure experiment

[purpose of experiment]
1. Master the definition of stored procedure
2. Master the use of stored procedures
[experimental nature] confirmatory

[experiment content]

(1) Delete the loan records of the designated legal person before the designated date. Legal person name and date are required as input parameters.
(2) Query the legal person name, loan date and loan amount of the specified bank whose loan amount is higher than the average loan amount of this bank. The bank name is required as the input parameter. If the specified bank does not exist, the "specified bank does not exist" will be displayed.
(3) Query the total loan amount of the specified legal person. The legal person name is required to be the input parameter, the total loan amount is the output parameter, and the default value of the legal person name is "Chongqing Xuchen Electronic Technology Co., Ltd.".

-- (1) 
-- Delete the loan records of the designated legal person before the designated date.
-- Legal person name and date are required as input parameters.

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('New legal person',2028-06-01) $$

explain:

  1. Delimiter means delimiter. In sql, when multiple sql statements are allowed, you should use; An identifier that serves as the end of a statement. If begin... End... Etc. are not sql statements, you need to declare an ID as the end ID first.
    2. CREATE PROCEDURE p1(in fr_name char(10), in dk_date char(20)) is a statement that creates a stored procedure, fr_ name,dk_name is the parameter of P1, and in indicates that they are all input parameters, and the actual parameters need to be passed in when calling.
    3.SET @frname = fr_name; SET is an assignment statement, where the parameter fr will be entered_ Pass the value of name to @ frname@ Frname is a user-defined variable, which is the same as fr_ The difference of the name local variable is that you can use it outside of the procedure sql statement. For example, use show tables; You can see it and use select frname; You can see its value.
    4.call p1('New legal person ', 2028-06-01) is a statement that calls a stored procedure. If executed correctly, it will return OK
    5. The deletion statement is dorp procedure p1;
-- (2) Query the legal person name, loan date and loan amount of the specified bank whose loan amount is higher than the average loan amount of this bank. 
-- The bank name is required as the input parameter. If the specified bank does not exist, the "specified bank does not exist" will be displayed.
delimiter $

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

	IF bank_name in (SELECT Bname FROM bankt) THEN
	
	SELECT Ename Name of legal person, Ldate Loan date, Lamount Loan amount
	
	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 'The specified bank does not exist';
	
END IF;

end $
CALL p2('Industrial and Commercial Bank of China Limited Beijing Xinfadi sub branch')$
-- (3) Query the total loan amount of the specified legal person. The legal person name is required to be the input parameter, the total loan amount is the output parameter, and the default value of the legal person name is "Chongqing Xuchen Electronic Technology Co., Ltd.".

delimiter $
CREATE PROCEDURE p3(in fr_name VARCHAR(32), out sum_amount INT)
BEGIN
	-- mysql Default parameters are not supported!!!
	IF fr_name in (SELECT Ename FROM let) THEN -- Input method name does not exist
	SELECT SUM(Lamount) into sum_amount
	FROM loant 
		LEFT JOIN let ON loant.Eno = let.Eno 
	WHERE 
		Ename = fr_name;

	ELSE
	SET fr_name = 'Chongqing Xuchen Electronic Technology Co., Ltd'; -- If the input legal person does not exist, the default value will be used
	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('Hangzhou Tiantu Venture Capital Co., Ltd',@sum_amount);
SELECT @sum_amount Loan amount;

It's not easy to create. Put more money in it

Tags: Database MySQL

Posted by izzy on Wed, 04 May 2022 09:52:20 +0300