存储过程 函数

存储过程 函数


存储过程和函数的区别

函数只能返回一个变量的限制。而存储过程可以返回多个。

存储过程来说可以返回参数，而函数只能返回值或者表对象。

存储过程一般是作为一个独立的部分来执行（EXEC执行），而函数可以作为查询语句的一个部分来调用（SELECT调用），由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。

函数是可以嵌入在sql中使用的,可以在select中调用，而存储过程不行。

函数限制比较多，比如不能用临时表，只能用表变量．还有一些函数都不可用等等．而存储过程的限制相对就比较少



表变量和临时表
表变量： 
DECLARE @tb  table(id   int   identity(1,1), name   varchar(100))  

临时表：
SELECT name, address
  INTO #ta   FROM mytable 
  WHERE name like ‘zhang%’

临时表是利用了硬盘(tempdb数据库) ，表名变量是占用内存，因此小数据量当然是内存中的表变量更快。当大数据量时，就不能用表变量了，太耗内存了。大数据量时适合用临时表。

表变量有明确的作用域，在定义表变量的函数、存储过程或批处理结束时，会自动清除表变量。

表变量不必删除，也就不会有命名冲突，临时表特别是全局临时表用的时候必须解决命名冲突。

全局临时表
本地临时表
以一个井号 (#) 开头的那些表名。只有在创建本地临时表的连接上才能看到这些表。
当局部临时表在存储过程内被创建时，存储过程结束也就意味着局部临时表被Drop。
当前会话结束，在会话内创建的所有局部临时表都会被Drop。

全局临时表
以两个井号 (##) 开头的那些表名。在所有连接上都能看到全局临时表。如果在创建全局临时表的连接断开前没有显式地除去这些表，那么只要所有其它任务停止引用它们，
这些表即被除去。当创建全局临时表的连接断开后，新的任务不能再引用它们。当前的语句一执行完，任务与表之间的关联即被除去；因此通常情况下，只要创建全局临时
表的连接断开，全局临时表即被除去。

全局临时表会在创建其的会话结束后被Drop，Drop后其他会话将不能对全局临时表进行引用。

不能对临时表加外键约束。


正确的删除方式：
--正确的临时表删除操作
if object_id('tempdb..#tempTable') is not null Begin
drop table #tempTable
End


sql 判断临时表是否存在，删除临时表重建
IF Object_id('Tempdb..#dl') IS NOT NULL
DROP TABLE #dl --如果有存在就删除临时表
CREATE TABLE #dl (neirong char(20),icount int, dlzonjine int, dlshu int, dlyin int) --重建临时表
INSERT INTO #dl SELECT * FROM tab1 --把物理表的数据插到临时表



自定义函数
创建UDF:
　　CREATE [AGGREGATE] FUNCTION function_name(parameter_name type,[parameter_name type,...])
　　RETURNS {STRING|INTEGER|REAL}
　　runtime_body

简单来说就是:
　　CREATE FUNCTION 函数名称(参数列表)
　　RETURNS 返回值类型
　　函数体
删除UDF:
　　DROP FUNCTION function_name

调用自定义函数语法:
　　SELECT function_name(parameter_value,...)



DELIMITER //   意思是修改默认的结束符";"为"//",以后的SQL语句都要以"//"作为结尾
CREATE FUNCTION IF EXIST deleteById(uid SMALLINT UNSIGNED)
RETURNS VARCHAR(20)
BEGIN
DELETE FROM son WHERE id = uid;
RETURN (SELECT COUNT(id) FROM son);
END//



如果包含多条语句,我们需要把多条语句放到BEGIN...END语句块中
自定义函数中定义局部变量语法:
DECLARE var_name[,varname]...date_type [DEFAULT VALUE];
简单来说就是:
DECLARE 变量1[,变量2,... ]变量类型 [DEFAULT 默认值]
这些变量的作用范围是在BEGIN...END程序中



存储过程
存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。

存储过程就是有业务逻辑和流程的集合， 可以在存储过程中创建表，更新数据， 删除等等。

简单的存储过程
create procedure porcedureName ()
begin
    select name from user;
end; 

-- 调用过程
call porcedureName (); 

删除存储过程
DROP PROCEDURE IF EXISTS porcedureName; -- 没有括号()


使用参数的存储过程
create procedure procedureName(
    out min decimal(8,2),
    out avg decimal(8,2),
    out max decimal(8,2)
)
BEGIN
    select MIN(price) INTO min from order;
    select AVG(price) into avg from order;
    select MAX(price) into max from order;
END; 


调用必须要参数匹配
call procedureName(@min, @avg, @max); 


select @min, @avg, @max;



使用in参数, 输入一个用户id， 返回该用户所有订单的总价格。
create procedure getTotalById (
    in userId int,
    out total decimal(8,2)
)
BEGIN
    select SUM(r.price) from order r
    where r.u_id = userId
    into total;
END; 

调用存储过程
call getTotalById(1, @total);
select @total; 



根据用户id获取该用户的所有订单价格， 并动态的选择是否加税。代码设计如下
create procedure getTotalByUser2(
    in userId int,
    in falg boolean, -- 是否加税标记
    out total decimal(8,2)
)
begin
    DECLARE tmptotal DECIMAL(8,2);
    DECLARE taxrate int DEFAULT 6;-- 默认的加税的利率
    
    select SUM(r.price) from order r
    where r.u_id = userId
    into tmptotal;
    
    if taxable then
        select tmptotal + (tmptotal/1000*taxrate) into tmptotal;
    end if;
    
    select tmptotal into total;
END; 


调用过程
call getTotalByUser2(1, false, @total); -- 不加税
call getTotalByUser2(1, true, @total);  -- 加税
select @total;



IF ELSE 做为流程控制语句使用
IF search_condition THEN
    statement_list 
[ELSEIF search_condition THEN] 
    statement_list ... 
[ELSE
    statement_list] 
END IF


create procedure dbname.proc_getGrade 
(stu_no varchar(20),cour_no varchar(10)) 
BEGIN
declare stu_grade float; 
select grade into stu_grade from grade where student_no=stu_no and course_no=cour_no; 
if stu_grade>=90 then
    select stu_grade,'A'; 
elseif stu_grade<90 and stu_grade>=80 then
    select stu_grade,'B'; 
elseif stu_grade<80 and stu_grade>=70 then
    select stu_grade,'C'; 
elseif stu_grade70 and stu_grade>=60 then
    select stu_grade,'D'; 
else
    select stu_grade,'E'; 
end if; 
END

注意：IF作为一条语句，在END IF后需要加上分号“;”以表示语句结束，其他语句如CASE、LOOP等也是相同的。




SELECT 
  case gender
WHEN 1 THEN 'NAN'
WHEN 0 THEN 'NV'
end  as gender
FROM
t_swidy_day_nutrient



第一种用法:
SELECT name,
CASE WHEN birthday < '1981' THEN 'old'
WHEN birthday > '1988' THEN 'yong'
ELSE 'ok' END YORN
FROM lee

第二种用法：
SELECT NAME, CASE name
WHEN 'sam' THEN 'yong'
WHEN 'lee' THEN 'handsome'
ELSE 'good' END as oldname
FROM lee

第三种：当然了，case when 语句还可以复合
select name, birthday,
case
when birthday > '1983' then 'yong'
when name='lee' then 'handsome'
else 'just so so' end
from lee;


select payment_date, amount,
case
when amount >= 0 AND amount < 40 then 'low'
when amount >=40 AND amount < 80 then 'moderate'
when amount >=80 then 'high'
else 'null' END as dd
FROM penalties


http://www.cnblogs.com/xiangxiaodong/archive/2013/06/04/3116679.html



在MySQL中用函数实现在字符串一后面循环拼接n个字符串二

delimiter $$
drop function if exists fun_addStr;
create function fun_addStr(str1 varchar(100),str2 varchar(10),num int) returns varchar(200)
begin
    declare i int default 1;
    declare result varchar(200) default '';
    set result=str1;
    myloop:loop
        set i=i+1;
        set result=concat(result,str2);
        if i>num
        then
        leave myloop;
        end if;
    end loop myloop;
    return result;
end $$
delimiter;


调用
select fun_addStr('字符串一','字符串二',3);


loop 与 leave,iterate 实现循环 
-- loop 标志位无条件循环，
      leave 类似于break 语句，跳出循环，跳出 begin end，
       iterate 类似于continue ，结束本次循环



DELIMITER $$
DROP PROCEDURE IF EXISTS LOOPLoopProc$$
CREATE PROCEDURE LOOPLoopProc()
       BEGIN
               DECLARE x  INT;
               DECLARE str  VARCHAR(255);
               SET x = 1;
               SET str =  '';
               loop_label:  LOOP
                           IF  x > 10 THEN
                               LEAVE  loop_label;
                           END  IF;
                           SET  x = x + 1;
                           IF  (x mod 2) THEN
                               ITERATE  loop_label;
                           ELSE
                               SET  str = CONCAT(str,x,',');
                           END  IF;

               END LOOP;   
               SELECT str;
       END$$
DELIMITER ;



mysql存储过程 游标 查询结果循环
http://blog.csdn.net/cao478208248/article/details/27642723


如果表不存在就建立这个表，那么可以直接用
create table if not exists tablename

create table if not exists like old_table_name;

select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='dbname' and TABLE_NAME='tablename' ;

drop table 表名 if exists 表名；

//-----------------------------------------
DROP TABLE IF EXISTS `Persons`;
CREATE TABLE `Persons` (
`Id_P`  int(11) NULL DEFAULT NULL ,
`LastName`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`FirstName`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`Address`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`City`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
;

-- ----------------------------
-- Records of Persons
-- ----------------------------
BEGIN;
INSERT INTO `Persons` VALUES ('1', '111', '111', '111', '222'), ('2', '22', '222', '222', '222'), ('3', '33', '33', '33', '33');
COMMIT;