用游标，和WHILE可以遍历您的查询中的每一条记录并将要求的字段传给变量进行相应的处理
 ==================
 DECLARE 
 @A1 VARCHAR(10),
 @A2 VARCHAR(10),
 @A3 INT
 DECLARE CURSOR YOUCURNAME FOR SELECT A1,A2,A3 FROM YOUTABLENAME
 OPEN YOUCURNAME
 fetch next from youcurname into @a1,@a2,@a3
 while @@fetch_status<>-1
 begin
 update … set …-a3 where …
 ……您要执行的操作写在这里
 fetch next from youcurname into @a1,@a2,@a3
 end
 close youcurname
 deallocate youcurname
 
—————————————
 
在应用程序开发的时候，我们经常可能会遇到下面的应用，我们会通过查询数据表的记录集，循环每一条记录，通过每一条的记录集对另一张表进行数据进行操作，如插入与更新，我们现在假设有一个这样的业务：老师为所在班级的学生选课，选的课程如有哲学、马克思主义政治经济学、毛泽东思想概论、邓小平理论这些课，现在操作主要如下：
 1) 先要查询这些还没有毕业的这些学生的名单，毕业过后的无法进行选课;
 2) 在批量的选取学生的同时，还需要添加对应的某一门课程;
 3) 点添加后选课结束。
 数据量少可能看不出用Java程序直接多次进行数据库操作这种办法实现的弱点，因为它每次在操作数据库的时候，都存在着频繁的和数据库的I/O直接交互，这点性能的牺牲实属不应该，那我们就看下面的方法，通过存储过程的游标方法来实现：建立存储过程：
 Create PROCEDURE P_InsertSubject
 @SubjectId int
 AS
 DECLARE rs CURSOR LOCAL SCROLL FOR
 select studentid from student where StudentGradu = 1
 OPEN rs
 FETCH NEXT FROM rs INTO @tempStudentID
 WHILE @@FETCH_STATUS = 0
 BEGIN
 Insert SelSubject values (@SubjectId,@tempStudentID)
 FETCH NEXT FROM rs INTO @tempStudentID
 END
 CLOSE rs
 GO
 使用游标对记录集循环进行处理的时候一般操作如以下几个步骤：
 1、把记录集传给游标;
 2、打开游标
 3、开始循环
 4、从游标中取值
 5、检查那一行被返回
 6、处理
 7、关闭循环
 8、关闭游标
 上面这种方法在性能上面无疑已经是提高很多了，但我们也想到，在存储过程编写的时候，有时候我们尽量少的避免使用游标来进行操作，所以我们还可以对上面的存储过程进行改造，使用下面的方法来实现：
 Create PROCEDURE P_InsertSubject
 @SubjectId int
 AS
 declare @i int,
 @studentid
 DECLARE @tCanStudent TABLE
 (
 studentid int
 ,FlagID TINYINT
 )
 BEGIN
 insert @tCanStudent select studentid,0 from student where StudentGradu = 1
 SET @i=1
 WHILE( @i>=1)
 BEGIN
 SELECT @studentid='’
 SELECT TOP 1 @studentid = studentid FROM @tCanStudent WHERE flagID=0
 SET @i=@@ROWCOUNT
 IF @i<=0 GOTO Return_Lab
 Insert SelSubject values (@SubjectId,@studentid)
 IF @@error=0
 UPDATE @tCanStudent SET flagID=1 WHERE studentid = @studentid
 Return_Lab:
 END
 End
 GO
 我们现在再来分析以上这个存储过程，它实现的方法是先把满足条件的记录集数据存放到一个表变量中，并且在这个表变量中增加一个FLAGID进行数据初始值为0的存放，然后去循环这个记录集，每循环一次，就把对应的FLAGID的值改成1，然后再根据循环来查找满足条件等于0的情况，可以看到，每循环一次，处理的记录集就会少一次，然后循环的往选好课程表里面插入，直到记录集的条数为0时停止循环，此时完成操作。
 比较以上的几种循环方法的应用，就会知道，有时候可能对于同一种功能我们实现的方法不同，而最终应用程序性能的影响的差异就会很大，第二种、第三种就大大的减少的数据库交互I/O操作的频繁，会节省很多时间，方法三又避免用游标又可以节省不必要的开销。
 
 
 
 
 
使用SQL的Agent可以执行计划任务，把写好的SQL语句放在计划任务里，可以达到奇妙的效果，如定时备份数据，定时执行特定操作等等，当涉及循环操作很多条数据时，这里就要使用游标了，当然SQL中也有循环语句，如使用While。不过while的功能只能实现一般的操作，游标的功能更为强大些，可在一个指定的一个集合内循环操作数据，实现动态操作，那就更牛了，呵呵，以下资料供存档用。
 
WHILE
 设置重复执行 SQL 语句或语句块的条件。只要指定的条件为真，就重复执行语句。可以使用 BREAK 和 CONTINUE 关键字在循环内部控制 WHILE 循环中语句的执行。
 
语法
 WHILE Boolean_expression
       { sql_statement | statement_block }
       [ BREAK ]
       { sql_statement | statement_block }
       [ CONTINUE ]
 
参数
 Boolean_expression
 
返回 TRUE 或 FALSE 的表达式。如果布尔表达式中含有 SELECT 语句，必须用圆括号将 SELECT 语句括起来。
 
{sql_statement | statement_block}
 
Transact-SQL 语句或用语句块定义的语句分组。若要定义语句块，请使用控制流关键字 BEGIN 和 END。
 
BREAK
 
导致从最内层的 WHILE 循环中退出。将执行出现在 END 关键字后面的任何语句，END 关键字为循环结束标记。
 
CONTINUE
 
使 WHILE 循环重新开始执行，忽略 CONTINUE 关键字后的任何语句。
 
注释
 如果嵌套了两个或多个 WHILE 循环，内层的 BREAK 将导致退出到下一个外层循环。首先运行内层循环结束之后的所有语句，然后下一个外层循环重新开始执行。
 
示例
 
declare @i int
 set @i=1
 while @i<30
 begin
 insert into test (userid) values(@i)
 set @i=@i+1
 end
 
------------------------------------------------------------
 
while 条件
 begin
 执行操作
 set @i=@i+1
 end
 
A. 在嵌套的 IF...ELSE 和 WHILE 中使用 BREAK 和 CONTINUE
 在下例中，如果平均价格少于 $30，WHILE 循环就将价格加倍，然后选择最高价。如果最高价少于或等于 $50，WHILE 循环重新启动并再次将价格加倍。该循环不断地将价格加倍直到最高价格超过 $50，然后退出 WHILE 循环并打印一条消息。
 
USE pubs
 GO
 WHILE (SELECT AVG(price) FROM titles) < $30
 BEGIN
      UPDATE titles
         SET price = price * 2
      SELECT MAX(price) FROM titles
      IF (SELECT MAX(price) FROM titles) > $50
         BREAK
      ELSE
         CONTINUE
 END
 PRINT 'Too much for the market to bear'
 
B. 在带有游标的过程中使用 WHILE
 以下的 WHILE 结构是名为 count_all_rows 过程中的一部分。下例中，该 WHILE 结构测试用于游标的函数 @@FETCH_STATUS 的返回值。因为 @@FETCH_STATUS 可能返回 –2、-1 或 0，所以，所有的情况都应进行测试。如果某一行在开始执行此存储过程以后从游标结果中删除，将跳过该行。成功提取 (0) 后将执行 BEGIN...END 循环内部的 SELECT 语句。
 
单变量循环
 USE pubs
 DECLARE tnames_cursor CURSOR
 FOR
      SELECT TABLE_NAME 
      FROM INFORMATION_SCHEMA.TABLES
 OPEN tnames_cursor
 DECLARE @tablename sysname
 --SET @tablename = 'authors'
 FETCH NEXT FROM tnames_cursor INTO @tablename
 WHILE (@@FETCH_STATUS <> -1)
 BEGIN
      IF (@@FETCH_STATUS <> -2)
      BEGIN    
         SELECT @tablename = RTRIM(@tablename) 
         EXEC ('SELECT ''' + @tablename + ''' = count(*) FROM ' 
               + @tablename )
         PRINT ' '
     END
      FETCH NEXT FROM tnames_cursor INTO @tablename
 END
 CLOSE tnames_cursor
 DEALLOCATE tnames_cursor
 
多变量循环
 
CREATE      PROCEDURE my_FeeCount  AS
 declare  @到期时间      char(10)
 declare  @片区          char(20)
 declare  @缴费用户数 char(10)
 declare  @sql  char(2000)
 
declare  cur_data  cursor  for 
  select   convert(varchar(10),到期时间,120)  as 到期时间 ,片区,count(distinct main_id) as  缴费用户数 
  from V_aipu_fee where 提交时间>=convert(varchar(10),getdate()-90,120) and 提交时间<convert(varchar(10),getdate()+1-90,120)
  and 收费类型='续费收费'
  Group   by   convert(varchar(10),到期时间,120),片区
  order   by   convert(varchar(10),到期时间,120) 
 open  cur_data
 
fetch  next  from  cur_data  into  @到期时间,@片区,@缴费用户数  
 while(@@fetch_status  =  0) 
 begin
         set @sql='update '+RTRIM(@片区)+'实收='+RTRIM(@片区)+'实收+'+RTRIM(@缴费用户数)+' where 收费日期='''+RTRIM(@到期时间)+''''
 
 print @sql
 
 fetch  next  from  cur_data  into  @到期时间,@片区,@缴费用户数 
 end
 
close  cur_data
 

 

 
Oracle存储过程中跳出循环的写法
 
发布时间：2020-06-15 09:08:23
 
来源：51CTO
 
阅读：1352
 
作者：51_dezhong
 
记录exit和return的用法
 
exit用来跳出循环
 
loop
 
IF V_KBP IS NULL THEN
 
EXIT;
 
END IF;
 
end loop;
 
return跳出存储过程
 
loop
 
IF V_KBP IS NULL THEN
 
return;
 
END IF;
 
end loop;
 
跳出loop 一次循环
 
oracle 11g已提供continue;
 
oracle 10g及以下,使用goto来替代,例如
 
SQL> set serveroutput on;
 
SQL> declare
 
2  begin
 
3    for i in 1..10 loop
 
4      if mod(i,2)=0 then
 
5        goto next;
 
6      end if;
 
7      dbms_output.put_line(i);
 
8      <>
 
9      null;
 
10    end loop;
 
11  end;
 
12  /
 
注意:<>标签后的null;语句不可少,因为goto标签后必须紧接着一个执行语句

MySQL存储过程中的3种循环,存储过程的基本语法,ORACLE与MYSQL的存储过程/函数的使用区别，退出存储过程方法
 
 
 
在MySQL存储过程的语句中有三个标准的循环方式：WHILE循环，LOOP循环以及REPEAT循环。还有一种非标准的循环方式：GOTO，不过这种循环方式最好别用，很容易引起程序的混乱，在这里就不错具体介绍了。
 
这几个循环语句的格式如下：
 
WHILE……DO……END WHILE
REPEAT……UNTIL END REPEAT
LOOP……END LOOP
GOTO
 
    下面首先使用第一种循环编写一个例子。
 
 
 

  
 
 
 
mysql> create procedure pro10()
    -> begin
    -> declare i int;
    -> set i=0;
    -> while i<5 do
    ->     insert into t1(filed) values(i);
    ->     set i=i+1;
    -> end while;
    -> end;//
 
 

  
 
 
 
Query OK, 0 rows affected (0.00 sec)
     在这个例子中，INSERT和SET语句在WHILE和END WHILE之间，当变量i大于等于5的时候就退出循环。使用set i=0；语句是为了防止一个常见的错误，如果没有初始化，i默认变量值为NULL，而NULL和任何值操作的结果都是NULL。
     执行一下这个存储过程并产看一下执行结果：
 mysql> delete from t1//
 Query OK, 0 rows affected (0.00 sec)
 mysql> call pro10()//
 Query OK, 1 row affected (0.00 sec)
 mysql> select * from t1//
 +——-+
 | filed |
 +——-+
 |     0 |
 |     1 |
 |     2 |
 |     3 |
 |     4 |
 +——-+
 5 rows in set (0.00 sec)
     以上就是执行结果，有5行数据插入到数据库中，证明存储过程编写正确无误^_^。
 
    再来看一下第二个循环控制指令 REPEAT……END REPEAT。使用REPEAT循环控制语句编写下面这个存储过程：
 
 
 

  
 
 
 
mysql> create procedure pro11()
    -> begin
    -> declare i int default 0;
    -> repeat
    ->     insert into t1(filed) values(i);
    ->     set i=i+1;
    ->     until i>=5
    -> end repeat;
    -> end;//
 
 

  
 
 
 
Query OK, 0 rows affected (0.00 sec)
     这个REPEAT循环的功能和前面WHILE循环一样，区别在于它的执行后检查是否满足循环条件（until i>=5），而WHILE则是执行前检查（while i<5 do）。
     不过要注意until i>=5后面不要加分号，如果加分号，就是提示语法错误。
     编写完成后，调用一下这个存储过程，并查看结果：
 mysql> delete from t1//
 Query OK, 5 rows affected (0.00 sec)
 
mysql> call pro11()//
 Query OK, 1 row affected (0.00 sec) #虽然在这里显示只有一行数据受到影响，但是下面选择数据的话，还是插入了5行数据。
 
mysql> select * from t1//
 +——-+
 | filed |
 +——-+
 |     0 |
 |     1 |
 |     2 |
 |     3 |
 |     4 |
 +——-+
 5 rows in set (0.00 sec)
 一行就是执行结果，实际的作用和使用while编写的存储过程一样，都是插入5行数据。
 
再来看一下第三个循环控制语句LOOP……END LOOP。编写一个存储过程程序如下：
 
 
 

  
 
 
 
mysql> create procedure pro12()
    -> begin
    -> declare i int default 0;
    -> loop_label: loop
    ->     insert into t1(filed) values(i);
    ->     set i=i+1;
    ->     if i>=5 then
    ->         leave loop_label;
    ->     end if;
    -> end loop;
    -> end;//
 
 

  
 
 
 Query OK, 0 rows affected (0.00 sec)

 从上面这个例子可以看出，使用LOOP编写同样的循环控制语句要比使用while和repeat编写的要复杂一些：在循环内部加入了IF……END IF语句，在IF语句中又加入了LEAVE语句，LEAVE语句的意思是离开循环，LEAVE的格式是：LEAVE 循环标号。

     编写完存储过程程序后，来执行并查看一下运行结果：

 mysql> delete from t1//

 Query OK, 5 rows affected (0.00 sec) 
 
 
mysql> call pro12//
 Query OK, 1 row affected (0.00 sec) #虽然说只有一行数据受影响，但是实际上是插入了5行数据。
 
mysql> select * from t1//
 +——-+
 | filed |
 +——-+
 |     0 |
 |     1 |
 |     2 |
 |     3 |
 |     4 |
 +——-+
 5 rows in set (0.00 sec)
     执行结果和使用WHILE、LOOP编写的循环一样，都是往标中插入5行值。
 
   Labels   标号和 END Labels 结束标号
    在使用loop的时候，使用到的labels标号，对于labels可以用到while，loop，rrepeat等循环控制语句中。而且有必要好好认识一下lables!！
 mysql> create procedure pro13()
     -> label_1:begin
     -> label_2:while 0=1 do leave label_2;end while;
     -> label_3:repeat leave label_3;until 0=0 end repeat;
     -> label_4:loop leave label_4;end loop;
     -> end;//
 Query OK, 0 rows affected (0.00 sec)
     上面这里例子显示了可以在BEGIN、WHILE、REPEAT或者LOOP语句前使用语句标号，语句标号只能在合法的语句前使用，所以LEAVE label_3意味着离开语句标号名为label_3的语句或符合语句。
     其实，也可以使用END labels来表示标号结束符。
 mysql> create procedure pro14()
     -> label_1:begin
     -> label_2:while 0=1 do leave label_2;end while label_2;
     -> label_3:repeat leave label_3;until 0=0 end repeat label_3;
     -> label_4:loop leave label_4;end loop label_4;
     -> end label_1;//
 Query OK, 0 rows affected (0.00 sec)
     上面就是使用了标号结束符，其实这个结束标号并不是十分有用，而且他必须和开始定义的标号名字一样，否则就会报错。如果要养成一个良好的编程习惯方便他人阅读的话，可以使用这个标号结束符。
 
ITERATE 迭代        
      如果是在ITERATE语句，即迭代语句中的话，就必须使用LEAVE语句。ITERATE只能出现在LOOP，REPEAT和WHILE语句中，它的意思是“再次循环”，例如：
 mysql> create procedure pro15() 
 
 

     -> begin

     -> declare i int default 0;

     -> loop_label:loop

     ->     if i=3 then

     ->         set i=i+1;

     ->         iterate loop_label;

     ->     end if;

     ->     insert into t1(filed) values(i);

     ->     set i=i+1;

     ->     if i>=5 then

     ->        leave loop_label;

     ->     end if;

     ->   end loop;

     -> end;//

 Query OK, 0 rows affected (0.00 sec)

     iterate语句和leave语句一样，也是在循环内部使用，它有点类似于C/C++语言中的continue。

     那么这个存储程序是怎么运行的的？首先i的值为0，条件判断语句if i=3 then判断为假，跳过if语段，向数据库中插入0，然后i+1，同样后面的if i>=5 then判断也为假，也跳过；继续循环，同样插入1和2；在i=3的时候条件判断语句if i=3 then判断为真，执行i=i+1，i值为4，然后执行迭代iterate loop_label;，即语句执行到iterate loop_label;后直接跳到if i=3 then判断语句，执行判断，这个时候由于i=4，if i=3 then判断为假，跳过IF语段，将4添加到表中，i变为5，条件判断if i>=5 then判断为真，执行leave loop_label;跳出loop循环，然后执行end;//，结束整个存储过程。

     综上所述，数据库中将插入数值：0,1,2,4。执行存储过程，并查看结果：|

 mysql> delete from t1//

 Query OK, 5 rows affected (0.00 sec) 
 
 
mysql> call pro15//
 Query OK, 1 row affected (0.00 sec)
 
mysql> select * from t1//
 +——-+
 | filed |
 +——-+
 |     0 |
 |     1 |
 |     2 |
 |     4 |
 +——-+
 4 rows in set (0.00 sec)
 
和我们上面分析的结果一样，只插入了数值0,1,2,4。
 
 
 
存储过程如同一门程序设计语言，同样包含了数据类型、流程控制、输入和输出和它自己的函数库。
 

 --------------------基本语法--------------------
 
一.创建存储过程
 create procedure sp_name()
 begin
 .........
 end
 
二.调用存储过程
 1.基本语法：call sp_name()
 注意：存储过程名称后面必须加括号，哪怕该存储过程没有参数传递
 
三.删除存储过程
 1.基本语法：
 drop procedure sp_name//
 
2.注意事项
 (1)不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程
 
四.其他常用命令
 
1.show procedure status
 显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等
 
2.show create procedure sp_name
 显示某一个MySQL存储过程的详细信息
 

 --------------------数据类型及运算符--------------------
 一、基本数据类型：
 略
 
二、变量：
 
自定义变量：DECLARE   a INT ; SET a=100;    可用以下语句代替：DECLARE a INT DEFAULT 100;
 
变量分为用户变量和系统变量，系统变量又分为会话和全局级变量
 
用户变量：用户变量名一般以@开头，滥用用户变量会导致程序难以理解及管理
 
1、 在mysql客户端使用用户变量
 mysql> SELECT 'Hello World' into @x;
 mysql> SELECT @x;
 
mysql> SET @y='Goodbye Cruel World';
 mysql> select @y;
 
mysql> SET @z=1+2+3;
 mysql> select @z;
 

 2、 在存储过程中使用用户变量
 
mysql> CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World');
 mysql> SET @greeting='Hello';
 mysql> CALL GreetWorld( );
 

 3、 在存储过程间传递全局范围的用户变量
 mysql> CREATE PROCEDURE p1( )   SET @last_procedure='p1';
 mysql> CREATE PROCEDURE p2( ) SELECT CONCAT('Last procedure was ',@last_procedure);
 mysql> CALL p1( );
 mysql> CALL p2( );
 
 
 
三、运算符：
 1.算术运算符
 +     加   SET var1=2+2;       4
 -     减   SET var2=3-2;       1
 *      乘   SET var3=3*2;       6
 /     除   SET var4=10/3;      3.3333
 DIV   整除 SET var5=10 DIV 3; 3
 %     取模 SET var6=10%3 ;     1
 
2.比较运算符
 >            大于 1>2 False
 <            小于 2<1 False
 <=           小于等于 2<=2 True
 >=           大于等于 3>=2 True
 BETWEEN      在两值之间 5 BETWEEN 1 AND 10 True
 NOT BETWEEN 不在两值之间 5 NOT BETWEEN 1 AND 10 False
 IN           在集合中 5 IN (1,2,3,4) False
 NOT IN       不在集合中 5 NOT IN (1,2,3,4) True
 =             等于 2=3 False
 <>, !=       不等于 2<>3 False
 <=>          严格比较两个NULL值是否相等 NULL<=>NULL True
 LIKE          简单模式匹配 "Guy Harrison" LIKE "Guy%" True
 REGEXP       正则式匹配 "Guy Harrison" REGEXP "[Gg]reg" False
 IS NULL      为空 0 IS NULL False
 IS NOT NULL 不为空 0 IS NOT NULL True
 
3.逻辑运算符
 
4.位运算符
 |   或
 &   与
 << 左移位
 >> 右移位
 ~   非(单目运算，按位取反)
 
注释：
 
mysql存储过程可使用两种风格的注释
 双横杠：--
 
该风格一般用于单行注释
 c风格：/* 注释内容 */ 一般用于多行注释
 
--------------------流程控制--------------------
 一、顺序结构
 二、分支结构
 if
 case
 
三、循环结构
 for循环
 while循环
 loop循环
 repeat until循环
 
注：
 区块定义，常用
 begin
 ......
 end;
 也可以给区块起别名，如：
 lable:begin
 ...........
 end lable;
 可以用leave lable;跳出区块，执行区块以后的代码。
 
可以提前退出存储过程（函数直接return）。
 
begin和end如同C语言中的{ 和 }。
 
--------------------输入和输出--------------------
 
mysql存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT
 Create procedure|function([[IN |OUT |INOUT ] 参数名 数据类形...])
 
IN 输入参数
 表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
 
OUT 输出参数
 该值可在存储过程内部被改变，并可返回
 
INOUT 输入输出参数
 调用时指定，并且可被改变和返回
 
IN参数例子：
 CREATE PROCEDURE sp_demo_in_parameter(IN p_in INT)
 BEGIN
 SELECT p_in; --查询输入参数
 SET p_in=2; --修改
 select p_in;--查看修改后的值
 END;
 
执行结果:
 mysql> set @p_in=1
 mysql> call sp_demo_in_parameter(@p_in)
 略
 mysql> select @p_in;
 略
 以上可以看出，p_in虽然在存储过程中被修改，但并不影响@p_id的值
 
OUT参数例子
 创建:
 mysql> CREATE PROCEDURE sp_demo_out_parameter(OUT p_out INT)
 BEGIN
 SELECT p_out;/*查看输出参数*/
 SET p_out=2;/*修改参数值*/
 SELECT p_out;/*看看有否变化*/
 END;
 
执行结果:
 mysql> SET @p_out=1
 mysql> CALL sp_demo_out_parameter(@p_out)
 略
 
mysql> SELECT @p_out;
 略
 
INOUT参数例子：
 mysql> CREATE PROCEDURE sp_demo_inout_parameter(INOUT p_inout INT)
 BEGIN
 SELECT p_inout;
 SET p_inout=2;
 SELECT p_inout;
 END;
 
执行结果：
 set @p_inout=1
 call sp_demo_inout_parameter(@p_inout) //
 略
 select @p_inout;
 略
 
 
 
 
 
附：函数库
 mysql存储过程基本函数包括：字符串类型，数值类型，日期类型
 
一、字符串类
 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，即参数position必须大于等于1
 mysql> select substring(’abcd’,0,2);
 +———————–+
 | substring(’abcd’,0,2) |
 +———————–+
 |                       |
 +———————–+
 1 row in set (0.00 sec)
 
mysql> select substring(’abcd’,1,2);
 +———————–+
 | substring(’abcd’,1,2) |
 +———————–+
 | ab                    |
 +———————–+
 1 row in set (0.02 sec)
 
TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符
 UCASE (string2 ) //转换成大写
 RIGHT(string2,length) //取string2最后length个字符
 SPACE(count) //生成count个空格
 
二、数值类型
 
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.23);
 +————-+
 | round(1.23) |
 +————-+
 |           1 |
 +————-+
 1 row in set (0.00 sec)
 
mysql> select round(1.56);
 +————-+
 | round(1.56) |
 +————-+
 |           2 |
 +————-+
 1 row in set (0.00 sec)
 
(2)可以设定小数位数，返回浮点型数据
 
mysql> select round(1.567,2);
 +—————-+
 | round(1.567,2) |
 +—————-+
 |           1.57 |
 +—————-+
 1 row in set (0.00 sec)
 
SIGN (number2 ) //返回符号,正负或0
 SQRT(number2) //开平方
 
三、日期类型
 
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) //分
 
注：可用在INTERVAL中的类型：DAY ,DAY_HOUR ,DAY_MINUTE ,DAY_SECOND ,HOUR ,HOUR_MINUTE ,HOUR_SECOND ,MINUTE ,MINUTE_SECOND,MONTH ,SECOND ,YEAR
 DECLARE variable_name [,variable_name...] datatype [DEFAULT value]; 
 其中，datatype为mysql的数据类型，如:INT, FLOAT, DATE, VARCHAR(length)
 
例：
 
DECLARE l_int INT unsigned default 4000000; 
 DECLARE l_numeric NUMERIC(8,2) DEFAULT 9.95; 
 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';
 
 ORACLE与MYSQL的存储过程/函数的使用区别
 
 
  
 
   
编号
类别
ORACLE
MYSQL
注释
1
创建存储过程语句不同
create or replace procedure P_ADD_FAC(
    id_fac_cd  IN ES_FAC_UNIT.FAC_CD%TYPE) is
DROP PROCEDURE IF EXISTS `SD_USER_P_ADD_USR`;
 create procedure P_ADD_FAC(
        id_fac_cd  varchar(100))

 1.在创建存储过程时如果存在同名的存储过程,会删除老的存储过程. 
   oracle使用create or replace.
   mysql使用先删除老的存储过程,然后再创建新的存储过程.
 2. oracle 存储过程可以定义在package中,也可以定义在Procedures中. 如果定义在包中,一个包中可以包含多个存储过程和方法.如果定义在Procedures中,存储过程中不可以定义多个存储过程. 
    Mysql  存储过程中不可以定义多个存储过程. 
 3. oracle中字符串类型可以使用varchar2.  
    Mysql 需要使用varchar
 4. Oracle中参数varchar长度不是必须的,
    Mysql中参数varchar长度是必须的, 比如varchar(100) 
2
创建函数语句不同
CREATE OR REPLACEFUNCTION F_ROLE_FACS_GRP(
      ii_role_int_key IN SD_ROLE.ROLE_INT_KEY%TYPE
     ) RETURN VARCHAR2
DROP FUNCTION IF EXISTS `SD_ROLE_F_ROLE_FACS_GRP`;
 CREATE  FUNCTION `SD_ROLE_F_ROLE_FACS_GRP`(
  ii_role_int_key INTEGER(10)
 ) RETURNS varchar(1000) 
1.在创建函数时如果存在同名的函数,会删除老的函数.  
   oracle使用create or replace.
   mysql使用先删除老的函数,然后再创建新的函数.
 2. oracle 函数可以定义在package中,也可以定义在Functions中. 如果定义在包中,一个包中可以包含多个存储过程和函数.如果定义在Functions中,每个函数只能定义一个函数.
    Mysql  Functions不可以定义多个函数. 
 3.  oracle返回值用return. 
     Mysql返回值用returns. 
3
传入参数写法不同
procedure P_ADD_FAC(
    id_fac_cd  IN ES_FAC_UNIT.FAC_CD%TYPE)
create procedure P_ADD_FAC(
      (in) id_fac_cd  varchar(100))

 1. oracle存储过程参数可以定义为表的字段类型.
    Mysql存储过程不支持这种定义方法.需要定义变量的实际类型和长度.
 2. oracle 参数类型in/out/inout写在参数名后面. 
    Mysql  参数类型in/out/inout写在参数名前面.
 3. oracle 参数类型in/out/inout 都必须写.
    Mysql  参数类型如果是in,则可以省略. 如果是out或inout则不能省略.
 注意: mysql中指定参数为IN, OUT, 或INOUT 只对PROCEDURE是合法的。（FUNCTION参数总是被认为是IN参数） RETURNS字句只能对FUNCTION做指定，对函数而言这是强制的。它用来指定函数的返回类型，而且函数体必须包含一个RETURN value语句。  
function func_name(
            gw_id  in(out)  varchar2 )
create function func_name(
        gw_id varchar（100))
4
包的声明方式
create or replace package/package body package name
拆分成多个存储过程或函数
oracle可以创建包,包中可以包含多个存储过程和方法. 
 mysql没有没有包这个概念,可以分别创建存储过程和方法. 每个存储过程或方法都需要放在一个文件中. 
 例1: 方法命名 
 oracle 中SD_FACILITY_PKG.F_SEARCH_FAC 
 to mysql SD_FACILITY_F_SEARCH_FAC 
 例2: 过程命名
 oracle 中SD_FACILITY_PKG.P_ADD_FAC
 to mysql SD_FACILITY_P_ADD_FAC 
 
5
存储过程返回语句不一样
return;
LEAVE proc; (proc 代表最外层的begin end)
oracle存储过程和方法都可以使用return退出当前过程和方法. 
 Mysql存储过程中只能使用leave退出当前存储过程.不可以使用return. 
 Mysql方法可以使用return退出当前方法. 
6
存储过程异常处理不一样
EXCEPTION
     WHEN OTHERS THEN
     ROLLBACK ;
     ov_rtn_msg := c_sp_name||'('|| li_debug_pos ||'):'||
         TO_CHAR(SQLCODE)||': '||SUBSTR(SQLERRM,1,100);
DECLARE EXIT HANDLER FOR  SQLEXCEPTION 
  BEGIN
     ROLLBACK ;
     set ov_rtn_msg = concat(c_sp_name,'(', li_debug_pos ,'):',
         TO_CHAR(SQLCODE),': ',SUBSTR(SQLERRM,1,100));
  END;
oracle : 内部异常不需要定义,在存储过程或函数末尾写上EXCEPTION后,后面的部分即为异常处理的部分.  oracle可以定义自定义异常,自定义异常需要使用raise关键字抛出异常后,才可以在EXCEPTION中捕获.
 
 mysql: mysql内部异常也需要先定义,在定义的同时也需要实现异常的功能. 
           目前mysql不支持自定义异常. 
7
过程和函数的声明变量的位置不同
声明变量在begin…end体之前
声明变量在begin...end体内，begin之后其他任何内容之前
 
8
NO_DATA_FOUND异常处理
 EXCEPTION
         WHEN NO_DATA_FOUND THEN
             oi_rtn_cd := 1;
             ov_rtn_msg := SD_COMMON.P_GET_MSG('DP-CBM-01100a-016',
                                                  li_sub_rtn_cd,
                                                  lv_sub_rtn_msg
                                                  );
使用FOUND_ROWS()代替NO_DATA_FOUND. 详见注释.
oracle中: 
 NO_DATA_FOUND是游标的一个属性. 
 当select没有查到数据就会出现 no data found 的异常，程序不会向下执行.
 
 Mysql: 
 没有NO_DATA_FOUND这个属性.但可是使用FOUND_ROWS()方法得到select语句查询出来的数据.如果FOUND_ROWS()得到的值为0,就进入异常处理逻辑. 
9
在存储过程中调用存储过程方式的不同
Procedure_Name(参数);
Call Procedure_Name(参数);
MYSQL存储过程调用存储过程，需要使用Call pro_name(参数).  
 Oracle调用存储过程直接写存储过程名就可以了. 
10
抛异常的方式不同
RAISE Exception_Name;
见备注
详见<<2009002-OTMPPS-Difficult Questions-0001.doc>>中2.5 Mysql异常处理部分
 
  
 
  

    
  
 
 
异常处理
 
MySQL的GET DIAGNOSTICS语句
 
 这是一个把我困扰已久的问题，今天偶然间解决了。
 
 以前用Oracle时经常会用到的三个东西：sql%rowcount、SQLCODE、SQLERRM
 sql%rowcount用于记录最近一条DML语句修改的记录条数，就如你在sqlplus下执行delete from之后提示已删除xx行一样。
 SQLCODE和SQLERRM是Oracle的异常处理函数，常被用于得到完整错误提示信息，方便错误时处理。
 
 那么问题来了，MySQL有没有相似的功能呢？以前查了很久也没有找到好的解决办法，然而在5.6.4以后，MySQL提供了GET DIAGNOSTICS语法，那么我的问题也随之迎刃而解。
 
 简单讲GET DIAGNOSTICS能提供以下两种信息：
 语句信息，例如错误信息号或者语句影响的行数。
 错误信息，例如错误号和错误消息。
 
 
如果一条语句产生了三种错误，诊断区域包含的语句和错误信息类似这样：
 
 
 
Statement information: row count ... other statement information items ... Condition area list: Condition area 1: error code for condition 1 error message for condition 1 ... other condition information items ... Condition area 2: error code for condition 2: error message for condition 2 ... other condition information items ... Condition area 3: error code for condition 3 error message for condition 3 ... other condition information items ...

 使用GET DIAGNOSTICS需要注意的是，它或者包含语句信息，或者包含错误信息，但一个GET DIAGNOSTICS不会同时包含语句信息和错误信息，所以需要用两个GET DIAGNOSTICS来获得语句信息和错误信息。
 
 获得语句信息：
 GET DIAGNOSTICS @p1 = NUMBER, @p2 = ROW_COUNT;
 
 获得错误信息：
 GET DIAGNOSTICS CONDITION 1 @p3 = RETURNED_SQLSTATE, @p4 = MESSAGE_TEXT;
 
 语句信息条目名称有：
 NUMBER 
 | ROW_COUNT
 
 错误信息条目名称有：
 CLASS_ORIGIN 
 | SUBCLASS_ORIGIN
 | RETURNED_SQLSTATE
 | MESSAGE_TEXT
 | MYSQL_ERRNO
 | CONSTRAINT_CATALOG
 | CONSTRAINT_SCHEMA
 | CONSTRAINT_NAME
 | CATALOG_NAME
 | SCHEMA_NAME
 | TABLE_NAME
 | COLUMN_NAME
 | CURSOR_NAME
 
 为了确保获得正确的主错误信息，必须使用类似如下的语句：
 GET DIAGNOSTICS @cno = NUMBER;
 GET DIAGNOSTICS CONDITION @cno @errno = MYSQL_ERRNO;
 
 最后来看一个使用GET DIAGNOSTICS完整例子（摘自官方文档）
 
  
 
   

    
   
 
   
BEGIN 
-- Declare variables to hold diagnostics area information 
DECLARE code CHAR(5) DEFAULT '00000'; 
DECLARE msg TEXT; 
DECLARE rows INT; 
DECLARE result TEXT; 

-- Declare exception handler for failed insert 
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 
BEGIN 
GET DIAGNOSTICS CONDITION 1 code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT; 
END; 
-- Perform the insert 
INSERT INTO t1 (int_col) VALUES(value); 

-- Check whether the insert was successful 
IF code = '00000' THEN 
GET DIAGNOSTICS rows = ROW_COUNT; 
SET result = CONCAT('insert succeeded, row count = ',rows); 
ELSE 
SET result = CONCAT('insert failed, error = ',code,', message = ',msg); 
END IF; 
-- Say what happened 
SELECT result; 
END
 
   

    结果：insert failed, error = 42S02, message = Table 'ud_omcs.t1' doesn't exist
   
 
  
 

目录
 
介绍
 
sp_executesql与EXECUTE命令
 
例1.0
 
例1.1
 
存储过程中的动态SQL
 
例2.0
 
在动态SQL中使用Like操作符，IN操作符和Order By
 
例3.0 - 使用LIKE操作符
 
例3.1 - 使用IN操作符
 
例3.2 - 使用Order By子句
 
结论
 

本文介绍如何在存储过程中构建和执行动态SQL。
 
介绍
 
在存储过程中的动态SQL是单个Transact-SQL语句或存储在变量中并使用SQL命令执行的一组语句。在SQL Server中可能有几种实现方法。本文将向您展示一种很好的方法。在详细解释之前，让我告诉你“何时使用动态SQL？” 我们不能肯定地说静态SQL将满足我们所有的编程需求。当我们需要根据不同的搜索参数检索一组记录时，需要动态SQL。比如说——员工搜索屏幕或通用报告，需要SELECT根据不同的WHERE子句执行不同的语句。
 
注意：最重要的是，变量中的动态SQL查询不会被编译、解析、检查错误，直到它们被执行。
 
sp_executesql与EXECUTE命令
 
可以使用EXECUTE命令或sp_executesql语句执行动态构建的Transact-SQL 语句。在这篇文章中我的示例里，我将使用sp_executesql，其更高效、执行更快并且还支持参数替换。如果我们使用EXECUTE命令来执行SQL String，那么所有参数都应该转换为字符，并在执行之前作为Query的一部分。但该sp_executesql声明提供了一种更好的实现方法。它允许我们将参数值替换为SQL String中指定的任何参数。在进入实际示例之前，让我用一个简单的例子来区分这两个命令。使用WHERE子句中的ID从employee表中选择一条记录。
 
使用EXECUTE命令的基本语法：
 
EXECUTE(@SQLStatement)
 
使用sp_executesql的基本语法：
 
sp_executesql [@SQLStatement],[@ParameterDefinitionList],
[@ParameterValueList]
 
例1.0
 
/* Using EXECUTE Command */
/* Build and Execute a Transact-SQL String with a single parameter 
 value Using EXECUTE Command */

/* Variable Declaration */
DECLARE @EmpID AS SMALLINT
DECLARE @SQLQuery AS NVARCHAR(500)
/* set the parameter value */
SET @EmpID = 1001
/* Build Transact-SQL String with parameter value */
SET @SQLQuery = 'SELECT * FROM tblEmployees WHERE EmployeeID = ' + 
CAST(@EmpID AS NVARCHAR(10))
/* Execute Transact-SQL String */
EXECUTE(@SQLQuery)
 
在上面的示例1.0中，声明了两个变量。第一个变量@EmpID用作SQL查询的参数，第二个变量@SQLQuery用于构建SQL String。您可以清楚地看到变量@EmpID被强制转换为NVarchar 类型并作为SQL字符串的一部分。如果打印@SQLQuery字符串（PRINT @SQLQuery），您将获得实际的SQL查询，如下所示：
 
SELECT * FROM tblEmployees WHERE EmployeeID = 1001
 
最后，使用该EXECUTE命令执行上述查询  。
 
例1.1
 
/* Using sp_executesql */
/* Build and Execute a Transact-SQL String with a single parameter 
value Using sp_executesql Command */

/* Variable Declaration */
DECLARE @EmpID AS SMALLINT
DECLARE @SQLQuery AS NVARCHAR(500)
DECLARE @ParameterDefinition AS NVARCHAR(100)
/* set the parameter value */
SET @EmpID = 1001
/* Build Transact-SQL String by including the parameter */
SET @SQLQuery = 'SELECT * FROM tblEmployees WHERE EmployeeID = @EmpID' 
/* Specify Parameter Format */
SET @ParameterDefinition =  '@EmpID SMALLINT'
/* Execute Transact-SQL String */
EXECUTE sp_executesql @SQLQuery, @ParameterDefinition, @EmpID
 
在示例1.1中，声明了三个变量。该变量@EmpID用作SQL查询的参数，第二个变量@SQLQuery用于构建SQL字符串，第三个变量@ParameterDefinition用于在执行SQL字符串之前指定参数格式。如果您打印@SQLQuery字符串（PRINT @SQLQuery），您将获得如下所示的查询：
 
SELECT * FROM tblEmployees WHERE EmployeeID = @EmpID
 
在这个例子中，您可以清楚地看到该参数@EmpID包含在语句中。最后，sp_executesql获取必要的信息来执行参数替换并执行动态构建的SQL字符串。
 
@SQLQuery - >包含SQL语句
@ParameterDefinition - >包含参数定义
@EmpID - >包含要替换为SQL语句中的参数的参数值。
注意：动态SQL字符串中包含的参数必须在“参数定义列表”和“参数值列表”中具有相应的条目。
 
存储过程中的动态SQL
 
本文的这一部分解释了一个真实的示例和示例过程“如何在存储过程中构建和执行动态SQL？”
 
例2.0
 
让我们举一个简单的例子——Employee表有普通字段，如EmployeeID，Name，Department，Designation，JoiningDate，Salary 和Description。您可以使用以下Transact-SQL CREATE TABLE语句在数据库中创建表Employee。
 
/* Transact-Sql to create the table tblEmployees */
CREATE TABLE tblEmployees
(
    EmployeeID       SMALLINT IDENTITY(1001,1) NOT NULL,
    EmployeeName     NVARCHAR(100) NOT NULL,
    Department       NVARCHAR(50) NOT NULL,
    Designation      NVARCHAR(50) NOT NULL,
    JoiningDate      DATETIME NOT NULL,
    Salary           DECIMAL(10,2) NOT NULL,
    [Description]    NVARCHAR(1000) NULL 
)
 
以下INSERT语句将一些示例记录插入tblEmployee 表中：
 
/* Transact SQL to insert some sample records into tblEmployee table */
INSERT INTO tblEmployees
(EmployeeName, Department, Designation, 
 JoiningDate, Salary, [Description]) 
VALUES    
('John Smith', 'IT Research', 'Research Analyst', 
 '02/08/2005', 23000.00, 'Analyst since 2005')

INSERT INTO tblEmployees
(EmployeeName, Department, Designation, 
 JoiningDate, Salary, [Description]) 
VALUES    
('John Micheal', 'IT Operations', 'Manager', 
 '07/15/2007', 15000.00, NULL)

INSERT INTO tblEmployees
(EmployeeName, Department, Designation, 
 JoiningDate, Salary, [Description]) 
VALUES    
('Will Smith', 'IT Support', 'Manager', 
 '05/20/2006', 13000.00, 'Joined last year as IT Support Manager')
 
我们的程序员可能会获得开发Employee搜索屏幕或生成Employee列表报告的任务，该报告将搜索数据库并根据搜索条件返回结果。在这种情况下，搜索界面应足够灵活，以便在数据库中搜索所有可能的标准。用户可能需要搜索以下详细信息：
 
使用Name搜索特定的Employee明细
在特定Department中的Employee列表
在特定Designation中的 Employee列表
去年加入该组织的Employee列表
Employees Salary > =某些特定金额的s 列表
上面列出的任何这些条件或所有这些条件
我在这里列出了一些可能的条件。可能还有许多其他可能性完全取决于用户要求。这里让我们列出几个可能的标准，并编写一个构建动态SQL的存储过程，它将用于搜索Employee表中的详细信息。以下CREATE PROCEDURE语句将创建一个存储过程“sp_EmployeeSelect”，其中包含必要的输入参数和变量以构建动态SQL。
 
/* This stored procedure builds dynamic SQL and executes 
using sp_executesql */
Create Procedure sp_EmployeeSelect
    /* Input Parameters */
    @EmployeeName NVarchar(100),
    @Department NVarchar(50),
    @Designation NVarchar(50),
    @StartDate DateTime,
    @EndDate DateTime,
    @Salary    Decimal(10,2)
        
AS
    Set NoCount ON
    /* Variable Declaration */
    Declare @SQLQuery AS NVarchar(4000)
    Declare @ParamDefinition AS NVarchar(2000) 
    /* Build the Transact-SQL String with the input parameters */ 
    Set @SQLQuery = 'Select * From tblEmployees where (1=1) ' 
    /* check for the condition and build the WHERE clause accordingly */
    If @EmployeeName Is Not Null 
         Set @SQLQuery = @SQLQuery + ' And (EmployeeName = @EmployeeName)'

    If @Department Is Not Null
         Set @SQLQuery = @SQLQuery + ' And (Department = @Department)' 
  
    If @Designation Is Not Null
         Set @SQLQuery = @SQLQuery + ' And (Designation = @Designation)'
  
    If @Salary Is Not Null
         Set @SQLQuery = @SQLQuery + ' And (Salary >= @Salary)'

    If (@StartDate Is Not Null) AND (@EndDate Is Not Null)
         Set @SQLQuery = @SQLQuery + ' And (JoiningDate 
         BETWEEN @StartDate AND @EndDate)'
    /* Specify Parameter Format for all input parameters included 
     in the stmt */
    Set @ParamDefinition =      ' @EmployeeName NVarchar(100),
                @Department NVarchar(50),
                @Designation NVarchar(50),
                @StartDate DateTime,
                @EndDate DateTime,
                @Salary    Decimal(10,2)'
    /* Execute the Transact-SQL String with all parameter value's 
       Using sp_executesql Command */
    Execute sp_Executesql     @SQLQuery, 
                @ParamDefinition, 
                @EmployeeName, 
                @Department, 
                @Designation, 
                @StartDate, 
                @EndDate,
                @Salary
                
    If @@ERROR <> 0 GoTo ErrorHandler
    Set NoCount OFF
    Return(0)
  
ErrorHandler:
    Return(@@ERROR)
GO
 
此示例存储过程将少量参数作为输入，并使用两个变量来构建和执行。@SQLQuery用于构建动态SQL语句。@ParamDefinition用于定义参数的格式。在每个步骤中构建SQL字符串时，使用IF-statement来检查输入的参数是否为Null。如果不是NULL，则该参数将包含在SQL语句中，该语句基本上在SQL语句的WHERE 子句中添加条件。您可以在过程中清楚地看到变量@ParamDefinition包含所有参数列表，最后使用sp_Executesql得到SQL-query、参数列表和参数值来执行SELECT语句。
 
让我们考虑一下上面列出的一些标准，看看这个存储过程是如何工作的。
 
Employee使用名称搜索特定详细信息。
/* 1. Search for specific Employee Detail with the Name say 'John Smith'. */
EXEC sp_EmployeeSelect 'John Smith', NULL, NULL, NULL, NULL, NULL
 
执行上述语句将列出Employee “ John Smith” 的详细信息。
 
在特定的Department中的Employee列表，和
在特定的Designation中Employee列表。
/* 2. List of Employees in a specific Department. AND 
3. List of Employees in a specific Designation. */
/* Say Department = 'IT Operations'  AND  Designation = 'Manager'*/
EXEC sp_EmployeeSelect NULL, 'IT Operations', 'Manager', NULL, NULL, NULL
 
执行上述声明将列出在IT运营部门中的Manager的详细信息。
 
在动态SQL中使用Like操作符，IN操作符和Order By
 
当我们构建动态SQL时，可能会有一些我们需要使用LIKE操作符，IN操作符和Order BY子句的实例。但是这些操作符和Order By子句使用的参数不像通常在使用sp_executesql对“ =”和“Between”操作符时那样工作。通常sp_executesql不会对order by子句执行参数替换，这样做会导致列引用问题。直接使用LIKE操作符和IN操作符会导致语法错误，当我们将参数包含在动态SQL语句中时，无法纠正。可以通过在动态SQL语句中包含实际参数值来解决此问题。以下示例显示在使用sp_executesql时如何使用Like操作符、IN操作符和OrderBy子句。
 
例3.0 - 使用LIKE操作符
 
例3.0使用LIKE操作符选择Employee名称为'John'的列表。在此示例中，参数不包含在SQL语句中，而是将参数的实际值添加到SQL语句中。所以在这里，不需要参数定义来执行SQL字符串。这同样适用于下面显示的其他两个例子：
 
/* Variable Declaration */
DECLARE @EmpName AS NVARCHAR(50)
DECLARE @SQLQuery AS NVARCHAR(500)

/* Build and Execute a Transact-SQL String with a single parameter 
value Using sp_executesql Command */
SET @EmpName = 'John' 
SET @SQLQuery = 'SELECT * FROM tblEmployees 
WHERE EmployeeName LIKE '''+ '%' + @EmpName + '%' + '''' 
EXECUTE sp_executesql @SQLQuery
 
例3.1 - 使用IN操作符
 
Example 3.1使用IN操作符选择Employee细节（ID = 1001,1003）：
 
/* Variable Declaration */
DECLARE @EmpID AS NVARCHAR(50)
DECLARE @SQLQuery AS NVARCHAR(500)

/* Build and Execute a Transact-SQL String with a single 
parameter value Using sp_executesql Command */
SET @EmpID = '1001,1003' 
SET @SQLQuery = 'SELECT * FROM tblEmployees 
WHERE EmployeeID IN(' + @EmpID + ')'
EXECUTE sp_executesql @SQLQuery
 
例3.2 - 使用Order By子句
 
例3.2 Employee 按“ Department”列对记录进行排序。
 
/* Variable Declaration */
DECLARE @OrderBy AS NVARCHAR(50)
DECLARE @SQLQuery AS NVARCHAR(500)

/* Build and Execute a Transact-SQL String with a single parameter 
value Using sp_executesql Command */
SET @OrderBy = 'Department' 
SET @SQLQuery = 'SELECT * FROM tblEmployees Order By ' + @OrderBy

EXECUTE sp_executesql @SQLQuery
 
结论
 
在本文中，我已经用几个例子对“如何在存储过程中构建和执行动态SQL”进行了解释。希望本文能够以一种友好的方式帮助您理解和编写动态SQL。
 
 
 
原文地址：https://www.codeproject.com/Articles/20815/Building-Dynamic-SQL-In-a-Stored-Procedure