mysql存儲過程詳細講解及完整實例下載

来源:http://www.cnblogs.com/sztx/archive/2017/08/13/7354421.html
-Advertisement-
Play Games

存儲過程（Stored Procedure）是一組為了完成特定功能的SQL語句集。經編譯後存儲在資料庫 中的存儲過程概念定義及存儲過程優點和缺點。 procedure通過完整的存儲過程實例來掌握存儲過程的創建、修改、刪除、顯示語法使用操作。掌握變數在實例中的應用，完整實例下載在以後工作中的應用... ...


一、存儲過程概念
　　1.存儲過程（Stored Procedure）是一組為了完成特定功能的SQL語句集。經編譯後存儲在資料庫 中。
　　2.存儲過程是資料庫中的一個重要對象，用戶通過指定存儲過程的名字並給出參數（如果該存儲過 程帶有參數）來執行它。
　　3.存儲過程是由流控制和SQL語句書寫的過程，這個過程經編譯和優化後存儲在資料庫伺服器中。
　　4.存儲過程可由應用程式通過一個調用來執行，而且允許用戶聲明變數。
　　5.同時，存儲過程可以接收和輸出參數、返回執行存儲過程的狀態值，也可以嵌套調用。
二、存儲過程優點
　　1.增強了SQL語句的功能和靈活性
　　2不需要反覆建立一系列處理步驟，保證了數據的完整性
　　3.降低了網路的通信量，客戶端調用存儲過程只需要傳存儲過程名和相關參數即可，與傳輸SQL語 句相比自然數據量少了很多
　　4.增強了使用的安全性，通過存儲過程可以使沒有許可權的用戶在控制之下間接地存取資料庫，從而 保證數據的安全。
　　5.可以實現集中控制，當規則發生改變時，只需要修改存儲過程就可以。。、
三、存儲過程缺點
　　1.調試不是很方便。
　　2.可能沒有創建存儲過程的權利。
　　3.重新編譯問題。
　　4.移植性問題。

四、變數

　　1.用戶變數：以”@”開始，形式為”@變數名。” 用戶變數跟MySQL客戶端是綁定的，設置的變數，只對當前用戶使用的客戶端生效.
　　2.全局變數：定義時，以如下兩種形式出現，set GLOBAL 變數名 或者 set @@global.變數名。show global variables; 對所有客戶端生效。只有super許可權才可以設置全局變數。
　　3.會話變數：只對連接的客戶端有效。一旦客戶端失去連接，變數失效。show session variables;
　　4.局部變數：作用範圍在begin到end語句塊之間。

　　　　4.1在該語句塊里設置的變數declare語句專門用於定義局部變數。declare numeric number(8,2)【MySQL的數據類型，如:int,float, date, varchar(length)】 default 9.95;

　　　　4.2變數賦值:SET 變數名 = 表達式值 [,variable_name= expression ...]，set numeric=1.2或者SELECT 2.3 into @x；

五、mysql 存儲程式

　　1.基本語法：create procedure  過程名 ([過程參數[,...]])[特性 ...] 過程體;先看基本例子

　　第一種：

delimiter ;;
create procedure proc_on_insert()
begin
end
;;
delimiter

　　第二種：

delimiter //
create procedure proc_on_insert()
begin
end
//
delimiter ;;

　　註意：

　　1).這裡需要註意的是delimiter // 和delimiter ;;兩句，delimiter是分割符的意思，因為MySQL預設以";"為分隔符，如果我們沒有聲明分割符，那麼編譯器會把存儲過程當成SQL語句進行處理，則存儲過程的編譯過程會報錯，所以要事先用delimiter關鍵字申明當前段分隔符，這樣MySQL才會將";"當做存儲過程中的代碼。
　　2).存儲過程根據需要可能會有輸入、輸出、輸入輸出參數，這裡有一個輸出參數s，類型是int型，如果有多個參數用","分割開。
　　3).過程體的開始與結束使用begin與emd進行標識。

　　2..調用存儲過程基本語法：call sp_name()

　　3.參數:MySQL存儲過程的參數用在存儲過程的定義，共有三種參數類型,IN,OUT,INOUT,形式如：

　　　　create procedure([[in |out |inout ] 參數名 數據類形...])
　　　　in輸入參數:表示該參數的值必須在調用存儲過程時指定，在存儲過程中修改該參數的值不能被返回，為預設值
　　　　out 輸出參數:該值可在存儲過程內部被改變，並可返回
　　　　inout 輸入輸出參數:調用時指定，並且可被改變和返回

　　　　3.1in參數例子：

drop procedure if exists prc_on_in;
delimiter ;;
create procedure prc_on_in(in num int)
begin
declare number int ;
set number=num;
select number;
end
;;
delimiter ;;
set @num=1;
call prc_on_in(@num);

　　3.2out參數創建例子

drop procedure if exists prc_on_out;
delimiter ;;
create procedure prc_on_out(out out_num int)
begin
select out_num;
set out_num=78;
select out_num;
end
;;
delimiter ;;
set @number=6;
call prc_on_out(@number);

　　3.3inout參數創建例子

drop procedure if exists prc_on_inout;
delimiter ;;
create procedure prc_on_inout(inout p_inout int)
begin
select p_inout;
set p_inout=100;
select p_inout;
end
;;
delimiter ;;
set @p_out=90;
call prc_on_inout(@p_out);

　　3.4存儲過程中的IF語句（if then elseif then else end if）

drop procedure if exists p_else;
create procedure p_else(in id int)
begin
    if (id > 0) then
        select '> 0' as id;
    elseif (id = 0) then
        select '= 0' as id;
    else
        select '< 0' as id;
    end if;
end;
set @p=-10;
call p_else(@p);

　　3.5存儲過程中的case when then

drop procedure if exists p_case;
delimiter ;;
create procedure p_case(  
    id int  
)  
begin  
    case id  
    when 1 then     
    select 'one' as trans;  
    when 2 then  
    select 'two' as trans;  
    when 3 then   
    select 'three' as trans;  
    else  
    select 'no trans' as trans;  
    end case;  
end;  
;;
delimiter ;;
set @id=1;
call p_case(@id);

　　3.6存儲過程中的while do … end while語句

drop procedure if exists p_while_do;  
create procedure p_while_do()  
begin  
    declare i int;  
        set i = 1;  
        while i <= 10 do  
            select concat('index : ', i) ;  
            set i = i + 1;  
        end while;  
end;  
call p_while_do();

　　3.7存儲過程中的repeat … until end repeat語句

drop procedure if exists p_repeat;
delimiter ;;
create procedure p_repeat(in parameter int)
BEGIN
     declare var int;  
     set var = parameter; 
     REPEAT
     set var = var - 1; 
     set parameter = parameter -2; 
     UNTIL var<0
     end REPEAT;
     select parameter;
END
;;
delimiter ;; 
set @parameter=1;
call p_repeat(@parameter);

　　這個REPEAT迴圈的功能和前面WHILE迴圈一樣，區別在於它的執行後檢查是否滿足迴圈條件（until i>=5），而WHILE則是執行前檢查（while i<5 do）。
　　不過要註意until i>=5後面不要加分號，如果加分號，就是提示語法錯誤。

3.8存儲過程中的loop ··· end loop語句

　　　　

drop procedure if exists p_loop;
delimiter;;
create procedure p_loop(in parameter int)
BEGIN
     declare var int;  
     set var = parameter; 
     LOOP_LABLE:loop
     set var = var - 1; 
     set parameter = parameter -2; 
     if var<0 THEN
   LEAVE LOOP_LABLE;
     END IF;
     end LOOP;
     select parameter;
END
;;
delimiter;;
set @parameter=4;
call p_loop(@parameter);

　　　　使用LOOP編寫同樣的迴圈控制語句要比使用while和repeat編寫的要複雜一些：在迴圈內部加入了IF……END IF語句，在IF語句中又加入了LEAVE語句，LEAVE語句的意思是離開迴圈，LEAVE的格式是：LEAVE 迴圈標號。

　　4.游標的使用 :定義游標 ，打開游標 ,使用游標 ,關閉游標例子

drop table if exists  person;
CREATE TABLE `person` (
  `id` int(11) NOT NULL DEFAULT '0',
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into person(age) value(1);
drop procedure if exists prc_test1;
delimiter ;;
create definer = root@localhost procedure prc_test1()
BEGIN
    declare var int;   
     /**跳出迴圈標識**/
   declare done INT DEFAULT FALSE;
     /**聲明游標**/
   declare cur cursor for select age from person;
   /**迴圈結束設置跳出標識**/
   declare continue handler for not FOUND set done = true;
   /**打開游標**/
   open cur;
     LOOP_LABLE:loop
        FETCH cur INTO var;
        select var;
     if done THEN
   LEAVE LOOP_LABLE;
     END IF;
     end LOOP;
     /**關閉游標**/
   CLOSE cur;
END;
;;
delimiter ;;
call prc_test1();

5.MySQL存儲過程的查詢

5.1.查看某個資料庫下麵的存儲過程

select name from mysql.proc where db=’資料庫名’;
或者
select routine_name frominformation_schema.routines where routine_schema='資料庫名';
或者
show procedure status where db='資料庫名';

5.2.查看存儲過程的詳細

show create procedure 資料庫.存儲過程名;

6、MySQL存儲過程的修改

ALTER PROCEDURE:更改用CREATE PROCEDURE 建立的預先指定的存儲過程，其不會影響相關存儲過程或存儲功能。

7.刪除存儲過程
drop procedure sp_name //註釋函數名

mysql存儲函數實例下載地址：http://pan.baidu.com/s/1gf1Swk7        密碼：282i


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

-Advertisement-
Play Games
更多相關文章
  • Android系統截屏的實現（附代碼）
    1.背景 寫博客快兩年了，寫了100+的文章，最火的文章也是大家最關註的就是如何實現android系統截屏。其實我們google android_screen_shot就會找到很對辦法，但那些都是很多年前的了，在android4.*版本後，android對於源碼進行了更正，使得以前的方法都不能夠使用 ...
  • 一步一步學MySQL-一致性非鎖定讀和鎖定讀
    一致性非鎖定讀（consistent nonlocking read） 一致性非鎖定讀是值InnoDB存儲引擎通過多版本控制（multi versioning）的方式來讀取當前執行時間資料庫中的數據。如果被讀的數據行被加了排他鎖，在讀取這行數據的時候並不會等待鎖釋放，而是讀取該行的一個快照數據。 之 ...
  • OLEDB數據源和目標組件
    在SSIS工程的開發過程中，OLEDB 數據源和目標組件是最常用的數據流組件。從功能上講，OLEDB 數據源組件用於從OLEDB 提供者（Provider）中獲取數據，傳遞給下游組件，OLEDB提供者是外部數據源，SQL Server是其中一種OLEDB提供者；OLEDB目標組件用於把數據流插入到關 ...
  • MySQL- 5.7 sys schema筆記
    如果轉載，請註明博文來源： www.cnblogs.com/xinysu/ ，版權歸 博客園 蘇家小蘿蔔 所有。望各位支持！ 如果轉載，請註明博文來源： www.cnblogs.com/xinysu/ ，版權歸 博客園 蘇家小蘿蔔 所有。望各位支持！ 如果轉載，請註明博文來源： www.cnblog ...
  • 3.storm-starter打包在storm集群上運行
    1.使用maven或者其他打包工具將storm-starter打成jar包 2.請將jar包用解壓工具打開在根目錄下找到defaults.yaml文件並將其刪除不然到時會報有multiply defaults.yaml的錯哦 3.用ftp工具將jar包上傳到linux系統主節點上 4.在各個節點上啟 ...
  • 關於mysql增刪改查的基本操作
    1.註釋語法：--，#2.尾碼是.sql的文件是資料庫查詢文件3.保存查詢4.在資料庫裡面 列有個名字叫欄位 行有個名字叫記錄CRUD操作：create 創建（添加）read 讀取update 修改delete 刪除1、添加數據insert into Info values('p009','張三', ...
  • SQL語句大全
    SQL即結構化查詢語言(Structured Query Language)，是一種特殊目的的編程語言，是一種資料庫查詢和程式設計語言，用於存取數據以及查詢、更新和管理關係資料庫系統；同時也是資料庫腳本文件的擴展名。SQL語句無論是種類還是數量都是繁多的，很多語句也是經常要用到的，SQL查詢語句就是 ...
  • mysql批量數據腳本
    mysql批量數據腳本 1 建表 2 設置參數 3 創建函數 a.隨機產生字元串 delimiter $$ create function rand_string(n int) returns varchar(255) begin declare chars_str varchar(100) def ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類