【数据库】编写存储过程
博客专区 > Zoe_2016 的博客 > 博客详情
【数据库】编写存储过程
Zoe_2016 发表于1年前
【数据库】编写存储过程
  • 发表于 1年前
  • 阅读 26
  • 收藏 2
  • 点赞 0
  • 评论 0

【腾讯云】如何购买服务器最划算?>>>   

ps:课程要求

存储过程(MySQL在5.0以前不支持)

    SQL语句在执行的时候需要要先编译,然后执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。

    一个存储过程是一个可编程的函数,它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数,或者封装特定功能时,存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟。它允许控制数据的访问方式。

存储过程优点

1. 增强了SQL语言的功能和灵活性。可以用流控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算。

2. 允许标准组件是编程。可以在程序中被多次调用,而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改,对应用程序源代码毫无影响。

3. 能实现较快的执行速度。因为存储过程是预编译的。在首次运行一个存储过程时查询,优化器对其进行分析优化,并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化,速度相对要慢一些。

4. 能过减少网络流量。当在客户计算机上调用该存储过程时,网络中传送的只是该调用语句,从而大大增加了网络流量并降低了网络负载。

5. 可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制,能够实现对相应的数据的访问权限的限制,避免了非授权用户对数据的访问,保证了数据的安全。

存储过程创建

1. 格式

CREATE PROCEDURE 过程名 ([过程参数[,...]]) [特性 ...] 过程体

  1. DELIMITER //  
  2.  CREATE PROCEDURE proc1(OUT s int)  
  3.     -> BEGIN 
  4.     -> SELECT COUNT(*) INTO s FROM user;  
  5.     -> END 
  6.     -> //  
  7. DELIMITER ;

 注:

a. DELIMITER是分割符的意思,因为MySQL默认以";"为分隔符,如果我们没有声明分割符,那么编译器会把存储过程当成SQL语句进行处理,则存储过程的编译过程会报错,所以要事先用DELIMITER关键字申明当前段分隔符,这样MySQL才会将";"当做存储过程中的代码,不会执行这些代码,用完了之后要把分隔符还原

b. 根据需要可能会有输入、输出、输入输出参数,这里有一个输出参数s,类型是int型,如果有多个参数用","分割开。

2. 声明分隔符

    上面说写的是在cmd中的操作,如果用mysql的administration管理工具就可以直接创建不再需要声明。

3. 参数

    三种参数类型:IN、OUT、INOUT

    IN 输入参数:表示该参数的值必须在调用存储过程时指定,在存储过程中修改该参数的值不能被返回,为默认值

    OUT 输出参数:该值可在存储过程内部被改变,并可返回

    INOUT 输入输出参数:调用时指定,并且可被改变和返回

4. 变量

(1)变量定义

DECLARE variable_name datatype DEFAULT value

如:DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded'

(2)用户变量

在MySQL客户端使用用户变量

select 变量值 into @用户变量名 -》 新建暂时列

set @用户变量名 = 值

在存储过程中使用用户变量

CREATE PROCEDURE GreetWorld( ) SELECT CONCAT (@greeting,' World');  

SET @greeting='Hello';  

CALL GreetWorld( );  

结果:Hello World

在存储过程间传递全局范围的用户变量

  1. CREATE PROCEDURE p1()   SET @last_procedure='p1';  
  2. mysql> CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_proc);  
  3. mysql> CALL p1( );  
  4. mysql> CALL p2( );  
  5. +-----------------------------------------------+  
  6. | CONCAT('Last procedure was ',@last_proc  |  
  7. +-----------------------------------------------+  
  8. | Last procedure was p1                         |  
  9. +-----------------------------------------------+  

注意:

①用户变量名一般以@开头

②滥用用户变量会导致程序难以理解及管理

(3)注释

①  --  :一般用于单行注释

②// ... // :一般用于多行注释

调用

call 过程名(参数)

查询存储过程

select name from mysql.proc where db=’数据库名’;

或者 select routine_name from information_schema.routines where routine_schema='数据库名';

或者 show procedure status where db='数据库名';

查询存储过程的详细

SHOW CREATE PROCEDURE 数据库.存储过程名;

修改存储过程

ALTER PROCEDURE

删除存储过程

DROP PROCEDURE

存储过程的控制语句

1. 变量的声明定义

2. if-then-else

  1. mysql > DELIMITER //  
  2. mysql > CREATE PROCEDURE proc2(IN parameter int)  
  3.      -> begin 
  4.      -> declare var int;  
  5.      -> set var=parameter+1;  
  6.      -> if var=0 then 
  7.      -> insert into t values(17);  
  8.      -> end if;  
  9.      -> if parameter=0 then 
  10.      -> update t set s1=s1+1;  
  11.      -> else 
  12.      -> update t set s1=s1+2;  
  13.      -> end if;  
  14.      -> end;  
  15.      -> //  
  16. mysql > DELIMITER ;  

3. case(when -then -else)

  1. mysql > DELIMITER //  
  2. mysql > CREATE PROCEDURE proc3 (in parameter int)  
  3.      -> begin 
  4.      -> declare var int;  
  5.      -> set var=parameter+1;  
  6.      -> case var  
  7.      -> when 0 then   
  8.      -> insert into t values(17);  
  9.      -> when 1 then   
  10.      -> insert into t values(18);  
  11.      -> else   
  12.      -> insert into t values(19);  
  13.      -> end case;  
  14.      -> end;  
  15.      -> //  
  16. mysql > DELIMITER ; 

循环语句

4. while -do(操作前检查,不用初始条件)

  1. mysql > DELIMITER //  
  2. mysql > CREATE PROCEDURE proc4()  
  3.      -> begin 
  4.      -> declare var int;  
  5.      -> set var=0;  
  6.      -> while var<6 do  
  7.      -> insert into t values(var);  
  8.      -> set var=var+1;  
  9.      -> end while;  
  10.      -> end;  
  11.      -> //  
  12. mysql > DELIMITER ; 

5. repeat(操作后检查,不用结束条件)-until

  1. mysql > DELIMITER //  
  2. mysql > CREATE PROCEDURE proc5 ()  
  3.      -> begin   
  4.      -> declare v int;  
  5.      -> set v=0;  
  6.      -> repeat  
  7.      -> insert into t values(v);  
  8.      -> set v=v+1;  
  9.      -> until v>=5  
  10.      -> end repeat;  
  11.      -> end;  
  12.      -> //  
  13. mysql > DELIMITER ;  

6. loop(不用初始条件和结束条件,有lables)-leave

  1. mysql > DELIMITER //  
  2. mysql > CREATE PROCEDURE proc6 ()  
  3.      -> begin 
  4.      -> declare v int;  
  5.      -> set v=0;  
  6.      -> LOOP_LABLE:loop  
  7.      -> insert into t values(v);  
  8.      -> set v=v+1;  
  9.      -> if v >=5 then 
  10.      -> leave LOOP_LABLE;  
  11.      -> end if;  
  12.      -> end loop;  
  13.      -> end;  
  14.      -> //  
  15. mysql > DELIMITER ;  

7. lables标号

可以用在begin repeat while 或者loop 语句前,语句标号只能在合法的语句前面使用。可以跳出循环,使运行指令达到复合语句的最后一步。

8. 迭代iterate

通过引用复合语句的标号,来从新开始复合语句

  1. mysql > DELIMITER //  
  2. mysql > CREATE PROCEDURE proc10 ()  
  3.      -> begin 
  4.      -> declare v int;  
  5.      -> set v=0;  
  6.      -> LOOP_LABLE:loop  
  7.      -> if v=3 then   
  8.      -> set v=v+1;  
  9.      -> ITERATE LOOP_LABLE;  
  10.      -> end if;  
  11.      -> insert into t values(v);  
  12.      -> set v=v+1;  
  13.      -> if v>=5 then 
  14.      -> leave LOOP_LABLE;  
  15.      -> end if;  
  16.      -> end loop;  
  17.      -> end;  
  18.      -> //  
  19. mysql > DELIMITER ; 

存储过程的基本函数

(1).字符串类

CHARSET(str) //返回字串字符集
CONCAT (string2 [,... ]) //连接字串
INSTR (string ,substring ) //返回substring首次在string中出现的位置,不存在返回0
LCASE (string2 ) //转换成小写
LEFT (string2 ,length ) //从string2中的左边起取length个字符
LENGTH (string ) //string长度
LOAD_FILE (file_name ) //从文件读取内容
LOCATE (substring , string [,start_position ] ) 同INSTR,但可指定开始位置
LPAD (string2 ,length ,pad ) //重复用pad加在string开头,直到字串长度为length
LTRIM (string2 ) //去除前端空格
REPEAT (string2 ,count ) //重复count次
REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替换search_str
RPAD (string2 ,length ,pad) //在str后用pad补充,直到长度为length
RTRIM (string2 ) //去除后端空格
STRCMP (string1 ,string2 ) //逐字符比较两字串大小,
SUBSTRING (str , position [,length ]) //从str的position开始,取length个字符,

注:mysql字符串默认首下标为1

TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符
UCASE (string2 ) //转换成大写
RIGHT(string2,length) //取string2最后length个字符
SPACE(count) //生成count个空格

(2).数学类

ABS (number2 ) //绝对值
BIN (decimal_number ) //十进制转二进制
CEILING (number2 ) //向上取整
CONV(number2,from_base,to_base) //进制转换
FLOOR (number2 ) //向下取整
FORMAT (number,decimal_places ) //保留小数位数
HEX (DecimalNumber ) //转十六进制
注:HEX()中可传入字符串,则返回其ASC-11码,如HEX('DEF')返回4142143
也可以传入十进制整数,返回其十六进制编码,如HEX(25)返回19
LEAST (number , number2 [,..]) //求最小值
MOD (numerator ,denominator ) //求余
POWER (number ,power ) //求指数
RAND([seed]) //随机数
ROUND (number [,decimals ]) //四舍五入,decimals为小数位数]

注:返回类型并非均为整数,默认变为整形值,但可以设定小数位数,返回浮点型数据

  1. mysql> select round(1.567,2);  
  2. +----------------+  
  3. | round(1.567,2) |  
  4. +----------------+  
  5. |           1.57 |  
  6. +----------------+  
  7. 1 row in set (0.00 sec) 
  8. SIGN (number2 ) //

(3).日期时间类

ADDTIME (date2 ,time_interval ) //将time_interval加到date2
CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //转换时区
CURRENT_DATE ( ) //当前日期
CURRENT_TIME ( ) //当前时间
CURRENT_TIMESTAMP ( ) //当前时间戳
DATE (datetime ) //返回datetime的日期部分
DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或时间
DATE_FORMAT (datetime ,FormatCodes ) //使用formatcodes格式显示datetime
DATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上减去一个时间
DATEDIFF (date1 ,date2 ) //两个日期差
DAY (date ) //返回日期的天
DAYNAME (date ) //英文星期
DAYOFWEEK (date ) //星期(1-7) ,1为星期天
DAYOFYEAR (date ) //一年中的第几天
EXTRACT (interval_name FROM date ) //从date中提取日期的指定部分
MAKEDATE (year ,day ) //给出年及年中的第几天,生成日期串
MAKETIME (hour ,minute ,second ) //生成时间串
MONTHNAME (date ) //英文月份名
NOW ( ) //当前时间
SEC_TO_TIME (seconds ) //秒数转成时间
STR_TO_DATE (string ,format ) //字串转成时间,以format格式显示
TIMEDIFF (datetime1 ,datetime2 ) //两个时间差
TIME_TO_SEC (time ) //时间转秒数]
WEEK (date_time [,start_of_week ]) //第几周
YEAR (datetime ) //年份
DAYOFMONTH(datetime) //月的第几天
HOUR(datetime) //小时
LAST_DAY(date) //date的月的最后日期
MICROSECOND(datetime) //微秒
MONTH(datetime) //月
MINUTE(datetime) //分返回符号,正负或0
SQRT(number2) //开平方

 

 

 

附:

CREATE [FUNCTION|PROCEDURE]

ALTER [FUNCTION|PROCEDURE]

DROP [FUNCTION|PROCEDURE]

SHOW CREATE [FUNCTION|PROCEDURE]

CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

CREATE
    [DEFINER = { user | CURRENT_USER }]
    FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

func_parameter:
    param_name type

type:
    Any valid MySQL data type

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
    Valid SQL routine statement
 

共有 人打赏支持
粉丝 0
博文 28
码字总数 12725
×
Zoe_2016
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: