mysql 語(yǔ)句 怎樣傳遞一個(gè)函數(shù)的參數(shù)

CREATE FUNCTION `fristPinyin`(P_NAME VARCHAR(255)) RETURNS varchar(255) CHARSET utf8

成都創(chuàng)新互聯(lián)公司是一家集網(wǎng)站建設(shè),賓陽(yáng)企業(yè)網(wǎng)站建設(shè),賓陽(yáng)品牌網(wǎng)站建設(shè),網(wǎng)站定制,賓陽(yáng)網(wǎng)站建設(shè)報(bào)價(jià),網(wǎng)絡(luò)營(yíng)銷,網(wǎng)絡(luò)優(yōu)化,賓陽(yáng)網(wǎng)站推廣為一體的創(chuàng)新建站企業(yè)，幫助傳統(tǒng)企業(yè)提升企業(yè)形象加強(qiáng)企業(yè)競(jìng)爭(zhēng)力。可充分滿足這一群體相比中小企業(yè)更為豐富、高端、多元的互聯(lián)網(wǎng)需求。同時(shí)我們時(shí)刻保持專業(yè)、時(shí)尚、前沿，時(shí)刻以成就客戶成長(zhǎng)自我，堅(jiān)持不斷學(xué)習(xí)、思考、沉淀、凈化自己，讓我們?yōu)楦嗟钠髽I(yè)打造出實(shí)用型網(wǎng)站。

BEGIN

DECLARE V_RETURN VARCHAR(255);

SET V_RETURN = ELT(INTERVAL(CONV(HEX(left(CONVERT(P_NAME USING gbk),1)),16,10),

0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,

0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,

0xC8F6,0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1),

'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z');

RETURN V_RETURN;

END

pinyin ：此函數(shù)是將一個(gè)中文字符串對(duì)應(yīng)拼音母的每個(gè)相連 (例如："中國(guó)人"-ZGR)

復(fù)制代碼 代碼如下:

CREATE FUNCTION `pinyin`(P_NAME VARCHAR(255)) RETURNS varchar(255) CHARSET utf8

BEGIN

DECLARE V_COMPARE VARCHAR(255);

DECLARE V_RETURN VARCHAR(255);

DECLARE I INT;

SET I = 1;

SET V_RETURN = '';

while I LENGTH(P_NAME) do

SET V_COMPARE = SUBSTR(P_NAME, I, 1);

IF (V_COMPARE != '') THEN

#SET V_RETURN = CONCAT(V_RETURN, ',', V_COMPARE);

SET V_RETURN = CONCAT(V_RETURN, fristPinyin(V_COMPARE));

#SET V_RETURN = fristPinyin(V_COMPARE);

END IF;

SET I = I + 1;

end while;

IF (ISNULL(V_RETURN) or V_RETURN = '') THEN

SET V_RETURN = P_NAME;

END IF;

RETURN V_RETURN;

END

示例：

復(fù)制代碼 代碼如下:

mysql select p.province, fristPinyin(p.province), pinyin(p.province) from province p;

+------------------+-------------------------+--------------------+

| province | fristPinyin(p.province) | pinyin(p.province) |

+------------------+-------------------------+--------------------+

| 北京市 | B | BJS |

| 天津市 | T | TJS |

| 河北省 | H | HBS |

| 山西省 | S | SXS |

| 內(nèi)蒙古自治區(qū) | N | NMGZZQ |

+------------------+-------------------------+--------------------+

5 rows in set

請(qǐng)教mySQL腳本傳遞參數(shù)的問(wèn)題

set @1=XXX這樣的只能用來(lái)定義MySQL連接中的session參數(shù)，如果你要定義變量為表名的話可以寫(xiě)在存儲(chǔ)過(guò)程中 望采納

mysql的存儲(chǔ)過(guò)程怎么傳入數(shù)組參數(shù)

沒(méi)試過(guò)往里面?zhèn)鲾?shù)組，一般都是多傳幾個(gè)參數(shù)，把幾個(gè)參數(shù)放入SqlParameter[]中，然后cmd.Parameters.AddRange(pars);

創(chuàng)建方法：

SqlParameter[] paras = new SqlParameter[]

{

new SqlParameter("@PageIndex","ccc"),

new SqlParameter("@PageSize","ccc"),

new SqlParameter("@StrSql","ccc")

};

ccc就是你想傳入的值，帶@的就是你在存儲(chǔ)過(guò)程中定義的參數(shù)。

算是一種另類的數(shù)組吧。要不你就把所有條件組合成一個(gè)字符串，中間用特殊符號(hào)隔開(kāi)，到數(shù)據(jù)庫(kù)用split方法再分回來(lái)，不過(guò)這個(gè)方法比較麻煩，不推薦

mysql 自定義函數(shù) 如何傳入中文參數(shù)

CREATE?FUNCTION?`fristPinyin`(P_NAME?VARCHAR(255))?RETURNS?varchar(255)?CHARSET?utf8

BEGIN

DECLARE?V_RETURN?VARCHAR(255);

SET?V_RETURN?=?ELT(INTERVAL(CONV(HEX(left(CONVERT(P_NAME?USING?gbk),1)),16,10),?

0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,?

0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,

0xC8F6,0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1),????

'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z');

RETURN?V_RETURN;

END

pinyin?：此函數(shù)是將一個(gè)中文字符串對(duì)應(yīng)拼音母的每個(gè)相連?(例如："中國(guó)人"-ZGR)

復(fù)制代碼?代碼如下:

CREATE?FUNCTION?`pinyin`(P_NAME?VARCHAR(255))?RETURNS?varchar(255)?CHARSET?utf8

BEGIN

DECLARE?V_COMPARE?VARCHAR(255);

DECLARE?V_RETURN?VARCHAR(255);

DECLARE?I?INT;

SET?I?=?1;

SET?V_RETURN?=?'';

while?I??LENGTH(P_NAME)?do

SET?V_COMPARE?=?SUBSTR(P_NAME,?I,?1);

IF?(V_COMPARE?!=?'')?THEN

#SET?V_RETURN?=?CONCAT(V_RETURN,?',',?V_COMPARE);

SET?V_RETURN?=?CONCAT(V_RETURN,?fristPinyin(V_COMPARE));

#SET?V_RETURN?=?fristPinyin(V_COMPARE);

END?IF;

SET?I?=?I?+?1;

end?while;

IF?(ISNULL(V_RETURN)?or?V_RETURN?=?'')?THEN

SET?V_RETURN?=?P_NAME;

END?IF;

RETURN?V_RETURN;

END

示例：

復(fù)制代碼?代碼如下:

mysql?select?p.province,?fristPinyin(p.province),?pinyin(p.province)?from?province?p;

+------------------+-------------------------+--------------------+

|?province?????????|?fristPinyin(p.province)?|?pinyin(p.province)?|

+------------------+-------------------------+--------------------+

|?北京市???????????|?B???????????????????????|?BJS????????????????|

|?天津市???????????|?T???????????????????????|?TJS????????????????|

|?河北省???????????|?H???????????????????????|?HBS????????????????|

|?山西省???????????|?S???????????????????????|?SXS????????????????|

|?內(nèi)蒙古自治區(qū)?????|?N???????????????????????|?NMGZZQ?????????????|

+------------------+-------------------------+--------------------+

5?rows?in?set