Oracle 9i与MS SQL Server 2000之比较连载五.zz

http://kb.cnblogs.com/a/1153156

Oracle 9i与MS SQL Server 2000之比较连载五

2008-04-14 18:13

四、Oracle中新的数据库对象:

    实例化视图、快照、序列、程序包、同义词、抽象的数据类型

●          实例化视图又称显形图:实例化说明它有自己的存储空间,视图说明它的数据来源于其它表数据。实例化视图中的数据,设置为隔一段时间更新数据,更新的模式可以定义为完全更新和增量更新

●          快照基本上同实例化视图,只不过数据来源不同,快照数据来源于远程数据库,而实例化视图则来源于本地数据表

●          序列,相当于MS SQL 中的identity列,它是一个数字顺序列表,下面有详细介绍。

●          程序包,它是过程、函数、全局变量的集合,它封装了私有变量、私有过程和私有函数,如:dbms-out

●          同义词,是对数据库中的对象的别名,同义词可以是全局的也可以是私有的(属于某个用户的)如:Tabcol

●          抽象的数据类型,类似于C中的结构体或Pascal记录类型。

 

五、Oracle回滚段和SQL Server的数据库事务日志文件

    回滚段提供了事物回滚需要使用的数据变化以前的映象,这些映象是按条目存储的,如果这些条目过少,一个事务等待另一个事务的几率增大,就会影响数据库的性能。缺省安装时,提供一个系统回滚段,它在system表空间。为了提高性能,system表空间不应存储有任何数据字典信息以外的信息。每次启动时,Oracle RDBMS 执行自动恢复。它检验表空间文件的内容是否与联机重做日志文件一致。如果不一致,Oracle 将联机重做日志文件内容应用到表空间文件(前滚),并删除回滚段中发现的任何未提交的事务(回滚)。如果 Oracle 不能从联机重做日志文件中得到它所需要的信息,它就会查询存档重做日志文件。 

每个 SQL Server 事务日志均有 Oracle 回滚段与 Oracle 联机重做日志的组合功能。每个数据库都有自已的事务日志,它记录了对数据库所作的全部更改,并且由数据库的所有用户共享。当一个事务开始且发生数据修改时,就会在日志中记录一个 BEGIN TRANSACTION 事件(以及修改事件)。在自动故障恢复过程中,这个事件用于确定事务的起始点。在收到每个数据修改语句时,先将更改写入事务日志,然后再写入数据库。MS SQL数据库事务日志文件功能雷同于回滚段,只不过它是同特定的数据库密切相关的。

六、主要数据类型的对比

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"><!-- saved from url=(0033)http://www.oradb.net/datatype.htm -->

<BIG>ORACLE的数据类型</BIG>

     常用的数据库字段类型如下:

<TBODY>字段类型

中文说明

限制条件

其它说明

<SMALL>CHAR</SMALL>

<SMALL>固定长度字符串</SMALL>

<SMALL>最大长度2000 bytes</SMALL>

 

<SMALL>VARCHAR2</SMALL>

<SMALL>可变长度的字符串</SMALL>

<SMALL>最大长度4000 bytes</SMALL>

<SMALL>可做索引的最大长度749</SMALL>

<SMALL>NCHAR</SMALL>

<SMALL>根据字符集而定的固定长度字符串</SMALL>

<SMALL>最大长度2000 bytes</SMALL>

 

<SMALL>NVARCHAR2</SMALL>

<SMALL>根据字符集而定的可变长度字符串</SMALL>

<SMALL>最大长度4000 bytes</SMALL>

 

<SMALL>DATE</SMALL>

<SMALL>日期(日--年)</SMALL>

<SMALL>DD-MM-YYHH-MI-SS</SMALL>

<SMALL> </SMALL>

<SMALL>LONG</SMALL>

可变长度的字符数据</SMALL>

<SMALL>最大长度</SMALL>2G231-1

足够存储大部头著作

<SMALL>RAW</SMALL>

<SMALL>固定长度二进制数据</SMALL>

<SMALL>最大长度2000 bytes</SMALL>

<SMALL>可存放比较小的多媒体图象声音等</SMALL>

<SMALL>LONG RAW</SMALL>

<SMALL>可变长度的二进制数据</SMALL>

<SMALL>最大长度</SMALL>2G

<SMALL>可存放比较大的多媒体图象声音等</SMALL>

<SMALL>BLOB</SMALL>

大型的<SMALL>二进制对象(可变长度)</SMALL>

<SMALL>最大长度</SMALL>4G

 

1、  一个表可有多个这

种列

2、使用select语句只能返回存储这种数据的位置

3、既可联机存储,也可脱机存储 

4、支持对象(Object)类型

5、随机访问数据

<SMALL>CLOB</SMALL>

大型的CHAR类型数据</SMALL>

<SMALL>NCLOB</SMALL>

大型的<SMALL>NCHAR类型数据</SMALL>

<SMALL>BFILE</SMALL>

<SMALL>存放在数据库外的大型的二进制文件</SMALL>

<SMALL>ROWID</SMALL>

<SMALL>数据表中记录的唯一行号</SMALL>

<SMALL>10 bytes ********.****.****格式,*01,存储在索引中。</SMALL>

UROWID <SMALL></SMALL>

同上<SMALL> </SMALL>

存储在索引中<SMALL> </SMALL>

NROWID

二进制数据表中记录的唯一行号

最大长度4000 bytes

<SMALL>NUMBER(P,S)</SMALL>

<SMALL>数字类型</SMALL>

<SMALL>P为总的位数,S为小数位</SMALL>

<SMALL>DECIMAL(P,S)</SMALL>

<SMALL>数字类型</SMALL>

<SMALL>P为总的位数,S为小数位</SMALL>

<SMALL>INTEGER</SMALL>

<SMALL>整数类型</SMALL>

小的整数

<SMALL>FLOAT</SMALL>

<SMALL>浮点数类型</SMALL>

<SMALL>NUMBER(38),双精度</SMALL>

<SMALL>REAL</SMALL>

<SMALL>实数类型</SMALL>

<SMALL>NUMBER(63),精度更高</SMALL></TBODY>

<SMALL>               

注意:每个表中只能有一个LONGLONG  RAW列,……….</SMALL>
<SMALL>                    <SMALL>                               </SMALL>
<SMALL>      二者映射关系: From  SQL Server  To  Oracle

</SMALL>

 SQL Server 2000 数据类型

Oracle 数据类型

bigint

NUMBER

binary

LONG RAW NOT NULL

bit

NUMBER (1, 0)

char

VARCHAR2 (900) NOT NULL

datetime

DATE

decimal

NUMBER (255, 3) NOT NULL

float

FLOAT NOT NULL

image

LONG RAW

int

NUMBER (255, 3) NOT NULL

money

NUMBER (255, 3) NOT NULL

nchar

VARCHAR2 (2000) NOT NULL

ntext

LONG

numeric

NUMBER (255, 3) NOT NULL

nvarchar

VARCHAR2 (2000) NOT NULL

real

FLOAT NOT NULL

smallint

NUMBER (255, 3) NOT NULL

smalldatetime

DATE NOT NULL

smallmoney

NUMBER (255, 3) NOT NULL

sql_variant

LONG

sysname

CHAR(255)

text

LONG

timestamp

RAW (255)

tinyint

NUMBER (255, 3) NOT NULL

 

From  Oracle  To  SQL Server

Oracle

Microsoft SQL Server

CHAR

建议使用 char。因为 char 类型的列使用固定的存储长度,所以,访问时比 varchar 列要快一些。

VARCHAR2 
 LONG

varchar 或 text。(如果 Oracle 列中数据值的长度为 8000 字节或更少,则使用 varchar;否则,必须使用 text。)

RAW 
LONG RAW

varbinary 或 image。(如果 Oracle 列中数据值的长度为 8000 字节或更少,则使用 varbinary;否则,必须使用 image。)

NUMBER

如果整数在 1  255 之间,使用 tinyint
如果整数在 -32768  32767 之间,使用 smallint
如果整数在 -2,147,483,648  2,147,483,647 之间,则使用int
如果需要浮点类型数,使用 numeric(有精度和小数位)。
注意:不要使用 float  real,因为可能会产生舍入(Oracle NUMBER  SQL Server numeric 均不舍入)。
如果不确定,则使用 numeric;它最接近 Oracle NUMBER 数据类型。

ROWID

使用 identity 列类型。

CURRVAL, NEXTVAL

使用 identity 列类型以及 @@IDENTITYIDENT_SEED() IDENT_INCR() 函数。

七、语法上的区别

1、基本SQL语句的区别

l         SELECT 语句

l         SQL Server 不支持 Oracle  INTERSECT  MINUS 集合运算符。可使用 SQL Server EXISTS  NOT EXISTS 子句,实现相同的结果。

   下面两条语句返回的数据是相同的。

Oracle(返回两个查询都有的行)

Microsoft SQL Server

SELECT  CCODE, CNAME
FROM    DEPT 
INTERSECT

SELECT  C.CCODE, C.CNAME
FROM   STUDENT  G,
         DEPT  C
WHERE  C.CCODE = G.CCODE

SELECT  CCODE, CNAME
FROM    DEPT  C
WHERE  EXISTS

(SELECT  *  FROM

          STUDENT  G
WHERE  C.CCODE = G.CCODE)

   下面两条语句返回的数据是相同的。

Oracle

Microsoft SQL Server

SELECT  CCODE, CNAME
FROM    DEPT 
MINUS

SELECT  C.CCODE, C.CNAME
FROM    STUDENT  G,
          DEPT  C
WHERE  C.CCODE = G.CCODE

SELECT    CCODE, CNAME
FROM      DEPT C
WHERE    NOT EXISTS

(SELECT   *  FROM

           STUDENT G
WHERE   C.CCODE = G.CCODE)

l       将 SELECT 语句做为表名使用

Microsoft SQL Server  Oracle 均支持在执行查询时,把 SELECT 语句作为表的来源使用。SQL Server 需要一个别名;Oracle别名的使用是可选的。

Oracle

Microsoft SQL Server

SELECT   SSN,LNAME,SUM_PAID
FROM     STUDENT,
(SELECT  SUM(TUITION)

         SUM_PAID FROM

         STUDENT)

SELECT    SSN, LNAME,SUM_PAID
FROM      STUDENT,
(SELECT   SUM(TUITION)

          SUM_PAID FROM

          STUDENT)   SUM_STUDENT

l        INSERT 语句

T-SQL 语言支持对表和视图的插入,但不支持对 SELECT 语句的 INSERT 操作。如果 Oracle 应用程序代码执行对 SELECT 语句的插入操作,则必须对它进行修改。如:

Oracle

Microsoft SQL Server

INSERT INTO (SELECT SSN,CCODE, GRADE FROM GRADE)
VALUES ('11', '1111',NULL)

INSERT INTO GRADE (SSN, CCODE, GRADE)
VALUES ('11', '1111',NULL)

Transact-SQL values_list 参数提供了 SQL-92 标准关键字 DEFAULT,但 Oracle 不支持。此关键字指定了执行插入操作时使用列的默认值。如果指定列的默认值不存在,则插入 NULL。如果该列不允许 NULL,则返回一个错误消息。如果该列数据类型定义为 timestamp,则插入下一个有序值。

l        DELETE 语句

如果要对 Oracle 中的 SELECT 语句执行删除操作,则必须修改 SQL Server 语法,因为 Transact-SQL 不支持这一功能。 Transact-SQL 支持在 WHERE 子句中使用子查询,以及在 FROM 子句中使用联接。后者可产生更有效的语句。请参见后面“UPDATE 语句”中的示例。

 

 

 Oracle

Microsoft SQL Server

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

DELETE 
[FROM ] 

table_name [ [AS] table_alias] WITH ( <table_hint_limited> [Un])
view_name [ [AS] table_alias]
rowset_function_limited 
}

[ FROM {<table_source>} [,Un] ]
[WHERE 
{ <search_condition> 
| { [ CURRENT OF 

{ [ GLOBAL ] cursor_name } 
cursor_variable_name 
}

}
]
[OPTION (<query_hint> [,Un])]

 
注意:删除记录并不能释放ORACLE里被占用的数据块表空间. 它只把那些被删除的数据块标成unused.如果确实要删除一个大表里的全部记录, 可以用 TRUNCATE 命令, 它可以释放占用的数据块表空间  TRUNCATE TABLE 表名,但此操作不可回退。

l         EXISTS

   
 

Oracle

Microsoft SQL Server

SQL*PLUS中不能正确执行这条语句:

BEGIN

IF EXISTS (SELECT * FROM

           ONLINEUSER) THEN

DBMS_OUTPUT.PUT_LINE('OK');

  END IF;

END;

在查询分析器中能正确地执行这条语句:

 

 

IF EXISTS  (SELECT * FROM ONLINEUSER)

   PRINT('OK')

 

 

 
注:在Oracle中函数或伪列 'EXISTS' 只能在 SQL 语句中使用
 

2、表数据复制

l         库内数据复制

MS SQL Server

Insert into  复制表名称  select语句 (复制表已存在)

Select  字段列表  into  复制表名称  from  表(复制表不存在)

Oracle

Insert into 复制表名称 select语句(复制表已存在)

create table 复制表名称 as select语句(复制表不存在)

l         文本文件转入、转出的批量处理

MS SQL Server

 

BCP命令行程序

 

Oracle

 

SQLLDR命令行程序

3、表数据更新

l         根据其它表数据更新你要更新的表。

MS SQL Server

Update   A   SET   字段1=B字段表达式   字段2B字段表达式 

From  B  WHERE  逻辑表达式

 

如:

UPDATE  titles  SET  ytd_sales =  t.ytd_sales + s.qty

    FROM  titles  t, sales  s

    WHERE  t.title_id = s.title_id

    AND  s.ord_date = (SELECT  MAX(sales.ord_date)  FROM  sales)

 

Oracle

Update  A  SET  字段1=(select   字段表达式  from  B  WHERE) 字段2(select  字段表达式  from  B  WHERE)   WHERE 逻辑表达式

 

假如A需要多个字段更新,显然MS SQL 语句更简练。

l        T-SQL UPDATE 语句不支持对 SELECT 语句的更新操作。

如果 Oracle 应用程序代码对 SELECT 语句进行更新,则可以把 SELECT 语句转换成一个视图,然后在 SQL Server UPDATE 语句中使用该视图名称。请参见前面“INSERT 语句”中的示例。

 

Oracle  UPDATE 命令只能使用一个 PL/SQL 块中的程序变量。而Transact-SQL 语言并不需要使用块。 如下图:

Oracle

Microsoft SQL Server

DECLARE
VAR1 NUMBER(10,2);
BEGIN
VAR1 := 2500;
UPDATE  STUDENT SET  TUITION =VAR1;

END;

DECLARE
@VAR1 NUMERIC(10,2)
SELECT  @VAR1 = 2500

UPDATE  STUDENT SET  TUITION =@VAR1

 

 SQL Server 中,DEFAULT 关键字可用于将一列设为其默认值。但不能使用 Oracle  UPDATE 命令,将一列设为默认值。

Transact-SQL  Oracle SQL 均支持在 UPDATE 语句中使用子查询。但是,Transact-SQL FROM 子句可用来创建一个基于联接的 UPDATE。这一功能使 UPDATE 语法可读性更好,在某些情况下还能改善性能。

Oracle

Microsoft SQL Server

UPDATE  STUDENT S SET TUITION = 1500
WHERE  SSN IN (SELECT SSN 
FROM GRADE G WHERE G.SSN = S.SSN

AND G.CCODE = '1234')

Subquery:
UPDATE  STUDENT  S  SET  TUITION = 1500
WHERE  SSN  IN  (SELECT SSN

FROM GRADE G WHERE G.SSN = S.SSN

AND G.CCODE = '1234')
FROM clause:

UPDATE  STUDENT  S  SET  TUITION = 1500
FROM  GRADE  G
WHERE S.SSN = G.SSN AND G.CCODE = '1234'

4内联结,外联结交叉联接

MS SQL Server 

  • 内联接   仅显示两个联接表中的匹配行的联接。(这是查询设计器中的默认联接类型。)例如,可以联接 titles 表和 publishers 表以创建显示每个书名的出版商名称的结果集。在内联接中,结果集内不包含没有出版商信息的书名,也不包含没有书名的出版商。如:
SELECT title, pub_name  FROM  titles INNER JOIN
publishers ON titles.pub_id = publishers.pub_id

注意   当创建内联接时,包含 NULL 的列不与任何值匹配,因此不包括在结果集内。空值不与其它的空值匹配。

内联结也就是我们平常的等联结.SQL-92标准之中,内部联结可指定于FROMWHERE子句中.这是 SQL-92WHERE子句中唯一支持的联结类型.

  • 外联接   甚至包括在联接表中没有相关行的行的联接。可以创建外联接的三个变化形式来指定所包括的不匹配行:
    • 左向外联接   包括第一个命名表(""表,出现在 JOIN 子句的最左边)中的所有行。不包括右表中的不匹配行。例如,下面的 SQL 语句说明 titles 表和 publishers 表之间的左向外联接包括所有的书名,甚至包括那些没有出版商信息的书名:
SELECT titles.title_id, titles.title, publishers.pub_name
FROM titles LEFT OUTER JOIN publishers ON titles.pub_id 
             = publishers.pub_id
    • 右向外联接   包括第二个命名表(""表,出现在 JOIN 子句的最右边)中的所有行。不包括左表中的不匹配行。例如,在 titles 和 publishers 表之间的右向外联接将包括所有的出版商,甚至包括那些在titles 表中没有书名的出版商。如:
SELECT titles.title_id, titles.title, publishers.pub_name
FROM titles RIGHT OUTER JOIN publishers ON titles.pub_id 
             = publishers.pub_id
    • 完整外部联接   包括所有联接表中的所有行,不论它们是否匹配。例如,titles 表和 publishers 表之间的完整外部联接显示所有书名和所有出版商,甚至包括那些在另一个表中没有匹配值的书名和出版商。
SELECT titles.title_id, titles.title, publishers.pub_name
FROM titles FULL OUTER JOIN publishers ON titles.pub_id 
             = publishers.pub_id
  • 交叉联接   在这类联接的结果集内,两个表中每两个可能成对的行占一行。例如,在通过作者 CROSS JOIN 出版商输出的结果集内,每个可能的作者/出版商组合占一行。如:
SELECT * FROM authors CROSS JOIN publishers 
 
Oracle(9i以前的版本)

1         字段1=字段2(+)(左连接)

2         字段1(+)=字段2(右连接)

Oracle不支持标准的外连接语法,也没有全外连接。它的外联结与众不同. Oracle的外联结符号是(+),并且在整个select语句中,只能有一张表做为主表和其它的表进行外联.如果你的SQL Server中用到两张或者两张以上的表作为主表的话,那么只有完全改写.并且在SQL Server的外联语句中如果用到了in或者or的话,那转成Oracle将颇费周折.

例程如下:

SQL Server:

select a.COMP_ID,a.TRANS_NO,b.PART_NO from TransMaster a left outer join TransDetail b on (a.COMP_ID = b.COMP_ID and a.TRANS_NO = b.TRANS_NO)

Oracle:

select a.COMP_ID,a.TRANS_NO,b.PART_NO from TransMaster a,TransDetail b

where a.COMP_ID = b.COMP_ID(+) and a.TRANS_NO = b.TRANS_NO(+);

//请注意(+)这个符号应该紧挨着从表的字段出现

 

   Oracle 外连接的一个小技巧: 外部联接"+"按其在"="的左边或右边分左联接和右联接.若不

"+"运算符的表中的一个行不直接匹配于带"+"预算符的表中的任何行,则前者的行与后者中的

一个空行相匹配并被返回.若二者均不带’+’,则二者中无法匹配的均被返回.利用外部联接"+",

以替代效率十分低下的 not in 运算,大大提高运行速度.例如,下面这条命令执行起来很慢 

 

Select   a.empno  from  emp  a  where  a.empno   not  in 

 (select  empno  from  emp1  where  job=’SALE’);

 

 倘若利用外部联接,改写命令如下

select  a.empno  from  emp a ,emp1 b where  a.empno=b.empno(+) 

 

and  b.empno is null  and  b.job=’SALE’; 

 
可以发现,运行速度明显提高.

 

Oracle 9i包括以下新的TABLE JOIN的句法结构:

  • NATURAL  JOIN——这是一个很有用的Oracle 9i的句法,它通过从WHERE子句中移动连接标准来改善SQL的稳定性(相当于SQL Server 内联接,但有区别)
  • LEFT OUTER JOIN——它返回表格中左边的行和右边的数值,如果没有搭配的行的话,则返回零(相当于SQL Server 左向外联接)
  • RIGHT OUTER JOIN——它返回表格中右边的行和左边的数值,如果没有搭配的行的话,则返回零(相当于SQL Server 右向外联接)
  • FULL OUTER JOIN——它返回的是两个表格中所有的行,用零填满每一个空格。这在Oracle8i中则没有相应的此种句法(相当于SQL Server完整外部联接)
  • CROSS  JOIN——它在两个表格中创建了一个卡迪尔列,就象是在Oracle 8i中没写WHERE时那样。(相当于SQL Server交叉联接)
  • USING子句——它可以通过名字来具体指定连接
  • ON子句——这个句法允许在两个表中为连接具体指定字段的名字SQL Server中的一样。

 


NATURAL JOIN

我喜欢NATURAL JOIN的原因在于它能够通过在两个表中配对的字段的名字来自动的检查join。它同时还简化了Oracle 9i SQL,当然,NATURAL JOIN要求在每一个表中的字段的名字相同。很有意思的是,这种特性甚至在没有主要的或是外来的关键词作为参考时也能起作用,如下面两条语句功能相同:

Oracle 8i,
Select book_title, sum(quantity)
from book, sales
where book.book_id = sales.book_id
group by book_title;

Oracle 9i
Select book_title, sum(quantity)
from book
natural join sales
group by book_title;


新的OUTER JOIN句法

ISO99标准把复杂的加号从Oracle outer join中拿出去,并使得outer join SQL更容易理解。

LEFT OUTER JOIN
LEFT OUTER JOIN中,会返回所有左边表格中的行,甚至在被连接的表格中没有可配对的栏目的情况下也如此。在下边的例子中,返回了所有雇员的姓,甚至包括了那些没有分配到部门的雇员。如下面两条语句功能相同:

Oracle8i
select last_name, dept_id
from emp e, dept d
where e.department_id = d.department_id(+);

Oracle9i
select last_name, dept_id
from emp
LEFT OUTER JOIN Dept
ON e.dept_id = d.dept_id;

RIGHT OUTER JOIN
RIGHT OUTER JOIN中返回的是表格中所有右边的行,甚至在被连接的表格中没有可配对的栏目的情况下也如此。在这个例子中,返回了所有部门的ID,包括那些没有一个雇员的的部门。如下面两条语句功能相同:


Oracle8i
select last_name, d.dept_id
from employees e, departments d
where e.department_id(+) = d.department_id;

Oracle9i
select last_name, d.dept_id
from employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id);

 

CROSS JOIN
Oracle中,CROSS JOIN产生了一个“卡迪尔的产物(Cartesian product)”,就象是在连接两个表格时忘记加入一个WHERE子句一样
select  last_name, dept_id  from  emp, depts;

Oracle9i中,我们使用CROSS JOIN 来达到相同的结果
select  last_name, dept_id  from  emp CROSS JOIN dept;

USING子句
假如几个栏目有同样的名字,而你又不想用所有的这些栏目来连接的时候,你就可以用USING子句。在USING子句中所列的栏目的句子中不会有任何的修饰词,包括where子句也不会有, 如下面两条语句功能相同:


Oracle8i
select dept_id, city
from departments, locations
where departments.location_id = location.location_id;

Oracle9i
select dept_id, city
from departments
JOIN locations
USING (location_id);

ON子句
ON子句被用于当在两个表格中的栏目名字不搭配时来连接表格。而连接条件就是where子句中的过滤条件, 如下面两条语句功能相同:


Oracle8i
select department_name, city
from department, location
where department.location_id = location.loc_id;

Oracle9i
select department_name, city
from department d
JOIN location l
ON (d.location_id = l.id);

易变的连接
易变的连接就是两个以上的表格被连接所用的。ISO SQL 1999标准通常假设表格从左至右连接,连接的条件是能够为现在的连接或以前的与左边的连接相关联的栏目提供参考。


Oracle8i
select emp_id, city_name, dept_name from location l, department d, emp e
where d.location_id = l.location_id and d.department_id = e.department_id;

Oracle9i
select emp_id, city_name, dept_name from locations l
JOIN departments d ON (d.location_id = l.location_id)
JOIN employees e ON (d.department_id = e.department_id);

 

 

5存储过程或函数中使用的临时表

这是Oracle 明显不如SQL Server的一个地方。

l         临时表的处理机制

Oracle中的临时表和SQL Server中的临时表根本不是一个概念,它无法达到SQL Server中临

时表的作用,在Oraclesp中不能create table

    SQL Server的临时表:

    SQL Server的临时表有两种本地临时表(#)和全局临时表(##) ,二者在名称、可见性和可用性上均不相同。SQL Server的临时表全部都存储在tempdb数据库中,但只要你的tempdb分配了足够大的硬盘空间,在多用户并发操作时临时表的性能就不会降低.并且每隔一段周期SQL SERVER就自动会对tempdb进行碎片整理以确保性能.

    本地临时表是独立于每一个用户连接的它们仅对当前的用户连接是可见的;当用户从 Microsoft® SQL Server&S482; 2000 实例断开连接时被删除。

    全局临时表一旦创建,所有的用户连接都可以使用创建后对任何用户都是可见的,当所有引用该表的用户从 SQL Server 断开连接时被删除。

 

    Oracle中的临时表:

    Oracle中的临时表和SQL Server中的临时表的处理机制全然不同.Oracle的临时表顶多只能相当于SQL Server中的全局临时表,而且它会永久存在,如果你自己不去drop它的话,Oracle是不会自动将其释放的.而且SQL Server中的局部临时表在Oracle中无法替代,因此我在Oracle中放弃了对临时表的使用,所有的临时表全部改为永久表.

    Oraclesp中不能出现DDL语句.因为所有的DDL语句(create,drop,alter,truncate)都是显式带有commit命令,Oraclesp中不能有显式commit的存在.如果你非要在sp中建表或者删除表的话,你可以用动态SQL来完成隐式commit. 例如: execute immediate create table ……”关于动态SQL,后面将做出详细的介绍.

    在这里将有两点要注意,处理不当将影响性能或者导致程序出错:

    1. sp一开始直接用动态SQL建永久表,然后对表进行操作,sp退出之前再用动态SQL删除永久表.在多用户并发操作的时候。A连接调用了该sp并成功创建了表, B连接也调用该sp试图去创建这张表的时候,Oracle会很野蛮的将sp中断,然后我们的客户就会看到很不友好的出错框.

    2. 为了让多用户使用互不干扰,由程序生成sessionid传入sp或者利用Oracle的函数userenv(sessionid)生成sessionid.然后在sp中用动态SQL生成表名+sessionid的表,对该表进行操作,sp退出时将其删除.

    但这样仍会有一个问题:由于sp被经常调用导致不断的建表删表.Oracle的表都存放在表空间上.这样大量的DDL语句会让表空间内的碎片不断的增多而表空间将不断增大,要知道Oracle的碎片整理必须要手动进行,它不会像SQL SERVER那样自动整理.Oracle最让人满意的是它可以优化,Oracle最让人不满意的是它必须优化!过了一个季度甚至于一个月,我们的用户就会向我们抱怨我们的系统跑得越来越慢了.又提到了我前面说过的话:我们的绝大多数程序没有受到真正的考验,如果不考虑清楚的话,我们的系统将有严重的问题.

l         SQL Server中的临时表移植到Oracle中我的处理方法如下:

1. 创建CreateTempTable.sql文件,所有原SQL Server中用到的临时表都在此建立,只是每张表多一个字段sessionid int,如果表有主键,那么将sessionid加入主键.

2. 创建DropTempTable.sql文件,里面的内容是:

begin

     for r in (select 'drop table '||object_name as sqls from user_objects where

        object_type = ' TABLE' and object_name in (‘temp1’,’temp2’,……)) loop

                                                //所有的临时表都写在in

     execute immediate (r.sqls);

end loop;

end;

这两个SQL文件让我们的程序自动运行.

3. 由程序生成sessionid或者通过userenv(‘sessionid’) 生成sessionid写入表的sessionid字段。每个连接只处理本连接的数据。

l         下面两条语句效果相当(全局临时表)

 MS SQL Server

 Create  table  ##表名  或 select   into  ##表名

 Oracle

 CREATE GLOBAL TEMPORARY TABLE temp1

 (……

  sessionid int)

 on commit delete rows

6分组语句 group by

下面两条语句执行结果一样:

   MS SQL Server  从右往左

select left(catid,1) as "小组号", ISNULL(result,'未评估') as "评估结果",count(*) as "加盟店数量" from cat group by result,left(catid,1)

   Oracle         从左往右

select substr(catid,1,1) as "小组号", result as "评估结果",count(*) as "加盟店数量" from cat group by substr(catid,1,1),result

 

Oracle  having 子句对 group by 子句所确定的行组进行控制,having 子句条件中只允许

涉及常量,聚组函数或group by 子句中的列

7存储过程

l         Oraclesp不能返回结果集。

     Oracle的任何PL/SQL语句块中所有的select语句必须要有into子句! 这就是Oraclesp不能返回结果集的原因本人采用下面的笨办法解决:

  create or replace package pkg_test

  as

  type cur_test is ref cursor; --定义一个cursortype

  end pkg_test;

  /

  create or replace procedure p_test

  (

  v_cur out pkg_test.cur_test

  )

  as

    v_sql varchar2(100); --

  begin

    v_sql := 'select a1,a2 from test';

    OPEN v_cur FOR v_sql; --

    exception

    when others then

    DBMS_OUTPUT.PUT_LINE('Error ---------' || sqlcode || ' : ' || sqlerrm );

  end p_test;

  / 

l         在Oraclesp中不能出现DDL语句.

因为所有的DDL语句(create,drop,alter,truncate)都是显式带有commit命令,Oraclesp中不能有显式commit的存在。如果你非要在sp中建表或者删除表的话,你可以用动态SQL来完成隐式commit。因此下面的语句在sp中是错误的:

1Create table 表名 

2Drop table 表名

l         延迟存储过程的执行

    Microsoft SQL Server 提供了 WAITFOR,它允许开发人员指定触发语句块、存储过程或事务执行的时间、时间间隔或事件。它相当于 Oracle  dbms_lock.sleep

l         SQL Server sp形参需要指定长度(若不指定长度,则系统取默认长度,不一定得到你需要的值)Oraclesp形参不需要指定长度

下面两条语句执行结果一样:

MS SQL Server:

 

IF EXISTS (SELECT name

       FROM   sysobjects

       WHERE  name = N'VALID'

       AND   type = 'P')

    DROP PROCEDURE VALID

GO

 

CREATE PROCEDURE VALID

  @Yhbh CHAR(6),    --形参需要指定长度

  @Password VARCHAR(20),

  @Valid bit OUTPUT,

  @Invalid  VARCHAR(100) OUTPUT

WITH ENCRYPTION

AS     --所有的局部变量、游标和类型都在这里声明。要用declare

  DECLARE  @V BIT, @I VARCHAR(100)

  SELECT @V=valid,@I=invalid FROM MEMBER WHERE  id=@Yhbh AND password=@Password

  IF @V=0   

  BEGIN

    SET @Valid=@V     --赋值语句用SETSELECT

    SET @Invalid=@I

  END

  ELSE

  BEGIN

    SET @Valid=1

    SET @Invalid=''

  END

     

GO

 

Oracle:

CREATE OR REPLACE PROCEDURE VALID

 (

  Yhbh  CHAR,          --形参不需要指定长度

  Password1 VARCHAR2,

  Valid OUT NUMBER ,   --顺序和SQL Server相反,且输出参数的关键字为OUT

  Invalid  OUT VARCHAR2

)       --在程序中输出参数也要先输入值,否则出错?

AS      --所有的局部变量、游标和类型都在这里声明。用分号隔开。不用declare

  V NUMBER(1,0);

  I VARCHAR2(100);

BEGIN

  SELECT valid,invalid INTO V,I FROM MEMBER WHERE id=Yhbh AND password=Password1;

  IF V=0 THEN    --必须用INTO

  BEGIN

    Valid:=V;    --赋值语句用:=

    Invalid:=I;

  END;           --此处的语法和PASCAL也有区别

  ELSE

  BEGIN

    Valid:=1;

    Invalid:='';

  END;

  END IF;

END;

/

l         临时存储过程

   在 SQL Server 中,临时过程创建在 tempdb 数据库中,对于局部临时过程,在 procedure_name 前加一个数字符 (#),全局临时过程前加两个数字符 (##)。 局部临时过程只能由创建它的用户使用。运行局部临时过程的权限不能授予其他用户。用户会话结束后,局部临时过程自动被删除。所有的 SQL Server 用户均可使用全局临时过程。如果创建了全局临时过程,所有的用户均可以访问它,权限不能被显式地撤销。使用过程的最后一个用户会话结束后,全局临时过程被删除。

   临时存储过程在连接到 SQL Server 的早期版本时很有用,这些早期版本不支持再次使用 Transact-SQL 语句或批处理执行计划。连接到 SQL Server 2000 的应用程序应使用 sp_executesql 系统存储过程,而不使用临时存储过程。

 

下面的例子给出了,如何使用 T-SQL 存储过程替代 Oracle  PL/SQL 打包的函数。Transact-SQL 版本要简单得多,因为 SQL Server 可以直接从存储过程中的 SELECT 语句返回结果集,而无需使用游标。

Oracle

Microsoft SQL Server

CREATE OR REPLACE PACKAGE STUDENT_ADMIN.P1 AS ROWCOUNT NUMBER :=0;
CURSOR C1 RETURN STUDENT%ROWTYPE;
FUNCTION S_R_S
(WORKVAR OUT VARCHAR2) RETURN NUMBER;
END P1;
/

CREATE OR REPLACE PACKAGE BODY STUDENT_ADMIN.P1 AS CURSOR C1 RETURN STUDENT%ROWTYPE IS
  SELECT * FROM STUDENT 
  WHERE NOT EXISTS (SELECT 'X' FROM GRADE
  WHERE GRADE.SSN=STUDENT.SSN) ORDER BY SSN;

FUNCTION S_R_S
(WORKVAR OUT VARCHAR2) RETURN NUMBER IS
WORKREC STUDENT%ROWTYPE;
BEGIN
IF NOT C1%ISOPEN THEN OPEN C1;
ROWCOUNT :=0;
ENDIF;
FETCH C1 INTO WORKREC;
IF (C1%NOTFOUND) THEN
CLOSE C1;
ROWCOUNT :=0;
ELSE
WORKVAR := WORKREC.FNAME||' '||WORKREC.LNAME||
', social security number '||WORKREC.SSN||' is not enrolled 
in any classes!';
ROWCOUNT := ROWCOUNT + 1;
ENDIF;
RETURN(ROWCOUNT);

CREATE PROCEDURE S_R_S
AS

  SELECT FNAME+LNAME+SSN  FROM STUDENT S
  WHERE NOT EXISTS(SELECT 'X' FROM GRADE G WHERE G.SSN=S.SSN)
  ORDER BY SSN
  RETURN @@ROWCOUNT
GO

EXCEPTION
WHEN OTHERS THEN
IF C1%ISOPEN THEN CLOSE C1;
ROWCOUNT :=0;
ENDIF;
RAISE_APPLICATION_ERROR(-20001,SQLERRM);
END SHOW_RELUCTANT_STUDENTS;
END P1;
/

 

8、触发器

l         功能上的对比

MS SQL Server

       MS SQL Server仅有表的触发器,而且触发机制不够丰富,如插入触发器不区分单条插入还是多条插入,也不区分插入前触发还是插入后触发。碰到多条数据的插入,需要使用游标处理每条插入的数据。SQL Server的触发器都是在触发语句之后执行,而且是语句级的也就是说一条语句只能导致一次触发,而不管它有多少行纪录被更改,它靠虚拟表inserteddeleted的存在,确保了每行纪录都能被引用。

Oracle  

    Oracle提供的触发器不仅有基于表的触发器,而且还有其它类型的,例如数据库级的触发器,数据库启动、数据库关闭。对于表级的触发器,区分单条插入还是多条插入,也区分插入前触发还是插入后触发。Oracle的触发器分为触发语句之前执行或者触发语句之后执行,语句级或者行级组合起来的4种方式。

ORACLE产生数据库触发器的语法为:

create [or replace] trigger 触发器名 触发时间 触发事件  on  表名  [for each row]

其中:

触发器名:触发器对象的名称。由于触发器是数据库自动执行的,因此该名称只是一个名称,没有实质的用途。

触发时间:指明触发器何时执行,该值可取:

before--表示在数据库动作之前触发器执行;

after --表示在数据库动作之后出发器执行。

触发事件:指明哪些数据库动作会触发此触发器:

insert: 数据库插入会触发此触发器;

update: 数据库修改会触发此触发器;

delete: 数据库删除会触发此触发器。

表 名:数据库触发器所在的表。

for each row:对表的每一行触发器执行一次。如果没有这一选项,则只对整个表执行一次。

 

举例:下面的触发器在更新表auths之前触发,目的是不允许在周末修改表:

create  trigger  auth_secure  before  insert or update or delete //对整表更新前触发

on auths

begin

if(to_char(sysdate,'DY')='SUN'

RAISE_APPLICATION_ERROR(-20600,'不能在周末修改表auths');

end if;

end

 

l         将SQL Server的触发器移植至Oracle

SQL Server的触发器移植至Oracle上,需要创建一个触发语句之后的行级触发器,一定要用行级触发器而不能用语句级触发器.因为Oracle的触发器模式中没有像SQL Server那样的inserteddeleted虚表的存在,而只有:new:old:new:old不能算成是二维的表,它只是纪录字段更新之前的值和更新之后的值Oracle,游标的概念贯穿整个PL/SQL,游标在Oracle中被广泛应用.就连每一个update语句和delete语句都有一个内置的隐式的游标!因此,我们可以使用行级触发器加:old:new来达到SQL Server中的语句级触发器加inserteddeleted的效果.例程如下: (级联删除)

 

 SQL Server:

 create trigger Ca_Del on table1

 for delete

 as

   if @@rowcount=0

     return

   delete table2 from table2 t,deleted d

   where t.e0 = d.b0 and t.e1 = d.b1 and t.e2 = d.b2

   if @@error <> 0

     goto error_handler

   return

   error_handler:

   begin

    rollback transaction

    return

  end

 

Oracle:

create or replace trigger Ca_Del

AFTER delete

on table1

for EACH ROW

begin

  delete from table2

  where e0 = :OLD.b0 and e1 = :OLD.b1 and e2 = :OLD.b2;

  exception

  when others then

  rollback;

end Ca_Del;

9视图

 Microsoft SQL Server 中,用于创建视图的语法与 Oracle 相似。

Oracle

Microsoft SQL Server

CREATE [OR REPLACE] [FORCE | 
NOFORCE] VIEW [schema.]view_name

[(column_name [,column_name]...)]
AS select_statement

[WITH CHECK OPTION [CONSTRAINT
name]]
[WITH READ ONLY]

CREATE VIEW [ < database_name > .] [ < owner > .] view_name [ ( column [ ,...n ] ) ]

[ WITH < view_attribute > [ ,...n ] ]

AS select_statement

[ WITH CHECK OPTION ]

< view_attribute > ::=

    { ENCRYPTION | SCHEMABINDING | VIEW_METADATA }

SQL Server 的视图要求该表存在,并且视图所有者有权访问 SELECT 语句中所指定的表(与 Oracle FORCE 选项类似)。

默认情况下,并不检查视图上的数据修改语句,来确定受影响的行是否在视图的作用域内。要检查所有的修改,则使用 WITH CHECK OPTIONWITH CHECK OPTION 的主要差异在于,Oracle 将其定义为一个约束,而SQL Server 没有。其它方面,两者是相同的。

定义视图时,Oracle 提供了 WITH READ ONLY 选项。通过将 SELECT 权限仅授予视图用户,SQL Server 应用程序也可获得相同的结果。

当一个视图是由外部联接定义的,并使用该联接内表一个列上的限定条件进行查询时,SQL Server  Oracle 给出的结果可能不同。在大多数情况下,Oracle 视图可以方便地转成 SQL Server 视图。

 

Oracle

Microsoft SQL Server

CREATE  VIEW S_A.SGPA(SSN, GPA)
AS SELECT SSN, ROUND(AVG(DECODE(grade
,'A',  4
,'A+', 4.3
,'A-', 3.7
,0)
),2)
FROM S_A.GRADE
GROUP BY SSN

CREATE VIEW S_A.SGPA(SSN, GPA)
AS SELECT SSN, ROUND(AVG(CASE grade
WHEN 'A'  THEN 4
WHEN 'A+' THEN 4.3
WHEN 'A-' THEN 3.7
ELSE 0
END)
,2)
FROM S_A.GRADE
GROUP BY SSN

10关于游标

l         游标的简单定义

MS SQL Server

DECLARE abc CURSOR FOR SELECT * FROM authors

Oracle

DECLARE CURSOR abc IS  SELECT * FROM authors

 

    PL/SQL用游标来管理SQLselect语句。游标是为处理这些语句而分配的一大块内存。游标定义类似于其他变量。显式游标(Explicit Cursor)要声明(Declare),在使用前要打开(Open),使用完毕要关闭(Close)。使用隐式游标(Implicit Cursor)时,用户无需执行上述步骤,只要简单地编码select语句并让PL/SQL根据需要处理游标即可。与循环结构结合的显式游标处理返回多于一行的Select语句。与循环结合的游标将允许你每次处理一行。当Select语句预计只返回一行时,隐式游标将做得更好。

    使用隐式游标要注意以下几点:

&S226; 每个隐式游标必须有一个into

- -以下不正确

if this_value > 0 THEN

  select count(*) from person;

end if;

- -以下正确

if this_value > 0 then

  select count(*) into cnter from person;

end if;

&S226; 和显式游标一样,“into”后的变量类型要与表列的类型一致。

&S226; 隐式游标一次仅返回一行,使用时必须检查异常。

最常见的异常为

no_data_found”和“too_many_rows”。

. . . . . .

if counter>=10 then

begin

  select age into v_age from person where pin =pin_value;

  exception

  when too_many_rows then

insert into taba values(pin_value,sysdate);

when no_data_found then

  null;

end;

end if;

. . . . . .

/

隐式游标的一个例子:本例将通过某人社会保险号码查询其出生日期。

create or replace procedure get_dob(ss_num varchar2,dob out date)

as

begin                 -- 程序开始

  select birth_date   -- 隐式游标

  into dob            -- dobbirth_date的数据类型必须相同

  from person

  where soc_sec_num = ss_num;

exception when no_data_found then

  error_notify(ss_num);  --调用其他过程

end;

/

 

隐式游标和显式游标

隐式游标

显式游标

PL/SQL维护,当执行查询时自动打开和关闭

在程序中显式定义、打开、关闭,游标有一个名字。

游标属性前缀是SQL

游标属性的前缀是游标名

属性%ISOPEN总是为FALSE

%ISOPEN根据游标的状态确定值

SELECT语句带有INTO子串,只有一行数据被处理

可以处理多行数据,在程序中设置循环,取出每一行数据。

 

 

l         游标的详细语法

操作

Oracle

Microsoft SQL Server

声明游标

CURSOR cursor_name

 [(cursor_parameter(s))]
IS select_statement;

DECLARE  cursor_name

 CURSOR
[LOCAL | GLOBAL]
[FORWARD_ONLY | SCROLL]
[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
[TYPE_WARNING]
FOR select_statement

[FOR UPDATE [OFcolumn_name [,Un]]]

打开游标

OPEN cursor_name

 [(cursor_parameter(s))];

OPEN cursor_name

从游标中提取

FETCH cursor_name INTO

 variable(s)

FETCH [[NEXT | PRIOR | FIRST | LAST | ABSOLUTE {n | @nvar} | RELATIVE {n | @nvar}]
FROM] cursor_name

[INTO @variable(s)]

更新提取的行

UPDATE table_name
SET statement(s)U
WHERE  CURRENT  OF

 cursor_name;

UPDATE table_name
SET statement(s)U
WHERE  CURRENT   OF

 cursor_name

删除提取的行

DELETE FROM table_name 
WHERE  CURRENT  OF

 cursor_name;

DELETE FROM table_name 
WHERE  CURRENT   OF

 cursor_name

关闭游标

CLOSE cursor_name;

CLOSE cursor_name

删除游标数据结构

暂缺

DEALLOCATEcursor_name

其它

1、 CLOSE CURSOR 语句关闭并

释放所有的数据结构

2、只能向前移动,不能向后或相对滚动

1、 CLOSE CURSOR 语句关闭

游标,但数据结构仍可用于重新

打开游标。

2、 不支持 Oracle 的游标 FOR

循环语法

    

注:Oracle中更新和删除游标要注意以下事项

  UPDATEDELETE语句中的WHERE CURRENT OF子串专门处理要执行UPDATEDELETE操作的表中取出的最近的数据。要使用这个方法,在声明游标时必须使用FOR UPDATE子串,当对话使用FOR UPDATE子串打开一个游标时,所有返回集中的数据行都将处于行级(ROW-LEVEL)独占式锁定,其他对象只能查询这些数据行,不能进行UPDATEDELETESELECT...FOR UPDATE操作。

  语法:

FOR UPDATE [OF [schema.]table.column[,[schema.]table.column]..
[nowait]


  在多表查询中,使用OF子句来锁定特定的表,如果忽略了OF子句,那么所有表中选择的数据行都将被锁定。如果这些数据行已经被其他会话锁定,那么正常情况下ORACLE将等待,直到数据行解锁。

  在UPDATEDELETE中使用WHERE CURRENT OF子串的语法如下:

WHERE{CURRENT OF cursor_name|search_condition}


  例:

DELCARE

  CURSOR c1 IS SELECT empno,salary
  FROM emp
  WHERE comm IS NULL
  FOR UPDATE OF comm;

  v_comm NUMBER(10,2);

BEGIN

  FOR r1 IN c1 LOOP

  IF r1.salary<500 THEN
   v_comm:=r1.salary*0.25;
   ELSEIF r1.salary<1000 THEN
   v_comm:=r1.salary*0.20;
   ELSEIF r1.salary<3000 THEN
   v_comm:=r1.salary*0.15;
   ELSE
   v_comm:=r1.salary*0.12;
   END IF;

   UPDATE emp;
   SET comm=v_comm
   WHERE CURRENT OF c1l;

   END LOOP;
END

 

l         Oracle 的游标 FOR循环语法

示例1

DECLARE  
   CURSOR  C1  IS
   SELECT  VIEW_NAME  FROM ALL_VIEWS  WHERE  ROWNUM<=10 ORDER BY

  VIEW_NAME;
BEGIN
   FOR  I  IN  C1  LOOP
        DBMS_OUTPUT.PUT_LINE(I.VIEW_NAME);
   END LOOP;
  EXCEPTION WHEN OTHERS THEN
    NULL;
END;

示例2

. . .

declare

  cursor region_cur is  select first_name,last_name,ssn from person

  where region_number = region_number_in;

begin

  for region_rec in region_cur

  loop   --请注意:select语句中所有列自动地产生record_name.column_name

    if region_rec.ssn is null then          --(:region_rec.last_name)

      insert into e_msg values(pin_in,'no ssnum');

    else

      insert into e_tab values(pin_in,sysdate);

    end if;

  end loop;

end ;

. . .

/

l         Oracle中带参数的游标

与存储过程和函数相似,可以将参数传递给游标并在查询中使用。这对于处理在某种条件下打开游标的情况非常有用。它的语法如下:

CURSOR cursor_name[(parameter[,parameter],...)] IS select_statement;


  定义参数的语法如下:

Parameter_name [IN] data_type[{:=|DEFAULT} value]


  与存储过程不同的是,游标只能接受传递的值,而不能返回值。参数只定义数据类型,没有大小。 

  另外可以给参数设定一个缺省值,当没有参数值传递给游标时,就使用缺省值。游标中定义的参数只是一个占位符,在别处引用该参数不一定可靠。

  在打开游标时给参数赋值,语法如下:

OPEN cursor_name[value[,value]....];


  参数值可以是文字或变量。

  例:

DECLARE
    CURSOR C1(PAT  VARCHAR2)  IS
        SELECT  NAME  FROM ALL_VIEWS
        WHERE  NAME  LIKE  PAT||'%'  AND   ROWNUM<=10
        ORDER  BY  NAME;
    VNAME  VARCHAR2(40);
BEGIN
    FOR  I  IN  C1('USER_AR')  LOOP
        DBMS_OUTPUT.PUT_LINE(I.NAME);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(
);
    FOR  I  IN  C1('USER')     LOOP
        DBMS_OUTPUT.PUT_LINE(I.NAME);
    END LOOP;

EXCEPTION WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('AAA');
END;

 

l         Oracle游标的属性

 

属性

含量

%FOUND

布尔型属性,当最近一次读该记录时成功返回,则值为TRUE

%NOTFOUND

布尔型属性,它的值总与%FOUND属性的值相反

%ISOPEN

布尔型属性,当游标是打开时返回TRUE

%ROWCOUNT

数字型属性,返回已从游标中读取的记录数

注:隐式游标只使用SQL%FOUND,SQL%NOTFOUND,SQL%ROWCOUNT三个属性。

例程如下:

DECLARE
    CURSOR C1 IS SELECT VIEW_NAME FROM ALL_VIEWS
        WHERE ROWNUM<=10
        ORDER BY VIEW_NAME;
    VNAME VARCHAR2(40);
BEGIN
    OPEN C1;
    FETCH C1 INTO VNAME;
    WHILE  C1%FOUND  LOOP
        DBMS_OUTPUT.PUT_LINE(TO_CHAR(C1%ROWCOUNT)||' '||VNAME);
        FETCH C1 INTO VNAME;
    END LOOP;
END;

l         PL/SQL  T-SQL 中对等的游标语句。

Oracle

Microsoft SQL Server

DECLARE
  VSSN CHAR(9);
  VFNAME VARCHAR(12);
  VLNAME VARCHAR(20);

DECLARE
  @VSSN CHAR(9),
  @VFNAME VARCHAR(12),
  @VLNAME VARCHAR(20)

CURSOR curl IS
SELECT SSN, FNAME, LNAME
FROM STUDENT ORDER BY LNAME;
BEGIN
OPEN CUR1;
FETCH CUR1 INTO VSSN, VFNAME, VLNAME;
WHILE (CUR1%FOUND) LOOP
FETCH CUR1 INTO VSSN, VFNAME, VLNAME;
END LOOP;
CLOSE CUR1;
END;

DECLARE curl CURSOR FOR
SELECT SSN, FNAME, LNAME
FROM STUDENT ORDER BY SSN
OPEN CUR1
FETCH NEXT FROM CUR1
INTO @VSSN, @VFNAME, @VLNAME
WHILE (@@FETCH_STATUS <> -1)
BEGIN
FETCH NEXT FROM CUR1 INTO @VSSN, @VFNAME, @VLNAME
END
CLOSE CUR1
DEALLOCATE CUR1

11关于自增的identity如何移植

SQL Server中的 identity 特性给我们的工作带来了很大的方便,我们可以利用它方便的进行排序.但是在Oracle中却没有这样的特性.Oracle只有 sequence 的概念.sequence 是事务无关性的,sequence 并不是依附在表的上面,它是独立存在的,和事务是无关的。但是我们只有利用sequence 来达到SQL SERVER identity 的效果,比较麻烦。例程如下:

SQL Server:

create table T1 -- 应收票据变动

(

f0  TYPE_v_CMPID  not null, -- 公司类别(这是个自定义类型,下同。)

f1  varchar(20)   not null, -- 票据号码

f2  int IDENTITY  not null, -- 流水号

f3  TYPE_v_DATE   null , -- 状况处理日

f4  TYPE_v_ENUM   null , -- 状况

f6  varchar(20)   null , -- 传票管理编号

f7  varchar(2)    null , -- 票据变动类别

f8  varchar(20)   null , -- 票据变动单号

constraint PK_T1 primary key (f0, f1, f2)

)

移植至Oracle:

create table T1 -- 应收票据变动

(

f0  varchar2(3)  not null, -- 公司类别

f1  varchar2(20) not null, -- 票据号码

f2  int          not null, -- 流水号  /* SEQUENCE */

f3  varchar2(8)  null , -- 状况处理日

f4  varchar2(1)  null , -- 状况

f6  varchar2(20) null , -- 传票管理编号

f7  varchar2(2)  null , -- 票据变动类别

f8  varchar2(20) null , -- 票据变动单号

constraint PK_T1 primary key (f0, f1, f2)

);

CREATE SEQUENCE T1_seq  INCREMENT BY  1; //创建一个序列

 

create or replace TRIGGER T1_insert_before //增加一个insert之前的触发器

before insert on T1

for each row

declare

  i_id integer;

begin

  select T1_seq.nextval into i_id from dual;

  :NEW.f2 := i_id;

end;

    但是有一个地方需要注意:sequence 的值不能被手工重置, SQL Server 中可以通过

dbcc checkident(表名,reseed,0)或者truncate table tableName(如果数据也不需要的话)将表中的identity字段重置为1,Oraclesequence做不到这点,sequence只有达到最大值后,系统才会自动将其重置为预定的最小值。

Sequence的语法如下:建立一个最小为1,最大为999999999的一个序列号会自动循环的序列
create sequence 序列名 increment by 1 start with 1 maxvalue 999999999 cycle;
当向表中插入数据时,SQL语句如下:insert into 表名 values(序列名.nextval,1,2);

12Top n  RowNum

SQL Server中可以用top n 来返回指定数量的纪录,Oracle中与之对应的是 rownum,

是不同的是:SQL Servertop n 逻辑顺序返回的纪录,Oracle rownum 指定的是物理顺

序的序号,其物理的序号是在order by之前就已经决定好了的.因此下面的语句在Oracle中都是

错误的:

   错误语句1:

select rownum,fom01.*  from fom01 where rownum < 11 order by foa02d;

//这条语句永远也得不到正确的,因为 rownum 是物理顺序的序号,是在排序之前就已经决定好了的.

   纠正后的语句:

select rownum,ss.* from (select * from fom01 order by foa02d) ss where rownum < 11;

//据说Oracle8I以前的版本在子查询中不能使用order by

   错误语句2:

select rownum,fom01.* from fom01 where rownum = 2;

//这条语句将返回不了任何的值,但是将2改成1就可以返回一条纪录。因为Oraclerownum只有在生成了1后才能有2,同样的有了2才能有3,以此类推.

   纠正后的语句:

select * from (select rownum  r, fom01.*  from fom01  where rownum <= 2)

where r > 1;

 

 

rownum实现大于、小于逻辑(返回rownum410之间的数据)(minus操作,速度会受影

响)

SQL> select rownum,month,sell from sale where rownum<10

  2  minus

  3  select rownum,month,sell from sale where rownum<5;

 

13、执行动态SQL

一个动态SQL语句是在运行时而不是在编译时建立和执行的,在编译时,用户如果不了解查询的结构或者想要查询的对象,就可以使用动态SQL.但是一般而言,运行动态SQL的系统资源花费是运行同样的静态SQL的两到三倍.因为每次执行动态SQL的时候都必须重新对它进行语法分析.由于此点因素,尽可能的少使用动态SQL.

 

SQL Server:

SQL Serverexec('字符串')可以执行动态SQL,如果需要从执行的动态SQL中得到结果值的话可以用sp_executesql存储过程,例程如下:

   declare @count int

   declare @SQL nvarchar(200)

   set @SQL = N'select count(*) from sysobjects'

   exec sp_executesql @SQL,N'@i int output',@count output

   print @count

 

Oracle:

Oracle,可以使用DBMS_SQL包和execute immediate ‘……’来执行动态SQL,不过听说execute immediate ‘……’Oracle8及以前的版本中是不能使用的,例程如下:

   set serveroutput on

   /

   declare

     i_count int ;

     v_SQL varchar2(200) := 'begin select count(*) into :1 from user_objects; end;';

   begin

     execute immediate v_SQL using out i_count;

     dbms_output.put_line(i_count);

   end;

   /

   DBMS_SQL包使用起来较为复杂繁琐而且显式使用光标,不提倡使用,但有一点需要注意 execute immediate 所能执行的字符串的长度是有限制的,如果超过了这个限制,那么只能用DBMS_SQL包了.

 

EXECUTE IMMEDIATE 的使用技巧:

 

1.     EXECUTE IMMEDIATE  将不会提交一个DML事务执行,应该显式提交 如果通过EXECUTE

 IMMEDIATE处理DML命令,那么在完成以前需要显式提交或者作为EXECUTE  IMMEDIATE

自己的一部分。如果通过EXECUTE IMMEDIATE处理DDL命令,它提交所有以前改变的数据。

2.     不支持返回多行的查询,这种交互将用临时表来存储记录(参照例子如下)或者用REF

 cursors.

 3. 当执行SQL语句时,不要用分号,当执行PL/SQL块时,在其尾部用分号。

 

下面的例子展示了所有用到Execute immediate的可能方面,希望能给你带来方便:

1.PL/SQL中运行DDL语句:

begin
    execute immediate 'set role all';
 end;

 2. 给动态语句传值(USING 子句)

  declare
    l_depnam varchar2(20) := 'testing';
    l_loc    varchar2(10) := 'Dubai';
  begin
     execute immediate 'insert into dept values  (:1, :2, :3)'
                        using 50, l_depnam, l_loc;
     commit;
  end;

 3. 从动态语句检索值(INTO子句)

 declare
    l_cnt    varchar2(20);
 begin
    execute immediate 'select count(1) from emp'
                       into l_cnt;
    dbms_output.put_line(l_cnt);
 end;

3.     动态调用例程.例程中用到的绑定变量参数必须指定参数类型.黓认为IN类型,其它类型必须显式指定

  declare
     l_routin   varchar2(100) := 'gen2161.get_rowcnt';
     l_tblnam   varchar2(20) := 'emp';
     l_cnt       number;
     l_status   varchar2(200);
 begin
    execute immediate 'begin ' || l_routin || '(:2, :3, :4); end;'
         using in l_tblnam, out l_cnt, in out l_status;

    if l_status != 'OK' then
       dbms_output.put_line('error');
    end if;
 end;

5. 将返回值传递到PL/SQL记录类型;同样也可用%rowtype变量

 Declare

   type empdtlrec is record (empno  number(4),
                           ename  varchar2(20),
                           deptno  number(2));
    empdtl empdtlrec;
 begin
   execute immediate 'select empno, ename, deptno ' ||
                   'from emp where empno = 7934'
    into empdtl;
 end;

 6. 传递并检索值.INTO子句用在USING子句前

  declare
    l_dept    pls_integer := 20;
    l_nam     varchar2(20);
    l_loc     varchar2(20);
 begin
    execute immediate 'select dname, loc from dept where deptno = :1'
      into l_nam, l_loc
      using l_dept ;
 end;

7. 多行查询选项.对此选项用insert语句填充临时表,用临时表进行进一步的处理,也可以用REF cursors纠正此缺憾.
  declare
    l_sal   pls_integer := 2000;
 begin
    execute immediate 'insert into temp(empno, ename) ' ||
                   '          select empno, ename from emp ' ||
                   '          where  sal > :1'
    using l_sal;
    commit;
 end;

14函数的比较

l         数字/学函数

函数

Oracle

Microsoft SQL Server

绝对值

ABS

ABS

反余弦

ACOS

ACOS

反正弦

ASIN

ASIN

的反正切

ATAN

ATAN

m/n 的反正切

ATAN2

ATN2

>=值的最小整数

CEIL

CEILING

余弦

COS

COS

双曲余弦

COSH

COT

指数值

EXP

EXP

<=值的最大整数

FLOOR

FLOOR

自然对数

LN

LOG

以任何为底的对数

LOG(N)

暂缺

 10 为底的对数

LOG(10)

LOG10

模数(余数)

MOD

USE MODULO (%) OPERATOR

POWER

POWER

随机数

暂缺

RAND

舍入

ROUND

ROUND

数的符号

SIGN

SIGN

正弦

SIN

SIN

双曲正弦

SINH

暂缺

平方根

SQRT

SQRT

正切

TAN

TAN

双曲正切

TANH

暂缺

截尾

TRUNC

暂缺

列表中的最大数

GREATEST

暂缺

列表中的最小数

LEAST

暂缺

如果为 NULL,转换成数字

NVL

ISNULL

l         字符函数

函数

Oracle

Microsoft SQL Server

把字符转换成 ASCII

ASCII

ASCII

字符串串联

CONCAT

(表达式 + 表达式)

 ASCII 转换成字符

CHR

CHAR

返回字符串中的起始字符(从左)

INSTR

CHARINDEX

将字符转换成小写

LOWER

LOWER

将字符转换成大写

UPPER

UPPER

在字符串的左边填充字符

LPAD*

暂缺

删除前导空格

LTRIM

LTRIM

删除尾空格

RTRIM

RTRIM

字符串中模式的起始点

INSTR

PATINDEX

多次重复字符串

RPAD

REPLICATE

字符串的语音表示

SOUNDEX

SOUNDEX

重复空格的字符串

RPAD

SPACE

从数字数据转换而来的字符数据

TO_CHAR

STR

子字符串

SUBSTR***

SUBSTRINGLEFTRIGHT

字符替换

REPLACE

STUFF

字符串中每个词的第一个字母大写

INITCAP

暂缺

字符串转换

TRANSLATE

暂缺

字符串长度

LENGTH

DATELENGTH  LEN

列表中的最大字符串

GREATEST

暂缺

列表中的最小字符串

LEAST

暂缺

如果为 NULL,则转换字符串

NVL**

ISNULL

*LPAD(char1,n,char2)函数

解释:

字符char1按制定的位数n显示,不足的位数用char2字符串替换左边的空位

 

**NVL(EXPR1, EXPR2)函数

解释:   

IF EXPR1=NULL

         RETURN EXPR2

ELSE

         RETURN EXPR1

***SUBSTR

解释:它具有SQL ServerSUBSTRINGLEFTRIGHT三者的功能

SUBSTR('abcd',1,2) 返回ab

SUBSTR('abcd',-2,2) 返回cd

l         日期函数

函数

Oracle

Microsoft SQL Server

日期加

(日期列 +/- 值)或ADD_MONTHS

DATEADD

日期间的间隔

(日期列 +/- 值)或MONTHS_BETWEEN

DATEDIFF

当前日期和时间

SYSDATE

GETDATE()

月的最后一天

LAST_DAY

暂缺

时区转换

NEW_TIME

暂缺

该日期后的第一个工作日

NEXT_DAY

暂缺

日期的字符串表示

TO_CHAR*

DATENAME**

日期的整数表示

TO_NUMBER (TO_CHAR))*

DATEPART**

日期舍入

ROUND

CONVERT

日期截尾

TRUNC

CONVERT

字符串转换为日期

TO_DATE

CONVERT

如果为 NULL,则转换日期

NVL

ISNULL

*另:oracle常用日期数据格式(TO_CHARTO_NUMBER函数)


**SQL Server常用日期数据格式(DATENAMEDATEPART函数)

 

l         转换函数

函数

Oracle

Microsoft SQL Server

数字到字符

TO_CHAR

CONVERT

字符到数字

TO_NUMBER

CONVERT

日期到字符

TO_CHAR

CONVERT

字符到日期

TO_DATE

CONVERT

十六进制到二进制

HEX_TO_RAW

CONVERT

二进制到十六进制

RAW_TO_HEX

CONVERT

l         其它行级函数

函数

Oracle

Microsoft SQL Server

返回第一个非空表达式

DECODE

COALESCE

当前序列值

CURRVAL

暂缺

下一个序列值

NEXTVAL

暂缺

如果表达式 1 = 表达式 2,则返回空

DECODE

NULLIF

用户的登录 ID 

UID

SUSER_ID

用户的登录名

USER

SUSER_NAME

用户的数据库 ID 

UID

USER_ID ([ 'user'])

给定标识号的用户数据库用户名

USER

USER_NAME ([ id ])

当前用户

CURRENT_USER

CURRENT_USER

用户环境(审核记录)

USERENV

暂缺

CONNECT BY 子句的级别

LEVEL

暂缺

l         合计函数

函数

Oracle

Microsoft SQL Server

平均值

AVG

AVG

计数

COUNT

COUNT

最大值

MAX

MAX

最小值

MIN

MIN

标准偏差

STDDEV

STDEV  STDEVP

汇总

SUM

SUM

方差

VARIANCE

VAR  VARP

15索引的差异

l         聚集索引

    SQL Server 聚集索引和 Oracle 聚集索引没有任何相同之处。Oracle 聚集是两个或多个表的物理组合,这些表共享相同的数据块,并使用公共列作为聚集键。在 SQL Server 中,没有与 Oracle 聚集相似的结构。

 SQL Server 中,删除和重新创建聚集索引是重组表的一种常用技巧。使用这种方法,可以很容易地保证在磁盘上的页是连续的,且可以在表上方便地重建一些可用空间。这与 Oracle 中的导出、删除和导入表类似。原则上,在表上定义聚集索引可改善 SQL Server 性能和空间管理。如果不了解给定表的查询或更新模式,可在主键上创建聚集索引。 一旦在表上施加了 PRIMARY KEY  UNIQUE 约束,Microsoft SQL Server 就会自动为该表创建一个聚集索引。

请注意下表SQL Server 聚集索引的使用。

Oracle

Microsoft SQL Server

CREATE TABLE STUDENT (
SSN CHAR(9) NOT NULL,
CCODE VARCHAR2(4) NOT NULL,
GRADE VARCHAR2(2) NULL,
CONSTRAINT GRADE_SSN_CCODE_PK
PRIMARY KEY (SSN, CCODE)
CONSTRAINT GRADE_SSN_FK
FOREIGN KEY (SSN) REFERENCES
STUDENT_ADMIN.STUDENT (SSN),
CONSTRAINT GRADE_CCODE_FK
FOREIGN KEY (CCODE) REFERENCES
DEPT_ADMIN.CLASS (CCODE)
)

CREATE TABLE STUDENT (
SSN CHAR(9) NOT NULL,
CCODE VARCHAR(4) NOT NULL,
GRADE VARCHAR(2) NULL,
CONSTRAINT
GRADE_SSN_CCODE_PK
PRIMARY KEY CLUSTERED (SSN, CCODE),
CONSTRAINT GRADE_SSN_FK
FOREIGN KEY (SSN) REFERENCES
STUDENT_ADMIN.STUDENT (SSN),
CONSTRAINT GRADE_CCODE_FK
FOREIGN KEY (CCODE) REFERENCES
DEPT_ADMIN.CLASS (CCODE)
)

l         非聚集索引

在非聚集索引中,索引数据和表数据在物理上是分离的,且表中的行不按照索引的顺序存储。可以把 Oracle 索引定义迁移到 Microsoft SQL Server 非聚集索引定义(如下面例子所示)。但是,出于性能方面的考虑,可能希望选择给定表的一个索引,并把它创建为聚集索引。

Oracle

Microsoft SQL Server

CREATE INDEX
S_A.STUDENT_MAJOR_IDX
ON S_A.STUDENT (MAJOR)
TABLESPACE USER_DATA
PCTFREE 0
STORAGE (INITIAL 10K NEXT 10K
MINEXTENTS 1 MAXEXTENTS
UNLIMITED)

CREATE NONCLUSTERED INDEX
STUDENT_MAJOR_IDX
ON USER_DB.S_A.STUDENT (MAJOR)

l         索引的语法和命名

    在 Oracle 中,索引名在用户帐户中是唯一的。在 Microsoft SQL Server 中,索引名在表名称中必须是唯一的,但在用户帐户或数据库中则不一定是唯一的。因此,在 SQL Server 中创建或删除索引时,必须指明表的名称和索引名称。此外,SQL Server DROP INDEX 语句可以同时删除多个索引。

Oracle

Microsoft SQL Server

CREATE [UNIQUE] INDEX [schema].index_name
ON [schema.]table_name(column_name
[, column_name]...)
[INITRANS n]
[MAXTRANS n]
[TABLESPACE tablespace_name]
[STORAGE storage_parameters]
[PCTFREE n]
[NOSORT]
DROP INDEX ABC;

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]
INDEX index_name ON table (column [,Un])
[WITH
[PAD_INDEX]
[[,] FILLFACTOR = fillfactor]
[[,] IGNORE_DUP_KEY]
[[,] DROP_EXISTING]
[[,] STATISTICS_NORECOMPUTE]
]
[ON filegroup]
DROP INDEX USER_DB.STUDENT.DEMO_IDX, USER_DB.GRADE.DEMO_IDX

l         索引数据存储参数

Microsoft SQL Server  FILLFACTOR 选项和 Oracle 中的 PCTFREE 变量的作用基本相同。随着表的增大,索引页就会拆分,以容纳新数据。索引必须重新组织,来容纳新的数据值。填充因子百分比只在索引创建时使用,以后不再维护。

在索引最初创建时,FILLFACTOR 选项(值从 0  100 )控制索引页上保留多少空间。如果没有指定,使用默认的填充因子 0 - 这将完全填充索引叶级页,并在每个决定节点页上保留至少一个条目的空间(对于非唯一的聚集索引保留两个)。

    使用较低的填充因子值,最初会减少索引页的拆分,并增加 B 树索引结构中级别的数目。使用较高的填充因子值,可更有效地使用索引页空间,访问索引数据需要较少的磁盘 I/O,并减少了 B 树索引结构中级别的数目。

PAD_INDEX 选项指定了,将填充因子应用到索引的决定节点页以及数据页中。

尽管在 Oracle 中,必须调整 PCTFREE 参数来优化性能,但在 CREATE INDEX 语句中,一般不需要加入 FILLFACTOR 选项。填充因子可用于优化性能。仅当使用现有数据在表上创建新索引,并且能够准确预估该数据以后的变化时,填充因子才是有用的。

如果已经把 Oracle 索引的 PCTFREE 设为 0,则考虑使用值为 100 的填充因子。它用于不发生插入和更新的表(只读表)。填充因子设为 100 时,SQL Server 创建每页均百分之百填充的索引。

 

l         使用索引的注意事项 

oracle中, select,update,delete 语句中的子查询应当有规律地查找少于20%的表行.如果一个语句查找的行数超过总行数的20%,它将不能通过使用索引获得性能上的提高
    索引可能产生碎片,因为记录从表中删除时,相应也从表的索引中删除.表释放的空间可以再用,而索引释放的空间却不能再用.频繁进行删除操作的被索引的表,应当阶段性地重建索引,以避免在索引中造成空间碎片,影响性能.在许可的条件下,也可以阶段性地truncate,truncate命令删除表中所有记录,也删除索引碎片.

l         使用Oracle9i全索引扫描快速访问数据

Oracle9i增强了全索引SQL执行计划以支持基于功能的索引(function-based index)。一旦存在索引,Oracle就会绕过对表的访问,索引组织表(index-organized tableIOT)结构就是一个例子。在IOT结构中,所有的数据都载入索引的b-树结构,这样表(table)就成为一个多余的东西了。
    一旦Oracle SQL优化器检测到查询无需访问表时,Oracle就调用全索引扫描并快速读取每一个索引块而无需接触表本身。有一点很重要:全索引扫描并没有读取索引节点,而是一块一块的执行扫描并快速捕获索引节点。最好,Oracle调用多块读取功能,调用多个过程来读取表。
    为了加快表和索引的访问速度,Oracle使用了db_file_multiblock_read_count参数(默认参数为8)来辅助把全表扫描和全索引扫描所获得的数据块尽快送到数据缓冲区中。然而,这个参数只有当SQL查询执行全表扫描时才可用,并且,在绝大多数情况下,查询要使用索引来访问表。
    Oracle对全索引扫描有如下限制: 
SQL请求的全部列(column)必须驻留在索引树中;也就是说,SELECTWHERE字句中的所有数据列必须存在于索引中。 查询访问大量的行(row)。根据你查询的范围,比例变化范围为10%到25%之间,这个比例参数db_file_multiblock_read_count的设置和查询的并行程度极大的影响到这个比例。 由于索引节点并没有按索引顺序排列,所以列并没有顺序。这样,ORDER BY字句将要求附加的排序操作。 Oracle提供了一个SQL提示(hint)来强制全索引扫描。你也可以通过指定index_ffs提示来强制快速索引扫描,这常常与parallel_index提示组合来提高性能。例如,下面的查询强迫使用并行快速全索引扫描:
  select distinct /*+ index_ffs(c,pk_auto) parallel_index_
       (automobile, pk_auto) color, count(*) from  automobiles group by color;
由于涉及了所有的变量,所以全索引是否会加快查询速度并不能简单的加以回答。所以,大多数有经验的SQL调试者(tuner)会对符合快速全索引扫描标准的查询进行手工计时,看看使用全索引扫描的反映时间是否会降低。

函数索引基础

Oracle9i之前,全索引扫描只有当创建的索引没有空值时才可以使用,也就是说,Oracle建立索引时必须用一个NOT NULL子句才可以使用该索引。由于Oracle9i支持用函数(function-based)索引实现的唯索索引扫描,这种情况大大改观。

简单回归一下,函数索引是Oracle8的一个重要改进,因为它提供了一种有效的消除无必要长表全扫描的机制,由于函数索引可以在任何查询语句中的WHERE子句中精确复制,Oracle总会用一个索引来匹配上SQL查询的WHERE子句。

下面用一个简单的例子来解释全索引扫描如何与函数索引一起工作的过程。

创建student

(student_name varchar2(40), date_of_birth date);
 
使用这个表,创建与表中所有列相关联的函数索引。在本例中,该函数为initcap(即大写每
个单词的首字母)和to_char(即把一个数字变成字符): 
create index whole_student  on student
(initcap(student_name), to_char(date_of_birth,’MM-DD-YY’));

定义完函数索引后,Oracle9i中任何可以引用这些列(column)的SQL语句都可以使用全索引

扫描。下面是SQL匹配函数索引的查询例子:

select * from student  where initcap(student_name) = ‘Jones’;
select * from student  where to_char(date_of_birth,’MM-DD=YY’) = ’04-07-85’; 

用函数索引来调用全索引扫描

Oracle9i只要可能就会使用函数索引并在函数索引的基础上调用全索引扫描。如果SQL优化器统计结果表明使用全索引扫描的速度将会超过通过索引的b-树访问的速度,那么Oracle9i就会这么做。

下面是用函数索引调用唯索扫描的准则。所有的SQL谓词匹配索引中的列,查询必须从表中返回足够多的行,这样做的目的是为了让代价优化器(cost-based optimizer)来判断全索引扫描是否要比传统的索引访问方法要快。是否调用全索引扫描的决定取决于下面几个参数设置:

  • 代价优化器的适当统计数字——这个计划(schema)最近已经分析过了,而且optimizer_mode参数不能设置为RULE
  • 索引的并行程度——注意索引的并行程度是独立设置的;索引并没有继承表的并行程度。
  • optimizer_index_cost_adj的设置——它控制代价优化器是否倾向于全索引扫描。
  • db_file_multiblock_read_count的设置——这个参数影响到全索引扫描的代价。这个值越高,全索引扫描的代价也就会越便宜
  • 索引的直方图表示——对偏移(skewed)索引,它帮助代价优化器评估查询返回的行数。

Oracle的一个重要提高

在函数索引基础上的快速全索引扫描是Oracle9i的另一个提高性能的途径。当数据库迁移到Oracle9i时,许多数据库自动开始使用这个新的执行计划。不过,当SQL代价优化器决定是否选择全索引扫描时,还要考虑几个因素。Oracle专业人员需要适当设置参数,以确保代价优化器不会用不合适的方式使用快速全索引扫描——这一点需要特别注意。

 

16约束与完整性

l         约束共有五类:

  • NOT NULL 指定不接受 NULL 值的列。

所有的 SQL 脚本(不论是 Oracle 还是 SQL Server)都应该为每一列显式定义 NULL  NOT NULL。要了解这一策略是如何实施的,请参见 Oratable.sql  Sstable.sql 示例表创建脚本。如果没有显式定义,列的为空性遵循下列规则。

Null 设置

说明

使用用户定义的数据类型定义列

SQL Server 使用该数据类型创建时指定的为空性。使用sp_help 系统存储过程,来获取数据类型默认的为空性。

使用系统提供的数据类型定义列

如果系统提供的数据类型只有一个选择,则它优先。现在,bit数据类型只能定义为 NOT NULL
如果任何会话设置是 ON(使用 SET 打开),那么:
如果 ANSI_NULL_DFLT_ON  ON,则赋值 NULL
如果 ANSI_NULL_DFLT_OFF  ON,则赋值 NOT NULL
如果配置了任何数据库设置(用 sp_dboption 系统存储过程更改),那么:
如果 ANSI null default 为 true,则赋值 NULL
如果 ANSI null default 为 false,则赋值 NOT NULL

NULL/NOT NULL 
没有定义

如果没有显式地定义(没有设置任何 ASNI_NULL_DFLT 选项),会话没有改变,并且数据库设为默认值(ANSI null default  false),那么,SQL Server 赋值为 NOT NULL

 

  • CHECK 约束对可以放入列中的值进行限制,以强制执行域的完整性。

 Oracle  SQL Server 中,定义 CHECK 约束的语法是相同的。搜索条件必须对一个布尔表达式进行求值,并且不能包括子查询。列级 CHECK 约束只能引用受约束的列,表级 CHECK 约束只可以引用受约束表中的列。可以为一个表定义多个 CHECK 约束。在 CREATE TABLE 语句中,SQL Server 语法规定,在一个列上只允许创建一个列级 CHECK 约束,约束可以有多个条件。

Oracle

Microsoft SQL Server

CREATE TABLE STUDENT_ADMIN.STUDENT (
SSN CHAR(9) NOT NULL,
FNAME VARCHAR2(12) NULL,
LNAME VARCHAR2(20) NOT NULL,
GENDER CHAR(1) NOT NULL
CONSTRAINT
STUDENT_GENDER_CK
CHECK (GENDER IN ('M','F')),

MAJOR VARCHAR2(4)
DEFAULT 'Undc' NOT NULL,

BIRTH_DATE DATE NULL,
TUITION_PAID NUMBER(12,2) NULL,
TUITION_TOTAL NUMBER(12,2) NULL,
START_DATE DATE NULL,
GRAD_DATE DATE NULL,
LOAN_AMOUNT NUMBER(12,2) NULL,
DEGREE_PROGRAM CHAR(1)
DEFAULT 'U' NOT NULL
CONSTRAINT
STUDENT_DEGREE_CK CHECK
(DEGREE_PROGRAM IN ('U', 'M', 'P', 'D')),

...

CREATE TABLE USER_DB.STUDENT
_ADMIN.STUDENT (
SSN CHAR(9) NOT NULL,
FNAME VARCHAR(12) NULL,
LNAME VARCHAR(20) NOT NULL,
GENDER CHAR(1) NOT NULL
CONSTRAINT STUDENT_GENDER_CK
CHECK (GENDER IN ('M','F')),

MAJOR VARCHAR(4)
DEFAULT 'Undc' NOT NULL,

BIRTH_DATE DATETIME NULL,
TUITION_PAID NUMERIC(12,2) NULL,
TUITION_TOTAL NUMERIC(12,2) NULL,
START_DATE DATETIME NULL,
GRAD_DATE DATETIME NULL,
LOAN_AMOUNT NUMERIC(12,2) NULL,
DEGREE_PROGRAM CHAR(1)
DEFAULT 'U' NOT NULL
CONSTRAINT STUDENT_DEGREE_CK 
CHECK
(DEGREE_PROGRAM IN ('U', 'M', 
'P','D')),

...

SQL Server的另一个例子如下:

下例显示名为 chk_id 约束的创建,该约束确保只对此关键字输入指定范围内的数字,以进一步强制执行主键的域。

CREATE TABLE cust_sample
    (
    cust_id                int        PRIMARY KEY,
    cust_name            char(50),
    cust_address            char(50),
    cust_credit_limit    money,
    CONSTRAINT chk_id CHECK (cust_id BETWEEN 0 and 10000 )
    )
  • UNIQUE 约束在列集内强制执行值的唯一性。

对于 UNIQUE 约束中的列,表中不允许有两行包含相同的非空值。主键也强制执行唯一性,但主键不允许空值。UNIQUE 约束优先于唯一索引。

对于完全唯一键(单个或多个列索引),SQL Server 只允许一行包含 NULL 值,而 Oracle 允许任意数量的行包含 NULL 值。 //????????

Oracle

Microsoft SQL Server

CREATE TABLE DEPT_ADMIN.DEPT
(DEPT VARCHAR2(4) NOT NULL,
DNAME VARCHAR2(30) NOT NULL,
CONSTRAINT DEPT_DEPT_PK
PRIMARY KEY (DEPT)
USING INDEX TABLESPACE
USER_DATA
PCTFREE 0 STORAGE (
INITIAL 10K NEXT 10K
MINEXTENTS 1 MAXEXTENTS UNLIMITED),
CONSTRAINT DEPT_DNAME_UNIQUE
UNIQUE (DNAME)
USING INDEX TABLESPACE USER_DATA
PCTFREE 0 STORAGE (
INITIAL 10K NEXT 10K
MINEXTENTS 1 MAXEXTENTS
UNLIMITED)
)

CREATE TABLEUSER_DB.DEPT_ADMIN.DEPT
(DEPT VARCHAR(4) NOT NULL,
DNAME VARCHAR(30) NOT NULL,
CONSTRAINT DEPT_DEPT_PK
PRIMARY KEY CLUSTERED (DEPT),
CONSTRAINT DEPT_DNAME_UNIQUE
UNIQUE NONCLUSTERED (DNAME)
)

 

  • PRIMARY KEY 约束标识列或列集,这些列或列集的值唯一标识表中的行。

在一个表中,不能有两行包含相同的主键值。不能在主键内的任何列中输入 NULL 值。在数据库中 NULL 是特殊值,代表不同于空白和 0 值的未知值。建议使用一个小的整数列作为主键。每个表都应有一个主键。

一个表中可以有一个以上的列组合,这些组合能唯一标识表中的行,每个组合就是一个候选键。数据库管理员从候选键中选择一个作为主键。例如,在 part_sample 表中,part_nmbr 和 part_name 都可以是候选键,但是只将 part_nmbr 选作主键。

CREATE TABLE part_sample
            (part_nmbr        int            PRIMARY KEY,
            part_name        char(30),
            part_weight        decimal(6,2),
            part_color        char(15) )
  • FOREIGN KEY 约束标识表之间的关系。

一个表的外键指向另一个表的候选键。当外键值没有候选键时,外键可防止操作保留带外键值的行。在下例中,order_part 表建立一个外键引用前面定义的 part_sample 表。通常情况下,order_part  order 表上也有一个外键,下面只不过是一个简单示例。

CREATE TABLE order_part
        (order_nmbr        int,
        part_nmbr        int
            FOREIGN KEY REFERENCES part_sample(part_nmbr)
                ON DELETE NO ACTION,
        qty_ordered        int)
GO

如果一个外键值没有候选键,则不能插入带该值(NULL 除外)的行。如果尝试删除现有外键指向的行,ON DELETE 子句将控制所采取的操作。ON DELETE 子句有两个选项:

    • NO ACTION 指定删除因错误而失败。
    • CASCADE 指定还将删除包含指向已删除行的外键的所有行。

如果尝试更新现有外键指向的候选键值,ON UPDATE 子句将定义所采取的操作。它也支持 NO ACTION  CASCADE 选项。

l         列约束和表约束

约束可以是列约束或表约束:

  • 列约束被指定为列定义的一部分,并且仅适用于那个列。
  • 表约束的声明与列的定义无关,可以适用于表中一个以上的列。

当一个约束中必须包含一个以上的列时,必须使用表约束。

例如,如果一个表的主键内有两个或两个以上的列,则必须使用表约束将这两列加入主键内。假设有一个表记录工厂内的一台计算机上所发生的事件。假定有几类事件可以同时发生,但不能有两个同时发生的事件属于同一类型。这一点可以通过将 type 列和 time 列加入双列主键内来强制执行。

CREATE TABLE factory_process
   (event_type   int,
   event_time   datetime,
   event_site   char(50),
   event_desc   char(1024),
CONSTRAINT event_key PRIMARY KEY (event_type, event_time) )
 

l         增加和删除约束

禁用约束可以提高数据库性能和简化数据复制过程。例如,在远程站点重建或复制表数据时,不需要再重复约束检查,因为数据最初插到表中时,数据完整性已经检查过了。可以编写一个 Oracle 应用程序,禁用或启用约束(除 PRIMARY KEY  UNIQUE 外)。在 Microsoft SQL Server 中,将 ALTER TABLE 语句与 CHECK  WITH ONCHECK 选项一起使用,也可实现上述过程。

 SQL Server 中,可以使用 NOCHECK 子句和 ALL 关键字延迟所有的表约束。

如果 Oracle 应用程序要使用 CASCADE 选项禁用或删除 PRIMARY KEY  UNIQUE 约束,则可能需要重写一些代码,因为 CASCADE 选项禁用或删除父约束及其相关的任何子完整性约束。

下面是该语法的一个示例:

DROP CONSTRAINT DEPT_DEPT_PK CASCADE

必须修改 SQL Server 应用程序,使其先删除子约束,然后再删除父约束。例如,要删除 DEPT 表上的 PRIMARY KEY 约束,必须删除列 STUDENT.MAJOR  CLASS.DEPT 上的外键。下面是该语法的一个示例:

ALTER TABLE STUDENT
DROP CONSTRAINT STUDENT_MAJOR_FK
ALTER TABLE CLASS
DROP CONSTRAINT CLASS_DEPT_FK
ALTER TABLE DEPT
DROP CONSTRAINT DEPT_DEPT_PK

尽管 IDENTITY 属性在一个表中自动完成行编号,但不同的表(每个表均有其自己的标识符列)的属性值可能会相同。这是因为,IDENTITY 属性只保证在使用它的表中唯一。如果应用程序必须生成一个标识符列,其在整个数据库中、或者甚至每个联网计算机上的每个数据库中都是唯一,则使用 ROWGUIDCOL 属性、uniqueidentifier 数据类型和 NEWID 函数。SQL Server 使用全局唯一标识符列,来合并复制,确保在表的多个副本中,行被唯一地标识。

对于给定列,范围完整性实施了有效的条目。范围完整性是通过限制可能值的类型(通过数据类型)、格式(通过 CHECK 约束)或范围(通过 REFERENCE  CHECK 约束)实施的。

l         DEFAULT

Oracle 把默认值作为列属性,而 Microsoft SQL Server 把默认值作为约束。SQL Server DEFAULT 约束可以包含常量、不带参数的内置函数(niladci 函数)或 NULL

要方便地迁移 Oracle DEFAULT 列属性,应该在 SQL Server 列级中定义 DEFAULT 约束,而不必使用约束名称。对于每个 DEFAULT 约束,SQL Server 均生成一个唯一的名称。

l         下表提供了一个用于定义引用完整性约束的语法比较。

约束

Oracle

Microsoft SQL Server

PRIMARY KEY

[CONSTRAINTconstraint_name]
PRIMARY KEY (col_name[, col_name2 [..., col_name16]])
[USING INDEXstorage_parameters]

[CONSTRAINTconstraint_name]
PRIMARY KEY [CLUSTERED | NONCLUSTERED] (col_name[, col_name2 [...,col_name16]])
[ON segment_name]
[NOT FOR REPLICATION]

UNIQUE

[CONSTRAINTconstraint_name]
UNIQUE (col_name [, col_name2 [...,col_name16]])
[USING INDEXstorage_parameters]

[CONSTRAINTconstraint_name]
UNIQUE [CLUSTERED | NONCLUSTERED](col_name [,col_name2 [...,col_name16]])
[ON segment_name]
[NOT FOR REPLICATION]

FOREIGN KEY

[CONSTRAINTconstraint_name]
[FOREIGN KEY (col_name[, col_name2 [...,col_name16]])]
REFERENCES [owner.]ref_table [(ref_col[, ref_col2 [...,ref_col16]])]
[ON DELETE CASCADE]

[CONSTRAINTconstraint_name]
[FOREIGN KEY (col_name [,col_name2 [...,col_name16]])]
REFERENCES [owner.]ref_table [(ref_col [,ref_col2 [..., ref_col16]])]
[NOT FOR REPLICATION]

DEFAULT

列属性,但不是约束
DEFAULT (constant_expression)

[CONSTRAINTconstraint_name]
DEFAULT {constant_expression |niladic-function | NULL}
[FOR col_name]
[NOT FOR REPLICATION]

CHECK

[CONSTRAINTconstraint_name]
CHECK (expression)

[CONSTRAINTconstraint_name]
CHECK [NOT FOR REPLICATION] (expression)

NOT FOR REPLICATION 子句用于复制过程中暂停列级、FOREIGN KEY  CHECK 约束的使用。

 

l         数据完整性

实体完整性

实体完整性将行定义为特定表的唯一实体。实体完整性强制表的标识符列或主键的完整性(通过索引、UNIQUE 约束、PRIMARY KEY 约束或 IDENTITY 属性)。

域完整性

域完整性是指给定列的输入有效性。强制域有效性的方法有:限制类型(通过数据类型)、格式(通过 CHECK 约束和规则)或可能值的范围(通过 FOREIGN KEY 约束、CHECK 约束、DEFAULT 定义、NOT NULL 定义和规则)。

引用完整性

在输入或删除记录时,引用完整性保持表之间已定义的关系。在 Microsoft® SQL Server&S482; 2000 中,引用完整性基于外键与主键之间或外键与唯一键之间的关系(通过 FOREIGN KEY  CHECK 约束)。引用完整性确保键值在所有表中一致。这样的一致性要求不能引用不存在的值,如果键值更改了,那么在整个数据库中,对该键值的所有引用要进行一致的更改。

强制引用完整性时,SQL Server 禁止用户进行下列操作:

  • 当主表中没有关联的记录时,将记录添加到相关表中。
  • 更改主表中的值并导致相关表中的记录孤立。
  • 从主表中删除记录,但仍存在与该记录匹配的相关记录。

例如,对于 pubs 数据库中的 sales 和 titles 表,引用完整性基于 sales 表中的外键 (title_id与 titles 表中的主键 (title_id之间的关系。

17优化 SQL 语句

 

以下是针对Oracle的一些优化方案,对SQL Server也基本一致:

SQL语句编写注意问题

在多数情况下,Oracle使用索引来更快地遍历表,优化器主要根据定义的索引来提高性能。但是,如果在SQL语句的where子句中写的SQL代码不合理,就会造成优化器删去索引而使用全表扫描,一般就这种SQL语句就是所谓的劣质SQL语句。在编写SQL语句时我们应清楚优化器根据何种原则来删除索引,这有助于写出高性能的SQL语句。

下面就某些SQL语句的where子句编写中需要注意的问题作详细介绍。在这些where子句中,即使某些列存在索引,但是由于编写了劣质的SQL,系统在运行该SQL语句时也不能使用该索引,而同样使用全表扫描,这就造成了响应速度的极大降低。

1. IS NULL  IS NOT NULL

不能用null作索引,任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下,只要这些列中有一列含有null,该列就会从索引中排除。也就是说如果某列存在空值,即使对该列建索引也不会提高性能。

任何在where子句中使用is nullis not null的语句优化器是不允许使用索引的。

2. 联接列

对于有联接的列,即使最后的联接值为一个静态值,优化器是不会使用索引的。我们一起来看一个例子,假定有一个职工表(employee),对于一个职工的姓和名分成两列存放(FIRST_NAMELAST_NAME),现在要查询一个叫比尔.克林顿(Bill Cliton)的职工。

下面是一个采用联接查询的SQL语句,?????????

select * from employss
 where
 first_name||''||last_name ='Beill Cliton';

上面这条语句完全可以查询出是否有Bill Cliton这个员工,但是这里需要注意,系统优化器对基于last_name创建的索引没有使用。

当采用下面这种SQL语句的编写,Oracle系统就可以采用基于last_name创建的索引。

Select * from employee
 where
 first_name ='Beill' and last_name ='Cliton';

 

遇到下面这种情况又如何处理呢?如果一个变量(name)中存放着Bill Cliton这个员工的姓名,对于这种情况我们又如何避免全程遍历,使用索引呢?可以使用一个函数,将变量name中的姓和名分开就可以了,但是有一点需要注意,这个函数是不能作用在索引列上。下面是SQL查询脚本:

select * from employee
 where
 first_name = SUBSTR('&&name',1,INSTR('&&name',' ')-1)
 and
 last_name = SUBSTR('&&name',INSTR('&&name’,' ')+1)

 

3. 带通配符(%)的like语句

同样以上面的例子来看这种情况。目前的需求是这样的,要求在职工表中查询名字中包含cliton的人。可以采用如下的查询SQL语句:

Select  *  from  employee  where  last_name  like  '%cliton%';

这里由于通配符(%)在搜寻词首出现,所以Oracle系统不使用last_name的索引。在很多情况下可能无法避免这种情况,但是一定要心中有底,通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时,优化器就能利用索引。在下面的查询中索引得到了使用:

Select  *  from  employee  where  last_name  like 'c%';

 

4. Order by语句

ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制,也可以将函数加入列中(象联接或者附加等)。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。

仔细检查order by语句以找出非索引项或者表达式,它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引,也可以为所使用的列建立另外一个索引,同时应绝对避免在order by子句中使用表达式。

5. NOT

我们在查询时经常在where子句使用一些逻辑表达式,如大于、小于、等于以及不等于等等,也可以使用and(与)、or(或)以及not(非)。NOT可用来对任何逻辑运算符号取反。下面是一个NOT子句的例子:

... where not (status ='VALID')

 

如果要使用NOT,则应在取反的短语前面加上括号,并在短语前面加上NOT运算符。NOT运算符包含在另外一个逻辑运算符中,这就是不等于(<>)运算符。换句话说,即使不在查询where子句中显式地加入NOT词,NOT仍在运算符中,见下例:

... where status <>'INVALID';

 

再看下面这个例子:

select * from employee where  salary<>3000;

对这个查询,可以改写为不使用NOT

select * from employee where  salary<3000 or salary>3000;

虽然这两种查询的结果一样,但是第二种查询方案会比第一种查询方案更快些。第二种查询允许Oraclesalary列使用索引,而第一种查询则不能使用索引。

6.  INEXISTS

有时候会将一列和一系列值相比较。最简单的办法就是在where子句中使用子查询。在where子句中可以使用两种格式的子查询。

第一种格式是使用IN操作符:

... where  column  in  (select  *  from ... where ...);

第二种格式是使用EXIST操作符:

... where  exists  (select  'X'   from ...where ...);

我相信绝大多数人会使用第一种格式,因为它比较容易编写,而实际上第二种格式要远比第一种格式的效率高。在Oracle中可以几乎将所有的IN操作符子查询改写为使用EXISTS的子查询。

第二种格式中,子查询以‘select 'X'开始。运用EXISTS子句不管子查询从表中抽取什么数据它只查看where子句。这样优化器就不必遍历整个表而仅根据索引就可完成工作(这里假定在where语句中使用的列存在索引)。相对于IN子句来说,EXISTS使用相连子查询,构造起来要比IN子查询困难一些。

通过使用EXISTOracle系统会首先检查主查询,然后运行子查询直到它找到第一个匹配项,这就节省了时间。Oracle系统在执行IN子查询时,首先执行子查询,并将获得的结果列表存放在在一个加了索引的临时表中。在执行子查询之前,系统先将主查询挂起,待子查询执行完毕,存放在临时表中以后再执行主查询。这也就是使用EXISTS比使用IN通常查询速度快的原因。

同时应尽可能使用NOT EXISTS来代替NOT IN,尽管二者都使用了NOT(不能使用索引而降低速度),NOT EXISTS要比NOT IN查询效率更高。

 

7.  其它

SET 语句可以为工作会话期、触发器或存储过程运行期设定 SQL Sever 查询处理选项。

SET FORCEPLAN ON 语句强制优化程序按照表在 FROM 子句中出现的顺序处理联接,类似 Oracle 优化程序中使用的 ORDERED 提示。

SET SHOWPLAN_ALL  SET SHOWPLAN_TEXT 语句只返回查询或语句的执行计划信息,但不执行查询或语句。要运行查询或语句,将相应的显示计划语句设为 OFF。然后,查询或语句就会执行。SHOWPLAN 选项与 Oracle EXPLAIN PLAN 工具提供的结果类似。

使用 SET STATISTICES PROFILE ON,每个执行的查询返回标准的结果集,然后,返回附加结果集(给出查询执行的事件探查)。其它选项包括 SET STATISTICS IO  SET STATISTIECS TIME

Transact-SQL 语句处理包括分两步,即编译和执行。NOEXEC 选项编译每个查询,但不执行。NOEXEC 设为 ON 时,不执行随后的语句(包括其它 SET 语句),直到 NOEXEC 设为 OFF 为止。

SET SHOWPLAN ON
SET NOEXEC ON
go
SELECT * FROM DEPT_ADMIN.DEPT,
STUDENT_ADMIN.STUDENT
WHERE MAJOR = DEPT
go
STEP 1
The type of query is SETON
STEP 1
The type of query is SETON
STEP 1
The type of query is SELECT
FROM TABLE
DEPT_ADMIN.DEPT
Nested iteration
Table Scan
FROM TABLE
STUDENT_ADMIN.STUDENT
Nested iteration
Table Scan

Oracle 需要使用提示,来调整基于开销的优化程序的操作和性能。Microsoft SQL Server 基于开销的优化程序不需要使用提示,来协助其查询评估过程。但是在某些情况下,确有使用它们的必要。

INDEX = {index_name | index_id} 提示指定了该表使用的索引名或 ID index_id  0,就会强制一个表扫描,而当 index_id  1,则强制使用聚集索引(如存在)。这和 Oracle 中使用的索引提示类似。

如果其列顺序和 ORDER BY 子句匹配,SQL Server FASTFIRSTROW 提示就会指示优化程序使用非聚集索引。这个提示的运行方式和 Oracle FIRST_ROWS 提示类似。

 

18、异常的处理

Oracle:

l         预定义异常

异常名称

ORACLE错误

说明

CURSOR_ALREADY_OPEN

ORA-6511

试图打开一个已打开的光标

DUP_VAL_ON_INDEX

ORA-0001

试图破坏一个唯一性限制

INVALID_CURSOR

ORA-1001

试图使用一个无效的光标

INVALID_NUMBER

ORA-1722

试图对非数字值进行数字操作

LOGIN_DENIED

ORA-1017

无效的用户名或者口令

NO_DATA_FOUND

ORA-1403

查询未找到数据

NOT_LOGGED_ON

ORA-1012

还未连接就试图数据库操作

PROGRAM_ERROR

ORA-6501

内部错误

ROWTYPE_MISMATCH

ORA-6504

主变量和光标的类型不兼容

STORAGE_ERROR

ORA-6500

内部错误

TIMEOUT_ON_RESOURCE

ORA-0051

发生超时

TOO_MANY_ROWS

ORA-1422

SELECT INTD命令返回的多行

TRANSACTION_BACKED_OUT

ORA-006

由于死锁提交被退回

VALUE_ERROR

ORA-6502

转换或者裁剪错误

ZERO_DIVIDE

ORA-1476

试图被零除

例如:
DECLARE
    X  NUMBER;
BEGIN
    X:= 'yyyy';--这里有错
EXCEPTION   WHEN VALUE_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('EXCEPTION HANDED');
END;

l         自定义异常

DECLARE                     
    BAD_ROWID  EXCEPTION;    
    X  ROWID;
PRAGMA EXCEPTION_INIT(BAD_ROWID,-01445);
BEGIN
    SELECT  ROWID  INTO  X  FROM  TAB
    WHERE  ROWNUM=1;
EXCEPTION WHEN BAD_ROWID THEN
    DBMS_OUTPUT.PUT_LINE('CANNOT QUERY ROWID FROM THIS VIEW');
END;
    注意:-01445 因为PRAGMA EXCEPTION_INIT命令把这个变量(-01455)连接到
这个ORACLE错误,该语句的语法如下:
   
 PRAGMA EXCEPTION_INIT(exception_name, error_number);
其中error_number是负数,因为错误号被认为负数,当定义错误时记住使用负号

    异常不一定必须是oracle返回的系统错误,用户可以在自己的应用程序中创
建可触发及可处理的自定义异常
DECLARE                                   // 定义异常处理
    SALARY_CODE VARCHAR2(1);
    INVALID_SALARY_CODE  EXCEPTION;        // INVALID_SALARY_CODE 是异常名
BEGIN
    SALARY_CODE:='X';
    IF SALARY_CODE NOT IN('A', 'B', 'C') THEN
       RAISE INVALID_SALARY_CODE;          // 触发异常处理
    END IF;
  EXCEPTION WHEN INVALID_SALARY_CODE THEN //
处理异常
      DBMS_OUTPUT.PUT_LINE('INVALID SALARY CODE');
END;

19子查询

有三种基本的子查询。它们是:

  • 通过无修改的比较运算符引入,并且必须返回单个值。(单列单行)

Oracle :单列单行子查询只能用在Where子句中。

SQL Server可以用在任何表达式中。如:

select title_id,qty,convert(float,qty)/convert(float,(select sum(qty) from sales)) from sales

  • 在通过 IN 引入的列表或者由 ANY  ALL 修改的比较运算符的列表上进行操作。(单列多行)

OracleSQL Server一样:只能用在Where子句中。

  • 多列子查询

SQL Server:分两种情况。一种情况是该子查询用在Where子句中,且使用关键字exists来作为是否存在的条件;另外一种情况是用在FROM子句中,作为一个导出表使用。如:

select * from titles where exists (select title_id,qty from sales)

   Oracle语法如下:

     SELECT col,col,

  FROM table 

WHERE (col,col,) IN (SELECT col,col, FROM table WHERE condition)

分两种情况。一种情况是成对比较的多列子查询,表示多个列同时相等,才称为满足条件,如:

SELECT ename,deptno,sal,comm

FROM emp 

WHERE (sal,NVL(comm,-1)) IN

(SELECT sal,NVL(comm,-1) FROM emp WHERE deptno=30)

 

另外一种情况是非成对比较的多列子查询,表示不是成对相等,但也称为满足条件,如:

SELECT ename,deptno,sal,comm

FROM emp 

WHERE sal IN

(SELECT sal FROM emp WHERE deptno=30)

AND  NVL(comm,-1) IN

(SELECT NVL(comm,-1) FROM emp WHERE deptno=30)

 

另外,还可以在FROM子句中使用子查询。

 

 

20其它语法的比较

l        锁定请求的行

    Oracle 使用 FOR UPDATE 子句来锁定 SELECT 命令中指定的行。不需要在 Microsoft SQL Server 中使用对等的子句,因为这是默认行为。

l         行合计和 COMPUTE 子句

    SQL Server  COMPUTE 子句用于生成行合计函数(SUMAVGMINMAX  COUNT),它们在查询结果中作为附加行出现。它允许查看一组结果的详细和汇总信息行。可以计算子组的汇总值,以及计算同一组的多个合计函数。

    Oracle  SELECT 命令语法不支持 COMPUTE 子句。但是,SQL Server  COMPUTE 子句与 Oracle SQL*Plus 查询工具中的 COMPUTE 命令作用相似。

l        读取和修改 BLOB

     SQL Server 使用 text  image 列,来实现二进制大型对象 (BLOB)Oracle 使用 LONG  LONG RAW 列实现 BLOB

    在 Oracle 中,SELECT 命令可以查询 LONG  LONG RAW 列中的值。 在 SQL Server 中,可以使用标准的 T-SQL 语句或专门的 READTEXT 语句读取 text  image 列中的数据,READTEXT 语句允许读取text  image 列的部分片段。Oracle 没有提供处理 LONG  LONG RAW 部分片段的对等语句。

    在 Oracle 中,UPDATE  INSERT 命令用于更改 LONG  LONG RAW 列中的值。在 SQL Server 中,可以使用标准的 UPDATE  INSERT 语句,也可以使用 UPDATETEXT  WRITETEXT 语句。UPDATETEXT  WRITETEXT 均允许无日志记录的选项,并且 UPDATETEXT 允许对 text  image 列进行部分更新。

l        条件测试

Oracle  DECODE 语句和 Microsoft SQL Server CASE 表达式都执行条件测试。当 test_value 中的值符合下列任何表达式时,就会返回相关的值。如果不符合,则返回 default_value。如果没有指定 default_value,且不符合任何表达式,则 DECODE  CASE 返回 NULL。下面给出了语法以及一个转换的 DECODE 命令的示例。 注意:Oracle9i中也有CASE语句。

DECODE(AAV1R1V2R2....)函数

解释:

IF AA=V1  THEN  RETURN  R1

IF AA=V2  THEN  RETURN  R2

.. .. ..

ELSE

RETURN  NULL

Oracle

CREATE   VIEW    PA(SSN, GPA)
AS SELECT SSN, ROUND(AVG(DECODE(grade,'A', 4,'A+', 4.3,'A-', 3.7)),2)   FROM

    STUDENT GROUP BY SSN  ----也可使用CASE语句

 

Microsoft SQL Server

CREATE    VIEW   PA(SSN, GPA)
AS SELECT SSN, ROUND(AVG(CASE grade
WHEN 'A' THEN 4 WHEN 'A+' THEN 4.3 WHEN 'A-' THEN 3.7 ELSE 0 END),2) FROM

STUDENT GROUP BY SSN

 

CASE 表达式可以支持使用 SELECT 语句进行布尔测试,这是 DECODE 命令所不允许的。

l        用户定义的函数

 

Oracle函数的一个实例

函数可以接受多个变量而只能返回一个值。

create or replace function get_dob(ss_num varchar2)  return date

as

  birthd date; -- 局部日期域存放生日

begin

  begin

    select dob

    into birthd -- 对隐式游标必须用"into"

    from person where soc_sec_num = ss_num;

    exception

    when no_data_found then

    begin

      error_notify(ss_num); -- 调用其他过程

      birthd := trunc(sysdate);

    end

    when others then  null;

  end ;

  return birthd;

end;         -- 结束外部块

/            --"/" 结束PL/SQL

 

在下面的示例中,Oracle 用户定义的函数 GET_SUM_MAJOR 用于获取按专业 (major) 交纳的学费总和。在 SQL Server 中,可通过把查询作为表使用,以替代这一函数。

Oracle

Microsoft SQL Server

SELECT SSN, FNAME, LNAME,T_PAID,
T_PAID/GET_SUM_MAJOR(MAJOR)

AS P_MAJOR FROM S_A.ST

SELECT  SSN, FNAME, LNAME,T_PAID,

        T_PAID/SUM_MAJOR ASP_MAJOR

FROM S_A.ST,(SELECTMAJOR,SUM(T_PAID)

 SUM_MAJOR FROM S_A.ST GROUP BY MAJOR)

 SUM_ST

WHERE ST.MAJOR = SUM_ST.MAJOR

CREATE  OR  REPLACE FUNCTION

 GET_SUM_MAJOR (IR VARCHAR2)

 RETURN NUMBER
AS 
SUM_PAID NUMBER;
BEGIN
SELECT 
SUM(T_PAID) INTOSUM_PAID
FROM 
S_A.ST WHERE MAJOR = IR;

RETURN(SUM_PAID);
END 
GET_SUM_MAJOR;

不需要 CREATE FUNCTION 语法;使用 CREATE PROCEDURE 语法。

l        模式匹配

SQL Server LIKE 关键字提供了一些 Oracle 不支持的、有用的通配符搜索选项。除了支持两个 RDBMS 通用的 %  _ 通配符外,SQL Server 还支持 []  [^] 字符。

[] 字符用于在给定范围内搜索某一单个字符。例如,如果在单字符位置搜索从 a  f 的字符,可以用 LIKE '[a-f]'  LIKE '[abcdef]' 指定。此表给出了这些附加通配符的用法。

Oracle

Microsoft SQL Server

SELECT  *  FROM  STUDENT 
WHERE  LNAME  LIKE  'A%'
OR  LNAME LIKE 'B%'
OR  LNAME LIKE 'C%'

SELECT  *  FROM  STUDENT 
WHERE  LNAME  LIKE '[ABC]%'

[^] 通配符集合用于指定不在给定范围内的字符。例如,如果接受除 a  f 以外的任何字符,则使用 LIKE '[^a - f]'  LIKE '[^abcdef]'

l        关键字

以下是每种 RDBMS 支持的关键字。

语句

Oracle PL/SQL

Microsoft SQL Server
Transact-SQL

声明变量

DECLARE

DECLARE

语句块

BEGIN...END;

BEGIN...END

条件处理

IF  THEN,
ELSIF THEN,
ELSE
ENDIF;

IF [BEGIN END]
ELSE <condition>
[BEGIN END]
ELSE IF <condition>
CASE expression

无条件退出

RETURN

RETURN

无条件退出到当前程序块结束后紧接着的那条语句

EXIT

BREAK

重新开始一个 WHILE 循环

暂缺

CONTINUE

等待指定的间隔

暂缺 (dbms_lock.sleep)

WAITFOR

循环控制

WHILE LOOP END LOOP;

LABEL GOTO LABEL;
FOR END LOOP;
LOOP END LOOP;

WHILE <condition>
BEGIN END

LABEL GOTO LABEL

程序注释

/*  */, --

/*  */, --

打印输出

RDBMS_OUTPUT.PUT_LINE

PRINT

提出程序错误

RAISE_APPLICATION_
ERROR

RAISERROR

执行程序

EXECUTE

EXECUTE

语句终止符

分号 (;)

暂缺

l         声明变量

Transact-SQL  PL/SQL 变量是使用 DECLARE 关键字来创建的。Transact-SQL 变量用 @ 来标识,并且像 PL/SQL 变量一样,第一次创建时该变量被初始化为空值。

Oracle

Microsoft SQL Server

DECLARE
VSSN CHAR(9);
VFNAME VARCHAR2(12);
VLNAME VARCHAR2(20);
VBIRTH_DATE DATE;
VLOAN_AMOUNT NUMBER(12,2);

DECLARE
@VSSN CHAR(9),
@VFNAME VARCHAR2(12),
@VLNAME VARCHAR2(20),
@VBIRTH_DATE DATETIME,
@VLOAN_AMOUNT NUMERIC(12,2)

 

Transact-SQL 不支持 %TYPE  %ROWTYPE 变量数据类型定义。在 DECLARE 命令中,不能对 Transact-SQL 变量进行初始化。Oracle NOT NULL  CONSTANT 关键字不能用在 Microsoft SQL Server 数据类型定义中。

 Oracle LONG  LONG RAW 数据类型一样,text  image 数据类型不能用于变量声明。此外,不支持 PL/SQL 类型的记录和表定义。

l        变量赋值

Microsoft SQL Server 赋值语句用setselect,Oracle赋值语句用pascal的语法:=

 

Oracle

Microsoft SQL Server

赋值运算符 (:=)

SET @local_variable = value

用于从一行中选择列值的

 SELECT...INTO 语法。

SELECT @local_variable = expression [FROM]用于为字面值、涉及其它局部变量的表达式或一行中的列值赋值。

FETCH INTO 语法

FETCH INTO 语法

 

以下是一些语法示例。

Oracle

Microsoft SQL Server

DECLARE  VSSN CHAR(9);
    VFNAME  VARCHAR2(12);
    VLNAME  VARCHAR2(20);
BEGIN
  VSSN := '123448887';
SELECT FNAME, LNAME INTO

    VFNAMEVLNAME FROM

 STUDENTS  WHERE SSN=VSSN;
END;

DECLARE  @VSSN CHAR(9),
     @VFNAME VARCHAR(12),
     @VLNAME VARCHAR(20)
SET  @VSSN = '12355887'

SELECT  @VFNAME=FNAME,

 @VLNAME=LNAME FROM STUDENTS

WHERE  SSN = @VSSN

l         语句块

Oracle PL/SQL  Microsoft  SQL Server T-SQL 支持使用 BEGIN END 术语,来指定程序块。T-SQL 不要求在 DECLARE 语句后面使用语句块。在 Microsoft SQL Server 中,如果 IF 语句和 WHILE 循环执行不止一个语句,需要使用 BEGINEND 语句块。 Oracle的示例如下:

DECLARE       /*声明部分。所有的变量、游标和类型都在这里声明。*/

  i NUMBER:=6;

  a VARCHAR2(30);

BEGIN  /*执行部分*/

  

EXCEPTION     /*异常处理部分*/

  WHEN NO_DATA_FOUND THEN

  DBMS_OUTPUT.PUT_LINE();

END;

l         条件控制(选择结构)

SQL Server T-SQL  IF 条件语句同 Oracle  PL/SQL 中的IF 语句基本相同。对等的CASE 语句如下:

Oracle

Microsoft SQL Server

DECLARE
  VD CHAR(1); VD_N VARCHAR2(20);
BEGIN
  VDEGREE := 'U';

  CASE VD 
  WHEN 'U' THEN VD_N:='Undergraduate';
  WHEN 'M' THEN VD_N:='Masters';
  WHEN 'P' THEN VD_N:='PhD';
  ELSE VD_N:='Unknown';
  END CASE;

END;

DECLARE
  @VD CHAR(1), @VD_N VARCHAR(20)
SELECT @VD = 'U'
SELECT @VD_N =

CASE @VD 
  WHEN 'U' THEN 'Undergraduate'
  WHEN 'M' THEN 'Masters'
  WHEN 'P' THEN 'PhD'.
  ELSE 'Unknown'
END

l        重复控制(循环结构)

SQL Server T-SQL 循环语句同 Oracle  PL/SQL 中的循环语句基本相同。Oracle多了一种FOR循环。对等的WHILE语句如下:

Oracle

Microsoft SQL Server

DECLARE
  COUNTER NUMBER;
BEGIN
  COUNTER := 0
  WHILE (COUNTER <5) LOOP
    COUNTER := COUNTER + 1;
  END LOOP;
END;

DECLARE
  @COUNTER NUMERIC
SELECT  @COUNTER = 1
WHILE (@COUNTER <5)
BEGIN
  SELECT @COUNTER = @COUNTER +1
END

Transact-SQL可以使用 BREAK  CONTINUE 关键字,从循环的内部控制语句的执行。BREAK 关键字导致从 WHILE 循环中无条件退出,CONTINUE 关键字使 WHILE 循环跳过后面的语句,并重新开始循环。BREAK关键字和 Oracle PL/SQL EXIT 关键字等同。Oracle 没有 CONTINUE 的对等关键字。

l         顺序控制:GOTONULL语句

Oracle  Microsoft SQL Server 均有 GOTO 语句,但是语法不同。遇到 GOTO 语句,Transact-SQL 批处理执行就会跳到标号处。GOTO 语句和标号之间的语句不执行。

Oracle

Microsoft SQL Server

GOTO label;
<<label name here>>

GOTO label

l        PRINTdbms_output.put_line()  

T--SQL  PRINT 语句和 PL/SQL  dbms_output.put_line() 过程所执行的操作相同。它用于打印用户指定的消息。

PRINT 语句的消息限度为 8,000 个字符。使用 char  varchar 数据类型定义的变量可以嵌入打印语句中。如果使用了任何其它数据类型,必须使用 CONVERT  CAST 函数。可以打印局部变量、全局变量和文本。可用单引号和双引号将文本括上。

l         从存储过程返回

Microsoft SQL Server  Oracle 均有 RETURN 语句。使用 RETURN 语句,程序可从查询或过程无条件退出。RETURN 是一条可立即执行的完整语句,并可在任何时候用于从过程、批处理或语句块中退出。RETURN 后面的语句均不执行。

Oracle

Microsoft SQL Server

RETURN expression:

RETURN [integer_expression]

l         提出程序错误

T-SQL RAISERROR 语句返回一个用户定义的错误信息,并设定一个系统标志,来记录已发生了一个错误。它和 PL/SQL raise_application_error 异常错误处理程序的功能相似。

在下面的代码示例中,PL/SQL 程序使用 raise_application_error 异常错误处理程序,而 Transact-SQL 程序什么也不使用。加入 raise_application_error 异常错误处理程序,可避免 PL/SQL 程序返回二义性的unhandled exception 错误信息。相反,当发生意外问题时,它总是返回 Oracle 错误信息 (SQLERRM)

 Transact-SQL 程序失败时,它总是给客户程序返回详细的错误信息。因此,除非需要进行专门错误处理,否则,并不总是需要 RAISERROR 语句。

Oracle

Microsoft SQL Server

CREATE OR REPLACE FUNCTION DEPT 
(VDEPT IN VARCHAR2) RETURN NUMBER AS
BEGIN
  DELETE FROM DEPT_ADMIN 
  WHERE DEPT = VDEPT;
  RETURN(SQL%ROWCOUNT);
EXCEPTION
WHEN OTHER THEN
RAISE_APPLICATION_ERROR 
(-20001,SQLERRM);
END;
/

CREATE PROCEDURE DEPT @VDEPT VARCHAR(4) AS
DELETE FROM DEPT_DB 
WHERE DEPT = @VDEPT
RETURN @@ROWCOUNT
GO

l         其它语法

MS SQL Server                              Oracle

   无须用结束每个语句                          用分号结束每个语句

   用GO表示批次执行                                 用/表示批次执行

   @@ROWCOUNT                                        SQL% ROWCOUNT

   @@SPID                                       Userenv(sessionid)

  (返回当前用户进程的进程 ID)

   @@FETCH_STATUS                                      游标名%FOUND

   insert语句可以只写insert               insert语句必须写insert into

   查看表结构用sp_help tableName           查看表结构用desc tableName

   object_id(object_name)        select object_id from user_objects

字段默认值为字符不需加引号           字段默认值为字符需加引号如:a

l         数据库对象之间的差异。

类别

Microsoft SQL Server

Oracle

列数

1024

254

行大小

8060 字节,加 16 字节指向每个 text image 

没有限制(但每行只允许一个 long  long raw

最大行数

没有限制

没有限制

BLOB 类型存储

和行一起存储的 16 字节指针。数据存储在其它数据页上。

每表一个 long  long raw。必须在行尾。数据存储在与行相同的块上。

聚集的表索引

每表一个

每表一个(索引组织的表)

非聚集的表索引

每表 249 

没有限制

单索引中索引的最大列数

16

16

索引中列值的最大长度

900 字节

1/2 

表命名规则

[[[server.]database.]owner.]table_name

[schema.]table_name

视图命名规则

[[[server.]database.]owner.]table_name

[schema.]table_name

索引命名规则

[[[server.]database.]owner.]table_name

[schema.]table_name

l         Dual

Dual  Oracle中的一个实际存在的表,任何用户均可读取,常用在没有目标表的Select,SQL Server中没有。

使用方法:

l         查看当前连接用户

SQL> select user from dual;

 

USER

------------------------------

SYSTEM

 

l         查看当前日期、时间

SQL> select sysdate from dual;

 

SYSDATE

----------

18-3 -01

 

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

 

TO_CHAR(SYSDATE,'YY

-------------------

2001-03-18 22:37:56

 

l         当作计算器用

SQL> select 1+2 from dual;

 

       1+2

----------

         3

 

l         查看序列值

SQL> create sequence aaa increment by 1 start with 1;

SQL> select aaa.nextval from dual;

 

SQL> select aaa.nextval from dual;

 

   NEXTVAL

----------

         1

 

SQL> select aaa.currval from dual;

 

   CURRVAL

----------

         1

l         取得建表和索引的DDL语句

    Oracle:

    我们都知道在9i之前,要想获得建表和索引的语句是一件很麻烦的事。我们通常的做法都是通过export with rows=no来得到,但它的输出因为格式的问题并不能直接拿来用。而另一种方法就是写复杂的脚本来查询数据字典,但这对于一稍微复杂的对象,如IOT和嵌套表等,还是无法查到。 
  从数据字典中获得DDL语句是经常要用的,特别是在系统升级/重建的时候。在Oracle 9i中,我们可以直接通过执行dbms_metadata从数据字典中查处DDL语句。使用这个功能强大的工具,我们可以获得单个对象或整个SCHEMADDL语句。最好不过的是因为它使用起来很简单。

  1、获得单个表和索引DDL语句的方法:

set heading off;

set echo off;

Set pages 999;

set long 90000;

spool get_single.sql

select dbms_metadata.get_ddl('TABLE','SZT_PQSO2','SHQSYS') from dual;

select dbms_metadata.get_ddl('INDEX','INDXX_PQZJYW','SHQSYS') from dual;

spool off;


  下面是输出。我们只要把建表/索引语句取出来在后面加个分号就可以直接运行了。

SQL> select dbms_metadata.get_ddl('TABLE','SZT_PQSO2','SHQSYS') from dual;

 

CREATE TABLE "SHQSYS"."SZT_PQSO2" 

( "PQBH" VARCHAR2(32) NOT NULL ENABLE, 

"ZJYW" NUMBER(10,0), 

"CGSO" NUMBER(10,0) NOT NULL ENABLE, 

"SOLS" VARCHAR2(17), 

"SORQ" VARCHAR2(8), 

"SOWR" VARCHAR2(8), 

"SOCL" VARCHAR2(6), 

"YWHM" VARCHAR2(10), 

"YWLX" VARCHAR2(6) 

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING 

STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) 

TABLESPACE "DATA1" 

 

SQL> select dbms_metadata.get_ddl('INDEX','INDXX_PQZJYW','SHQSYS') from dual;

 

CREATE INDEX "SHQSYS"."INDXX_PQZJYW" ON "SHQSYS"."SZT_PQSO2" ("ZJYW") 

PCTFREE 10 INITRANS 2 MAXTRANS 255 

STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) 

TABLESPACE "DATA1" 

SQL> 

SQL> spool off;


  2、获得整个SCHEMA DDL语句的方法:

set pagesize 0

set long 90000

set feedback off

set echo off 

spool get_schema.sql 

connect shqsys/shqsys@hawk1;

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)

FROM USER_TABLES u;

SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)

FROM USER_INDEXES u;

spool off;


  需要注意的是,当我们的表中有外健(参照约束)时,我们需要判别参照表之间的顺序,确保重建时按照合理的顺序进行。你可以通过查询dba_constraints and dba_cons_columns来确定各表之间的顺序,不再详述。

 

SQL Server:

通过图形化界面获取脚本,非常简单。

 

八、数据库的复制

复制是一项复杂的高级技术,SQL Server的复制注意事项较多,有不少SQL Server的使用者不会使用复制,Oracle的复制其复杂程度就不用说了,本人到目前为止还没有用过。

SQL Server资料暂缺。

 

 

Oracle

 

 

Internet上运作数据库经常会有这样的需求:把遍布全国各城市相似的数据库应用统一起来,一个节点的数据改变不仅体现在本地,还反映到远端。复制技术给用户提供了一种快速访问共享数据的办法。

 

一、实现数据库复制的前提条件

 

1、数据库支持高级复制功能

 

您可以用system身份登录数据库,查看v$option视图,如果其中Advanced replicationTRUE,则支持高级复制功能;否则不支持。

 

2、数据库初始化参数要求

 

①、db_domain = test.com.cn

 

指明数据库的域名(默认的是WORLD),这里可以用您公司的域名。

 

②、global_names = true

 

它要求数据库链接(database link)和被连接的数据库名称一致。

 

现在全局数据库名:db_name+”.”+db_domain

 

③、有跟数据库job执行有关的参数

 

job_queue_processes = 1

 

job_queue_interval = 60

 

distributed_transactions = 10

 

open_links = 4

 

第一行定义SNP进程的启动个数为n。系统缺省值为0,正常定义范围为036,根据任务的多少,可以配置不同的数值。

 

第二行定义系统每隔N秒唤醒该进程一次。系统缺省值为60秒,正常范围为13600秒。事实上,该进程执行完当前任务后,就进入睡眠状态,睡眠一段时间后,由系统的总控负责将其唤醒。

 

如果修改了以上这几个参数,需要重新启动数据库以使参数生效。

 

二、实现数据库同步复制的步骤

 

假设在Internet上我们有两个数据库:一个叫深圳(shenzhen),一个叫北京(beijing)

 

具体配置见下表:

 

数据库名

shenzhen

beijing

 

数据库域名

test.com.cn

test.com.cn

 

数据库sid

shenzhen

beijing

 

Listener端口号

1521

1521

 

服务器ip地址

10.1.1.100

10.1.1.200

 

 

 

1、确认两台数据库之间可以互相访问,在tnsnames.ora里设置数据库连接字符串。

 

①、例如:深圳这边的数据库连接字符串是以下的格式

 

beijing =

 

(DESCRIPTION =

 

(ADDRESS_LIST =

 

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.200)(PORT = 1521))

 

)

 

(CONNECT_DATA =

 

(SERVICE_NAME = beijing)

 

)

 

)

 

运行$tnsping beijing

 

出现以下提示符:

 

Attempting to contact (ADDRESS=(PROTOCOL=TCP)(HOST=10.1.1.200)(PORT=1521))

 

OKn毫秒)

 

表明深圳数据库可以访问北京数据库。

 

②、在北京那边也同样配置,确认$tnsping shenzhen 是通的。

 

2、改数据库全局名称,建公共的数据库链接。

 

①、用system身份登录shenzhen数据库

 

SQL>alter database rename global_name to shenzhen.test.com.cn;

 

system身份登录beijing数据库:

 

SQL>alter database rename global_name to beijing.test.com.cn;

 

②、用system身份登录shenzhen数据库

 

SQL>create public database link beijing.test.com.cn using 'beijing';

 

测试数据库全局名称和公共的数据库链接

 

SQL>select * from global_name@beijing.test.com.cn;

 

返回结果为beijing.test.com.cn就对了。

 

system身份登录beijing数据库:

 

SQL>create public database link shenzhen.test.com.cn using 'shenzhen';

 

测试数据库全局名称和公共的数据库链接

 

SQL>select * from global_name@shenzhen.test.com.cn;

 

返回结果为shenzhen.test.com.cn就对了。

 

3、建立管理数据库复制的用户repadmin,并赋权。

 

①、用system身份登录shenzhen数据库

 

SQL>create user repadmin identified by repadmin default tablespace users temporary tablespace temp;

 

SQL>execute dbms_defer_sys.register_propagator('repadmin');

 

SQL>grant execute any procedure to repadmin;

 

SQL>execute dbms_repcat_admin.grant_admin_any_repgroup('repadmin');

 

SQL>grant comment any table to repadmin;

 

SQL>grant lock any table to repadmin;

 

②、同样用system身份登录beijing数据库,运行以上的命令,管理数据库复制的用户repadmin,并赋权。

 

说明:repadmin用户名和密码可以根据用户的需求自由命名。

 

4、在数据库复制的用户repadmin下创建私有的数据库链接。

 

①、用repadmin身份登录shenzhen数据库

 

SQL>create database link beijing.test.com.cn connect to repadmin identified by repadmin;

 

测试这个私有的数据库链接:

 

SQL>select * from global_name@beijing.test.com.cn;

 

返回结果为beijing.test.com.cn就对了。

 

②、用repadmin身份登录beijing数据库

 

SQL>create database link shenzhen.test.com.cn connect to repadmin identified by repadmin;

 

测试这个私有的数据库链接

 

SQL>select * from global_name@shenzhen.test.com.cn;

 

返回结果为shenzhen.test.com.cn就对了。

 

5、创建或选择实现数据库复制的用户和对象,给用户赋权,数据库对象必须有主关键字。

 

假设我们用ORACLE里举例用的scott用户,dept表。

 

①、用internal身份登录shenzhen数据库,创建scott用户并赋权

 

SQL>create user scott identified by tiger default tablespace users temporary tablespace temp;

 

SQL>grant connect, resource to scott;

 

SQL>grant execute on sys.dbms_defer to scott;

 

②、用scott身份登录shenzhen数据库,创建表dept

 

SQL>create table dept

 

(deptno number(2) primary key,

 

dname varchar2(14),

 

loc varchar2(13) );

 

③、如果数据库对象没有主关键字,可以运行以下SQL命令添加:

 

SQL>alter table dept add (constraint dept_deptno_pk primary key (deptno));

 

④、在shenzhen数据库scott用户下创建主关键字的序列号,范围避免和beijing的冲突。

 

SQL> create sequence dept_no increment by 1 start with 1 maxvalue 44 cycle nocache;

 

(说明:maxvalue 44可以根据应用程序及表结构主关键字定义的位数需要而定)

 

⑤、在shenzhen数据库scott用户下插入初始化数据

 

SQL>insert into dept values (dept_no.nextval,'accounting','new york');

 

SQL>insert into dept values (dept_no.nextval,'research','dallas');

 

SQL>commit;

 

⑥、在beijing数据库那边同样运行以上①,②,③

 

⑦、在beijing数据库scott用户下创建主关键字的序列号,范围避免和shenzhen的冲突。

 

SQL> create sequence dept_no increment by 1 start with 45 maxvalue 99 cycle nocache;

 

⑧、在beijing数据库scott用户下插入初始化数据

 

SQL>insert into dept values (dept_no.nextval,'sales','chicago');

 

SQL>insert into dept values (dept_no.nextval,'operations','boston');

 

SQL>commit;

 

6、创建要复制的组scott_mg,加入数据库对象,产生对象的复制支持

 

①、用repadmin身份登录shenzhen数据库,创建主复制组scott_mg

 

SQL> execute dbms_repcat.create_master_repgroup('scott_mg');

 

说明:scott_mg组名可以根据用户的需求自由命名。

 

②、在复制组scott_mg里加入数据库对象

 

SQL>execute dbms_repcat.create_master_repobject(sname=>'scott',oname=>'dept', type=>'table',use_existing_object=>true,gname=>'scott_mg');

 

参数说明:

 

sname 实现数据库复制的用户名称

 

oname 实现数据库复制的数据库对象名称

 

(表名长度在27个字节内,程序包名长度在24个字节内)

 

type 实现数据库复制的数据库对象类别

 

(支持的类别:表,索引,同义词,触发器,视图,过程,函数,程序包,程序包体)

 

use_existing_object true表示用主复制节点已经存在的数据库对象

 

gname 主复制组名

 

③、对数据库对象产生复制支持

 

SQL>execute dbms_repcat.generate_replication_support('scott','dept','table');

 

(说明:产生支持scott用户下dept表复制的数据库触发器和程序包)

 

④、确认复制的组和对象已经加入数据库的数据字典

 

SQL>select gname, master, status from dba_repgroup;

 

SQL>select * from dba_repobject;

 

7、创建主复制节点

 

①、用repadmin身份登录shenzhen数据库,创建主复制节点

 

SQL>execute dbms_repcat.add_master_database

 

(gname=>'scott_mg',master=>'beijing.test.com.cn',use_existing_objects=>true, copy_rows=>false, propagation_mode => 'asynchronous');

 

参数说明:

 

gname 主复制组名

 

master 加入主复制节点的另一个数据库

 

use_existing_object true表示用主复制节点已经存在的数据库对象

 

copy_rows false表示第一次开始复制时不用和主复制节点保持一致

 

propagation_mode 异步地执行

 

②、确认复制的任务队列已经加入数据库的数据字典

 

SQL>select * from user_jobs;

 

8、使同步组的状态由停顿(quiesced )改为正常(normal)

 

①、用repadmin身份登录shenzhen数据库,运行以下命令

 

SQL> execute dbms_repcat.resume_master_activity('scott_mg',false);

 

②、确认同步组的状态为正常(normal)

 

SQL> select gname, master, status from dba_repgroup;

 

③、如果这个①命令不能使同步组的状态为正常(normal),可能有一些停顿的复制,运行以下命令再试试(建议在紧急的时候才用)

 

SQL> execute dbms_repcat.resume_master_activity('scott_mg',true);

 

9、创建复制数据库的时间表,我们假设用固定的时间表:10分钟复制一次。

 

①、用repadmin身份登录shenzhen数据库,运行以下命令

 

SQL>begin

 

dbms_defer_sys.schedule_push (

 

destination => 'beijing.test.com.cn',

 

interval => 'sysdate + 10/1440',

 

next_date => sysdate);

 

end;

 

/

 

 

SQL>begin

 

dbms_defer_sys.schedule_purge (

 

next_date => sysdate,

 

interval => 'sysdate + 10/1440',

 

delay_seconds => 0,

 

rollback_segment => '');

 

end;

 

/

 

 

②、用repadmin身份登录beijing数据库,运行以下命令

 

SQL>begin

 

dbms_defer_sys.schedule_push (

 

destination => ' shenzhen.test.com.cn ',

 

interval => 'sysdate + 10 / 1440',

 

next_date => sysdate);

 

end;

 

/

 

 

SQL>begin

 

dbms_defer_sys.schedule_purge (

 

next_date => sysdate,

 

interval => 'sysdate + 10/1440',

 

delay_seconds => 0,

 

rollback_segment => '');

 

end;

 

/

 

10、添加或修改两边数据库的记录,跟踪复制过程

 

如果你想立刻看到添加或修改后数据库的记录的变化,可以在两边repadmin用户下找到pushjob_number,然后运行:

 

SQL>exec dbms_job.run(job_number);

 

三、异常情况的处理

 

1、检查复制工作正常否,可以在repadmin 用户下查询user_jobs

 

SQL>select job,this_date,next_date,what, broken from user_jobs;

 

正常的状态有两种:

 

任务闲——this_date为空,next_date为当前时间后的一个时间值

 

任务忙——this_date不为空,next_date为当前时间后的一个时间值

 

异常状态也有两种:

 

任务死锁——next_date为当前时间前的一个时间值

 

任务死锁——next_date为非常大的一个时间值,例如:4001-01-01

 

这可能因为网络中断照成的死锁

 

解除死锁的办法:

 

$ps –ef|grep orale

 

找到死锁的刷新快照的进程号ora_snp*,用kill –9 命令删除此进程

 

然后进入repadmin 用户SQL>操作符下,运行命令:

 

SQL>exec dbms_job.run(job_number);

 

说明:job_number 为用select job,this_date,next_date,what from user_jobs;命令查出的job编号。

 

2、增加或减少复制组的复制对象

 

①、停止主数据库节点的复制动作,使同步组的状态由正常(normal)改为停顿(quiesced )

 

repadmin身份登录shenzhen数据库,运行以下命令

 

SQL>execute dbms_repcat.suspend_master_activity (gname => 'scott_mg');

 

②、在复制组scott_mg里加入数据库对象,保证数据库对象必须有主关键字。

 

SQL>execute dbms_repcat.create_master_repobject(sname=>'scott',oname=>'emp', type=>'table',use_existing_object=>true,gname=>'scott_mg');

对加入的数据库对象产生复制支持

SQL>execute dbms_repcat.generate_replication_support('scott','emp','table');

 

③、在复制组scott_mg里删除数据库对象。

 

SQL>execute dbms_repcat.drop_master_repobject ('scott','dept','table');

 

④、重新使同步组的状态由停顿(quiesced )改为正常(normal)

 

SQL> execute dbms_repcat.resume_master_activity('scott_mg',false);

 

九、备份与恢复

MS SQL Server:

    备份、恢复都比较简单。

采用图形化界面备份、恢复。非常简单,不再详述。

 

采用语句备份、恢复。一个例子如下。

BACKUP DATABASE [MLS] TO  DISK = N'C:\e\sqlback\mls' WITH  NOINIT ,  NOUNLOAD ,  NAME = N'MLS 备份',  NOSKIP ,  STATS = 10,  NOFORMAT

RESTORE DATABASE [MLS] FROM  DISK = N'C:\e\sqlback\mls' WITH  FILE = 1,  NOUNLOAD ,  STATS = 10,  RECOVERY

 

Oracle:

    备份、恢复都比较复杂,难倒了无数初学者,令无数英雄竟折腰。

采用图形化界面备份、恢复。需要满足五个前提条件,缺一不可(WINDOWS XP为例)

●将数据库设为归档日志模式。可在 例程-配置-恢复 中设置。

●设置节点的首选身份证明,用户名:Administrator

●设置数据库的首选身份证明,用户名:SYS,角色:SYSDBA

●给NT管理员授予批处理作业权限。控制面板---管理工具---本地安全策略

---本地策略---用户权利指派---作为批处理作业登录---Administrator加入。

●给数据库管理员授予角色权限。必需具备EXP_FULL_DATABASEIMP_FULL_DATABASE角色权限。SYS已授予上述角色,SYSTEM还没有授予上述角色,需要手工授予。

 

采用语句备份、恢复暂缺。

 

·数据库的备份:
     关于Oracle数据库的备份,标准的有三种办法:导出/导入Export/Import)、冷备份热备份。导出/导入是一种逻辑备份,冷备份和热备份是物理备份 

 

  <1>导出/导入(Export/Import 
      此操作只需设置节点的首选身份证明即可,其它几个条件不需要。

利用Export可将数据从数据库中提取出来,利用Import则可将提取出来的数据送回Oracle数据库中去。 
  A
、简单导出数据(Export)和导入数据(Import
      Oracle
支持三种类型的输出: 
    
1)表方式(T方式),将指定表的数据导出。 
    
2)用户方式(U方式),将指定用户的所有对象及数据导出, 导入时需要先建用户。
    
3)全库方式(Full方式),将数据库中的所有对象导出。 此操作没试过 。
      
数据导出(Import)的过程是数据导入(Export)的逆过程,它们的数据流向不同。 
 B
.增量导出/导入   此操作没试过
      增量导出是一种常用的数据备份方法,它只能对整个数据库来实施,并且必须作为SYSTEM

来导出。在进行此种导出时,系统不要求回答任何问题。导出文件名缺省为export.dmp

如果不希望自己的输出文件定名为export.dmp,必须在命令行中指出要用的文件名。 
      
增量导出包括三个类型: 
     
1完全增量导出(Complete 
         
即备份整个数据库,比如: 
           
exp system/manager inctype=complete file=990702.dmp 
     
2增量型增量导出 
           备份上一次备份后改变的数据。比如: 
           
exp system/manager inctype=incremental file=990702.dmp 
     
3累计型增量导出(Cumulative 
           
累计型导出方式只是导出自上次完全” 导出之后数据库中变化了的信息。比如: 
           $exp system/manager inctype=cumulative file=990702.dmp 
     
数据库管理员可以排定一个备份日程表,用数据导出的三个不同方式合理高效地完成。比如数据库的备份任务可作如下安排: 
       ·
星期一:完全导出(A 
       ·
星期二:增量导出(B 
       ·
星期三:增量导出(C 
       ·
星期四:增量导出(D 
       ·
星期五:累计导出(E 
       ·
星期六:增量导出(F 
       ·
星期日:增量导出(G 
   
如果在星期日,数据库遭到意外破坏,数据库管理员可按以下步骤来恢复数据库: 
    
第一步:用命令CREATE DATABASE重新生成数据库结构; 
    
第二步:创建一个足够大的附加回段。 
    
第三步:完全增量导入A 
      
imp system./manager inctype= RECTORE FULL=Y FILE=A 
   
第四步:累计增量导入E 
    
imp system/manager inctype= RECTORE FULL=Y FILE =E 
    
第五步:最近增量导入F 
    
imp system/manager inctype=RESTORE FULL=Y FILE=F 
   

 

 <2>冷备份 (待完善)
      物理冷备份(offline backup)要求数据库在关闭(所有INSTANCEs停止)的情况下进行。这种备份必须是完全备份,即需备份所有的数据文件、控制文件(control file)、日志文件(redo log file)、初始参数文件等等。 
    物理冷备份的步骤简单,但要求系统能够停止。

冷备份是将关键性文件拷贝到另外位置的一种说法。对于备份Oracle信息而言,冷备份是最快和最安全的方法。

冷备份的优点是:
   ·
是非常快速的备份方法(只需拷贝文件) 
   ·
容易归档(简单拷贝即可) 
   ·
容易恢复到某个时间点上(只需将文件再拷贝回去) 
   ·
能与归档方法相结合,作数据库最新状态的恢复。 
   ·
低度维护,高度安全。 
    
但冷备份也有如下不足: 
   ·
单独使用时,只能提供到某一时间点上的恢复。 
   ·
在实施备份的全过程中,数据库必须要作备份而不能作其它工作。也就是说,在冷备份过程中,数据库必须是关闭状态。 
   ·
若磁盘空间有限,只能拷贝到磁带等其它外部存储设备上,速度会很慢。 
   ·
不能按表或按用户恢复。 
    
如果可能的话(主要看效率),应将信息备份到磁盘上,然后启动数据库(使用户可以工作)并将所备份的信息拷贝到磁带上(拷贝的同时,数据库也可以工作)。冷备份中必须拷贝的文件包括: 
   ·
所有数据文件 
   ·
所有控制文件 
   ·
所有联机REDO LOG文件 
   ·Init.ora
文件(可选) 
    
值得注意的是冷备份必须在数据库关闭的情况下进行,当数据库处于打开状态时,执行数据库文件系统备份是无效的 
   
下面是做冷备份的完整例子: 
   
1 关闭数据库$sqldba lmode=y 
                 SQLDBA >connect internal; 
                 SQLDBA >shutdown normal; 
   
2 用拷贝命令备份全部的时间文件、重做日志文件、控制文件、初始化参数文件 
                 SQLDBA >! cp < file > < backup directory > 
   
3 重启Oracle数据库 
                 
sqldba lmode=y 
                 SQLDBA >connect internal; 
                 SQLDBA >startup; 
  

  <3>热备份 

    注:如果您的数据库处于“无归档日志”模式,则只能执行数据库完全备份。如果数据库处于“无归档日志”模式,并且在备份作业之际是打开的,则它将在备份过程中被关闭,接着以“装载”模式启动、备份,然后在备份完成后停留在此状态。


        
物理热备份(online backup)是在数据库运行的情况下,采用archivelog mode方式备份数据的方法。所以,如果你有昨天夜里的一个冷备份而且又有今天的热备份文件,在发生问题时,就可以利用这些资料恢复更多的信息。热备份要求数据库在Archivelog方式下操作,并需要大量的档案空间。一旦数据库运行在archivelog状态下,就可以做备份了。 
    可选择下列基本备份类型:

整个数据库:整个数据库备份包含控制文件以及属于此数据库的所有数据库文件。整体数据库备份是最常见的备份类型。选择此备份类型可以一次备份整个数据库。如果在“归档日志”模式下操作,您还可以选择在一定时期内备份数据库的不同部分,由此可以逐步构建一个完整的数据库备份。

表空间:表空间备份是一个数据库子集的备份。仅当数据库处于“归档日志”模式下运行时,表空间备份才有效。

数据文件:数据文件备份是单个数据文件的备份。数据文件备份不象表空间备份那样常用,只有在“归档日志”模式下运行数据库时才有效。

归档日志: 已归档的重做日志是一个联机重做日志,Oracle 在其中存放重做操作,以非活动形式再现,并被复制到一个或多个日志归档目标位置。例如,归档重做日志不可以改变,但对于恢复数据库却至关重要。所以,您应该尽可能地维护多个副本。该选项只可用于“归档日志”模式。 注:如果数据库在创建备份作业时“打开”并处于“归档日志”模式,而在作业运行时处于“装载”状态,则该备份作业将因为“变更系统切换日志”的缘故而失败。

使用映像副本  如果要使用映像副本备份数据文件,请选中此框。Oracle 支持使用数据文件、控制文件或归档日志的映像副本执行备份。您只能通过数据文件的映像副本执行控制文件的映像副本。只使用控制文件的映像副本的情况不太可能发生。 如果选择“数据文件”单选按钮和“使用映像副本”复选框,则出现“配置”页。

 

   热备份的优点是: 
  ·
可在表空间或数据文件级备份,备份时间短。 
  ·
备份时数据库仍可使用。 
  ·
可达到秒级恢复(恢复到某一时间点上)。 
  ·
可对几乎所有数据库实体作恢复。 
  ·
恢复是快速的,在大多数情况下在数据库仍工作时恢复。 
   
热备份的不足是: 
  ·
不能出错,否则后果严重。 
  ·
若热备份不成功,所得结果不可用于时间点的恢复。 
  ·
因难于维护,所以要特别仔细小心,不允许以失败而告终 

 

十、超大型数据库的设计

SQL Server:

 

 

  一般来讲,在一个MIS系统分析、设计、测试和试运行阶段,因为数据量较小,设计人员和测试人员往往只注意到功能的实现,而很难注意到性能的薄弱之处,等到系统投入实际运行一段时间后,才发现系统的性能在降低,这时再来考虑提高系统性能则要花费更多的人力物力,而整个系统也不可避免的形成了一个打补丁工程。笔者依据多年来设计和使用数据库的经验,提出以下一些设计准则,供同仁们参考。

  • 命名的规范

  不同的数据库产品对对象的命名有不同的要求,因此,数据库中的各种对象的命名、后台程序的代码编写应采用大小写敏感的形式,各种对象命名长度不要超过30个字符,这样便于应用系统适应不同的数据库。

  • 游标(Cursor)的慎用

  游标提供了对特定集合中逐行扫描的手段,一般使用游标逐行遍历数据,根据取出的数据不同条件进行不同的操作。尤其对多表和大表定义的游标(大的数据集合)循环很容易使程序进入一个漫长的等特甚至死机,笔者在某市《住房公积金管理系统》进行日终帐户滚积数计息处理时,对一个10万个帐户的游标处理导致程序进入了一个无限期的等特(后经测算需48个小时才能完成),后根据不同的条件改成用不同的UPDATE语句得以在二十分钟之内完成。示例如下:

Declare Mycursor cursor for select  count_no from COUNT
         Open Mycursor
         Fetch Mycursor into @vcount_no
While (@@sqlstatus=0)
    Begin
         If  @vcount_no=’’  条件1
         操作1
         If  @vcount_no=’’  条件2
         操作2
         …
         Fetch Mycursor into @vcount_no
End
         改为
         Update COUNT set  操作1 for 条件1
         Update COUNT set  操作2 for 条件2
         …
         …

  在有些场合,有时也非得使用游标,此时也可考虑将符合条件的数据行转入临时表中,再对临时表定义游标进行操作,可时性能得到明显提高。笔者在某地市〈电信收费系统〉数据库后台程序设计中,对一个表(3万行中符合条件的30多行数据)进行游标操作(硬件环境:PC服务器,PII266 64Mram ,NT4.0 Ms Sqlserver 6.5)。 示例如下:

Create #tmp   /* 定义临时表 */
(        字段1
                          字段2
         …
         )
         Insert into #tmp select * from TOTAL where  
条件  /* TOTAL3万行 符合条件只有几十行 */
         Declare Mycursor cursor for select * from #tmp 
  /*对临时表定义游标*/
         …
  • 索引(Index)的使用原则

  创建索引一般有以下两个目的:维护被索引列的唯一性和提供快速访问表中数据的策略。大型数据库有两种索引即簇索引和非簇索引,一个没有簇索引的表是按堆结构存储数据,所有的数据均添加在表的尾部,而建立了簇索引的表,其数据在物理上会按照簇索引键的顺序存储,一个表只允许有一个簇索引,因此,根据B树结构,可以理解添加任何一种索引均能提高按索引列查询的速度,但会降低插入、更新、删除操作的性能,尤其是当填充因子(Fill Factor)较大时。所以对索引较多的表进行频繁的插入、更新、删除操作,建表和索引时因设置较小的填充因子,以便在各数据页中留下较多的自由空间,减少页分割及重新组织的工作。

  • 数据的一致性和完整性

  为了保证数据库的一致性和完整性,设计人员往往会设计过多的表间关联(Relation),尽可能的降低数据的冗余。表间关联是一种强制性措施,建立后,对父表(Parent Table)和子表(Child Table)的插入、更新、删除操作均要占用系统的开销,另外,最好不要用Identify 属性字段作为主键与子表关联。如果数据冗余低,数据的完整性容易得到保证,但增加了表间连接查询的操作,为了提高系统的响应时间,合理的数据冗余也是必要的。使用规则(Rule)和约束(Check)来防止系统操作人员误输入造成数据的错误是设计人员的另一种常用手段,但是,不必要的规则和约束也会占用系统的不必要开销,需要注意的是,约束对数据的有效性验证要比规则快。所有这些,设计人员在设计阶段应根据系统操作的类型、频度加以均衡考虑。

  • 事务的陷阱

  事务是在一次性完成的一组操作。虽然这些操作是单个的操作,SQL Server能够保证这组操作要么全部都完成,要么一点都不做。正是大型数据库的这一特性,使得数据的完整性得到了极大的保证。

---- 众所周知,SQL Server为每个独立的SQL语句都提供了隐含的事务控制,使得每个DML的数据操作得以完整提交或回滚,但是SQL Server还提供了显式事务控制语句

  BEGIN TRANSACTION 开始一个事务

  COMMIT TRANSACTION 提交一个事务

  ROLLBACK TRANSACTION 回滚一个事务

  事务可以嵌套,可以通过全局变量@@trancount检索到连接的事务处理嵌套层次。需要加以特别注意并且极容易使编程人员犯错误的是,每个显示或隐含的事物开始都使得该变量加1,每个事务的提交使该变量减1,每个事务的回滚都会使得该变量置0,而只有当该变量为0时的事务提交(最后一个提交语句时),这时才把物理数据写入磁盘。

  • 数据库性能调整

  在计算机硬件配置和网络设计确定的情况下,影响到应用系统性能的因素不外乎为数据库性能和客户端程序设计。而大多数数据库设计员采用两步法进行数据库设计:首先进行逻辑设计,而后进行物理设计。数据库逻辑设计去除了所有冗余数据,提高了数据吞吐速度,保证了数据的完整性,清楚地表达数据元素之间的关系。而对于多表之间的关联查询(尤其是大数据表)时,其性能将会降低,同时也提高了客 户端程序的编程难度,因此,物理设计需折衷考虑,根据业务规则,确定对关联表的数据量大小、数据项的访问频度,对此类数据表频繁的关联查询应适当提高数据冗余设计。

  • 数据类型的选择

  数据类型的合理选择对于数据库的性能和操作具有很大的影响,有关这方面的书籍也有不少的阐述,这里主要介绍几点经验。

  1. Identify字段不要作为表的主键与其它表关联,这将会影响到该表的数据迁移。
  2. Text Image字段属指针型数据,主要用来存放二进制大型对象(BLOB)。这类数据的操作相比其它数据类型较慢,因此要避开使用。
  3. 日期型字段的优点是有众多的日期函数支持,因此,在日期的大小比较、加减操作上非常简单。但是,在按照日期作为条件的查询操作也要用函数,相比其它数据类型速度上就慢许多,因为用函数作为查询的条件时,服务器无法用先进的性能策略来优化查询而只能进行表扫描遍历每行。

  例如:要从DATA_TAB1中(其中有一个名为DATE的日期字段)查询1998年的所有记录。

Select * from DATA_TAB1 where datepart(yy,DATE)=1998

l         对于文件和文件组的一些一般性建议:

1.       大多数数据库在只有单个数据文件和单个事务日志文件的情况下可以很好地运行。

2.       如果使用多个文件,请为附加文件创建第二个文件组,并将其设置为默认文件组。这样,主文件将仅包含系统表和对象。

3.      若要获得最佳性能,请在尽可能多的可用本地物理磁盘上创建文件或文件组,并将争夺空间最激烈的对象置于不同的文件组中。

4.       使用文件组以允许将对象放置在特定的物理磁盘上。

5.       将在同一联接查询中使用的不同表置于不同的文件组中。由于采用并行磁盘输入/输出对联接数据进行搜索,所以性能将得以改善。

6.       将最常访问的表和属于这些表的非聚集索引置于不同的文件组上。如果文件位于不同的物理磁盘上,由于采用并行输入/输出,所以性能得以提高。

7.       不要将事务日志文件与其它文件和文件组置于同一物理磁盘上。

 

 

Oracle

一、概论

超大型系统的特点为:

1.处理的用户数一般都超过百万,有的还超过千万,数据库的数据量一般超过1TB 
2.系统必须提供实时响应功能,系统需不停机运行,要求系统有很高的可用性及可扩展性。 
为了能达到以上要求,除了需要性能优越的计算机和海量存储设备外,还需要先进的数据库结构设计和优化的应用系统。

一般的超大型系统采用双机或多机集群系统。下面以数据库采用ORACLE并行服务器为例来谈谈超大型数据库设计方法:

·确定系统的ORACLE并行服务器应用划分策略 
·数据库物理结构的设计 
·系统硬盘的划分及分配 
·备份及恢复策略的考虑 

二、ORACLE并行服务器应用划分策略

ORACLE并行服务器允许不同节点上的多个INSTANCE实例同时访问一个数据库,以提高系统的可用性、可扩展性及性能。ORACLE并行服务器中的每个INSTANCE实例都可将共享数据库中的表或索引的数据块读入本地的缓冲区中,这就意味着一个数据块可存在于多个INSTANCE实例的SGA区中。那么保持这些缓冲区的数据的一致性就很重要。ORACLE 使用 PCM Parallel Cache Management) 锁维护缓冲区的一致性,ORACLE同时通过I DLM( 集成的分布式锁管理器)实现PCM ,并通过专门的LCK进程实现INSTANCE实例间的数据一致。 
考虑这种情况:INSTANCE1BLOCK X块修改,这时INSTANCE2BLOCK X块也需要修改。ORACLE并行服务器利用PCM锁机制,使BLOCK XINSTANCE 1SGA区写入数据库数据文件中,又从数据文件中把BLOCK X块读入INSTANCE2SGA区中。发生这种情况即为一个PINGPING使原来1MEMORY IO可以完成的工作,变成2DISK IO1 MEMORY IO才能够完成,如果系统中有过多的PING,将大大降低系统的性能。 
ORACLE并行服务器中的每个PCM锁可管理多个数据块。PCM锁管理的数据块的个数与分配给一个数据文件的PCM锁的个数及该数据文件的大小有关。当INSTANCE 1INSTANCE 2要操作不同的BLOCK,如果这些BLOCK 是由同一个PCM 锁管理的,仍然会发生PING。这些PING称为FALSE PING。当多个INSTANCE访问相同的BLOCK而产生的PINGTRUE PING

合理的应用划分使不同的应用访问不同的数据,可避免或减少TRUE PING;通过给FALSE PING较多的数据文件分配更多的PCM锁可减少 FALSE PING的次数,增加PCM锁不能减少TRUE PING

所以, ORACLE并行服务器设计的目的是使系统交易处理合理的分布在INSTANCE实例间,以最小化PING,同时合理的分配PCM锁,减少FALSE PING。设计的关键是找出可能产生的冲突,从而决定应用划分的策略。应用划分有如下四种方法:

1.根据功能模块划分,不同的节点运行不同的应用 
2.根据用户划分,不同类型的用户运行在不同的节点上 
3.根据数据划分,不同的节点访问不同的数据或索引 
4.根据时间划分,不同的应用在不同的时间段运行 
应用划分的两个重要原则是使PING最小化及使各节点的负载大致均衡。

三、数据库物理结构的设计

数据库物理结构设计包括确定表及索引的物理存储参数,确定及分配数据库表空间,确定初始的回滚段,临时表空间,redo log files等,并确定主要的初始化参数。物理设计的目的是提高系统的性能。整个物理设计的参数可以根据实际运行情况作调整。

● 表及索引数据量估算及物理存储参数的设置 

表及索引的存储容量估算是根据其记录长度及估算的最大记录数确定的。在容量计算中考虑了数据块的头开销及记录和字段的头开销等等。表及索引的initialnext存储参数一般设为相等,pctincrease设为0 
● 表空间的设计 
    ORACLE数据库的表和索引是透过表空间tablespace存储在数据库中的。在tablespace设计时一般作以下考虑: 
1、一般较大的表或索引单独分配一个tablespace 
2Read only对象或Read mostly对象分成一组,存在对应的tablespace中。 
3、若tablespace中的对象皆是read only对象,可将tablespace设置成read only模式,在  备份时,read only tablespace只需备份一次。 
4、高频率insert的对象分成一组,存在对应的tablespace中。 
5、增、删、改的对象分成一组,存在对应的tablespace中。 
6、表和索引分别存于不同的tablespace 
7、存于同一个 tablespace中的表(或索引)的extent 大小最好成倍数关系,有利于空间的重利用和减少碎片。 
 DB BLOCK SIZE 
    超大型数据库DB BLOCK SIZE一般在4KB  64KB,而最常用的是8KB 16KB32KB。选用较大的DB BLOCK SIZE可使INDEX的高度降低,也会提高IO效率。 
 Redo Log Files 
   将Redo Log文件放在一个读写较少的盘上。对于每个oracle 实例要建立两个以上的redo log 组,同组的两个成员放在不同的设备上。

ORACLE 使用专用的进程redo log writer (LGWR)将日志写入日志文件。一般日志文件最好建在专用的镜像盘上。日志文件组的个数及文件的大小的设定与系统交易量的大小有关。ORACLE并行服务器中每个INSTANCE使用各自的一组rego log files。一般的每组日志文件的个数为3-7个,每个的大小为200MB500MB 
● 数据文件大小 
   建议用标准的文件大小,如200M、1GB、2GB4GB8GB等,可简化空间的维护工作。 
● 回滚段 
   回滚段一般建在专用的表空间中。每一个INSTANCE实例拥有各自的回滚段。设置回滚段的一般原则是: initial  next 存储参数的值是相等的,同时还是DB BLOCK SIZE的倍数。每个回滚段的minextents设为20optimal参数的值保证回滚段缩小时不低于20extents 
● 临时表空间 
   临时表空间一般建在专用的表空间中。每一个INSTANCE实例拥有各自的临时表空间。这样使用临时表空间时不会有PING。设置临时表空间的initial=next

另外还要注意:

1、了解终端用户怎样访问数据,如果可能,将经常同时查询和频繁查询的对象放在不同的物理磁盘上。 
2
、当数据库包含允许用户并行访问不同数据元素的大对象时,将对象分割存放在多个磁盘上是有好处的。可以采用分区。在某个操作系统平台上定义拥有数百万行的表时,则更需小心,因为数据库文件的大小受到限止,这种限制是由操作系统而不是由oracle引起的。 
3
、在独立的各盘上至少创建两个用户定义的rollback表空间,以存放用户自己的rollback段。在初始化文件中安排rollback段的次序,使它们在多个磁盘之间进行切换。 
4
、确立表和索引的大小,这决定了保存它们所需的表空间的尺寸,也决定了哪些表空间物理地装在哪些盘上和哪些表空间可以结合在一起。具体的估算方法可以按照oracle 一些公式,这里还要按照各个表的具体特性,用途,定义它的存贮参数如(pctfree,pctused)。 

四、系统硬盘的划分及分配

在多机集群环境下,ORACLE并行服务器通过操作系统提供的DRD服务来共享同一个数据库。每一个INSTANCE对数据库的数据文件的访问都是通过该数据文件所在的DRD服务进行的。 
考虑以下情况:主机1上有DRD服务1,该服务对应的数据文件有12133567等,这时如果主机2上的INSTANCE2需要读取数据文件13,通过DRD服务调度,主机1通过DRD服务访问磁盘阵列上的数据文件13,把INSTANCE2需要的数据读到内存,然后通过MEMORY IO把数据传到主机2INSTANCE2。写操作是读操作的逆过程。 
通过以上分析可知,系统硬盘的划分及分配的原则是尽量减少MEMORY IO 

 

 

十一、元数据与数据字典

SQL Server :

 

元数据简介

元数据 (metadata) 最常见的定义为"有关数据的结构数据",或者再简单一点就是"关于数据的信息",日常生活中的图例、图书馆目录卡和名片等都可以看作是元数据。在关系型数据库管理系统 (DBMS) 中,元数据描述了数据的结构和意义。比如在管理、维护 SQL Server 或者是开发数据库应用程序的时候,我们经常要获取一些涉及到数据库架构的信息:

  1. 某个数据库中的表和视图的个数以及名称 ;
  2. 某个表或者视图中列的个数以及每一列的名称、数据类型、长度、精度、描述等;
  3. 某个表上定义的约束;
  4. 某个表上定义的索引以及主键/外键的信息。

下面我们将介绍几种获取元数据的方法。

获取元数据

一、使用系统存储过程与系统函数访问元数据

获取元数据最常用的方法是使用 SQL Server 提供的系统存储过程与系统函数。

系统存储过程与系统函数在系统表和元数据之间提供了一个抽象层,使得我们不用直接查询系统表就能获得当前数据库对象的元数据。

常用的与元数据有关的系统存储过程有以下一些:

系统存储过程

描述

sp_columns

返回指定表或视图的列的详细信息。

sp_databases

返回当前服务器上的所有数据库的基本信息。

sp_fkeys

若参数为带有主键的表,则返回包含指向该表的外键的所有表;若参数为带有外键的表名,则返回所有同过主键/外键关系与该外键相关联的所有表。

sp_pkeys

返回指定表的主键信息。

sp_server_info

返回当前服务器的各种特性及其对应取值。

sp_sproc_columns

返回指定存储过程的的输入、输出参数的信息。

sp_statistics

返回指定的表或索引视图上的所有索引以及统计的信息。

sp_stored_procedures

返回当前数据库的存储过程列表,包含系统存储过程。

sp_tables

返回当前数据库的所有表和视图,包含系统表。

常用的与元数据有关的系统函数有以下一些:

系统函数

描述

COLUMNPROPERTY

返回有关列或过程参数的信息,如是否允许空值,是否为计算列等。

COL_LENGTH

返回指定数据库的指定属性值,如是否处于只读模式等。

DATABASEPROPERTYEX

返回指定数据库的指定选项或属性的当前设置,如数据库的状态、恢复模型等。

OBJECT_ID

返回指定数据库对象名的标识号

OBJECT_NAME

返回指定数据库对象标识号的对象名。

OBJECTPROPERTY

返回指定数据库对象标识号的有关信息,如是否为表,是否为约束等。

fn_listextendedproperty

返回数据库对象的扩展属性值,如对象描述、格式规则、输入掩码等。

由于我们无法直接利用到存储过程与函数的返回结果,因此只有在我们关心的只是查询的结果,而不需要进一步利用这些结果的时候,我们会使用系统存储过程与系统函数来查询元数据。

例如,如果要获得当前服务器上所有数据库的基本信息,我们可以在查询分析器里面运行:

EXEC sp_databases
GO

在返回结果中我们可以看到数据库的名称、大小及备注等信息。

但是如果要引用这部分信息,或者存储这部分信息以供后面使用,那么我们必须借助中间表来完成这个操作:

CREATE TABLE #sp_result
(
DATABASE_NAME sysname,
DATABASE_SIZE int,
REMARKS varchar(254) NULL
)
GO
INSERT INTO #sp_result
EXEC ('sp_databases')
GO

二、使用信息架构视图访问元数据

信息架构视图基于 SQL-92 标准中针对架构视图的定义,这些视图独立于系统表,提供了关于 SQL Server 元数据的内部视图。信息架构视图的最大优点是,即使我们对系统表进行了重要的修改,应用程序也可以正常地使用这些视图进行访问。因此对于应用程序来说,只要是符合 SQL-92 标准的数据库系统,使用信息架构视图总是可以正常工作的。

常用的信息架构视图有以下一些:

信息架构视图

描述

INFORMATION_SCHEMA .CHECK_CONSTRAINTS

返回有关列或过程参数的信息,如是否允许空值,是否为计算列等。

INFORMATION_SCHEMA .COLUMNS

返回当前数据库中当前用户可以访问的所有列及其基本信息。

INFORMATION_SCHEMA .CONSTRAINT_COLUMN_USAGE

返回当前数据库中定义了约束的所有列及其约束名。

INFORMATION_SCHEMA .CONSTRAINT_TABLE_USAGE

返回当前数据库中定义了约束的所有表及其约束名。

INFORMATION_SCHEMA .KEY_COLUMN_USAGE

返回当前数据库中作为主键/外键约束的所有列。

INFORMATION_SCHEMA .SCHEMATA

返回当前用户具有权限的所有数据库及其基本信息。

INFORMATION_SCHEMA .TABLES

返回当前用户具有权限的当前数据库中的所有表或者视图及其基本信息。

INFORMATION_SCHEMA .VIEWS

返回当前数据库中的当前用户可以访问的视图及其所有者、定义等信息。

由于这些信息架构都是以视图的方式存在的,因此我们可以很方便地获得并利用需要的信息。

例如,我们要得到某个表有多少列,可以使用以下语句:

SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='mytable'

三、使用系统表访问元数据

虽然使用系统存储过程、系统函数与信息架构视图已经可以为我们提供了相当丰富的元数据信息,但是对于某些特殊的元数据信息,我们仍然需要直接对系统表进行查询。因为SQL Server 将所有数据库对象的信息均存放在系统表中,作为 SQL Server 的管理、开发人员,了解各个系统表的作用将有助于我们了解 SQL Server 的内在工作原理。

SQL Server 的系统表非常多,其中最常用的与元数据查询有关的表有如下一些:

 

系统表

描述

syscolumns

存储每个表和视图中的每一列的信息以及存储过程中的每个参数的信息。

syscomments

存储包含每个视图、规则、默认值、触发器、CHECK 约束、DEFAULT 约束和存储过程的原始 SQL 文本语句。

sysconstraints

存储当前数据库中每一个约束的基本信息。

sysdatabases

存储当前服务器上每一个数据库的基本信息。

sysindexes

存储当前数据库中的每个索引的信息。

sysobjects

存储数据库内的每个对象(约束、默认值、日志、规则、存储过程等)的基本信息。

sysreferences

存储所有包括 FOREIGN KEY 约束的列。

systypes

存储系统提供的每种数据类型和用户定义数据类型的详细信息。

将系统存储过程、系统函数、信息架构视图与系统表结合使用,可以方便地让我们获得所有需要的元数据信息。

示例:
1、 获得当前数据库所有用户表的名称。

SELECT OBJECT_NAME (id)
FROM sysobjects
WHERE xtype = 'U' AND OBJECTPROPERTY (id, 'IsMSShipped') = 0

其中主要用到了系统表 sysobjects以及其属性 xtype,还有就是用到了 OBJECTPROPERTY 系统函数来判断是不是安装 SQL Server 的过程中创建的对象。

2、 获得指定表上所有的索引名称

SELECT name FROM sysindexes
WHERE id = OBJECT_ID ('mytable') AND indid > 0

综合实例

下面给出了一个存储过程,它的作用是自动将当前数据库的用户存储过程加密。

DECLARE @sp_name nvarchar(400)
DECLARE @sp_content nvarchar(2000)
DECLARE @asbegin int
declare @now datetime
select @now = getdate()
DECLARE sp_cursor CURSOR FOR
SELECT object_name(id)
FROM sysobjects
WHERE xtype = 'P'
AND type = 'P'
AND crdate < @now
AND OBJECTPROPERTY(id, 'IsMSShipped')=0

OPEN sp_cursor

FETCH NEXT FROM sp_cursor
INTO @sp_name

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sp_content = text FROM syscomments WHERE id = OBJECT_ID(@sp_name)
SELECT @asbegin = PATINDEX ( '%AS' + char(13) + '%', @sp_content)
SELECT @sp_content = SUBSTRING(@sp_content, 1, @asbegin - 1)
+ ' WITH ENCRYPTION AS'
+ SUBSTRING (@sp_content, @asbegin+2, LEN(@sp_content))
SELECT @sp_name = 'DROP PROCEDURE [' + @sp_name + ']'
EXEC sp_executesql @sp_name
EXEC sp_executesql @sp_content
FETCH NEXT FROM sp_cursor
INTO @sp_name
END

CLOSE sp_cursor
DEALLOCATE sp_cursor

该存储过程利用了 sysobjects  syscomments 表,并巧妙地修改了原存储过程的 SQL 定义语句,将 AS 修改为了 WITH ENCRYPTION AS,从而达到了加密存储过程的目的。本存储过程在 SQL Server 2000 上通过。

 

 

Oracle:

 

Oracle常用数据字典

 

  以下表格中收集了Oracle数据字典中几乎所有的视图或别名,至于每个视图或别名的字段请用‘Describe语句来查询。

   命名说明:

   Oracle数据字典中,对象名称多数以"USER""ALL""DBA"为前缀,"USER"视图是当前用户拥有的对象,"ALL"视图是当前用户可访问的对象,"DBA"视图是数据库中所有的对象。

   使用举例:

   select * from dba_data_files

   查询表空间的信息(当前用户必须拥有DBA角色)

   select owner , object_name ,object_type from all_objects

   查询某一用户下的所有表、过程、函数等信息。

 

先看下表,再看视图,将会有助于理解视图的含义。

 

视图家族(View Family)

描述

COL_PRIVS

包含了表的列权限,包括授予者、被授予者和权限

EXTENTS

数据范围信息,比如数据文件,数据段名(segment_name)和大小

INDEXES

索引信息,比如类型、唯一性和被涉及的表

IND_COLUMNS

索引列信息,比如索引上的列的排序方式

OBJECTS

对象信息,比如状态和DDL time

ROLE_PRIVS

角色权限,比如GRANTADMIN选项

SEGMENTS

表和索引的数据段信息,比如tablespacestorage

SEQUECNCES

序列信息,比如序列的cachecycleast_number

SOURCE

除触发器之外的所有内置过程、函数、包的源代码

SYNONYMS

别名信息,比如引用的对象和数据库链接db_link

SYS_PRIVS

系统权限,比如granteeprivilegeadmin选项

TAB_COLUMNS

表和视图的列信息,包括列的数据类型

TAB_PRIVS

表权限,比如授予者、被授予者和权限

TABLES

表信息,比如表空间(tablespace),存储参数(storage parms)和数据行的数量

TRIGGERS

触发器信息,比如类型、事件、触发体(trigger body)

USERS

用户信息,比如临时的和缺省的表空间

VIEWS

视图信息,包括视图定义


  
Oracle中还有一些不常用的数据字典表,但这些表不是真正的字典家族,他们都是一些重要的单一的视图。

VIEW NAME

描述

USER_COL_PRIVS_MADE

用户授予他人的列权限

USER_COL_PRIVS_RECD

用户获得的列权限

USER_TAB_PRIVS_MADE

用户授予他人的表权限

USER_TAB_PRIVS_RECD

用户获得的表权限

 

以下为全部视图:

视图名

描述

ALL_CATALOG

All tables, views, synonyms, sequences accessible to the user

ALL_COL_COMMENTS

Comments on columns of accessible tables and views

ALL_COL_GRANTS_MADE

Grants on columns for which the user is owner or grantor

ALL_COL_GRANTS_RECD

Grants on columns for which the user or PUBLIC is the grantee

ALL_COL_PRIVS

Grants on columns for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee

ALL_COL_PRIVS_MADE

Grants on columns for which the user is owner or grantor

ALL_COL_PRIVS_RECD

Grants on columns for which the user, PUBLIC or enabled role is the grantee

ALL_CONSTRAINTS

Constraint definitions on accessible tables

ALL_CONS_COLUMNS

Information about accessible columns in constraint definitions

ALL_DB_LINKS

Database links accessible to the user

ALL_DEF_AUDIT_OPTS

Auditing options for newly created objects

ALL_DEPENDENCIES

Dependencies to and from objects accessible to the user

ALL_ERRORS

Current errors on stored objects that user is allowed to create

ALL_INDEXES

Descriptions of indexes on tables accessible to the user

ALL_IND_COLUMNS

COLUMNs comprising INDEXes on accessible TABLES

ALL_OBJECTS

Objects accessible to the user

ALL_REFRESH

All the refresh groups that the user can touch

ALL_REFRESH_CHILDREN

All the objects in refresh groups, where the user can touch the group

ALL_SEQUENCES

Description of SEQUENCEs accessible to the user

ALL_SNAPSHOTS

Snapshots the user can look at

ALL_SOURCE

Current source on stored objects that user is allowed to create

ALL_SYNONYMS

All synonyms accessible to the user

ALL_TABLES

Description of tables accessible to the user

ALL_TAB_COLUMNS

Columns of all tables, views and clusters

ALL_TAB_COMMENTS

Comments on tables and views accessible to the user

ALL_TAB_GRANTS_MADE

User's grants and grants on user's objects

ALL_TAB_GRANTS_RECD

Grants on objects for which the user or PUBLIC is the grantee

ALL_TAB_PRIVS

Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee

ALL_TAB_PRIVS_MADE

User's grants and grants on user's objects

ALL_TAB_PRIVS_RECD

Grants on objects for which the user, PUBLIC or enabled role is the grantee

ALL_TRIGGERS

Triggers accessible to the current user

ALL_TRIGGER_COLS

Column usage in user's triggers or in triggers on user's tables

ALL_USERS

Information about all users of the database

ALL_VIEWS

Text of views accessible to the user

USER_AUDIT_CONNECT

Audit trail entries for user logons/logoffs

USER_AUDIT_OBJECT

Audit trail records for statements concerning objects, specifically: table, cluster, view, index, sequence, [public] database link, [public] synonym, procedure, trigger, rollback segment, tablespace, role, user

USER_AUDIT_SESSION

 

USER_AUDIT_STATEMENT

Audit trail records concerning grant, revoke, audit, noaudit and alter system

USER_AUDIT_TRAIL

Audit trail entries relevant to the user

USER_CATALOG

Tables, Views, Synonyms and Sequences owned by the user

USER_CLUSTERS

Descriptions of user's own clusters

USER_CLU_COLUMNS

Mapping of table columns to cluster columns

USER_COL_COMMENTS

Comments on columns of user's tables and views

USER_COL_GRANTS

Grants on columns for which the user is the owner, grantor or grantee

USER_COL_GRANTS_MADE

All grants on columns of objects owned by the user

USER_COL_GRANTS_RECD

Grants on columns for which the user is the grantee

USER_COL_PRIVS

Grants on columns for which the user is the owner, grantor or grantee

USER_COL_PRIVS_MADE

All grants on columns of objects owned by the user

USER_COL_PRIVS_RECD

Grants on columns for which the user is the grantee

USER_CONSTRAINTS

Constraint definitions on user's own tables

USER_CONS_COLUMNS

Information about accessible columns in constraint definitions

USER_CROSS_REFS

Cross references for user's views and synonyms

USER_DB_LINKS

Database links owned by the user

USER_DEPENDENCIES

Dependencies to and from a users objects

USER_ERRORS

Current errors on stored objects owned by the user

USER_EXTENTS

Extents comprising segments owned by the user

USER_FREE_SPACE

Free extents in tablespaces accessible to the user

USER_INDEXES

Description of the user's own indexes

USER_IND_COLUMNS

COLUMNs comprising user's INDEXes or on user's TABLES

USER_JOBS

All jobs owned by this user

USER_OBJECTS

Objects owned by the user

USER_OBJECT_SIZE

Sizes, in bytes, of various pl/sql objects

USER_OBJ_AUDIT_OPTS

Auditing options for user's own tables and views

USER_REFRESH

All the refresh groups

USER_REFRESH_CHILDREN

All the objects in refresh groups, where the user owns the refresh group

USER_RESOURCE_LIMITS

Display resource limit of the user

USER_ROLE_PRIVS

Roles granted to current user

USER_SEGMENTS

Storage allocated for all database segments

USER_SEQUENCES

Description of the user's own SEQUENCEs

USER_SNAPSHOTS

Snapshots the user can look at

USER_SNAPSHOT_LOGS

All snapshot logs owned by the user

USER_SOURCE

Source of stored objects accessible to the user

USER_SYNONYMS

The user's private synonyms

USER_SYS_PRIVS

System privileges granted to current user

USER_TABLES

Description of the user's own tables

USER_TABLESPACES

Description of accessible tablespaces

USER_TAB_AUDIT_OPTS

Auditing options for user's own tables and views

USER_TAB_COLUMNS

Columns of user's tables, views and clusters

USER_TAB_COMMENTS

Comments on the tables and views owned by the user

USER_TAB_GRANTS

Grants on objects for which the user is the owner, grantor or grantee

USER_TAB_GRANTS_MADE

All grants on objects owned by the user

USER_TAB_GRANTS_RECD

Grants on objects for which the user is the grantee

USER_TAB_PRIVS

Grants on objects for which the user is the owner, grantor or grantee

USER_TAB_PRIVS_MADE

All grants on objects owned by the user

USER_TAB_PRIVS_RECD

Grants on objects for which the user is the grantee

USER_TRIGGERS

Triggers owned by the user

USER_TRIGGER_COLS

Column usage in user's triggers

USER_TS_QUOTAS

Tablespace quotas for the user

USER_USERS

Information about the current user

USER_VIEWS

Text of views owned by the user

AUDIT_ACTIONS

Description table for audit trail action type codes. Maps action type numbers to action type names

COLUMN_PRIVILEGES

Grants on columns for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee

DICTIONARY

Description of data dictionary tables and views

DICT_COLUMNS

Description of columns in data dictionary tables and views

DUAL

 

GLOBAL_NAME

global database name

INDEX_HISTOGRAM

statistics on keys with repeat count

INDEX_STATS

statistics on the b-tree

RESOURCE_COST

Cost for each resource

ROLE_ROLE_PRIVS

Roles which are granted to roles

ROLE_SYS_PRIVS

System privileges granted to roles

ROLE_TAB_PRIVS

Table privileges granted to roles

SESSION_PRIVS

Privileges which the user currently has set

SESSION_ROLES

Roles which the user currently has enabled.

TABLE_PRIVILEGES

Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee

ACCESSIBLE_COLUMNS

Synonym for ALL_TAB_COLUMNS

ALL_COL_GRANTS

Synonym for COLUMN_PRIVILEGES

ALL_JOBS

Synonym for USER_JOBS

ALL_TAB_GRANTS

Synonym for TABLE_PRIVILEGES

CAT

Synonym for USER_CATALOG

CLU

Synonym for USER_CLUSTERS

COLS

Synonym for USER_TAB_COLUMNS

DBA_AUDIT_CONNECT

Synonym for USER_AUDIT_CONNECT

DBA_AUDIT_RESOURCE

Synonym for USER_AUDIT_RESOURCE

DBA_REFRESH_CHILDREN

Synonym for USER_REFRESH_CHILDREN

DICT

Synonym for DICTIONARY

IND

Synonym for USER_INDEXES

OBJ

Synonym for USER_OBJECTS

SEQ

Synonym for USER_SEQUENCES

SM$VERSION

Synonym for SM_$VERSION

SYN

Synonym for USER_SYNONYMS

TABS

Synonym for USER_TABLES

V$ACCESS

Synonym for V_$ACCESS

V$ARCHIVE

Synonym for V_$ARCHIVE

V$BACKUP

Synonym for V_$BACKUP

V$BGPROCESS

Synonym for V_$BGPROCESS

V$CIRCUIT

Synonym for V_$CIRCUIT

V$COMPATIBILITY

Synonym for V_$COMPATIBILITY

V$COMPATSEG

Synonym for V_$COMPATSEG

V$CONTROLFILE

Synonym for V_$CONTROLFILE

V$DATABASE

Synonym for V_$DATABASE

V$DATAFILE

Synonym for V_$DATAFILE

V$DBFILE

Synonym for V_$DBFILE

V$DBLINK

Synonym for V_$DBLINK

V$DB_OBJECT_CACHE

Synonym for V_$DB_OBJECT_CACHE

V$DISPATCHER

Synonym for V_$DISPATCHER

V$ENABLEDPRIVS

Synonym for V_$ENABLEDPRIVS

V$FILESTAT

Synonym for V_$FILESTAT

V$FIXED_TABLE

Synonym for V_$FIXED_TABLE

V$LATCH

Synonym for V_$LATCH

V$LATCHHOLDER

Synonym for V_$LATCHHOLDER

V$LATCHNAME

Synonym for V_$LATCHNAME

V$LIBRARYCACHE

Synonym for V_$LIBRARYCACHE

V$LICENSE

Synonym for V_$LICENSE

V$LOADCSTAT

Synonym for V_$LOADCSTAT

V$LOADTSTAT

Synonym for V_$LOADTSTAT

V$LOCK

Synonym for V_$LOCK

V$LOG

Synonym for V_$LOG

V$LOGFILE

Synonym for V_$LOGFILE

V$LOGHIST

Synonym for V_$LOGHIST

V$LOG_HISTORY

Synonym for V_$LOG_HISTORY

V$MLS_PARAMETERS

Synonym for V_$MLS_PARAMETERS

V$MTS

Synonym for V_$MTS

V$NLS_PARAMETERS

Synonym for V_$NLS_PARAMETERS

V$NLS_VALID_VALUES

Synonym for V_$NLS_VALID_VALUES

V$OPEN_CURSOR

Synonym for V_$OPEN_CURSOR

V$OPTION

Synonym for V_$OPTION

V$PARAMETER

Synonym for V_$PARAMETER

V$PQ_SESSTAT

Synonym for V_$PQ_SESSTAT

V$PQ_SLAVE

Synonym for V_$PQ_SLAVE

V$PQ_SYSSTAT

Synonym for V_$PQ_SYSSTAT

V$PROCESS

Synonym for V_$PROCESS

V$QUEUE

Synonym for V_$QUEUE

V$RECOVERY_LOG

Synonym for V_$RECOVERY_LOG

V$RECOVER_FILE

Synonym for V_$RECOVER_FILE

V$REQDIST

Synonym for V_$REQDIST

V$RESOURCE

Synonym for V_$RESOURCE

V$ROLLNAME

Synonym for V_$ROLLNAME

V$ROLLSTAT

Synonym for V_$ROLLSTAT

V$ROWCACHE

Synonym for V_$ROWCACHE

V$SESSION

Synonym for V_$SESSION

V$SESSION_CURSOR_CACHE

Synonym for V_$SESSION_CURSOR_CACHE

V$SESSION_EVENT

Synonym for V_$SESSION_EVENT

V$SESSION_WAIT

Synonym for V_$SESSION_WAIT

V$SESSTAT

Synonym for V_$SESSTAT

V$SESS_IO

Synonym for V_$SESS_IO

V$SGA

Synonym for V_$SGA

V$SGASTAT

Synonym for V_$SGASTAT

V$SHARED_SERVER

Synonym for V_$SHARED_SERVER

V$SQLAREA

Synonym for V_$SQLAREA

V$STATNAME

Synonym for V_$STATNAME

V$SYSSTAT

Synonym for V_$SYSSTAT

V$SYSTEM_CURSOR_CACHE

Synonym for V_$SYSTEM_CURSOR_CACHE

V$SYSTEM_EVENT

Synonym for V_$SYSTEM_EVENT

V$THREAD

Synonym for V_$THREAD

V$TIMER

Synonym for V_$TIMER

V$TRANSACTION

Synonym for V_$TRANSACTION

V$TYPE_SIZE

Synonym for V_$TYPE_SIZE

V$VERSION

Synonym for V_$VERSION

V$WAITSTAT

Synonym for V_$WAITSTAT

V$_LOCK

Synonym for V_$_LOCK </TBODY>

 

 

 

 

十二、相互移植时的注意事项

   要将 Oracle DML 语句和 PL/SQL 程序迁移到 SQL Server 时,请按下列步骤执行:

1.     验证所有 SELECTINSERTUPDATE  DELETE 语句的语法是有效的。

2.     把所有外部联接改为 SQL-92 标准外部联接语法。

3.     用相应 SQL Server 函数替代 Oracle 函数。

4.     检查所有的比较运算符。

5.     用+字符串串联运算符代替||字符串串联运算符。

6.     用 T-SQL 程序代替 PL/SQL 程序。

7.     把所有 PL/SQL 游标改为非游标 SELECT 语句或 Transact-SQL 游标。

8.     用 T-SQL 过程代替 PL/SQL 过程、函数和包。

9.     把 PL/SQL 触发器转换为 Transact-SQL 触发器。

10.  使用 SET SHOWPLAN 语句,优化查询性能。

十三、完全卸载

  • SQL Server 卸载十分简单,不再累述。
  • Oracle卸载比较复杂,步骤如下:

1、开始->设置->控制面板->管理工具->服务

   停止所有Oracle服务。

2、开始->程序->Oracle Installation Products->Universal Installer

   卸装所有Oracle产品

3、运行regedit,选择HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE,按del键删除这个入口。

4、运行regedit,选择HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services,滚动这个列表,删除所有Oracle入口。

5、从桌面上删除所有有关Oracle的组和图标(如果有的话)

6、重新启动计算机,重起后才能完全删除Oracle所在目录。

7、删除C:\Program Files目录下的Oracle目录,并从C:\WINNTC:\WINDOWS下删除

以下文件 ORACLE.INIoradim90.INI(如果有的话)

8WIN.INI文件中若有[ORACLE]的标记段,删除该段。

 

十四、作者简介

袁平,男,汉族。93年毕业于成都电子科技大学电子机械系,同年七月分配到郑州日产汽车有限公司设备处,95年辞职到某软件公司。99年年底任财政部**公司河南分公司技术部副经理(其间研究过财政部通用财会软件,该系统是全国推广)。2000年年底任**财务软件公司副总兼开发部经理。现任****研发中心 ERP技术顾问(研发中心主要开发基础架构平台)。

本人有十年软件研发经验,作品众多。其中2001年在**公司所主持开发的大型行政事业版财会软件《预通2000国家版权局专利证书(两层C/S结构、Delphi 5.0/SQL Server 2000个模块、源码约30万行、项目组共9人、开发周期约年),专利号******,有源码,仅郑州市就有100多家行政事业单位的财务部门在使用,如河南省高级人民法院河南省广电厅等等,在南阳还有几十个用户。之后为河南省高级人民法院开发了诉讼费管理系统,已无源码。2002年受人之托,为郑州******公司开发规费征收系统的财务部分,该系统是全省推广,有财务部分的源码,其它部分是由其它同志用PB 7.0写的。2003年为郑州最大的房地产公司*****作了一个楼盘管理系统EMLS三层C/S结构、Delphi 7.0Oracle 9iOracle 9i数据库在远程internet上、SocketConnection服务器在商都信息港机房),有源码。当年还开发了郑州卷烟厂物业管理软件,有源码,郑州烟厂共有8个小区,3000多户居民。2000年还为财政部通用公司河南分公司开发了会计考试程序,河南省行政事业单位的会计均采用此程序进行考试,已无源码。同期为**人事局开发了人事工资管理系统。而本人的第一个商品化软件则是95年为广西桂林某电厂设计的一个电厂电量管理系统(FoxProw for DOS,DOS版本,已无源码)。

本人99年曾到微软(中国)总部参加SQL Server 2000的培训,并曾任****培训中心SQL Server 2000 培训教师(2000年兼职),同期还为该中心开发过ASP组件并代表****建业房地产公司设计过一个多层分布式电子商务系统方案(由于种种原因未被采用),当时(2001年)设计的架构为:建业WEB应用系统=ASP+JavaScript+ADOExpress(也可采用BDE/IDAPI存取数据)+定制ASP对象(InternetExpressMIDASWebBroker)+MTS中间件(或COM+对象),无源码。此前还为该中心开发了一个学校自动排课系统,有源码。2002年年底兼职为北大青鸟地平线讲过项目开发的课。

本人精通DelphiSQL Server 2000等等,熟悉Oracle 9i,熟悉软件工程,熟悉Internet编程,研究过C#,了解PB 8.0,对面向对象的编程思想有着深刻的认识。目前正在为****设计施工行业的ERPERP是基于我们自行开发的基础架构平台上的二次开发(有关此平台的内容不在此介绍),此前我们已成功开发过一个ERPC#/ SQL Server 2000),其核心财务部分有源码。其它开发过的产品(项目),不再一一介绍。

联系方式:E-mailyznytz@sina.com yznytz8@hotmail.com

             QQ 350431253   MSN:  yznytz8@hotmail.com

电话: 6682**92(宅)、 6**41248(宅)

手机: 13**3839687 1301459**92

 

<!--3-->附录1Oracle中显示SQL语句的内存使用情况

Oracle9i可以在显示RAM内存使用情况的同时提供执行计划信息,要获得这方面的信息,你需要从V$SQL出发搜集合适的SQL语句的地址。举例来说,如果你有一个关于NEW_CUSTOMER表格运作方面的疑问时,你可以运行下面的代码来获得地址。

 

select

address

from

v$sql

where

sql_text like '%NEW_CUSTOMER';

88BB460C

1 row selected.

 

获得了地址以后,你可以将他插入下列脚本之中来获得执行计划的详细资料和SQL语句的PGA内存使用状况。

 

select

operation,

options,

object_name name,

trunc(bytes/1024/1024) "input(MB)",

trunc(last_memory_used/1024) last_mem,

trunc(estimated_optimal_size/1024) opt_mem,

trunc(estimated_onepass_size/1024) onepass_mem,

decode(optimal_executions, null, null,

optimal_executions||'/'||onepass_executions||'/'||

multipasses_exections) "O/1/M"

from

v$sql_plan p,

v$sql_workarea w

where

p.address=w.address(+)

and

p.hash_value=w.hash_value(+)

and

p.id=w.operation_id(+)

and

p.address='88BB460C';

 

下面是来自这个脚本的表单:

 

OPERATION OPTIONS NAME input(MB)

LAST_MEM OPT_MEM ONEPASS_MEM O/1/M

 

------------ -------- ---- ---------

 

-------- ---------- ---------- ----

 

SELECT STATESORT GROUP BY 4582 8 16

16 26/0/0HASH JOIN SEMI 4582 5976

5194 187 16/0/0TABLE ACCESS FULL ORDERS

 

51TABLE ACCESS FUL LINEITEM 1000

 

你可以在其中看到关于执行计划和特殊内存使用方面的细节,这是Oracle9i的一个令人激动的进步,他使得DBA可以获得关于任何SQL语句内部执行过程的高度详细的资料。

附录2:保持Oracle优良性能的若干诀窍

   如何使Oracle数据库保持优良性能,是许多数据库管理员关心的问题,笔者建议不妨针对以下几个方面加以考虑。

 

分区

 

根据实际经验,在一个大数据库中,数据库空间的绝大多数是被少量的表所占有。为了简化大型数据库的管理,改善应用的查询性能,一般可以使用分区这种手段。所谓分区就是动态地将表中的记录分离到若干不同的表空间上,使数据在物理上被分割开来,便于维护、备份、恢复、事务及查询性能。当使用的时候可建立一个连接所有分区的视图,使其在逻辑上仍以一个整体出现。

 

1. 建立分区表

 

Create table Employee (

 

EmpNo varchar2(10) primary key,

 

Name varchar2(30),

 

DeptNo Number(2)

 

)

 

Partition by range(DeptNo)

 

( partition PART1 values less than (11)

 

tablespace PART1_TS,

 

partition PART2 values less than (21)

 

tablespace PART2_TS,

 

partition PART3 values less than (31)

 

tablespace PART3_TS

 

partition PART4 values less than (MAXVALUE)

 

tablespace PART4_TS

 

);

 

Employee依据DeptNo列进行分区。

 

2. 分区索引

 

Create index Employee_DeptNo on Employee(DeptNo) local (

 

partition PART1 tablespace PART1_NDX_TS,

 

partition PART2 tablespace PART2_NDX_TS,

 

partition PART3 tablespace PART3_NDX_TS,

 

partition PART4 tablespace PART4_NDX_TS,

 

);

 

当分区中出现许多事务并且要保证所有分区中的数据记录的惟一性时采用全局索引,在建立全局索引时,Global子句允许指定索引的范围值,这个范围值可以不同于表分区的范围值。只有建立局部索引才会使索引分区与表分区间建立起一一对应关系。因此,在大多数情况下,应该使用局部索引分区。若使用了此索引,分区就能够很容易地将索引分区与表分区建立关联,局部索引比全局索引更易于管理。

 

3. 分区管理

 

根据实际需要,还可以使用Alter table 命令来增加、删除、交换、移动、修改、重命名、划分、截短一个已存在分区的结构。

 

重建索引

 

如果表中记录频繁地被删除或插入,尽管表中的记录总量保持不变,索引空间的使用量会不断增加。虽然记录从索引中被删除,但是该记录索引项的使用空间不能被重新使用。因此,如果表变化不定,索引空间量会不断增加,不论表中记录数量是否增加,只是因为索引中无效空间会增加。

 

要回收那些曾被删除记录使用的空间,需要使用Alter index rebuild 命令。可以做一个定期运行的批处理程序,来重建最活动表的索引。这个批处理程序可以在空闲时运行,以避免该程序与其他应用程序冲突。若能坚持索引的这一程序规划,便可以及时回收那些未使用空间,提高空间利用率。

 

段的碎片整理

 

当生成一个数据库对象时(一个表或一个索引),通过用户缺省值或指定值来为它指定表空间。一个在表空间中生成的段,用于存储对象的相关数据。在段被关闭、收缩、截断之前,段所分配的空间将不被释放。

 

一个段是由范围组成,而范围是由相邻的Oracle块组成。一旦存在的范围不能再存储新的数据,这个段就会去获得新的范围,但并不要求这些范围是彼此相邻的。这样的扩展会一直继续下去,直到表空间中的数据文件不能提供更多的自由空间,或者范围数量已达到极限。

 

因此,一个碎片太多的数据段,不仅会影响运行,也会引发表空间中的空间管理问题。所以,每个数据段只含有一个范围是十分有益的。借助监控系统,可以通过检查DBA_SEGMENTS数据字典视图来了解哪些数据库对象含有10个或更多范围的段,确定其数据段碎片。

 

若一个段的碎片过多,可用两种方法解决:

 

1. 用正确的存储参数建立一个新表,将旧表中的数据插入到新表中,再删除旧表;

 

2. 利用Export/Import工具。

 

如:exp system/manager file=exp.dmp compress=Y grants=Y indexes=Y

 

tables=(T1,T2)

 

若输出成功,进入Oracle,删除上述表。

 

注:compress=Y表示将在输出过程中修改它们的存储参数。

 

imp system/manager file=exp.dmp commit=Y buffer=64000 full=Y

 

注:在输入时重新配置新的存储参数。

 

自由范围的碎片整理

 

表空间中的一个自由范围是表空间中相连的自由(空间)块的集合。当一个段关闭时,它的范围将被释放,并被标记为自由范围。然而,这些自由范围再也不能与相邻的自由范围合并,它们之间的界线始终存在。但是当表空间的缺省值pctincrease设置不是0时,SMON后台进程会定期将这些相邻的自由范围合并。若pctincrease设置为0,那么相邻自由范围不会被数据库自动合并。但可以使用Alter table命令“coalesce选项,来强迫进行相邻自由范围的合并。

 

不进行自由范围合并,在日后的空间请求中,会影响到表空间中的空间分配。当需要一个足够大的范围时,数据库并不会合并相邻的自由范围,除非没有其他选择。这样,当表空间中前面较小的自由范围已被使用时,将使用表空间中后面部分最大的一个自由范围。结果,会因为没有足够多的使用空间,从而导致表空间需求的矛盾。由于这样的情况出现,使数据库的空间分配距理想越来越远。自由空间碎片常会出现在那些经常关闭又重新生成的数据库表和索引中。

 

在理想的Oracle表空间中,每一个数据库对象存储在一个单独的范围中,并且所有有效自由空间集中在一个巨大而连续的范围中。这样,在一个对象需要附加存储空间时,可以在增加获取足够大自由空间的可能性的同时,最小化空间中的循环调用,提高自由空间使用率。

 

附录3Oracle中查看本用户下的各种对象的SQL脚本

表:

  select * from cat;

  select * from tab;

  select table_name from user_tables;

视图:

  select text from user_views where view_name=upper('&view_name');

索引:

  select index_name,table_owner,table_name,tablespace_name,status from user_indexes order by table_name;

触发器:

  select trigger_name,trigger_type,table_owner,table_name,status from user_triggers;

快照:

  select owner,name,master,table_name,last_refresh,next from user_snapshots order by owner,next;

同义词:

  select * from syn;

序列:

  select * from seq;

数据库链路:

  select * from user_db_links;

约束限制:

  select TABLE_NAME,CONSTRAINT_NAME,SEARCH_CONDITION,STATUS from user_constraints;

本用户读取其他用户对象的权限:

  select * from user_tab_privs;

本用户所拥有的系统权限:

  select * from user_sys_privs;

用户:

  select * from all_users order by user_id;

表空间剩余自由空间情况:

  select tablespace_name,sum(bytes) 总字节数,max(bytes),count(*) from dba_free_space group by tablespace_name

数据字典:

  select table_name from dict order by table_name;

锁及资源信息:

  select * from v$lock;不包括DDL

数据库字符集:

  select name,value$ from props$ where name='NLS_CHARACTERSET';

inin.ora参数:

  select name,value from v$parameter order by name;

SQL共享池:

  select sql_text from v$sqlarea;

数据库:

  select * from v$database

控制文件:

  select * from V$controlfile;

重做日志文件信息:

  select * from V$logfile;

来自控制文件中的日志文件信息:

  select * from V$log;

来自控制文件中的数据文件信息:

  select * from V$datafile;

NLS参数当前值:

  select * from V$nls_parameters;

ORACLE版本信息:

  select * from v$version;

描述后台进程:

  select * from v$bgprocess;

查看版本信息:

  select * from product_component_version;

附录4Oracle数据操作和控制语言详解

 

  SQL语言共分为四大类:数据查询语言DQL,数据操纵语言DML, 数据定义语言DDL,数据控制语言DCL。其中用于定义数据的结构,比如 创建、修改或者删除数据库;DCL用于定义数据库用户的权限;下面详细讲述这两种语言在Oracle中的使用方法。

 

  DML语言

 

  DMLSQL的一个子集,主要用于修改数据,下表列出了ORACLE支持的DML语句。

 

 

 

语句 用途

INSERT 向表中添加行

UPDATE 更新存储在表中的数据

DELETE 删除行

SELECT FOR UPDATE 禁止其他用户访问DML语句正在处理的行。

LOCK TABLE 禁止其他用户在表中使用DML语句

 

  插入数据

 

  INSERT语句常常用于向表中插入行,行中可以有特殊数据字段,或者可以用子查询从已存在的数据中建立新行。

 

  列目录是可选的,缺省的列的目录是所有的列名,包括comlumn_id,comlumn_id可以在数据字典视图ALL_TAB_COLUMNS,USER_TAB_COLUMNS,或者DBA_TAB_COLUMNS中找到。

 

  插入行的数据的数量和数据类型必须和列的数量和数据类型相匹配。不符合列定义的数据类型将对插入值实行隐式数据转换。NULL字符串将一个NULL值插入适当的列中。关键字NULL常常用于表示将某列定义为NULL值。

 

  下面的两个例子是等价的。

 

 

INSERT INTO customers(cust_id,state,post_code)

VALUE('Ariel',NULL,'94501');

 

  或

 

INSERT INTO customers(cust_id,state,post_code)

VALUE('Ariel',,'94501');

 

  更新数据

 

  UPDATE命令用于修改表中的数据。

 

UPDATE order_rollup

SET(qty,price)=(SELECT SUM(qty),SUM(price) FROM order_lines WHERE customer_id='KOHL'

WHERE cust_id='KOHL' AND order_period=TO_DATE('01-Oct-2003')

 

  删除数据

 

  DELETE语句用来从表中删除一行或多行数据,该命令包含两个语句:

 

   1、关键字DELETE FROM后跟准备从中删除数据的表名。

 

   2WHERE后跟删除条件

 

DELETE FROM po_lines

WHERE ship_to_state IN ('TX','NY','IL')

AND order_date 

 

  清空表

 

  如果你想删除表中所有数据,清空表,可以考虑使用DDL语言的TRUNCATE语句。TRUNCATE就像没有WHERE子句的DELETE命令一样。TRUNCATE将删除表中所有行。TRUNCATE不是DML语句是DDL语句,他和DELETE有不同的特点。

 

TRUNCATE TABLE (schema)table DROP(REUSE) STORAGE

 

  STORAGE子串是可选的,缺省是DROP STORAGE。当使用DROP STORAGE时将缩短表和表索引,将表收缩到最小范围,并重新设置NEXT参数。REUSE STORAGE不会缩短表或者调整NEXT参数。

 

  TRUNCATEDELETE有以下几点区别

 

  1TRUNCATE在各种表上无论是大的还是小的都非常快。如果有ROLLBACK命令DELETE将被撤销,而TRUNCATE则不会被撤销。

 

  2TRUNCATE是一个DDL语言,向其他所有的DDL语言一样,他将被隐式提交,不能对TRUNCATE使用ROLLBACK命令。

 

  3TRUNCATE将重新设置高水平线和所有的索引。在对整个表和索引进行完全浏览时,经过TRUNCATE操作后的表比DELETE操作后的表要快得多。

 

  4TRUNCATE不能触发任何DELETE触发器。

 

  5、不能授予任何人清空他人的表的权限。

 

  6、当表被清空后表和表的索引讲重新设置成初始大小,而delete则不能。

 

  7、不能清空父表。

 

  SELECT FOR UPDATE

 

  select for update语句用于锁定行,阻止其他用户在该行上修改数据。当该行被锁定后其他用户可以用SELECT语句查询该行的数据,但不能修改或锁定该行。

 

  锁定表

 

  LOCK语句常常用于锁定整个表。当表被锁定后,大多数DML语言不能在该表上使用。LOCK语法如下:

 

LOCK schema table IN lock_mode

 

  其中lock_mode有两个选项:

 

   share 共享方式

 

   exclusive 唯一方式

 

  例:

 

LOCK TABLE intentory IN EXCLUSIVE MODE

 

  死锁

 

  当两个事务都被锁定,并且互相都在等待另一个被解锁,这种情况称为死锁。

 

  当出现死锁时,ORACLE将检测死锁条件,并返回一个异常。

 

  事务控制

 

  事务控制包括协调对相同数据的多个同步的访问。当一个用户改变了另一个用户正在使用的数据时,oracle使用事务控制谁可以操作数据。

 

  事务

 

  事务表示工作的一个基本单元,是一系列作为一个单元被成功或不成功操作的SQL语句。在SQLPL/SQL中有很多语句让程序员控制事务。程序员可以:

 

   1、显式开始一个事物,选择语句级一致性或事务级一致性

 

   2、设置撤销回滚点,并回滚到回滚点

 

   3、完成事务永远改变数据或者放弃修改。

  

  事务控制语句

 

 

 

语句 用途

Commit 完成事务,数据修改成功并对其他用户开放

Rollback 撤销事务,撤销所有操作

rollback to savepoint 撤销在设置的回滚点以后的操作

set transaction 响应事务或语句的一致性;特别对于事务使用回滚段

 

 

  例:

 

BEGIN

UPDATE checking

SET balance=balance-5000

WHERE account='Kieesha';

 

INSERT INTO checking_log(action_date,action,amount)

VALUES (SYSDATE,'Transfer to brokerage',-5000);

 

UPDATE brokerage

SET cash_balance=cash_balance+5000

WHERE account='Kiesha';

 

INSERT INTO brokerage_log(action_date,action,amount)

VALUES (SYSDATE,'Tracfer from checking',5000)

 

COMMIT

 

EXCEPTION

WHEN OTHERS

ROLLBACK

 

END

 

  Savepoint 和 部分回滚(Partial Rollback)

 

  在SQLPL/SQLSavepoint是在一事务范围内的中间标志。经常用于将一个长的事务划分为小的部分。保留点Savepoint可标志长事务中的任何点,允许可回滚该点之后的操作。在应用程序中经常使用Savepoint;例如一过程包含几个函数,在每个函数前可建立一个保留点,如果函数失败,很容易返回到每一个函数开始的情况。在回滚到一个Savepoint之后,该Savepoint之后所获得的数据封锁被释放。为了实现部分回滚可以用带TO Savepoint子句的ROLLBACK语句将事务回滚到指定的位置。

 

  例

 

BEGIN

 

INSERT INTO ATM_LOG(who,when,what,where)

VALUES ('Kiesha',SYSDATE,'Withdrawal of $100','ATM54')

SAVEPOINT ATM_LOGGED;

 

UPDATE checking

SET balance=balance-100

RETURN balance INTO new_balance;

 

IF new_balance<0

THEN

ROLLBACK TO ATM_LOGGED;

COMMIT

RAISE insufficient_funda;

END IF

 

END

 

 

  关键字SAVEPOINT是可选的,所以下面两个语句是等价的:

 

ROLLBACK TO ATM_LOGGED;

ROLLBACK TO SAVEPOINT ATM_LOGGED;

 

  一致性和事务

 

  一致性是事物控制的关键慨念。掌握了oracle 的一致性模型,能使您更好的,更恰当的使用事务控制。oracle通过一致性保证数据只有在事务全部完成后才能被用户看见和使用。这项技术对多用户数据库有巨大的作用。

 

  oracle常常使用语句级(state-level)一致性,保证数据在语句的生命期之间是可见的但不能被改变。事务由多个语句组成,当使用事务时,事物级(transaction-level)一致性在整个事务生命期中保证数据对所有语句都是可见的。

 

  oracle通过SCN(syatem change number)实施一致性。一个SCN是一个面向时间的数据库内部键。SCN只会增加不会减少,SCN表示了时间上的一个点,每个数据块都有一个SCN,通过比较这个点实施操作。

 

  事务级一致性

 

  SET TRANSACTION 的一个作用是确保事务级一致或语句级一致中有一个实施。ORACLE使用这些术语:

 

   ISOLATION LEVEL READ COMMIT 表示语句级一致

 

   ISOLATION LEVEL SERIALIZABLE 表示事务级一致。

 

  例:

 

SET TRANSACTION ISOLATION LEVEL READ COMMIT;

 

SET TRANSACTION ISOLATION LEVEL READ COMMIT

 

  下面的语句也能确保事务级一致:

 

SET TRANSCATION READ ONLY

 

  任何企图在只读(READ ONLY)事务中修改数据的操作都会抛出一个异常。但是,READ ONLY事务只能在下列语句中使用:

 

SELECT(没有FOR UPDATE子句)

LOCK TABLE

SET ROLE

ALTER SYSTEM

ALTER ALARM

 

  即使没有改变任何数据,READ ONLY事务依然必须使用一个COMMITROLLBACK以结束整个事务。

 

  SET TRANSCTION的另外一个应用是在回滚时直接使用回滚段(ROLLBACK SEGMENT)。回滚段是ORACLE的一个特殊的数据对象,回滚段的头部包含正在使用该回滚段事务的信息。当用户回滚事务(ROLLBACK)时,ORACLE将会利用回滚段中的数据前影像来将修改的数据恢复到原来的值。oracleround-robin给事务随机分配回滚段。一个大的事务可以分配任何回滚段,这也许会导致回滚段的大小变得很大。因此要避免让大的事务随机分配回滚段。

 

  事务以SET TRANSACTION开始,象下面这样:

 

SET TRANSACTION USE ROLLBACK SEGMENT rb_large;

 

  rb_large是一个大的回滚段的名称,现在就给一个大的事务分配了一个大的回滚段,其他的小的回滚段将不由动态空间管理,这样就更有效率。

 

  下面我们看一个例子.我们有一个回滚段表空间大小是2G,在高峰时期需要10个回滚段以满足用户的需要,这些高峰在线用户只有小的事务。一周我们连续运行了4个大的事务,这些事务需要删除和加载数据,每一个撤销需要1G,回滚段的大小如下:

 

rb_large(initial 100M minextenta 2)

 

rb1 (initial 1M next minextents 5)

rb2 (initial 1M next minextents 5)

rb3 (initial 1M next minextents 5)

rb4 (initial 1M next minextents 5)

rb5 (initial 1M next minextents 5)

rb6 (initial 1M next minextents 5)

rb7 (initial 1M next minextents 5)

rb8 (initial 1M next minextents 5)

rb9 (initial 1M next minextents 5)

rb10 (initial 1M next minextents 5)

 

  所有的都非常恰当的安排在2G的表空间中,如果我们缺省的round-robin给事务分配回滚段,4个大事务将有4个独立的回滚段,每个回滚段的大小将是1G,如果这样我们的2G表空间就不够,而数据库管理员就不得不在夜晚2点起来工作,每个事务都由以下面的语句开始:

 

SET TRANSACTION USE ROLLBACK SEGMENT rb_large

 

  现在 4个事务重用相同的表空间,保正4个回滚段的表空间在2G以内。数据库管理员可以睡到天亮。

 

  建立和修改用户

 

  CREATE USER 语句将建立一个用户。当一个用户连接到ORACLE数据库时,它必须被验证。ORACLE中验证有三种类型:

 

   Database

 

   external

 

   Global

 

  缺省是数据库验证,当用户连接到数据库时,oracle将检测用户是否是数据库的合法用户,并且要提供正确的password.external验证,oracle将只检测用户是否是合法用户,password已经被网络或系统验证了。global验证也是只检测是否是合法用户,passwordoraclesecurity server验证。

 

  Database验证用户账号

 

  数据库验证账号是张好的缺省类型,也是最普通的类型。建立一个账号是piyush,口令是welcome的账号,只需执行下面的命令:

 

CREATE USE piyush IDENTIFIED BY welcome

 

  piyush可以通过下面的语句将口令改变为saraswatt:

 

ALTER USER piyush IDENTIFIED BY saraswati;

 

  外部验证用户账号

 

  用户账号进入数据库时可以不提供口令,这种情况下代替数据库识别口令的是客户端操作系统。外部验证账号有时也叫OPS$账号,当他们最初在oracle6开始介绍时,oracle账号都有关键字前缀OPS$,这也就是为什么init.ora 参数os_authent_prefixOPS$--默认特征与oracle6保持一致。os_authent_prefix定义的字符串必须被预处理为用于Oracle外部识别账号的操作系统账号名。创建操作系统用户appl的语句是:

 

CREATE USER ops$appl IDENTIFIED EATERNALLY

 

  但在通常情况下,os_authent_prefix将被设置为空,像下面这样:

 

CREATE USER appl IDENTIFIED EATERNALLY

 

  这样效果是一样的,关键字IDENTIFIED EXTERNALLY告诉ORACLE这是一个外部识别账号。

 

  GLOBAL用户账号

 

  GLOBAL类型的用户账号数据库不检测口令,而是由X.509目录服务器检测口令。创建一个GLOBAL类型的用户账号的方法是:

 

CREATE USER scott IDENTIFIED GLOBALLY AS "CN=scott,OU=divisional,O=sybex,C=US"

 

  关键字IDENTIFIED GLOBALLY AS表示建立的是一个GLOBAL类型的用户账号.

 

  创建和更改用户账号

 

  CREATE USER 用于建立用户账号和给用户账号的属性赋值。ALTER USER用于更改用户账号和属性。但CREATE USER语句必须包括用户名和口令。

 

  有部分属性能用CREATER USERALTER USER语句设置,下面对是这些的属性具体描述:

 

  给用户分配缺省表空间

 

  表空间(tablespace)是放置表、索引、丛等用户对象的。如果在create user语句中没有包含表空间,那么缺省的是系统表空间。

 

CREATE USER piyush IDENTIFIED BY saraswati

DEFAULTE TABLESPACE user_data;

ALTER USER manoj DEFAULTE TABLESPACE dev1_data;

 

  给用户分配临时表空间

 

  临时表空间,顾名思义是临时存放表、索引等用户对象的临时段。建立方法一样

 

CREATE USER piyush IDENTIFIED BY saraswati

Temporary TABLESPACE user_data;

ALTER USER manoj Temporary TABLESPACE dev1_data;

 

  给用户分配表空间的使用定额

 

  使用定额限制用户在表空间中使用磁盘的数量。定额可以按字节、千字节、兆字节或者无限制来制定。

 

CREATE USER piyush IDENTIFIED BY saraswati

DEFAULT TABLESPACE user_data

QUOTA UNLIMITED ON user_data

QUOTA 20M ON tools;

ALTER USER manoj QUOTA 2500K ON tools;

 

  给用户分配一个简表

 

  简表可以限制用户在会话时消耗的资源。这些资源包括:连接数据库的时间,空闲时间,每次会话的逻辑读数据的数量等等,缺省的简表对资源无限制。

 

CREATE USER piyush IDENTIFIED BY saraswati

PROFILE TABLESPACE user_data;

ALTER USER manoj Temporary TABLESPACE dev1_data;

 

  为用户响应指定角色

 

  这个属性只能由ALTER USER语句设置,试图用CREATE USER语句设置将回返回一个例外。

 

ALTER USER manoj DEFAULT ROLE ALL EXCEPT salary_adm;

 

  为用户的password设定到期时间以便在用户下次登录时更改

 

  当用户的password到期,在下一次登录时将强迫修改passwordoracle提示用户输入旧的password,然后输入新的password。这项功能常用于新用户,当新用户用缺省的password登录时必须修改立即修改password.

 

ALTER USER manoj IDENTIFIED BY welcome;

ALTER USER manoj PASSWORD EXPIRE;

 

  锁定账号,是用户不能登录

 

ALTER USER ql AC

COUNT LOCK

 

  对账号解锁,以便用户能登录数据库

 

ALTER USER ql ACCOUNT UNLOCK

 

  权限和角色

 

  权限允许用户访问属于其它用户的对象或执行程序,ORACLE系统提供三种权限:

 

   Object 对象级

 

   System 系统级

 

   Role 角色级

 

  这些权限可以授予给用户、特殊用户public或角色,如果授予一个权限给特殊用户"Public"(用户publicoracle预定义的,每个用户享有这个用户享有的权限),那么就意味作将该权限授予了该数据库的所有用户。

 

  对管理权限而言,角色是一个工具,权限能够被授予给一个角色,角色也能被授予给另一个角色或用户。用户可以通过角色继承权限,除了管理权限外角色服务没有其它目的。权限可以被授予,也可以用同样的方式撤销。

 

  建立和使用角色

 

  如前所诉,角色存在的目的就是为了使权限的管理变得轻松。建立角色使用CREATE ROLE语句,他的语法如下:

 

CREATE ROLE role_name IDENTIFIED BY password

CREATE ROLE role_name IDENTIFIED EXTERNALLY

CREATE ROLE role_name IDENTIFIED GLOBALLY

 

  缺省情况下建立的角色没有password或者其他的识别。如果使用IDENTIFIED BY 子句建立,那么角色不会自动响应,必须用SET ROLE激活。

 

SET ROLE role_name IDENTIFIED BY password

 

  EXTERNALLYGLOBALLY类型的角色由操作系统和ORACLE Service server验证。通常用户需要权限修改应用程序中使用的表单中的数据,但是只有在应用程序运行时而不是在使用ad hoc工具时,这种上下文敏感安全可以通过有PASSWORD的角色来实现。当用户在应用程序内部连结数据库时,代码将执行SET ROLE命令,通过安全验证。所以用户不需要知道角色的password,也不需要自己输入SET ROLE命令。

 

  对象权限

 

  对象权限就是指在表、视图、序列、过程、函数或包等对象上执行特殊动作的权利。有九种不同类型的权限可以授予给用户或角色。如下表:

 

 

 

权限 ALTER DELETE EXECUTE INDEX INSERT READ REFERENCE SELECT UPDATE

Directory no no no no no yes no no no

function no no yes no no no no no no

procedure no no yes no no no no no no

package no no yes no no no no no no

DB Object no no yes no no no no no no

Libary no no yes no no no no no no

Operation no no yes no no no no no no

Sequence yes no no no no no no no no

Table yes yes no yes yes no yes yes yes

Type no no yes no no no no no no

View no yes no no yes no no yes yes

 

 

  对象由不止一个权限,特殊权限ALL可以被授予或撤销。如TABLEALL权限就包括:

 

   SELECT,INSERT,UPDATEDELETE,还有INDEX,ALTER,REFERENCE

 

  如何看这个表我们以ALTER权限为例进行说明

 

  ALTER权限

 

  允许执行ALTER TABLELOCK TABLE操作,ALTER TABLE可以进行如下操作:

 

    更改表名

 

    增加或删除列

 

    改变列的数据类型或大小

 

    将表转变为分区表

 

  在SEQUENCE上的ALTER权限允许执行ALTER Sequence语句,重新给sequence分配最小值、增量和缓冲区大小。

 

  系统权限

 

  系统权限需要授予者有进行系统级活动的能力,如连接数据库,更改用户会话、建立表或建立用户等等。你可以在数据字典视图SYSTEM_PRIVILEGE_MAP上获得完整的系统权限。对象权限和系统权限都通过GRANT语句授予用户或角色。需要注意的是在授予对象权限时语句应该是WITH GRANT OPTION子句,但在授予系统权象时语句是WITH ADMIN OPTION,所以在你试图授予系统权限时,使用语句WITH GRANT OPTION系统会报告一个错误:ONLY ADMIN OPTION can be specified。在考试中要特别注意这个语法和错误信息。

 

  角色和角色权限

 

  角色权限就是将属于用户的权限授予一个角色。任何权限都可以授予给一个角色。授予系统权限给被授予者必须使用WITH_ADMIN_OPTION子句,在会话期间通过SET ROLE语句授予或撤销角色权限。然而,角色权限不能依靠存储在SQL中的权限。如果函数、程序、包、触发器或者方法使用另一个计划拥有的对象,那么就必须直接给对象的拥有者授权,这是因为权限不会在会话之间改变。

 

  授予和撤销权限

 

   给用户或者角色授