sql 存储过程_sql存储过程 - CSDN
精华内容
参与话题
  • 系统存储过程是系统创建的存储过程,目的在于能够方便的从系统表中查询信息或完成与更新数据库表相关的管理任务或其他的系统管理任务。系统存储过程主要存储在master数据库中,以“sp”下划线开头的存储过程。尽管...

    一. 什么是存储过程

    系统存储过程是系统创建的存储过程,目的在于能够方便的从系统表中查询信息或完成与更新数据库表相关的管理任务或其他的系统管理任务。系统存储过程主要存储在master数据库中,以“sp”下划线开头的存储过程。尽管这些系统存储过程在master数据库中,但我们在其他数据库还是可以调用系统存储过程。有一些系统存储过程会在创建新的数据库的时候被自动创建在当前数据库中。

    二. 存储过程运行流程

    这里写图片描述

    存储过程是由一些SQL语句和控制语句组成的被封装起来的过程,它驻留在数据库中,可以被客户应用程序调用,也可以从另一个过程或触发器调用。它的参数可以被传递和返回。与应用程序中的函数过程类似,存储过程可以通过名字来调用,而且它们同样有输入参数和输出参数。

    根据返回值类型的不同,我们可以将存储过程分为三类:

    • 返回记录集的存储过程的执行结果是一个记录集,典型的例子是从数据库中检索出符合某一个或几个条件的记录;
    • 返回数值的存储过程执行完以后返回一个值,例如在数据库中执行一个有返回值的函数或命令;
    • 行为存储过程仅仅是用来实现数据库的某个功能,而没有返回值,例如在数据库中的更新和删除操作。

    个人认为,存储过程说白了就是一堆 SQL 的合并。中间加了点逻辑控制。

    1. 但是存储过程处理比较复杂的业务时比较实用。比如说,

    一个复杂的数据操作。如果你在前台处理的话。可能会涉及到多次数据库连接。但如果你用存储过程的话。就只有一次。从响应时间上来说有优势。

    1. 也就是说存储过程可以给我们带来运行效率提高的好处。

    另外,程序容易出现 BUG 不稳定,而存储过程,只要数据库不出现问题,基本上是不会出现什么问题的。也就是说从安全上讲,使用了存储过程的系统更加稳定。

    那么问题来了,什么时候才可以用存储?对于数据量不是很大以及业务处理不是很复杂的小项目就无需要了么?

    答:错。存储过程不仅仅适用于大型项目,对于中小型项目,使用存储过程也是非常有必要的。其威力和优势主要体现在:

    1. 存储过程只在创造时进行编译,以后每次执行存储过程都不需再重新编译,而一般 SQL 语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
    2. 当对数据库进行复杂操作时(如对多个表进行 Update,Insert,Query,Delete 时),可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。这些操作,如果用程序来完成,就变成了一条条的 SQL 语句,可能要多次连接数据库。而换成存储,只需要连接一次数据库就可以了。
    3. 存储过程可以重复使用,可减少数据库开发人员的工作量。
    4. 安全性高,可设定只有某此用户才具有对指定存储过程的使用权。
    5. 减少网络通信量。调用一个行数不多的存储过程与直接调用SQL语句的网络通信量可能不会有很大的差别,可是如果存储过程包含上百行SQL语句,那么其性能绝对比一条一条的调用SQL语句要高得多。
    6. 执行速度更快。有两个原因:首先,在存储过程创建的时候,数据库已经对其进行了一次解析和优化。其次,存储过程一旦执行,在内存中就会保留一份这个存储过程,这样下次再执行同样的存储过程时,可以从内存中直接调用。
    7. 更强的适应性:由于存储过程对数据库的访问是通过存储过程来进行的,因此数据库开发人员可以在不改动存储过程接口的情况下对数据库进行任何改动,而这些改动不会对应用程序造成影响。
    8. 布式工作:应用程序和数据库的编码工作可以分别独立进行,而不会相互压制。

    存储过程的使用,好像一直是一个争论。

    我不倾向于尽可能使用存储过程,是这么认为的:

    1. 运行速度: 大多数高级的数据库系统都有statement cache的,所以编译sql的花费没什么影响。但是执行存储过程要比直接执行sql花费更多(检查权限等),所以对于很简单的sql,存储过程没有什么优势。
    2. 网络负荷:如果在存储过程中没有多次数据交互,那么实际上网络传输量和直接sql是一样的。
    3. 团队开发:很遗憾,比起成熟的IDE,没有什么很好存储过程的IDE工具来支持,也就是说,这些必须手工完成。
    4. 安全机制:对于传统的C/S结构,连接数据库的用户可以不同,所以安全机制有用;但是在web的三层架构中,数据库用户不是给用户用的,所以基本上,只有一个用户,拥有所有权限(最多还有一个开发用户)。这个时候,安全机制有点多余。
    5. 用户满意:实际上这个只是要将访问数据库的接口统一,是用存储过程,还是EJB,没太大关系,也就是说,在三层结构中,单独设计出一个数据访问层,同样能实现这个目标。
    6. 开发调试:一样由于IDE的问题,存储过程的开发调试要比一般程序困难(老版本DB2还只能用C写存储过程,更是一个灾难)。
    7. 移植性:算了,这个不用提,反正一般的应用总是绑定某个数据库的,不然就无法靠优化数据库访问来提高性能了。
    8. 维护性:的确,存储过程有些时候比程序容易维护,这是因为可以实时更新DB端的存储过程,但是在3层结构下,更新server端的数据访问层一样能实现这个目标,可惜现在很多平台不支持实时更新而已。

    常用系统存储过程有:

    exec sp_databases; --查看数据库
    exec sp_tables;        --查看表
    exec sp_columns student;--查看列
    exec sp_helpIndex student;--查看索引
    exec sp_helpConstraint student;--约束
    exec sp_stored_procedures;
    exec sp_helptext 'sp_stored_procedures';--查看存储过程创建、定义语句
    exec sp_rename student, stuInfo;--修改表、索引、列的名称
    exec sp_renamedb myTempDB, myDB;--更改数据库名称
    exec sp_defaultdb 'master', 'myDB';--更改登录名的默认数据库
    exec sp_helpdb;--数据库帮助,查询数据库信息
    exec sp_helpdb master;

    系统存储过程示例:

    --表重命名
    exec sp_rename 'stu', 'stud';
    select * from stud;
    --列重命名
    exec sp_rename 'stud.name', 'sName', 'column';
    exec sp_help 'stud';
    --重命名索引
    exec sp_rename N'student.idx_cid', N'idx_cidd', N'index';
    exec sp_help 'student';
    
    --查询所有存储过程
    select * from sys.objects where type = 'P';
    select * from sys.objects where type_desc like '%pro%' and name like 'sp%';

    用户自定义存储过程

    create proc | procedure pro_name
        [{@参数数据类型} [=默认值] [output],
         {@参数数据类型} [=默认值] [output],
         ....
        ]
    as
        SQL_statements

    2、 创建不带参数存储过程

    --创建存储过程
    if (exists (select * from sys.objects where name = 'proc_get_student'))
        drop proc proc_get_student
    go
    create proc proc_get_student
    as
        select * from student;
    
    --调用、执行存储过程
    exec proc_get_student;

    3、 修改存储过程

    --修改存储过程
    alter proc proc_get_student
    as
    select * from student;

    4、 带参存储过程

    --带参存储过程
    if (object_id('proc_find_stu', 'P') is not null)
        drop proc proc_find_stu
    go
    create proc proc_find_stu(@startId int, @endId int)
    as
        select * from student where id between @startId and @endId
    go
    
    exec proc_find_stu 2, 4;

    5、 带通配符参数存储过程

    --带通配符参数存储过程
    if (object_id('proc_findStudentByName', 'P') is not null)
        drop proc proc_findStudentByName
    go
    create proc proc_findStudentByName(@name varchar(20) = '%j%', @nextName varchar(20) = '%')
    as
        select * from student where name like @name and name like @nextName;
    go
    
    exec proc_findStudentByName;
    exec proc_findStudentByName '%o%', 't%';

    6、 带输出参数存储过程

    if (object_id('proc_getStudentRecord', 'P') is not null)
        drop proc proc_getStudentRecord
    go
    create proc proc_getStudentRecord(
        @id int, --默认输入参数
        @name varchar(20) out, --输出参数
        @age varchar(20) output--输入输出参数
    )
    as
        select @name = name, @age = age  from student where id = @id and sex = @age;
    go
    
    -- 
    declare @id int,
            @name varchar(20),
            @temp varchar(20);
    set @id = 7; 
    set @temp = 1;
    exec proc_getStudentRecord @id, @name out, @temp output;
    select @name, @temp;
    print @name + '#' + @temp;
    

    7、 不缓存存储过程

    --WITH RECOMPILE 不缓存
    if (object_id('proc_temp', 'P') is not null)
        drop proc proc_temp
    go
    create proc proc_temp
    with recompile
    as
        select * from student;
    go
    
    exec proc_temp;

    8、 加密存储过程

    --加密WITH ENCRYPTION 
    if (object_id('proc_temp_encryption', 'P') is not null)
        drop proc proc_temp_encryption
    go
    create proc proc_temp_encryption
    with encryption
    as
        select * from student;
    go
    
    exec proc_temp_encryption;
    exec sp_helptext 'proc_temp';
    exec sp_helptext 'proc_temp_encryption';
    •  

    9、 带游标参数存储过程

    if (object_id('proc_cursor', 'P') is not null)
        drop proc proc_cursor
    go
    create proc proc_cursor
        @cur cursor varying output
    as
        set @cur = cursor forward_only static for
        select id, name, age from student;
        open @cur;
    go
    --调用
    declare @exec_cur cursor;
    declare @id int,
            @name varchar(20),
            @age int;
    exec proc_cursor @cur = @exec_cur output;--调用存储过程
    fetch next from @exec_cur into @id, @name, @age;
    while (@@fetch_status = 0)
    begin
        fetch next from @exec_cur into @id, @name, @age;
        print 'id: ' + convert(varchar, @id) + ', name: ' + @name + ', age: ' + convert(char, @age);
    end
    close @exec_cur;
    deallocate @exec_cur;--删除游标

    10、 分页存储过程

    ---存储过程、row_number完成分页
    if (object_id('pro_page', 'P') is not null)
        drop proc proc_cursor
    go
    create proc pro_page
        @startIndex int,
        @endIndex int
    as
        select count(*) from product
    ;    
        select * from (
            select row_number() over(order by pid) as rowId, * from product 
        ) temp
        where temp.rowId between @startIndex and @endIndex
    go
    --drop proc pro_page
    exec pro_page 1, 4
    --
    --分页存储过程
    if (object_id('pro_page', 'P') is not null)
        drop proc pro_stu
    go
    create procedure pro_stu(
        @pageIndex int,
        @pageSize int
    )
    as
        declare @startRow int, @endRow int
        set @startRow = (@pageIndex - 1) * @pageSize +1
        set @endRow = @startRow + @pageSize -1
        select * from (
            select *, row_number() over (order by id asc) as number from student 
        ) t
        where t.number between @startRow and @endRow;
    
    exec pro_stu 2, 2;

    Raiserror返回用户定义的错误信息,可以指定严重级别,设置系统变量记录所发生的错误。

    Raiserror({msg_id | msg_str | @local_variable}
      {, severity, state}
      [,argument[,…n]]
      [with option[,…n]]
    )

    # msg_id:在sysmessages系统表中指定的用户定义错误信息

    # msg_str:用户定义的信息,信息最大长度在2047个字符。

    # severity:用户定义与该消息关联的严重级别。当使用msg_id引发使用sp_addmessage创建的用户定义消息时,raiserror上指定严重性将覆盖sp_addmessage中定义的严重性。

    任何用户可以指定0-18直接的严重级别。只有sysadmin固定服务器角色常用或具有alter trace权限的用户才能指定19-25直接的严重级别。19-25之间的安全级别需要使用with log选项。
    
    

    # state:介于1至127直接的任何整数。State默认值是1。

    raiserror('is error', 16, 1);
    select * from sys.messages;
    --使用sysmessages中定义的消息
    raiserror(33003, 16, 1);
    raiserror(33006, 16, 1);

    一.不含参数的存储过程

    1.没有返回值:

    创建语句:

    CREATE PROCEDURE dbo.ProTest
    AS 
        DECLARE @test int
        SET @test = 1

    Go

    执行SQL语句:

    EXEC dbo.ProTest

    消息:命令已成功完成。结果:无

    2.有返回值(使用select):

    创建语句:
    CREATE PROCEDURE dbo.ProTest
    AS
        DECLARE @test INT;
        SET @test = 123;
        SELECT  @test;

    GO

    执行SQL语句:

    EXEC dbo.ProTest

    消息:(1 行受影响)。结果:123(表结构形式)

    3.有返回值(使用return)

    创建语句:

    CREATE PROCEDURE dbo.ProTest

    AS
        DECLARE @test INT;
        SET @test = 123;
        RETURN @test;
    GO

    执行SQL语句:

    DECLARE @test INT;
    EXEC @test = dbo.ProTest;

    SELECT  @test

    消息:(1 行受影响)。结果:123(表结构形式)

    4.查询一个或多个集合(类似执行select)

    创建语句:

    CREATE PROCEDURE dbo.ProTest

    AS
        SELECT  *
        FROM    dbo.Material_SO_PipeOrder;
    GO

    执行SQL语句:

    EXEC dbo.ProTest

    消息:查询出来的条数

    结果:查询结果

    二.含参数的存储过程

    1.没有返回值

    创建语句:

    CREATE PROCEDURE dbo.ProTest

        @OrderNO NVARCHAR(50) ,
        @OrderName NVARCHAR(50) ,
        @RMDSC NVARCHAR(500) = NULL  --表示可为空参数
    AS
        IF ( @OrderNO IS NOT NULL )
            BEGIN    
                INSERT  INTO dbo.Material_SO_PipeOrder
                        ( ID, OrderNO, OrderName, RMDSC )
                VALUES  ( NEWID(), -- ID - uniqueidentifier
                          @OrderNO, -- OrderNO - nvarchar(50)
                          @OrderName, -- OrderName - nvarchar(50)
                          @RMDSC  -- RMDSC - nvarchar(500)
                          );
            END;
    GO

    执行SQL语句:

    EXEC dbo.ProTest @OrderNO = N'单号001', @OrderName = N'名称001', @RMDSC = N'备注'

    (或不写列名"EXEC  dbo.ProTest  N'单号001', N'名称001', N'备注';",但不能混合使用,下同)

     消息:(1 行受影响)。结果:无

    2.有返回值(使用select)

    创建语句:

    CREATE PROCEDURE dbo.ProTest

        @OrderNO NVARCHAR(50) ,
        @OrderName NVARCHAR(50) ,
        @RMDSC NVARCHAR(500)
    AS
        IF ( @OrderNO IS NOT NULL )
            BEGIN    
                INSERT  INTO dbo.Material_SO_PipeOrder
                        ( ID, OrderNO, OrderName, RMDSC )
                VALUES  ( NEWID(), @OrderNO, -- OrderNO - nvarchar(50)
                          @OrderName, -- OrderName - nvarchar(50)
                          @RMDSC  -- RMDSC - nvarchar(500)
                          );
                SELECT 1;
            END;
        ELSE
            SELECT -1;
    GO

    执行SQL语句:

    EXEC  dbo.ProTest @OrderNO = N'单号001', @OrderName = N'名称001', @RMDSC = N'备注';

    消息:

    (1 行受影响)

    (1 行受影响)

    结果:1(表结构形式)

    3.有返回值(使用return)

    创建语句:

    CREATE PROCEDURE dbo.ProTest

        @OrderNO NVARCHAR(50) ,
        @OrderName NVARCHAR(50) ,
        @RMDSC NVARCHAR(500)
    AS
        IF ( @OrderNO IS NOT NULL )
            BEGIN    
                INSERT  INTO dbo.Material_SO_PipeOrder
                        ( ID, OrderNO, OrderName, RMDSC )
                VALUES  ( NEWID(), @OrderNO, -- OrderNO - nvarchar(50)
                          @OrderName, -- OrderName - nvarchar(50)
                          @RMDSC  -- RMDSC - nvarchar(500)
                          );
                RETURN 1;
            END;
        ELSE
            RETURN -1;
    GO

    执行SQL语句:

    DECLARE @test INT;
    EXEC @test = dbo.ProTest @OrderNO = N'单号001', @OrderName = N'名称001', @RMDSC = N'备注';

    SELECT @test

    消息:

    (1 行受影响)

    (1 行受影响)

    结果:1(表结构形式)

    4.带输出参数的存储过程(以没有返回值的为例)

    创建语句:

    CREATE PROCEDURE dbo.ProTest

        @OrderNO NVARCHAR(50) ,
        @OrderName NVARCHAR(50) ,
        @RMDSC NVARCHAR(500) ,
        @ID UNIQUEIDENTIFIER OUTPUT --输出参数要用output标识
    AS
        IF ( @OrderNO IS NOT NULL )
            BEGIN    
                DECLARE @newID UNIQUEIDENTIFIER;
                SET @newID = NEWID();
                INSERT  INTO dbo.Material_SO_PipeOrder
                        ( ID ,
                          OrderNO ,
                          OrderName ,
                          RMDSC
                        )
                VALUES  ( @newID ,
                          @OrderNO , -- OrderNO - nvarchar(50)
                          @OrderName , -- OrderName - nvarchar(50)
                          @RMDSC  -- RMDSC - nvarchar(500)
                        );
                SET @ID = @newID;--可以不赋值
            END;
        ELSE
            SET @ID = NULL;--可以不赋值
    GO

    执行SQL语句:

    DECLARE @IDTest UNIQUEIDENTIFIER;
    EXEC dbo.ProTest @OrderNO = N'单号001', -- nvarchar(50)
        @OrderName = N'名称001', -- nvarchar(50)
        @RMDSC = N'备注', -- nvarchar(500)
        @ID = @IDTest OUTPUT; --如果不加OUTPUT,select结果为NULL
    SELECT  @IDTest;

    消息:

    (1 行受影响)

    (1 行受影响)

    结果:EDD11EF3-CD03-4C95-8B79-B3123B34C292(表结构形式)

    三.总结与注意:

    1.关于存储过程的返回值:

    (1).如果有return,则返回return的结果;

    (2).如果没有return,则返回INT值0(即使存储过程中有select集合,或Insert受影响行等);

    (3).如果带输出参数,则存储过程的返回值同(1),(2);OUTPUT的参数需要select出来;

     

     

    展开全文
  • sql-存储过程

    万次阅读 2019-04-29 16:43:54
    存储过程简称过程,是为了完成某种功能而编写的程序段,相当于C语言中的函数或java中的方法 存储过程存放在数据库的“可编程性”组件中,属于数据库,和表、视图级别相同。存储过程允许用户声明变量,可以调用系统...

    存储过程简称过程,是为了完成某种功能而编写的程序段,相当于C语言中的函数或java中的方法

    存储过程存放在数据库的“可编程性”组件中,属于数据库,和表、视图级别相同。存储过程允许用户声明变量,可以调用系统函数,经过编译后存储在数据库服务器中。存储过程可以接收输入参数,也可以将运行结果带出过程,还可以嵌套调用。

    存储过程有以下4个功能:

    1.减少网络流量

    存储过程直接在服务器端运行,减少了与客户机的交互

    2.增强了代码的重用性和共享性

    3.加快系统运行速度

    4.使用灵活

    存储过程分类

    1.系统存储过程。系统存储过程均以sp_开头

    2.自定义存储过程

    3.扩展存储过程

    建立存储过程的命令格式

    create procedure 存储过程名

    [with encryption]

    [@参数名 类型[= 默认值][output][,..n]]

    as sql 语句

    建立无参数存储过程

    create procedure p1_all

    as

    begin

    select *

    from bookinfo

    where publish='清华大学出版社'

    执行后,可以在ssms管理器中找到这个存储过程

    调用存储过程的时候,只需要在查询窗口输入存储过程名即可

    如果存储过程不是处理的第一条语句,那么必须采用以下的格式:

    execute 存储过程名[参数值]

    例如调用p1_all的命令是

    execute pa_all

    其中execute可以简写为exec

    建立有参数存储过程

    参数包括形参和实参,定义参数时,需要明确规定是输入参数还是输出参数,参数后面加output就是输出参数,没加就是输入参数。

    例:创建存储过程p3_writer,显示bookinfo中指定作者编写的全部图书

    这里指定作者是一个形参,具体指需要在调用这个存储过程时输入。形参的类型还有长度必须与作者这个字段的类型和长度保持一致。假设参数名为editor。

    create proc p3_writer
    @editor varchar(8)
    as
    begin
    	select *
    	from BookInfo
    	where Writer=@editor
    end
    

    查询

    exec p3_writer '胡伏湘'

    查询结果如下

     

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

    万次阅读 多人点赞 2019-09-09 10:02:01
    在数据库编程过程中经常会用到存储过程 , 相比 SQL 语句 , 存储过程更方便 , 快速 , 安全 ; 先将存储过程的相关介绍和使用方法总结如下 ;1. 存储过程的概念存储过程 (Stored Procedure) 是在大型数据库系统中 , 一组...

    在数据库编程过程中经常会用到存储过程 , 相比 SQL 语句 , 存储过程更方便 , 快速 , 安全 ; 先将存储过程的相关介绍和使用方法总结如下 ;

    1. 存储过程的概念

    存储过程 (Stored Procedure) 是在大型数据库系统中 , 一组为了完成特定功能的 SQL 语句集 , 存储在数据库中 , 经过第一次编译后再次调用不需要再次编译 , 用户通过指定存储过程的名字并给出参数 (如果该存储过程带有参数) 来执行它 , 存储过程是数据库中的一个重要对象 ; 存储过程中可以包含 逻辑控制语句数据操纵语句 , 它可以接受参数 , 输出参数 , 返回单个或多个结果集以及返回值 ;


    2. 存储过程的优缺点

    优点 :

    1. 由于应用程序随着时间推移会不断更改 , 增删功能 , SQL 语句会变得更复杂 , 存储过程为封装此类代码提供了一个替换位置 ;
    2. 由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中 , 所以存储过程运行要比单个的 SQL 语句块要快 ;
    3. 由于在调用时只需用提供存储过程名和必要的参数信息 , 所以在一定程度上也可以减少网络流量 , 简单网络负担 ;
    4. 可维护性高 , 更新存储过程通常比更改 , 测试以及重新部署程序集需要较少的时间和精力 ;
    5. 代码精简一致 , 一个存储过程可以用于应用程序代码的不同位置 ;
    6. 增强安全性 :
      • 通过向用户授予对存储过程 (而不是基于表) 的访问权限 , 它们可以提供对特定数据的访问 ;
      • 提高代码安全 , 防止 SQL注入 (但未彻底解决 , 例如将数据操作语言 DML 附加到输入参数) ;
      • SQLParameter 类指定存储过程参数的数据类型 , 作为深层次防御性策略的一部分 , 可以验证用户提供的值类型 (但也不是万无一失 , 还是应该传递至数据库前得到附加验证) ;

    缺点 :

    1. 如果更改范围大到需要对输入存储过程的参数进行更改 , 或者要更改由其返回的数据 , 则仍需要更新程序集中的代码以添加参数 , 等等 ;
    2. 可移植性差 , 由于存储过程将应用程序绑定到 Server , 因此使用存储过程封装业务逻辑将限制应用程序的可移植性 ; 如果应用程序的可移植性在您的环境中非常重要 , 则将业务逻辑封装在不特定于 RDBMS 的中间层中可能是一个更佳的选择 ;

    3. 编写简单存储过程

    创建一个存储过程

    create procedure GetUsers()
    begin 
    	select * from user; 
    end;
    

    调用存储过程

    call GetUsers();
    

    删除存储过程

    drop procedure if exists GetUsers;
    

    4. 带参数的存储过程

    MySql 支持 IN (传递给存储过程) , OUT (从存储过程传出) 和 INOUT (对存储过程传入和传出) 类型的参数 , 存储过程的代码位于 BEGIN 和 END 语句内 , 它们是一系列 SQL 语句 , 用来检索值 , 然后保存到相应的变量 (通过指定INTO关键字) ;

    下面的存储过程接受三个参数 , 分别用于获取用户表的最小 , 平均 , 最大分数 , 每个参数必须具有指定的类型 , 这里使用十进制值(decimal(8,2)) , 关键字 OUT 指出相应的参数用来从存储过程传出

    create procedure GetScores(
    	out minScore decimal(8,2),
    	out avgScore decimal(8,2),
    	out maxScore decimal(8,2)
    )
    begin
    	select min(score) into minScore from user;
    	select avg(score) into avgScore from user;
    	select max(score) into maxScore from user;
    end;
    

    调用此存储过程 , 必须指定3个变量名(所有 MySql 变量都必须以 @ 开始) , 如下所示 :

    call GetScores(@minScore, @avgScore, @maxScore);
    

    该调用并没有任何输出 , 只是把调用的结果赋给了调用时传入的变量 @minScore, @avgScore, @maxScore , 然后即可调用显示该变量的值 :

    select @minScore, @avgScore, @maxScore;
    

    使用 IN 参数 , 输入一个用户 id , 返回该用户的名字 :

    create procedure GetNameByID(
    	in userID int,
    	out userName varchar(200)
    )
    begin
    	select name from user
    	where id = userID
    	into userName;
    end;
    

    调用存储过程 :

    call GetNameByID(1, @userName);
    select @userName;
    

    5. 复杂一点示例

    根据 ID 获取货品的价格 , 并根据参数判断是否折扣 :

    create procedure GetPriceByID(
    	in prodID int,
    	in isDisc boolean,
    	out prodPrice decimal(8,2)
    ) 
    begin
    	declare tmpPrice decimal(8,2);
    	declare prodDiscRate decimal(8,2);
    	set prodDiscRate = 0.88;
    
    	select price from products
    	where id = prodID
    	into tmpPrice;
    
    	if isDisc then
    		select tmpPrice*prodDiscRate into tmpPrice;
    	end if;
    
    	select tmpPrice into prodPrice;
    end;
    

    该存储过程传入三个参数 , 货品 ID , 是否折扣以及返回的价格 , 在存储过程内部 , 定义两个局部变量 tmpPrice 和 prodDiscRate , 把查询出来的结果赋给临时变量 , 再判断是否折扣 , 最后把局部变量的值赋给输出参数 ; 调用如下 :

    call GetPriceByID(1, true, @prodPrice);
    select @prodPrice;
    

    6. DELIMITER

    MySql 的命令行客户机的语句分隔符默认为分号 ; ,而实用程序也是用 ; 作为分隔符,这会使得存储过程的 SQL 出现语法错误,使用 DELIMITER $$ 告诉命令行实用程序将 $$ 作为新的语句结束分隔符,最后再使用 DELIMITER ; 改回来,如下所示:

    DELIMITER $$ 
    create procedure getUsers() begin select * from user; 
    end $$ 
    DELIMITER ;
    
    
    DELIMITER $$
    CREATE PROCEDURE getcount()
    BEGIN
    SELECT
    *
    FROM
    contract_user;
    END $$
    DELIMITER ;
    
    CALL getcount();
    

    个人网站: Github , 欢迎点击给星

    展开全文
  • Oracle/PLSQL存储过程详解

    万次阅读 多人点赞 2018-03-14 17:34:38
    如果新建毫无反应直接文件-新建-程序窗口-空白,新建一个程序窗口: 存储过程创建语法: create [or replace] procedure 存储过程名(param1 in type,param2 out type) as 变量1 类型(值范围); 变量2 类型(值...

    一.在plsql中创建一个存储过程

    打开plsql,右键procedures,新建。
    新建
    如果新建毫无反应
    直接文件-新建-程序窗口-空白,新建一个程序窗口: 
    新建2


    存储过程创建语法:

        create [or replace] procedure 存储过程名(param1 in type,param2 out type)
    as
    变量1 类型(值范围);
    变量2 类型(值范围);
    Begin
        Select count(*) into 变量1 from 表A where列名=param1;
    
        If (判断条件) then
           Select 列名 into 变量2 from 表A where列名=param1;
           Dbms_output。Put_line(‘打印信息’);
        Elsif (判断条件) then
           Dbms_output。Put_line(‘打印信息’);
        Else
           Raise 异常名(NO_DATA_FOUND);
        End if;
    
    Exception
        When others then
           Rollback;
    End;

    二:实例:写存储过程(例子)

    --创建一个名为p_contract_purchase_import的存储过程
    create or replace procedure p_contract_purchase_import(
      --以下写存储过程的外部参数(传入的参数)
      --格式为:参数名  in 参数类型
      --注意,这里的varchar不标注大小
      V_IN_SUBCOMPANYID       in VARCHAR2,  --专业分公司
      V_IN_PURCONTRACTMONEY   in NUMBER,        --采购合同金额                                            
      V_IN_PARTYBNAME         in VARCHAR2,  --供应商名称
      --设置一个返回值
      v_o_ret         out number                --返回结果0:成功;1:失败; 4:查不到供应商; 5:添加关联失败;6:新增采购合同失败
    
    )
    
    --以下写内部参数
    --格式为:参数名称  参数类型
    --注意,这里的varchar需要标注大小
    as
    V_SUPPLIERID            INTEGER;  --供应商编号
    V_PARTYBACCOUNT         VARCHAR2(100);--收款账号
    V_SQLERRM         VARCHAR2(4000);--错误详情
    
    --存储过程开始
    begin
      --为某些变量赋初值 
      --格式为  变量名 := 值
      v_o_ret := 1;   
      V_SUPPLIERID := '';
      V_PARTYBACCOUNT := '';
    
      --写具体的操作语句(sql)
      --if语句
      if(V_IN_PARTYBNAME is not null) then
        begin
          select t.SUPPLIERID,t.PARTYBACCOUNT,t.PARTYBBANK ,t.PARTYBNAME 
            into V_SUPPLIERID,V_PARTYBACCOUNT,V_PARTYBBANK,V_PARTYBNAME
            from T_SUPPLIER t where t.PARTYBNAME=trim(V_IN_PARTYBNAME) and t.SUBCOMPANYID=trim(V_IN_SUBCOMPANYID);
        --抛异常
        exception
          when others then
            v_o_ret := 4 ;  --找不到该供应商
            V_PARTYBNAME := V_IN_PARTYBNAME;
    
             -- 将异常原因写入存储过程日志表
            V_SQLERRM := SQLERRM;
            INSERT INTO T_LOG_DBERR
             (ERRTIME, ERRMODEL, ERRDESC)
            VALUES
              (SYSDATE,
                'PROCEDURES',
                 'p_contract_purchase_import:ret=' || v_o_ret ||','||
            V_SQLERRM);
            COMMIT;
        end ;
      end if;
    
     ······
    
        end ; 
        commit;
        v_o_ret :=0 ;
    
    
       return;
    
    EXCEPTION
      WHEN OTHERS THEN
        ROLLBACK;
        -- 插入异常原因
        V_SQLERRM := SQLERRM;
        INSERT INTO T_LOG_DBERR
          (ERRTIME, ERRMODEL, ERRDESC)
        VALUES
          (SYSDATE,
           'PROCEDURES',
           'p_contract_purchase_import:ret=' || v_o_ret ||','||
           V_SQLERRM);
        COMMIT;
    
    --存储过程结束
    end p_contract_purchase_import;

    1.注意begin 和 end成对 

    2.务必将错误内容打印出来。否则,有的时候,就算调试看出来时哪一个语句的错误,也闹不明白错在哪里

    三.存储过程调试

    1.按F8或是菜单栏第三行第二个执行按键编译存储过程。此时,如果有语法上的明显错误,plsql会给予提示。 
    2.在procedures中找到要调试的存储过程,右键,选测试。注意,要记得勾选添加调试信息啊。 
    开启调试 
    3.打开调试窗口,填写输入参数 
    4.点测试窗口的调试按键(如图中画圈的位置)开始调试 
    填参数并打开调试
    5.逐步调试,如下图: 
    调试
    图中1:点击单步调试,主要就是使用这个来进行调试。执行到的代码会高亮显示,此外,注意图中4的位置,可以在这里输入变量的名字,来查看变量的当前值。 
    图中2/3:分别为跳出单步执行和全部执行,调试时不推荐使用。

    单步调试过程中,如果执行到哪一步直接跳转到了exception结束,那么,就是这一步出来问题,可以记住这个位置,再次调试,通过查看这附近变量的值,以及查看错误日志记录的错误详情,来确认出错的具体原因并进行修改。

    四.在java中调用

    存储过程调试好之后,就可以在java中调用该存储过程。

    logger.info("调用存储过程p_contract_purchase_import");
    //存储过程名称。有多少个传入参数打几个问号(包括v_o_ret)
    String procName="{Call p_contract_purchase_import(?,?,?,?) }";
    DataSource ds = SessionFactoryUtils.getDataSource(this.getHibernateTemplate().getSessionFactory());
                        Connection conn = null;
                        CallableStatement call = null;
                        //ResultSet rs =null;
    
                        try
                        {
                            //创建连接
                            conn = ds.getConnection();
                            call = conn.prepareCall(procName);
                            //传入数据
                            call.setString(1, (importList.get(0)).trim());
                            if(ratio_amount==null) {
                                call.setString(2,null);
                            } else {
                                call.setLong(2, ratio_amount);
                            }
                            call.setString(3, (importList.get(2)).trim());
    
                            //第四个参数是作为返回值存在的
                            call.registerOutParameter(4, Types.BIGINT);
                            //执行存储过程
                            call.executeUpdate();
                            //获取返回的结果
                            ret = call.getInt(17) ;
                            logger.info("ret:"+ret); ;//call.getInt(6));
    
                            try
                            {
                                //关闭连接
                                call.close();
                                call = null ;
                                conn.close();
                                conn = null ;
                            } catch (SQLException e) {
                                // TODO Auto-generated catch block
                            }
    
                        }catch (SQLException e){
                            logger.error("打开存储过程错误:",e);
                        }
                        finally{
                            try {
                                if (call != null) {
                                    call.close();
                                }
                                if (conn != null) {
                                    conn.close();
                                }
                            } catch (SQLException e) {
                                // TODO Auto-generated catch block
                                conn = null;
                            }
                        }

    需要注意的是,call.setLong()不可以传入空值。如果内容有可能为空的话,set之前需要判断是否为空,不为空才能使用setLong()方法,否则,要使用setString方法。


    五:注意事项:

    1. 存储过程参数不带取值范围,in表示传入,out表示输出
    2. 变量带取值范围,后面接分号
    3. 在判断语句前最好先用count(*)函数判断是否存在该条操作记录
    4. 用select … into … 给变量赋值
    5. 在代码中抛异常用 raise+异常名

    已命名的异常:

    命名的系统异常产生原因
    ACCESS_INTO_NULL未定义对象
    CASE_NOT_FOUNDCASE 中若未包含相应的 WHEN ,并且没有设置ELSE 时
    COLLECTION_IS_NULL集合元素未初始化
    CURSER_ALREADY_OPEN游标已经打开
    DUP_VAL_ON_INDEX唯一索引对应的列上有重复的值
    INVALID_CURSOR在不合法的游标上进行操作
    INVALID_NUMBER内嵌的 SQL 语句不能将字符转换为数字
    NO_DATA_FOUND使用 select into 未返回行,或应用索引表未初始化的
    TOO_MANY_ROWS执行 select into 时,结果集超过一行
    ZERO_DIVIDE除数为 0
    SUBSCRIPT_BEYOND_COUNT元素下标超过嵌套表或 VARRAY 的最大值
    SUBSCRIPT_OUTSIDE_LIMIT使用嵌套表或 VARRAY 时,将下标指定为负数
    VALUE_ERROR赋值时,变量长度不足以容纳实际数据
    LOGIN_DENIEDPL/SQL 应用程序连接到 oracle 数据库时,提供了不正确的用户名或密码
    NOT_LOGGED_ONPL/SQL 应用程序在没有连接 oralce 数据库的情况下访问数据
    PROGRAM_ERRORPL/SQL 内部问题,可能需要重装数据字典& pl./SQL系统包
    ROWTYPE_MISMATCH宿主游标变量与 PL/SQL 游标变量的返回类型不兼容
    SELF_IS_NULL使用对象类型时,在 null 对象上调用对象方法
    STORAGE_ERROR运行 PL/SQL 时,超出内存空间
    SYS_INVALID_ID无效的 ROWID 字符串
    TIMEOUT_ON_RESOURCEOracle 在等待资源时超时

    六:基本语法

    1. 基本结构

    CREATE OR REPLACE PROCEDURE 存储过程名字
    (
        参数1 IN NUMBER,
        参数2 IN NUMBER
    ) IS
    变量1 INTEGER :=0;
    变量2 DATE;
    BEGIN
        --执行体
    END 存储过程名字;

    2. SELECT INTO STATEMENT

    将select查询的结果存入到变量中,可以同时将多个列存储多个变量中,必须有一条记录,否则抛出异常(如果没有记录抛出NO_DATA_FOUND) 
    例子:

      BEGIN
      SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
          xxxx;
      END;

    3. IF 判断

     IF V_TEST=1 THEN
        BEGIN 
           do something
        END;
      END IF;

    4. while 循环

      WHILE V_TEST=1 LOOP
      BEGIN
        XXXX
      END;
      END LOOP;

    5. 变量赋值

     V_TEST := 123;

    6. 用for in 使用cursor

      IS
      CURSOR cur IS SELECT * FROM xxx;
      BEGIN
     FOR cur_result in cur LOOP
      BEGIN
       V_SUM :=cur_result.列名1+cur_result.列名2
      END;
     END LOOP;
      END;

    7. 带参数的cursor

      CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;
      OPEN C_USER(变量值);
      LOOP
     FETCH C_USER INTO V_NAME;
     EXIT FETCH C_USER%NOTFOUND;
        do something
      END LOOP;
      CLOSE C_USER;

    8. 用pl/sql developer debug

    连接数据库后建立一个Test WINDOW,在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试

    八:关于oracle存储过程的若干问题备忘

    1.在oracle中,数据表别名不能加as,如:

    select a.appname from appinfo a;-- 正确
    select a.appname from appinfo as a;-- 错误

    也许,是怕和oracle中的存储过程中的关键字as冲突的问题吧

    2.在存储过程中,select某一字段时,后面必须紧跟into,如果select整个记录,利用游标的话就另当别论了。

    select af.keynode into kn from APPFOUNDATION af 
       where af.appid=aid and af.foundationid=fid;-- 有into,正确编译
    select af.keynode from APPFOUNDATION af 
     where af.appid=aid and af.foundationid=fid;-- 没有into,编译报错,提示:Compilation 
    
    Error: PLS-00428: an INTO clause is expected in this SELECT statement

    3.在利用select…into…语法时,必须先确保数据库中有该条记录,否则会报出”no data found”异常。

    可以在该语法之前,先利用select count(*) from 查看数据库中是否存在该记录,如果存在,再利用select…into…

    4.在存储过程中,别名不能和字段名称相同,否则虽然编译可以通过,但在运行阶段会报错

     --正确
    select keynode into kn from APPFOUNDATION where appid=aid and foundationid=fid;
    
    --错误
    select af.keynode into kn from APPFOUNDATION af 
     where af.appid=appid and af.foundationid=foundationid;
    -- 运行阶段报错,提示ORA-01422:exact fetch returns more than requested number of rows

    5.在存储过程中,关于出现null的问题

    假设有一个表A,定义如下:

    create table A(
    id varchar2(50) primary key not null,
    vcount number(8) not null,
    bid varchar2(50) not null -- 外键 
    );

    如果在存储过程中,使用如下语句:

    select sum(vcount) into fcount from A where bid='xxxxxx';

    如果A表中不存在bid=”xxxxxx”的记录,则fcount=null(即使fcount定义时设置了默认值,如:fcount number(8):=0依然无效,fcount还是会变成null),这样以后使用fcount时就可能有问题,所以在这里最好先判断一下:

    if fcount is null then
        fcount:=0;
    end if;

    这样就一切ok了。


    展开全文
  • 简单sql存储过程实例、储过程实战

    千次阅读 2019-01-06 15:18:36
    实例1:只返回单一记录集的存储过程。 银行存款表(bankMoney)的内容如下 Id userID Sex Money 001 Zhangsan 男 30 ...
  • sql server 创建存储过程

    万次阅读 多人点赞 2018-03-01 14:58:53
    右键单击“存储过程”,再单击“新建存储过程”将会创建存储过程模板:让我困惑的 <Procedure_Name, sysname, ProcedureName> 有什么用?搜遍全网 也没答案。。。。上图其实是一个存储过程...
  • Mysql_sql存储过程

    千次阅读 2019-02-19 15:26:23
    #存储过程  存储过程和函数:类似于java中的方法  好处:  1.提高代码的重用性  2.简化操作  #存储过程 procedure  含义:一组预先编译好的SQL语句的集合,理解成批处理语句  1.提高代码的重用性  ...
  • MySQL的存储过程原来还可以这样玩?(还不收藏)

    千次阅读 多人点赞 2020-08-29 23:34:12
           ...存储过程是为了完成特定功能的SQL语句集,经编译创建并保存在数据库中,用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。 存储过程思想上.
  • SQL存储过程实例详解

    万次阅读 多人点赞 2014-09-02 00:08:08
    本文用3个题目,从建立数据库到创建存储过程,详细讲解数据库的功能。
  • Sql Server中用Print命令轻松调试【存储过程】的技巧或经验! 经常为存储过程的调试犯愁,通过不断的调试,终于找到一个简单的方法: 1、在存储过程中,用【print】输出,来判断错误问题点。 2、关键是这一步,...
  • print @变量 是以文本的方式在消息栏显示。 select @变量 是以表的形式在结果栏显示。 PROCEDURE [dbo].[...] ( ... ) AS BEGIN SET NOCOUNT ON DECLARE @access BIT IF EXISTS(...) ... SET @acc...
  • SqlServer 存储过程带输入和输出参数

    千次阅读 2019-01-10 17:37:40
    从student表中选出ID为'00128'的学生的name use university_database go create procedure Get_instructor_name2 @Set_ID varchar(10), @Get_Name varchar(20) output as ...select @Get_Name=name ...
  • sqlserver找不到存储过程

    千次阅读 2018-12-02 20:45:20
    在敲组合查询的时候要用到存储过程,而我在敲组合查询的过程中却遇到一个问题: 这就让我很纳闷了,难道是因为我的存储过程的名字写错了?还是存储过程里的sql语句有错误?我仔仔细细检查了一遍,没有错啊。就在...
  • sql sqlserver存储过程中,如果用来判断该记录是否存在时。如果用if exitst(select * from table )如果记录多的话会影响查询速度,可以转换为 if exitst(select count(1) from table)这样的话当查询只要有一条符合...
  • Oracle中sql语句查看存储过程内容

    千次阅读 2016-07-13 17:18:19
    如果没有plsql工具,可以用sql语句实现查看某一个存储过程的内容 select text from user_source where name=upper('swap_drb') order by line;
  • 删除存储过程

    万次阅读 2018-06-23 14:56:57
    1.使用SQL Server管理平台删除存储过程操作步骤如下:(1)打开SQL Server管理平台,展开结点”对象资源管理器“→”数据库服务器“→”可编程性“→”存储过程“,选择要删除的存储过程,右击,在弹出的快捷菜单中...
  • 创建存储过程: [c-sharp] view plain copy ALTER PROCEDURE [dbo].[GetCustomers]  (@rowcount INT OUTPUT)  AS   SELECT [CustomerID]   ,[CompanyName]   ,[ContactNam
  • 在实际使用存储过程是,有时我们希望先判断存储过程的返回结果集是否有记录,然后走不同的业务逻辑,这是就需要在SQL语句中直接读取到存储过程的返回结果集,方式如下: 先按照存储过程结果集定义一个变量 ...
  • SQL SERVER存储过程,参数默认值设置

    万次阅读 2014-07-12 23:51:56
    Example1: USE AdventureWorks2008R2; GO IF OBJECT_ID('Sales.uspGetSalesYTD', 'P') IS NOT NULL  DROP PROCEDURE Sales.uspGetSalesYTD;...CREATE PROCEDURE Sales.uspGetSalesYTD ...@SalesPerson nvarc
  • SqlServer 存储过程 if else 两层判断 语句拼接
1 2 3 4 5 ... 20
收藏数 563,698
精华内容 225,479
关键字:

sql 存储过程