写了一个mysql 的procedure 怎么测试它?

[csdn1111]

写了一个mysql 的procedure 怎么测试它?

[WWWWA]

call sp
OR
在图形化管理工具中　CALL　SP名

[ACMAIN_CHM]

http://topic.csdn.net/u/20090531/13/60562c9a-74a5-414a-ae84-329d7e38c8fb.html

这个贴子三楼不是已经给你例子了吗？
mysql> CALL curdemo(10101);

mysql> delimiter //
mysql>
mysql> CREATE PROCEDURE curdemo(IN V_GROUP INT)
    -> BEGIN
    ->   DECLARE done INT DEFAULT 0;
    ->   DECLARE a INT;
    ->   DECLARE b CHAR(16);
    ->
    ->   DECLARE cur1 CURSOR FOR SELECT id,name FROM student where classid=V_GROUP;
    ->   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    ->
    ->   OPEN cur1;
    ->
    ->   REPEAT
    ->     FETCH cur1 INTO a, b;
    ->     select a,b;
    ->   UNTIL done END REPEAT;
    ->
    ->   CLOSE cur1;
    -> END
    ->
    ->
    -> //
Query OK, 0 rows affected (0.20 sec)

mysql>
mysql> delimiter ;
mysql>
mysql>  SELECT id,name,classid FROM student ;
+----------+--------+---------+
| id       | name   | classid |
+----------+--------+---------+
| 91010101 | 丁乙甲 |   10101 |
| 91010102 | 索春龙 |   10101 |
| 91010103 | 刘莉莉 |   10101 |
| 91010104 | 隋鑫   |   10101 |
| 91010105 | 长命   |   10101 |
| 91010201 | 河江山 |   10102 |
| 91010202 | 祝立业 |   10102 |
| 91010203 | 姚桂娇 |   10102 |
| 91010204 | 张冬梅 |   10102 |
| 91020101 | 蒋旭   |   10201 |
| 91020102 | 邱介臣 |   10201 |
| 91020103 | 王晓燕 |   10201 |
| 91020104 | 夏丽佳 |   10201 |
| 91020201 | 熊建业 |   10202 |
| 91020202 | 丛俊江 |   10202 |
| 91020203 | 周静燕 |   10202 |
| 91020204 | 黄爽   |   10202 |
| 91020301 | 魏国兴 |   10203 |
| 91020302 | 蒋洪涛 |   10203 |
| 91020303 | 倪春红 |   10203 |
| 91030101 | 张文天 |   10301 |
| 91030102 | 姜洋   |   10301 |
| 91030103 | 赵聪   |   10301 |
| 91030201 | 王飞   |   10302 |
+----------+--------+---------+
24 rows in set (0.00 sec)

mysql>
mysql> CALL curdemo(10101);
+----------+--------+
| a        | b      |
+----------+--------+
| 91010101 | 丁乙甲 |
+----------+--------+
1 row in set (0.00 sec)

+----------+--------+
| a        | b      |
+----------+--------+
| 91010102 | 索春龙 |
+----------+--------+
1 row in set (0.00 sec)

+----------+--------+
| a        | b      |
+----------+--------+
| 91010103 | 刘莉莉 |
+----------+--------+
1 row in set (0.00 sec)

+----------+------+
| a        | b    |
+----------+------+
| 91010104 | 隋鑫 |
+----------+------+
1 row in set (0.00 sec)

+----------+------+
| a        | b    |
+----------+------+
| 91010105 | 长命 |
+----------+------+
1 row in set (0.00 sec)

+----------+------+
| a        | b    |
+----------+------+
| 91010105 | 长命 |
+----------+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

[lixq2000]

call一下，

[studying_]

[Quote=引用 2 楼 ACMAIN_CHM 的回复:]
http://topic.csdn.net/u/20090531/13/60562c9a-74a5-414a-ae84-329d7e38c8fb.html

这个贴子三楼不是已经给你例子了吗？
mysql> CALL curdemo(10101);


SQL codemysql> delimiter //
mysql>
mysql> CREATE PROCEDURE curdemo(IN V_GROUP INT)
-> BEGIN
-> DECLARE done INT DEFAULT 0;
-> DECLARE a INT;
-> DECLARE b CHAR(16);
->
-> DECLARE cur1 CURSOR FOR SELECT id,n…
[/Quote]
正解，用Call

[csdn1111]

PROCEDURE `test`.`p1`(V_checkup_id BIGINT,OUT msg VARCHAR(1000) )
....


在sqlyog里面写:
DECLARE msd VARCHAR(1000)
CALL P_BI_DIABETIC(735,msg )

为什么不正确?

[ACMAIN_CHM]

DECLARE msd VARCHAR(1000)
CALL P_BI_DIABETIC(735,msd )

[vinsonshen]

不知道你说的是指调用还是调试啦？
如果是调用的话，直接
call 存储过程名(参数列表）;

如果是调试的话，那就要讲究技巧了：一般用第3方图形界面工具，从上到下， 从内到外（主要指包含存储过程内调用别的存储过程的情况），分步注释，打印中间变量值等。

[csdn1111]

[Quote=引用 6 楼 ACMAIN_CHM 的回复:]
DECLARE msd VARCHAR(1000)
CALL P_BI_DIABETIC(735,msd )
[/Quote]


Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE msg VARCHAR(1000)
CALL P_BI_DIABETIC(735,msg )' at line 1

Execution Time : 00:00:00:000
Transfer Time : 00:00:00:000
Total Time : 00:00:00:000

[ACMAIN_CHM]

直接贴你的错误提示就容易多了。

直接
set @msd = '';
CALL P_BI_DIABETIC(735,@msd )

就行了。

[csdn1111]

打印中间变量值
用什么方法?

[ACMAIN_CHM]

打印中间变量值

DECLARE a INT;
DECLARE b CHAR(16);

set a=10;
..

select a;