四、变量
1.用户变量:以”@”开始,形式为”@变量名。” 用户变量跟MySQL客户端是绑定的,设置的变量,只对当前用户使用的客户端生效.
2.全局变量:定义时,以如下两种形式出现,set GLOBAL 变量名 或者 set @@global.变量名。show global variables; 对所有客户端生效。只有super权限才可以设置全局变量。 3.会话变量:只对连接的客户端有效。一旦客户端失去连接,变量失效。show session variables; 4.局部变量:作用范围在begin到end语句块之间。4.1在该语句块里设置的变量declare语句专门用于定义局部变量。declare numeric number(8,2)【MySQL的数据类型,如:int,float, date, varchar(length)】 default 9.95;
4.2变量赋值:SET 变量名 = 表达式值 [,variable_name= expression ...],set numeric=1.2或者SELECT 2.3 into @x;
五、mysql 存储程序
1.基本语法:create procedure 过程名 ([过程参数[,...]])[特性 ...] 过程体;先看基本例子
第一种:
delimiter ;;create procedure proc_on_insert()beginend;;delimiter
第二种:
delimiter //create procedure proc_on_insert()beginend//delimiter ;;
注意:
1).这里需要注意的是delimiter // 和delimiter ;;两句,delimiter是分割符的意思,因为MySQL默认以";"为分隔符,如果我们没有声明分割符,那么编译器会把存储过程当成SQL语句进行处理,则存储过程的编译过程会报错,所以要事先用delimiter关键字申明当前段分隔符,这样MySQL才会将";"当做存储过程中的代码。
2).存储过程根据需要可能会有输入、输出、输入输出参数,这里有一个输出参数s,类型是int型,如果有多个参数用","分割开。 3).过程体的开始与结束使用begin与emd进行标识。2..调用存储过程基本语法:call sp_name()
3.参数:MySQL存储过程的参数用在存储过程的定义,共有三种参数类型,IN,OUT,INOUT,形式如:
create procedure([[in |out |inout ] 参数名 数据类形...])
in输入参数:表示该参数的值必须在调用存储过程时指定,在存储过程中修改该参数的值不能被返回,为默认值 out 输出参数:该值可在存储过程内部被改变,并可返回 inout 输入输出参数:调用时指定,并且可被改变和返回3.1in参数例子:
drop procedure if exists prc_on_in;delimiter ;;create procedure prc_on_in(in num int)begindeclare number int ;set number=num;select number;end;;delimiter ;;set @num=1;call prc_on_in(@num);
3.2out参数创建例子
drop procedure if exists prc_on_out;delimiter ;;create procedure prc_on_out(out out_num int)beginselect out_num;set out_num=78;select out_num;end;;delimiter ;;set @number=6;call prc_on_out(@number);
3.3inout参数创建例子
drop procedure if exists prc_on_inout;delimiter ;;create procedure prc_on_inout(inout p_inout int)beginselect p_inout;set p_inout=100;select p_inout;end;;delimiter ;;set @p_out=90;call prc_on_inout(@p_out);
3.4存储过程中的IF语句(if then elseif then else end if)
drop procedure if exists p_else;create procedure p_else(in id int)begin if (id > 0) then select '> 0' as id; elseif (id = 0) then select '= 0' as id; else select '< 0' as id; end if;end;set @p=-10;call p_else(@p);
3.5存储过程中的case when then
drop procedure if exists p_case;delimiter ;;create procedure p_case( id int ) begin case id when 1 then select 'one' as trans; when 2 then select 'two' as trans; when 3 then select 'three' as trans; else select 'no trans' as trans; end case; end; ;;delimiter ;;set @id=1;call p_case(@id);
3.6存储过程中的while do … end while语句
drop procedure if exists p_while_do; create procedure p_while_do() begin declare i int; set i = 1; while i <= 10 do select concat('index : ', i) ; set i = i + 1; end while; end; call p_while_do();
3.7存储过程中的repeat … until end repeat语句
drop procedure if exists p_repeat;delimiter ;;create procedure p_repeat(in parameter int)BEGIN declare var int; set var = parameter; REPEAT set var = var - 1; set parameter = parameter -2; UNTIL var<0 end REPEAT; select parameter;END;;delimiter ;; set @parameter=1;call p_repeat(@parameter);
这个REPEAT循环的功能和前面WHILE循环一样,区别在于它的执行后检查是否满足循环条件(until i>=5),而WHILE则是执行前检查(while i<5 do)。
不过要注意until i>=5后面不要加分号,如果加分号,就是提示语法错误。3.8存储过程中的loop ··· end loop语句
drop procedure if exists p_loop;delimiter;;create procedure p_loop(in parameter int)BEGIN declare var int; set var = parameter; LOOP_LABLE:loop set var = var - 1; set parameter = parameter -2; if var<0 THEN LEAVE LOOP_LABLE; END IF; end LOOP; select parameter;END;;delimiter;;set @parameter=4;call p_loop(@parameter);
使用LOOP编写同样的循环控制语句要比使用while和repeat编写的要复杂一些:在循环内部加入了IF……END IF语句,在IF语句中又加入了LEAVE语句,LEAVE语句的意思是离开循环,LEAVE的格式是:LEAVE 循环标号。
4.游标的使用 :定义游标 ,打开游标 ,使用游标 ,关闭游标例子
drop table if exists person;CREATE TABLE `person` ( `id` int(11) NOT NULL DEFAULT '0', `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into person(age) value(1);drop procedure if exists prc_test1;delimiter ;;create definer = root@localhost procedure prc_test1()BEGIN declare var int; /**跳出循环标识**/ declare done INT DEFAULT FALSE; /**声明游标**/ declare cur cursor for select age from person; /**循环结束设置跳出标识**/ declare continue handler for not FOUND set done = true; /**打开游标**/ open cur; LOOP_LABLE:loop FETCH cur INTO var; select var; if done THEN LEAVE LOOP_LABLE; END IF; end LOOP; /**关闭游标**/ CLOSE cur;END;;;delimiter ;;call prc_test1();
5.MySQL存储过程的查询
5.1.查看某个数据库下面的存储过程
select name from mysql.proc where db=’数据库名’;
或者 select routine_name frominformation_schema.routines where routine_schema='数据库名'; 或者 show procedure status where db='数据库名';5.2.查看存储过程的详细
show create procedure 数据库.存储过程名;
6、MySQL存储过程的修改
ALTER PROCEDURE:更改用CREATE PROCEDURE 建立的预先指定的存储过程,其不会影响相关存储过程或存储功能。
7.删除存储过程
drop procedure sp_name //注释函数名mysql存储函数实例下载地址:http://pan.baidu.com/s/1gf1Swk7 密码:282i