超级会员免费看
数据库操作与互操作性全解析
1. 数据库索引创建
1.1 Oracle 索引创建
Oracle 允许在多种对象上创建索引，包括表、分区表、簇、索引组织表、类型化表或簇的标量对象以及嵌套表。可以指定索引的物理属性，还能选择是否并行创建索引。索引类型有普通 B - 树索引、部分索引、基于函数的索引、域索引和位图索引等。
为实现最大互操作性，应避免以下 Oracle 的
CREATE INDEX
语句变体：
-
CREATE BITMAP INDEX
-
INDEX TYPE IS index - type
-
[NO]PARALLEL
-
CLUSTER
-
[NO]LOGGING
-
[NO]COMPRESS
-
[NO]SORT
-
REVERSE
-
ONLINE
此外，Oracle 支持表分区，且可使用
PCTFREE
子句模拟 SQL Server 的
FILLFACTOR
和
PAD_INDEX
子句。
1.2 不同数据库创建视图
1.2.1 通用视图创建
视图是基于预定义查询结果集创建的虚拟表，在查询时呈现给用户。部分情况下，视图可用于检索数据库，还能对其基表进行插入、更新和删除操作。最具互操作性的
CREATE VIEW
语句形式如下：
CREATE VIEW view_name {[(column [,...])] |
AS
subquery [WITH [CASCADED | LOCAL] CHECK OPTION]
WITH CASCADED CHECK OPTION
和
WITH LOCAL CHECK OPTION
子句用于告知数据库管理系统（DBMS）在处理嵌套视图时的行为。使用前者时，对主视图进行插入、更新或删除操作时，会检查主视图及其依赖的所有视图；使用后者时，仅检查主视图。
1.2.2 各数据库视图创建特点
| 数据库 | 支持情况 | 注意事项 |
|---|---|---|
| SQL Server |
允许创建基本视图并带有一些额外选项。避免使用
WITH
选项，因为其他平台不支持视图加密、架构绑定或视图元数据选项。其
WITH CHECK OPTION
实现与标准的
WITH LOCAL CHECK OPTION
基本相同。
| |
| DB2 |
允许创建层次结构和面向对象的视图。为实现最大互操作性，应避免
OF type MODE DB2SQL
、
REF IS
、
UNDER parent_view INHERIT SELECT PRIVILEGES
、
WITH common_table_expression
等子句。不支持物化视图（SQL Server 中的索引视图）。
| |
| MySQL |
从 5.0 版本开始支持 ANSI 标准的
CREATE VIEW
语句。避免使用
ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}
子句，且不允许对临时表创建视图，而 SQL Server 允许。
| |
| Oracle |
支持丰富的
CREATE VIEW
语句子句。构建互操作性语句时，避免
[NO] FORCE
、
OF type ...
、
OF XMLTYPE ...
子句。
FORCE
子句可强制创建视图，忽略错误，类似于 SQL Server 中的延迟名称解析。支持标准的
WITH CHECK OPTION
子句及一些可选子子句，功能与标准的
WITH LOCAL CHECK OPTION
子句基本相同。允许使用
CREATE MATERIALIZED VIEW
语句创建物化视图，类似于 SQL Server 的索引视图。
|
1.3 创建触发器
1.3.1 通用触发器创建语法
所有数据库平台都支持以下通用的
CREATE TRIGGER
语句最低通用语法：
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {DELETE | INSERT | UPDATE }
ON table_name
[FOR EACH {ROW | STATEMENT} ]
BEGIN
statements
END
使用此通用语法，可设置触发器在实际的删除、插入或更新操作之前或之后触发（不能同时在两者时触发）。可选择指定触发器逻辑是针对事务中的每一行触发，还是针对事务中的每个语句触发。触发器可通过
OLD
和
NEW
伪表访问操作前后的数据。
1.3.2 各数据库触发器创建特点
| 数据库 | 支持情况 | 注意事项 |
|---|---|---|
| SQL Server |
支持互操作性
CREATE TRIGGER
语句语法，有一些细微变化。触发器始终以
FOR EACH STATEMENT
模式运行，允许在单个语句中为多个事件创建单个触发器。使用
FOR
关键字代替
BEFORE
。避免使用
INSTEAD OF
、
WITH ENCRYPTION
、
WITH APPEND
、
NOT FOR REPLICATION
、
IF UPDATE...
子句。支持
INSTEAD OF
触发器，可通过
sp_settriggerorder
系统存储过程设置触发器触发顺序。将触发器伪表称为
DELETED
和
INSERTED
。SQL Server 2005 支持 DDL 触发器事件。
| |
| DB2 |
支持互操作性
CREATE TRIGGER
语句语法，避免使用
MODE DB2SQL
和
WHEN
条件子句。默认将两个伪表称为
OLD
和
NEW
，可使用
REFERENCING
子句重命名。支持
INSTEAD OF
触发器类型，使用
NO CASCADE BEFORE
子句代替
BEFORE
。允许在给定表上创建多个触发器，按创建顺序触发。删除基表时不会自动删除触发器，需手动操作。
| |
| MySQL |
支持互操作性版本的
CREATE TRIGGER
语句，但仅支持
FOR EACH ROW
子句。将触发器伪表称为
OLD
和
NEW
。当前每个表每种时间和动作（如
BEFORE INSERT
触发器或
AFTER UPDATE
触发器）只允许有一个触发器。
| |
| Oracle |
支持互操作性的
CREATE TRIGGER
语句，并增加了
INSTEAD OF
触发时间。将触发器伪表称为
OLD
和
NEW
，可使用
REFERENCING
子句重命名。构建互操作性
CREATE TRIGGER
语句时，避免
[OR]
子句、
PARENT
子句和
WHEN
条件子句。允许特定关键字（如
DROP
、
GRANT
或
TRUNCATE
）触发对象事件，可轻松转换为 SQL Server 2005 的 DDL 触发器。
|
1.4 创建存储过程和函数
大多数数据库平台支持创建存储过程和用户定义函数。由于 ANSI SQL 标准直到 2003 年才涉及过程扩展问题，各数据库平台通过创建自己的 SQL 方言来提供过程语言功能。
1.4.1 通用创建语法
- 创建存储过程 ：
CREATE PROCEDURE routine_name
( [{[IN | OUT | INOUT]} [parameter_name] data type [,...] ])
BEGIN
routine_body
END
- 创建函数 ：
CREATE FUNCTION routine_name ([parameter[,...]])
( [{[IN | OUT | INOUT]} [parameter_name] data type [,...] ])
RETURNS scalar_value
BEGIN
routine_body
END
参数可携带值进入例程（
IN
）、从例程输出（
OUT
）或两者兼具（
INOUT
）。函数必须返回单个标量值，存储过程可返回其他类型的值。为实现最大互操作性，
routine_body
应仅包含 SQL 语句或过程调用。
1.4.2 各数据库支持情况
| 数据库 | 支持情况 | 注意事项 |
|---|---|---|
| SQL Server |
使用 T - SQL 方言，2005 年开始允许通过 CLR 接口将 .NET 语言（如 C#）编译为存储过程。允许存储过程返回结果集。支持
ENCRYPTION
和
SCHEMABINDING
选项。
| |
| Oracle | 使用 PL/SQL 方言和 Java。不允许存储过程返回结果集，需将重要结果集存储到临时表中。 | |
| DB2 | 允许使用第三代语言（如 C 和 Fortran）以及 SQL 和 ANSI 2003 SQL/PL 扩展语句。 | |
| MySQL | 仅允许使用 SQL 存储过程和用户定义函数。 |
2. 数据库操作最佳实践
2.1 遵循 ANSI 标准
尽量遵循 ANSI 标准可提高代码的互操作性。尽管 SQL Server 本身并非完全符合 ANSI 标准，但这仍是一个重要原则。以下是一些最佳实践：
-
数据类型
：使用 ANSI 标准数据类型，避免使用
CURSOR
、
IMAGE
、
SQL_VARIANT
、
TABLE
、
VARCHAR(max)
、
TEXT
等数据类型，
DATETIME
数据类型在 SQL Server 中的实现与 ANSI 标准差异较大，也需谨慎使用。
-
标识符
：避免使用带引号、方括号或其他分隔符的标识符。
-
语句操作
：在使用
DELETE
、
INSERT
、
SELECT
和
UPDATE
语句时，避免使用提示、非 ANSI 函数调用、
WITH
子句和
ANSI ONLY
子句。
-
SQL Server 特定操作
：不使用 SQL Server 的第二个
FROM
子句实现
JOINS
，避免
INSERT...EXECUTE
语句。注意 SQL Server 的
SELECT
语句中有许多不具互操作性的子句，如
INTO
子句、
CUBE
和
ROLLUP
子句、
COMPUTE
子句和
FOR XML
子句。
-
分区和索引
：除非仅与 SQL Server 和 Oracle 互操作，否则避免在数据库架构中使用分区。注意 SQL Server 的聚集索引与其他平台的“聚集”索引不同。
-
数据库例程
：数据库例程（过程、函数等）依赖于数据库平台的 SQL 方言，构建互操作性数据库例程通常需要大量迁移工作。
2.2 Codd 的 12 条关系数据库规则
2.2.1 规则概述
这些规则由 E. F. Codd 在 1985 年提出，用于定义关系数据库管理系统（RDBMS）应满足的更具体标准。以下是部分规则及 SQL Server 2005 的符合情况：
-
规则 1：信息规则
：关系数据库中的所有信息仅以一种方式表示，即表中的值。SQL Server 通常满足此规则，因为只能将信息存储在表中。
-
规则 2：保证访问规则
：每个数据项（原子值）可通过表名、主键值和列名的组合逻辑访问。SQL Server 严格遵循此规则，主键用于定位数据，通过表名、主键值和列名可准确访问数据。
-
规则 3：空值的系统处理规则
：完全关系型的 RDBMS 应支持不同数据类型的
NULL
值，用于表示缺失信息。
NULL
值在数学和字符串操作中应正确传播，即
NULL + <anything> = NULL
。SQL Server 有一些设置可自定义
NULL
值的处理方式：
-
ANSI_NULLS
：决定
NULL
值比较的处理方式。
OFF
时，
NULL = NULL
比较结果为
True
；
ON
（默认）时，结果为
UNKNOWN
。
-
CONCAT_NULL_YIELDS_NULL
：
ON
时，
NULL + 'String Value' = NULL
；
OFF
时，为了与 SQL Server 早期版本兼容，
NULL + 'String Value' = 'String Value'
。
2.3 总结
编写在 SQL Server 和其他主要数据库平台之间具有互操作性的代码时，会遇到诸多问题。无论是将代码从 SQL Server 2005 迁移到其他平台，还是反之，都可能需要修改代码以确保其正常运行。通过了解各数据库平台对不同语句的实现方式，可从一开始就编写更具互操作性和可移植性的代码。
通过遵循上述最佳实践和规则，可提高代码的互操作性，减少在不同数据库平台之间迁移代码时的工作量。在实际开发中，应根据具体需求和数据库平台的特点，合理选择和使用数据库功能。
2.4 操作步骤总结
为了更清晰地展示在不同数据库操作中的关键步骤，下面以表格形式总结创建索引、视图、触发器、存储过程和函数的操作步骤：
| 操作类型 | 通用步骤 | 各数据库特殊步骤 |
| ---- | ---- | ---- |
| 创建索引 | 1. 确定要创建索引的对象（表、分区表等）
2. 选择合适的索引类型（如 B - 树索引、位图索引等）
3. 指定索引的物理属性和是否并行创建 | Oracle：避免特定的
CREATE INDEX
语句变体；SQL Server 和 Oracle 可考虑分区索引 |
| 创建视图 | 1. 定义视图的查询语句
2. 根据需要选择是否使用
WITH CHECK OPTION
子句 | SQL Server：避免使用
WITH
选项；DB2：避免特定子句；MySQL：避免
ALGORITHM
子句；Oracle：避免特定子句 |
| 创建触发器 | 1. 确定触发器的触发时间（
BEFORE
或
AFTER
）和触发事件（
DELETE
、
INSERT
或
UPDATE
）
2. 指定触发器作用的表
3. 编写触发器逻辑 | SQL Server：使用
FOR
代替
BEFORE
，避免特定子句；DB2：避免特定子句，可重命名伪表；MySQL：仅支持
FOR EACH ROW
；Oracle：避免特定子句，支持
INSTEAD OF
触发时间 |
| 创建存储过程和函数 | 1. 定义例程的名称和参数
2. 确定参数的方向（
IN
、
OUT
或
INOUT
）
3. 编写例程的主体逻辑 | SQL Server：支持
ENCRYPTION
和
SCHEMABINDING
选项；Oracle：使用 PL/SQL 或 Java，不允许存储过程返回结果集；DB2：支持多种语言；MySQL：仅支持 SQL |
2.5 流程图展示
下面是一个创建存储过程的 mermaid 流程图，展示了通用的创建步骤：
graph LR
A[开始] --> B[定义存储过程名称]
B --> C[定义参数及方向]
C --> D[编写存储过程主体逻辑]
D --> E[结束创建]
2.6 实际应用案例
为了更好地理解上述数据库操作和互操作性的重要性，下面给出一个简单的实际应用案例。假设我们有一个跨平台的应用程序，需要在 SQL Server 和 Oracle 数据库之间迁移数据和代码。
2.6.1 数据迁移
在数据迁移过程中，首先要确保使用的是 ANSI 标准的数据类型，避免使用特定数据库的非标准数据类型。例如，将 SQL Server 中的
VARCHAR(max)
替换为 ANSI 标准的
VARCHAR
类型。
2.6.2 代码迁移
在迁移代码时，要根据各数据库的特点进行调整。例如，在创建视图时，需要避免使用不具互操作性的子句。以下是一个简单的视图创建代码示例，在 SQL Server 和 Oracle 中都能正常运行：
-- 创建视图
CREATE VIEW view_name
AS
SELECT column1, column2
FROM table_name
WHERE condition;
2.6.3 触发器和存储过程迁移
对于触发器和存储过程，同样要遵循互操作性原则。例如，在创建触发器时，使用通用的
CREATE TRIGGER
语法：
-- 创建触发器
CREATE TRIGGER trigger_name
AFTER INSERT
ON table_name
BEGIN
-- 触发器逻辑
INSERT INTO another_table (column1, column2)
VALUES (new_value1, new_value2);
END;
2.7 总结与展望
在数据库开发和管理中，实现代码的互操作性是一个重要的目标。通过遵循 ANSI 标准、了解各数据库平台的特点以及掌握通用的操作语法，可以有效地提高代码的可移植性和可维护性。
未来，随着数据库技术的不断发展，各数据库平台可能会进一步提高对标准的支持，减少差异。开发者也需要不断学习和适应这些变化，以更好地应对跨平台开发的挑战。同时，在实际项目中，要充分考虑数据库的性能、安全性等因素，综合运用各种数据库功能，为用户提供更高效、稳定的应用程序。
在实际工作中，建议开发者建立一个数据库操作的最佳实践文档，记录在不同数据库平台上的操作经验和注意事项，以便在后续项目中快速参考和应用。通过不断积累和总结，提高自己在数据库开发和管理方面的能力。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
