mysql初識（二）

八、儲存過程

mysql在5.0版本後提供了儲存過程和函式。

語法與oracle不同在於：
1）指定引數是輸入、輸出、輸入輸出的引數（in、out、 in out）放在引數名前面。
2）以begin開始，以end結束語句塊。宣告在begin之後。
3）用delimiter關鍵字來改變語句結束的標誌。（預設是分號，若不修改的話，會將儲存過

程當做單條語句處理，並報錯）
4）可以進行dml操作

如：
mysql> delimiter //
mysql> create procedure proc_name(in cc integer)
    -> begin
    -> declare v varchar(20);
    -> if cc=1 then
    -> set v='mysql';
    -> else
    -> set v='php';
    -> end if;
    -> insert into tb(name) values(v);
    -> end;
    -> //
Query OK, 0 rows affected (0.03 sec)

執行結束後需要將語句結束符改回成分號：
mysql> delimiter ;

儲存過程的呼叫方式為：call sp_name（parameter……）；
函式的呼叫方式為： select function_name(……);

進行建立表操作的例子：
mysql> create procedure p_test()
    -> begin
    ->   create table tt(t_name varchar(2));
    -> end;
    -> //
Query OK, 0 rows affected (0.02 sec)

mysql> call p_test();
-> //
Query OK, 0 rows affected (0.14 sec)

mysql> show tables;
    -> //
+----------------+
| Tables_in_test |
+----------------+
| t              |
| tb             |
| tt             |
+----------------+
3 rows in set (0.00 sec)

建立函式：--注意是returns，不是return
mysql> create function f_name(t_n int)
    -> returns varchar(20)
    -> begin
    -> return(select t_name from tt where t_name=t_n);
    -> end;
    -> //
Query OK, 0 rows affected (0.00 sec)

儲存過程中的變數：
mysql儲存過程中宣告的變數分為區域性變數和全域性變數兩種。
區域性變數作用域只在其宣告所在的程式塊或子塊中。
全域性變數又叫做會話變數，在儲存過程中有效。以@作為起始符號。
定義變數的語句是declare variable_name1，variable_name2…… type default xxxx；
不設定預設值時預設是NULL。

為變數賦值
方法1：set variable_name=xxxx，variable_name2=xxxx……；
方法2：select xxx into variable_name from table_name ……;
兩種方式都可以同時為多個變數賦值。

mysql中游標的使用方法和oracle顯式遊標使用方法類似，但宣告不太一樣。

create procedure p_test2(out var_out varchar)
begin
declare c_a cursor for select t_name from tt;
declare var_a varchar(20) default 'NO_RECORD';
open c_a;
fetch c_a into var_a;
set var_out=var_a;
close c_a;
end;
//
以上語句執行報錯，不太明白。

mysql> create procedure p_test2(out var_out varchar)
    -> begin
    -> declare c_a cursor for select t_name from tt;
    -> declare var_a varchar(20) default 'NO_RECORD';
    -> open c_a;
    -> fetch c_a into var_a;
    -> set var_out=var_a;
    -> close c_a;
    -> end;
    -> //
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ')
begin
declare c_a cursor for select t_name from tt;
declare var_a varchar(20) ' at line 1

mysql的儲存過程中，流程控制語句除if、case、loop、while外，還有iterate和leave。

if語句語法為：
if xxx then xxxx
elseif xxx then xxx
else xxx
end if;

case語法：
case xxx
when xxx then xxx
when xxx then xxx
【else xxx】
end case；

while語法：
while xxxxxxx do
……
end while;

loop語法：
loop
……
end loop；
可以用“leave 迴圈標籤名”退出迴圈

repeat：
先執行一次迴圈體，再判斷condition：
repeat
……
until condition
end repeat：

iterate表示“再次迴圈”。與leave功能相反。

檢視儲存過程和函式的狀態：
show {procedure|function} status [like 'xxxxx'];
檢視儲存過程和函式的定義：
show create {procedure|function} sp_name；

修改儲存過程和函式：
alter {procedure|function} sp_name xxxxx;
刪除儲存過程和函式：
drop {procedure|function}[if exists] sp_name;

捕獲錯誤

檢視：
show triggers；
select * from information_schema.triggers;
刪除：
drop trigger xxxx;

觸發器不能包含事務處理語句或call語句

十、事務
mysql有innodb和bdb兩種型別的資料表支援事務。要建立事務，必須是這兩類表中的一種。
因此首先需要建立或修改，以便得到Innodb或BDB表。
建立：create table xxx(……) type=INNODB/BDB;
修改：alter table xxx type innodb/BDB;

建立事務的一般過程為初始化事務、建立事務、查詢資料是否被錄入和提交事務。
1）初始化事務：
start transaction；
2）建立事務：
就是執行事務中的操作（insert、update、delete等）
3）檢視
用select檢視第二步的操作結果
4）提交事務
commit
5）撤銷事務
rollback

mysql預設是事務自動提交的，也就是我們每輸入一條DML語句後，自動執行commit語句，提

交當前事務。若需要手動提交，可以設定set autocommit=0；
檢視該引數的方法為：
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)

事務分為序列化、可重複讀取、提交後讀取、未提交讀取四個隔離級別。
mysql預設是可重複讀取，即repeatable read。
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

為了資料的一致性，可以將表鎖住：
mysql> lock table t read;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from t;
+------+------+
| id   | code |
+------+------+
|    1 | ACBD |
|    2 | ABCD |
|    3 | BACD |
|    4 | BADC |
+------+------+
4 rows in set (0.00 sec)

mysql> delete from t where code='ACBD';
ERROR 1099 (HY000): Table 't' was locked with a READ lock and can't be updated
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from t where code='ACBD';
Query OK, 1 row affected (0.13 sec)

這是一種mysql的偽事務。設定lock的型別為read後，使用者只能讀資料，不能進行其他操作

；若設定lock型別為write，則當前使用者可以修改資料，而其他會話中的使用者不能進行任何

讀操作。

十一、mysql系統管理

工具：
mysqld_safe 啟動、監控、重啟mysqld伺服器程式的工具
mysqladmin 關閉伺服器或伺服器不正常時檢查執行狀態等
mysqlcheck、isamchk、myisamchk 對資料表進行分析、優化
mysqldump、mysqlhotcopy 備份或複製工具

資料目錄的位置：
mysql> show variables like 'datadir';
+---------------+---------------------------------------------------------------
--------------------+
| Variable_name | Value
|
+---------------+---------------------------------------------------------------
--------------------+
| datadir | D:\Documents and Settings\All Users\Application Data\MySQL\MyS
QL Server 5.5\Data\ |
+---------------+---------------------------------------------------------------
--------------------+
1 row in set (0.00 sec)

也可以用mysqladmin的命令：
mysqladmin variables

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

從名稱上初步理解為：
“information_schema”是所有資料庫物件的屬性資訊
“mysql”資料庫是使用者、許可權、時區、日誌等基本資訊
“performance_schema”儲存效能相關的設定資訊

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26451536/viewspace-765148/，如需轉載，請註明出處，否則將追究法律責任。

mysql初識（二）

相關文章