4. 存储过程 · sql编程

、存储过程框架

    变量：局部、全局、内置

     declare 仅用在begin...end中, 声明的是局部变量, 作用范围也仅在此begin...end中

    declare 前不能有任何其他非declare语句

     declare v1, v2, v3 int default 0;            -- 无default则初始值为null

    declare a, b int;

    set a = 10, b = 20

     全局变量: 以@开头如@a

    set @a = 100;                     select @a := ‘xyz‘;

     begin

declare a int;

declare b int default 200;

set a = 100;

select a, b, @a; -- 可以获取全局变量@a

     end

    @@ 内置系统变量

    select @@version;

   select @@sql_mode;

    例子1：

    drop procedure if exists sp1;

    delimiter //                                                          ---定义//为结束符

    create procedure sp1(stuID int,stuName varchar(16),c int ,d varchar(64) charset utf8)       ---默认为In

    begin

 insert into stu select stuID,stuName,c,d;

    end //

    delimiter   ;                                                           ---恢复;为结束符     注意空格

    call sp1(20,‘kitty‘,3,‘长沙‘);                             ---传入值

存储过程   stored  procedure    

优点：较快的执行速度，减少网络流量

sp是数据库内的一种对象, 与表一样, call ds.sp1();

一段sql语句的集合, 无返回值      可以接收参数, 可以没有参数, 可以传出参数        用户自定义的

    例 2：

  drop procedure if exists sp1;

  delimiter //

  create procedure sp1()

  begin

declare a int default 0;

select count(1) into a from information_schema.columns where table_schema = ‘ds‘ && table_name= ‘stu‘ && column_name = ‘stuAddr‘;

if a = 1 then

select ‘stuAddr haved‘;

else

alter table stu add stuAddr varchar(32);

end if;

  end//

  delimiter   ;

  call sp1();

  例 3：

   delimiter //

  create procedure sp1(out a int)                                   ---传出值

   begin

select count(distinct deptID) into a from stu;

  end //

  delimiter ;

  call sp1(@x);               -- 用@x来接收这个传出来的值a

  select @x;

 例 4：

  create procedure sp1(a int, b int, out c int)

  begin

select a + b into c;

  end //

  delimiter ;

  call sp1(10, 20, @x);                  ---传入、传出

   select @x;

例 5：

  sp1(inout a int)                           ---传入又传出

  begin

set a = a + 10;

  end

  set @x = 10;

  call sp1(@x);                                ---传入

  select @x;                                     ---传出更新

查看与删除 sp 

show procedure status\G

show procedure status like ‘sp1‘\G

show create procedure sp1\G

 

select * from information_schema.routines\G

select * from mysql.proc\G

drop procedure if exists sp1;

 

2、sp 应用

  判断（if）

   if a = 100 then – 注意判断相等就是=

   select ‘a’;

   select ’b’;

   elseif  a = 200 then

     …

   else

     …

   end if;

   if a >= 0 && a <= 10 then -- between and也可

   set @a = 1; if @a即可

   判断（case）

   case operator

   简单格式:

   set @a = 10;

   select case @a when 10 then ‘abc’ when 20 then ‘def’ else ‘xyz’ end;

   select  case age when 20 then salary*1. 2 when 30 then salary*1.3 else salary*1.4 end  from ds.emp;

  

   搜索格式:

   select case

   when age >= 20 && age < 30 then

   salary * 1.1

   when age >= 30 then

   salary * 1.2

   else

   salary

   end from stu;

 case statement   搜索格式:

   when @a >= 80 && @a <= 100 then

  ‘abc’

   when @a >= 60 && @a < 80 then

‘  def’

   else

  ‘xyz’

   end;

 

while 循环

delimiter //

create procedure sp1()

begin

declare i int default 0;

while i <= 5

do

select i;

set i = i + 1;

end while;

end //

delimiter  ;

循环写入数据

insert into t1 values (i, concat(‘tom‘, i));

循环中的leave  与  iterate

作用同break与continue

declare a int default 1;

   

aix:while a <= 10

do

if a % 2 = 0 then

leave aix;

#set a = a + 1;

#iterate aix; 跳过当前循环, 即不执行后面的语句, 进入下一轮循环

end if;

 

select a;        

set a = a + 1;

          

end while;

 

repeat … until循环(相当于do…while)

declare i int default 1;

repeat

select i;

set i = i + 1;

until i > 5 end repeat;

 

   function     

 可传入参数,  也可无参数传入

 没有参数传出   必须有返回值

 函数体中不可有create table、drop table、select结果

 格式：

drop function if exists fun1;

delimiter //

create function fun1(a  varchar(32)   [charset utf8])       ----定义函数，设置参数，字符集

returns varchar(32)   [charset utf8]                                            ----返回值格式

begin

declare x varchar(32) charset utf8;

set x = concat(‘hello ‘, a, ‘ !‘);

return x;                                                                                     ----返回值

#return concat(‘hello ‘, a, ‘ !‘);  也可, begin...end都可不要       #多条语句时就用begin...end括起来

end//

delimiter   ;

select fun1(‘tom‘);                                                                           ----调用函数

 例子：

drop function if exists fun1;

delimiter //

create function fun1( a   varchar(32) charset utf8,   b int)

returns varchar(32) charset utf8

begin

if isnull(a) || char_length(a) = 0 then

return ‘error‘;

elseif char_length(a) <= b then

return a;

else

return concat (left(a,b),‘...‘);

end if;

end//

delimiter ;

select   fun1(‘abc‘,3)

 cursor      

drop procedure if exists sp1;

delimiter //

create procedure sp1()

begin

declare a int;

declare b varchar(16);

declare cur cursor for select stuID,stuName from stu order by stuID;      ----声明游标

open cur;                                                                                                                                 ----打开游标

fetch cur into a,b;                                                                                                                ----获取游标

fetch cur into a,b;                                                      ---a,b不能是全局变量，可用循环获取数据

select a,b;

close cur;                                                                                                                                ----关闭游标

end//

delimiter ;

 

event 事件

类似linux下的crontab

show variables like ‘%event%‘;      或者   select @@event_scheduler;

默认是关闭的，set global event_scheduler = on 或= 1

格式：

drop event if exists ev1;

create event ev1

on schedule every 1 minute

do

insert into t1(f1) values (now());                      ----只有一条语句，可以不用begin…..end

----立即启动, 只做一次

on schedule at now()                                                    -- show events不见了

----30秒后启动, 只做1次:

on schedule at now() + interval 30 second       -- show events不见了

alter event ev1 enable|disable;

 

view (视图)

虚拟表, 数据库中的对象，主要用来查看数据

数据放在表中, 视图中没有数据

基表的数据变化会自动在视图中体现出来

 

权限控制, 只让用户看到某些列某些行，将多表查询的结果放在视图中

删除视图不影响基表及其数据，不会向视图增删改数据, 视图只用来查数据

 例子：

drop view if exists v1;

create view v1

as

select empName, empAddr from emp where ifnull(salary, 0) > 3000;

 

create view v2

as

select bookName, pressName, authorName    from book b, press p, author a

where b.pressID = p.pressID && b.authorID = a.authorID;

  查看视图：

desc v1;

show tables;                                                        ----这里视图可以看出一张表

show create view v1\G

select * from information_schema.views;

来自为知笔记(Wiz)

4. 存储过程 · sql编程

标签：