数据库 3

DELIMITER //
CREATE PROCEDURE CountProc2 (IN sid INT, OUT num INT)
BEGIN
DECLARE cnt int default 0;
SELECT COUNT() INTO num FROM fruits WHERE s_id = sid;
set cnt = 100;
select cnt;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE CountProc4 (IN sid INT, OUT num INT)
BEGIN
DECLARE cnt int default 0;
DECLARE tmp int default 0;
SELECT COUNT() INTO num FROM fruits WHERE s_id = sid;
set cnt = 100,tmp =0 ;
select cnt;

set tmp = cnt + 1;
if tmp > 100 then
select tmp;
else
select cnt;
end if;
update fruits set f_name = ‘ok’ where f_id = ‘a’;
select * from fruits;
END //
DELIMITER ;

delimiter //
create function p1(n int)
returns int
begin
declare var int;
set var = 0;
while n>0 do
set var = var + n;
set n = n -1;
end while;
return var;
end //
delimiter ;

select p1(5);
select p1(6);

delimiter //
create function p4(n int)
returns int
begin
declare var int;
set var = 0;
if n>0 then set
var = var + n;
set n = n -1;
end if;
return var;
end //
delimiter ;

select p4(5);
select p4(6);