溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本文小編為大家詳細介紹“MySQL存儲過程創建使用及實現數據快速插入的方法是什么”，內容詳細，步驟清晰，細節處理妥當，希望這篇“MySQL存儲過程創建使用及實現數據快速插入的方法是什么”文章能幫助大家解決疑惑，下面跟著小編的思路慢慢深入，一起來學習新知識吧。
自MySQL 5.0 版本開始支持存儲過程。存儲過程（Stored Procedure）是一種在數據庫中存儲復雜程序，以便外部程序調用的一種數據庫對象。
存儲過程是為了完成特定功能的SQL語句集，經編譯創建并保存在數據庫中，用戶可通過指定存儲過程的名字并給定參數(需要時)來調用執行。
存儲過程思想就是數據庫 SQL 語言層面的代碼封裝與重用。類似于Java開發中封裝工具類方便以后直接調用的作用。
優點
存儲過程可以一次編譯多次使用。存儲過程只在創建時進行編譯，之后的使用都不需要重新編譯，k就提升了sQL的執行效率。
可以減少開發工作量。將代碼封裝成模塊，實際上是編程的核心思想之一，這樣可以把復雜的問題拆解成不同的模塊，然后模塊之間可以重復使用，在減少開發工作量的同時，還能保證代碼的結構清晰。
存儲過程的安全性強。我們在設定存儲過程的時候可以設置對用戶的使用權限，這樣就和視圖一樣具有較強的安全性。
可以減少網絡傳輸量。因為代碼封裝到存儲過程中，每次使用只需要調用存儲過程即可，這樣就減少了網絡傳輸量。
良好的封裝性。在進行相對復雜的數據庫操作時，原本需要使用一條一條的sQL語句，可能要連接多次數據庫才能完成的操作，現在變成了一次存儲過程，只需要連接一次即可。
缺點
可移植性差。存儲過程不能跨數據庫移植，比如在MysQL、Oracle和sQL Server里編寫的存儲過程，在換成其他數據庫時都需要重新編寫。
調試困難。只有少數DBMS支持存儲過程的調試。對于復雜的存儲過程來說，開發和維護都不容易。雖然也有一些第三方工具可以對存儲過程進行調試，但要收費。
存儲過程的版本管理很困難。比如數據表索引發生變化了，可能會導致存儲過程失效。我們在開發軟件的時候往往需要進行版本管理，但是存儲過程本身沒有版本控制，版本迭代更新的時候很麻煩。
它不適合高并發的場景。高并發的場景需要減少數據庫的壓力，有時數據庫會采用分庫分表的方式，而且對可擴展性要求很高，在這種情況下，存儲過程會變得難以維護，增加數據庫的壓力，顯然就不適用了。
存儲過程就是具有名字的一段代碼，用來完成一個特定的功能。
創建的存儲過程保存在數據庫的數據字典中。
聲明語句結束符，可以自定義:
DELIMITER $$
聲明存儲過程:
CREATE PROCEDURE pro_name(IN num int)
存儲過程開始和結束符號:
BEGIN .... END
變量賦值:
SET num=1
變量定義:
DECLARE num int unsigned default 100;
創建mysql存儲過程、存儲函數:
create procedure 存儲過程名(參數)
存儲過程體:
create function 存儲函數名(參數)
參數解析：
MySQL存儲過程的參數用在存儲過程的定義，共有三種參數類型,IN,OUT,INOUT,形式如：
CREATEPROCEDURE 存儲過程名([[IN |OUT |INOUT ] 參數名 數據類形...])
IN 輸入參數：表示調用者向過程傳入值（傳入值可以是字面量或變量）
OUT 輸出參數：表示過程向調用者傳出值(可以返回多個值)（傳出值只能是變量）
INOUT 輸入輸出參數：既表示調用者向過程傳入值，又表示過程向調用者傳出值（值只能是變量）
需要注意的是，這里的參數根據需求而定，如果不需要參數，亦可不填寫！
下面依次根據實例對不同的情況進行演示：
首先準備一張my_datas表：
mysql> create table if not exists `my_datas`( `id` int(20) not null auto_increment comment '數據id', `name` varchar(30) default null comment '姓名', `address` varchar(45) default null comment '地址', `time` datetime default null comment '創建時間', primary key(`id`) )engine=innodb auto_increment=1 default charset=utf8mb4; Query OK, 0 rows affected, 1 warning (0.33 sec)
查看結構是否正確：
mysql> show columns from `my_datas`; +---------+-------------+------+-----+-------------------+-------------------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+-------------------+-------------------+ | id | int | NO | PRI | NULL | auto_increment | | name | varchar(30) | YES | | NULL | | | address | varchar(45) | YES | | NULL | | | time | datetime | YES | | NULL | DEFAULT_GENERATED | +---------+-------------+------+-----+-------------------+-------------------+ 4 rows in set (0.00 sec)
(1),創建存儲函數，向數據表中插入50條數據
mysql> delimiter // #定義結束符
mysql> drop procedure if exists addMyData;
-> create procedure addMyData() #創建一個存儲過程，名為：addMyData
-> begin
-> declare num int;
-> set num =1;
-> while num <=50 #插入50條數據
-> do
-> insert into `my_datas`(id,name,address,time)
-> values(null,concat('數據_',num,'號'),concat('北京四 合院',round(rand()*10),'號'),concat(current_timestamp())); #concat函數拼接信息
-> set num =num +1;
-> end
-> while;
-> end //;
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> delimiter; #將語句的結束符號恢復為分號默認情況下，存儲過程和默認數據庫相關聯，如果想指定存儲過程創建在某個特定的數據庫下，那么在過程名前面加數據庫名做前綴。 在定義過程時，使用 DELIMITER //命令將語句的結束符號從分號 ; 臨時改為兩個//，使得過程體中使用的分號被直接傳遞到服務器，而不會被客戶端解釋。
調用存儲函數，并查詢插入結果
mysql> call addMyData(); Query OK, 1 row affected (0.58 sec) mysql> select * from `my_datas`; +----+--------------+----------------------+---------------------+ | id | name | address | time | +----+--------------+----------------------+---------------------+ | 1 | 數據_1號 | 北京四合院3號 | 2022-08-24 14:21:17 | | 2 | 數據_2號 | 北京四合院8號 | 2022-08-24 14:21:17 | | 3 | 數據_3號 | 北京四合院4號 | 2022-08-24 14:21:17 | | 4 | 數據_4號 | 北京四合院3號 | 2022-08-24 14:21:17 | | 5 | 數據_5號 | 北京四合院3號 | 2022-08-24 14:21:17 | | 6 | 數據_6號 | 北京四合院7號 | 2022-08-24 14:21:17 | | 7 | 數據_7號 | 北京四合院7號 | 2022-08-24 14:21:17 | | 8 | 數據_8號 | 北京四合院5號 | 2022-08-24 14:21:17 | | 9 | 數據_9號 | 北京四合院1號 | 2022-08-24 14:21:17 | | 10 | 數據_10號 | 北京四合院1號 | 2022-08-24 14:21:17 | | 11 | 數據_11號 | 北京四合院3號 | 2022-08-24 14:21:17 | | 12 | 數據_12號 | 北京四合院1號 | 2022-08-24 14:21:17 | | 13 | 數據_13號 | 北京四合院6號 | 2022-08-24 14:21:17 | | 14 | 數據_14號 | 北京四合院8號 | 2022-08-24 14:21:17 | | 15 | 數據_15號 | 北京四合院3號 | 2022-08-24 14:21:17 | | 16 | 數據_16號 | 北京四合院9號 | 2022-08-24 14:21:17 | | 17 | 數據_17號 | 北京四合院7號 | 2022-08-24 14:21:17 | | 18 | 數據_18號 | 北京四合院8號 | 2022-08-24 14:21:17 | | 19 | 數據_19號 | 北京四合院1號 | 2022-08-24 14:21:17 | | 20 | 數據_20號 | 北京四合院9號 | 2022-08-24 14:21:17 | | 21 | 數據_21號 | 北京四合院2號 | 2022-08-24 14:21:17 | | 22 | 數據_22號 | 北京四合院2號 | 2022-08-24 14:21:17 | | 23 | 數據_23號 | 北京四合院3號 | 2022-08-24 14:21:17 | | 24 | 數據_24號 | 北京四合院10號 | 2022-08-24 14:21:17 | | 25 | 數據_25號 | 北京四合院1號 | 2022-08-24 14:21:17 | | 26 | 數據_26號 | 北京四合院5號 | 2022-08-24 14:21:17 | | 27 | 數據_27號 | 北京四合院1號 | 2022-08-24 14:21:17 | | 28 | 數據_28號 | 北京四合院3號 | 2022-08-24 14:21:17 | | 29 | 數據_29號 | 北京四合院10號 | 2022-08-24 14:21:17 | | 30 | 數據_30號 | 北京四合院10號 | 2022-08-24 14:21:17 | | 31 | 數據_31號 | 北京四合院0號 | 2022-08-24 14:21:17 | | 32 | 數據_32號 | 北京四合院2號 | 2022-08-24 14:21:17 | | 33 | 數據_33號 | 北京四合院8號 | 2022-08-24 14:21:17 | | 34 | 數據_34號 | 北京四合院6號 | 2022-08-24 14:21:17 | | 35 | 數據_35號 | 北京四合院5號 | 2022-08-24 14:21:17 | | 36 | 數據_36號 | 北京四合院7號 | 2022-08-24 14:21:17 | | 37 | 數據_37號 | 北京四合院1號 | 2022-08-24 14:21:17 | | 38 | 數據_38號 | 北京四合院3號 | 2022-08-24 14:21:17 | | 39 | 數據_39號 | 北京四合院3號 | 2022-08-24 14:21:17 | | 40 | 數據_40號 | 北京四合院6號 | 2022-08-24 14:21:17 | | 41 | 數據_41號 | 北京四合院9號 | 2022-08-24 14:21:17 | | 42 | 數據_42號 | 北京四合院7號 | 2022-08-24 14:21:17 | | 43 | 數據_43號 | 北京四合院9號 | 2022-08-24 14:21:17 | | 44 | 數據_44號 | 北京四合院5號 | 2022-08-24 14:21:17 | | 45 | 數據_45號 | 北京四合院8號 | 2022-08-24 14:21:17 | | 46 | 數據_46號 | 北京四合院3號 | 2022-08-24 14:21:17 | | 47 | 數據_47號 | 北京四合院1號 | 2022-08-24 14:21:17 | | 48 | 數據_48號 | 北京四合院7號 | 2022-08-24 14:21:17 | | 49 | 數據_49號 | 北京四合院10號 | 2022-08-24 14:21:17 | | 50 | 數據_50號 | 北京四合院9號 | 2022-08-24 14:21:17 | +----+--------------+----------------------+---------------------+ 50 rows in set (0.00 sec)
這樣，50條需要的數據就能快速插入完畢。但是局限性在于插入的數據是在定義存儲過程中寫死的，不夠靈活。
(2),in輸入參數的使用
為了方便靈活的插入/修改/刪除/查詢我們需要的數據，我們可以定義in 來輸入參數，如下：
創建一個用域刪除指定id信息的存儲過程
mysql> delimiter !! mysql> create procedure delete_data(in ids int) #定義一個輸入的參數 -> begin -> delete from `my_datas` where id=ids; -> end !! Query OK, 0 rows affected (0.05 sec) mysql> delimiter ;
執行存儲過程并查看數據庫信息情況：
mysql> call delete_data(3); #輸入指定參數 Query OK, 1 row affected (0.04 sec) mysql> select * from `my_datas` limit 3; +----+-------------+---------------------+---------------------+ | id | name | address | time | +----+-------------+---------------------+---------------------+ | 1 | 數據_1號 | 北京四合院3號 | 2022-08-24 14:21:17 | | 2 | 數據_2號 | 北京四合院8號 | 2022-08-24 14:21:17 | | 4 | 數據_4號 | 北京四合院3號 | 2022-08-24 14:21:17 | +----+-------------+---------------------+---------------------+ 3 rows in set (0.00 sec)
在存儲過程中設置了需要傳參的變量ids，調用存儲過程的時候，通過傳參將3賦值給ids，然后進行存儲過程里的SQL操作。
(3),out參數的使用
構建一個簡單的存儲過程：
mysql> create procedure sums(a int ,b int ,out c int) -> begin -> set c = a+b; -> end // Query OK, 0 rows affected (0.03 sec) mysql> delimiter ;
進行驗證：
mysql> call sums(11,25,@s); Query OK, 0 rows affected (0.01 sec) mysql> select @s; #正常輸出c的值 +------+ | @s | +------+ | 36 | +------+
(4),inout參數的使用
構建一個inout型的存儲過程：
mysql> delimiter // mysql> create procedure inout_test(inout test int) -> begin -> select test; -> set test=100; -> select test; -> end -> // Query OK, 0 rows affected (0.07 sec) mysql> delimiter ;
測試執行過程：
mysql> set @test=52 -> ; Query OK, 0 rows affected (0.00 sec) mysql> call inout_test(@test); +------+ | test | +------+ | 52 | +------+ 1 row in set (0.00 sec) +------+ | test | +------+ | 100 | +------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) #調用了inout_param存儲過程，接受了輸入的參數，也輸出參數，改變了變量 mysql> select @test; +-------+ | @test | +-------+ | 100 | +-------+ 1 row in set (0.00 sec)
注意事項：參數的名字不要和列名相同，不然在過程體中，參數名會當作列名來處理，并且，存儲過程命名盡量不要跟一些常用函數命名一樣，否則sql檢查會出錯。
局部變量聲明一定要放在存儲過程體的開始：
DECLARE 變量名 數據類型（int，float，date……）
例如：
DECLARE numbers int
DECLARE l_datetime datetime DEFAULT '2022-09-31 16:12:35';
DECLARE l_varchar varchar(255) DEFAULT '黎治躍在內卷！';
SET 變量名 = 表達式值 [,variable_name = expression ...]
mysql > SELECT '黎治躍在內卷' into @l; Query OK, 0 rows affected (0.00 sec) mysql > SELECT @l; +-------------+ | @l | +-------------+ | 黎治躍在內卷 | +-------------+ 1 row in set (0.00 sec) mysql> set @z='做個人吧，黎治躍'; Query OK, 0 rows affected (0.00 sec) mysql> select @z; +--------------------------+ | @z | +--------------------------+ | 做個人吧，黎治躍 | +--------------------------+ 1 row in set (0.00 sec) mysql> SET @y=5+2+52; Query OK, 0 rows affected (0.00 sec) mysql> select @y; +------+ | @y | +------+ | 59 | +------+ 1 row in set (0.00 sec)
存儲過程中使用用戶變量：
mysql> create procedure see() select concat(@lzy,'Java全能選手'); Query OK, 0 rows affected (0.02 sec) mysql> set @lzy ='黎治躍：'; Query OK, 0 rows affected (0.00 sec) mysql> call see(); +---------------------------------+ | concat(@lzy,'Java全能選手') | +---------------------------------+ | 黎治躍：Java全能選手 | +---------------------------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)
在存儲過程間傳遞全局范圍的用戶變量：
mysql> CREATE PROCEDURE p1() SET @last_procedure='l1';
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_procedure);
Query OK, 0 rows affected (0.03 sec)
mysql> CALL p1( );
mysql> CALL p2( );
+-----------------------------------------------+
| CONCAT('Last procedure was ',@last_proc |
+-----------------------------------------------+
| Last procedure was l1 |
+-----------------------------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)注意:
1、用戶變量名一般以@開頭
2、濫用用戶變量會導致程序難以理解及管理
查看具體存儲過程信息
show create procedure 存儲過程名 \G
查看所有存儲過程
show procedure status \G
模糊查詢對應的存儲過程信息
show procedure status like “模糊查詢名” \G
查看指定表中的存儲信息
select * from 表名 where routine_name = '存儲過程名' \G
mysql > delimiter // mysql > create prcedure test2(in s int) -> begin -> declare num int; -> set num=s+1; -> if num=0 then -> insert into `new_table` values(555); -> end if; -> if s=0 then -> update `new_table` set s1=s1+1; -> else -> update `new_table` set s1=s1+2; -> end if; -> end; -> // Query OK, 0 rows affected (0.07 sec) mysql > delimiter ;
mysql > delimiter // mysql > create procedure test(in sb int) -> begin -> declare num int; -> set num=sb+1; -> case num -> when 0 then -> insert into `new_table` values(23); -> when 1 then -> insert into `new_table` values(24); -> else -> insert into `new_table` values(25); -> end case; -> end; -> // Query OK, 0 rows affected (0.06 sec) mysql > delimiter ;
mysql > delimiter // mysql > create procedure test() -> begin -> declare num int; -> set num=0; -> while num<6 do -> insert into `new_tables` values(num); -> set num=num+1; -> end while; -> end; -> // Query OK, 0 rows affected (0.03 sec) mysql > delimiter ;
這個語句與while語句的不同之處在于while是先檢查再執行，而repeat語句是執行操作后檢查結果。
mysql > delimiter // mysql > create procedure test() -> begin -> declare num int; -> set num=0; -> repeat -> insert into `new_table` values(num); -> set num=num+1; -> until num>=5 #循環條件 -> end repeat; -> end; -> // Query OK, 0 rows affected (0.04 sec) mysql > delimiter ;
loop循環相當于一個while True ...if ... break 循環，與repeat一循環不同，loop可以在循環體的任何位置通過leave離開循環，而repeat只能在循環體最后進行until判斷 。此外loop還提供了循環標簽，用于在嵌套·循環中標識不同層次的循環。
mysql > delimiter // mysql > create procedure test5() -> begin -> declare num int; -> set num=0; -> LOOP1:loop -> insert into `new_table` values(num); -> set num=num+1; -> if num >=5 then -> leave LOOP1; -> end if; -> end loop; -> end; -> // Query OK, 0 rows affected (0.04 sec) mysql > delimiter ;
存儲過程體包含了在過程調用時必須執行的語句，例如：dml、ddl語句，if-then-else和while-do語句、聲明變量的declare語句等
過程體格式：以begin開始，以end結束(可嵌套)
BEGIN
BEGIN
BEGIN
statements;
END
END
END
每個嵌套塊及其中的每條語句，必須以分號結束，表示過程體結束的begin-end塊(又叫做復合語句compound statement)，則不需要分號。
為語句塊貼標簽:
label1: BEGIN
label2: BEGIN
label3: BEGIN
statements;
END label3 ;
END label2;
END label1
標簽有兩個作用：
1、增強代碼的可讀性
2、在某些語句(例如:leave和iterate語句)，需要用到標簽
刪除表字段
ALTER TABLE 表名 DROP 字段名;添加表字段
ALTER TABLE 表名 ADD 字段名 字段數據類型;添加表字段默認值
ALTER TABLE 表名 ALTER 字段名 SET DEFAULT 默認值;刪除表字段默認值
ALTER TABLE 表名 ALTER 字段名 DROP DEFAULT;
另外，如果需要將表字段插入指定的位置，可以使用MySQL提供的關鍵字 FIRST (設定位第一列)， AFTER 字段名（設定位于某個字段之后）。使用show columns查看表結構變化：
mysql> show columns from user; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | username | varchar(30) | YES | | NULL | | | address | varchar(45) | YES | | NULL | | +----------+-------------+------+-----+---------+----------------+ 3 rows in set (0.04 sec) mysql> alter table user add time datetime; #添加一個time字段 Query OK, 0 rows affected (0.17 sec) Records: 0 Duplicates: 0 Warnings: 0 #在address后面添加一個sex字段 mysql> alter table user add sex tinyint(1) after address;Query OK, 0 rows affected, 1 warning (0.41 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql> show columns from user; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | username | varchar(30) | YES | | NULL | | | address | varchar(45) | YES | | NULL | | | sex | tinyint(1) | YES | | NULL | | | time | datetime | YES | | NULL | | +----------+-------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)
當需要修改字段類型或者字段名時，常常會使用到change與modify關鍵字
modify使用
alter table 表名 modify 字段名 字段屬性（更改后）
modify主要用于更改數據字段范圍，當遇到在數據庫構建時，范圍數據定義過小，或者范圍數據定義過大浪費內存空間時，對字段屬性的更改。
change使用
alter table 表名 change old字段名 new字段名 對應的字段屬性
change關鍵字主要用于對字段名的更改，在語法上CHANGE 關鍵字之后，緊跟著的是你要修改的字段名，然后指定新字段名及類型。但是CHANGE又不僅僅可以更改字段名，它也可以同時修改指明后的字段屬性，同時對字段名和字段屬性進行修改。
#使用change僅修改字段名 mysql> alter table user change address address varchar(40); Query OK, 499 rows affected (0.38 sec) Records: 499 Duplicates: 0 Warnings: 0 #使用modify僅修改字段屬性 mysql> alter table user modify address varchar(45); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 #使用change同時修改字段名和字段屬性 mysql> alter table user change address u_address varchar(40)); Query OK, 499 rows affected (0.26 sec) Records: 499 Duplicates: 0 Warnings: 0 #查看更改后的表結構 mysql> show columns from user; +-----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | username | varchar(30) | YES | | NULL | | | u_address | varchar(40) | YES | | NULL | | | sex | tinyint(1) | YES | | NULL | | | time | datetime | YES | | NULL | | +-----------+-------------+------+-----+---------+----------------+ 5 rows in set (0.01 sec)
修改指定表的數據引擎
alter table user engine=指定數據引擎
例如：ALTER TABLE testalter_tbl ENGINE = MYISAM;
如果對當前數據表信息不清楚的話可以通過SHOW TABLE STATUS命令進行查看。
例如：查看當前user表的信息
mysql> show table status like 'user' \G; *************************** 1. row *************************** Name: user Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 499 Avg_row_length: 131 Data_length: 65536 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: 500 Create_time: 2022-08-24 17:32:27 Update_time: NULL Check_time: NULL Collation: utf8mb4_0900_ai_ci Checksum: NULL Create_options: Comment: 1 row in set (0.03 sec) ERROR: No query specified
修改表名
alter table old表名 rename to new表名
此外，ALTER關鍵字的操作也不僅僅局限于操作表，在以后的索引，外鍵上也有很多作用。
讀到這里，這篇“MySQL存儲過程創建使用及實現數據快速插入的方法是什么”文章已經介紹完畢，想要掌握這篇文章的知識點還需要大家自己動手實踐使用過才能領會，如果想了解更多相關內容的文章，歡迎關注億速云行業資訊頻道。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
