Oracle SQL命令通解三步曲

XQKA

支持的，点赞

芳林野草

既然大家对该主题如此感兴趣，我再补充一些内容


Oracle 版本7新增SQL命令

版本7引入了众多新概念及相关SQL命令，包括过程化SQL、触发器、安全管理、对象管理、系统控制等一共30个新命令。

为了更好地理解新命令，我们先了解Oracle版本7引入的若干基本概念（概念后面会给出Oracle对此概念的定义，供大家参考）。

过程化SQL（PL/SQL）

过程化SQL是Oracle对SQL进行扩展的结果，可以包含SQL语句，也可以包含过程控制结构（顺序、分支以及循环结构）。过程、函数以及包都是PL/SQL的程序单元。

PL/SQL is Oracle’s procedural language extension to SQL. PL/SQL allowsyou to mix SQL statements with procedural constructs. PL/SQL provides thecapability to define and execute PL/SQL program units such as procedures,functions, and packages.

存储过程与函数（stored procedure and function）

存储过程和函数组合了SQL和PL/SQL编程语言的语句以完成特定任务，它们都是模式对象。存储过程和函数非常相似，但并不相同。函数会给调用者返回一个返回值，但存储过程不会。

A procedure or function is a schema object that logically groups a setof SQL and other PL/SQL programming language statements together to perform aspecific task.

Procedures and functions are nearly identical. The only differences are thatfunctions always return a single value to the caller, while procedures do not.

包（package）

包是相关存储过程和函数的组合，同时也包括这些存储过程和函数使用的游标及公共变量。

我相信绝大部分人都使用过Oracle内部一个名叫dbms_metadata的包，这个包有一个成员函数叫get_ddl，用于获取对象的定义。

下面这个语句使用该函数获取存储过程P_DELETE_OLD_PART_TBS的具体内容：

selectdbms_metadata.get_ddl( 'PROCEDURE', 'P_DELETE_OLD_PART_TBS', 'WOW_DATA' )  from dual;

A package is a group of related procedures and functions, together withthe cursors and variables they use, stored together in the database forcontinued use as a unit.

触发器（trigger）

触发器和存储过程很类似，其主体本质上都是PL/SQL程序单元。触发器的作用是当表的记录发生增删改操作时，隐式执行一些预定义的操作。

Oracle allows you to define procedures that are implicitly executed whenan INSERT, UPDATE, or DELETE statement is issued against the associated table.These procedures are called database triggers. Triggers are similar to storedprocedures.

概要文件（profile）

概要文件是资源限制的集合。当我们把一个概要文件关联到用户之后，这个用户创建的会话可使用的资源（比如对CPU的使用份额）就受到了约束和限制。

需注意的是，概要文件一开始仅包含对资源的限制，后续版本才增加了对密码管理的规则。

A profile is a named set of resource limits that you can assign to auser. You manage resource limits with user profiles.

角色（role）

角色是相关权限的集合，其主要目的是简化权限的管理。

想象一个场景：我们的数据库中有20个终端用户，有300个表。现在要将其中280个表的读权限赋予其中18个用户，该怎么做？

从数据库内部看，我们可维护一个权限分配表，而这个表将包含280*18=5040条数据，是不是很繁琐？如果定义一个角色，将280个表的读权限赋予该角色，然后将该角色赋给18个用户，那么我们要保存的权限配置数据只有280+18=298条，约为原来的6%！这就是角色存在的意义。

Roles are named groups of related privileges that you grant to users orother roles. Roles are designed to ease the administration of end–user systemand object privileges.

However, roles are not meant to be used for application developers,because the privileges to access objects within stored programmatic constructsneed to be granted directly.

模式（schema）

很多人会把模式和用户混为一谈，因为他们总是成对出现，并且一一对应。但本质上，模式是数据逻辑结构的集合，或者说是模式对象的集合。那么什么是模式对象呢？表、索引、视图、存储过程、序列等都是。

既然用户和模式不同，那么用户又是什么？用户是Oracle实现权限管理的基础，是人和数据库交互的桥梁。没有用户，我们无法登录数据库，也无法做任何操作。因此，用户侧重于安全管理，而模式侧重于业务或者说数据本身，二者是不同的。

A schema is a collection of logical structures of data, or schemaobjects. A schema is owned by a database user and has the same name as that user.Each user owns a single schema.

用户（user）

前面在介绍模式的时候已经提到，用户是连接数据库系统使用人员和数据库本身的桥梁，通过用户可实现不同的人做不同的操作。

确切地讲，这里的user应该理解为账号，即account。用户一般指的是人，account则是人通过它来与系统（软件）进行交互的渠道，或称桥梁。

A user (sometimes called a username) is a name defined in the databasethat can connect to and access objects in database schemas.

补充几点对于命令功能的说明。

对于一开始就使用Oracle 10g、11g的人来说，可能使用过针对数据库本身的触发器，比如当用户登录数据库的时候，做某些特定的操作。但是你可能不知道，CREATE TRIGGER命令在Oracle版本7刚出现的时候，并没有这个特性。所有的触发器只是针对表，不针对数据库，也不针对视图。

其实很多功能选项，都是Oracle在各个版本陆陆续续添加进来的。很多特性可能我们一辈子都不会去用，因为特定的功能都是针对特定的场景而设计，如果没有必要，就不要乱用，否则后续维护成本会上升，简单为上。

还想提一点，Oracle有时候很搞笑。

看到那个CREATE SCHEMA命令了吗？猜猜它是什么意思？你可能会直观地认为它要创建一个schema，但根本不是这样！这个命令的格式如下：

是不是有点无语？这个命令并不是要创建什么schema，而是要设置一个还原点，之后执行一系列的建表、建视图和赋权语句。这个问题背后的原因是，DDL命令会隐式提交事务，也就是说，各个DDL之间是独立的事务。那么如何做到像插入数据那样创建表和视图呢？要么所有表和视图都创建起来，要么所有的都不要创建。CREATE SCHEMA就是为此而设计的。

未完待续 。。。。。。

sjtime

支持，赞一个

芳林野草

Oracle版本8新增SQL命令

版本8引入了目录、库、物化视图、类型等概念及18个相关命令。

Oracle 版本8引入与SQL命令相关的概念如下（来源于Oracle官方文档）：

目录（directory）

Use the CREATE DIRECTORY statement to create a directory object. Adirectory object specifies an alias for a directory on the server’s file systemwhere external binary file LOBs (BFILEs) and external table data are located.You can use directory names when referring to BFILEs in your PL/SQL code andOCI calls, rather than hard coding the operating system path name, therebyproviding greater file management flexibility.

库（library）

Use the CREATE LIBRARY statement to create a schema object associatedwith an operating-system shared library. The name of this schema object canthen be used in the call_spec of CREATE FUNCTION or CREATE PROCEDURE statements,or when declaring a function or procedure in a package or type, so that SQL andPL/SQL can call to third-generation-language (3GL) functions and procedures.

物化视图（materialized view）

Use the CREATE MATERIALIZED VIEW statement to create a materialized view.A materialized view is a database object that contains the results of a query.The FROM clause of the query can name tables, views, and other materializedviews. Collectively these are called master tables (a replication term) or detailtables (a data warehouse term). This reference uses "master tables"for consistency. The databases containing the master tables are called the masterdatabases.

For replication purposes, materialized views allow you to maintaincopies of remote data on your local node.

For data warehousing purposes, the materialized views commonly createdare materialized aggregate views, single-table materialized aggregate views,and materialized join views.

物化视图日志（materialized view log）

Use the CREATE MATERIALIZED VIEW LOG statement to create a materializedview log, which is a table associated with the master table of a materializedview.

When DML changes are made to the master table’s data, Oracle stores rowsdescribing those changes in the materialized view log and then uses the materializedview log to refresh materialized views based on the master table. This processis called an incremental or fast refresh. Without a materialized view log, Oraclemust reexecute the materialized view query to refresh the materialized view. Thisprocess is called a complete refresh.

类型与类型体（type and type body）

Oracle is an object-relational database management system (ORDBMS),which means that users can define additional kinds of data—specifying both thestructure of the data and the ways of operating on it—and use these typeswithin the relational model. This approach adds value to the data stored in adatabase. Object datatypes make it easier for application developers to workwith complex data such as images, audio, and video. Object types storestructured business data in its natural form and allow applications to retrieveit that way.

Use the CREATE TYPE statement to create the specification of an objecttype, a SQLJ object type (which is a kind of object type), a named varyingarray (varray), a nested table type, or an incomplete object type. You createobject types with the CREATE TYPE and the CREATE TYPE BODY statements. The CREATETYPE statement specifies the name of the object type, its attributes, methods,and other properties. The CREATE TYPE BODY statement contains the code for themethods in the type.

芳林野草

Oracle8i新增SQL命令

8i版本引入了上下文、维度、Java、统计信息等概念以及18个相关命令。

上下文（context）

A set of application-defined attributes that validates and secures anapplication.

维度（dimension）

A dimension defines a parent-child relationship between pairs of columnsets, where all the columns of a column set must come from the same table.However, columns in one column set (or "level") can come from a differenttable than columns in another set. The optimizer uses these relationships withmaterialized views to perform query rewrite.

Use the CREATE DIMENSION statement to create a dimension.

Java（Java）

Java has emerged as the object-oriented programming language of choice,because it is object-oriented and efficient for application-level programs. Itincludes the following features:

(1) A Java virtual machine (JVM), which provides the fundamental basisfor platform independence.

(2) Automated storage management techniques, the most visible of whichis garbage collection.

(3) Language syntax that borrows from C and enforces strong typing.

存储大纲（stored outline）

Use the CREATE OUTLINE statement to create a stored outline, which is aset of attributes used by the optimizer to generate an execution plan.

索引类型（indextype）

An object that registers a new indexing scheme by specifying the set ofsupported operators and routines that manage a domain index.

操作（operator）

Operators can be referenced by indextypes and by DML and query SQLstatements. The operators, in turn, reference functions, packages, types, andother user-defined objects.

芳林野草

Oracle 10g新增SQL命令

10g版本引入了ASM磁盘组以及闪回数据库的概念及9个相关新命令。

磁盘组（disk group）

One or more Automatic Storage Management disks managed as a logicalunit. Automatic Storage Management disks can be added or dropped from a diskgroup while preserving the contents of the files in the group, and with only aminimal amount of automatically initiated I/O required redistributing the dataevenly. All I/O to a disk group is automatically spread across all the disks inthe group.

还原点（restore point）

Use the CREATE RESTORE POINT statement to create a restore point, whichis a name associated with an SCN of the database corresponding to the time ofthe creation of the restore point. A restore point can be used to flash a tableor the database back to the time of creation of the restore point without theneed to determine the SCN or timestamp.

芳林野草

Oracle 11g新增SQL命令

11g版本引入了闪回数据归档及模式对象多版本的概念，以及相关的7个命令。

闪回数据归档（flashback data archive）

Use the CREATE FLASHBACK ARCHIVE statement to create a flashback data archive, which provides the ability to automatically track and archivetransactional data changes to specified database objects. A flashback dataarchive consists of multiple tablespaces and stores historic data from alltransactions against tracked tables. Flashback data archives retain historicaldata for the time duration specified using the RETENTION parameter. Historicaldata can be queried using the Flashback Query AS OF clause. Archived historicdata that has aged beyond the specified retention period is automaticallypurged.

版本（edition）

A private environment in which you can redefine database objects.Edition-based redefinition enables you to upgrade an application's databaseobjects while the application is in use, thus minimizing or eliminating downtime.

芳林野草

Oracle 12c新增SQL命令

12c版本为了支撑软件即服务的应用模式，提供了多租户架构的数据库支撑，引入可插拔式数据库的概念以及相关的安全审计策略，包含了10个新增命令。

可插拔数据库（pluggable database (PDB)）

In a multitenant container database (CDB), a portable collection ofschemas, schema objects, and non-schema objects that appears to an Oracle Netclient as a non-CDB.

Use the CREATE PLUGGABLE DATABASE statement to create a pluggabledatabase (PDB).

审计策略（audit policy）

This type of auditing is new beginning with Oracle Database 12c andprovides a full set of enhanced auditing features. Refer to Oracle DatabaseSecurity Guide for more information on unified auditing.

Use the CREATE AUDIT POLICY statement to create a unified audit policy.

至此，我们简单介绍了Oracle各个版本引入的关键概念，并通过这些概念来把握相关的SQL命令，从而对整个SQL命令体系有了大致了解。

Simon。115

支持一下！好像很厉害的样子呢。

芳林野草

Simon。115 发表于 2014-3-9 18:31
支持一下！好像很厉害的样子呢。

厉害谈不上，谢谢支持！