~~存储过程使用

1、创建存储过程

create proceduer sp_name([proc_parameter])

[charactersitice...] routine_body

(1).proc_parameter

参数列表的形式 [IN|OUT|INOUT] param_name type

(2).charactersitice

可以取值为，language sql 由sql语句组成 ； [NOT]DETERMINISTIC 指明存储过程的结果是否确定。相同输入是否相同输出;

CONTAINS SQL | NO SQL | READS SQL DATA | MODIFYIES SQL DATA 

   CONTAINS SQL 子程序包含sql语句 不包含读写（默认）

   NO SQL 不包含sql语句

  READS SQL DATA 包含读写数据语句

 MODIFYIES SQL DATA  包含写语句

 CONTAINS SQL

例子：

create PROCEDURE proc1()
BEGIN 
SELECT * from student;
END

2、创建变量

declare var_name [,varname]  data _type [default value]

例子：declare myvariable int default 100

变量赋值

set var_name = expr[,var_name=expr]...;

例子：declare var1,var2,var3 int;

           set var1 = 10 var = 30;

           set var3 = var1+var2;

语句赋值

select col_name[...] into var_name[...] table_expr;

例子：

create PROCEDURE proc2()
BEGIN
DECLARE s_grade FLOAT;
DECLARE s_gender char(2);
SELECT sex,chengji INTO s_grade,s_gender
FROM student where name='哈哈';
END

3、定义条件和处理程序

定义条件 declarea condition_name CONDITION FOR [condition_type];

condition_type 两种形式：

SQLSTATE[VALUE] SQL ; sqlstate_value | mysql_error_code

例子：

使用sqlstate_value

declare command_not_ allowed CONDITION FOR SQLSTATE'4200';

declare command_not_ allowed CONDITION FOR 1148;

定义处理程序 

declare handler_type handler for condition_value[,...] sp_statement

handler_type: continue| exit | undo  (遇到错误不处理，遇到错误推出，遇到错误撤回之前操作)

condition_value:

（1）SQLSTATE[VALUE]  包含5个字符的字符串错误值

（2）condition_name  表示DECLARE CONDITION 定义的错误条件名称

（3）SQLWARNINT 匹配01开头的SQLSTATE错误代码            

（4）NNOT FOUND 匹配02开头的SQLSTATE错误代码

（5）SQLEXCEPTION匹配所有没有被SQLWARNING或者NOT FOUND捕获的SQLSTATE错误代码

（6）mysql_error_code 匹配数值类型错误代码

例子：

CREATE procedure demo()
BEGIN 
DECLARE continue HANDLER FOR SQLSTATE '23000' SET @x2=1;
SET @x=1;
insert into student VALUES(1,1,1,1);
SET @x=2;
insert into student VALUES(1,1,1,1);
SET @x=3;
end;
CALL demo();
SELECT @x;

返回  @x =3

4、光标的使用

(1)声明：申明  变量、条件 之后，处理程序之前

delare cursor_name CURSOR FOR select_statement

declare cursor_student CURSOR FOR select s_name,s_sex from student;

(2)打开光标 使用光标

open cursor_name

fetch cursor_name into var_name [,varname]...

使用名称为 cursor_name的光标 将查询出的信息存入 s_name和 s_sex中

fetch cursor_student into s_name,s_sex;

(3).关闭光标  close cursor_name

5流程控制

(1)if语句

if val IS NULL 

               then select 'val is null';

               then select 'val is not null';

    end if; 

(2)CASE

case val 

        when 1 then select '  val is 1';

        when 1 then select '  val is 1';

        else select  '  val is 1';

end case;

(3)loop

declare id INT DEFAULT 0;

add_loop:LOOP

set id=id+1;

  I F id>=10 then leave add_loop;

   end if;

end loop add_loop;

(1)。iterate

create PROCEDURE doiterate()
BEGIN
DECLARE p1 int DEFAULT 0;
my_loop:LOOP
   SET  p1=p1+1;
   if p1<10 THEN ITERATE my_loop;
   elseif p1>20 THEN LEAVE my_loop;
   end if;
   SELECT 'p1 is between 10 and 20';
   END LOOP my_loop;
END

(6).repeat  (类似do-while)

declare id int default 0 ;

(7)repeat

 set id=id+1;

 until id>=10;

end repeat;

(8).while

delcare i INT DEFAULT 0； 

while i<10 do

set i=i+1;

end while;

调用存储过程

CREATE PROCEDURE countProcl(IN s_sex varchar(50),OUT num INT)
BEGIN 
SELECT COUNT(*) into num FROM student WHERE sex=s_sex;
END

CALL countProcl("女",@num);
select @num

结果：

num

3

查看存储过程

存储的状态

SHOW PROCEDURE STATUS LIKE 'c%'

存储过程的状态和详细
show CREATE PROCEDURE day06.countProcl

删除存储过程

drop{procedure | function} [if exists] sp_name

drop procedure countproc1;