专题 存储过程.ppt

1、专题 存储过程,本章学习目标 熟练掌握存储过程的概念和特点 熟练掌握存储过程的创建和执行,存储过程概述,存储过程是SQL语句和控制流语句的预编译集合，以一个名称存储并作为一个单元处理，应用程序通过调用的方法执行存储过程。 存储过程可以将多条T-SQL语句封装到一个存储过程里面，内容清晰、结构简单且易于理解。,存储过程概述,存储过程由参数、编程语句和返回值组成。 可以通过输入参数向存储过程中传递参数值，也可以通过输出参数向存储过程的调用者传递多个输出值。 编程语句可以是T-SQL的控制语句、表达式、访问数据库的语句，也可以调用其他的存储过程 存储过程只能有一个返回值，通常用于表示调用存储过程的结

2、果是成功还是失败。,创建和执行存储过程,创建存储过程的两种方式： CREATE PROCEDURE语句创建存储过程 使用模板创建存储过程,创建和执行存储过程,执行存储过程的两种方式： EXECUTE语句执行存储过程 使用SQL Server Management Studio执行存储过程,使用T-SQL语句创建和执行存储过程,创建存储过程的语句一般格式是：,CREATE PROCEDURE 存储过程名 参数名 数据类型=default OUTPUT ,n WITH ENCRYPTION AS SQL语句,说明： Default：为参数指定的默认值。 OUTPUT：指示参数是输出参数。此选项的值

3、可以返回给调用EXECUTE的语句。 WITH ENCRYPTION:指示SQL SERVER将CREATE PROCEDURE语句的原始文本转换为模糊格式。,使用T-SQL语句创建和执行存储过程,执行存储过程的语句一般格式是：,EXECUTE 存储过程名 参数名= 参数值 |变量名 OUTPUT | DEFAULT ,n,说明： 参数名：是存储过程的参数名。 参数值：传递给存储过程的参数值。 变量名:用来存储参数或返回参数的变量。 OUTPUT:指定存储过程返回一个参数 DEFAULT：使用存储过程定义时提供的参数默认值。,【例1】创建简单存储过程：查询“工商银行北京A支行”的贷款情况，要求

4、列出法人名称、贷款日期和贷款金额。,举例,CREATE PROCEDURE P_Loan AS SELECT Ename,Ldate,Lamount FROM BankT B JOIN LoanT L ON B.Bno=L.Bno JOIN LegalEntityT LE ON LE.Eno=L.Eno WHERE Bname=工商银行北京A支行,【例2】执行在例1中创建的存储过程P_Loan。,举例,EXECUTE P_Loan,【例3】创建带输入参数的存储过程：查询某家指定银行的贷款情况，要求列出法人名称、贷款日期和贷款金额。,举例,CREATE PROC P_In_Loan BankNa

5、me nvarchar(10) AS SELECT Ename,Ldate,Lamount FROM BankT B JOIN LoanT L ON B.Bno=L.Bno JOIN LegalEntityT LE ON LE.Eno=L.Eno WHERE Bname=BankName,【例4】创建带输入参数和默认值的存储过程：查询某家指定银行（银行名称的默认值为：工商银行北京分行）的贷款情况，要求列出法人名称、贷款日期和贷款金额。,举例,CREATE PROC P_InDe_Loan BankName nvarchar(10)=工商银行北京分行 AS SELECT Ename,Ldate,

6、Lamount FROM BankT B JOIN LoanT L ON B.Bno=L.Bno JOIN LegalEntityT LE ON LE.Eno=L.Eno WHERE Bname=BankName,【例4】创建带多个输入参数的存储过程：查询某个指定法人在某家银行的贷款情况，要求列出法人名称、贷款日期和贷款金额。,举例,CREATE PROC P_MulIn_Loan1 LEName nvarchar(15), BankName nvarchar(10) AS SELECT Ename,Ldate,Lamount FROM BankT B JOIN LoanT L ON B.Bn

7、o=L.Bno JOIN LegalEntityT LE ON LE.Eno=L.Eno WHERE (Ename=LEName) AND (Bname=BankName),对例4的改进：,CREATE PROC P_MulIn_Loan1 LEName nvarchar(15), BankName nvarchar(10) AS IF LEName NOT IN (SELECT Ename FROM LegalEntityT) PRINT 该法人不存在！ ELSE IF BankName NOT IN (SELECT Bname FROM BankT) PRINT该银行不存在 ELSE SE

8、LECT Ename,Ldate,Lamount FROM BankT B JOIN LoanT L ON B.Bno=L.Bno JOIN LegalEntityT LE ON LE.Eno=L.Eno WHERE (Ename=LEName) AND (Bname=BankName),【例5】创建带输入参数和输出参数的存储过程：统计指定城市的银行在指定日期以后的总贷款金额和平均贷款金额，将统计结果作为输出参数。,举例,CREATE PROC P_InOut_Loan city nchar(4), date smalldatetime, sum_amount int OUTPUT, avg_amount int OUTPUT AS SELECT sum_amount=SUM(Lamount),avg_amount=AVG(Lamount) FROM BankT B JOIN LoanT L ON B.Bno=L.Bno WHERE (Bname LIKE %+RTRIM(city)+%) AND (Ldatedate),利用例5的存储过程，查询上海的银行在2005年12月31日之后的贷款情况。代码如下：,举例,declare s int,