MySQL從刪庫到跑路_高級（四）——存儲過程

来源:https://www.cnblogs.com/cxydczzl/archive/2018/11/06/9915666.html
-Advertisement-
Play Games

作者：天山老妖S 鏈接：http://blog.51cto.com/9291927 一、存儲過程簡介 1、存儲過程簡介 存儲過程是一組具有特定功能的SQl語句集組成的可編程的函數，經編譯創建並保存在資料庫中，用戶可通過指定存儲過程的名字並給定參數來調用執行。 存儲過程是資料庫管理中常用的技術之一，可 ...


作者：天山老妖S

鏈接：http://blog.51cto.com/9291927

 

一、存儲過程簡介

1、存儲過程簡介

存儲過程是一組具有特定功能的SQl語句集組成的可編程的函數，經編譯創建並保存在資料庫中，用戶可通過指定存儲過程的名字並給定參數來調用執行。

存儲過程是資料庫管理中常用的技術之一，可以很方便的做些類似數據統計、數據分析等工作，SQL SERVER、ORACLE、MySQL都支持存儲過程，但不同的資料庫環境語法結構有所區別。

2、存儲過程的優點

A、存儲過程增強了SQL語言的功能和靈活性。存儲過程可以用流控制語句編寫，有很強的靈活性，可以完成複雜的判斷和較複雜的運算。

B、存儲過程允許標準組件式編程。存儲過程被創建後，可以在程式中被多次調用，而不必重新編寫該存儲過程的SQL語句。而且資料庫專業人員可以隨時對存儲過程進行修改，對應用程式源代碼毫無影響。

C、存儲過程能實現較快的執行速度。如果某一操作包含大量的Transaction-SQL代碼或分別被多次執行，那麼存儲過程要比批處理的執行速度快很多。因為存儲過程是預編譯的。在首次運行一個存儲過程時查詢，優化器對其進行分析優化，並且給出最終被存儲過程在系統表中的執行計劃。而批處理的Transaction-SQL語句在每次運行時都要進行編譯和優化，速度相對要慢一些。

D、存儲過程能夠減少網路流量。針對同一個資料庫對象的操作（如查詢、修改），如果操作所涉及的Transaction-SQL語句被組織存儲過程，那麼當在客戶電腦上調用該存儲過程時，網路中傳送的只是該調用語句，從而大大增加了網路流量並降低了網路負載。

E、存儲過程可被作為一種安全機制來充分利用。系統管理員通過執行某一存儲過程的許可權進行限制，能夠實現對相應的數據的訪問許可權的限制，避免了非授權用戶對數據的訪問，保證了數據的安全。

二、存儲過程的使用

1、存儲過程的創建

創建存儲過程的語法：

CREATE
    [DEFINER = { user | CURRENT_USER }]
　PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name typecharacteristic:    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
　　Valid SQL routine statement[begin_label:] BEGIN　　[statement_list]
　　　　END [end_label]

IN輸入參數：表示該參數的值必須在調用存儲過程時指定，在存儲過程中修改改參數的值不能被返回，為預設值。

OUT輸出參數：該值可在存儲過程內部被改變，並可返回。

INOUT輸入輸出參數：調用時指定，並且可被改變和返回。

A、無參數的存儲過程創建

創建查找平均分最高的前三名同學的存儲過程

create procedure getMax() BEGIN select a.sname as '姓名', AVG(b.mark) as '平均分' from TStudent a join TScore b on a.studentID = b.studentID group by b.studentID order by '平均分' DESC limit 3; END;

B、帶輸入參數的存儲過程創建

查找指定班級的平均分最高的前三名學生

create procedure getMaxByClass(in classname VARCHAR(10)) BEGIN select a.sname as '姓名', AVG(b.mark) as '平均分' from TStudent a join TScore b on a.studentID = b.studentID where a.class = classname group by b.studentID order by '平均分' DESC limit 3; END

C、帶輸入參數和輸出參數的存儲過程創建

根據輸入的班級，找到學號最大的學生，將學號存儲到輸出參數。

create procedure getMaxSIDByClass(IN classname VARCHAR(20), out maxid int) BEGIN select MAX(studentID) into maxid from TStudent where class = classname; END;

2、存儲過程的刪除

drop procedure sp_name;

不能在一個存儲過程中刪除另一個存儲過程，只能調用另一個存儲過程。

3、存儲過程的調用

call sp_name[(傳參)];

存儲過程名稱後面必須加括弧，即使存儲過程沒有參數傳遞。

4、存儲過程信息的查看

show procedure status;

顯示資料庫中所有存儲的存儲過程基本信息，包括所屬資料庫，存儲過程名稱，創建時間等。

show create procedure sp_name;

顯示某一個存儲過程的詳情信息。

5、使用存儲過程插入數據

create procedure insertTStudent(in sid CHAR(5), name CHAR(10), ssex CHAR(1) ) BEGIN insert into TStudent (studentID, sname, sex) VALUES(sid, name, ssex); select * from TStudent where studentID = sid; END; call insertTStudnet('01020','孫悟空','男');

6、使用存儲過程刪除數據

根據提供的學號刪除學生的學生成績，再刪除學生

create procedure deleteStudent(in sid CHAR(5) ) BEGIN delete from TScore where studentID = sid; delete from TStudent wheres studentID = sid; END;

7、使用存儲過程備份還原數據

A、使用存儲過程備份數據

創建存儲過程備份學生表，根據指定的表明創建新表，將TStudent表中的記錄導入到新表。

create procedure backupStudent(in tablename CHAR(10))BEGINset @sql1=CONCAT('create table ',tablename,'
(
studentID VARCHAR(5),
sname VARCHAR(10),
sex CHAR(1),
cardID VARCHAR(20),
Birthday DATETIME,
email VARCHAR(20),
class VARCHAR(10),
enterTime DATETIME
)');prepare CT1 from @sql1;EXECUTE CT1;set @sql2=CONCAT('insert into ', tablename, 
'(studentID,sname,sex,cardID,Birthday,email,class,enterTime)
select studentID,sname,sex,cardID,Birthday,email,class,enterTime from TStudent');PREPARE CT2 from @sql2;EXECUTE CT2;END;call backupStudent('table2019');

B、使用當前時間作為表名備份數據

創建存儲過程，使用系統當前事件構造新的表名，備份Tstudent表中的記錄。

create procedure backupStudentByDateTime()BEGINDECLARE tablename VARCHAR(20);set tablename = CONCAT('Table', REPLACE(REPLACE(REPLACE(now(),' ',''),':',''),'-',''));set @sql1=CONCAT('create table ',tablename,'
(
studentID VARCHAR(5),
sname VARCHAR(10),
sex CHAR(1),
cardID VARCHAR(20),
Birthday DATETIME,
email VARCHAR(20),
class VARCHAR(10),
enterTime DATETIME
)');prepare CT1 from @sql1;EXECUTE CT1;set @sql2=CONCAT('insert into ', tablename, 
'(studentID,sname,sex,cardID,Birthday,email,class,enterTime)
select studentID,sname,sex,cardID,Birthday,email,class,enterTime from TStudent');PREPARE CT2 from @sql2;EXECUTE CT2;ENDcall backupStudentByDateTime();

C、使用存儲過程還原數據

創建存儲過程，根據輸入的學號從指定的表還原學生記錄，存儲過程先刪除指定的學號的TStudent表中學生記錄，再從指定的表中插入該學生到Tstudent表。

create procedure restoreStudent(in sid VARCHAR(5), in tablename VARCHAR(20))BEGINset @sql1=concat('delete from TStudent where studentid=',sid);prepare CT1 from @sql1;EXECUTE CT1;set @sql2=concat('insert into TStudent
 (Studentid,sname,sex,cardID,Birthday,Email,Class,enterTime)
  select Studentid,sname,sex,cardID,Birthday,Email,Class,enterTime
  from ',tablename,' where studentid=',sid);prepare CT2 from @sql2;EXECUTE CT2;END;

修改某個學生的記錄

update TStudent set sname = '孫悟空' where studentID = '00997';

從指定表中恢複數據

call restore Student('00997','Table20180404215950');

查看恢復的結果

select * from TStudent where studentID = '00997';

三、存儲過程實例

1、增加學生到資料庫表

create procedure addStudent(in num int)
begin
declare i int;
set i=1;delete from TStudent;while num>=i doinsert TStudent values (
       LPAD(convert(i,char(5)),5,'0'),
       CreateName(),       if(ceil(rand()*10)%2=0,'男','女'),
       RPAD(convert(ceil(rand()*1000000000000000000),char(18)),18,'0'),
       Concat(convert(ceil(rand()*10)+1980,char(4)),'-',LPAD(convert(ceil(rand()*12),char(2)),2,'0'),'-',LPAD(convert(ceil(rand()*28),char(2)),2,'0')),
       Concat(PINYIN(sname),'@hotmail.com'),
       case ceil(rand()*3) when 1 then '網路與網站開發' when 2 then 'JAVA' ELSE 'NET' END,
       NOW()
);
set i=i+1;
end while;select * from TStudent;
end

2、給學生添加成績

create procedure fillScore()
begin
DECLARE St_Num INT;
DECLARE Sb_Num INT;
DECLARE i1 INT;
DECLARE i2 INT;set i1=1;set i2=1;
delete from TScore;select count(*) into St_Num from TStudent;select count(*) into Sb_Num from TSubject;while St_Num>=i1 doset i2=1;while Sb_Num>=i2 doinsert TScore values
 (LPAD(convert(i1,char(5)),5,'0'),LPAD(convert(i2,char(4)),4,'0'),ceil(50+rand()*50));set i2=i2+1;
END WHILE;set i1=i1+1;
END WHILE;
end

 

喜歡的小伙伴們可以搜索我們個人的微信公眾號“程式員的成長之路”點擊關註或掃描下方二維碼


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • PyMySQL模塊的使用
    安裝： 基本使用 pymysql增刪該查 增刪改 查詢 ...
  • （轉）聊聊Greenplum的那些事
    開卷有益——作者的話 有時候真的感嘆人生歲月匆匆，特別是當一個IT人沉浸於某個技術領域十來年後，驀然迴首，總有說不出的萬千感慨。 筆者有幸從04年就開始從事大規模數據計算的相關工作，08年作為Greenplum 早期員工加入Greenplum團隊（當時的工牌是“005”，哈哈），記得當時看了一眼Gr ...
  • mysql 嚴格模式 Strict Mode說明（text 欄位不能加預設或者 不能加null值得修改方法）
    mysql 嚴格模式 Strict Mode說明 1.開啟與關閉Strict Mode方法找到mysql安裝目錄下的my.cnf（windows系統則是my.ini)文件 在sql_mode中加入STRICT_TRANS_TABLES則表示開啟嚴格模式，如沒有加入則表示非嚴格模式，修改後重啟mysq ...
  • Mysql 事務日誌(Ib_logfile)
    mysql的innodb中事務日誌ib_logfile(0/1)概念：事務日誌或稱redo日誌，在mysql中預設以ib_logfile0,ib_logfile1名稱存在,可以手工修改參數，調節開啟幾組日誌來服務於當前mysql資料庫,mysql採用順序，迴圈寫方式，每開啟一個事務時，會把一些相關信 ...
  • mssql sqlserver text數據類型專題說明
    摘要: 下文分享text數據類型的簡介及處理text數據類型所涉及的函數，如下所示: text 數據類型簡介: mssql sqlserver 常用數據類型簡介 mssql sqlserver text同ntext數據類型區別說明 mssql sqlserver varchar(max)同text、 ...
  • navicat 將自增長欄位重置（重新從1開始）的方法
    手機先說明，此語句會將你的表中數據全部刪除。 很簡單，運行如下sql語句： ...
  • Lepus搭建企業級資料庫全方位監控系統
    前言 Lepus(天兔)資料庫企業監控系統是一套由專業DBA針對互聯網企業開發的一款專業、強大的企業資料庫監控管理系統，企業通過Lepus可以對資料庫的實時健康和各種性能指標進行全方位的監控。目前已經支持MySQL、Oracle、MongoDB、Redis資料庫的全面監控。 Lepus可以在資料庫出 ...
  • Linux下安裝MySQL以及一些小坑
    第一次寫博客，各位湊合著看吧（假裝有人看）。 我這裡使用的是centos7。 1、首先打開終端，查看有沒有安裝過MySQL： 若為空則說明沒有安裝過，若要刪除可用yum remove mysql命令。 2、下載mysql的repo源： 安裝mysql-community-release-el7-5. ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類