1) 增强了SQL语言的功能和灵活性。
2) 存储过程被创建后,可以在程序中被多次调用,而不必重新编写该存储过程SQL语句,而且数据库专业人员可以随时对存储过程进行修改,对应用程序源代码毫无影响
3) 存储过程能实现较快的执行速度
4) 存储过程能减少网络流量。
5) 存储过程可被作为一种安全机制来充分利用,系统管理员通过执行某一存储过程的权限进行限制,能够实现对相应数据
的访问权限的限制,避免了非授权用户对数据的访问,保证了数据的安全。
Mysql存储过程
Mysql5.0以前不支持存储过程
1) 格式 create procedure 过程名([过程参数[,...]])
[特性...]过程体
例子:
delimiter // ----------------声明分隔符
create procedure procl(OUT S int)
begin
select count(*) into s from user;
end
//
delimiter;
注:(1) 这里需要注意的是delimiter//和delimiter;两句,delimiter是分隔符的意思,因为MYSQL默认以";"为分隔符,
如果没有声明分隔符,那么编译器会把存储过程当成SQL语句进行处理,则存储过程的编译过程会报错,所以要事
先用delimiter关键字声明当前段分隔符,这样MYSQL才会将";"当作存储过程中的代码,不会执行这谢代码,用完
之后要把分隔符还原。
(2) 存储过程根据需要可能会有输入,输出,输入输出参数,这里有一个输出参数s,类型是int型,如果有多个参数用
","分隔开。
(3) 过程体的开始于结束使用begin与end进行标识。
2) 参数
MYSQL存储过程的参数用在存储过程的定义,共有三种参数类型IN,OUT,INTOUT,形式如:
create procedure([IN|OUT|INOUT] 参数名 数据类型...)
Mysql存储过程参数如果不显式指定"in","out","inout",则默认为"in",习惯上,对于是"in"的参数,我们都不会显式指定
IN 输入参数:表示该参数的值必须在调用存储过程时指定,在存储过程中修改该参数的值不能被返回,为默认值
例子:
创建:
mysql > DELIMITER //
mysql > CREATE PROCEDURE demo_in_parameter(IN p_in int)
-> BEGIN
-> SELECT p_in;
-> SET p_in=2;
-> SELECT p_in;
-> END;
-> //
mysql > DELIMITER ;
执行结果:
mysql > SET @p_in=1;
mysql > CALL demo_in_parameter(@p_in);
+------+
| p_in |
+------+
| 1 |
+------+
+------+
| p_in |
+------+
| 2 |
+------+
mysql> SELECT @p_in;
+-------+
| @p_in |
+-------+
| 1 |
+-------+
以上可以看出,p_in虽然在存储过程中被修改,但并不影响@p_id的值
OUT 输出参数: 该值可在存储过程内部被改编,并可返回
创建:
mysql > DELIMITER //
mysql > CREATE PROCEDURE demo_out_parameter(OUT p_out int)
-> BEGIN
-> SELECT p_out;
-> SET p_out=2;
-> SELECT p_out;
-> END;
-> //
mysql > DELIMITER ;
执行结果:
mysql > SET @p_out=1;
mysql > CALL demo_out_parameter(@p_out);
+-------+
| p_out |
+-------+
| NULL |
+-------+
+-------+
| p_out |
+-------+
| 2 |
+-------+
mysql> SELECT @p_out;
+-------+
| p_out |
+-------+
| 2 |
+-------+
INOUT 输入输出参数: 调用时指定,并且可被改变和返回
创建:
mysql > DELIMITER //
mysql > CREATE PROCEDURE demo_inout_parameter(INOUT p_inout int)
-> BEGIN
-> SELECT p_inout;
-> SET p_inout=2;
-> SELECT p_inout;
-> END;
-> //
mysql > DELIMITER ;
执行结果:
mysql > SET @p_inout=1;
mysql > CALL demo_inout_parameter(@p_inout) ;
+---------+
| p_inout |
+---------+
| 1 |
+---------+
+---------+
| p_inout |
+---------+
| 2 |
+---------+
mysql > SELECT @p_inout;
+----------+
| @p_inout |
+----------+
| 2 |
+----------+
3) 变量:
格式:
declare 声明变量名(可以有多个,使用","分开声明) 声明变量类型 default 默认值;
declare variable_name[,variable_name...] datatype [default value];
其中datatype为Mysql的数据类型,如int,float,date,varchar(length)
Mysql数据类型是没有number类型的,对应的只有int(整型)、float(浮点型);
Mysql存储过程名字后面的"()"是必须的,即使没有一个参数,也需要"()";
Mysql存储过程参数,不能再参数名称前加"@",如“@a int”.
Mysql存储过程的参数不能指定默认值
Mysql存储过程不需要在procedure body前面加"as".而sql_server存储过程必须加"as"关键字
如果Mysql存储过程中包含多条Mysql语句,则需要begin end关键字
BEGIN 和 END 语句用于将多个 Transact-SQL 语句组合为一个逻辑块。在控制流语句必须执行包含两条或多条 Transact-SQL 语句的语句块的任何地方,都可以使用 BEGIN 和 END 语句。
BEGIN 和 END 语句必须成对使用:任何一个均不能单独使用。BEGIN 语句单独出现在一行中,后跟 Transact-SQL 语句块。最后,END 语句单独出现在一行中,指示语句块的结束。
不能在 MySQL 存储过程中使用 "return" 关键字,return 只能出现在函数中
调用 MySQL 存储过程时候,需要在过程名字后面加"()",即使没有一个参数,也需要"()"
例如:
declare l_int int unsinged default 4000000;
declare l_date date default '1999-12-31';
declare l_datetime datetime default '1999-12-31 23:59:59';
declare l_varchar varchar(255) default 'This will not be padded';
变量赋值
set 变量名 = 表达式值[,variable_name = expression...]
用户变量
ⅰ. 在MySQL客户端使用用户变量
mysql > SELECT 'Hello World' into @x;
mysql > SELECT @x;
+-------------+
| @x |
+-------------+
| Hello World |
+-------------+
mysql > SET @y='Goodbye Cruel World';
mysql > SELECT @y;
+---------------------+
| @y |
+---------------------+
| Goodbye Cruel World |
+---------------------+
mysql > SET @z=1+2+3;
mysql > SELECT @z;
+------+
| @z |
+------+
| 6 |
+------+
ⅱ. 在存储过程中使用用户变量
mysql > CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World');
mysql > SET @greeting='Hello';
mysql > CALL GreetWorld( );
+----------------------------+
| CONCAT(@greeting,' World') |
+----------------------------+
| Hello World |
+----------------------------+
注意:
1、用户变量名一般以@开头
2、滥用用户变量会导致程序难以理解及管理
4) 注释
MySQL 存储过程可使用两种风格的注释
双模杠: --
该风格一般用于单行注释
/**/: 一般用于多行注释(将需要注释的多行内容嵌套在/**/中间)
MySQL 存储过程的调用
用call 过程名(参数...)
MySQL 存储过程的查询
想要知道数据库下面有哪些表,一般采用show tables;进行查看。
我们查看某个数据库下面的存储过程方式如下:
1、select name from mysql.proc where db='数据库名';
2、select routine_name from information_schema routines where routines_shema = '数据库名';
3、show procedure status where db='数据库名';
如果想要了解某个存储过程的详细我们可以进行如下操作:
show create procedure 数据库.存储过程名;
MySQL 存储过程的修改
alter procedure 存储过程名
MySQL 存储过程的删除
drop procedure 存储过程名
MySQL 存储过程的控制语句
1) 内部作用域
内部的变量在其作用域内享有更高的优先权,当执行到end.变量时,内部变量消失,此时已经在其作用域外,变量不再
可见了,因为在存储过程外再也不能找到这个申明的变量,但是你可以通过out参数或者将其值指派给会话变量来保存
其值
mysql > DELIMITER //
mysql > CREATE PROCEDURE proc3()
-> begin
-> declare x1 varchar(5) default 'outer';
-> begin
-> declare x1 varchar(5) default 'inner';
-> select x1;
-> end;
-> select x1;
-> end;
-> //
mysql > DELIMITER ;
2) 条件语句
1、if-then-else 语句
DELIMITER $$
CREATE DEFINER=`dbma`@`192.168.7.62` PROCEDURE `proc2`(parameter int)
begin
declare var int;
set var = parameter+1;
if var=0 then
insert into wanyun values(17);
end if;
if parameter =0 then
update wanyun set age = age+1;
else
update wanyun set age = age+2;
end if;
end
2、case 语句
delimiter //
create procedure proc3(parameter int)
begin
declare var int;
set var = parameter+1;
case car
when 0 then
insert into wanyun values(17);
when 1 then
insert into wanyun values(18);
when 2 then
insert into wanyun values(19);
end case;
end;
3) while...end while 循环语句
delimiter //
create procedure proc4(parameter int)
begin
declare var int;
set var = 0;
while var < 6 do
insert into wanyun values(var);
set var = var+1;
end while;
end;
4) loop...end loop 循环
loop循环不需要初始条件,这点和while循环相似;同时和repeat循环一样不需要结束条件
leave 语句的意义是离开循环
delimiter //
create procedure proc5()
begin
declare var int;
LOOP_LABEL:loop
insert into wanyun values(var);
set var = var +1;
if(v>=5) then
leave LOOP_LABEL;
end if;
end loop;
end;
注意: LABELS 标号:
标号可以用在begin repeat while 或者loop 语句前,语句标号只能在合法的语句前面使用。
可以跳出循环,使运行指令达到复合语句的最后一步。
ITERATE迭代
1、iterate:
通过引用复合语句的标号来重新开始复合语句
delimiter //
create procedure proc6()
begin
declare v int;
set v = 0;
LOOP_LABEL:loop
if v = 3 then
set v = v+1;
iterate LOOP_LABEL;
end if;
insert into wanyun values(v);
set v = v+1;
if v>= 5 then
leave LOOP_LABEL;
end if;
end loop;
end;
MySQL 存储过程的基本函数
1) 字符串类
charset(str)//返回字串字符集;
concat(str1[,...])//连接字串;
instr(str,substr)//返回substring首次在string中出现的位置,不存在返回0;
lcase(str)//转换成小写;
left(str,length)//从str中的左边起取length个字符;
load_file(file_name)//从文件读取内容;
locate(substr,string[,start_position])//同instr,但可指定开始位置;
lpad(string,length,pad)//重复用pad加在string开头,直到字串长度为length;
ltrim(string)//去除前端空格;
repeat(string,count)//重复count次;
replace(str,search_str,replace_str)//在str中用replace_str替换search_str;
rpad(string,length,pad)//在str后用pad补充,直到长度为length;
rtrim(string)//去除后端空格;
stramp(string1,string2)//逐字符比较两字串大小;
substring(str,position[,length])//从str的position开始取length个字符;
注:mysql中处理字符串时,默认第一个字符下标为1,即参数position必须大于等于1
select substring('abcd',0,2);
执行结果是:空
select substring('abcd',1,2);
执行结果是:ab
trim([[both|leading|trailing][padding]from]string)//去除指定位置的指定字符;
ucase(string)//转换成大写;
right(string,length)//取string最后length个字符;
space(count)//生成count个空格;
2) 数学类
abs(number)//绝对值;
bin(decimal_number)//十进制转二进制;
ceiling(number)//向上取整;
conv(number)//进制转换;
floor(number)//向下取整;
format(number,decimal_places)//保留小数位数;
hex(DecimalNumber)//转十六进制;
注:hex()中科传入字符串,则返回其ASC-11码,如hex('def')返回4142143,也可以传入十进制整数,返回其十六进制
编码,如hex(25) 返回19;
least(number,number...)//求最小值;
mod(除数,被除数)//求余;
power(number,power)//求指数;
rand([seed])//随机数;
round(number[,decimals])//四舍五入,decimals为小树位数;
注:返回类型并非均为整数;
3) 日期时间类
addtime(date,time_interval)//将time_interval加到date;
convert_tz(datetime,fromTZ,toTZ)//转换时区;
current_date()//当前日期;
current_time()//当前时间;
current_timestamp()//当前时间戳;
date(datetime)//返回datetime的日期部分;
date_add(date,interval d_value d_type)//在date中加上日期或时间;
date_format(datetime,formatCodes)//使用formatcodes格式显示datetime;
date_sub(date,interval d_value d_type)//在date上减去一个时间;
datediff(date1,date1)//两个时间差;
day(date)//返回date在当前date日期的月中是第多少天;例如:select day('2015-02-14');返回的结果是14;
dayname(date)//英文星期;例如:select dayname('2015-02-14');返回的结果是Saturday;
dayofweek(date)//星期(1-7),1为星期天;例如:select dayofweek('2015-02-14');返回结果是7,代表是星期六;
dayofyear(date)//一年中的第几天;
extract(interval_name from date)//从date中提取日期的指定部分;
makedate(hour,minute,second)//生成时间串;
monthname(date)//英文月份名
now()//当前时间;
sec_to_time(seconds)//秒数转成时间;例如:select sec_to_time(1290);返回结果是00:21:30;
str_to_date(string,format)//字串转成时间,以format格式显示;
timediff(datetime1,datetime2)//两个时间差;
time_to_sec(time)//时间转秒数;
week(date_time[,start_of_week])//第几周;
year(datetime)//年份;
dayofmonth(datetime)//当前datetime时间月的第几天;例如:select dayofmonth('2015-01-14');返沪结果是14;
hour(datetime)//小时;例如:select hour('2015-01-14 16:41:00');返沪结果是16;
last_date(date)//date的月的最后日期;
microsecond(datetime)//微秒;
month(datetime)//月;
minute(datetime)//分返回符号,正负或0;
sqrt(number)//开平方; 例如: select sqrt(144);返沪结果是12;
内容转自:http://blog.sina.com.cn/s/blog_52d20fbf0100ofd5.html