1、无参数的查询
CREATE PROCEDURE GetAllProducts()
BEGIN
SELECT * FROM `authors`;
END;
执行 call GetAllProducts;
2、带传入参数
CREATE PROCEDURE GetAllProductsParam2(in id int ,in aname VARCHAR(30))
BEGIN
insert into `authors` VALUES (id,aname);
END;
执行:call GetAllProductsParam2(3,'555');
3、带输出参数
create PROCEDURE pr_multi2(
out c int,
a int,
b int
)
begin
if a is null THEN
set a=10;
end if;
if b IS null THEN
set b=20;
end if;
set c=(select count(*) from `authors`);
END
执行:call pr_multi2(@name,5,3);
select @name
4、根据传入参数修改字段
create PROCEDURE pr_multi3(
b int
)
begin
update `authors` set name=b;
END
call pr_multi3(3);
执行:call pr_multi3(3);
5、根据声明的变量更新值
CREATE PROCEDURE myProc()
BEGIN
DECLARE i INT DEFAULT 1;
SET i=5;
UPDATE employee
SET first_name=CONCAT("name",i)
WHERE id=i;
END;
执行:call myProc;
6、SQL 利用存储过程插入100万条数据
CREATE TABLE `procedure_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`price` double DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
)
创建存储过程
CREATE PROCEDURE `prcedure_insert_test`()
BEGIN
SET @i=4;
WHILE @i<=1000000 DO
INSERT INTO PROCEDURE_TEST(name,price,create_time) VALUES(CONCAT("user",@i),6.88,NOW());
SET @i=@i+1;
END WHILE;
END;
执行存储过程 call prcedure_insert_test();
查询 select * from PROCEDURE_TEST;
查询mysql数据库正在执行的sql语句:select * from information_schema.`PROCESSLIST` where info is not null;
7、批量更新
从数据库查出来数据放入游标中,然后遍历游标,更新数据
CREATE PROCEDURE update_district_level()
BEGIN
DECLARE row_id INT;#定义变量ID
DECLARE row_district_id INT;#定义变量地区ID
DECLARE row_level INT;#定义变量地区等级
DECLARE done INT;
-- 定义游标
DECLARE rs_cursor CURSOR FOR
SELECT main.id,main.district_id,main.level FROM jsjh_goods_district main ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;#当没有数据的时候设置为1
OPEN rs_cursor; #打开游标
cursor_loop:LOOP
FETCH rs_cursor INTO row_id,row_district_id,row_level; -- 取数据
IF done=1 THEN #当done=1的时候关闭游标
leave cursor_loop;
END IF;
-- 更新表
UPDATE jsjh_goods_district SET district_id=row_district_id*10 WHERE id=row_id;
END LOOP cursor_loop;
CLOSE rs_cursor;
END;
CALL update_district_level();
8、存储过程和函数的区别
函数只会返回一个值,不允许返回一个结果集。函数强调返回值,所以函数不允许返回多个值的情况,即使是查询语句。
-- 不行的代码:Not allowed to return a result set from a function
create function myf()returns int
begin
select * from student;
return 100;
end;