存储过程入门与提高

 

什么是 存储过程 呢？

定义：

将常用的或很复杂的工作，预先用 SQL 语句写好并用一个指定的名称存储起来 , 那么以后要叫 数据库 提供与已定义好的 存储过程 的功能相同的服务时 , 只需调用 execute, 即可自动完成命令。

讲到这里 , 可能有人要问：这么说 存储过程 就是一堆 SQL 语句而已啊？

那么 存储过程 与一般的 SQL 语句有什么区别呢 ?

存储过程 的优点：

1.存储过程 只在创造时进行编译，以后每次执行 存储过程 都不需再重新编译，而一般 SQL 语句每执行一次就编译一次 , 所以使用 存储过程 可提高 数据库 执行速度。

2. 当对 数据库 进行复杂操作时 ( 如对多个表进行 Update,Insert,Query,Delete 时），可将此复杂操作用 存储过程 封装起来与 数据库 提供的事务处理结合一起使用。

3.存储过程 可以重复使用 , 可减少 数据库 开发人员的工作量

4. 安全性高 , 可设定只有某此用户才具有对指定 存储过程 的使用权

存储过程 的种类：

1. 系统 存储过程 ：以 sp_ 开头 , 用来进行系统的各项设定 . 取得信息 . 相关管理工作 ,

如 sp_help 就是取得指定对象的相关信息

2. 扩展 存储过程   以 XP_ 开头 , 用来调用 操作系统 提供的功能

exec master..xp_cmdshell 'ping 10.8.16.1'

3. 用户自定义的 存储过程, 这是我们所指的 存储过程

常用格式

Create procedure procedue_name

[@parameter data_type][output]

[with]{recompile|encryption}

as

sql_statement

解释 :

output ：表示此参数是可传回的

with {recompile|encryption}

recompile: 表示每次执行此 存储过程 时都重新编译一次

encryption: 所创建的 存储过程 的内容会被加密

如 :

表 book 的内容如下

编号     书名                            价格

001      C 语言入门                    $30

002      PowerBuilder 报表开发   $52

实例 1: 查询表 Book 的内容的 存储过程

create proc query_book

as

select * from book

go

exec query_book

实例 2: 加入一笔记录到表 book, 并查询此表中所有书籍的总金额

Create proc insert_book

@param1 char(10),@param2 varchar(20),@param3 money,@param4 money output

with encryption  --------- 加密

as

insert book( 编号 , 书名，价格） Values(@param1,@param2,@param3)
   select @param4=sum( 价格 ) from book
  go

执行例子 :
  declare @total_price money
  exec insert_book '003','Delphi 控件 开发指南 ',$100,@total_price
  print ' 总金额为 '+convert(varchar,@total_price)
  go
存储过程 的 3 种传回值 :
   1. 以 Return 传回整数
   2. 以 output 格式传回参数
   3.Recordset
传回值的区别 :
       output 和 return 都可在批次程式中用变量接收 , 而 recordset 则传回到执行批次的客户端中

实例 3 ：设有两个表为 Product,Order, 其表内容如下：
      Product
           产品编号        产品名称     客户订数     
            001             钢笔          30        
            002             毛笔          50        
            003             铅笔          100       
      Order 
           产品编号          客户名      客户订金
            001              南山区       $30
            002              罗湖区       $50
            003              宝安区       $4
请实现按编号为连接条件 , 将两个表连接成一个临时表 , 该表只含编号 . 产品名 . 客户名 . 订金 . 总金额 ,
总金额 = 订金 * 订数 , 临时表放在 存储过程 中

代码如下 :
     Create proc temp_sale
     as
       select a. 产品编号 ,a. 产品名称 ,b. 客户名 ,b. 客户订金 ,a. 客户订数 * b. 客户订金 as 总金额
       into #temptable from Product a inner join Order b on a. 产品编号 =b. 产品编号
    if  @@error=0
       print 'Good'
    else
       print 'Fail'
     go