????? 之前總是在MSSQL上寫存儲(chǔ)過程，沒有在MYSQL上寫過，也基本沒有用過，今天需要用到MYSQL，研究了下，把項(xiàng)目的需要的存儲(chǔ)過程寫了一部分，寫一下工作總結(jié)。這里沒有給出數(shù)據(jù)庫結(jié)構(gòu)，不討論SQL語句的細(xì)節(jié)，主要探討存儲(chǔ)過程語法，適合有基礎(chǔ)的人。
?發(fā)表地址： http://www.cnblogs.com/zxlovenet/p/3783136.html
#查詢文章回復(fù)
--
----------------------------
--
Procedure structure for `sp_select_reply_article`
--
----------------------------
DROP
PROCEDURE
IF
EXISTS
`sp_select_reply_article`;
DELIMITER ;;
CREATE
DEFINER
=
`root`@`localhost`
PROCEDURE
`sp_select_reply_article`(
IN
`ra_id`
int
,
IN
`pagefrom`
int
,
IN
`pagesize`
int
)
BEGIN
#Routine body goes here...
SET
@ra_id
=
ra_id;
SET
@pagefrom
=
pagefrom;
SET
@pagesize
=
pagesize;
SET
@ssra
=
CONCAT(
'
SELECT * FROM gk_article WHERE id = ? LIMIT ?,?
'
);
PREPARE
sqlquery
FROM
@ssra
;
EXECUTE
sqlquery USING
@ra_id
,
@pagefrom
,
@pagesize
;
END
;;
DELIMITER ;
?
#技術(shù)點(diǎn)1：MySql5.1不支持LIMIT參數(shù)（MySql5.5就支持了），如果編寫存儲(chǔ)過程時(shí)使用LIMIT做變量，那是需要用動(dòng)態(tài)SQL來構(gòu)建的，而這樣做性能肯定沒有靜態(tài)SQL好。主要代碼如下：
???????? SET @ssra = CONCAT('SELECT * FROM gk_article WHERE id = ? LIMIT ?,?');
???????? PREPARE sqlquery FROM @ssra;
???????? EXECUTE sqlquery USING @ra_id,@pagefrom,@pagesize;
?
#技術(shù)點(diǎn)2：如果同時(shí)需要返回受影響行數(shù)需要在語句后面添加語句：ROW_COUNT()函數(shù)，兩條語句之間需要“;”分隔。
??發(fā)表地址： http://www.cnblogs.com/zxlovenet/p/3783136.html
#更新數(shù)據(jù)
--
----------------------------
--
Procedure structure for `sp_update_permission`
--
----------------------------
DROP
PROCEDURE
IF
EXISTS
`sp_update_permission`;
DELIMITER ;;
CREATE
DEFINER
=
`root`@`localhost`
PROCEDURE
`sp_update_permission`(
IN
`puser_uid`
varchar
(
20
),
IN
`plevel`
int
,
IN
`ppower`
int
)
BEGIN
#Routine body goes here...
SET
@puser_uid
=
puser_uid;
SET
@plevel
=
plevel;
SET
@ppower
=
ppower;
UPDATE
gk_permission
SET
`
level
`
=
@plevel
,
power
=
@ppower
WHERE
user_uid
=
CONVERT
(
@puser_uid
USING utf8) COLLATE utf8_unicode_ci;
END
;;
DELIMITER ;
?
#技術(shù)點(diǎn)3：MySQL進(jìn)行字符串比較時(shí)發(fā)生錯(cuò)誤（Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='），解決方法：將比較等式一邊進(jìn)行字符串轉(zhuǎn)換，如改為“CONVERT(b.fullCode USING utf8) COLLATE utf8_unicode_ci”，主要代碼如下：
???????? UPDATE gk_permission SET `level` = @plevel, power = @ppower WHERE user_uid = CONVERT(@puser_uid USING utf8) COLLATE utf8_unicode_ci;
?
#插入數(shù)據(jù)
--
----------------------------
--
Procedure structure for `sp_insert_user`
--
----------------------------
DROP
PROCEDURE
IF
EXISTS
`sp_insert_user`;
DELIMITER ;;
CREATE
DEFINER
=
`root`@`localhost`
PROCEDURE
`sp_insert_user`(
IN
`uid`
varchar
(
20
),
IN
`upw`
varchar
(
32
),
IN
`name`
varchar
(
20
),
IN
`sex`
int
,
IN
`phone`
varchar
(
20
),
IN
`u_id`
int
,
IN
`s_id`
int
,
IN
`j_id`
int
)
BEGIN
#Routine body goes here...
SET
@uid
=
uid;
SET
@upw
=
upw;
SET
@uname
=
uname;
SET
@sex
=
sex;
SET
@phone
=
phone;
#由于外鍵約束，所以添加的外鍵字段需要在對應(yīng)外鍵所在表有相應(yīng)數(shù)據(jù)
SET
@u_id
=
u_id;
SET
@s_id
=
s_id;
SET
@j_id
=
j_id;
SET
@verifytime
=
DATE(
'
0000-00-00
'
);
INSERT
INTO
gk_user(uid,upw,uname,sex,phone,u_id,s_id,j_id,verifytime)
VALUES
(
@uid
,
@upw
,
@uname
,
@sex
,
@phone
,
@u_id
,
@s_id
,
@j_id
,
@verifytime
);
#查詢結(jié)果會(huì)自動(dòng)返回受影響行數(shù)
END
;;
DELIMITER ;
??發(fā)表地址： http://www.cnblogs.com/zxlovenet/p/3783136.html
#根據(jù)ID刪除數(shù)據(jù)
--
----------------------------
--
Procedure structure for `sp_delete_exchange_by_id`
--
----------------------------
DROP
PROCEDURE
IF
EXISTS
`sp_delete_exchange_by_id`;
DELIMITER ;;
CREATE
DEFINER
=
`root`@`localhost`
PROCEDURE
`sp_delete_exchange_by_id`(
IN
`eid`
int
)
BEGIN
#Routine body goes here...
SET
@eid
=
eid;
DELETE
FROM
gk_exchange
WHERE
id
=
@eid
;
END
;;
DELIMITER ;
?
#通過賬號查詢用戶或者管理員
--
----------------------------
--
Procedure structure for `sp_select_user_by_uid`
--
----------------------------
DROP
PROCEDURE
IF
EXISTS
`sp_select_user_by_uid`;
DELIMITER ;;
CREATE
DEFINER
=
`root`@`localhost`
PROCEDURE
`sp_select_user_by_uid`(
IN
`uid`
varchar
(
20
),
IN
`getAdmin`
int
)
BEGIN
#Routine body goes here...
SET
@uid
=
uid;
#
SET
@getadmin
=
getAdmin;
#查詢管理員
IF
(getAdmin
=
1
)
THEN
SELECT
us.
*
, un.`name`, se.`name`, jo.`name`, pe.`
level
`, pe.
power
FROM
gk_user
AS
us, gk_unit
AS
un, gk_section
AS
se, gk_jobtitle
AS
jo, gk_permission
AS
pe
WHERE
us.u_id
=
un.id
AND
us.s_id
=
se.id
AND
us.j_id
=
jo.id
AND
us.uid
=
pe.user_uid
AND
us.uid
=
CONVERT
(
@uid
USING utf8) COLLATE utf8_unicode_ci;
END
IF
;
#查詢用戶
IF
(getAdmin
=
0
)
THEN
SELECT
us.
*
, un.`name`, se.`name`, jo.`name`
FROM
gk_user
AS
us, gk_unit
AS
un, gk_section
AS
se, gk_jobtitle
AS
jo
WHERE
us.u_id
=
un.id
AND
us.s_id
=
se.id
AND
us.j_id
=
jo.id
AND
us.uid
=
CONVERT
(
@uid
USING utf8) COLLATE utf8_unicode_ci;
END
IF
;
END
;;
DELIMITER ;
?
#技術(shù)點(diǎn)4：這個(gè)存數(shù)過程需要用到控制語句（if else elseif while loop repeat leave iterate）。
???????? IF (getAdmin = 1) THEN
?????????????????? #語句…
???????? END IF;
??發(fā)表地址： http://www.cnblogs.com/zxlovenet/p/3783136.html
#技術(shù)點(diǎn)5：在傳入?yún)?shù)不匹配的情況下報(bào)錯(cuò)（Column count doesn't match value count at row 1），這個(gè)就是細(xì)心問題了，詳細(xì)檢查參數(shù)吧。
?
#技術(shù)點(diǎn)6：獲取當(dāng)前時(shí)間的函數(shù)：NOW()
?
#技術(shù)點(diǎn)7：“`”這個(gè)符號是反單引號，兩個(gè)反單引號夾起來的會(huì)被當(dāng)做變量，一般是在定義字段時(shí)遇到關(guān)鍵字沖突的時(shí)候會(huì)用到。
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動(dòng)力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長非常感激您！手機(jī)微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
