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
  • WPF 已知問題 監聽 WMI 事件導致觸摸失效
    示例項目結構 在 Visual Studio 中創建一個 WinForms 應用程式後，項目結構如下所示： MyWinFormsApp/ │ ├───Properties/ │ └───Settings.settings │ ├───bin/ │ ├───Debug/ │ └───Release/ ...
  • [STAThread]特性
    [STAThread] 特性用於需要與 COM 組件交互的應用程式，尤其是依賴單線程模型（如 Windows Forms 應用程式）的組件。在 STA 模式下，線程擁有自己的消息迴圈，這對於處理用戶界面和某些 COM 組件是必要的。 [STAThread] static void Main(stri ...
  • UWP WinUI3 傳入 AddHandler 的 RoutedEventHandler 類型與事件所需不匹配將拋出參數異常
    在WinForm中使用全局異常捕獲處理 在WinForm應用程式中，全局異常捕獲是確保程式穩定性的關鍵。通過在Program類的Main方法中設置全局異常處理，可以有效地捕獲並處理未預見的異常，從而避免程式崩潰。 註冊全局異常事件 [STAThread] static void Main() { / ...
  • .NET 多版本 WinForm 開源控制項庫 SunnyUI
    前言 給大家推薦一款開源的 Winform 控制項庫，可以幫助我們開發更加美觀、漂亮的 WinForm 界面。 項目介紹 SunnyUI.NET 是一個基於 .NET Framework 4.0+、.NET 6、.NET 7 和 .NET 8 的 WinForm 開源控制項庫，同時也提供了工具類庫、擴展 ...
  • .net core8 使用Swagger（附當前源碼）
    說明 該文章是屬於OverallAuth2.0系列文章，每周更新一篇該系列文章（從0到1完成系統開發）。 該系統文章，我會儘量說的非常詳細，做到不管新手、老手都能看懂。 說明：OverallAuth2.0 是一個簡單、易懂、功能強大的許可權+可視化流程管理系統。 有興趣的朋友，請關註我吧(*^▽^*) ...
  • WPF 什麼時候 VisualTreeHelper.GetDescendantBounds 將返回無窮大
    一、下載安裝 1.下載git 必須先下載並安裝git，再TortoiseGit下載安裝 git安裝參考教程：https://blog.csdn.net/mukes/article/details/115693833 2.TortoiseGit下載與安裝 TortoiseGit，Git客戶端，32/6 ...
  • C# 開源教程帶你輕鬆掌握數據結構與演算法
    前言 在項目開發過程中，理解數據結構和演算法如同掌握蓋房子的秘訣。演算法不僅能幫助我們編寫高效、優質的代碼，還能解決項目中遇到的各種難題。 給大家推薦一個支持C#的開源免費、新手友好的數據結構與演算法入門教程：Hello演算法。 項目介紹 《Hello Algo》是一本開源免費、新手友好的數據結構與演算法入門 ...
  • 第22篇 生成proto文件bat腳本
    1.生成單個Proto.bat內容 @rem Copyright 2016, Google Inc. @rem All rights reserved. @rem @rem Redistribution and use in source and binary forms, with or with ...
  • 記一次 .NET某上位機視覺程式 卡死分析
    一：背景 1. 講故事 前段時間有位朋友找到我，說他的窗體程式在客戶這邊出現了卡死，讓我幫忙看下怎麼回事？dump也生成了，既然有dump了那就上 windbg 分析吧。 二：WinDbg 分析 1. 為什麼會卡死 窗體程式的卡死，入口門檻很低，後續往下分析就不一定了，不管怎麼說先用 !clrsta ...
  • 醫學DICOM文件與PACS系統進行數據傳輸
    前言 人工智慧時代，人臉識別技術已成為安全驗證、身份識別和用戶交互的關鍵工具。 給大家推薦一款.NET 開源提供了強大的人臉識別 API，工具不僅易於集成，還具備高效處理能力。 本文將介紹一款如何利用這些API，為我們的項目添加智能識別的亮點。 項目介紹 GitHub 上擁有 1.2k 星標的 C# ...
所有分類