MySQL Tutorial chapter 7th stored Procedures and functions

Chapter 7th stored procedures and function stored procedures and stored functions

The stored procedures for MySQL (stored procedure) and functions (stored function) are collectively referred to as stored routines.

1. Differences between MySQL stored procedures and functions

A function can only return a single value or a Table object through a return statement. The stored procedure does not allow return, but returns multiple values through the out parameter. Functions can be embedded in SQL and can be called in a select, and stored procedures do not work.

There are many function limitations, such as the inability to use temporary tables and only table variables. There are also some functions that are not available, and so on. and stored procedures are relatively less restrictive

In general, the function of the stored procedure implementation is a little more complicated, and the function implementation is more specific.

When stored procedures and functions are executed, SQL Manager will go to the procedure cache to fetch the corresponding query statement, and if there is no corresponding query in the procedure cache, SQL Manager compiles the stored procedures and functions.

The Procedure cache holds the execution plan (execution plan), executes the execution plan in the Procedure cache when it is compiled, and then SQL Server will follow each execution Plan's actual situation to consider whether or not to save the plan in the cache, the criteria for judging is the frequency at which the execution plan might be used, and secondly, the cost of generating the plan, which is the time it takes to compile. The plan saved in the cache will not be compiled the next time it executes.

Stored Procedure Application Instance

1. Create a stored procedure with no parameters

Create a stored procedure to get the highest scores

Create PROCEDURE Getmaxmark ()

Begin

Select Max (Mark) highest score from ' Tscore ';

End

Call a stored procedure

Call Getmaxmark ();

650) this.width=650; "Style=" background-image:none;padding-left:0px;padding-right:0px;height:58px;padding-top:0 px; "title=" clip_image001 "border=" 0 "alt=" clip_image001 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361516322ybof.png "width=" 219 "height=" "/>"

2. Create a stored procedure with input parameters

The following stored procedure is able to enter the student number to isolate the student's information parameter SID represents the number, in represents the input parameter.

CREATE PROCEDURE Getstudentbyid (in SID varchar (15))

BEGIN

SELECT * from ' tstudent ' where studentid=sid;

END

Call this parameter

Call Getstudentbyid (' 00009 ')

650) this.width=650; "Style=" background-image:none;padding-left:0px;padding-right:0px;height:49px;padding-top:0 px; "title=" clip_image003 "border=" 0 "alt=" clip_image003 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361516322cq4t.jpg "width=" 650 "height="/>

3. Creating a stored procedure with input and output parameters

Creates a stored procedure that outputs the highest score for a given course

Create PROCEDURE getmaxmarkbysubject (in subname varchar (+), out Maxmark int)

Begin

Select MAX (Mark) into Maxmark from ' Tscore ' a joins ' Tsubject ' B on a. ' Subjectid ' =b. ' Subjectid '

where B. ' Subjectname ' =subname;

End

Call the stored procedure and place the maximum value of the fetch into the variable @maxscore

Call Getmaxmarkbysubject (' computer network ', @maxScore);

Select @maxScore computer network highest score;

650) this.width=650; "Style=" background-image:none;padding-left:0px;padding-right:0px;height:85px;padding-top:0 px; "title=" clip_image004 "border=" 0 "alt=" clip_image004 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361516323i1hf.png "width=" "height="/>

Call Getmaxmarkbysubject (' data structure ', @maxScore);

Select @maxScore data structure highest score;

650) this.width=650; "Style=" background-image:none;padding-left:0px;padding-right:0px;height:91px;padding-top:0 px; "title=" clip_image005 "border=" 0 "alt=" clip_image005 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361516323y5vx.png "width=" "height="/>

4. Think: Create stored procedure, can delete student record according to assigned number

5. Delete a stored procedure

Drop PROCEDURE ' Getmaxmarkbysubject '

6. View the stored procedures created

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image006 "border=" 0 "alt=" clip_image006 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361516330y7ra.png "height=" 181 "/>

Create a storage function

7. Judging whether the student's performance is excellent

The following functions can output poor results based on the range of input values.

Create FUNCTION getGrad1 (score int)

RETURNS varchar (50)

BEGIN

return if (score>90, ' good grades ', if (score<90 and score>80, ' good grades ', if (score<80 and score>70, ' grade average ', if (score <70 and Score>60, ' passing grades ', ' failing ')));

END

Using a defined function in a query

Select B.sname name, Mark Score, GetGrad1 (mark) grade from ' Tscore ' a join ' tstudent ' B on a. ' StudentID ' =b. ' StudentID '

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image007 "border=" 0 "alt=" clip_image007 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361516330sx1t.png "height=" 154 "/>

8. The function of the first letter of Chinese pinyin

Create a table to get the Pinyin function for Chinese characters first

DROP TABLE IF EXISTS ' Pinyin ';

CREATE TABLE ' Pinyin ' (

' Letter ' char (1) is not NULL,

' Chinese ' char (1) Not NULL,

PRIMARY KEY (' letter ')

) Engine=myisam DEFAULT CHARSET=GBK;

INSERT into ' Pinyin ' VALUES (' A ', ' 驁 '), (' B ', ' book '), (' C ', ' wrong '), (' D ', ' 鵽 '), (' E ', ' 樲 '), (' F ', ' 鰒 '), (' G ', ' hiker '),

(' H ', ' inceѕt '), (' J ', ' 攈 '), (' K ', ' 穒 '), (' L ', ' 鱳 '), (' M ', ' temperature '), (' N ', ' 桛 '), (' O ', ' 漚 '), (' P ', ' exposure '), (' Q ', ' 囕 '), (' R ', ' 鶸 '),

(' S ', ' 蜶 '), (' T ', ' 籜 '), (' W ', ' clamoring '), (' X ', ' 鑂 '), (' Y ', ' Wan Leng '), (' Z ', ' n ');

Create a function to get pinyin for Chinese characters

DROP FUNCTION IF EXISTS ' PINYIN '

CREATE FUNCTION PINYIN (str CHAR (255))

RETURNS Char (255)

BEGIN

DECLARE Hexcode char (4);

DECLARE Pinyin varchar (255);

DECLARE Firstchar char (1);

DECLARE Achar char (1);

DECLARE POS int;

DECLARE Strlength int;

SET Pinyin = ';

SET strlength = Char_length (LTRIM (RTRIM (str)));

SET pos = 1;

SET @str = (CONVERT (str USING GBK));

While POS <= strlength do

SET @aChar = SUBSTRING (@str, pos,1);

SET Hexcode = HEX (@aChar);

IF hexcode >= "8140" and Hexcode <= "FEA0" Then

SELECT Letter to Firstchar

from pinyin

WHERE Chinese >= @aChar

LIMIT 1;

ELSE

SET Firstchar = @aChar;

END IF;

SET Pinyin = CONCAT (Pinyin,firstchar);

SET pos = pos + 1;

END while;

RETURN UPPER (pinyin);

END

Use a function to get the user's name Pinyin first letter

Select sname name, pinyin (sname) pinyin first letter from ' Tstudent '

650) this.width=650; "Style=" background-image:none;padding-left:0px;padding-right:0px;height:300px;padding-top:0 px; "title=" clip_image008 "border=" 0 "alt=" clip_image008 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361516330fi39.png "width=" 650 "height="/>

9. Think: Change the user's mailbox, set the user's email address to the phonetic abbreviation of the name

10. Digital to Chinese characters

The following functions can convert Arabic numerals into Chinese characters used in finance.

Create FUNCTION Tohanzi (N_lowermoney DECIMAL)

RETURNS VARCHAR (120)

BEGIN

Declare v_lowerstr VARCHAR (200);

Declare V_upperpart VARCHAR (200);

Declare v_upperstr VARCHAR (200);

Declare i_i int;

Set v_lowerstr = LTRIM (RTRIM (ROUND (n_lowermoney,2)));

Set i_i = 1;

Set v_upperstr = ';

while (I_i <=char_length (V_LOWERSTR)) do

Set V_upperpart = CONCAT (case substring (v_lowerstr,char_length (V_LOWERSTR)-i_i + +)

When '. ' Then ' Yuan '

When ' 0 ' then ' 0 '

When ' 1 ' then ' one '

When ' 2 ' Then ' Ii. '

When ' 3 ' then ' three '

When the ' 4 ' then ' the restaurant '

When ' 5 ' Then ' ng '

When ' 6 ' Then ' land '

When ' 7 ' Then ' QI '

When ' 8 ' Then ' BA '

When ' 9 ' Then ' JIU '

END,

Case I_i

When 1 Then ' minutes '

When 2 Then ' Corner '

When 3 Then '

When 4 Then '

When 5 Then ' pick '

When 6 then ' Bai '

When 7 Then ' thousand '

When 8 then ' million '

When 9 then ' pick '

When ten then ' Bai '

When one and then ' thousand '

When the then ' billion '

When the "Pick up"

When the then ' Bai '

When the then ' thousand '

When the then ' million '

ELSE '

END);

Set V_upperstr =concat (V_upperpart, V_UPPERSTR);

Set i_i = i_i + 1;

End while;

Set v_upperstr = REPLACE (V_upperstr, ' 0 pickup ', ' 0 ');

Set v_upperstr = REPLACE (V_upperstr, ' 0 bai ', ' 0 ');

Set v_upperstr = REPLACE (V_upperstr, ' 0 thousand ', ' 0 ');

Set v_upperstr = REPLACE (v_upperstr, ' 000 ', ' 0 ');

Set v_upperstr = REPLACE (v_upperstr, ' 00 ', ' 0 ');

Set v_upperstr = REPLACE (v_upperstr, ' Certer ', ' whole ');

Set v_upperstr = REPLACE (V_upperstr, ' 0 points ', ' whole ');

Set v_upperstr = REPLACE (v_upperstr, ' 0 angle ', ' 0 ');

Set v_upperstr = REPLACE (V_upperstr, ' 0 yuan ', ' billion Yuan ');

Set v_upperstr = REPLACE (V_upperstr, ' billion zero million ', ' billion Yuan ');

Set v_upperstr = REPLACE (V_upperstr, ' 0 ', ' billion ');

Set v_upperstr = REPLACE (v_upperstr, ' $0 ', ' million ');

Set v_upperstr = REPLACE (v_upperstr, ' zero Yuan ', ' million ');

Set v_upperstr = REPLACE (V_upperstr, ' 0 ', ' billion ');

Set v_upperstr = REPLACE (V_upperstr, ' 0 ', ' million ');

Set v_upperstr = REPLACE (V_upperstr, ' 0 yuan ', ' Yuan ');

Set v_upperstr = REPLACE (v_upperstr, ' 00 ', ' 0 ');

if (' meta ' = substring (v_upperstr,1,1)) Then

Set v_upperstr = substring (v_upperstr,2, (Char_length (V_UPPERSTR)-1));

End If;

if (' 0 ' = substring (v_upperstr,1,1)) Then

Set v_upperstr = substring (v_upperstr,2, (Char_length (V_UPPERSTR)-1));

End If;

if (' angle ' = substring (v_upperstr,1,1)) Then

Set v_upperstr = substring (v_upperstr,2, (Char_length (V_UPPERSTR)-1));

End If;

if (' Minute ' = substring (v_upperstr,1,1)) Then

Set v_upperstr = substring (v_upperstr,2, (Char_length (V_UPPERSTR)-1));

End If;

if (' whole ' = substring (v_upperstr,1,1)) Then

Set v_upperstr = ' 0 yuan whole ';

End If;

return v_upperstr;

END

Select Tohanzi (20321)

650) this.width=650; "Style=" background-image:none;padding-left:0px;padding-right:0px;height:87px;padding-top:0 px; "title=" clip_image009 "border=" 0 "alt=" clip_image009 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361516331f12d.png "width=" "height="/>

11. Randomly generated name function

The function, which uses three strings, holds the user's name and randomly arranges the combination of adult names from the name using random functions.

Create function Create_name ()

RETURNS varchar (3)

Begin

DECLARE LN VARCHAR (300);

DECLARE MN VARCHAR (200);

DECLARE FN VARCHAR (200);

DECLARE Ln_n INT;

DECLARE Mn_n INT;

DECLARE Fn_n INT;

SET LN=‘李王张刘陈杨黄赵周吴徐孙朱马胡郭林何高梁郑罗宋谢唐韩曹许邓萧冯曾程蔡彭潘袁于董余苏叶吕魏蒋田杜丁沈姜范江傅钟卢汪戴崔任陆廖姚方金邱夏谭韦贾邹石熊孟秦阎薛侯雷白龙段郝孔邵史毛常万顾赖武康贺严尹钱施牛洪龚‘;

SET mn= ' Wei Gangyong spring Chrysanthemum Yi June Feng Strong army ping Bao Dong Wenhui Liming zhi na yan Yun Rong garden xu Qingcong pure yu Yue Zhao ice cool wan tea feather Hinningxin fluttering ying fu new Liyun soft bamboo mist ning Xiao Huan Feng Yun phenanthrene Cold oia Keishuyi Shinlanghai you Fossengrong National sheng Learning Xiang Liang Zheng Stonehenge solid of the LAN Yuan Fushunshin kwantip Tao Chang into Kang Starlight Tian da Ann Rock in Mao into the forest and Puma first Jingzhen Zhuang will think group Hao Qing fei Naging cui ya zhi shes autumn sansa jin Dai Green Qian Ting Jiao Wan Xian Jing Ying Yao Yi Chan Yan ceruse instrument Dan Rong Eyebrow June Qin Rui Wei Ching Meng su Wei just courageous June Feng strong army ping Bao Dong Wenhui Liming zhi Yuchenliang Renponin you Fossengrong national sheng Learn xiang just hair Vuniuli fei Fushunshin kwantip Tao Chang into Kang Star Day da-an rock in Mao into the forest and Puma first to the earthquake vibration strong will think group Hao Heart State Lechaugong pine good thick qing lei min Yu Jiang Shuhao Liang Stonehenge solid round John Lamberhon words if Ming Peng present Witchkelen Xiang Xu Peng ze Chen Shi to build home to the tree inflammation when Thai Shengxiung June Crown policy Teng Nan Banyan wind and Air Hong ';

SET fn= ' Wei just courageous June Yun Lianjin ring snow rong Love sister Xia Xiang month Ying Yuan Yan Rui van Jia Jia Jong Qin zhen Guidi Ye Shi just hair Wulilin round John Lamberhon say if the Ming Peng present Witchkelen Xiang Xu Chen Shi build home-induced tree inflammation Dehe Jiang Shuhao lu ya Crystal Yue Hwa Qiao Mei Jie Xin litchi think Heart State Cheng Lechaugong pine good thick qing lei people friends Jade Ping red Ah Ling fragrance yan Choi Lan Fengjie Meshujuan the Thai Shengxiong Total June crown Policy Teng nan Banyan Wind Hong Feng strong army ping Bao Dong Wenhui Liming zhi Yuchenliang Renponin Fossengrong National sheng Learn xiang just hair Vuniuli fly bin Fushunshin kwantip Tao Chang into Kang star Day da ' an Yan Jin Lin Jian and Puma first to the earthquake vibration strong will think group Hao Heart State Lechaugong pine good thick qing lei min Yu Jiang Shuhao Liang Zheng Stonehenge fixed round John Lamberhon said if the Ming friend bin Present Witchkelen Xiang Xu Peng ze Chen Shi to build home to the tree inflammation when the Thai Shengxiung June Crown policy Teng Nan Banyan wind and Air Hong ';

SET ln_n=char_length (LN);

SET mn_n=char_length (MN);

SET fn_n=char_length (FN);

Return Concat (SUBSTRING (Ln,ceil (rand () *ln_n), 1), SUBSTRING (Mn,ceil (rand () *mn_n), 1), SUBSTRING (Fn,ceil (rand () *fn_n ), 1));

End

Call function to generate name

Select Create_name () Random name Union

Select Create_name () Random name Union

Select Create_name () Random name Union

Select Create_name () Random name Union

Select Create_name () Random name Union

Select Create_name () Random name Union

Select Create_name () Random name Union

Select Create_name () Random name Union

Select Create_name () Random name Union

Select Create_name () Random name Union

Select Create_name () Random name Union

Select Create_name () Random name Union

Select Create_name () random name

View Results

650) this.width=650; "Style=" background-image:none;padding-left:0px;padding-right:0px;height:300px;padding-top:0 px; "title=" clip_image010 "border=" 0 "alt=" clip_image010 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361516331hjbf.png "width=" 177 "height="/>

12. View the Created function

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image011 "border=" 0 "alt=" clip_image011 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361516465hwh2.png "height=" 212 "/>

13. Delete a stored function

Drop FUNCTION ' Tohanzi '

Using variables, judgments, and loops in stored procedures and functions

variables, judgments, and loops in MySQL can only be used in stored procedures and stored functions.

14. Using loops, variables in stored procedures

Write a stored procedure that can insert a specified number of student records into the Tstudent table. Number randomly generated, the name randomly generated, gender random, class randomly generated. This uses the random function, and the function created above to generate the name. Birthdays are randomly generated, and in 1980-1989, the user's mailbox is composed of the initials of the user.

If you already have a stored procedure, you must first delete the

drop procedure Addstudent

Stored procedures that are created

CREATE PROCEDURE addstudent (in num int)

Begin

declare i int;

Set I=1;

Delete from tstudent;

While Num>=i do

Insert Tstudent VALUES (

Lpad (CONVERT (I,char (5)), 5, ' 0 '),

Create_name (),

if (Ceil (rand () *10)%2=0, ' Male ', ' female '),

Rpad (Convert ceil (rand () *1000000000000000000), char (18)), 18, ' 0 '),

Concat (' 198 ', CONVERT (Ceil (rand () *10), char (1)), '-', Lpad (CONVERT (Ceil (rand () *12), char (2)), 2, ' 0 '), '-', Lpad ( CONVERT (Ceil (rand () *30), char (2)), 2, ' 0 ')),

Concat (PINYIN (sname), ' @hotmail. com '),

Case Ceil (rand ()) while 1 then ' web and Web development ' when 2 Then ' JAVA ' ELSE ' NET ' END,

Now ());

Set i=i+1;

End while;

SELECT * from Tstudent;

End

Call a stored procedure

Call Addstudent (100)

650) this.width=650; "Style=" background-image:none;padding-left:0px;padding-right:0px;height:353px;padding-top:0 px; "title=" clip_image013 "border=" 0 "alt=" clip_image013 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 13615164669pku.jpg "width=" 650 "height=" 353 "/>

15. Create a stored procedure that uses while to insert student scores

After inserting 100 students, execute the following command.

Create stored procedures to insert fractions for students. The stored procedure uses two loops, with fractions between 50-100 and a random number.

drop procedure Fillsore

Create a stored procedure

CREATE PROCEDURE Fillsore ()

Begin

DECLARE St_num INT;

DECLARE Sb_num INT;

DECLARE i1 INT default 1;

DECLARE i2 INT default 1;

Delete from Tscore;

Select COUNT (*) into st_num from Tstudent;

Select COUNT (*) into sb_num from Tsubject;

While St_num>=i1 do

Set i2=1;

While Sb_num>=i2 do

Insert Tscore values

(Lpad (CONVERT (I1,char (5)), 5, ' 0 '), Lpad (CONVERT (I2,char (4)), 4, ' 0 '), Ceil (50+rand () *50));

Set i2=i2+1;

END while;

Set i1=i1+1;

END while;

End

Call a stored procedure

Call Fillsore ()

Inquire

SELECT * FROM Tscore

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image014 "border=" 0 "alt=" clip_image014 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361516466w2sv.png "height=" 348 "/>

16. Create a function that uses if

If function supports multilayer nesting

Create FUNCTION getGrad2 (score int)

RETURNS varchar (50)

BEGIN

DECLARE grad varchar (50);

If Score>90 Then

Set grad= ' excellent performance ';

else if Score>80 then

Set grad= ' good grades ';

else if Score>70 then

Set grad= ' performance in general ';

else set grad= ' just passed ';

End If;

End If;

End If;

return grad;

END

Querying a database using functions

Select B.sname name, Mark Score, GetGrad2 (mark) Grade

From ' Tscore ' a joins ' tstudent ' B on a. ' StudentID ' =b. ' StudentID ' limit 5

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image015 "border=" 0 "alt=" clip_image015 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361516466t08k.png "height=" 134 "/>

17. Create a function that uses case

The function is evaluated according to the student's score.

Create FUNCTION getGrad3 (score int)

RETURNS varchar (50)

BEGIN

DECLARE grad varchar (50);

declare mark int;

Set Mark=ceil (SCORE/10);

Case Mark

When 9 then set grad= ' excellent performance ';

When 8 then set grad= ' good results ';

When 7 then set grad= ' results in general ';

else set grad= ' just passed ';

End case;

return grad;

END

Test function

Select B.sname name, Mark Score, GETGRAD3 (mark) Grade

From ' Tscore ' a joins ' tstudent ' B on a. ' StudentID ' =b. ' StudentID ' limit 5

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image016 "border=" 0 "alt=" clip_image016 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361516467rktc.png "height=" 131 "/>

View statements for stored procedures and stored functions

Run a command to view the Create Fillsore stored procedure statement

18. Use show create to view the contents of the stored procedure

Show CREATE PROCEDURE Fillsore

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image017 "border=" 0 "alt=" clip_image017 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361516467a818.png "height=" 335 "/>

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image018 "border=" 0 "alt=" clip_image018 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361516468pnej.png "height=" 369 "/>

19. Use administrative tools to generate statements to view the creation of stored procedures

650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image019 "border=" 0 "alt=" clip_image019 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 13615164680owv.png "height=" 516 "/>

650) this.width=650; "style=" background-image:none;padding-left:0px;padding-right:0px;padding-top:0px; "title=" clip_image021 "border=" 0 "alt=" clip_image021 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361516469gzhh.jpg "height=" 408 "/>

Homework:

20. Statistics on the number of boys and girls

21. Statistics on the number of classes

22. Find the student with the surname

23. Find the student with an even number at the end of the * *.

24. Check the date of birth between 1985-01-00 and 1988-01-00 students.

25. Statistics on the average division of "computer networks" in each class

26. Find out the "computer network" failed male students

This article is from "Ghost" blog, please make sure to keep this source http://caizi.blog.51cto.com/5234706/1543139