数据库概论六

¶MYSQL相关

¶储存过程和函数

¶定义

#储存过程
CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
#函数
CREATE 
    [DEFINER= {user|CURRENT_USER}]
    FUNCTION fun_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  

• DEFINER 表示创建的用户,默认当前用户
• IN OUT INOUT 表示储存过程参数作为输入或输出

简单的储存过程

delimiter $$
create procedure test (IN arg1 int,OUT arg2 int)
begin
select count(*) into arg2 from student where id=arg1;
end;$$
delimiter ;
call test(1,@a);

注意在end这种流程控制语句表示结束时要有;在储存过程和函数中;不代表结束,因此要使用delimiter替换结束 符号

函数

#简单函数
create funciotn hello (s char(20)) returns char(50)
return concat('hello' ,s,'!')
#复杂函数
create function test(param int) returns int
begin 
update student set sex='女' where id=param;
select count(*) into @a from student where id>param;
return @a;
end;$$
#使用
select test(1)$$

¶流程控制语句

¶Declear 语句

• 定义
  • 声明本地变量,游标,条件或handler
  • 只允许出现在begin…end语句中而且必须出现在第一行
  • Declare的顺序也有要求，通常是先声明本地变量，再是游标(CURSOR)，然后是条件和HANDLER

create procedure test(IN arg1 int)
begin 
declare xname varchar(20);
declare xsex char(2);
select  name,sex into xname,xsex from student where id=arg1;
select xname,xsex;
end;$$ 

¶流程控制

¶case语句

• 语法

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

#或者:
CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

• case_value与when_value依次做相等对比，如果相等则执行对应的后面的SQL语句，否则接着对比；
• 当search_condition满足true/1的结果时，则执行对应的SQL语句，否则执行else对应的SQL语句；

   
#之前的教师的例子
select 教师号,sum(星期一),... from (select id as '教师号',case when day=1 and up then 1 else 0 as '星期一',..from teacher) A group by 教师号;
#例子
create procedure test2(sid int)
begin 
declare v int default 1;
select gender into v from student where id=sid;
case v 
when v=1 then update student set sex='女' where id=sid;
when v>0 then update student set sex='男' where id>sid;
else 
update students set sex='男' where id=sid;
end case;
end;$$