网页设计案例教程杨松答案app优化方案

1、数据存储：把多个sql语句封装成一个方法注意：存储过程中的多游标：
     一个游标中可以存储多个字段，但是每个字段只能存储单个值，如果一个字段想要存储多个值,则只能对这个字段使用多游标。

BEGIN
declare m1 int DEFAULT 0;
declare m2 int DEFAULT 0;
DECLARE cursor1 CURSOR FOR SELECT maney FROM user_account WHERE card_id = id1;
declare cursor2 cursor for select maney from user_account where card_id = id2;
open cursor1;
fetch cursor1 into m1;
close cursor1;
open cursor2;
fetch cursor2 into m2;
close cursor2;
update user_account set maney = m1-tran where card_id = id1;
update user_account set maney = m2+tran where card_id = id2;
END

delimiter //    修改结束符
create procedure proc()
begin  
select * from student;
end
//
call  调用方法变量的定义：
declare 变量名  type  default 默认值；
修改变量的值
set 变量名= 值 或 Drop  procedure proc2;
delimiter //
Create procedure proc2()
Begin 
Declare var1 varchar(10) default null;
Declare var2 int  default 0;
select name ,age into  var1, var2 from student where id =1; Result consisted of more than one row(这种错误出现的原因是没指定id 的值，结果多余变量的个数)
Select var1;
Select var2;
End  //
Delimiter ;delimiter //
drop procedure if exists proc2;
Create procedure proc2()
Begin 
Declare var1 varchar(10) default null;
declare var2   int  default 0;
select name ,age into  var1, var2 from student where id =1; 
Select var1 as  姓名, var2 as 年龄;
End  //
Delimiter ;1、存储过程：
Delimiter //    修改结束符
Drop  procedure if exists pro1;     判断，如果存在pro1 则删除
Create  procedure pro1()     创建 procedure 存储过程
Begin 
End//
Delimiter ;
Call pro1;2、游标：语法：
(声明游标declare cursor_name cursor for select_statement;打开游标（在使用游标之前）open cursor_name;获取游标中的数据 into 变量fetch cursor_name into var_name1,var_name2,...;关闭游标（在使用游标之后）close cursor_name;
)delimiter //drop procedure if exists pro;create procedure pro()begindeclare var varchar(20) default null;declare cursor_name cursor for select name from student where id=1;open cursor_name;fetch cursor_name into var;close cursor_name;select var as 姓名;end //delimiter ;call pro;procedure  程序、declare  声明、
delimiter //
Drop procedure if exists pro//
create procedure pro()
begin 
declare  var1  char(10) default null;
declare  var2  int  default 0;
declare  cursor1 cursor for select name ,age  from student where id=1;    
open cursor1;
fetch cursor1 into var1, var2;
close cursor1;
select var1 as ‘姓名’, var2 as ‘年龄’;
end  //
delimiter ;
call pro;3、存储过程中的if判断语句语法：
(if ..... then ......;else if ...... then ......;else .......;end if;
)delimiter //
drop procedure if exists pro//
Create procedure pro()
begin 
declare var int;
if var is null then select 'var is null' as 结果;
Else select var as 结果;
end if;
Set var=10;
If var is null then select  'var is null' as 结果;
Else select var  as 结果;
End if;
End//
Delimiter ;
Call pro;4、存储过程中的case判断语句语法：
(case  要判断的变量when .... then .... ;when .... then .... ;....;else ....;end case;
)delimiter //drop procedure if exists pro;create procedure pro()begindeclare var int default 0;set var=5;case varwhen 1 then select * from student where id=var;when 2 then select * from student where id=var;when 3 then select * from student where id=var;else select 'no such case' as 结果;end case;end//delimiter ;call pro;5、存储过程中的loop循环语句语法：(loop sql_statement;if ... then leave/iterate;end if;end loop;)delimiter //drop procedure if exists pro//create procedure pro()begindeclare var int default 0;myloop:loop insert student values(null,'Rose',var,'女');if var >=100 then leave myloop;end if;set var=var+1;end loop;end//delimiter ;call pro;注意：在sql语句中没有 “==”，直接用“=”来作为判断等于的符号。6、存储过程中的repeat循环语句语法：(repeat_label:repeat sql_statement;until ... end repeat repeat_label;)delimiter //select * from student//drop procedure if exists pro;create procedure pro()begindeclare var int default 1;my_repeat:repeat insert into student values(null,'Rose',var,'女');set var=var+1;until var>100 end repeat my_repeat;end//delimiter ;call pro;select * from student;7、存储过程中的while循环语法：(while_label：while ...(循环条件)do sql_statement;end while while_label;)delimiter //select * from student//drop procedure if exists pro//create procedure pro()begindeclare var int default 719;my_while:while var<=818do delete from student where id = var;set var=var+1;end while my_while;end//delimiter ;call pro;delimiter //drop procedure if exists pro//create procedure pro()begindeclare var int default 1;my_while:while var<=100do insert into student values(null,'Rose',var,'女');set var=var+1;end while my_while;end//delimiter ;call pro;8、查看存储过程语法：(show {procedure/function} status [like 'pattern'];show create procedure pro_name;)show procedure status;show create procedure pro;9、修改存储过程10、删除存储过程语法：(drop {procedure/function} if exists pro_name;)drop procedure if exists pro;11、存储过程出错处理定义错误情况declare condition_name condition for condition_type;