mysql创建存储过程测试实验讲解

mysql创建存储过程测试实验讲解1.创建\t"/database/201806/_blank"数据库test3：createdatabsetest3；2.创建表t2：usetest3;createtablet2(idint,namechar(50),dtimetimestamp);[plain]viewplaincopy\t"/database/201806/_blank"mysql>select*fromt2;

Emptyset(0.00sec)

3.创建procedure来循环插入数据到新建的t2表中[plain]viewplaincopymysql>DELIMITER$$

mysql>CREATEPROCEDUREt2insert(INcountINT)

->BEGIN

->DECLAREiINTDEFAULT0;

->WHILEi<count

->DO

->insertintot2values(i,concat('yoo',i),CURRENT_TIMESTAMP());

->SETi=i+1;

->ENDWHILE;

->END$$

QueryOK,0rowsaffected(0.06sec)

988棋牌4.查看存储过程的信息：DELIMITER;showprocedurestatuslike'%insert%'\G;showcreateproceduret2insert\G;select*frominformation_schema.routineswhereroutine_name='t2insert'\G;[plain]viewplaincopymysql>showprocedurestatuslike'%insert%'\G;

***************************1.row***************************

Db:test3

Name:t2insert

Type:PROCEDURE

Definer:root@localhost

Modified:2018-06-1022:13:09

Created:2018-06-1022:13:09

Security_type:DEFINER

Comment:

character_set_client:gbk

collation_connection:gbk_chinese_ci

DatabaseCollation:utf8_general_ci

1rowinset(0.00sec)

5.调用存储过程[plain]viewplaincopymysql>callt2insert(10);

QueryOK,1rowaffected(0.65sec)

mysql>select*fromt2;

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

|id

|name|dtime

|

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

|

0|yoo0|2018-06-1022:18:25|

|

1|yoo1|2018-06-1022:18:26|

|

2|yoo2|2018-06-1022:18:26|

|

3|yoo3|2018-06-1022:18:26|

|

4|yoo4|2018-06-1022:18:26|

|

5|yoo5|2018-06-1022:18:26|

|

6|yoo6|2018-06-1022:18:26|

|

7|yoo7|2018-06-1022:18:26|

|

8|yoo8|2018-06-1022:18:26|

|

9|yoo9|2018-06-1022:18:26|

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

10rowsinset(0.00sec)