精华内容
下载资源
问答
  • 2021-05-03 04:16:55

    此查询返回我想要的内容(来自查询的逗号分隔值)

    SELECT emp_id, LISTAGG(HOBBIES, ',') WITHIN GROUP (ORDER BY HOBBIES)

    FROM employee_list

    WHERE emp_id = 123456

    GROUP BY emp_id;

    结果:

    Squash,Tennis,Walking

    初步数据:

    emp_id hobbies

    -------------------

    123456 Walking

    123456 Tennis

    123456 Squash

    000001 Bowling

    000002 Tennis

    000002 Soccer

    当我尝试将其转换为这样的存储过程时:

    CREATE OR REPLACE PROCEDURE GET_EMP_ID_FOR_HOBBIES(id NUMBER)

    AS

    x varchar(255);

    BEGIN

    SELECT emp_id, LISTAGG(HOBBIES, ',') WITHIN GROUP (ORDER BY HOBBIES)

    INTO x

    FROM employee_list

    WHERE emp_id = id

    GROUP BY HOBBIES;

    END;

    它在编译时返回以下错误

    错误(6,8):PL / SQL:ORA-00947:值不够

    如果它是插入物,我会理解它。我做错了什么?

    1

    投票

    您正在选择两个列表达式emp_id和listagg()结果 - 但您只选择单个变量x。

    您需要两个变量,并在into子句中列出,以匹配列表达式。

    ...

    AS

    l_emp_id employee_list.emp_id%TYPE;

    l_hobbies varchar2(4000); -- has to be big enough

    BEGIN

    SELECT emp_id, LISTAGG(HOBBIES, ',') WITHIN GROUP (ORDER BY HOBBIES)

    INTO l_emp_id, l_hobbies

    FROM employee_list

    WHERE emp_id = id

    GROUP BY emp_id;

    ...

    如果你确实想要一个变量,那么你需要一个列表达式 - 要么删除其中一个,因为你真的不需要另一个ID副本:

    ...

    AS

    l_hobbies varchar2(4000); -- has to be big enough

    BEGIN

    SELECT LISTAGG(HOBBIES, ',') WITHIN GROUP (ORDER BY HOBBIES)

    INTO l_hobbies

    FROM ...

    或者可能不太可能,将ID和爱好连接成单个字符串值。当然,x变量必须足够大以容纳组合字符串。

    这取决于您在局部变量中使用它们后计划对值进行的操作。

    更多相关内容
  • 存储过程fetch into

    千次阅读 2021-01-18 22:14:55
    SQL Server 中,对结果集及游标的使用[总结]一、对结果集使用的解决办法如何在存储过程中使用另一个存储过程的结果集,或者对动态SQL语句的执行结果再次使用,可以使用下面这样的语句:INSERT INTO table_name EXEC ...

    SQL Server 中,对结果集及游标的使用[总结]

    一、对结果集使用的解决办法

    如何在存储过程中使用另一个存储过程的结果集,或者对动态SQL语句的执行结果再次使用,可以使用下面这样的语句:

    INSERT INTO table_name EXEC procedure_name @parameters_var

    使用代码如下:

    --1 创建一个临时表...

    文章

    老朱教授

    2017-11-26

    881浏览量

    Sql server存储过程中常见游标循环用法

    原文:Sql server存储过程中常见游标循环用法

    用游标,和WHILE可以遍历您的查询中的每一条记录并将要求的字段传给变量进行相应的处理

    DECLARE

    @A1 VARCHAR(10),

    @A2 VARCHAR(10),

    @A3 INT

    DECLARE YOUCURNAME CURSOR ...

    文章

    杰克.陈

    2016-04-17

    820浏览量

    好用的清理数据库脚本

    1、脚本说明

    此脚本用于清空数据库数据,只删除相关表记录,保留表结构及存储过程触发器等主要架构。

    设计思路:

    1)根据表添加时间逆向获取所有用户表信息

    2)使用游标循环删除每张表内数据

    3)使用delete进行删除,即使有外键关系同样可以删除表记录

    4)表存在自增主键则将其...

    文章

    寒凝雪

    2017-07-03

    1080浏览量

    2107fbe6c5ed95c9d4c764a2741ac656.png

    阿里云爆款特惠专场,精选爆款产品低至0.95折!

    爆款ECS云服务器8.1元/月起,云数据库低至1.5折,限时抢购!

    广告

    MySQL利用存储过程清除所有表中的所有记录

    MySQL利用存储过程清除所有表中的所有记录

    在使用MySQL过程中,难免会出现一些测试数据,然而这些测试数据在真正部署运行的时候,通常要被清楚掉,并且自增长的字段要回复初始值。如何高效快速的实现以上功能,可以使用存储过程实现这一个功能。

    主要使用:TRUNCATE TABLE。不仅可以清楚数据...

    文章

    推荐码发放

    2018-05-02

    1585浏览量

    Mysql 存储过程应用

    关闭订单存储过程

    begin

    declare done int default 0;

    declare orderid varchar(22);

    #声明查询订单的游标

    declare cur cursor for select orders.order_id from orders where or...

    文章

    余二五

    2017-11-23

    796浏览量

    oracle与sql server大批量存储过程可以替换部分

    1、将oracle 建立存储过程的代码 CREATE OR REPLACE Procedure 存储过程名中的 OR REPLACE 替换为空

    2、将oracle的变量(和字段名不重名的)直接替换成@变量名。

    例如:  user_id  替换为 @user_id

    3、将IS 替换为 ...

    文章

    jiankunking

    2014-01-21

    854浏览量

    oracle与sql server大批量存储过程可以替换部分

    1、将oracle 建立存储过程的代码 CREATE OR REPLACE Procedure 存储过程名中的 OR REPLACE 替换为空

    2、将oracle的变量(和字段名不重名的)直接替换成@变量名。

    例如:  user_id  替换为 @user_id

    3、将IS 替换为 ...

    文章

    衣舞晨风

    2014-01-21

    676浏览量

    mysql存储过程之游标遍历数据表

    原文:mysql存储过程之游标遍历数据表

    今天写一个mysql存储过程,根据自己的需求要遍历一个数据表,因为对存储过程用的不多,语法不甚熟悉,加之存储过程没有调试环境,花了不少时间才慢慢弄好,故留个痕迹。

    1 BEGIN

    2 DECLARE Done INT DEFAULT 0;...

    文章

    杰克.陈

    2016-09-08

    753浏览量

    利用navicat创建存储过程、触发器和使用游标的简单实例

    创建存储过程和触发器

    1、建表

    首先先建两张表(users表和number表),具体设计如下图:

    2、存储过程

    写一个存储过程,往users表中插入数据,创建过程如下:

    代码如下:

    BEGIN

    #Routine body goes here....

    文章

    家宝

    2017-06-29

    2033浏览量

    sql 游标循环遍历

    原文:sql 游标循环遍历

    写存储过程的时候碰到一个需要对数据进行遍历循环操作的问题,最后通过游标解决了,感觉很适用、

    1 declare @level varchar(100)

    2 declare @uid varchar(100)

    3 declare cur cursor--定义一...

    文章

    杰克.陈

    2014-04-12

    723浏览量

    跟屌丝一起学习 DB2 第五课 存储过程(二)

    REPEAT语句ftch_loop2:

    REPEAT

    FETCH  c1  INTO  v_firstname,  v_midinit,  v_lastname;

    UNTIL  SQLCODE  <>  0  AND  REPEAT  ftch_loop2;

    WHILE语句

    ...

    文章

    玄学酱

    2017-07-10

    1119浏览量

    常用的PL/SQL开发原则

    广泛使用UROWID来处理DML语句

    在你的存储过程中谨慎使用DDL语句,因为这可能会破坏你的transaction的连续性,更为严重的是可能会阻塞DML操作并可能会导致大量library cache latch争用并且有可能会导致某些sql执行计划的改变。

    ...

    文章

    rudy_gao

    2014-08-15

    512浏览量

    写了一个简单的对多游标控制的存储过程

    写了一个简单的对多游标控制的存储过程。此例是示范游标的具体使用方法。不过生产过程中不推荐使用游标。因为完全可以用INTO 变量语句和循环来代替游标。1、SP 代码:

    DELIMITER $$DROP PROCEDURE IF EXISTS `test2`.`sp_cur_demo`$$CREATE...

    文章

    科技小能手

    2017-11-15

    587浏览量

    oracle 存储过程的基本语法

    原文:oracle 存储过程的基本语法1.基本结构 CREATE OR REPLACE PROCEDURE 存储过程名字(    参数1 IN NUMBER,    参数2 IN NUMBER) IS变量1 INTEGER :=0;变量2 DATE;BEGIN

    END 存储过程名字2.SELE...

    文章

    杰克.陈

    2014-12-09

    511浏览量

    Oracle下我个人认为有些事情不一定用存储过程,但还是说一下

    oracle 存储过程的基本语法

    1.基本结构

    CREATE OR REPLACE PROCEDURE 存储过程名字

    (

    参数1 IN NUMBER,

    参数2 IN NUMBER

    ) IS

    变量1 INTEGER :=0;

    变量2 DATE;

    BEGIN

    END 存储过程名字

    2...

    文章

    余二五

    2017-11-15

    655浏览量

    SQL Server修改表结构后批量更新所有视图

    最近修改了数据库表结构,数据同步的时候出了问题,发现很多数据明明已经修改,但是通过视图筛选出来的还是原来的数据,所以怀疑应该是视图缓存了数据,在园子里找到下面的博文,在这里做个记录备忘。

    原文链接:http://www.cnblogs.com/yashen/archive/2004/12/23...

    文章

    不能超过五

    2010-09-14

    781浏览量

    mysql 游标

    游标

    游标用于多行处理, 打开内存块, 并在内存中进行操作

    调用前必须先声明, 并且经历四个过程

    声明

    打开

    FETCH 传递

    关闭

    example:

    DECLARE c1 CURSOR FOR SELECT id,data FROM new.t...

    文章

    terry.zeng

    2012-02-14

    1180浏览量

    SqlServer:此数据库处于单用户模式,导致数据库无法删除的处理

    今天在删除一个数据库时,一直报错,大意是:此数据库处理单用户模式,尚在连接当中,无法删除(既使将SQLServer停止后再启动也是如此)

    百度之后找到了解决办法,备份于此:

    USE [master]

    GO

    /****** Object: StoredProcedure [dbo].[kil...

    文章

    杨俊明

    2011-03-28

    773浏览量

    oracle 存储过程的基本语法 及注意事项

    oracle 存储过程的基本语法

    1.基本结构

    CREATE OR REPLACE PROCEDURE 存储过程名字

    (

    参数1 IN NUMBER,

    参数2 IN NUMBER

    ) IS

    变量1 INTEGER :=0;

    变量2 DATE;

    BEGIN

    END 存储过程名字2...

    文章

    老先生二号

    2017-05-28

    582浏览量

    MySQL 第八篇:自定义函数、存储过程、游标

    我把MySQL的内容整理成9篇博客,学完这9篇博客虽不能说能成为大神,但是应付一般中小企业的开发已经足够了,有疑问或建议的欢迎留言讨论。

    自定义函数

    一、函数的概念与定义

    1、理解函数

    函数可以看作是一个加工作坊,这个加工作坊接收调用者传递过来的原料(传递的参数),然后将这些原料加...

    文章

    我巴巴

    2017-10-29

    2079浏览量

    MySql的存储过程

    存储过程和函数是在数据库中定义一些SQL语句的集合,然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复的编写相同的SQL语句。而且,存储过程和函数是在MySQL服务器中存储和执行的。可以减少客户端和服务器端的数据传输。

    创建存储过程和存储函数

    数据库系...

    文章

    jephon

    2016-11-07

    719浏览量

    游标

    一:什么是游标游标是可以在结果集中上下游动的指针二:创建一个简单的游标

    CodeCode highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-->use xlandg...

    文章

    xland

    2009-01-10

    533浏览量

    [转]删除MSSQL所有的约束及表格

    --删除所有约束、表、视图等SQL脚本

    --###############################################

    --删除所有外键约束

    --###############################################

    DECLARE @SQL VARC...

    文章

    netwild

    2015-07-30

    487浏览量

    Oracle与Sql Server差异点详解

    1、create函数或存储过程异同点

    Oracle 创建函数或存储过程一般是 create or replace ……

    SQL SERVER 则是在创建之前加一条语句,先判断是否已经存在,如果存在删除已有的函数或存储过程。

    函数语句:

    if exists (select ...

    文章

    jiankunking

    2014-01-22

    1041浏览量

    Oracle与Sql Server差异点详解

    1、create函数或存储过程异同点

    Oracle 创建函数或存储过程一般是 create or replace ……

    SQL SERVER 则是在创建之前加一条语句,先判断是否已经存在,如果存在删除已有的函数或存储过程。

    函数语句:

    if exists (select ...

    文章

    衣舞晨风

    2014-01-22

    878浏览量

    [原创]通过动态Sql语句,一次性彻底删除或者修改SBO的某个字段对应内容的信息

    尽管SBO强烈要求我们不要通过Sql语句在数据库维护界面上修改SBO业务信息,事实上,任何一个ERP等管理软件的厂商也都是这样要求的。不过有些时候,我们只有--真的只有这一条路了。

    不过,要修改SBO的某个主数据的主键信息,还是应该慎重,至少您需要有能力一次性将数据库中的对应的所有数据全部修改...

    文章

    科技小先锋

    2017-11-21

    864浏览量

    Mysql----浅入浅出之视图、存储过程、触发器

    一、视图 VIEW

    视图是虚拟的表,本身不存储不论什么数据。仅仅有执行时,才包括动态检索出来的数据。

    eg:SELECT sid, name, sex, s_num FROM  student, school WHERE sid = 2 AND student.sid = scholl.sid ;...

    文章

    技术mix呢

    2017-10-04

    940浏览量

    存储过程之四—游标

    游标在存储过程和函数中使用。语法如同在嵌入的SQL中。游标是只读的及不滚动的,只能在一个方向上进行遍历,不能在记录之间随意进退,不能跳过某些记录,所以每次读完之后就应该移动到下一个记录。游标必须在声明处理程序之前被声明,并且变量和条件必须在声明光标或处理程序之前被声明。

    一、游标

    1、定义

    ...

    文章

    烟火_

    2014-08-20

    417浏览量

    SQL Server中授予用户查看对象定义的权限

    SQL Server中授予用户查看对象定义的权限   在SQL Server中,有时候需要给一些登录名(用户)授予查看所有或部分对象(存储过程、函数、视图、表)的定义权限存。如果是部分存储过程、函数、视图授予查看定义的权限,那么就像下面脚本所示,比较繁琐:     GRANT VIEW DEFIN...

    文章

    潇湘隐者

    1970-01-01

    1137浏览量

    Oracle存储过程

    先来一个简单的例子,有个感性认识.

    create or replace procedure test is

    aa number; begin    select count(*) into aa from t_server_server;

    DBMS_OUTPUT.PUT_LINE(aa);...

    文章

    科技小先锋

    2017-11-07

    737浏览量

    展开全文
  • SQLSERVER存储过程语法详解

    万次阅读 2018-09-01 11:36:13
    用户必须在执行过程时提供每个所声明参数的(除非定义了该参数的默认值)。 OUTPUT  表明参数是返回参数。该选项的可以返回给 EXEC[UTE]。使用 OUTPUT 参数可将信息返回给调用过程。Text、ntext 和 image ...

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    CREATE PROC [ EDURE ] procedure_name [ ; number ]

        [ { @parameter data_type }

            [ VARYING ] [ = default ] [ OUTPUT ]

        ] [ ,...n ]

     

    [ WITH

        { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

     

    [ FOR REPLICATION ]

     

    AS sql_statement [ ...n ]

     

    @parameter 
    过程中的参数。在 Create PROCEDURE 语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值(除非定义了该参数的默认值)。

    OUTPUT 
    表明参数是返回参数。该选项的值可以返回给 EXEC[UTE]。使用 OUTPUT 参数可将信息返回给调用过程。Text、ntext 和 image 参数可用作 OUTPUT 参数。使用 OUTPUT 关键字的输出参数可以是游标占位符。 

    AS :指定过程要执行的操作

     

    SQLSERVER:
    变量的声明:
    声明变量时必须在变量前加@符号 
    DECLARE @I INT

    变量的赋值:
    变量赋值时变量前必须加set
    SET @I = 30

    声明多个变量:
    DECLARE @s varchar(10),@a INT

     

     

     

     

     

     

     

     

    ----------------------------------------------------------------------------------------

    oracle的建表sql转成sqlserver的建表sql时的注意点 :
    1.所有的comment语句需要删除。
    2.clob类型转换为text类型。
    3.blob类型转换为image类型。
    4.number类型转换为int,number(16,2)等转换为decimal(16,2),number(18)转换为bigint。
    5.default sysdate改为default getDate()。
    6.to_date('2009-12-18','yyyy-mm-dd')改为cast('2009-12-18'  as   datetime)

    SQLSERVER:
    变量的声明:
    声明变量时必须在变量前加@符号
    DECLARE @I INT

    变量的赋值:
    变量赋值时变量前必须加set
    SET @I = 30

    声明多个变量:
    DECLARE @s varchar(10),@a INT

    if语句:

    Java代码  收藏代码

    1. if ..  
    2. begin  
    3.   ...  
    4. end  
    5. else if ..  
    6. begin  
    7.   ...  
    8. end  
    9. else  
    10. begin  
    11.   ...  
    12. end   

     Example:

    Sql代码  收藏代码

    1. DECLARE @d INT  
    2. set @d = 1  
    3. IF @d = 1 BEGIN  
    4.    PRINT '正确'  
    5. END  
    6. ELSE BEGIN  
    7.    PRINT '错误'  
    8. END  

     
    多条件选择语句:
    Example:

    Sql代码  收藏代码

    1. declare @today int  
    2. declare @week nvarchar(3)  
    3. set @today=3  
    4. set @week= case  
    5.      when @today=1 then '星期一'  
    6.      when @today=2 then '星期二'  
    7.      when @today=3 then '星期三'  
    8.      when @today=4 then '星期四'  
    9.      when @today=5 then '星期五'  
    10.      when @today=6 then '星期六'  
    11.      when @today=7 then '星期日'  
    12.      else '值错误'  
    13. end  
    14. print @week  

     
    循环语句:

    Java代码  收藏代码

    1. WHILE 条件 BEGIN    
    2. 执行语句  
    3. END    

     Example:

    Java代码  收藏代码

    1. DECLARE @i INT  
    2. SET @i = 1  
    3. WHILE @i<1000000 BEGIN  
    4. set @i=@i+1  
    5. END  

     

    定义游标:

    Sql代码  收藏代码

    1. DECLARE @cur1 CURSOR FOR SELECT .........  
    2.   
    3. OPEN @cur1  
    4. FETCH NEXT FROM @cur1 INTO 变量  
    5. WHILE(@@FETCH_STATUS=0)  
    6. BEGIN  
    7. 处理.....  
    8. FETCH NEXT FROM @cur1 INTO 变量  
    9. END  
    10. CLOSE @cur1  
    11. DEALLOCATE @cur1   

     

    Sql代码  收藏代码

    1. AS  
    2.   
    3. declare @CATEGORY_CI_TABLENAME VARCHAR(50) =''  
    4. declare @result VARCHAR(2000) = ''  
    5. declare @CI_ID DECIMAL = 0  
    6. declare @num int = 1  
    7. declare @countnum int = 1  
    8.   
    9. BEGIN  
    10. select  @countnum = count(ATTRIBUTE_CONFIG_ID) from T_ATTRIBUTE_CONFIG where CMDB_UPDATE_FLAG= 'Y' and CATEGORY_CODE =@CATEGORY_CODE  
    11.    
    12. IF (@ATTRIBUTE2='A')  
    13.   begin    
    14.         DECLARE MyCursor CURSOR for select ATTRIBUTE_CONFIG_CODE from T_ATTRIBUTE_CONFIG where  CMDB_UPDATE_FLAG= 'Y' and CATEGORY_CODE =@CATEGORY_CODE  
    15.          OPEN MyCursor FETCH NEXT FROM MyCursor INTO @CONFIG_CODE  
    16.                 set @result = @result+@CONFIG_CODE+','  
    17.              WHILE @@FETCH_STATUS = 0  
    18.                     BEGIN  
    19.                     FETCH NEXT FROM MyCursor INTO @CONFIG_CODE  
    20.                     set @num = @num+ 1  
    21.                         if(@num<@countnum)   
    22.                             begin  
    23.                                 set @result = @result+@CONFIG_CODE+','  
    24.                             end   
    25.                         else if(@num=@countnum)   
    26.                              begin  
    27.                                 set @result = @result +@CONFIG_CODE  
    28.                              end   
    29.                     END  
    30.             CLOSE MyCursor   
    31.             DEALLOCATE MyCursor   
    32.         set @result = 'insert into ' + @ATTRIBUTE1 + '(' + @result +') select '+ @result +' from '+@CATEGORY_CI_TABLENAME +' where CI_ORDER_LINE_ID='+@KEY_ID  
    33.   end             
    34.  else if((@ATTRIBUTE2='U'))  

     

    临时表:

    -- Select INTO 从一个查询的计算结果中创建一个新表。 数据并不返回给客户端,这一点和普通的Select 不同。 新表的字段具有和 Select 的输出字段相关联(相同)的名字和数据类型。
            select * into NewTable
                from Uname

    -- Insert INTO ABC Select
            -- 表ABC必须存在 
            -- 把表Uname里面的字段Username复制到表ABC
            Insert INTO ABC Select Username FROM Uname

    -- 创建临时表
            Create TABLE #temp(
                UID int identity(1, 1) PRIMARY KEY,
                UserName varchar(16),
                Pwd varchar(50),
                Age smallint,
                Sex varchar(6)
            )
            
    -- 打开临时表
            Select * from #temp

     

     1、局部临时表(#开头)只对当前连接有效,当前连接断开时自动删除。
    2、全局临时表(##开头)对其它连接也有效,在当前连接和其他访问过它的连接都断开时自动删除。
    3、不管局部临时表还是全局临时表,只要连接有访问权限,都可以用drop table #Tmp(或者drop table ##Tmp)来显式删除临时表。

    临时表对执行效率应该影响不大,只要不是太过份,相反可以提高效率特别是连接查询的地方,只要你的数据库临时表空间足够
    游标多,会严重执行效率,能免则免!

     

    临时表在不同数据库设计中的作用

    SQLSERVER 存储过程 语法

     ===============================================================================

    其他:

    --有输入参数的存储过程--

    create proc GetComment

    (@commentid int)

    as

    select * from Comment where CommentID=@commentid

     

    --有输入与输出参数的存储过程--

    create proc GetCommentCount

    @newsid int,

    @count int output

    as

    select @count=count(*) from Comment where NewsID=@newsid

     

     

    --返回单个值的函数--

    create function MyFunction

    (@newsid int)

    returns int

    as

    begin

    declare @count int

    select @count=count(*) from Comment where NewsID=@newsid

    return @count

    end

     

    --调用方法--

    declare @count int

    exec @count=MyFunction 2

    print @count

     

    --返回值为表的函数--

    Create function GetFunctionTable

    (@newsid int)

    returns table

    as

    return

    (select * from Comment where NewsID=@newsid)

     

    --返回值为表的函数的调用--

    select * from GetFunctionTable(2)

     

     

    -----------------------------------------------------------------------------------------------------------------------------------

    SQLServer 存储过程中不拼接SQL字符串实现多条件查询

     以前拼接的写法
      set @sql=' select * from table where 1=1 '
      if (@addDate is not null) 
       set @sql = @sql+' and addDate = '+ @addDate + ' ' 
      if (@name <>'' and is not null) 
       set @sql = @sql+ ' and name = ' + @name + ' '
      exec(@sql)
    下面是 不采用拼接SQL字符串实现多条件查询的解决方案
      第一种写法是 感觉代码有些冗余
      if (@addDate is not null) and (@name <> '') 
       select * from table where addDate = @addDate and name = @name 
      else if (@addDate is not null) and (@name ='') 
       select * from table where addDate = @addDate 
      else if(@addDate is null) and (@name <> '') 
       select * from table where and name = @name 
      else if(@addDate is null) and (@name = '') 
      select * from table 
      第二种写法是 
      select * from table where (addDate = @addDate or @addDate is null) and (name = @name or @name = '') 
      第三种写法是 
      SELECT * FROM table where 
      addDate = CASE @addDate IS NULL THEN addDate ELSE @addDate END, 
      name = CASE @name WHEN '' THEN name ELSE @name END

     

    -----------------------------------------------------------------------------------------------------------------------------------

     

    SQLSERVER存储过程基本语法

     

    一、定义变量

    --简单赋值
    declare @a  int
    set @a=5
    print @a
       
    --使用select语句赋值
    declare @user1 nvarchar(50)
    select @user1= '张三'
    print @user1
    declare @user2 nvarchar(50)
    select @user2 =  Name from ST_User  where ID=1
    print @user2
       
    --使用update语句赋值
    declare @user3 nvarchar(50)
    update ST_User  set @user3 =  Name where ID=1
    print @user3

     

    二、表、临时表、表变量

    --创建临时表1
    create table #DU_User1
    (
          [ID] [ int  NOT NULL ,
          [Oid] [ int ]  NOT NULL ,
          [Login] [nvarchar](50)  NOT NULL ,
          [Rtx] [nvarchar](4)  NOT NULL ,
          [ Name ] [nvarchar](5)  NOT NULL ,
          [ Password ] [nvarchar]( max )  NULL ,
          [State] [nvarchar](8)  NOT NULL
    );
    --向临时表1插入一条记录
    insert into #DU_User1 (ID,Oid,[Login],Rtx, Name ,[ Password ],State)  values (100,2, 'LS' ,'0000' , '临时' , '321' , '特殊' );
       
    --从ST_User查询数据,填充至新生成的临时表
    select *  into #DU_User2  from ST_User  where ID<8
       
    --查询并联合两临时表
    select *  from #DU_User2  where ID<3  union select *  from #DU_User1
       
    --删除两临时表
    drop table #DU_User1
    drop table #DU_User2

     

    --创建临时表
    CREATE TABLE #t
    (
         [ID] [ int ]  NOT NULL ,
         [Oid] [ int ]  NOT NULL ,
         [Login] [nvarchar](50)  NOT NULL ,
         [Rtx] [nvarchar](4)  NOT NULL ,
         [ Name ] [nvarchar](5)  NOT NULL ,
         [ Password ] [nvarchar]( max )  NULL ,
         [State] [nvarchar](8)  NOT NULL ,
    )
       
    --将查询结果集(多条数据)插入临时表
    insert into #t  select *  from ST_User
    --不能这样插入
    --select * into #t from dbo.ST_User
       
    --添加一列,为int型自增长子段
    alter table #t  add [myid]  int NOT NULL IDENTITY(1,1)
    --添加一列,默认填充全球唯一标识
    alter table #t  add [myid1] uniqueidentifier  NOT NULL default (newid())
       
    select *  from #t
    drop table #t
    --给查询结果集增加自增长列
       
    --无主键时:
    select IDENTITY( int ,1,1) as ID,  Name ,[Login],[ Password ]  into #t  from ST_User
    select *  from #t
       
    --有主键时:
    select ( select SUM (1)  from ST_User  where ID<= a.ID)  as myID,*  from ST_User a  order bymyID
    --定义表变量
    declare @t  table
    (
         id  int not null ,
         msg nvarchar(50)  null
    )
    insert into @t  values (1, '1' )
    insert into @t  values (2, '2' )
    select *  from @t

     三、循环

    --while循环计算1到100的和
    declare @a  int
    declare @ sum int
    set @a=1
    set @ sum =0
    while @a<=100
    begin
         set @ sum +=@a
         set @a+=1
    end
    print @ sum

    四、条件语句

    --if,else条件分支
    if(1+1=2)
    begin
         print  '对'
    end
    else
    begin
         print  '错'
    end
       
    --when then条件分支
    declare @today  int
    declare @week nvarchar(3)
    set @today=3
    set @week= case
         when @today=1  then '星期一'
         when @today=2  then '星期二'
         when @today=3  then '星期三'
         when @today=4  then '星期四'
         when @today=5  then '星期五'
         when @today=6  then '星期六'
         when @today=7  then '星期日'
         else '值错误'
    end
    print @week

     

    五、游标

    declare @ID  int
    declare @Oid  int
    declare @Login  varchar (50)
       
    --定义一个游标
    declare user_cur  cursor for select ID,Oid,[Login]  from ST_User
    --打开游标
    open user_cur
    while @@fetch_status=0
    begin
    --读取游标
         fetch next from user_cur  into @ID,@Oid,@Login
         print @ID
         --print @Login
    end
    close user_cur
    --摧毁游标
    deallocate user_cur

    六、触发器

       触发器中的临时表:

      Inserted 
      存放进行insert和update 操作后的数据 
      Deleted 
      存放进行delete 和update操作前的数据

    --创建触发器
    Create trigger User_OnUpdate 
         On ST_User 
         for Update  
    As  
         declare @msg nvarchar(50)
         --@msg记录修改情况
         select @msg = N '姓名从“' + Deleted. Name + N '”修改为“' + Inserted. Name +  '”' fromInserted,Deleted
         --插入日志表
         insert into [LOG](MSG) values (@msg)
           
    --删除触发器
    drop trigger User_OnUpdate

    七、存储过程

    --创建带output参数的存储过程
    CREATE PROCEDURE PR_Sum
         @a  int ,
         @b  int ,
         @ sum int output
    AS
    BEGIN
         set @ sum =@a+@b
    END
       
    --创建Return返回值存储过程
    CREATE PROCEDURE PR_Sum2
         @a  int ,
         @b  int
    AS
    BEGIN
         Return @a+@b
    END
           
    --执行存储过程获取output型返回值
    declare @mysum  int
    execute PR_Sum 1,2,@mysum  output
    print @mysum
       
    --执行存储过程获取Return型返回值
    declare @mysum2  int
    execute @mysum2= PR_Sum2 1,2
    print @mysum2

     

       

    八、自定义函数

      函数的分类:

        1)标量值函数

        2)表值函数

            a:内联表值函数

            b:多语句表值函数

        3)系统函数

     

    --新建标量值函数
    create function FUNC_Sum1
    (
         @a  int ,
         @b  int
    )
    returns int
    as
    begin
         return @a+@b
    end
       
    --新建内联表值函数
    create function FUNC_UserTab_1
    (
         @myId  int
    )
    returns table
    as
    return ( select *  from ST_User  where ID<@myId)
       
    --新建多语句表值函数
    create function FUNC_UserTab_2
    (
         @myId  int
    )
    returns @t  table
    (
         [ID] [ int ]  NOT NULL ,
         [Oid] [ int ]  NOT NULL ,
         [Login] [nvarchar](50)  NOT NULL ,
         [Rtx] [nvarchar](4)  NOT NULL ,
         [ Name ] [nvarchar](5)  NOT NULL ,
         [ Password ] [nvarchar]( max )  NULL ,
         [State] [nvarchar](8)  NOT NULL
    )
    as
    begin
         insert into @t  select *  from ST_User  where ID<@myId
         return
    end
       
    --调用表值函数
    select *  from dbo.FUNC_UserTab_1(15)
    --调用标量值函数
    declare @s  int
    set @s=dbo.FUNC_Sum1(100,50)
    print @s
       
    --删除标量值函数
    drop function FUNC_Sum1

    谈谈自定义函数与存储过程的区别:

    一、自定义函数:

      1. 可以返回表变量

      2. 限制颇多,包括

        不能使用output参数;

        不能用临时表;

        函数内部的操作不能影响到外部环境;

        不能通过select返回结果集;

        不能update,delete,数据库表;

      3. 必须return 一个标量值或表变量

      自定义函数一般用在复用度高,功能简单单一,争对性强的地方。

    二、存储过程

      1. 不能返回表变量

      2. 限制少,可以执行对数据库表的操作,可以返回数据集

      3. 可以return一个标量值,也可以省略return

       存储过程一般用在实现复杂的功能,数据操纵方面。

     

    -----------------------------------------------------------------------------------------------------------------------------------

    SqlServer存储过程--实例

    实例1:只返回单一记录集的存储过程。

      表银行存款表(bankMoney)的内容如下

     

    Id

    userID

    Sex

    Money

    001

    Zhangsan

    30

    002

    Wangwu

    50

    003

    Zhangsan

    40

     

    要求1:查询表bankMoney的内容的存储过程

    create procedure sp_query_bankMoney
    as
    select * from bankMoney
    go
    exec sp_query_bankMoney

    注*  在使用过程中只需要把T-Sql中的SQL语句替换为存储过程名,就可以了很方便吧!

    实例2(向存储过程中传递参数):

    加入一笔记录到表bankMoney,并查询此表中userID= Zhangsan的所有存款的总金额。

    Create proc insert_bank @param1 char(10),@param2 varchar(20),@param3 varchar(20),@param4 int,@param5 int output
    with encryption ---------加密
    as
    insert into bankMoney (id,userID,sex,Money)
    Values(@param1,@param2,@param3, @param4)
    select @param5=sum(Money) from bankMoney where userID='Zhangsan'
    go
    在SQL Server查询分析器中执行该存储过程的方法是:
    declare @total_price int
    exec insert_bank '004','Zhangsan','男',100,@total_price output
    print '总余额为'+convert(varchar,@total_price)
    go

    在这里再啰嗦一下存储过程的3种传回值(方便正在看这个例子的朋友不用再去查看语法内容):

    1.以Return传回整数
    2.以output格式传回参数
    3.Recordset

    传回值的区别:

    output和return都可在批次程式中用变量接收,而recordset则传回到执行批次的客户端中。

    实例3:使用带有复杂 SELECT 语句的简单过程

      下面的存储过程从四个表的联接中返回所有作者(提供了姓名)、出版的书籍以及出版社。该存储过程不使用任何参数。

      USE pubs
    IF EXISTS (SELECT name FROM sysobjects
             WHERE name = 'au_info_all' AND type = 'P')
       DROP PROCEDURE au_info_all
    GO
    CREATE PROCEDURE au_info_all
    AS
    SELECT au_lname, au_fname, title, pub_name
       FROM authors a INNER JOIN titleauthor ta
          ON a.au_id = ta.au_id INNER JOIN titles t
          ON t.title_id = ta.title_id INNER JOIN publishers p
          ON t.pub_id = p.pub_id
    GO

      au_info_all 存储过程可以通过以下方法执行:

      EXECUTE au_info_all
    -- Or
    EXEC au_info_all

      如果该过程是批处理中的第一条语句,则可使用:

      au_info_all

    实例4:使用带有参数的简单过程

      CREATE PROCEDURE au_info
       @lastname varchar(40),
       @firstname varchar(20)
    AS
    SELECT au_lname, au_fname, title, pub_name
       FROM authors a INNER JOIN titleauthor ta
          ON a.au_id = ta.au_id INNER JOIN titles t
          ON t.title_id = ta.title_id INNER JOIN publishers p
          ON t.pub_id = p.pub_id
       WHERE  au_fname = @firstname
          AND au_lname = @lastname
    GO

      au_info 存储过程可以通过以下方法执行:

      EXECUTE au_info 'Dull', 'Ann'
    -- Or
    EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann'
    -- Or
    EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull'
    -- Or
    EXEC au_info 'Dull', 'Ann'
    -- Or
    EXEC au_info @lastname = 'Dull', @firstname = 'Ann'
    -- Or
    EXEC au_info @firstname = 'Ann', @lastname = 'Dull'

      如果该过程是批处理中的第一条语句,则可使用:

      au_info 'Dull', 'Ann'
    -- Or
    au_info @lastname = 'Dull', @firstname = 'Ann'
    -- Or
    au_info @firstname = 'Ann', @lastname = 'Dull'

     

     实例5:使用带有通配符参数的简单过程

    CREATE PROCEDURE au_info2
    @lastname varchar(30) = 'D%',
    @firstname varchar(18) = '%'
    AS
    SELECT au_lname, au_fname, title, pub_name
    FROM authors a INNER JOIN titleauthor ta
       ON a.au_id = ta.au_id INNER JOIN titles t
       ON t.title_id = ta.title_id INNER JOIN publishers p
       ON t.pub_id = p.pub_id
    WHERE au_fname LIKE @firstname
       AND au_lname LIKE @lastname
    GO

      au_info2 存储过程可以用多种组合执行。下面只列出了部分组合:

      EXECUTE au_info2
    -- Or
    EXECUTE au_info2 'Wh%'
    -- Or
    EXECUTE au_info2 @firstname = 'A%'
    -- Or
    EXECUTE au_info2 '[CK]ars[OE]n'
    -- Or
    EXECUTE au_info2 'Hunter', 'Sheryl'
    -- Or
    EXECUTE au_info2 'H%', 'S%'

      = 'proc2'

    实例6:if...else

    存储过程,其中@case作为执行update的选择依据,用if...else实现执行时根据传入的参数执行不同的修改. 
    --下面是if……else的存储过程: 
    if exists (select 1 from sysobjects where name = 'Student' and type ='u' )
    drop table Student
    go

    if exists (select 1 from sysobjects where name = 'spUpdateStudent' and type ='p' )
    drop proc spUpdateStudent
    go

    create table Student
    (
    fName nvarchar (10),
    fAge 

    smallint ,
    fDiqu varchar (50),
    fTel  int 
    )
    go

    insert into Student values ('X.X.Y' , 28, 'Tesing' , 888888)
    go

    create proc spUpdateStudent
    (
    @fCase int ,
    @fName nvarchar (10),
    @fAge smallint ,
    @fDiqu varchar (50),
    @fTel  int 
    )
    as 
    update Student
    set fAge = @fAge, -- 传 1,2,3 都要更新 fAge 不需要用 case 
    fDiqu = (case when @fCase = 2 or @fCase = 3 then @fDiqu else fDiqu end ),
    fTel  = (case when @fCase = 3 then @fTel else fTel end )
    where fName = @fName
    select * from Student
    go

    -- 只改 Age 
    exec spUpdateStudent
    @fCase = 1,
    @fName = N'X.X.Y' ,
    @fAge = 80,
    @fDiqu = N'Update' ,
    @fTel  = 1010101

    -- 改 Age 和 Diqu 
    exec spUpdateStudent
    @fCase = 2,
    @fName = N'X.X.Y' ,
    @fAge = 80,
    @fDiqu = N'Update' ,
    @fTel  = 1010101

    -- 全改 
    exec spUpdateStudent
    @fCase = 3,
    @fName = N'X.X.Y' ,
    @fAge = 80,
    @fDiqu = N'Update' ,
    @fTel  = 1010101

    展开全文
  • 使用SQL存储过程

    千次阅读 2016-07-01 14:22:46
    SQLServer 存储过程中不拼接SQL字符串实现多条件查询  以前拼接的写法   set @ sql = ' select * from table where 1=1 '   if (@addDate is not null )   set @ sql = @ sql + ' and ...

    SQLSERVER:

    变量的声明:

    声明变量时必须在变量前加@符号

    DECLARE @I INT

    变量的赋值:

    变量赋值时变量前必须加set

    SET @I = 30

    声明多个变量:

    DECLARE @s varchar(10),@a INT

    if语句:

    if ..  
    begin  
      ...  
    end  
    else if ..  
    begin  
      ...  
    end  
    else  
    begin  
      ...  
    end   
     Example:
    DECLARE @d INT  
    set @d = 1  
    IF @d = 1 BEGIN  
       PRINT '正确'  
    END  
    ELSE BEGIN  
       PRINT '错误'  
    END  

    多条件选择语句:

    Example:
    declare @today int  
    declare @week nvarchar(3)  
    set @today=3  
    set @week= case  
         when @today=1 then '星期一'  
         when @today=2 then '星期二'  
         when @today=3 then '星期三'  
         when @today=4 then '星期四'  
         when @today=5 then '星期五'  
         when @today=6 then '星期六'  
         when @today=7 then '星期日'  
         else '值错误'  
    end  
    print @week  

    循环语句:

    WHILE 条件 BEGIN    
    执行语句  
    END    
     Example:
    Java代码  收藏代码
    DECLARE @i INT  
    SET @i = 1  
    WHILE @i<1000000 BEGIN  
    set @i=@i+1  
    END 

    定义游标:

    DECLARE @cur1 CURSOR FOR SELECT .........  
    
    OPEN @cur1  
    FETCH NEXT FROM @cur1 INTO 变量  
    WHILE(@@FETCH_STATUS=0)  
    BEGIN  
    处理.....  
    FETCH NEXT FROM @cur1 INTO 变量  
    END  
    CLOSE @cur1  
    DEALLOCATE @cur1   
    
    AS  
    
    declare @CATEGORY_CI_TABLENAME VARCHAR(50) =''  
    declare @result VARCHAR(2000) = ''  
    declare @CI_ID DECIMAL = 0  
    declare @num int = 1  
    declare @countnum int = 1  
    
    BEGIN  
    select  @countnum = count(ATTRIBUTE_CONFIG_ID) from T_ATTRIBUTE_CONFIG where CMDB_UPDATE_FLAG= 'Y' and CATEGORY_CODE =@CATEGORY_CODE  
    
    IF (@ATTRIBUTE2='A')  
      begin    
            DECLARE MyCursor CURSOR for select ATTRIBUTE_CONFIG_CODE from T_ATTRIBUTE_CONFIG where  CMDB_UPDATE_FLAG= 'Y' and CATEGORY_CODE =@CATEGORY_CODE  
             OPEN MyCursor FETCH NEXT FROM MyCursor INTO @CONFIG_CODE  
                    set @result = @result+@CONFIG_CODE+','  
                 WHILE @@FETCH_STATUS = 0  
                        BEGIN  
                        FETCH NEXT FROM MyCursor INTO @CONFIG_CODE  
                        set @num = @num+ 1  
                            if(@num<@countnum)   
                                begin  
                                    set @result = @result+@CONFIG_CODE+','  
                                end   
                            else if(@num=@countnum)   
                                 begin  
                                    set @result = @result +@CONFIG_CODE  
                                 end   
                        END  
                CLOSE MyCursor   
                DEALLOCATE MyCursor   
            set @result = 'insert into ' + @ATTRIBUTE1 + '(' + @result +') select '+ @result +' from '+@CATEGORY_CI_TABLENAME +' where CI_ORDER_LINE_ID='+@KEY_ID  
      end             
     else if((@ATTRIBUTE2='U'))  

    临时表:

    – Select INTO 从一个查询的计算结果中创建一个新表。 数据并不返回给客户端,这一点和普通的Select 不同。 新表的字段具有和 Select 的输出字段相关联(相同)的名字和数据类型。

            select * into NewTable
                from Uname

    – Insert INTO ABC Select
    – 表ABC必须存在
    – 把表Uname里面的字段Username复制到表ABC

            Insert INTO ABC Select Username FROM Uname

    – 创建临时表

            Create TABLE #temp(
                UID int identity(1, 1) PRIMARY KEY,
                UserName varchar(16),
                Pwd varchar(50),
                Age smallint,
                Sex varchar(6)
            )

    – 打开临时表

            Select * from #temp
    1. 局部临时表(#开头)只对当前连接有效,当前连接断开时自动删除。
    2. 全局临时表(##开头)对其它连接也有效,在当前连接和其他访问过它的连接都断开时自动删除。
    3. 不管局部临时表还是全局临时表,只要连接有访问权限,都可以用drop table #Tmp(或者drop table
      ##Tmp)来显式删除临时表。

    临时表对执行效率应该影响不大,只要不是太过份,相反可以提高效率特别是连接查询的地方,只要你的数据库临时表空间足够
    游标多,会严重执行效率,能免则免!

    临时表在不同数据库设计中的作用

    SQLSERVER 存储过程 语法
    ===============================================================================
    其他:
    –有输入参数的存储过程–

    create proc GetComment
    (@commentid int)
    as
    select * from Comment where CommentID=@commentid

    –有输入与输出参数的存储过程–

    create proc GetCommentCount
    @newsid int,
    @count int output
    as
    select @count=count(*) from Comment where NewsID=@newsid

    –返回单个值的函数–

    create function MyFunction
    (@newsid int)
    returns int
    as
    begin
    declare @count int
    select @count=count(*) from Comment where NewsID=@newsid
    return @count
    end

    –调用方法–

    declare @count int
    exec @count=MyFunction 2
    print @count

    –返回值为表的函数–

    Create function GetFunctionTable
    (@newsid int)
    returns table
    as
    return
    (select * from Comment where NewsID=@newsid)

    –返回值为表的函数的调用–

    select * from GetFunctionTable(2)

    SQLServer 存储过程中不拼接SQL字符串实现多条件查询

     以前拼接的写法

      set @sql=' select * from table where 1=1 '
      if (@addDate is not null) 
       set @sql = @sql+' and addDate = '+ @addDate + ' ' 
      if (@name <>'' and is not null) 
       set @sql = @sql+ ' and name = ' + @name + ' '
      exec(@sql)

    下面是 不采用拼接SQL字符串实现多条件查询的解决方案
      第一种写法是 感觉代码有些冗余

      if (@addDate is not null) and (@name <> '') 
       select * from table where addDate = @addDate and name = @name 
      else if (@addDate is not null) and (@name ='') 
       select * from table where addDate = @addDate 
      else if(@addDate is null) and (@name <> '') 
       select * from table where and name = @name 
      else if(@addDate is null) and (@name = '') 
      select * from table 

      第二种写法是

      select * from table where (addDate = @addDate or @addDate is null) and (name = @name or @name = '') 

      第三种写法是

      SELECT * FROM table where 
      addDate = CASE @addDate IS NULL THEN addDate ELSE @addDate END, 
      name = CASE @name WHEN '' THEN name ELSE @name END

    SQLSERVER存储过程基本语法

    定义变量

    –简单赋值

    declare @a  int
    set @a=5 
    print @a 

    –使用select语句赋值

    declare @user1 nvarchar(50) 
    select @user1= '张三'
    print @user1 
    declare @user2 nvarchar(50) 
    select @user2 =  Name from ST_User  where ID=1 
    print @user2 

    –使用update语句赋值

    declare @user3 nvarchar(50) 
    update ST_User  set @user3 =  Name where ID=1 
    print @user3

    表、临时表、表变量

    –创建临时表1

    create table #DU_User1 
    ( 
          [ID] [ int ]   NOT NULL , 
          [Oid] [ int ]  NOT NULL , 
          [Login] [nvarchar](50)  NOT NULL , 
          [Rtx] [nvarchar](4)  NOT NULL , 
          [ Name ] [nvarchar](5)  NOT NULL , 
          [ Password ] [nvarchar]( max )  NULL , 
          [State] [nvarchar](8)  NOT NULL
    ); 

    –向临时表1插入一条记录

    insert into #DU_User1 (ID,Oid,[Login],Rtx, Name ,[ Password ],State)  values (100,2, 'LS' , '0000' , '临时' , '321' , '特殊' ); 

    –从ST_User查询数据,填充至新生成的临时表

    select *  into #DU_User2  from ST_User  where ID<8 

    –查询并联合两临时表

    select *  from #DU_User2  where ID<3  union select *  from #DU_User1 

    –删除两临时表

    drop table #DU_User1 
    drop table #DU_User2

    –创建临时表

    CREATE TABLE #t 
    ( 
         [ID] [ int ]  NOT NULL , 
         [Oid] [ int ]  NOT NULL , 
         [Login] [nvarchar](50)  NOT NULL , 
         [Rtx] [nvarchar](4)  NOT NULL , 
         [ Name ] [nvarchar](5)  NOT NULL , 
         [ Password ] [nvarchar]( max )  NULL , 
         [State] [nvarchar](8)  NOT NULL , 
    ) 

    –将查询结果集(多条数据)插入临时表

    insert into #t  select *  from ST_User 

    –不能这样插入

    select * into #t from dbo.ST_User 

    –添加一列,为int型自增长子段

    alter table #t  add [myid]  int NOT NULL IDENTITY(1,1) 

    –添加一列,默认填充全球唯一标识

    alter table #t  add [myid1] uniqueidentifier  NOT NULL default (newid()) 
    select *  from #t 
    drop table #t

    –给查询结果集增加自增长列

    –无主键时:

    select IDENTITY( int ,1,1) as ID,  Name ,[Login],[ Password ]  into #t  from ST_User 
    select *  from #t 

    –有主键时:

    select ( select SUM (1)  from ST_User  where ID<= a.ID)  as myID,*  from ST_User a  order by myID

    –定义表变量

    declare @t  table
    ( 
         id  int not null , 
         msg nvarchar(50)  null
    ) 
    insert into @t  values (1, '1' ) 
    insert into @t  values (2, '2' ) 
    select *  from @t

    循环

    –while循环计算1到100的和

    declare @a  int
    declare @ sum int
    set @a=1 
    set @ sum =0 
    while @a<=100 
    begin
         set @ sum +=@a 
         set @a+=1 
    end
    print @ sum

    条件语句

    --if,else条件分支 
    if(1+1=2) 
    begin
         print  '对'
    end
    else
    begin
         print  '错'
    end

    –when then条件分支

    declare @today  int
    declare @week nvarchar(3) 
    set @today=3 
    set @week= case
         when @today=1  then '星期一'
         when @today=2  then '星期二'
         when @today=3  then '星期三'
         when @today=4  then '星期四'
         when @today=5  then '星期五'
         when @today=6  then '星期六'
         when @today=7  then '星期日'
         else '值错误'
    end
    print @week

    游标

    declare @ID  int
    declare @Oid  int
    declare @Login  varchar (50) 

    –定义一个游标

    declare user_cur  cursor for select ID,Oid,[Login]  from ST_User 

    –打开游标

    open user_cur 
    while @@fetch_status=0 
    begin

    –读取游标

         fetch next from user_cur  into @ID,@Oid,@Login 
         print @ID 
         --print @Login 
    end
    close user_cur

    –摧毁游标

    deallocate user_cur

    触发器

       触发器中的临时表:
      Inserted
      存放进行insert和update 操作后的数据
      Deleted
      存放进行delete 和update操作前的数据
    –创建触发器

    Create trigger User_OnUpdate  
         On ST_User  
         for Update  
    As  
         declare @msg nvarchar(50) 
         --@msg记录修改情况 
         select @msg = N '姓名从“' + Deleted. Name + N '”修改为“' + Inserted. Name +  '”' from Inserted,Deleted

    –插入日志表

         insert into [LOG](MSG) values (@msg) 

    –删除触发器

    drop trigger User_OnUpdate

    存储过程

    –创建带output参数的存储过程

    CREATE PROCEDURE PR_Sum 
         @a  int , 
         @b  int , 
         @ sum int output
    AS
    BEGIN
         set @ sum =@a+@b 
    END

    –创建Return返回值存储过程

    CREATE PROCEDURE PR_Sum2 
         @a  int , 
         @b  int
    AS
    BEGIN
         Return @a+@b 
    END

    –执行存储过程获取output型返回值

    declare @mysum  int
    execute PR_Sum 1,2,@mysum  output
    print @mysum 

    –执行存储过程获取Return型返回值

    declare @mysum2  int
    execute @mysum2= PR_Sum2 1,2 
    print @mysum2

    自定义函数

      函数的分类:
        1)标量值函数
        2)表值函数
            a:内联表值函数
            b:多语句表值函数
        3)系统函数

    –新建标量值函数

    create function FUNC_Sum1 
    ( 
         @a  int , 
         @b  int
    ) 
    returns int
    as
    begin
         return @a+@b 
    end

    –新建内联表值函数

    create function FUNC_UserTab_1 
    ( 
         @myId  int
    ) 
    returns table
    as
    return ( select *  from ST_User  where ID<@myId) 

    –新建多语句表值函数

    create function FUNC_UserTab_2 
    ( 
         @myId  int
    ) 
    returns @t  table
    ( 
         [ID] [ int ]  NOT NULL , 
         [Oid] [ int ]  NOT NULL , 
         [Login] [nvarchar](50)  NOT NULL , 
         [Rtx] [nvarchar](4)  NOT NULL , 
         [ Name ] [nvarchar](5)  NOT NULL , 
         [ Password ] [nvarchar]( max )  NULL , 
         [State] [nvarchar](8)  NOT NULL
    ) 
    as
    begin
         insert into @t  select *  from ST_User  where ID<@myId 
         return
    end

    –调用表值函数

    select *  from dbo.FUNC_UserTab_1(15) 
    --调用标量值函数 
    declare @s  int
    set @s=dbo.FUNC_Sum1(100,50) 
    print @s 

    –删除标量值函数

    drop function FUNC_Sum1

    谈谈自定义函数与存储过程的区别:
    一、自定义函数:
      1. 可以返回表变量
      2. 限制颇多,包括
        不能使用output参数;
        不能用临时表;
        函数内部的操作不能影响到外部环境;
        不能通过select返回结果集;
        不能update,delete,数据库表;
      3. 必须return 一个标量值或表变量
      自定义函数一般用在复用度高,功能简单单一,争对性强的地方。
    二、存储过程
      1. 不能返回表变量
      2. 限制少,可以执行对数据库表的操作,可以返回数据集
      3. 可以return一个标量值,也可以省略return
       存储过程一般用在实现复杂的功能,数据操纵方面。


    SqlServer存储过程–实例
    实例1:只返回单一记录集的存储过程。
      表银行存款表(bankMoney)的内容如下

    IduserIDSexMoney
    001Zhangsan30
    002Wangwu50
    003Zhangsan40

    要求1:查询表bankMoney的内容的存储过程

    create procedure sp_query_bankMoney
    as
    select * from bankMoney
    go
    exec sp_query_bankMoney

    注* 在使用过程中只需要把T-Sql中的SQL语句替换为存储过程名,就可以了很方便吧!
    实例2(向存储过程中传递参数):
    加入一笔记录到表bankMoney,并查询此表中userID= Zhangsan的所有存款的总金额。

    Create proc insert_bank @param1 char(10),@param2 varchar(20),@param3 varchar(20),@param4 int,@param5 int output
    with encryption ---------加密
    as
    insert into bankMoney (id,userID,sex,Money)
    Values(@param1,@param2,@param3, @param4)
    select @param5=sum(Money) from bankMoney where userID='Zhangsan'
    goSQL Server查询分析器中执行该存储过程的方法是:
    declare @total_price int
    exec insert_bank '004','Zhangsan','男',100,@total_price output
    print '总余额为'+convert(varchar,@total_price)
    go

    在这里再啰嗦一下存储过程的3种传回值(方便正在看这个例子的朋友不用再去查看语法内容):

    1. 以Return传回整数
    2. 以output格式传回参数
    3. Recordset

    传回值的区别:
    output和return都可在批次程式中用变量接收,而recordset则传回到执行批次的客户端中。
    实例3:使用带有复杂 SELECT 语句的简单过程
      下面的存储过程从四个表的联接中返回所有作者(提供了姓名)、出版的书籍以及出版社。该存储过程不使用任何参数。

      USE pubs
    IF EXISTS (SELECT name FROM sysobjects
             WHERE name = 'au_info_all' AND type = 'P')
       DROP PROCEDURE au_info_all
    GO
    CREATE PROCEDURE au_info_all
    AS
    SELECT au_lname, au_fname, title, pub_name
       FROM authors a INNER JOIN titleauthor ta
          ON a.au_id = ta.au_id INNER JOIN titles t
          ON t.title_id = ta.title_id INNER JOIN publishers p
          ON t.pub_id = p.pub_id
    GO
      au_info_all 存储过程可以通过以下方法执行:
      EXECUTE au_info_all
    -- Or
    EXEC au_info_all
      -- 如果该过程是批处理中的第一条语句,则可使用:
      au_info_all

    实例4:使用带有参数的简单过程
      

    CREATE PROCEDURE au_info
       @lastname varchar(40),
       @firstname varchar(20)
    AS
    SELECT au_lname, au_fname, title, pub_name
       FROM authors a INNER JOIN titleauthor ta
          ON a.au_id = ta.au_id INNER JOIN titles t
          ON t.title_id = ta.title_id INNER JOIN publishers p
          ON t.pub_id = p.pub_id
       WHERE  au_fname = @firstname
          AND au_lname = @lastname
    GO
      au_info 存储过程可以通过以下方法执行:
      EXECUTE au_info 'Dull', 'Ann'
    -- Or
    EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann'
    -- Or
    EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull'
    -- Or
    EXEC au_info 'Dull', 'Ann'
    -- Or
    EXEC au_info @lastname = 'Dull', @firstname = 'Ann'
    -- Or
    EXEC au_info @firstname = 'Ann', @lastname = 'Dull'
      -- 如果该过程是批处理中的第一条语句,则可使用:
      au_info 'Dull', 'Ann'
    -- Or
    au_info @lastname = 'Dull', @firstname = 'Ann'
    -- Or
    au_info @firstname = 'Ann', @lastname = 'Dull'

    * 实例5:使用带有通配符参数的简单过程*

    CREATE PROCEDURE au_info2
    @lastname varchar(30) = 'D%',
    @firstname varchar(18) = '%'
    AS
    SELECT au_lname, au_fname, title, pub_name
    FROM authors a INNER JOIN titleauthor ta
       ON a.au_id = ta.au_id INNER JOIN titles t
       ON t.title_id = ta.title_id INNER JOIN publishers p
       ON t.pub_id = p.pub_id
    WHERE au_fname LIKE @firstname
       AND au_lname LIKE @lastname
    GO
      au_info2 存储过程可以用多种组合执行。下面只列出了部分组合:
      EXECUTE au_info2
    -- Or
    EXECUTE au_info2 'Wh%'
    -- Or
    EXECUTE au_info2 @firstname = 'A%'
    -- Or
    EXECUTE au_info2 '[CK]ars[OE]n'
    -- Or
    EXECUTE au_info2 'Hunter', 'Sheryl'
    -- Or
    EXECUTE au_info2 'H%', 'S%'
      = 'proc2'

    实例6:if…else
    存储过程,其中@case作为执行update的选择依据,用if…else实现执行时根据传入的参数执行不同的修改.
    –下面是if……else的存储过程:

    if exists (select 1 from sysobjects where name = 'Student' and type ='u' )
    drop table Student
    go
    
    if exists (select 1 from sysobjects where name = 'spUpdateStudent' and type ='p' )
    drop proc spUpdateStudent
    go
    
    create table Student
    (
    fName nvarchar (10),
    fAge 
    smallint ,
    fDiqu varchar (50),
    fTel  int 
    )
    go
    
    insert into Student values ('X.X.Y' , 28, 'Tesing' , 888888)
    go
    
    create proc spUpdateStudent
    (
    @fCase int ,
    @fName nvarchar (10),
    @fAge smallint ,
    @fDiqu varchar (50),
    @fTel  int 
    )
    as 
    update Student
    set fAge = @fAge, -- 传 1,2,3 都要更新 fAge 不需要用 case 
    fDiqu = (case when @fCase = 2 or @fCase = 3 then @fDiqu else fDiqu end ),
    fTel  = (case when @fCase = 3 then @fTel else fTel end )
    where fName = @fName
    select * from Student
    go
    
    -- 只改 Age 
    exec spUpdateStudent
    @fCase = 1,
    @fName = N'X.X.Y' ,
    @fAge = 80,
    @fDiqu = N'Update' ,
    @fTel  = 1010101
    
    -- 改 Age 和 Diqu 
    exec spUpdateStudent
    @fCase = 2,
    @fName = N'X.X.Y' ,
    @fAge = 80,
    @fDiqu = N'Update' ,
    @fTel  = 1010101
    
    -- 全改 
    exec spUpdateStudent
    @fCase = 3,
    @fName = N'X.X.Y' ,
    @fAge = 80,
    @fDiqu = N'Update' ,
    @fTel  = 1010101
    展开全文
  • 关系数据库——视图/存储过程/触发器

    千次阅读 多人点赞 2019-12-02 09:51:43
    创建名为productpricing的储存过程。如果存储过程中需要传递参数,则将他们在括号中列举出来即可。括号必须有。BEGIN和END关键字用来限制存储过程体。上述存储过程体本身是一个简单的select语句。注意这里只是创建...
  • 10.pgsql函数和存储过程

    千次阅读 2020-02-17 23:01:13
    pgsql并没有将函数和存储过程做区分,通俗一点说,就是pgsql的函数也就是存储过程存储过程就是函数。psql官方网站统称为PL/pgSQL - SQL 过程语言。 2.函数的语法 CREATE [OR REPLACE] FUNCTION function_name ...
  • 存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,它存储在数据库中,一次编译后永久有效,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储...
  • 本篇博客主要围绕 基本语法、简单代码示例,以及实战上常用的示例,这三点来讲解,学习循环看这一篇足够了。 目录LOOP语句基本语法:代码示例:实战示例:WHILE语句基本语法:代码示例:FOR语句基本语法:代码示例:...
  • Sql Server 存储过程实例讲解

    千次阅读 2014-01-20 17:25:14
    这并不意味着存储过程没有任何缺点,毕竟任何东西都不能滥用,但是可以看出来,存储过程的确是T-SQL编程的核心内容,是最重要的部分。而它本身又是如此地容易掌握,相信你现在的心情不错吧? 【在存储过程中使用...
  • PL/SQL Developer中调试oracle的存储过程

    万次阅读 2014-10-30 12:11:07
    唉,真土,以前用Toad,一直用dbms_output.put_line调试存储过程,只觉得不方便,用上PL/SQL Developer后,习惯性的还是用这个方法,人都是有惰性的。今天分析存储过程生成的数据,实在觉得不便,网上搜了一下,PL/...
  • oracle存储过程的调试

    2012-09-03 12:42:37
    1.打开PL/SQL Developer 如果在机器上安装了PL/SQL Developer的话,打开...找到需要调试的存储过程所在的包(Package bodies),如PACK_ACTIVITY,点击右键,在弹出菜单中选择[查看],得到包中的所有存储过程和他们的
  • pb使用RPCFUNC调用存储过程

    千次阅读 2016-09-26 22:56:02
    归纳起来PB6.0中调用Oracle存储过程有以下四种方法。  方法一:以关键字RPCFUNC声明存储过程;  方法二:以DECLARE PROCEDURE语句声明存储过程;  方法三:以动态SQL语句实现;  方法四:调用函数PBDBMS.Put_...
  • PLsql调试 存储过程

    千次阅读 2017-10-18 14:41:55
    使用PL/SQL Developer的调试功能,对于编写复杂的存储过程,包,funtion...非常有帮助,对执行存储过程形成的结果进行分析时也很有用处,学习之后,果然、当真方便,现将相关步骤摘录如下: 1、首先是编写存储过程...
  • 数据库中对存储过程的理解

    千次阅读 2018-04-16 22:04:31
    1.存储过程的概念存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集。经编译后存储在数据库中。存储过程是数据库中的一个重要对象,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)...
  • 存储过程(学习笔记)

    千次阅读 2014-04-09 17:38:41
    一、 存储过程的概念 存储过程的概念 存储过程的概念 存储过程的概念 存储过程的概念 存储过程的概念 存储过程的概念 存储过程的概念
  • mysql触发器与存储过程

    千次阅读 2016-05-04 20:26:20
    此时,在新打开一个终端,来查看当前张三的余额,可以看到由于当前的事务还没有执行完成,所以此时查询到的张三的余额还是没有变化,上面的查询之所以会有变化,并不是真正的数据查询,而是从事务日志中进行查询所得...
  • 为何要这么使用呢? 1、可以防止项目在开发时误删程序运行必备数据, 2、特方便,简洁(只需要把写好的数据库脚本写成文件放入工程目录... # 这句话的意思是(初始化模式(springboot2.0),其中有三个,always为...
  • Oracle通过Job调用存储过程

    万次阅读 2015-05-20 17:13:53
    通过Oracle的job调用存储过程来实现一些定时任务,在此操作过程中的一些注意事项做一记录供大家参考
  • 这道题没有一个标准的答案,它涉及很多的知识点,面试官会通过这道题了解你对哪一方面的知识比较擅长,然后继续追问看看你的掌握程度。当然本篇博客的分析也只是我的一些个人理解,从前端的角度出发,具体地分析从...
  • 视图、索引、存储过程优缺点

    千次阅读 2016-01-28 11:12:38
    SQL存储过程的概念 优点及语法整理在学习程序过程之前,先了解下什么是存储过程?为什么要用存储过程,他有那些优点定义:将常用的或很复杂的工作,预先用SQL语句写好并用一个指定的名称存储起来, 那么以后要叫...
  • 另外,数值0比较特殊,它只包含一个数值最高位表示位80,没有数据部分。  正数的最高位表示位大于80,负数的最高位表示位小于80。其中一个正数的最高位是个位的话,则最高位表示位为C1,百位、万位依次为C2、C3...
  • 使用存储过程时报错:在应使用条件的上下文中指定了非布尔类型的表达式!
  • 本文总结声音的存储,对如下问题做出回答。 1.声音有哪些属性? 2.声音转换成电脑文件(wav/mp3)的过程是怎么样的? 3.PCM格式的音频文件和WAV格式文件的内容有什么差异? 声音是物体振动产生的声波。声音...
  • Oracle 存储过程异常处理

    万次阅读 2011-07-05 15:29:45
    1、异常的优点    ... 如果没有异常,在程序中,应当检查每个命令的成功还是失败,如   BEGIN   SELECT ...   -- check for ’no data found’ error   SELECT ...   -- check f
  • 数据缺失的4种处理方法

    千次阅读 2021-06-24 09:38:37
    机械原因是由于机械原因导致的数据收集或保存的失败造成的数据缺失,比如数据存储的失败,存储器损坏,机械故障导致某段时间数据未能收集(对于定时数据采集而言)。 人为原因是由于人的主观失误、历史局限或有意...
  • 文件在上传过程前,安全组件计算散列失败是什么意思需要把文件上传页面的网址添加到可信站点,具体操作步骤如下: 天黑路会滑,社会太复杂,还有什么东西比人心更可怕,首先打开系统的internet选项卡。 证明小编足够...
  • sql 索引 存储过程

    千次阅读 2010-07-28 16:56:00
    存储过程 优点: 1.由于应用程序随着时间推移会不断更改,增删功能,T-SQL过程代码会变得更复杂,StoredProcedure为封装此代码提供了一个替换位置。 2.执行计划(存储过程在首次运行时将被编译,这将产生一个执行...
  • Java类加载及对象创建过程详解

    千次阅读 多人点赞 2019-06-27 08:00:00
    将这个字节流所代表的静态存储结构转化为方法区域的运行时数据结构。 在Java堆中生成一个代表这个类的java.lang.Class对象,作为方法区域数据的访问入口。 验证 验证阶段作用是保证Class...
  • Oracle 的存储过程和定时任务

    千次阅读 2010-06-08 14:26:00
    存储过程、定时任务

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 255,345
精华内容 102,138
关键字:

存储过程没有足够的值