OCP认证考试指南（4）：接合Oracle数据库

一、SQL的使用

• CREATE、ALTER和DROP是主要的3种DDL语句，这些语句被用于在数据库中添加、修改或删除对象。
• DCL语句包括GRANT和REVOKE命令，它们可以被用于为用户或角色授予或取消权限，可以被授予和取消的两种权限是系统权限和对象权限。取决于不同的对象类型，对象权限可能不同。
• 操纵或检索数据都需要使用DML语句。DML的主要元素是SELECT、INSERT、UPDATE和DELETE命令；DML还包括由COMMIT、ROLLBACK和SAVEPOINT命令组成的事务控制语言元素。

1、SELECT语句

基本格式如下：

^?[Copy to clipboard] View Code SQL

SELECT [DISTINCT] {* | column, [expression],...} FROM table;

例子：

^?[Copy to clipboard] View Code SQL

SQL> select username || 'is' || account_status as "User and Sstatus" 2 from dba_users;

1.1、使用WHERE子句限定返回的行

在SELECT语句中，WHERE子句总是位于FROM子句之后：

^?[Copy to clipboard] View Code SQL

SELECT [DISTINCT] {* | column, [expression],...} FROM table; [WHERE condition1 [{AND | OR [NOT]} condition2...] ]

例子：

^?[Copy to clipboard] View Code SQL

SQL> select count(*) from dba_tables where owner = 'SYSMAN';

记住：Oracle执行字符比较时会区分大小写。例如：

^?[Copy to clipboard] View Code SQL

SQL> select count(*) from dba_tables where owner = 'sysman';   COUNT(*) ---------- 0   SQL> select count(*) from dba_tables where owner = 'SYSMAN';   COUNT(*) ---------- 337

1.2、避免结果集中出现重复

DISTINCT关键字可以应用于SELECT列表中的所有列或表达式。例如：

^?[Copy to clipboard] View Code SQL

SQL> select distinct owner from dba_objects;   SQL> select distinct owner, object_type from dba_objects;

1.3、从多个表中检索数据

Oracle能够支持传统的连接语法，不过应当首选ANSI JOIN语法，下面两种，我们更推荐第二种方法。

^?[Copy to clipboard] View Code SQL

SQL> select dba_data_files.tablespace_name, file_name 2 from dba_tablespaces, dba_data_files 3 where dba_tablespaces.tablespace_name=dba_data_files.tablespace_name;   SQL> select dba_data_files.tablespace_name, file_name 2 from dba_tablespaces join dba_data_files 3 on dba_tablespaces.tablespace_name = dba_data_files.tablespace_name;

1.4、数据的排序与分组

ORDER BY子句必须始终出现在SQL语句的最后；子句中的表达式不必位于SELECT列表、JOIN条件或WHERE子句中；除了大型对象数据类型之外，可以使用任何数据类型的列进行排列。

^?[Copy to clipboard] View Code SQL

SQL> select dba_data_files.tablespace_name, file_name 2 from dba_tablespaces join dba_data_files 3 on dba_tablespaces.tablespace_name = dba_data_files.tablespace_name 4 order by dba_data_files.bytes desc;

2、使用INSERT、UPDATE与DELETE语句操纵数据

• INSERT、UPDATE与DELETE语句只能应用于一个表中的数据。

  2.1、INSERT语句
  INSERT语句的基本语法：

  ^?[Copy to clipboard] View Code SQL

  INSERT INTO table [(column, [column...])] VALUES (value [,value...]);

  2.2、UPDATE语句
  UPDATE语句的基本语法：

  ^?[Copy to clipboard] View Code SQL

  UPDATE table SET column = value [, column = value,...] [WHERE condition];

  2.3、DELETE语句
  DELETE语句的基本语法：

  ^?[Copy to clipboard] View Code SQL

  DELETE [FROM] table [WHERE condition];

  2.4、事务控制

  Oracle中的事务控制用于满足ACID测试。ACID代表“原子性（Atomicity）”、“一致性（Consistency）”、“隔离性（Isolation）”与“持久性（Durability）”。

  • 原子性：整个事务必须作为一个单元提交，或者全部都不被提交。
  • 一致性：事务必须遵循某些逻辑原则。
  • 隔离性：完成事务的进程必须能够绝对控制其作用的所有元素。
  • 持久性：意味着某个事务完成（或提交）之后，数据必须保持不变。

  执行第一个DML语句时会启动一个事务。执行COMMIT或ROLLBACK命令可以结束一个事务，执行隐匿的提交或回滚操作也可以结束一个事务。

  2.5、锁定

  Oracle中存在两种锁定类型：共享锁与排他锁。

  执行SELECT语句可以获得共享锁。通过锁定来确保没有人能够在你使用表数据时修改表结构（ALTER TABLE或DROP TABLE），但是，查询的记录并没进行锁定。多个用户可以同时获得相同数据上的共享锁。

  执行DML语句时可以获得排他锁，并且正在被修改的所有记录都会获得排他锁，直至执行COMMIT或ROLLBACK语句。这样，两个用户就无法同时更新相同的数据。

  3、数据定义语言

  3种主要的DDL命令：

• CREATE：执行CREATE语句可以在数据库中添加一个新对象。
• ALTER：ALTER语句被用于改变数据中的表、索引和其他对象的特征。
• DROP：DROP语句被用于从数据库中删除对象。

  4、数据库、系统与会话管理命令
  这些特殊命令包括ALTER DATABASE、ALTER SYSTEM、ALTER SESSION以及其它命令。

• ALTER DATABASE：用于数据库进行全部或部分修改。
• ALTER SYSTEM：用于改变运行实例的状态。
• ALTER SESSION：允许用户修改其会话的属性。

  二、理解PL/SQL

  PL/SQL表示对SQL语言的过程语言扩展（Procedural Language Extensions to SQL）。

  PL/SQL代码必须被编写为若干名为代码块（Block）的部分。因为PL/SQL是一种编译型语言，所以这些代码块在执行之前必须经过编译器的处理。

  PL/SQL代码块具有两种形式：匿名块和命名块。

  匿名（Anonymous）PL/SQL代码块是头部不具有名称的PL/SQL代码。通过工具（如SQL*Plus）发送至PL/SQL引擎，这些代码块随后会立即消失。

  命名（Named）PL/SQL代码块的名称就可以多次“调用”命名块。Oracle中存在4种命名子程序：过程、函数、程序包和触发器。

  所有PL/SQL代码块都具有相同的结构，包括一个用于声明变量和其它标识符的声明部分（以DECLARE关键字开始），一个用于可以运行代码的执行部分（以BEGIN关键字开始），一个用于捕获错误的异常部分（以EXCEPTION关键字开始）以及一个使用END关键字指示的代码块结束符。

  有效的PL/SQL代码块只需要BEGIN和END关键字，并且在这两个关键字之间至少存在一行有效的代码。

  匿名PL/SQL代码块：

  ^?[Copy to clipboard] View Code PLSQL

  SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 Val1 NUMBER := 5; 3 Val2 NUMBER := 2; 4 TheAnswer NUMBER; 5 BEGIN 6 TheAnswer := Val1 + Val2; 7 DBMS_OUTPUT.PUT_LINE('The answer is ' || TheAnswer); 8 EXCEPTION 9 WHEN ZERO_DIVIDE THEN 10 DBMS_OUTPUT.PUT_LINE('Cannot divide by zero！'); 11 END; 12 / The answer is 7   PL/SQL procedure successfully completed.

  命名PL/SQL代码块：

  ^?[Copy to clipboard] View Code PLSQL

  SQL> SET SERVEROUTPUT ON SQL> CREATE OR REPLACE PROCEDURE Add_Nums (Val1 IN NUMBER, Val2 IN NUMBER) 2 AS 3 TheAnswer NUMBER; 4 BEGIN 5 TheAnswer := Val1 + Val2; 6 DBMS_OUTPUT.PUT_LINE('The answer is ' || TheAnswer); 7 EXCEPTION 8 WHEN ZERO_DIVIDE THEN 9 DBMS_OUTPUT.PUT_LINE('Cannot divide by zero！'); 10 END; 11 /   Procedure created.   SQL> execute Add_Nums(10,2); The answer is 12   PL/SQL procedure successfully completed.

  三、Oracle 10G中的查询工具

  1、SQL*Plus

  一直以来，我们在前面所说的环境都是SQL*Plus，这里就不多说了。

  2、iSQL*Plus

  在Linux环境下，我们要启动iSQL*Plus，需要以用户Oracle用户登录，要保证环境变量和Oralce的安装没有问题，都一直按我说的做，当然不会有问题拉，呵。下面来启动它：

  ^?[Copy to clipboard] View Code BASH

  $ isqlplusctl start

  有没有启动成功，最好的方法是打开网页，输入 http://<server>:<port>/isqlplus
  server：运行Oracle的主机名或IP地址。
  port：侦听iSQL*Plus的TCP端口号（通常为5560）。

  成功打开网页，登录进去，一切OK。（口口口？乱码？哦，兄弟，正常！我的也有，所以从来不用，试着改浏览器的语言为English吧，我的改了都没办法，再加上一般情况下都不会用到这个iSQL*Plus工具，所以也不用太急！）

  停止iSQL*Plus：

  ^?[Copy to clipboard] View Code BASH

  $ isqlplusctl stop