mysql存儲過程on
1、存儲過程定義：
存儲過程是事先經過編譯並存儲在資料庫中的一段 SQL 語句的集合，調用存儲過程可以簡化應用開發 人員的很多工作，減少數據在資料庫和應用伺服器之間的傳輸，對於提高數據處理的效率是有好處的。 存儲過程思想上很簡單，就是資料庫 SQL 語言層面的代碼封裝與重用。
2、特點：
封裝，復用 ： 可以把某一業務SQL封裝在存儲過程中，需要用到 的時候直接調用即可。
可以接收參數，也可以返回數據 ：再存儲過程中，可以傳遞參數，也可以接收返回 值。
減少網路交互，效率提升 ： 如果涉及到多條SQL，每執行一次都是一次網路傳 輸。 而如果封裝在存儲過程中，我們只需要網路交互一次可能就可以了。
3、基本語法
（1）創建：
（2）調用：
（3）查看：
（4）刪除
注意: 在命令行中，執行創建存儲過程的SQL時，需要通過關鍵字 delimiter 指定SQL語句的 結束符。
2. mysql存儲過程
MySQL存儲過程創建的格式如下：
CREATE PROCEDURE 過程名 ([過程參數[,...]])[特性 ...] 過程體
舉例代碼如下：
CREATE PROCEDURE proc1(OUT s int) BEGIN SELECT COUNT(*) INTO s FROM user; END
存儲過程根據需要可能會有輸入、輸出、輸入輸出參數，這里有一個輸出參數s，類型是int型，如果有多個參數用","分割開。
過程體的開始與結束使用BEGIN與END進行標識。
注意：MySQL在5.0以前並不支持存儲過程
3. mysql存儲過程中執行sql不成功
關鍵詞:啟動SQL
SERVER時自動執行存儲過程
將存儲過程創建在master資料庫中，然後企業管理器中找到這個存儲過程--右鍵--屬性--勾選"每當sql
server啟動時執行"。
或者在master中創建存儲過程後,執行語句設置為自動啟動
use
master
exec
sp_procoption
'存儲過程名','startup','on'
自動執行存儲過程
SQL
Server
啟動時可以自動執行一個或多個存儲過程。這些存儲過程必須由系統管理員創建，並在
sysadmin
固定伺服器角色下作為後台過程執行。這些過程不能有任何輸入參數。
對啟動過程的數目沒有限制，但是要注意，每個啟動過程在執行時都會佔用一個連接。如果必須在啟動時執行多個過程，但不需要並行執行，則可以指定一個過程作為啟動過程，讓該過程調用其它過程。這樣就只佔用一個連接。
在啟動時恢復了最後一個資料庫後，即開始執行存儲過程。若要跳過這些存儲過程的執行，請將啟動參數指定為跟蹤標記
4022。如果以最低配置啟動
SQL
Server（使用
-f
標記），則啟動存儲過程也不會執行。有關更多信息，請參見跟蹤標記。
若要創建啟動存儲過程，必須作為
sysadmin
固定伺服器角色的成員登錄，並在
master
資料庫中創建存儲過程。
使用
sp_procoption
可以：（中網互贏
手機客戶端
將現有存儲過程指定為啟動過程。
4. 五、MYSQL存儲過程和函數
• create procere用來創建 存儲過程 ，create function用來創建 函數
• Delimiter命令是改變語句的結束符 ，MySQL默認的結束符為;號，由於procere和function中的;號並不代表創建的結束，所以要替換成另外的結束符以便表示創建的結束
• rontine_body子句可以包含一個簡單的SQL語句，也可以包含多個SQL語句， 通過begin…end將這多個SQL語句 包含在一起
• MySQL存儲過程和函數中也可以包含類似create和drop等DDL語句
• comment子句用來寫入對存儲過程和函數的注釋
• Language子句用來表示此存儲過程和函數的創建語言
• 存儲過程和函數被標注為deterministic表明當輸入相同的參數是會返回相同的結果，反之如果是not deterministic則表示相同參數不會是相同結果，默認是not deterministic
• 相關屬性短語只有咨詢含義，並不是強制性的約束
• Drop procere/function語句用來 刪除指定名稱的存儲過程或函數
• Begin…end語句通常出現在存儲過程、函數和觸發器中，其中 可以包含一個或多個語句 ，每個語句用;號隔開
• 標簽label可以加在begin…end語句以及loop, repeat和while語句
• 語句中通過iterate和leave來控制流程，iterate表示返回指定標簽位置，leave表示跳出標簽
• Declare語句通常用來聲明本地變數、游標、條件或者handler
• Declare語句只允許出現在begin … end語句中而且必須出現在第一行
• Declare的順序也有要求，通常是先聲明本地變數，再是游標，然後是條件和handler
• 本地變數可以通過declare語句進行聲明
• 聲明後的變數可以通過select … into var_list進行賦值，或者通過set語句賦值，或者通過定義游標並使用fetch … into var_list賦值
• 通過declare聲明變數方法：
• MySQL支持if,case,iterate,leave,loop,while,repeat語句作為存儲過程和函數中的 流程式控制制語句 ，另外return語句也是函數中的特定流程式控制制語句
• Case語句在存儲過程或函數中表明了 復雜的條件選擇語句
• IF語句在存儲過程或函數中表明了 基礎的條件選擇語句
其中在 function 裡面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我們開啟了 bin-log, 我們就必須為我們的 function 指定一個參數。
在 MySQL 中創建函數時出現這種錯誤的解決方法：
set global log_bin_trust_function_creators=TRUE;
• Iterate語句 僅出現在loop,repeat,while循環語句中，其含義表示重新開始此循環
• Leave語句表明 退出指定標簽的流程式控制制語句塊
• 通常會用在begin…end，以及loop,repeat,while的循環語句中
• Loop語句是存儲過程或函數中表達 循環執行 的一種方式
• repeat語句是存儲過程或函數中表達 循環執行 的一種方式
• while語句是存儲過程或函數中表達 循環執行 的一種方式
• Return語句用在 函數中，用來終結函數的執行並將指定值返回給調用者
• Cursor游標用來 聲明一個數據集
• 游標的聲明必須在變數和條件聲明之後，在handler聲明之前
• Cursor close語句用來 關閉之前打開的游標
• Cursor declare語句用來聲明一個游標和指定游標對應的數據集合， 通常數據集合是一個select語句
• Cursor fetch語句用來獲取游標指定數據集的 下一行數據 並將各個欄位值賦予後面的變數
• Open cursor語句用來打開一個之前已經 聲明好的游標
• Declare condition語句命名 特定的錯誤條件 ，而該特定錯誤可以在declare…handler中指定 處理方法
• 比如在MySQL中1051error code表示的是unknown table的錯誤，如果要對這
個錯誤做特殊處理，可以用三種方法：
• Declare handler語句用來聲明一個handler來處理一個或多個特殊條件，當其中的某個條件滿足時則觸發其中的statement語句執行
• Statement可以是一個簡單SQL語句，也可以是begin…end組成的多個語句
• Handler_action子句聲明當執行完statement語句之後應該怎麼辦
Condition_value的值有以下幾種：
• 當condition發生但沒有聲明handler時，則存儲過程和函數依照如下規則處理
• create trigger語句用來創建一個觸發器，觸發器的作用是當表上有對應SQL語句發生時，則觸發執行
• 觸發器創建時需要 指定對應的表名 tbl_name
• Definer關鍵詞用來指定trigger的安全環境
• Trigger_time指定觸發器的執行時間，BEFORE和AFTER指定觸發器在表中的 每行數據修改前或者後 執行
• Trigger_event指定觸發該觸發器的具體 事件
• INSERT當新的一行數據插入表中時觸發，比如通過執行insert,load data,replace語句插入新數據
• UPDATE當表的一行數據被修改時觸發，比如執行update語句時
• DELETE當表的一行數據被刪除時觸發，比如執行delete,replace語句時
• 當執行insert into … on plicate key update語句時，當碰到重復行執行update時，則觸發update下的觸發器
• 從5.7.2版本開始，可以創建具有相同trigger_time和trigger_event的同一個表上的多個觸發器，默認情況下按照創建的時間依次執行，通過 指定FOLLOWS/PRECEDES改變執行順序 ，即FOLLOWS時表示新創建的觸發器後執行，PRECEDES則表示新觸發器先執行
• Trigger_body表示觸發器觸發之後要執行的一個或多個語句，在內部可以引用涉及表的欄位， OLD.col_name表示行數據被修改或刪除之前的欄位數據，NEW.col_name表示行數據被插入或修改之後的欄位數據
• Drop trigger語句用來 刪除一個觸發器
• If exists短語用來避免刪除不存在的觸發器時引發報錯
• 當你執行drop table時，表上的觸發器也被drop掉了
5. mysql存儲過程
很明顯，你這個存儲過程是SQL Server的語法，不是MySQL的，兩者之間語法差異挺大。過程類似於如下：
create procere addUser(
username nvarchar(20),
password nvarchar(20),
OUT isSuccess int)
BEGIN
........
END
6. 如何修改mysql 存儲過程許可權
方法如下：
修改mysql 存儲過程的definer
修改mysql.proc表 的definer欄位
update mysql.proc set definer='root@%' where db='servant_591up'; UPDATE `mysql`.`proc` SET `definer`='root00@%' WHERE `db`='test' AND `name`='jjjj' AND `type`='PROCEDURE'; UPDATE `mysql`.`proc` SET `definer`='wtc_678869@%' WHERE `db`='servant_591up' AND `type`='PROCEDURE';
2.修改sql security
ALTER PROCEDURE www SQL SECURITY INVOKER ； ALTER PROCEDURE www SQL SECURITY DEFINER ；
（1）MySQL存儲過程是通過指定SQL SECURITY子句指定執行存儲過程的實際用戶；
（2）如果SQL SECURITY子句指定為DEFINER，存儲過程將使用存儲過程的DEFINER執行存儲過程，驗證調用存儲過程的用戶是否具有存儲過程的execute許可權和DEFINER用戶是否具有存儲過程引用的相關對象的許可權；
（3）如果SQL SECURITY子句指定為INVOKER，那麼MySQL將使用當前調用存儲過程的用戶執行此過程，並驗證用戶是否具有存儲過程的execute許可權和存儲過程引用的相關對象的許可權；
（4）如果不顯示的指定SQL SECURITY子句，MySQL默認將以DEFINER執行存儲過程。
3.執行存儲過程授權
GRANT EXECUTE ON test.* TO 'wtc'@'%'； GRANT CREATE ROUTINE,ALTER ROUTINE, SELECT,CREATE, INSERT, UPDATE, DELETE, EXECUTE ON test.* TO 'wtc'@'%' IDENTIFIED BY '111111'
CREATE ROUTINE ： 創建存儲過程的許可權
ALTER ROUTINE ： 修改存儲過程的許可權
4.刪除用戶
REVOKE all ON test.* FROM wtc@'%'
DELETE FROM user WHERE User='user_name' and Host='host_name';
FLUSH PRIVILEGES;
好文要頂關注我
7. mysql存儲過程案例講解
CREATE PROCEDURE test_pro1(IN username VARCHAR(20),IN loginPwd VARCHAR(20))
BEGIN
INSERT INTO admin(admin.username,PASSWORD)
VALUES(username,loginpwd);
END $
CREATE PROCEDURE test_pro2(IN id INT,OUT NAME VARCHAR(20),OUT phone VARCHAR(20))
BEGIN
SELECT b.name ,b.phone INTO NAME,phone
FROM beauty b
WHERE b.id = id;
END $
CREATE PROCEDURE test_pro3(IN birth1 DATETIME,IN birth2 DATETIME,OUT result INT)
BEGIN
SELECT DATEDIFF(birth1,birth2) INTO result;
END $
CREATE PROCEDURE test_pro4(IN mydate DATETIME,OUT strDate VARCHAR(50))
BEGIN
SELECT DATE_FORMAT(mydate,'%y年%m月%d日') INTO strDate;
END $
CALL test_pro4(NOW(),@str)
如 傳入 ：小昭
返回： 小昭 AND 張無忌
DROP PROCEDURE test_pro5 $
CREATE PROCEDURE test_pro5(IN beautyName VARCHAR(20),OUT str VARCHAR(50))
BEGIN
SELECT CONCAT(beautyName,' and ',IFNULL(boyName,'null')) INTO str
FROM boys bo
RIGHT JOIN beauty b ON b.boyfriend_id = bo.id
WHERE b.name=beautyName;
END $
CALL test_pro5('柳岩',@str)
DROP PROCEDURE test_pro6
CALL test_pro6(3,5)$
8. mysql怎麼讓一個存儲過程定時執行
mysql怎麼讓一個存儲過程定時執行
查看event是否開啟: show variables like '%sche%';
將事件計劃開啟: set global event_scheler=1;
關閉事件任務: alter event e_test ON COMPLETION PRESERVE DISABLE;
開戶事件任務: alter event e_test ON COMPLETION PRESERVE ENABLE;
簡單實例.
創建表 CREATE TABLE test(endtime DATETIME);
創建存儲過程test
CREATE PROCEDURE test ()
BEGIN
update examinfo SET endtime = now() WHERE id = 14;
END;
創建event e_test
CREATE EVENT if not exists e_test
on schele every 30 second
on completion preserve
do call test();
每隔30秒將執行存儲過程test,將當前時間更新到examinfo表中id=14的記錄的endtime欄位中去
1) 首先來看一個簡單的例子來演示每秒插入一條記錄到數據表
USE test;
CREATE TABLE aaa (timeline TIMESTAMP);
CREATE EVENT e_test_insert
ON SCHEDULE EVERY 1 SECOND
DO INSERT INTO test.aaa VALUES (CURRENT_TIMESTAMP);
等待3秒鍾後，再執行查詢看看：
mysql> SELECT * FROM aaa;
+---------------------+
| timeline |
+---------------------+
| 2007-07-18 20:44:26 |
| 2007-07-18 20:44:27 |
| 2007-07-18 20:44:28 |
+---------------------+
2) 5天後清空test表：
CREATE EVENT e_test
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO TRUNCATE TABLE test.aaa;
3) 2007年7月20日12點整清空test表：
CREATE EVENT e_test
ON SCHEDULE AT TIMESTAMP '2007-07-20 12:00:00'
DO TRUNCATE TABLE test.aaa;
4) 每天定時清空test表：
CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
DO TRUNCATE TABLE test.aaa;
5) 5天後開啟每天定時清空test表：
CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO TRUNCATE TABLE test.aaa;
6) 每天定時清空test表，5天後停止執行：
CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
ENDS CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO TRUNCATE TABLE test.aaa;
7) 5天後開啟每天定時清空test表，一個月後停止執行：
CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY
ENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTH
DO TRUNCATE TABLE test.aaa;
[ON COMPLETION [NOT] PRESERVE]可以設置這個事件是執行一次還是持久執行，默認為NOT PRESERVE。
8) 每天定時清空test表(只執行一次，任務完成後就終止該事件)：
CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
ON COMPLETION NOT PRESERVE
DO TRUNCATE TABLE test.aaa;
[ENABLE | DISABLE]可是設置該事件創建後狀態是否開啟或關閉，默認為ENABLE。
[COMMENT 『comment』]可以給該事件加上注釋。
三、修改事件(ALTER EVENT)
ALTER EVENT event_name
[ON SCHEDULE schele]
[RENAME TO new_event_name]
[ON COMPLETION [NOT] PRESERVE]
[COMMENT 'comment']
[ENABLE | DISABLE]
[DO sql_statement]
1) 臨時關閉事件
ALTER EVENT e_test DISABLE;
2) 開啟事件
ALTER EVENT e_test ENABLE;
3) 將每天清空test表改為5天清空一次：
ALTER EVENT e_test
ON SCHEDULE EVERY 5 DAY;
四、刪除事件(DROP EVENT)
語 法很簡單，如下所示：
DROP EVENT [IF EXISTS] event_name
例如刪除前面創建的e_test事件
DROP EVENT e_test;
當然前提是這個事件存在，否則會產生ERROR 1513 (HY000): Unknown event錯誤，因此最好加上IF EXISTS
DROP EVENT IF EXISTS e_test;
create event test
ON SCHEDULE AT '2007-09-01 12:00:00' + INTERVAL 1 DAY
on completion not preserve
do insert into yyy values('hhh','uuu');
解釋：從2007-09-01開始，每天對表yyy在12：00：00進行一個插入操作。而且只執行一次（on completion not preserve ）
我的計劃任務為：
create event sysplan
ON SCHEDULE AT '2010-05-22 23:00:00' + INTERVAL 1 DAY
on completion not preserve
do truncate table bjproj.ae_tmp;
三、通過設定全局變數event_scheler 的值即可動態的控制事件調度器是否啟用。
查看是否event_scheler開啟mysql> SHOW VARIABLES LIKE '%event%';
設置開啟mysql> SET GLOBAL event_scheler=ON;
四、例子:
每分鍾插入一條日誌:DELIMITER //CREATE EVENT `user_log_event` ON SCHEDULE EVERY 1 MINUTE STARTS '2010-12-27 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN INSERT INTO log SET addtime=NOW();END//
調用存儲過程:DELIMITER //CREATE EVENT `user_log_event` ON SCHEDULE EVERY 1 DAY STARTS '2010-00-00 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN CALL user_log_prov();END//
9. mysql存儲過程中有沒有類似try catch
在存儲過程中使用事務時，如果沒有try…catch語句，那麼當set xact_abort
on時，如果有錯誤發生，在批處理語句結束後，系統會自動回滾所有的sql操作。當set xact_abort
off時，如果有錯誤發生，在批處理語句結束後，系統會執行所有沒有發生錯誤的語句，發生錯誤的語句將不會被執行。
在存儲過程中使用事務時，如果存在try…catch語句塊，那麼當捕獲到錯誤時，需要在catch語句塊中手動進行Rollback操作，否則系統會給客戶端傳遞一條錯誤信息。如果在存儲過程開始處將set
xact_abort
on，那麼當有錯誤發生時，系統會將當前事務置為不可提交狀態，即會將xact_state()置為-1，此時只可以對事務進行Rollback操作，不可進行提交(commit)操作，那麼我們在catch語句塊中就可以根據xact_state()的值來判斷是否有事務處於不可提交狀態，如果有則可以進行rollback操作了。如果在存儲過程開始處將set
xact_abort
off，那麼當有錯誤發生時，系統不會講xact_state()置為-1，那麼我們在catch塊中就不可以根據該函數值來判斷是否需要進行
rollback了，但是我們可以根據@@Trancount全局變數來判斷，如果在catch塊中判斷出@@Trancount數值大於0，代表還有未提交的事務，既然進入catch語句塊了，那麼還存在未提交的事務，該事務應該是需要rollback的，但是這種方法在某些情況下可能判斷的不準確。推薦的方法還是將set
xact_abort on，然後在catch中判斷xact_state()的值來判斷是否需要Rollback操作。
下面我們來看看兩個例子：
一．使用Set xact_abort on
代碼
Create proc myProcere
As
begin
set xact_abort on;
begin try
begin tran
insert into TestStu values('Terry','boy',23);
insert into TestStu values('Mary','girl',21);
commit tran
end try
begin catch
--在此可以使用xact_state()來判斷是否有不可提交的事務,不可提交的事務
--表示在事務內部發生錯誤了。Xact_state()有三種值：-1.事務不可提交；
--1.事務可提交；0.表示沒有事務，此時commit或者rollback會報錯。
if xact_state()=-1
rollback tran;
end catch
end
二.使用Set xact_abort off
代碼
Create proc myProcere
As
begin
set xact_abort off;
begin try
begin tran
insert into TestStu values('Terry','boy',23);
insert into TestStu values('Mary','girl',21);
commit tran
end try
begin catch
--在此不可以使用xact_state來判斷是否有不可提交的事務
--只可以使用@@Trancount來判斷是否有還未提交的事務，未提交的事務未必
--就是不可提交的事務，所以使用@@TranCount>0後就RollBack是不準確的
if @@TranCount>0
rollback tran;
end catch
end
另外，對於@@Trancount需要說明的是，begin tran 語句將 @@Trancount加 1。Rollback tran將
@@Trancount遞減到 0，但 Rollback tran savepoint_name 除外，它不影響 @@Trancount。Commit tran 或 Commit work 將 @@Trancount 遞減 1。
10. mysql存儲過程
存儲過程在mysql中是新面孔，最近一直在用存儲過程，但忘記了一個很重要的問題，如何得到存儲過程的列表。
可能是對mysql太有信心了，一開始我沒考慮過這個問題，直到我建立了55個存儲過程後，才意識到問題的嚴重性，我無法用類似show tables獲得表的列表一樣用show PROCEDURES 來獲得存儲過程的列表。我試了show stored routines 、show storedroutines 、show routines等等，結果都在意料之中，得不到。
接著我想到了information_schema庫，在以前，select * from information_schema.tables;是比show tables還要方便得到表信息的辦法，於是我去了information_schema庫中轉了一圈，仍然一無所獲。而完全沒有去想一直用來保存用戶和地址信息的mysql庫。
就在今天為了建立可以調用存儲過程的用戶時，我訪問了mysql庫，在剛進的時候察覺了庫中多了幾個表，也沒在意，在要離開的時候才注意到裡面有一個表的名字叫proc，似乎是建立存儲過程時使用的PROCEDURES前4位字母，於是select了一下，結果掘到了寶。存儲過程的相關數據好好的保存在裡面。看來以後也同樣不能小看mysql庫了。
想要得到你的存儲過程列表，那麼執行下面的語句吧。
select name,db from mysql.proc;
ps:還得知了存儲過程的名稱長度為64個位元組，多於64則自動刪除。可以使用中文-_-!!!