利用存储过程和触发器来管理数据库

最新推荐文章于 2024-10-10 16:39:04 发布

lfywy

最新推荐文章于 2024-10-10 16:39:04 发布

阅读量687

点赞数

分类专栏： SQL 文章标签：存储 sql server table encryption database 数据库

本文链接：https://blog.csdn.net/lfywy/article/details/4510593

版权

SQL 专栏收录该内容

27 篇文章 0 订阅

订阅专栏

本文主要介绍如何在数据库后台利用存储过程，触发器来管理数据库的技术，并以Delphi做前台，SQL Server做后台的模式给出具体的实现代码。

一、SQL交互式数据库查询语言

交互式数据库查询语言SQL中有关表操作基本的SQL语句有如下四种：(下面分别出给四种语句的语法形式及其说明)

(1)查询表命令

SELECT[ALL|DISTINCT]select_list 
[INTO[new_table_name]] 
[FROM{table_name|view_name} 
[WHERE clause] 
[GROUP BY clause] 
[HAVING clause] 
[ORDER BY clause] 
[COMPUTE clause]

其中：

select_list指定列
(ALL则为所有列，DISTINCT为不选相同记录 
new_table_name指定目标表名 
table_name|view_name指定源表名或源视图名 
WHERE clause给出选择条件 
GROUP BY clause按clause分组处理 
HAVING clause给出分组处理的条件 
ORDER BY clause按clause排序 
COMPUTE clause则产生新行

(2)插入记录命令

INSERT[INTO] 
{table_name|view_name}[(cohurnn_list)] 
{DEFAULT VALUES|values_list|select_statement}

其中：

table_name|view_name指定新表名或新视图名 
values_list|select_statement指定列或子查询

(3)修改表令命

UPDATE{table_name|view_name} 
SET[{table_name|view_name} 
{column_list|variable_list|
variable_and_column_list}
[WHERE clause]

其中：

table_name|view_name指定源表名或源视图名 
column_list|variable_list指出列或变量名 
WHERE clause给修改条件

(4)删除命令

DELETE[FROM]{table_name|view_name} 
[WHERE clause]

其中：

table_name|view_name指定源表名或源视图名 
WHERE clause给出修改条件

二、SQL Server中存储过程和触发器的使用

存储过程是存储在服务器上的预先编译好的SQL 语句在使用时要考虑以下问题：

1.存储过程在第一次编译时进行语法检查，编译好的存储过程保存在高速缓存中用于调用，这样执行的速度和效率较高。

2.存储过程由应用程序激活，不由SQL Server自动执行。

3.一个存储过程可以用于收集数据和修改数据，但是不能同时用于两者。

存储过程的优点是：

1.在执行重复任务时能提高效率；

2.使前端的应用程序共享应用逻辑；

3.可以永久创建，也可以临时创建；

4.可以在SQL Server启动时自动执行。

存储过程的创建语句语法为：

CREATE PROCedure[owner.]procedure_name[;number] 
[(parameter1[,parameter2]...[parameter255])] 
[{FOR REPLICATION}|{WITH RECOMPILE} 
[{[WITH]|{,}ENCRYPTION]] 
AS sql_statements

其中：

proceddure_name〓〓为过程名称

;number〓〓用于在过程名称重复时进行编号

[(parameter1[,parameter2]...[parameter255])]〓〓为参数序列

WHTH RECOMPILE〓〓执行计划不保存的高速缓存中，每次执行过程需要重新编译

ENCRYPTION〓〓加密syscomments表的内容，syscomments表中包含CREATE PROCedure的文本，保证无论何时都不删除syscomments表

FOR REPLICATION〓〓过程在前台执行，不在服务器上执行

下面的SQL语句在MYDATABASE数据库上创建存储过程my_store_pro1

USE MYDATABASE

以下须是一个独立的查询模块，因为CREATE PROCDURE语句须是查询模块的首行。

　　CREATE PROCDURE my_store_pro1 
　　@my_paral char, 
　　@my_para2 int 
　　AS 
　　SELECT*FROM my_table1 
　　WHERE my_table1.no1=@my_para1 
　　AND my_table.1no2<=@my_para2 
　　GO 
　　执行存储过my_store_pro1 
　　EXEC my_store_pro1'12',23

触发器是一种特殊的存储过程，无论何时要对它所保护的表进行修改时它就自动执行。触发器由SQL Server自动执行，不能由应用程序调用，便于保护数据库的完整性和完全性。其语法结构为：

　　CREATE TRIGGER [owner.]trigger_name 
　　ON[owner.]table_name 
　　FOR {INSERT,UPDATE,DELETE} 
　　[WITH ENCR YPTION] 
　　AS 
　　IF UPDATE(column_name) 
　　[{AND|OR}UPDATE(column_name)...]sql_statements 
　　其中： 
　　trigger_name〓〓指定触发器的名称 
　　table_name〓〓指定触发器所在的表名 
　　INSERT,UPDATE,DELETE〓〓指定触发条件

ENCRYPTION〓〓加密syscomments表的内容，syscomments表中包含CREATEPROCedure的文本，保证无论何时都不删除syscomments表,sql_statementw是在表的内容有修改(UPDATE)时引起的动作以下是一个修改触发器，如果my_tabel的nolmy_table1字段有修改，给出错误提示。

　　CREATE TRIGGER test 
　　ON my_tablel 
　　FOR UPDATE 
　　AS 
　　IF UPDATE(nol) 
　　BEGIN 
　　PRINT(不能修改此列数据’) 
　　END

　　以下插入触发器在TITLE_L有数据增加时，给末对NO_LOCAL赋值的记录赋值，其值是现有记录中NO_LOCAL的最大值加1(NO_LOCAL为字符串类型)

　　CREATE TRIGGER add_no 
　　ON TITLE_L 
　　FOR INSERT 
　　AS 
　　DECLARE @tmpl int 
　　SELECT @tmpl=MAX(CONVERT(int,NO_LOCAL))FROM TITLB_L 
　　SELECT @tmpl=@tmpl+1 
　　DECLARE @tmpstr char(4) 
　　SELECT @tmpstr=CONVERRT(varchar(4),@tmpl) 
　　UPDATE TITLE_L 
　　SET NO_LOCAL=@tmpstr WHERE NO_LOCAL=NULL

三、应用实例介绍

以下给出的程序段功能为：在前台Delphi环境下调用存储过程，在服务器由表dbo.all选出符合用户身份的记录生成表dbo.today；由触发器删除部分不合日期要求的记录；再从前台用批量记录移动把dbo.today的内容下载的本地LOCALDATA数据库上data.dbf表。

{在服务器的MYDATA数据库上创建存储过程my_store_prol：}

　　CREATE PROCDURE my_store_prol 
　　@secu_id int 
　　AS 
　　SELECT*FROM all 
　　WHERE my_table1.no1<=@secu_id 
　　GO 
　　{在MYDATA数据库的表dbo.today上创建触发器：}
　　CREATE TRLGGER add_no 
　　ON today 
　　FOR INSERT
　　AS 
　　DELETE*FORM today 
　　WHERE riqi　　GO

{在前台程序中执行存储过程my_store_prol:}

　　Databasel.AliasName:='MYDATA'; 
　　Databasel.DatabaseName;='my_database'; 
　　Database1.connected;=True; 
　　SourceTable1.DatabaseName;='my_database'; 
　　SourceTable1.TableName='dbo.today'; 
　　SourceTable1.Active;=True; 
　　StoredProc1.DatabaseName;='My_database'; 
　　StoredProc1.StoredProcName:='my_proc'; 
　　StoredProc1.Params.Clear; 
　　StoredProc1.Params.CreateParam(ftInteger,'secu_id',ptInput); 
　　StoredProc1.Prepare; 
　　StoredProc1.ExecProc;

{在前台程序中下载dbo.today的内容到data.dbf表：}

　Database2.AliasName:='LOCALDATA'; 
　　Database2.DatabaseName:='local_data'; 
　　Database2.connectde:=True; 
　　DestinTable1.DatabaseName:='local_data'; 
　　DestinTable1.TableName:='data.dbf; 
　　DestinTable1.Active:=True; 
　　BatchMovel.Mode:=batAppend; 
　　BatchMovel.RecordCount:=0 
　　BatchMovel.Source:=SourceTable1; 
　　BatchMovel.Destination:=DestinTable1; 
　　BatchMovel.Execute;