程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL 系列(三)你不知道的 視圖、觸發器、存儲過程、函數、事務、索引、語句,mysql觸發器

MySQL 系列(三)你不知道的 視圖、觸發器、存儲過程、函數、事務、索引、語句,mysql觸發器

編輯:MySQL綜合教程

MySQL 系列(三)你不知道的 視圖、觸發器、存儲過程、函數、事務、索引、語句,mysql觸發器


MySQL 系列(三)你不知道的 視圖、觸發器、存儲過程、函數、事務、索引、語句

 

第一篇:MySQL 系列(一) 生產標准線上環境安裝配置案例及棘手問題解決

第二篇:MySQL 系列(二) 你不知道的數據庫操作

第三篇:MySQL 系列(三)你不知道的 視圖、觸發器、存儲過程、函數、事務、索引、語句

 

前天有一個五年java開發經驗工程師,問我存儲過程怎麼寫?

My god, 你這個也太 low 了!

 

本章內容:

  • 視圖、增/刪/改/查
  • 觸發器、增/刪/改/查
  • 存儲過程、增/刪/改/查
  • 存儲過程三種傳參,pymysql 怎麼用?
  • 函數、增/刪/改/查/return值
  • 內置函數
  • 事務
  • 索引!

 

一、視圖

視圖是查詢命令結果構成的一個虛擬表(非真實存在),其本質是【根據SQL語句獲取動態的數據集,並為其命名】,用戶使用時只需使用【名稱】即可獲取結果集合,並可以當作表來查詢使用。

1、創建視圖

--格式:CREATE VIEW 視圖名稱 AS  SQL語句
CREATE VIEW v1 AS 
SELET nid, 
    name
FROM
    A
WHERE
    nid > 4

2、刪除視圖

--格式:DROP VIEW 視圖名稱

DROP VIEW v1

3、修改視圖

-- 格式:ALTER VIEW 視圖名稱 AS SQL語句

ALTER VIEW v1 AS
SELET A.nid,
    B. NAME
FROM
    A
LEFT JOIN B ON A.id = B.nid
LEFT JOIN C ON A.id = C.nid
WHERE
    A.id > 2
AND C.nid < 5

4、使用視圖

視圖的使用和普通表一樣,由於視圖是虛擬表,所以無法對其真實表進行創建、更新和刪除操作,僅做查詢用。

select * from v1

 

二、觸發器

對某個表進行【增/刪/改】操作的前後觸發一些操作即為觸發器,如果希望觸發增刪改的行為之前或之後做操作時,可以使用觸發器,觸發器用於自定義用戶對表的行進行【增/刪/改】前後的行為。

1、基本語法

# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 插入後
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 刪除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 刪除後
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新後
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

2、創建觸發器

創建觸發器基本語法是以下code:

但有一點要注意,觸發器內關鍵字NEW表示即將插入的數據行,OLD表示即將刪除的數據行。

# 插入前觸發器

delimiter //
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN

IF NEW. NAME == 'nick' THEN
    INSERT INTO tb2 (NAME)
VALUES
    ('aa')
END
END//
delimiter ;
# 插入後觸發器

delimiter //
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
    IF NEW. num = 666 THEN
        INSERT INTO tb2 (NAME)
        VALUES
            ('666'),
            ('666') ;
    ELSEIF NEW. num = 555 THEN
        INSERT INTO tb2 (NAME)
        VALUES
            ('555'),
            ('555') ;
    END IF;
END//
delimiter ;

3、刪除觸發器

DROP TRIGGER tri_after_insert_tb1;

4、使用觸發器

觸發器是由於對表的增、刪、改操作時被動執行的

insert into tb1(num) values(666)

 

三、存儲過程

存儲過程是一個SQL語句集合,類似函數,需要主動調用。

1、創建存儲過程

# 無參數存儲過程

# 創建存儲過程
delimiter //
create procedure p1()
BEGIN
    select * from t1;
END//
delimiter ;

# 執行存儲過程
call p1()

都說了類似函數,那必須得可以接收參數,且參數有三類:

  • in          僅用於傳入參數用
  • out        僅用於返回值用
  • inout     既可以傳入又可以當作返回值
# 有參數存儲過程

# 創建存儲過程
delimiter \\    # 結尾分號改為\\
create procedure p1(
    in i1 int,
    in i2 int,
    inout i3 int,
    out r1 int
)
BEGIN
    DECLARE temp1 int;    # 創建申明局部變量
    DECLARE temp2 int default 0;
    set temp1 = 1;
    set r1 = i1 + i2 + temp1 + temp2;
    set i3 = i3 + 100;
end\\
delimiter ;

# 執行存儲過程
DECLARE @t1 INT default 3;
DECLARE @t2 INT;
CALL p1 (1, 2 ,@t1, @t2);
SELECT @t1,@t2;

2、刪除存儲過程

drop procedure proc_name;

3、執行存儲過程

執行為函數名加括號;

DECLARE代表創建一個局部變量

# 無參數
call proc_name()

# 有參數,全in
call proc_name(1,2)

# 有參數,有in,out,inout
DECLARE @t1 INT;
DECLARE @t2 INT default 3;
call proc_name(1,2,@t1,@t2)
#!/usr/bin/env python # -*- coding:utf-8 -*- import pymysql conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1') cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 執行存儲過程 cursor.callproc('p1', args=(1, 22, 3, 4)) # 獲取執行完存儲的參數 cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3") result = cursor.fetchall() conn.commit() cursor.close() conn.close() print(result) pymysql執行存儲過程

 

四、函數

該講講真正的函數了,與存儲過程不同的是有return值

1、自定義函數

delimiter \\
create function f1(
    i1 int,
    i2 int)
returns int
BEGIN
    declare num int;
    set num = i1 + i2;
    return(num);
END \\
delimiter ;

2、刪除函數

drop function func_name;

3、執行函數

# 獲取返回值
declare @i VARCHAR(32);
select UPPER('nick') into @i;
SELECT @i;

# 在查詢中使用
select f1(11,nid) ,name from tb2;

4、內置函數

當然MySQL也不例外,有著非常好用的內置函數

CHAR_LENGTH(str)
        返回值為字符串str 的長度,長度的單位為字符。一個多字節字符算作一個單字符。
        對於一個包含五個二字節字符集, LENGTH()返回值為 10, 而CHAR_LENGTH()的返回值為5。

    CONCAT(str1,str2,...)
        字符串拼接
        如有任何一個參數為NULL ,則返回值為 NULL。
    CONCAT_WS(separator,str1,str2,...)
        字符串拼接(自定義連接符)
        CONCAT_WS()不會忽略任何空字符串。 (然而會忽略所有的 NULL)。

    CONV(N,from_base,to_base)
        進制轉換
        例如:
            SELECT CONV('a',16,2); 表示將 a 由16進制轉換為2進制字符串表示

    FORMAT(X,D)
        將數字X 的格式寫為'#,###,###.##',以四捨五入的方式保留小數點後 D 位, 並將結果以字符串的形式返回。若  D 為 0, 則返回結果不帶有小數點,或不含小數部分。
        例如:
            SELECT FORMAT(12332.1,4); 結果為: '12,332.1000'
    INSERT(str,pos,len,newstr)
        在str的指定位置插入字符串
            pos:要替換位置其實位置
            len:替換的長度
            newstr:新字符串
        特別的:
            如果pos超過原字符串長度,則返回原字符串
            如果len超過原字符串長度,則由新字符串完全替換
    INSTR(str,substr)
        返回字符串 str 中子字符串的第一個出現位置。

    LEFT(str,len)
        返回字符串str 從開始的len位置的子序列字符。

    LOWER(str)
        變小寫

    UPPER(str)
        變大寫

    LTRIM(str)
        返回字符串 str ,其引導空格字符被刪除。
    RTRIM(str)
        返回字符串 str ,結尾空格字符被刪去。
    SUBSTRING(str,pos,len)
        獲取字符串子序列

    LOCATE(substr,str,pos)
        獲取子序列索引位置

    REPEAT(str,count)
        返回一個由重復的字符串str 組成的字符串,字符串str的數目等於count 。
        若 count <= 0,則返回一個空字符串。
        若str 或 count 為 NULL,則返回 NULL 。
    REPLACE(str,from_str,to_str)
        返回字符串str 以及所有被字符串to_str替代的字符串from_str 。
    REVERSE(str)
        返回字符串 str ,順序和字符順序相反。
    RIGHT(str,len)
        從字符串str 開始,返回從後邊開始len個字符組成的子序列

    SPACE(N)
        返回一個由N空格組成的字符串。

    SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
        不帶有len 參數的格式從字符串str返回一個子字符串,起始於位置 pos。帶有len參數的格式從字符串str返回一個長度同len字符相同的子字符串,起始於位置 pos。 使用 FROM的格式為標准 SQL 語法。也可能對pos使用一個負值。假若這樣,則子字符串的位置起始於字符串結尾的pos 字符,而不是字符串的開頭位置。在以下格式的函數中可以對pos 使用一個負值。

        mysql> SELECT SUBSTRING('Quadratically',5);
            -> 'ratically'

        mysql> SELECT SUBSTRING('foobarbar' FROM 4);
            -> 'barbar'

        mysql> SELECT SUBSTRING('Quadratically',5,6);
            -> 'ratica'

        mysql> SELECT SUBSTRING('Sakila', -3);
            -> 'ila'

        mysql> SELECT SUBSTRING('Sakila', -5, 3);
            -> 'aki'

        mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
            -> 'ki'

    TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)
        返回字符串 str , 其中所有remstr 前綴和/或後綴都已被刪除。若分類符BOTH、LEADIN或TRAILING中沒有一個是給定的,則假設為BOTH 。 remstr 為可選項,在未指定情況下,可刪除空格。

        mysql> SELECT TRIM('  bar   ');
                -> 'bar'

        mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
                -> 'barxxx'

        mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
                -> 'bar'

        mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
                -> 'barx'

更多函數:猛擊這裡 OR 猛擊這裡

 

五、事務

事務用於將某些操作的多個SQL語句作為原子性操作,只有出現錯誤,即可回滾到原來的狀態,從而保證數據庫數據完整性。

delimiter 為改變結束符號,默認";"

# 支持事務的存儲過程
delimiter \\
create PROCEDURE p1(
    OUT p_return_code tinyint
)
BEGIN 
  DECLARE exit handler for sqlexception 
  BEGIN 
    -- ERROR 
    set p_return_code = 1; 
    rollback; 
  END; 
 
  DECLARE exit handler for sqlwarning 
  BEGIN 
    -- WARNING 
    set p_return_code = 2; 
    rollback; 
  END; 
 
  START TRANSACTION; 
    DELETE from tb1;
    insert into tb2(name)values('seven');
  COMMIT; 
 
  -- SUCCESS 
  set p_return_code = 0; 
 
  END\\
delimiter ;
# 執行存儲過程
DECLARE @i TINYINT;
call p1(@i);
select @i;

 

六、索引

1、索引概述

索引是表的索引目錄,在查找內容之前先查目錄中查找索引位置,從而快速定位查詢數據;

可以理解成新華字典中的索引;

索引會保存在額外的文件中。

2、索引種類

一般的索引種類及功能:

  • 普通索引:僅加速查詢
  • 唯一索引:加速查詢 + 列值唯一(可以有null)
  • 主鍵索引:加速查詢 + 列值唯一 + 表中只有一個(不可以有null)
  • 組合索引:多列值組成一個索引,專門用於組合搜索,其效率大於索引合並
  • 全文索引:對文本的內容進行分詞,進行搜索 

索引合並:使用多個單列索引組合查詢搜索
覆蓋索引:select的數據列只用從索引中就能夠取得,不必讀取數據行,換句話說查詢列要被所建的索引覆蓋

a、普通索引

# 創建表 + 索引
create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    index ix_name (name)
)
# 創建索引
create index index_name on table_name(column_name)
# 刪除索引
drop index_name on table_name;
# 查看索引
show index from table_name;
#注意:對於創建索引時如果是BLOB 和 TEXT 類型,必須指定length。
create index ix_extra on in1(extra(32));

b、唯一索引

# 創建表 + 唯一索引
create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    unique ix_name (name)
)
# 創建唯一索引
create unique index 索引名 on 表名(列名)
# 刪除唯一索引
drop unique index 索引名 on 表名

c、主鍵索引

# 創建表 + 創建主鍵
create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    index ix_name (name)
)

OR

create table in1(
    nid int not null auto_increment,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    primary key(ni1),
    index ix_name (name)
)
# 創建主鍵
alter table 表名 add primary key(列名);
# 刪除主鍵
alter table 表名 drop primary key;
alter table 表名  modify  列名 int, drop primary key;

d、組合索引

組合索引是多個列組合成一個索引來查詢

應用場景:頻繁的同時使用多列來進行查詢,如:where name = 'nick' and age = 18。

# 創建表
create table mess(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    age int not null
)

# 創建組合索引
create index ix_name_age on mess(name,age);

如上創建組合索引之後,查詢一定要注意:

  • name and email  -- >使用索引,name一定要放前面
  • name                 -- >使用索引
  • email                 -- >不使用索引

注意:同時搜索多個條件時,組合索引的性能效率好過於多個單一索引合並。

 3、相關命令

# 查看索引
    show index from  表名
 
# 查看執行時間
    set profiling = 1;  # 開啟profiling
    SQL...              # 執行SQL語句
    show profiles;      # 查看結果

4、如何正確使用索引

# like '%xx',避免%_寫在開頭
    select * from tb1 where name like '%n';

# 使用函數
    select * from tb1 where reverse(name) = 'nick';

# or
    select * from tb1 where nid = 1 or email = '[email protected]';
    注:當or條件中有未建立索引的列才失效,否則會走索引

# 類型不一致
    如果列是字符串類型,傳入條件是必須用引號引起來。
    select * from tb1 where name = 999;

# !=,不等於
    select * from tb1 where name != 'nick'
    注:如果是主鍵,則還是會走索引
        select * from tb1 where nid != 123

# >,大於
    select * from tb1 where name > 'nick'
    注:如果是主鍵或索引是整數類型,則還是會走索引
        select * from tb1 where nid > 123
        select * from tb1 where num > 123

# order by
    select email from tb1 order by name desc;
    當根據索引排序時候,選擇的映射如果不是索引,則不走索引
    注:如果對主鍵排序,則還是走索引:
        select * from tb1 order by nid desc;
 
# 組合索引最左前綴
    如果組合索引為:(name,email),查詢使用:
    name and email       -- 使用索引
    name                 -- 使用索引
    email                -- 不使用索引

5、注意事項

# 避免使用select *
# count(1)或count(列) 代替 count(*)
# 創建表時盡量時 char 代替 varchar
# 表的字段順序固定長度的字段優先
# 組合索引代替多個單列索引(經常使用多個條件查詢時)
# 盡量使用短索引
# 使用連接(JOIN)來代替子查詢(Sub-Queries)
# 連表時注意條件類型需一致
# 索引散列值(重復少)不適合建索引,例:性別不適合

6、執行計劃

explain + 查詢SQL  用於顯示SQL執行信息參數,根據參考信息可以進行SQL優化

mysql> explain select * from suoning;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | suoning | ALL  | NULL          | NULL | NULL    | NULL |    4 |       |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
1 row in set (1.67 sec)
id
        查詢順序標識
            如:mysql> explain select * from (select nid,name from tb1 where nid < 10) as B;
            +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
            | id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
            +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
            |  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL |    9 | NULL        |
            |  2 | DERIVED     | tb1        | range | PRIMARY       | PRIMARY | 8       | NULL |    9 | Using where |
            +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
        特別的:如果使用union連接氣值可能為null


    select_type
        查詢類型
            SIMPLE          簡單查詢
            PRIMARY         最外層查詢
            SUBQUERY        映射為子查詢
            DERIVED         子查詢
            UNION           聯合
            UNION RESULT    使用聯合的結果
            ...
    table
        正在訪問的表名


    type
        查詢時的訪問方式,性能:all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
            ALL             全表掃描,對於數據表從頭到尾找一遍
                            select * from tb1;
                            特別的:如果有limit限制,則找到之後就不在繼續向下掃描
                                   select * from tb1 where email = '[email protected]'
                                   select * from tb1 where email = '[email protected]' limit 1;
                                   雖然上述兩個語句都會進行全表掃描,第二句使用了limit,則找到一個後就不再繼續掃描。

            INDEX           全索引掃描,對索引從頭到尾找一遍
                            select nid from tb1;

            RANGE          對索引列進行范圍查找
                            select *  from tb1 where name < 'alex';
                            PS:
                                between and
                                in
                                >   >=  <   <=  操作
                                注意:!= 和 > 符號


            INDEX_MERGE     合並索引,使用多個單列索引搜索
                            select *  from tb1 where name = 'alex' or nid in (11,22,33);

            REF             根據索引查找一個或多個值
                            select *  from tb1 where name = 'seven';

            EQ_REF          連接時使用primary key 或 unique類型
                            select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid;



            CONST           常量
                            表最多有一個匹配行,因為僅有一行,在這行的列值可被優化器剩余部分認為是常數,const表很快,因為它們只讀取一次。
                            select nid from tb1 where nid = 2 ;

            SYSTEM          系統
                            表僅有一行(=系統表)。這是const聯接類型的一個特例。
                            select * from (select nid from tb1 where nid = 1) as A;
    possible_keys
        可能使用的索引

    key
        真實使用的

    key_len
        MySQL中使用索引字節長度

    rows
        mysql估計為了找到所需的行而要讀取的行數 ------ 只是預估值

    extra
        該列包含MySQL解決查詢的詳細信息
        “Using index”
            此值表示mysql將使用覆蓋索引,以避免訪問表。不要把覆蓋索引和index訪問類型弄混了。
        “Using where”
            這意味著mysql服務器將在存儲引擎檢索行後再進行過濾,許多where條件裡涉及索引中的列,當(並且如果)它讀取索引時,就能被存儲引擎檢驗,因此不是所有帶where子句的查詢都會顯示“Using where”。有時“Using where”的出現就是一個暗示:查詢可受益於不同的索引。
        “Using temporary”
            這意味著mysql在對查詢結果排序時會使用一個臨時表。
        “Using filesort”
            這意味著mysql會對結果使用一個外部索引排序,而不是按索引次序從表裡讀取行。mysql有兩種文件排序算法,這兩種排序方式都可以在內存或者磁盤上完成,explain不會告訴你mysql將使用哪一種文件排序,也不會告訴你排序會在內存裡還是磁盤上完成。
        “Range checked for each record(index map: N)”
            這個意味著沒有好用的索引,新的索引將在聯接的每一行上重新估算,N是顯示在possible_keys列中索引的位圖,並且是冗余的。

詳細

 7、慢日志查詢

日志可以記錄哪些查詢語句慢,沒有走索引,用戶發現問題,從而解決優化

a、配置MySQL自動記錄慢日志

 修改配置文件 my.ini,沒有則添加

slow_query_log = OFF                   # 是否開啟慢日志記錄
long_query_time = 2                    # 時間限制,超過此時間,則記錄
slow_query_log_file = /usr/slow.log    # 日志文件
log_queries_not_using_indexes = OFF    # 為使用索引的搜索是否記錄

# 查看當前配置信息:
   show variables like '%query%'
# 修改當前配置:
   set global 變量名 = 值

b、查看MySQL慢日志

mysqldumpslow -s at -a  /usr/local/var/mysql/MacBook-Pro-3-slow.log

--verbose    版本
--debug      調試
--help       幫助
 
-v           版本
-d           調試模式
-s ORDER     排序方式
             what to sort by (al, at, ar, c, l, r, t), 'at' is default
              al: average lock time
              ar: average rows sent
              at: average query time
               c: count
               l: lock time
               r: rows sent
               t: query time
-r           反轉順序,默認文件倒序拍。reverse the sort order (largest last instead of first)
-t NUM       顯示前N條just show the top n queries
-a           不要將SQL中數字轉換成N,字符串轉換成S。don't abstract all numbers to N and strings to 'S'
-n NUM       abstract numbers with at least n digits within names
-g PATTERN   正則匹配;grep: only consider stmts that include this string
-h HOSTNAME  mysql機器名或者IP;hostname of db server for *-slow.log filename (can be wildcard),
             default is '*', i.e. match all
-i NAME      name of server instance (if using mysql.server startup script)
-l           總時間中不減去鎖定時間;don't subtract lock time from total time

 

七、其它

自然所有的東西都離不開基本的語法語句啦,循環語句與判斷語句

1、條件語句

if then\ elseif then\ else\ end if

# if條件語句

delimiter \\
CREATE PROCEDURE proc_if ()
BEGIN
    
    declare i int default 0;
    if i = 1 THEN
        SELECT 1;
    ELSEIF i = 2 THEN
        SELECT 2;
    ELSE
        SELECT 7;
    END IF;

END\\
delimiter ;

2、循環語句

分為三種循環語句:while循環、repeat循環、loop循環

# while循環

delimiter \\
CREATE PROCEDURE proc_while ()
BEGIN

    DECLARE num INT ;
    SET num = 0 ;
    WHILE num < 10 DO
        SELECT
            num ;
        SET num = num + 1 ;
    END WHILE ;

END\\
delimiter ;
# repeat循環,相當於 do while

delimiter \\
CREATE PROCEDURE proc_repeat ()
BEGIN

    DECLARE i INT ;
    SET i = 0 ;
    repeat
        select i;
        set i = i + 1;
        until i >= 5
    end repeat;

END\\
delimiter ;
# loop

delimiter \\
CREATE PROCEDURE proc_loop ()
BEGIN
    
    declare i int default 0;
    loop_label: loop
        select i;
        set i=i+1;
        if i>=5 then
            leave loop_label;
            end if;
    end loop;

END\\
delimiter ;

3、動態執行SQL語句

# 動態執行SQL

delimiter \\
DROP PROCEDURE IF EXISTS proc_sql \\
CREATE PROCEDURE proc_sql ()
BEGIN
    declare p1 int;
    set p1 = 11;
    set @p1 = p1;

    PREPARE prod FROM 'select * from tb2 where nid > ?';
    EXECUTE prod USING @p1;
    DEALLOCATE prepare prod; 

END\\
delimiter ;
# 方式二

delimiter \\
DROP PROCEDURE IF EXISTS proc_sql \\  # 如果不存在則添加,否則不做操作
CREATE PROCEDURE proc_sql (
    in strSQL char(128),
    in nidd int
)
BEGIN
        set @sqll = strSQL;
    set @p1 = nidd;

    PREPARE prod FROM @sqll;
    EXECUTE prod USING @p1;
    DEALLOCATE prepare prod; 

END\\
delimiter ;

call proc_sql('select * from suoning2 where id > ?','2');

 

     

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved