第13章 数据库对象 13.1 存储过程* n7 e2 O! p, w" e
13.1.1 存储过程基本概念0 D" |; S9 M. ~7 h
1、 在关系数据库中,SQL语言是应用程序和数据库管理之间的主要编程接口;
& [1 M1 o/ d6 J+ c' O 2、 使用SQL语言编写代码时,可用两种方法存储和执行代码:
! h# G$ U2 M% k" y6 |+ J' z3 O (1) 在客户端存储代码,并创建向数据库管理系统发送SQL命令,并处理返回结果的应用程序;+ \, {- E8 E/ x2 D' }
(2) 将这些发送的SQL语句存储在数据库管理系统中,这些存储在数据库管理系统中的SQL语句就是存储过程,然后再创建执行存储过程并处理返回结果的应用程序。
2 p' Y7 X6 L( M2 @ 3、 使用存储过程的好处:3 G* J0 }' m7 c$ ^8 I x
(1) 模块化程序设计:只需创建一次存储过程并将其存储在数据库中,以后就可以在应用程序中多次调用存储过程;
H$ U" h- y9 v, h$ F/ A% M (2) 提高性能:系统在创建存储过程时对其进行分析和优化,并在第一次执行时进行语法检查和编译,编译好的代码放入内存中,以后再执行此存储过程时,只需直接执行内存中的代码,从而提高代码的执行效率;
4 M$ D" M' }6 y$ q, R (3) 减少网络流量:一个需要数百行SQL代码完成的操作现在只需一条执行存储过程的代码即可实现,因此,不再需要在网络中发送这些多语句; P5 x6 c Y7 w
(4) 可作为安全机制使用:
) G/ B: h' Q- v. D z2 V 13.1.2 创建和执行存储过程
$ y3 N8 w2 u' J$ H. n: L 1、 创建存储过程的SQL语句为:CREATE PROCEDURE,语法格式为:% p3 l) V3 m. H# A$ e0 Q
CREATE PROCEDURE 存储过程名: E, S# D- ]* |0 V/ }
[{@ 参数名 数据类型}[=default][OUTPUT]
- S1 ~ \$ h9 x: T. S ][,…n]7 T8 F- a# P- _' v! p8 r: z
AS
4 [" g- ^- v& Y6 t7 g& x! R" y; X. C SQL语句[…n]+ D. ~, T. r' z* T* _, h* d' D$ ?
其中:
" X1 C* P" [/ `8 l) c1 a (1) default:表示参数的默认值。如果定义了默认值,则在执行存储过程时,可以不必指定该参数的值,默认值必须是常量或NULL;
5 w* C* n. H5 Q (2) OUTPUT:表明参数是输出参数,该选项的值可以返回给存储过程的调用者。
/ k' }/ H$ l5 `8 x' Z 2、执行存储过程的SQL语句是EXECUTE,语法格式:* S5 y4 K+ z( U- W, f2 z
CREATE EXECUTE 存储过程名 [实参[,OUTPUT][,…n]]
* @/ Q: A; p5 ]3 E8 a 1、 执行有多个输入参数的存储过程时,参数的传递方式有两种:
, D9 h0 Q! a/ {; Y9 ]$ p (1) 按参数位置传递值:指执行存储过程的EXEC语句中的实参的排列顺序必须与定义存储过程时定义的参数的顺序一致;9 _" D0 C; Y; r: M, l4 i( l
(2) 按参数名传递值:指执行存储过程的EXEC语句中要指明定义存储过程时指定的参数的名字以及此参数的值,而不关心参数的定义顺序。
; y+ L' d* O$ ~% J+ c; c, ]# O 2、 注意:" l, q/ ?9 [; E. T% o3 q! t% M: ^
(1) 在执行有输出参数的存储过程时,执行语句中的变量名的后边要加上OUTPUT修饰符;0 S5 t4 R8 x, y" \& X# \+ [8 V4 h. `
(2) 在调用有输出参数的存储过程时,与输出参数对应的是一个变量,此变量用于保存输出参数返回的结果;" o( I6 {9 g1 K) B, Y. Y- R% Y
13.2 用户自定义函数
, H% c: j7 |7 L 13.2.1 基本概念8 H1 {; \% G* C
1、 用户定义函数可以扩展数据操作的功能,它在概念上类似于一般的程序设计语言中定义的函数。4 h8 t6 n9 N$ m+ `7 r# x. p
13.2.2 创建和调用标量函数
% {( ?, m8 r) T 标量函数—返回单个数据值的函数;4 ?. @4 \9 _/ `: h! I- ^: L
1、定义标量函数( W9 Z8 W6 a7 D5 G2 Z( x' ~1 h
CREATE FUNCTION [拥有者名.] 函数名
X% Z4 r) W8 V ([{@ 参数名[AS]标量数据类型[=default]}[,…n]]). K3 W, w7 q# w s6 K& D
RETURNS 返回值类型7 D9 x- U: T5 o
[AS]
' `: j- @! g! H" j; }8 S, d9 U" [ BEGIN8 ]& n T; N: f' P' ^
函数体+ k R' B! Z6 l9 x' {+ z$ @
RETURN 标量表达式/ s0 y7 [& I0 x x; a8 U- n
END
7 D; {+ ^9 s q7 L8 |( v+ t (1) 同存储过程一样,函数的参数也可以有默认值。7 K/ \- W7 y3 U
(2) 标量表达式:指定标量函数返回的标量值。) f8 `) U" B, O1 M- E
2、 调用标量函数:
' s2 v! L5 j- O4 X1 X4 \! C, T t 当调用标量函数时,必须提供至少由两部分组成的名称:函数拥有者名和函数名。可在任何允许出现表达式的SQL语句中调用标量函数,只要类型一致;( \3 i; |1 h/ W8 ]# ?
13.2.3 创建和调用内嵌表值函数. w9 W" p: V) }. k
1、 创建内嵌表值函数,其语法为:0 m$ S' a* G3 ?/ t3 M; u
CREATE FUNCTION [拥有者名.] 函数名
: {6 Y. g1 o8 H. i4 G! L ([{@ 参数名[AS]标量数据类型[=default]}[,…n]])
, \3 A4 I4 r) Y5 e RETURNS 返回变量 TABLE
5 r2 x1 ]7 |) e0 q" ]+ {' W [AS]! `. A4 [ V7 s- b: G- y. \7 I
BEGIN
9 J# ~6 H1 {" F) r 函数体
; `9 F# J" a, m+ e RETURN
! \0 D9 Z0 M. W5 }2 L$ J1 J1 |& x% s END
( f8 K/ M3 b0 t( E1 u ::=({列定义|表约束}[,…n])
& ~5 _1 X; h$ G' Q6 g 2、调用多语句表值函数$ S0 A1 B4 H) k8 }8 |
多语句表值函数的返回值是一个表,因此对多语句表值函数的使用也是放在SELECT语句的FROM子句中。
. z {0 d* c$ y! N% E8 v' N. i 13.3 触发器
$ I* P/ O5 W* y9 ^4 D( z 13.3.1 触发器基本概念/ i2 T+ |1 z% [6 X) U7 ]
1、 触发器是一种特殊的存储过程,其特殊性在于它不需要由用户来调用,而是当用户对表中的数据进行UPDATE、INSERT或DELETE操作时自动触发执行;
1 f- f% ~3 K/ I ~ 2、 触发器常用于下列场合:: C- A. j0 _+ e g& p3 h; F6 t
(1) 完成比CHECK的约束更复杂的数据约束;% H" O" e3 M1 |) }5 u9 k j5 w |
(2) 为保证数据库性能而维护的非规范化数据;
! y- _) g" D1 x4 s7 {. S# ]6 t (3) 实现复杂的业务规则,可使业务的处理任务自动进行。
- L& v" b8 t& e9 f& a5 w- D 13.3.2 创建触发器' v4 n3 F2 |4 x# }
1、 创建触发器的语句:CREATE TRIGGER,语法为:
( s7 Z: O; e3 L! F" ~: n, G' ?$ b" j- G CREATE TRIGGER 触发器名称
% O. y# |0 e1 b5 [# K8 v2 @1 M ON {表名| 视图名}
9 A2 W( U' O4 [5 B# ` [WITH ENCRYPTION]
- y% e3 P, |* s. i* @, W {FOR |AFTER|INSTEAD OF}{[INSERT][,][DELETE][,][UPDATE]}
& \% J0 {. q' b$ X3 p, k6 N; ~ AS
: l& d. D7 n- t! F" u$ Q& J [{IF UPDATE(column)…}]
P3 ?8 S! m7 S" i+ ~' M/ O SQL语句; X# l7 N5 W$ I. Y2 o
(1) 触发器名称在数据库中必须是惟一的;4 G/ ], M7 \* n, `& V6 b( U1 j
(2) ON子句用于指定在其上执行触发器的表;
0 a9 b/ r3 ~# a' S _ (3) AFTER:指定触发器只有在引发的SQL语句中指定的操作都已成功执行,并且所有的约束检查也成功完成后,才执行此触发器,这种触发器称为后触发型触发器;& c$ u3 y8 r7 b; V3 j
(4) FOR:作用同AFTER;
6 t* F. {4 w1 `# O7 V; a8 H6 y2 ^
" W |, F: M* g (5) INSTEAD OF:指定执行触发器而不是执行引发触发器执行的SQL语句,从而替代触发语句的操作,这种触发器称为前触发型触发器; |