mssql2005存储过程学习

最新推荐文章于 2024-10-16 17:29:30 发布

wsimei

最新推荐文章于 2024-10-16 17:29:30 发布

阅读量434

点赞数

分类专栏：数据库文章标签：存储 sql server encryption 数据库 go schema

数据库专栏收录该内容

25 篇文章 0 订阅

订阅专栏

好记性不如烂笔头，以备以后查看。

一，首先如前人所说，存储过程的优点有如下几点：

      1，存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译故而提高数据库执行速度。
     2，当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
    3，存储过程可以重复使用,可减少数据库开发人员的工作量
    4，安全性高,可设定只有某此用户才具有对指定存储过程的使用权

5，降低网络负荷

二，语法

    CREATE PROC[ EDURE ] [ owner. ] procedure_name [ ; number ]
      [ { @parameter data_type }
          [ VARYING ] [ = default ] [ OUTPUT ]
       ] [ ,...n ]

[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

[ FOR REPLICATION ]

AS sql_statement [ ...n ]

部分参数：

[；Number]: 用于对同名过程进行分组的可选整数。使用一个 DROP PROCEDURE 语句可将这些分组过程一起删除。

三，简单的存储过程

这里假设有张Students表，则一个可用的存储过程为：

      Create Procedure Q_Students
      AS
      Select * From Students;

即创建了名为Q_Students的存储过程。

在程式中可通过Exec Q_Students來执行。

四，部分代码

    --修改存储过程
    Alter Procedure Q_Students
    As
    Select S_Id as '編號',S_Name as '姓名',S_Sex as '性別',S_Age as '年齡',S_CId as '班別' From Students;
    go
    -- 输入型参数
    Alter procedure Q_Students
    @Q_Name varchar(20)
    AS
    Select * From Students where S_Name=@Q_Name
    go
    Declare @Q_Name varchar(20);
    Set @Q_Name='劉偉';
    -- 输出型参数
    alter procedure Q_Students
    @Q_Id varchar(10), @Pout varchar(20) output
    AS
    Select @Pout=S_Name From Students where S_Id=@Q_Id
    go
    Declare @Q_Id varchar(10)
    Set @Q_Id='1';

    --procedure with Schema
    Create Schema Schema1
    go
    Alter procedure Schema1.Q_Students
    AS
    Select * from Students

五，部分常用的系统存储过程

    --列出SQL Server实例中的数据库
    sp_databases
    --返回SQL Server、数据库网关或基础数据源的特性名和匹配值的列表
    sp_server_info
    --返回当前环境中的存储过程列表
    sp_stored_procedures
    --返回当前环境下可查询的对象的列表（任何可出现在 FROM 子句中的对象）
    sp_tables
    select * from sysobjects
    ---添加或更改SQL Server登录的密码。
   sp_password @new=null,@loginame='sa'
   - -将登录 Victoria 的密码更改为 ok。
   EXEC sp_password NULL, 'ok', 'Victoria'
   --将登录 Victoria 的密码由 ok 改为 coffee。
   EXEC sp_password 'ok', 'coffee'
    --更改配置选项
   use master
    go
    exec sp_configure 'recovery interval','3'
    reconfigure with override
    go
    --查看数据库文件
    sp_helpdb tmp
    use tmp
    go
    sp_helpfile
    go
   --分离数据库
      use master
      go
      sp_detach_db tmp
      go
    - -sp_helpdb tmp          --error
    --go
    --附加数据库
     sp_attach_db tmp,@filename1='E:/DB/tmp_dat.mdf',@filename2='E:/DB/tmp_log.ldf'
     go
     sp_helpdb tmp
     go

例如：

            Create Proc S1 ;1
            AS
                   Select * From Students

            Go
            Create Proc S1 ;2
            As
                   Select * From Clazz

创建完毕了两个存储过程。它们在同一个组S1里，如果执行Exec S1 则存储过程默认执行 Exec S1 ;1 。如果我们想得到所有据点信息则需要执行Exec S1 ;2。当我们要删除存储过程的时候，只能执行Drop Exec S1 则该组内所有的存储过程被删除。

五存储过程中使用游标

   --一个标准的游标实例
   Alter procedure Name_Cursor
   AS
   Declare @Temp varchar(20)
   --声明一个游标
   Declare Names Cursor FOR Select S_Name From Students
   --打开游标
   open Names
   --从游标中取数据并存储到变量中
   fetch NEXT from Names Into @Temp
   --如果查询成功则循环执行
   while @@Fetch_Status =0
   Begin
   print('姓名：'+@Temp);
   --取下一条数据
   fetch NEXT From Names Into @Temp
   end
   --关闭游标
   close Names
   --释放游标
   Deallocate Names