create procedure processorders()
begin
declare done boolean default 0;
declare o int;
declare t decimal(8,2);
declare ordernumbers cursor
for
select order_num from orders;
declare continue handler for sqlstate '02000' set done=1;
create table if not exists ordertotals(order_num int, total decimal(8,2));
open ordernumbers;
repeat
fetch ordernumbers into o;
call ordertotal(o, 1, t);
insert into ordertotals(order_num, total) values(o, t);
until done end repeat;
close ordernumbers;
end;
//在MySQL创建存储过程如上面代码,在里面我有用create table if not exists ordertotals(order_num int, total decimal(8,2));创建一个表,语句执行正常,没报错,过后用show tables却看不到ordertotals这张表,是什么问题?还是我上面语句有什么地方是不对的?请高人帮忙指出下。非常感谢!
begin
declare done boolean default 0;
declare o int;
declare t decimal(8,2);
declare ordernumbers cursor
for
select order_num from orders;
declare continue handler for sqlstate '02000' set done=1;
create table if not exists ordertotals(order_num int, total decimal(8,2));
open ordernumbers;
repeat
fetch ordernumbers into o;
call ordertotal(o, 1, t);
insert into ordertotals(order_num, total) values(o, t);
until done end repeat;
close ordernumbers;
end;
//在MySQL创建存储过程如上面代码,在里面我有用create table if not exists ordertotals(order_num int, total decimal(8,2));创建一个表,语句执行正常,没报错,过后用show tables却看不到ordertotals这张表,是什么问题?还是我上面语句有什么地方是不对的?请高人帮忙指出下。非常感谢!
MySQL5.1版本,而且已经切换到当前数据库下面了。
执行这个存储过程之前先USE DBNAME,指定数据库。
select 1;
create table if not exists ordertotals(order_num int, total decimal(8,2));
select 2;
open ordernumbers;看看此句执行没有
Query OK, 0 rows affected (0.00 sec)
其他什么都没有。
create table if not exists ordertotals(order_num int, total decimal(8,2));
insert into 临时表 values(1)
create procedure p_create()
begin
create table if not exists ordertotals(order_num int, total decimal(8,2));
insert into ordertotals values(1,10);
select * from ordertotals;
end;
mysql> call p_create();
+-----------+-------+
| order_num | total |
+-----------+-------+
| 1 | 10.00 |
| 1 | 10.00 |
+-----------+-------+
2 rows in set (0.17 sec)Query OK, 0 rows affected, 1 warning (0.20 sec)mysql> call p_create();
+-----------+-------+
| order_num | total |
+-----------+-------+
| 1 | 10.00 |
| 1 | 10.00 |
| 1 | 10.00 |
+-----------+-------+
3 rows in set (0.06 sec)Query OK, 0 rows affected, 1 warning (0.06 sec)mysql>
create procedure processorders()
begin
create table if not exists testtest(order_num int, total decimal(8,2));
end;我表名随便取的,都不会创建了。。
有兄弟知道的吗?
begin
create table if not exists ordertotals(order_num int, total decimal(8,2));
end;
//你是不是根本没有执行你的存储过程啊。你有 call processorders() 吗?
大大,我现在没环境试,不过,我觉得你说对我的问题所在了,应该调用下call的,给忘记了,呵呵,谢谢楼上几位兄弟的热心解答。