SQL Server日志清除两种方法

核心提示： 不知道从哪里得到这个文档，有用就放上来了 －gwb 数据库端SQL语法的迁移以下为常用的SQL语法迁移，SQL Server日志清除两种方法(7)，包括数据类型、ID列向SEQUENCE迁移、表（主键、外键、CHECK、UNIQUE、DEFAULT、INDEX）、游标、存储过程、函数、触发器、常用SQL语法与函数几

　　
 不知道从哪里得到这个文档，有用就放上来了 －gwb

 
数据库端SQL语法的迁移
以下为常用的SQL语法迁移，包括数据类型、ID列向SEQUENCE迁移、表（主键、外键、CHECK、UNIQUE、DEFAULT、INDEX）、游标、存储过程、函数、触发器、常用SQL语法与函数几个方面，考虑sql server(WINDOWS平台上强大的数据库平台)的实际情况，没有涉及Oracle(大型网站数据库平台)特有的PACKAGE、EXCEPTION等。在以下的描述中，将sql server(WINDOWS平台上强大的数据库平台)的TRANSACT-SQL简称为T-SQL。在Oracle(大型网站数据库平台)中，其语法集称为PL/SQL。

 
<一> 数据类型的迁移
　  <1>、Oracle(大型网站数据库平台)端语法说明

在Oracle(大型网站数据库平台)中，分析其数据类型，大致可分为数字、字符、日期时间和特殊四大类。其中，数字类型有NUMBER；字符类型有CHAR与VARCHAR2；日期时间类型只有DATE一种；除此之外，LONG、RAW、LONG RAW、BLOB、CLOB和BFILE等数据类型都可视为特殊数据类型。

 

　  <2>、sql server(WINDOWS平台上强大的数据库平台)端语法说明

在sql server(WINDOWS平台上强大的数据库平台)中，参照上面对Oracle(大型网站数据库平台)的划分，数据类型也大致可分为数字、字符、日期时间和特殊四大类。数字类型又可分为精确数值、近似数值、整数、二进制数、货币等几类，其中，精确数值有DECIMAL[(P[, S])]与NUMERIC[(P[, S])]；近似数值有FLOAT[(N)]；整数有INT、SMALLINT、TINYINT；二进制数有BINARY[(N)]、VARBINARY[(N)]；货币有MONEY、SMALLMONEY。字符类型有CHAR[(N)]与VARCHAR[(N)]。日期时间类型有DATETIME、SMALLDATETIME。除此之外，BIT、TIMESTAMP、TEXT和IMAGE、BINARY VARING等数据类型都可视为特殊数据类型。

 

<3>、从sql server(WINDOWS平台上强大的数据库平台)向Oracle(大型网站数据库平台)的迁移方案

比较Oracle(大型网站数据库平台)与sql server(WINDOWS平台上强大的数据库平台)在数据类型上的不同，当从sql server(WINDOWS平台上强大的数据库平台)向Oracle(大型网站数据库平台)迁移时，可以做如下调整：

 

sql server(WINDOWS平台上强大的数据库平台)

Oracle(大型网站数据库平台)

数字类型

DECIMAL[(P[, S])]

NUMBER[(P[, S])]

NUMERIC[(P[, S])]

NUMBER[(P[, S])]

FLOAT[(N)]

NUMBER[(N)]

INT

NUMBER

SMALLINT

NUMBER

TINYINT

NUMBER

MONEY

NUMBER[19,4]

SMALLMONEY

NUMBER[19,4]

字符类型

CHAR[(N)]

CHAR[(N)]

VARCHAR[(N)]

VARCHAR2[(N)]

日期时间类型

DATETIME

DATE

SMALLDATETIME

DATE

其它

TEXT

CLOB

IMAGE

BLOB

BIT

NUMBER（1）

方法:

公司原系统中的Money 用于金额时转换用number(14,2);用于单价时用 number(10,4)代替;
<二> ID列向SEQUENCE迁移
　  <1>、sql server(WINDOWS平台上强大的数据库平台)端语法说明

在sql server(WINDOWS平台上强大的数据库平台)中，可以将数据库中的某一字段定义为IDENTITY列以做主键识别，如：

　  jlbh　  　  numeric(12,0) 　 　  identity(1,1)　  　  /*记录编号字段*/

　  CONSTRAINT  PK_tbl_example  Prima(最完善的虚拟主机管理系统)RY KEY  nonclustered (jlbh)  /*主键约束*/

在这里，jlbh是一个ID列，在向具有该列的表插入记录时，系统将从1开始以1的步长自动对jlbh的值进行维护。

 

　  <2>、Oracle(大型网站数据库平台)端语法说明

但在Oracle(大型网站数据库平台)中，没有这样的ID列定义，而是采用另一种方法，即创建SEQUENCE。

如：

/*--1、创建各使用地区编码表--*/

drop table LT_AREA;

create table LT_AREA

  (

  area_id　  number(5,0)  　  NOT NULL,   /*地区编码*/

  area_name　  varchar2(20) 　  NOT NULL,　 /*地区名称*/

  constraint PK_LT_AREA Prima(最完善的虚拟主机管理系统)RY KEY(area_id)

 );

 

/*--2、创建SEQUENCE，将列area_id 类ID化--*/

drop sequence SEQ_LT_AREA;

create sequence SEQ_LT_AREA increment by 1　  /*该SEQUENCE以1的步长递增*/

 start with 1 maxvalue 99999;　　　　　  　  /*从1开始，最大增长到99999*/

 

/*--3、实际操作时引用SEQUENCE的下一个值--*/

insert into LT_AREA(area_id, area_name) values(SEQ_LT_AREA.NEXTVAL, ’深圳’);

insert into LT_AREA(area_id, area_name) values(SEQ_LT_AREA.NEXTVAL, ’广州’);

insert into LT_AREA(area_id, area_name) values(SEQ_LT_AREA.NEXTVAL, ’北京’);

 

/*--4、新插入连续三条记录后，下一条语句运行后，‘上海’地区的area_id为4--*/

insert into LT_AREA(area_id, area_name) values(SEQ_LT_AREA.NEXTVAL, ’上海’);

 

　 <3>、从sql server(WINDOWS平台上强大的数据库平台)向Oracle(大型网站数据库平台)的迁移方案

 

根据以上分析，当从sql server(WINDOWS平台上强大的数据库平台)向Oracle(大型网站数据库平台)迁移时，可以做如下调整：

1、去掉建表语句中有关ID列的identity声明关键字；

2、创建SEQUENCE，将此SEQUENCE与需类ID化的列对应；

3、在INSERT语句中对相应列引用其SEQUENCE值：SEQUENCENAME.NEXTVAL

实际上，处理以上情况在Oracle(大型网站数据库平台)中采用的方法为对有自动增长字段的表增加一插入前触发器（具体资料见后“触发器”一节），如下：

CREATE OR REPLACE TRIGGER GenaerateAreaID

BEFORE INSERT ON LT_AREA

FOR EACH ROW

　  　  Select SEQ_LT_AREA.NEXTVAL INTO :NEW.ID

　  　  FROM DUAL;

BEGIN

END GenaerateAreaID;

GenaerateAreaID实际上修改了伪记录:new的area_id值。 :new最有用的一个特性----当该语句真正被执行时，:new中的存储内容就会被使用。所以系统每次都能自动生成新的号码。
<三> 表（主键、外键、CHECK、UNIQUE、DEFAULT、INDEX）
　  <1>、sql server(WINDOWS平台上强大的数据库平台)端语法说明

有如下sql server(WINDOWS平台上强大的数据库平台)语句：

/* ------------------------ 创建employee 表------------------------ */

IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME = ‘employee’

　 　　 　AND TYPE = ‘U’)

　  　  DROP TABLE employee

GO

 

CREATE TABLE employee

(

　  emp_id　 empid　  /*empid为用户自定义数据类型*/

　  /*创建自命名主键约束*/

　  　  CONSTRAINT PK_employee Prima(最完善的虚拟主机管理系统)RY KEY NONCLUSTERED

　  /*创建自命名CHECK约束*/

　  　  CONSTRAINT CK_emp_id CHECK (emp_id LIKE

　　　　　　　　　　 ’[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]’ or

　　　  　  　　　　 emp_id LIKE ’[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]’),

　  　  /* CHECK约束说明：Each employee ID consists of three characters that

　  　  represent the employee’s initials, followed by a five

　  　  digit number ranging from 10000 to 99999 and then the

　  　  employee’s gender (M or F). A (hyphen) - is acceptable

　  　  for the middle initial. */

　  fname 　  varchar(20)　　  NOT NULL,

　  minit 　  char(1) 　  　  NULL,

　  lname 　  varchar(30)　　  NOT NULL,

 

　  ss_id 　  varchar(9)　  　  UNIQUE,　  /*创建唯一性约束*/

 

job_id　  smallint　　　  　  NOT NULL

　  　  DEFAULT 1,　　　  　  /*设定DEFAULT值*/

　  job_lvl tinyint

　  　 DEFAULT 10,　　　  　  /*设定DEFAULT值*/

　  　  /* Entry job_lvl for new hires. */

　  pub_id　 char(4) 　　　  NOT NULL

　  　  DEFAULT (’9952’)　  　  /*设定DEFAULT值*/

　  　  REFERENCES publishers(pub_id),  /*创建系统命名外键约束*/

　  　  /* By default, the Parent Company Publisher is the company

　  　  to whom each employee reports. */

　  hire_date 　 　  datetime　 　  NOT NULL

　  　  DEFAULT (getdate()),　　　  /*设定DEFAULT值*/

　  　  /* By default, the current system date will be entered. */

　  CONSTRAINT FK_employee_job FOREIGN KEY (job_id)

　  　  REFERENCES jobs(job_id)　　　  /*创建自命名外键约束*/

)

GO

　 

　  /* --------------------- 创建employee表上的index --------------------- */

IF EXISTS (SELECT 1 FROM sysindexes

　　　  　  　 WHERE name = ’emp_pub_id_ind’)

　  DROP INDEX employee. emp_pub_id_ind

GO

 

　  CREATE INDEX emp_pub_id_ind

　  　  ON employee(pub_id)

GO

 

　  <2>、Oracle(大型网站数据库平台)端语法说明

在Oracle(大型网站数据库平台)端的语法如下：

　  /* ---------------------- 创建employee 表---------------------- */

　  DROP TABLE employee；

 

CREATE TABLE employee

(

　  emp_id　  varchar2(9)  /*根据用户自定义数据类型的定义调整为varchar2(9)*/

　  /*创建自命名主键约束*/

　  　  CONSTRAINT PK_employee Prima(最完善的虚拟主机管理系统)RY KEY NONCLUSTERED

　  /*创建自命名CHECK约束*/

　  　  CONSTRAINT CK_emp_id CHECK (emp_id LIKE

　　　　　　　　　　 ’[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]’ or

　　　  　  　　　　 emp_id LIKE ’[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]’),

　  　  /* CHECK约束说明：Each employee ID consists of three characters that

　  　  represent the employee’s initials, followed by a five

　  　  digit number ranging from 10000 to 99999 and then the

　  　  employee’s gender (M or F). A (hyphen) - is acceptable

　  　  for the middle initial. */

　  fname 　  varchar2(20)　　 NOT NULL,

　  minit 　  varchar2(1)  　  NULL,

　  lname 　  varchar2(30) 　  NOT NULL,

 

　  ss_id 　  varchar2(9)  　  UNIQUE,　  /*创建唯一性约束*/

 

job_id　  number(5,0)　　  NOT NULL

　  　  /*这里考虑了SMALLINT的长度，也可调整为number*/

　  　  DEFAULT 1,　　　  　  /*设定DEFAULT值*/

　  job_lvl 　  number(3,0)

　  　  /*这里考虑了TINYINT的长度，也可调整为number*/

　  　 DEFAULT 10,　　　  　  /*设定DEFAULT值*/

　  　  /* Entry job_lvl for new hires. */

　  pub_id  varchar2(4)　　　  NOT NULL

　  　  DEFAULT (’9952’)　  　  /*设定DEFAULT值*/

　  　  REFERENCES publishers(pub_id),  /*创建系统命名外键约束*/

　  　  /* By default, the Parent Company Publisher is the company

　  　  to whom each employee reports. */

　  hire_date 　 　  date 　 　　　  NOT NULL

　  　  DEFAULT SYSDATE,　  　  /*设定DEFAULT值*/

　  　  /*这里，sql server(WINDOWS平台上强大的数据库平台)的getdate()调整为Oracle(大型网站数据库平台)的SYSDATE*/

　  　  /* By default, the current system date will be entered. */

　  CONSTRAINT FK_employee_job FOREIGN KEY (job_id)

　  　  REFERENCES jobs(job_id)　　　  /*创建自命名外键约束*/

);

　 

　  /* -------------------- 创建employee表上的index -------------------- */

DROP INDEX employee. emp_pub_id_ind;

　  CREATE INDEX emp_pub_id_ind ON employee(pub_id);

 

<3>、从sql server(WINDOWS平台上强大的数据库平台)向Oracle(大型网站数据库平台)的迁移方案

比较这两段SQL代码，可以看出，在创建表及其主键、外键、CHECK、UNIQUE、DEFAULT、INDEX时，sql server(WINDOWS平台上强大的数据库平台) 与Oracle(大型网站数据库平台)的语法大致相同,但时迁移时要注意以下情况:

　  （1） Oracle(大型网站数据库平台)定义表字段的default属性要紧跟字段类型之后，如下：

　  Create table MZ_Ghxx

  ( ghlxh  number Prima(最完善的虚拟主机管理系统)y key ,

　  rq　　 date　 default sysdate not null,

  ….

  )

  而不能写成

　  Create table MZ_Ghxx

  ( ghlxh  number Prima(最完善的虚拟主机管理系统)y key ,

　  rq　　 date　 not null default sysdate,

  ….

  )

（2）T-SQL定义表结构时，如果涉及到用默认时间和默认修改人员，全部修改如下：

 ZHXGRQ 　  DATE　 DEFAULT SYSDATE NULL，

 ZHXGR  　  CHAR（8） DEFAULT ‘FUTIAN’ NULL，

（3）如表有identity定段，要先将其记录下来，建完表之后，马上建相应的序列和表触发器，并作为记录。
<四> 游标
　  <1>、sql server(WINDOWS平台上强大的数据库平台)端语法说明

1、DECLARE CURSOR语句

　　　 语法：

　　　　　 DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR

　  FOR select_statement

  [FOR {READ ONLY | UPDATE [OF column_list ]}]

　　　 例：

　　　 DECLARE  authors_cursor  CURSOR  FOR

　　　  SELECT  au_lname, au_fname

　 　　　 FROM  authors

  　　　 WHERE  au_lname LIKE ‘B%’

　 　 ORDER BY  au_lname, au_fname

 

2、OPEN语句

　 语法：

　　　 OPEN  cursor_name

　　　 例：

　　　 OPEN authors_cursor

 

3、FETCH语句

　 语法：

　　　 FETCH

　　　  　　 [ [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n ]

　　　　　 FROM  cursor_name

　　　　 [INTO @variable_name1, @variable_name2,… ]

　　　 例：

　　　 FETCH NEXT FROM authors_cursor

　　　　 　　　　 INTO @au_lname, @au_fname

 

4、CLOSE语句

　 语法：

　　　 CLOSE  cursor_name

　　　 例：

　　　 CLOSE authors_cursor

 

5、DEALLOCATE语句

　 语法：

　　　 DEALLOCATE  cursor_name

　　　 例：

　　　 DEALLOCATE authors_cursor

 

6、游标中的标准循环与循环终止条件判断

　 （1）FETCH NEXT FROM authors_cursor INTO @au_lname, @au_fname

 

　 （2）-- Check @@FETCH_STATUS to see if there are any more rows to fetch.

　　　  WHILE @@FETCH_STATUS = 0

　　　  BEGIN

  　　　　 -- Concatenate and display the current values in the variables.

　　 　 　 PRINT "Author: " + @au_fname + " " +  @au_lname

 

    　　　 -- This is executed as long as the previous fetch succeeds.

　    　　　　 FETCH NEXT FROM authors_cursor INTO @au_lname, @au_fname

　　　  END

 

　 （3）CLOSE authors_cursor

7、隐式游标

mssql(WINDOWS平台上强大的数据库平台)Server中对于数据操纵语句受影响的行数，有一个全局的变量：@@rowcount，其实它是一个隐式的游标，它记载了上条数据操纵语句所影响的行数，当@@rowcount小于1时，表时，上次没有找到相关的记录,如下：

Update students set lastname = ‘John’ where student_id = ‘301’

If @@rowcount < 1 then

Insert into students values (‘301’,’stdiv’,’john’,’996-03-02’)

表示如果数据表中有学号为“301”的记录，则修改其名字为“John”，如果找不到相应的记录，则向数据库中插入一条“John”的记录。

8、示例：

-- Declare the variables to store the values returned by FETCH.

DECLARE @au_lname varchar(40), @au_fname varchar(20)

 

DECLARE  authors_cursor  CURSOR  FOR

 SELECT  au_lname, au_fname

　 FROM  authors

  WHERE  au_lname LIKE ‘B%’

　 ORDER BY  au_lname, au_fname

 

OPEN authors_cursor

 

-- Perform the first fetch and store the values in variables.

-- Note: The variables are in the same order as the columns

-- in the SELECT statement.

 

FETCH NEXT FROM authors_cursor INTO @au_lname, @au_fname

 

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.

WHILE @@FETCH_STATUS = 0

 

BEGIN

  -- Concatenate and display the current values in the variables.

　    PRINT "Author: " + @au_fname + " " +  @au_lname

 

　    -- This is executed as long as the previous fetch succeeds.

　    FETCH NEXT FROM authors_cursor INTO @au_lname, @au_fname

END

 

CLOSE authors_cursor

 

DEALLOCATE authors_cursor

 

　  <2>、Oracle(大型网站数据库平台)端语法说明

1、  DECLARE CURSOR语句

　　　 语法：

　  CURSOR  cursor_name  IS  select_statement;

　　　 例：

　　　　　 CURSOR  authors_cursor  IS

　　   SELECT  au_lname, au_fname

　 　　　FROM  authors

  　　　WHERE  au_lname LIKE ‘B%’

　 　ORDER BY  au_lname, au_fname;

 

2、  OPEN语句

　 语法：

　　　 OPEN  cursor_name

　　　 例：

　　　 OPEN authors_cursor;

 

3、  FETCH语句

　 语法：

　　　 FETCH  cursor_name  INTO  variable_name1 [, variable_name2,… ] ;

　　　 例：

　　　 FETCH  authors_cursor  INTO  au_lname, au_fname;

 

4、  CLOSE语句

　 语法：

　　　 CLOSE  cursor_name

　　　 例：

　　　 CLOSE authors_cursor;

 

5、简单游标提取循环结构与循环终止条件判断

　 1> 用%FOUND做循环判断条件的WHILE循环

　　 （1）FETCH  authors_cursor  INTO  au_lname, au_fname ；

　　 （2）WHILE authors_cursor%FOUND LOOP

　　　　　　 -- Concatenate and display the current values in the variables.

　　　  　  　　 DBMS_OUTPUT.ENABLE;

　　  　　　 DBMS_OUTPUT.PUT_LINE( ‘Author: ‘ || au_fname || ‘ ‘ || au_lname) ;

　　　　　　 FETCH  authors_cursor  INTO  au_lname, au_fname ；

　　　  　END LOOP ;

　　 （3）CLOSE authors_cursor ;

 

　 2> 用%NOTFOUND做循环判断条件的简单LOOP...END LOOP循环

　　 （1）OPEN authors_cursor;

　　 （2）LOOP

　　　　　　 FETCH  authors_cursor  INTO  au_lname, au_fname ；

　　　　　　　　 -- Exit loop when there are no more rows to fetch.

　　　  　　 EXIT  WHEN  authors_cursor%NOTFOUND ;

  　　　　　 -- Concatenate and display the current values in the variables.

　　 　 　　 DBMS_OUTPUT.ENABLE;

　　　　　 　DBMS_OUTPUT.PUT_LINE( ‘Author: ‘ || au_fname || ‘ ‘ || au_lname) ;

　　　  　END LOOP ;

　　 （3）CLOSE authors_cursor ;

3>用游标式FOR循环,如下：

DECLARE

　　　  　  　  CURSOR c_HistoryStudents IS

　　　  　  　  SELECT id,first_name,last_name

　　　  　　　  FROM Students

　　　  　　　  WHERE major = ‘History’

BEGIN

　　　  　    FOR v_StudentData IN c_HistoryStudents LOOP

　　　  　    INSERT INTO registered_students

(student_id,first_name,last_name,department,course)

VALUES(v_StudentData.ID,v_StudentData.first_name, v_StudentData.last_name,’HIS’,301);

　　　  　    END LOOP;

COMMIT;

END;

首先，记录v_StudentData没有在块的声明部分进行声明，些变量的类型是c_HistoryStudents%ROWTYPE，v_StudentData的作用域仅限于此FOR循环本身；其实，c_HistoryStudents以隐含的方式被打开和提取数据，并被循环关闭。

6、隐式游标SQL%FOUND 与SQL%NOTFOUND

　  　  与mssql(WINDOWS平台上强大的数据库平台) server(WINDOWS平台上强大的数据库平台) 一样，Oracle(大型网站数据库平台)也有隐式游标，它用于处理INSERT、DELETE和单行的SELECT..INTO语句。因为SQL游标是通过PL/SQL引擎打开和关闭的，所以OPEN、FETCH和CLOSE命令是无关的。但是游标属性可以被应用于SQL游标，如下：

BEGIN

　  　  UPDATE rooms

　  　　  SET number_seats = 100

　  　  WHERE room_id = 9990;

--如果找不相应的记录，则插入新的记录

　  　  IF SQL%NOTFOUND THEN

　　　  　  INSERT INTO rooms(room_id,number_seats)

　　　  　  VALUES (9990,100)

END IF

　  END;

7、示例：

-- Declare the variables to store the values returned by FETCH.

-- Declare the CURSOR authors_cursor.

DECLARE

　 au_lname  varchar2(40) ;

　 au_fname  varchar2(20) ;

　　　 CURSOR  authors_cursor  IS

　 SELECT  au_lname, au_fname

　 　FROM  authors

  　WHERE  au_lname LIKE ‘B%’

 ORDER BY  au_lname, au_fname;

 

BEGIN

　 OPEN authors_cursor;

　 FETCH  authors_cursor  INTO  au_lname, au_fname ；

　 WHILE authors_cursor%FOUND LOOP

　　  -- Concatenate and display the current values in the variables.

　    　　DBMS_OUTPUT.ENABLE;

　 　 DBMS_OUTPUT.PUT_LINE( ‘Author: ‘ || au_fname || ‘ ‘ || au_lname) ;

　　  FETCH  authors_cursor  INTO  au_lname, au_fname ；

　 END LOOP ;

 

　 CLOSE authors_cursor ;

END ;

 

　  <3>、从sql server(WINDOWS平台上强大的数据库平台)向Oracle(大型网站数据库平台)的迁移方案

　　　  比较上述SQL代码，在迁移过程中要做如下调整：

　　　  （1）T-SQL对CURSOR的声明在主体代码中，而PL/SQL中对CURSOR的声明与变

　　　　　　 量声明同步，都要在主体代码（BEGIN关键字）之前声明，所以在迁移时要

　　　　　　 将游标声明提前,mssql(WINDOWS平台上强大的数据库平台) server(WINDOWS平台上强大的数据库平台)的Cursor定义后的参数省去；

　　　  （2）对CUOSOR操作的语法中PL/SQL没有T-SQL里DEALLOCATE CURSOR这一部分，

　　　　　　 迁移时要将该部分语句删除。

　　　  （3）PL/SQL 与T-SQL对游标中的循环与循环终止条件判断的处理不太一样，根

　　　　　　 据前面的讨论并参考后面对两种语法集进行控制语句对比分析部分的叙述，

　　　　　　 建议将T-SQL中的游标提取循环调整为PL/SQL中的WHILE游标提取循环结

　　　　　　 构，这样可保持循环的基本结构大致不变，同时在进行循环终止条件判断时

　　　　　　 要注意将T-SQL中的对@@FETCH_STATUS全局变量的判断调整为对

　　　　　　 CURSOR_NAME%FOUND语句进行判断。

　　　  （4）对于T-SQL，没有定义语句结束标志，而PL/SQL用“；”结束语句。

（5）对于原mssql(WINDOWS平台上强大的数据库平台) server(WINDOWS平台上强大的数据库平台)类型的游标，如果游标取出的值没有参与运算的，全部采用FOR循环方式来替换；而对于取出的值还要进行其它运算的，可以采用直接在定义变量位置定义变量。

 (6)mssql(WINDOWS平台上强大的数据库平台)中对于同一游标重复定义几次的情况在Oracle(大型网站数据库平台)中可通过游标变量来解决.如下：

mssql(WINDOWS平台上强大的数据库平台) server(WINDOWS平台上强大的数据库平台) 中：

Declare cur_ypdm cursor for

Select * from yp

Open cur_yp

Fetch cur_yp into @yp,@mc …

While @@fetch_status <> -1

Begin

  If @@fetch_status <> -2

  Begin

　  ….

  End

　Fetch cur_yp into @yp,@mc …

End

Close cur_ypdm

Deallocate cur_ypdm

..

Declare cur_ypdm cursor for

Select * from yp where condition 1

Open cur_yp

Fetch cur_yp into @yp,@mc …

While @@fetch_status <> -1

Begin

  If @@fetch_status <> -2

  Begin

　  ….

  End

  Fetch cur_yp into @yp,@mc …

End

Close cur_ypdm

Deallocate cur_ypdm

..

Declare cur_ypdm cursor for

Select * from yp  where condition 2

Open cur_yp

Fetch cur_yp into @yp,@mc …

While @@fetch_status <> -1

Begin

  If @@fetch_status <> -2

  Begin

　  ….

  End

  Fetch cur_yp into @yp,@mc …

End

Close cur_ypdm

Deallocate cur_ypdm

..

在程序中，三次定义同一游标cur_yp

在迁移过程中，最好先定义一游标变量，在程序中用open打开，如下：

declare

  type cur_type is ref  cur_type;

  cur_yp cur_type;

  …

begin

  open cur_yp for select * from yp;

  loop

　  fetch cur_yp into yp,mc …

　  Exit When cur_yp%NotFound;

　  ….

  end loop;

  close cur_yp;

  open cur_yp for select * from yp where condition1;

  loop

　  fetch cur_yp into yp,mc …

　  Exit When cur_yp%NotFound;

　  ….

  end loop;

  close cur_yp;

  open cur_yp for select * from yp where condition2;

  loop

　  fetch cur_yp into yp,mc …

　  Exit When cur_yp%NotFound;

　  ….

  end loop;

  close cur_yp;

end;

(7)请注意，游标循环中中一定要退出语名，要不然执行时会出现死循环。

<五> 存储过程/函数

　  <1>、sql server(WINDOWS平台上强大的数据库平台)端语法说明

　  1、语法：

　　　　　 CREATE PROC[EDURE] [owner.]procedure_name [;number]

　  　　　　 [ (parameter1[, parameter2]…[, parameter255])]

　　　  　　 [ {FOR REPLICATION} | {WITH RECOMPILE}

　　  　　　 [ {[WITH] | [ , ] } ENCRYPTION ] ]

　　　　　 AS

　  　　　　 sql_statement [...n]

　　　　　 其中，Parameter = @parameter_name datatype [=default] [output]

 

　　　 说明：T-SQL中存储过程的结构大致如下

　　　　　 CREATE PROCEDURE procedure_name

　　　　　　   /*输入、输出参数的声明部分*/

　　　　　 AS

　　　　　　　 DECLARE

　　　　　　　 /*局部变量的声明部分*/

　　　　　 BEGIN

　　　　　　　 /*主体SQL语句部分*/

　　　　　　  /*游标声明、使用语句在此部分*/

　　　　　 END

 

　  2、示例：

　　　 IF EXISTS(SELECT 1 FROM sysobjects

　　　　　　　　 WHERE name = ’titles_sum’ AND type = ’P’)

　  　　  DROP PROCEDURE titles_sum

　　　 GO

 

　　　 CREATE PROCEDURE titles_sum

　　　　　　  @TITLE varchar(40) = ’%’, @SUM money OUTPUT

　　　 AS

　　　 BEGIN

　　　　  SELECT  ’Title Name’ = title

　  　　　　FROM  titles

　　 　　　WHERE  title  LIKE  @TITLE

　　　　  SELECT  @SUM = SUM(price)

　　  　　　FROM  titles

　　　 　　WHERE  title  LIKE  @TITLE

　　　 END

 

　  <2>、Oracle(大型网站数据库平台)端PROCEDURE语法说明

　  1、语法：

　　　　　 CREATE [OR REPLACE] PROCEDURE procedure_name

　  　　　　 [ (parameter1 [ {IN | OUT | IN OUT } ] type ,

　　　　　　　 …

　　　　　　　 parametern [ {IN | OUT | IN OUT } ] type ) ]

　　　　　 { IS | AS }

　　　　   [BEGIN]

　  　　　　 sql_statement [...n] ;

　　　　　 [END] ;

　　　

　　　 说明：PL/SQL中存储过程的结构大致如下

　　　　　 CREATE OR REPLACE PROCEDURE procedure_name

　　　　　　  （  /*输入、输出参数的声明部分*/  )

　　　　　 AS

　　　　　　   /*局部变量、游标等的声明部分*/

　　　　　 BEGIN

  　　　　　　 /*主体SQL语句部分*/

　　　　　　　 /*游标使用语句在此部分*/

　　　　　 EXCEPTION

　　　　　　　 /*异常处理部分*/

　　　　　 END ；

 

　  2、示例：

　　　 CREATE OR REPLACE PROCEDURE  drop_class

　　　　  ( arg_student_id  IN 　  　  varchar2,

　　　　　  arg_class_id　  IN  　  varchar2,

　　　　　  status　　　　  OUT 　  number　 )

　　　 AS

　　　　  counter 　  number ;

　　　 BEGIN

　　　　  status := 0 ;

　　　　  -- Verify that this class really is part of the student’s schedule.

　　　　  select  count (*)  into  counter

　　　 　　 from  student_schedule

　　　　　 where  student_id  =  arg_student_id

　　　　　　 and  class_id　  =  arg_class_id ;

　　　　 

　　　　  IF  counter  =  1  THEN

　　　　　　 delete  from  student_schedule

　　　　　　  where  student_id  =  arg_student_id

　　　　　　　  and  class_id　  =  arg_class_id ;

　　　　　　 status := -1 ;

　　　　  END IF ;

　　　 END ;

<3>Oracle(大型网站数据库平台)端FUNCTION语法说明

(1)  语法

CREATE [OR REPLACE] FUNCTION function_name

[(argument [{IN | OUT | IN OUT }] ) type,

…

[(argument [{IN | OUT | IN OUT }] ) type

RETURN return_type {IS | AS}

BEGIN

…

END;

关键字return 指定了函数返回值的数据类型。它可以是任何合法的PL/SQL数据类型。每个函数都必须有一个return 子句，因为在定义上函数必须返回一个值给调用环境。

（2）示例

CREATE OR REPLACE FUNCTION blanace_check(Person_Name IN varchar2)

RETURN NUMBER

IS

Balance NUMBER(10,2);

BEGIN

　　　 　　　 Select sum(decode(acton,’BOUGHT’,Amount,0))

　　　 　　　 INTO balance

　　　 　　　 FROM ledger

　　　 　　　 WHERE Person = Person_name;

　　　 　　　 RETURN (balance);

END;

(3)过程与函数的区别

函数可以返回一个值给调用环境；而过程不能，过程只能通过返回参数（带“OUT”或“IN OUT”）传回去数据。

<4>从sql server(WINDOWS平台上强大的数据库平台)向Oracle(大型网站数据库平台)的迁移方案

通过比较上述SQL语法的差异，在迁移时必须注意以下几点：

1、对于有返回单值的mssql(WINDOWS平台上强大的数据库平台)存储过程，在数据库移值最好转换成ORALCE的函数；对于mssql(WINDOWS平台上强大的数据库平台)有大量数据的处理而又不需返回值的存储过程转换成Oracle(大型网站数据库平台)的过程

　　　 2、在T-SQL中，输入、输出参数定义部分在“CREATE…”和“AS”之间，前后

　　　　  没有括号；而在PL/SQL中必须有“（”和“）”与其他语句隔开。

　　　 3、在T-SQL中，声明局部变量时，前面要有DECLARE关键字；

　　　　  而在PL/SQL中不用DECLARE关键字。

　　   4、在T-SQL中，参数名的第一个字符必须是“@”，并符合标识符的规定；

　　　　  而在PL/SQL中，参数名除符合标识符的规定外没有特殊说明,T-SQL中,对于参数可其数据类型及其长度和精度;但是PL/SQL中除了引用%TYPE和%ROWTYPE之外,不能在定义参数数据类型时给出长度和精度,如下:

　　  CREATE OR REPLACE  PROCEDURE PROC_SELE_YS

　　　 (YSDM  CHAR(6),GZ NUMBER(14,4))

　　  AS

　 　 BEGIN

　　　  …

　　  END;

　　 是错误的,应如下定义

　　  CREATE OR REPLACE  PROCEDURE PROC_SELE_YS

　　　 (YSDM  CHAR,GZ NUMBER)

　　  AS

　　  BEGIN

　　　  …

　　  END;

　　 或者

　　  CREATE OR REPLACE  PROCEDURE PROC_SELE_YS

　　　 (YSDM  YSDMB.YSDM%TYPE,GZ YSDMB.GZ%TYPE)

　　  AS

　　  BEGIN

　　　  …

　　  END;

 

　　　 5、对于T-SQL，游标声明在主体SQL语句中，即声明与使用语句同步；

　　　　  而在PL/SQL中，游标声明在主体SQL语句之前，与局部变量声明同步。

　　　 6、对于T-SQL，在主体SQL语句中用如下语句对局部变量赋值（初始值或

　　　　  数据库表的字段值或表达式）：

　　　　　 “SELECT 局部变量名 =  所赋值（初始值或数据库表的字段值或表达式）”；

　  　　　而在PL/SQL中，将初始值赋给局部变量时，用如下语句：

　　　　　 “局部变量名 : =  所赋值（初始值或表达式）;” ，

　　　　  将检索出的字段值赋给局部变量时，用如下语句：

　　　　　 “SELECT 数据库表的字段值 INTO 局部变量名 …” 。

　　　 7、在PL/SQL中，可以使用%TYPE来定义局部变量的数据类型。说明如下：

　　　　  例如，students表的first_name列拥有类型VARCHAR2(20)，基于这点，

　　　　  我们可以按照下述方式声明一个变量：

　　　　　　  V_FirstName　  VARCHAR2(20) ;

　　　　  但是如果改变了first_name列的数据类型则必须修改该声明语句，因此可以采

　　　　  用%TYPE进行变量数据类型声明：

　　　　　　 V_FirstName 　  students.first_name%TYPE ;

　　　　  这样，该变量在存储过程编译时将由系统自动确定其相应数据类型。

8、对于T-SQL，没有定义语句结束标志，而PL/SQL用“END <过程名>；”结束语句。

9、存储过程的调用要注意：在mssql(WINDOWS平台上强大的数据库平台)SERVER中的格式为“EXEC Procedure_Name {arg1,arg2,…}，但在Oracle(大型网站数据库平台)中直接引用过程名即可，如要执行存储过程DefaltNo，其参数为“9”，则执行时为 Default(“9”)。

10、Oracle(大型网站数据库平台) 数据库的存储过程不支持用select 子句直接返回一个数据集，要做到通过程产生一记录集有两种方案：

方案一：采用包和游标变量

第一步,创建一个包，定义一个游标变量

create package p_name
is
type cursor_name is ref cursor;
end;

第二步，创建过程，但是基返回参数用包中的游标类型
create procedure procedure_name(s in out p_name.cursor_name) is
begin
open s for select * from table_name...;
end;

这样，通过存储过程就可以返回一个数据集了，但用到这种情况，过程的参数中只这返回结果的游标参数可以带关键字”OUT”,其它不能带”out”，否则，系统会出现导常。

方案二：通过中间表，建一中间表，其表格的列为所需数据列再加上一个序列字段。过程的处理为将数据插入到中间表中，同时通过

select userenv(‘sessionid’) from dual;取得当前连接会话的序号，将取得的序号值放置到序列字段中，同时存储过程返回连接会话的序号，前台PB程序直接访问中间表，数据窗口在检索时通过序号参数可将所需的数据检索出来。
<六> 触发器
　  <1>、sql server(WINDOWS平台上强大的数据库平台)端语法说明

　  1、语法：

　　　  　 CREATE TRIGGER [owner.]trigger_name

　　　　　 ON [owner.]table_name

　  　　　 FOR { INSERT, UPDATE, DELETE }

　　　  　　 [WITH ENCRYPTION]

　　　  　 AS

　　　　　   sql_statement [...n]

　　　 或者使用IF UPDATE子句：

　　　　　 CREATE TRIGGER [owner.]trigger_name

　　　　　 ON [owner.]table_name

　  　　　 FOR { INSERT, UPDATE }

　　　  　　 [WITH ENCRYPTION]

　　　  　 AS

　　　  　　 IF UPDATE (column_name)

　　　　　   [{AND | OR} UPDATE (column_name)…]

　　　　　   sql_statement [ ...n]

 

　　 2、示例：

　　　  IF EXISTS (SELECT 1 FROM sysobjects

　　　　　　 　　　WHERE name = ’reminder’ AND type = ’TR’)

　　　　  DROP TRIGGER reminder

　　　  GO

 

　　　  CREATE TRIGGER employee_insupd

　　　　　　　 ON employee

　　　　　　  FOR INSERT, UPDATE

　　　  AS

　　　  /* Get the range of level for this job type from the jobs table. */

　　　  DECLARE @min_lvl tinyint,

　　　  　  　  　  @max_lvl tinyint,

　　　  　  　  　  @emp_lvl tinyint,

　　　  　  　  　  @job_id  smallint

　　　   SELECT @min_lvl = min_lvl,

　　　  　  　  　  @max_lvl = max_lvl,

　　　  　  　  　  @emp_lvl = i.job_lvl,

　　　  　  　  　  @job_id  = i.job_id

　　　　　 FROM employee e, jobs j, inserted i

　　　　  WHERE e.emp_id = i.emp_id AND i.job = j.job_id

　　　  IF (@job_id = 1) and (@emp_lvl <> 10)

　　　  BEGIN

　　　  　　　 RAISERROR (’Job id 1 expects the default level of 10.’, 16, 1)

　  　　　　  ROLLBACK TRANSACTION

　　　  END

　　　  ELSE

　　　  IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)

　　　  BEGIN

　　　  　　 RAISERROR (’The level for job_id:%d should be between %d and %d.’,

　      　　　　　　　　 16, 1, @job_id, @min_lvl, @max_lvl)

　　　  　　 ROLLBACK TRANSACTION

　　　  END

　　　  GO

 

　  <2>、Oracle(大型网站数据库平台)端语法说明

　  1、语法：

　　　　　 CREATE [OR REPLACE] TRIGGER trigger_name

　　　　　 { BEFORE | AFTER } triggering_event ON  table_name

　  　　　 [ FOR EACH ROW ]

　　　　　 [ WHEN trigger_condition ]

　　　　　 trigger_body ;

　  2、使用说明与示例：

　　　 （1）、上语法中，trigger_event 是对应于DML的三条语句INSERT、UPDATE、

　　　　　　 DELETE；table_name是与触发器相关的表名称；FOR EACH ROW是可选

　　　　　　 子句，当使用时，对每条相应行将引起触发器触发；condition是可选的

　　　　　　 Oracle(大型网站数据库平台) BOOLEAN条件，当条件为真时触发器触发；trigger_body是触发

　　　　　　 器触发时执行的PL/SQL块。

 

　　　 （2）、Oracle(大型网站数据库平台)触发器有以下两类：

　　　　　　 1> 语句级（Statement-level）触发器，在CREATE TRIGGER语句中不

　　　　　　　  包含FOR EACH ROW子句。语句级触发器对于触发事件只能触发一次，

　　　　　　　  而且不能访问受触发器影响的每一行的列值。一般用语句级触发器处理

　　　　　　　  有关引起触发器触发的SQL语句的信息——例如，由谁来执行和什么时

　　　　　　　  间执行。

　　　　　　 2> 行级（Row-level）触发器，在CREATE TRIGGER语句中

　　　　　　　  包含FOR EACH ROW子句。行级触发器可对受触发器影响的每一行触

　　　　　　　  发，并且能够访问原列值和通过SQL语句处理的新列值。行级触发器的

　　　　　　　  典型应用是当需要知道行的列值时，执行一条事务规则。

 

　　　  （3）在触发器体内，行级触发器可以引用触发器触发时已存在的行的列值，这些

　　　　　　 值倚赖于引起触发器触发的SQL语句。

　　　　　　 1> 对于INSERT语句，要被插入的数值包含在new.column_name，这里的

　　　　　　　  column_name是表中的一列。

　　　　　　 2> 对于UPDATE语句，列的原值包含在old.column_name中，数据列的新

　　　　　　　  值在new.column_name中。

　　　 　　　3> 对于DELETE语句，将要删除的行的列值放在old.column_name中。

触发语句

：old

：new

INSERT

无定义——所有字段都是NULL

当该语句完成时将要插入的数值

UPDATE

在更新以前的该行的原始取值

当该语句完成时将要更新的新值

DELETE

在删除行以前的该行的原始取值

未定义——所有字段都是NULL

　　　　　　 4> 在触发器主体中，在new和old前面的“：”是必需的。而在触发器的

　　　　　　　  WHEN子句中，:new和:old记录也可以在WHEN子句的condition内部

　　　　　　　  引用，但是不需要使用冒号。例如，下面CheckCredits触发器的主体仅

　　　　　　　  当学生的当前成绩超过20时才会被执行：

　　　　　　　  CREATE OR REPLACE TRIGGER CheckCredits

　　　　　　　　　 BEFORE INSERT OR UPDATE OF current_credits ON students

　　 　　　　　　　FOR EACH ROW

　　　　　　　　　 WHEN (new.current_credits > 20)

　　　　　　　  BEGIN

　　　　　　　  　 /*Trigger body goes here. */

　　　　　　　  END ;

　　　　　　　  但CheckCredits也可以按下面方式改写：

　　　　　　　  CREATE OR REPLACE TRIGGER CheckCredits

　　　　　　　　　 BEFORE INSERT OR UPDATE OF current_credits ON students

　　　　　　　　　 FOR EACH ROW

　　　　　　　  BEGIN

　　　　　　　　　 IF  :new.current_credits > 20  THEN

　　　　　　　  　　　/*Trigger body goes here. */

　　　　　  　　　 END IF ;

　　　　　　　  END ;

　　　　 　　　 注意，WHEN子句仅能用于行级触发器，如果使用了它，那么触发器主体

　　　　 　　　 仅仅对那些满足WHEN子句指定的条件的行进行处理。

 

　　　  （4）触发器的主体是一个PL/SQL块，在PL/SQL块中可以使用的所有语句在触

　　　　　　 发器主体中都是合法的，但是要受到下面的限制：

　　　　　　 1> 触发器不能使用事务控制语句，包括COMMIT、ROLLBACK或

　　　　　　　  SAVEPOINT。Oracle(大型网站数据库平台)保持这种限制的原因是：如果触发器遇到错误时，

　　　　 　　　 由触发器导致的所有数据库变换均能被回滚（roll back）取消；但如果

　　　　 　　　 触发器确认（commit）了对数据库进行的部分变换，Oracle(大型网站数据库平台)就不能完全

　　　　 　　　 回滚（roll back）整个事务。

　　　　　　 2> 在触发器主体中调用到的存储过程的实现语句里也不能使用事务控制语

　　　　　　　  句。

　　　　　　 3> 触发器主体不能声明任何LONG或LONG RAW变量。而且，:new和:old

　　　　　　　  不能指向定义触发器的表中的LONG或LONG RAW列。

　　　　　　 4> 当声明触发器的表中有外键约束时，如果将定义触发器的表和需要作为

　　　　　　　  DELETE CASCADE参考完整性限制的结果进行更新的表称为变化表，

　　　　　　　  将外键相关联的表称为限制表，则在此触发器主体中的SQL语句不允许

　　　　　　　  读取或修改触发语句的任何变化表，也不允许读取或修改限制表中的主

　　　　　　　  键、唯一值列或外键列。

 

　　　  （5）以下是建立一个事前插入触发器的示例：

　　　　　　 CREATE OR REPLACE TRIGGER Credit_Charge_Log_Ins_Before

　　　　　　 　 BEFORE insert ON Credit_Charge_Log

　 　　　　　　 FOR EACH ROW

　　　　　　 DECLARE

　　　　　　 　 Total_for_past_3days 　  number ;

　　　　　　 BEGIN

　　　　　　 　 -- Check the credit charges for the past 3 days.

　　　　　　 　 -- If they total more than $1000.00, log this entry

　　　　　　 　 -- int the Credit_Charge_Attempt_Log for further handling.

　　　　　　 　 select sum ( amount ) into total_for_past_3days

　　　　　　  　　from Credit_Charge_Log

　　　　　　 　　where Card_Number = :new.Card_Number

　　　　　　 　　　and Transaction_Date >= sysdate – 3;

　　　　　　 　 IF total_for_past_3days > 1000.00 THEN

　　　　　　 　　　insert into credit_Charge_Attemp_Log

　　　　　　 　　　　(Card_Number, Amount, Vendor_ID, Transaction_Date)

　　　　　　 　　　values

　　　　　　 　　　　(:new.Card_Number, :new.Amount,

　　　　　　 　　　　 :new.Vendor_ID, :new.Transaction_Date);

　　　　　　 　 END IF ;

　　　　　　 END ;

 

<3>、从sql server(WINDOWS平台上强大的数据库平台)向Oracle(大型网站数据库平台)的迁移方案

 

　　　　 1、通过比较上面SQL语法的不同并考虑现有sql server(WINDOWS平台上强大的数据库平台)的实际编程风格，在从

　　　　 　 T-SQL向PL/SQL迁移时，要遵守下面规则：

　　　　 　 1> 在CREATE TRIGGER定义中采用AFTER关键字，即调整为事后触发器。

　　　　 　 2> 在CREATE TRIGGER定义中采用FOR EACH ROW关键字，即调整为行级触发

　　　　 　　　器。

　　　　 　 3> 将触发器主体中的“inserted”调整为“:new”，将“deleted”调整为“:old”。

　　　　 　 4> 在触发器主体中禁用CURSOR操作:new与:old。

　　　　 　 5> 在触发器主体中禁用COMMIT、ROLLBACK、SAVEPOINT等事务控制语句。

 

　　　　 2、用触发器解决ID列向SEQUENCE迁移的问题：

　　　　 　 下面的GenerateStudentID触发器使用了:new。这是一个before INSERT触

　　　　　  发器，其目的是使用student_sequence序列所产生的数值填写

　　　　 　 students表的ID字段。

　　　　 　 例：

　　　　 　 CREATE OR REPLACE TRIGGER GenerateStudentID

　　　　　  　 BEFORE INSERT ON students

　　　　　  　 FOR EACH ROW

　　　　 　 BEGIN

　　　　 　　　SELECT student_sequence.nextval

　　　　 　　　　INTO :new.ID

　　　　 　　　　FROM dual;

　　　　 　 END;

　　　　 　 在上面的触发器主体中，GenerateStudentID实际上修改了:new.ID的值。这

　　　　 　 是:new最有用的一个特性——当该语句真正被执行时，:new中的存储内容就

　　　　 　 将被使用。有了这个触发器，我们就可以使用下面这样的INSERT语句，而不

　　　　 　 会产生错误：

　　　　 　　　INSERT INTO students (first_name, last_name)

　　　　 　　　VALUES (‘LUO’, ‘TAO’) ;

　　　　 　 尽管我们没有为主键列ID（这是必需的）指定取值，触发器将会提供所需要

　　　　 　 的取值。事实上，如果我们为ID指定了一个取值，它也将会被忽略，因为触

　　　　 　 发器修改了它。如果我们使用下面的语句：

　　　　 　　　INSERT INTO students (ID, first_name, last_name)

　　　　 　　　VALUES (-789, ‘LUO’, ‘TAO’) ;

　　　　 　 其处理结果还是相同的。无论在哪种情况下，student_sequence.nextval都

　　　　 　 将用作ID列值。

 

　　　　 　 由此讨论，可以采用这种方法处理sql server(WINDOWS平台上强大的数据库平台)中ID列向Oracle(大型网站数据库平台)的SEQUENCE

　　　　 　 转换的问题。

 

　　　　 　 另外，由于上面的原因，我们不能在after行级触发器中修改 :new，因为该

　　　　 　 语句已经被处理了。通常，:new仅仅在before行级触发器中被修改，而:old

　　　　 　 永远不会被修改，仅仅可以从它读出数据。

 

　　　　 　 此外，:new和:old记录仅仅在行级触发器内部是有效的。如果试图要从语句

　　　　 　 级触发器进行引用，将会得到一个编译错误。因为语句级触发器只执行一次

　　　　 　 ——尽管语句要处理许多行——所以:new和:old是没有意义的，因为怎么确

　　　　 　 定它们引用的会是哪一行呢？

　　　　 　 
<七> 常用SQL语法与函数
　  <1>、sql server(WINDOWS平台上强大的数据库平台)端常用语法说明

　  1、使用局部变量：

　　　 1> 变量定义：

　　　　　　  DECLARE @variable_name datatype [,…]

　　　　  例：

　　　　　　  declare

　　　　　　　　 @name　  varchar(30),

　　　　　　　　 @type　 int

　　　 2> 给变量赋值：

　　　 　方法一：

　　　　  　　例：

　　　　　　  　　declare @int_var　  int

　　　　　　  　　select @int_var = 12

　　　 　方法二：

　  　　　　　例：

　  　　　　　　　declare

　　　  　　　　　　 @single_auth　  varchar(40),

　　　　　  　　　　 @curdate　  　  datetime

　  　　　　　　　select @single_auth = au_lname,

　  　　　　　　　　　　 @curdate　　 = getdate()

　　　  　　　　　　from authors

　　　　　  　　　 where au_id = ‘123-45-6789’

 

2、使用T-SQL标准控制结构：

　　　 1> 定义语句块

　　　　  语法：

　　　　　　  BEGIN

　　　　　　　　 Statements

　　　　　　  END

 

　　　 2> IF ... ELSE语句

　　　　  语法：

　　　　　　  IF boolean_expression

　　　　　　　　 { statement | statement_block }

　　　　　　  ELSE

　　　　　　  　 { statement | statement_block }

　　　　  示例：

　　　　　　  if (select avg(price) from titles where type = ‘business’) > $19.95

　　　　　　  　　print ‘The average price is greater then $19.95’

　　　　　　  else

　　　　  　　　　print ‘The average price is less then $19.95’

 

　　　 3> IF EXISTS语句

　　　　  语法：

　　　　　　  IF [not] EXISTS (select_statement)

　　　　　　　　 { statement | statement_block }

　　　　　　  [ELSE

　　　　　　  　 { statement | statement_block }]

　　　　  示例：

　　　　　　  declare

　　 　　　　　　@lname  　  varchar(40),

　　　　　　  　 @msg　  　  varchar(255)

　　　　　　  select @lname = ‘Smith’

　　　　　　  if exists(select * from titles where au_lname = @lname)

　　　　　　  　 begin

　　　　　　  　　　select @msg = ‘There are authors named’ + @lname

　　　　　　  　　　print @msg

　　　　　　  　 end

　　　　　　  else

　　　　　　  　 begin

　　　　　　  　　　select @msg = ‘There are no authors named’ + @lname

　　　　　　  　　　print @msg

　　　　　　  　 end

 

　　　 4> 循环语句：

　　　　  WHILE

　　　　  语法：

　　　　　　  WHILE boolean_condition

　　　　　　　　 [{ statement | statement_block }]

　　　　　　  [BREAK]

　　　　　　  [condition]

　　　　  示例：

　　　　　　  declare

　　　　　　  　 @avg_price  　　　  　  money,

　　　　 　　　　@max_price  　　　  　  money,

　　　　　　  　 @count_rows 　　　  　  int,

　　　　　　  　 @times_thru_the_loop int

　　　　　　  select @avg_price  　　　　  = avg(price),

　　　　　　  　　　 @max_price  　　　　  = max(price),

　　　　　　  　　　 @count_rows 　 　　　 = count(*),

　　　　　　  　　　 @times_thru_the_loop = 0

　　　　　　  　from titles

　　　　　　  while @avg_price < $25 and (@count_rows < 10 or @max_price < $50)

　　　　　　  begin

　　　　  　　　 select @avg_price　　　　  = avg(price) * 1.05,

　　　　　　　　　　　  @max_price　　　　  = max(price) * 1.05,

　　　　　　　　　　　  @time_thru_the_loop = @time_thru_the_loop + 1

　　　　　　  end

　　　　　　  if @time_thru_the_loop = 0

　　　　　 　　　select @time_thru_the_loop = 1

　　　　　  　update titles

　　　　　　　　 set price = price * power(1.05, @time_thru_the_loop)

 

　　　 4> GOTO语句

　　　　  语法：

　　　　　　  GOTO label

　　　　　　  ...

　　　　　　  label:

　　　　  示例：

　　　　　　  begin transaction

　　　　　　  　 insert tiny(c1) values(1)

　　　　　　  　 if @@error != 0 goto error_handler

　　　　　　  　 commit transaction

　　　　　　　　 return

　　　　　　  error_handler:

　　　　　　　　 rollback transaction

　　　　　　　　 return

 

　　　 5> RETURN语句

　　　　  语法：

　　　　  　　RETURN

　　　　  （1）用于无条件退出一个批处理、存储过程或触发器。

　　　　　　　 示例：

　　　　　　　　　 if not exists(select 1 from inventory

　　  　　　　　　　　　　　　　　where item_num = @item_num)

　　 　　　　　　　begin

  　　　　　　　　　　raiseerror 51345 ‘Not Found’

　  　　　　　　　　　return

　　  　　　　　　 end

　 　　　　　　　　print ‘No error found’

　 　　　　　　　　return

　　　　  （2）用于存储过程中返回状态值。

　　　　　　　 示例：

　　　　　　　　　 create procedure titles_for_a_pub

  　　　　　　　　　　　　(@pub_name varchar(40) = null)

  　　　　　　　　 as

　  　　　　　　　 if @pub_name is null

　　  　　　　　　　　return 15

　　　  　　　　　 if not exists(select 1 from publishers

　　　　  　　　　　　　　　　　　where pub_name = @pub_name)

　　　　　  　　　　　return –101

　　　　　　  　　 select t.tile from publishers p, titles t

　　　　　　　  　　where p.pub_id = t.pub_id

  　　　　　　　　　　and pub_name = @pub_name

  　　　　　　　　 return 0

 

　  3、T-SQL中的游标提取循环语句：

　 （1）FETCH [NEXT FROM] cursor_name INTO @variable_1, ...@variable_n

　 （2）WHILE @@FETCH_STATUS = 0

　　　  BEGIN

　　　　　 Other_statements

  　　　　 FETCH [NEXT FROM] cursor_name INTO @variable_1, ...@variable_n

　　　  END

　 （3）CLOSE cursor_name

 

　  4、T-SQL中的事务处理语句：

　　　 1> 开始一个事务：

　　　　  BEGIN TRAN[SACTION [transaction_name]]

 

　　　 2> 提交一个事务：

　　　　  COMMIT TRAN[SACTION [transaction_name]]

 

　　　 3> 回滚一个事务：

　　　　  ROLLBACK TRAN[SACTION [transaction_name]]

 

　　　 4> 使用事务保存点：

　　　　  BEGIN TRAN[SACTION [transaction_name]]

  　　　　　 SAVE TRAN[SACTION] savepoint_name

  　　　　　 ROLLBACK TRAN[SACTION] savepoint_name

　　　　  COMMIT TRAN[SACTION [transaction_name]]

 

　  5、T-SQL中可用于错误判断或其它处理的全局变量：

　　　 1>  @@rowcount：　　　  前一条命令处理的行数

　　　 2>  @@error：　 　　　  前一条SQL语句报告的错误号

　　　 3>  @@trancount：　　　  事务嵌套的级别

　　　 4>  @@transtate：　　　  事务的当前状态

　　　 5>  @@tranchained：  当前事务的模式（链接的（chained）或非链接的）

　　　 6>  @@servername：　  本地sql server(WINDOWS平台上强大的数据库平台)的名称

　　　 7>  @@version　 ：　　　  sql server(WINDOWS平台上强大的数据库平台)和O/S的版本级别

　　　 8>  @@spid：　  　　　  当前进程的id

　　　 9>  @@identity：　　　  上次insert操作中使用的identity值

　　　 10> @@nestlevel：　　　  存储过程/触发器中的嵌套层

　　　 11> @@fetch_status： 游标中上条fetch语句的状态

 

　  6、使用标准内置错误消息发送函数：

　　　 函数说明：

　　　　　 RAISERROR ({msg_id | msg_str}, severity, state

　  　　　 [, argument1 [，argument2][，...] )

　  　　　 [WITH LOG]

 

　　　 其中，msg_id表示错误号，用户定义错误消息的错误号在50001到2147483647之

　　　 间，特定的消息会引起错误50000。msg_str是错误消息正文，最多可有255个字

　　　 符。Severity描述了与这个消息关联的用户定义的严重性级别，取值包括0和10

　　　 至25之间的任何整数。State描述了错误的“调用状态”，它是1到127之间的整

　　　 数值。Argument定义用于代替在msg_str中定义的变量或对应与msg_id的消息的

　　　 参数。WITH LOG表示要在服务器错误日志和事件日志中记录错误。

　　　 例1：

　　　 　 RAISEERROR( ‘Invalid customer id in order.’, 16, 1)

　　　　  则返回：

　　　　　　  Msg 50000, Level 16, State 1

　　　　  　　Invalid customer id in order.

　　　 例2：

　　　　  sp_addmessage 52000, 16, ‘Invalid customer id %s in order’

　　　 　 RAISEERROR( 52000, 16, 1, ‘ID52436’)

　　　　  则返回：

　　　　　　  Msg 52000, Level 16, State 1

　　　　  　　Invalid customer id ID52436 in order.

　 

　  <2>、Oracle(大型网站数据库平台)端常用语法说明

　  1、使用局部变量：

　  　 1> 定义变量：

　  　　　VARIABLE_NAME　 DATA TYPE　 [ := INITIAL VALUE ] ;

　　　　  例：定义变量

　 　　　　　 v_Num　 　  number;

　  　　　　　v_string　  varchar2(50);

　  　　　例：定义变量并赋初值

　 　　　　　 v_Num　 　  number := 1 ;

　  　　　　　v_string　  varchar2(50) := ‘Hello world!’ ;

　　　 2> 给变量赋值：

　　　 　方法一：

　　　　  　　例：

　　　 　　　　　 v_Num　 　 := 1;

　 　　　　　　　 v_string := ‘Hello world!’;

　　　 　方法二：

　  　　　　　例：

　　　 　　　　　 SELECT first_name INTO v_String

　 　　　　　　　　 FROM students

　 　　　　　　　　WHERE id = v_Num ;

 

　  2、使用PL/SQL标准控制结构：

　　　 1> 定义语句块

　　　　  语法：

　　　　　　  BEGIN

　　　　　　　　 Statements ；

　　　　　　  END ；

 

　　　 2> IF ... THEN ... ELSE语句

　　　　  语法：

　 　　　　　 IF boolean_expression THEN

　　　　　　　　 { statement | statement_block } ；

　　　　　　  [ELSIF boolean_expression THEN  　  /*注意此处的写法—— ELSIF */

　　　　　　  　 { statement | statement_block } ；]

　　　　　　  ...

　　　　　　  [ELSE

　　　　  　　　 { statement | statement_block } ；]

　　　　　　  END IF ；

 

　　　　  示例：

　　　　　　  v_NumberSeats rooms.number_seats%TYPE;

　　　　　　  v_Comment VARCHAR2(35);

　　　　　　  BEGIN

　　　　　　  /* Retrieve the number of seats in the room identified by ID 99999.

　　　　　　  　 Store the result in v_NumberSeats. */

　　　　　　  　SELECT number_seats

　　　  　　　　　INTO v_NumberSeats

　　　　　　　　  FROM rooms

　　　　　　　　 WHERE room_id = 99999;

　　　　　　　  IF v_NumberSeats < 50 THEN

　　　　　　  　　 v_Comment := ’Fairly small’;

　　　　　　　  ELSIF v_NumberSeats < 100 THEN

　　　　　　  　　 v_Comment := ’A little bigger’;

　　　　　　　  ELSE

　　　　　　  　　 v_Comment := ’Lots of room’;

　　　　　　　  END IF;

　　　　　　  END;

 

　　　 3> 循环语句：

　　　　 （1）简单循环语句：

　　　　　　  语法：

　　　　　　　　  LOOP

　  　　　　　　　　 { statement | statement_block } ；

　　　　　　　　　　 [EXIT [WHEN condition] ；]

　　　　　　　　  END LOOP ；

　　　　　　　　  其中，语句EXIT [WHEN condition];等价于

　　　　　　　　　　  IF condition THEN

　　　　　　　　　　　　 EXIT ；

　　　　　　　　　　  END IF ；

　　　　　　  示例1：

　　　　　　　　  v_Counter BINARY_INTEGER := 1;

　　　　　　　　  BEGIN

　　　　　　　　　  LOOP

　　　　　　　　　　  -- Insert a row into temp_table with the current value of the

　　　　　　　　　　  -- loop counter.

　　　　　　　　　　  INSERT INTO temp_table

　　　　　　　　　　　　　 VALUES (v_Counter, ’Loop index’);

　　　　　　　　　　  v_Counter := v_Counter + 1;

　　　  　　　　　　　-- Exit condition - when the loop counter > 50 we will

　　　　　　　　　　  -- break out of the loop.

　　　　　　　　　　  IF v_Counter > 50 THEN

　　　　　　　　　　　　 EXIT;

　　　　　　　　　　  END IF;

　　　　　　　　　  END LOOP;

  　　　　　　　　END;

 

　　　　　　  示例2：

　　　　　　　　  v_Counter BINARY_INTEGER := 1;

　　　　　　　　  BEGIN

　　　　　　　　　  LOOP

　　　　　　　　　　  -- Insert a row into temp_table with the current value of the

　　　　　　　　　　  -- loop counter.

　　　　　　　　　　  INSERT INTO temp_table

　　　　　　　　　　　　　 VALUES (v_Counter, ’Loop index’);

　　　　　　　　　　  v_Counter := v_Counter + 1;

　　　　　　　　　　  -- Exit condition - when the loop counter > 50 we will

　　　　　　　  　　　-- break out of the loop.

　　　　　　　　　　  EXIT WHEN v_Counter > 50;

　　　　　　　　　  END LOOP;

　　　　　　　　  END;

 

　　　　 （2）WHILE循环语句：

　　　　　　  语法：

　　　　　　　　  WHILE condition LOOP

　  　　　　　　　　 { statement | statement_block } ；

　　　　　　　　  END LOOP ；

 

　　　　　　  示例1：

　　　　　　　　  v_Counter BINARY_INTEGER := 1;

　　　　　　　　  BEGIN

　　　  　　　　　　-- Test the loop counter before each loop iteration to

　　　　　　　　　  -- insure that it is still less than 50.

　　　　　　　　　  WHILE v_Counter <= 50 LOOP

　　　　　　　　　　  INSERT INTO temp_table

　　　　　　　　  　　　　 VALUES (v_Counter, ’Loop index’);

　　　　　　　　　　  v_Counter := v_Counter + 1;

　　　　　　　　　  END LOOP;

　　　　　　　　  END;

 

　　　　　　  示例2：

　　　　　　　　  v_Counter BINARY_INTEGER;

　　　　　　　　  BEGIN

　　　　　　　　　  -- This condition will evaluate to NULL, since v_Counter

　　　　　　　　　  -- is initialized to NULL by default.

　　　　　　　　　  WHILE v_Counter <= 50 LOOP

　　　　　　　　　　  INSERT INTO temp_table

　　　　　　　　  　　　　 VALUES (v_Counter, ’Loop index’);

　　　　　　　　　　  v_Counter := v_Counter + 1;

　　　　　　　　　  END LOOP;

　　　　　　　　  END;

 

　　　　 （3）数字式FOR循环语句：

　　　　　　  语法：

　　 　　　　　　 FOR loop_counter IN [REVERSE] low_bound..high_bound LOOP

　  　　　　　　　　 { statement | statement_block } ；

　　　　　　　　  END LOOP ；

　　　　　　　　  这里，loop_counter是隐式声明的索引变量。

 

　　　　　　  示例1：

　　　　　　　　  FOR循环的循环索引被隐式声明为BINARY_INTEGER。在循环前面没有

　　　　　　　　  必要声明它，如果对它进行了声明，那么循环索引将屏蔽外层的声明，

　　　　　　　　  如下所示

　　　　　　　　  v_Counter  NUMBER := 7;

　　　　　　　　  BEGIN

　　　　　　　　　  -- Inserts the value 7 into temp_table.

　　　　　　　　　  INSERT INTO temp_table (num_col)

　　　　　　　　　　  VALUES (v_Counter);

　　　　　　　　　  -- This loop redeclares v_Counter as a BINARY_INTEGER, which

　　　　　　　　　  -- hides the NUMBER declaration of v_Counter.

　　　　　　　　　  FOR v_Counter IN 20..30 LOOP

  　　　　　　　　　　-- Inside the loop, v_Counter ranges from 20 to 30.

　　　　　　　　　　  INSERT INTO temp_table (num_col)

　　　　　　　　　　　  VALUES (v_Counter);

　　　　　　　　　  END LOOP;

　　　　　　　　　  -- Inserts another 7 into temp_table.

　　　　　　　　　  INSERT INTO temp_table (num_col)

　　　　　　　　　　  VALUES (v_Counter);

　　　　　　　　  END;

 

　　　　　　  示例2：

　　　　　　　　  如果在FOR循环中有REVERSE关键字，那么循环索引将从最大值向最

　　　　　　　　  小值进行循环。请注意语法是相同的——仍然首先书写的是最小值，

　　　　　　 　　 如下所示

　　　　　　　　  BEGIN

　　　　　　　　  　FOR v_Counter IN REVERSE 10..50 LOOP

　　　　　　　　  　　-- v_Counter will start with 50, and will be decremented

　　　　　　　　  　　-- by 1 each time through the loop.

　　　　　　　　  　　NULL;

　　　　　　　　  　END LOOP;

　　　　　　　　  END;

 

　　　　　　  示例3：

　　　　　　　　  FOR循环中的最大值和最小值没有必要必须是数字型文字，它们可以

　　　　　　　　  是能够被转换为数字值的任何表达式，如下所示

　　　　　　　　  v_LowValue  　  NUMBER := 10;

　　　　　　　　  v_HighValue　  NUMBER := 40;

　　 　　　　　　 BEGIN

　　　　　　　　  　FOR v_Counter IN REVERSE v_LowValue..v_HighValue LOOP

　　　　　　　　　　  INSER INTO temp_table

　　　　　　　　  　　　　VALUES (v_Counter, ‘Dynamically sqecified loop range’);

　　　　　　　　  　END LOOP;

　　　 　　　　　 END;

 

　　　 4> GOTO语句

　　　　  语法：

　　　　　　  GOTO label;

　　　　　　  ...

　　　　　　  <<label>>

　　　　　　  ...

 

　　　　  示例：

　　　　　　  v_Counter  BINARY_INTEGER := 1;

　　　　　　  BEGIN

　　　　　　　  LOOP

　　　　　　　　  INSERT INTO temp_table

　　　　  　　　　　VALUES (v_Counter, ’Loop count’);

　　　　　　　　  v_Counter := v_Counter + 1;

　　　　　　　　  IF v_Counter > 50 THEN

　　　　　　　　　  GOTO l_EndOfLoop;

　　　　　　　　  END IF;

　　　　　　　  END LOOP;

　　　　　　 

　　　　　　　  <<l_EndOfLoop>>

　　　　　　　  INSERT INTO temp_table (char_col)

　　　　　　　　  VALUES (’Done!’);

　　　　　　  END;

 

　　　 5> EXIT语句

　　　　  语法：

　　　　  　　EXIT;

　　　　  参见上面的PL/SQL标准控制结构之循环语句说明部分。

 

　  3、PL/SQL中的游标提取循环语句：

　　　 1> 简单循环

　　　　  此循环采用简单的循环语法（LOOP..END LOOP），如下所示

　　　　　  -- Declare variables to hold information about the students

　　　　　  -- majoring in History.

　　　　　  v_StudentID　 students.id%TYPE;

　　　　　  v_FirstName　 students.first_name%TYPE;

　　　　　  v_LastName　  students.last_name%TYPE;

　　　　　  -- Cursor to retrieve the information about History students

　　　　　  CURSOR c_HistoryStudents IS

　　　　　  SELECT id, first_name, last_name

　　　　　　  FROM students

　　　　　　 WHERE major = ’History’;

　　　　  BEGIN

　　　　　  -- Open the cursor and initialize the active set

　　　　　  OPEN c_HistoryStudents;

　　　　　  LOOP

　　　　　　  -- Retrieve information for the next student

　　　　　　  FETCH c_HistoryStudents INTO v_StudentID, v_FirstName, v_LastName;

　　　　　　  -- Exit loop when there are no more rows to fetch

　　　　　　  EXIT WHEN c_HistoryStudents%NOTFOUND;

　　　　　　  -- Process the fetched rows.  In this case sign up each

　　　　　　  -- student for History 301 by inserting them into the

　　　　　　  -- registered_students table. Record the first and last

　　　　　　  -- names in temp_table as well.

　　　　　　  INSERT INTO registered_students (student_id, department, course)

　　　　　　　  VALUES (v_StudentID, ’HIS’, 301);

　　　　　　  INSERT INTO temp_table (num_col, char_col)

　　　　　　　  VALUES (v_StudentID, v_FirstName || ’ ’ || v_LastName);

　　　　　  END LOOP;

　　　　　  -- Free resources used by the cursor

　　　　　  CLOSE c_HistoryStudents;

　　　　　  -- Commit our work

　　　　　  COMMIT;

　　　　  END;

　　　　  请注意，EXIT WHEN语句的位置是紧跟在FETCH语句的后边。在检索完最后一

　　　　  个行以后，c_HistoryStudents%NOTFOUND变为TRUE，该循环退出。EXIT WHEN

　　　　  语句的位置也在数据处理部分的前面，这样做是为了确保该循环过程不处理任

　　　　  何重复行。

 

　　　 2> WHILE循环

　　　　  此循环采用WHILE .. LOOP的循环语法，如下所示

　　　　　  -- DECLARE cursor to retrieve the information about History students

　　　　　  CURSOR c_HistoryStudents IS

　　　　　  SELECT id, first_name, last_name

　　　　　　  FROM students

　　　　　　 WHERE major = ’History’;

 

　　　　　  -- Declare a record to hold the fetched information.

　　　　　  v_StudentData  c_HistoryStudents%ROWTYPE;

　　　　  BEGIN

　　　　　  -- Open the cursor and initialize the active set

　　　　　  OPEN c_HistoryStudents;

　　　　　  -- Retrieve the first row, to set up for the WHILE loop

　　　　　  FETCH c_HistoryStudents INTO v_StudentData;

　　　　　  -- Continue looping while there are more rows to fetch

　　　　　  WHILE c_HistoryStudents%FOUND LOOP

　　　　　　  -- Process the fetched rows, in this case sign up each

　　　　　　  -- student for History 301 by inserting them into the

　　　　　　  -- registered_students table. Record the first and last

　　　　　　  -- names in temp_table as well.

　　　　　　  INSERT INTO registered_students (student_id, department, course)

　　　　　　　  VALUES (v_StudentData.ID, ’HIS’, 301);

　　　　　　  INSERT INTO temp_table (num_col, char_col)

　　　　　　　  VALUES (v_StudentData.ID,

　　　　　　　　　　　  v_StudentData.first_name || ’ ’

　　　　　　　　　　　　　　　　　　　　　　　　 || v_StudentData.last_name);

　　　　　　  -- Retrieve the next row. The %FOUND condition will be checked

　　　　　　  -- before the loop continues again.

　　　　　　  FETCH c_HistoryStudents INTO v_StudentData;

　　　　　  END LOOP;

　　　　　  -- Free resources used by the cursor

　　　　　  CLOSE c_HistoryStudents;

　　　　　  -- Commit our work

　　　　　  COMMIT;

　　　　  END;

　　　　  请注意，FETCH语句出现了两次——一次是在循环的前面，另一次是在循环处

　　　　  理的后面，这样做是为了使循环条件（c_HistoryStudents%FOUND）对每一次循

　　　　  环叠代都求值以确保该循环过程不处理任何重复行。

 

　　　 3> 游标式FOR循环

　　　　  因与迁移关系不大，此处略。

 

　  4、PL/SQL中的事务处理语句：

　　　 在PL/SQL中，事务的开始位置是从前一个事务结束以后执行的第一条SQL语句，

　　　 或者在连接到该数据库以后所执行的第一条SQL语句。事务的结束是使用COMMIT

　　　 或ROLLBACK语句标识的。

　　　 1> COMMIT的语法是：

　　　　　 　 COMMIT [work];

　　　　　　  可选的关键字work用来提高可读性。

　　　 2> ROLLBACK的语法是：

　　　　  　　ROLLBACK [work];

　　　　　　  可选的关键字work用来提高可读性。

　　　 3> ROLLBACK语句会撤消整个事务，如果使用SAVEPOINT命令，那么只有部分的事

　　　　  务需要被撤消，其语法是：

　　　　　　  SAVEPOINT name;

　　　　　　  这里name是保存点的名字。

　　　 4> 示例：

　　　　 　　 v_NumIterations　 NUMBER;

　　　　　　  BEGIN

　　　　　　　  -- Loop from 1 to 500, inserting these values into temp_table.

　　　　　　　  -- Commit every 50 rows.

　　　　　　　  FOR v_LoopCounter IN 1..500 LOOP

　　　　　　　　  INSERT INTO temp_table (num_col) VALUES (v_LoopCounter);

　　　　　　　　  v_NumIterations := v_NumIterations + 1;

　　　　　　　　  IF v_NumIterations = 50 THEN

　　　　　　　　　  COMMIT;

　　　　　　　　　  v_NumIterations := 0;

　　　　　　　　  END IF;

　　　　　　　  END LOOP;

　　　　　　  END;

 

　  5、使用标准内置错误消息发送函数：

　  　 与T-SQL中RAISEERROR对应，PL/SQL中有这样一个内置函数

　　　 函数说明：

　  　　　 RAISE_APPLICATION_ERROR (error_number, error_message, [keep_errors]) ;

　　　 这里，error_number是从-20,000到-20,999之间的参数；error_message是与此

　 　　错误相关的正文，error_message必须不多于512个字节；而keep_errors是一个

　　　 可选的布尔值参数，其为TRUE则新的错误将被添加到已经引发的错误列表中（如

　　　 果有的话），其为FALSE（这是缺省的设置）则新的错误将替换错误的当前列表。

　　　 例：

　  　　　 RAISE_APPLICATION_ERROR(-20000, ‘Can’t find any record.’) ;

 

　  <3>、T-SQL与PL/SQL常用函数比较(以下的exp为expression的缩写)

T-SQL

PL/SQL

字符类函数

Ascii(char_exp)

Ascii(str_exp)

Char(int_exp)

Chr(int_exp)

Datalength(char_exp)

Length(str_exp)

Substring(exp, start, length)

Substr(exp, start, length)

Upper(char_exp)

Upper(str_exp)

Lower(char_exp)

Lower(str_exp)

Stuff(char_exp1,start,length,

　　  Char_exp2)

Translate(str_exp,from_str,to_str)

Ltrim(char_exp)

Ltrim(str_exp1 [,str_exp2])

Rtrim(char_exp)

Rtrim(str_exp1 [,str_exp2])

日期类函数

Getdate()

Sysdate

数学类函数

Abs(numeric_exp)

Abs(number_exp)

Ceiling(numeric_exp)

Ceil(number_exp)

Exp(float_exp)

Exp(number_exp)

Floor(numeric_exp)

Floor(number_exp)

Power(numeric_exp,int_exp)

Power(number_exp1,number_exp2)

Round(numeric_exp,int_exp)

Round(number_exp1 [,number_exp2])

Sign(int_exp)

Sign(number_exp)

Sqrt(float_exp)

Sqrt(number_exp)

转换函数

Convert(datatype[(length)],exp,format)

To_char(datatype,str_format)

Convert(datatype[(length)],exp,format)s

To_date(str_exp,date_format)

Convert(datatype[(length)],exp,format)

To_number(str_exp,num_format)

其它函数

AVG([ALL | DISTINCT] col)

AVG([ALL | DISTINCT] col)

COUNT({[ALL | DISTINCT] col] | *})

COUNT({[ALL | DISTINCT] col} | *))

MAX([ALL | DISTINCT] col)

MAX([ALL | DISTINCT] col)

MIN([ALL | DISTINCT] col)

MIN([ALL | DISTINCT] col)

SUM([ALL | DISTINCT] col)

SUM([ALL | DISTINCT] col)

STDEV(col)

STDDEV(col)

VAR(col)

VARIANCE(col)

ISNULL(check_exp, replace_value)

NVL(check_exp, replace_value)

CASE

DECCODE

 

<4>mssql(WINDOWS平台上强大的数据库平台)与Oracle(大型网站数据库平台)比较注意几个语法转换

(1)ISNULL与 NVL

  在mssql(WINDOWS平台上强大的数据库平台)中为了替换空值常用ISNULL函数，如ISNULL(@dno,”00”)表示当变量@dno的值为空时，则用”00”替换其值;在Oracle(大型网站数据库平台) 中，同样的功能用NVL实现，如上述例可以用NVL(dno,”00”)来替换。

(2)CASE 与 DECCODE

CASE在mssql(WINDOWS平台上强大的数据库平台)中可以用以对某个值进行多个判断分支进行处理，简化了代码如下：

Update Student set Class = (Case inyear when “1993” then “8” when “1994” then “7” when “1995 then “6” else “0”)

　　  相同的功能在Oracle(大型网站数据库平台)中可以用DECCODE来实现,如上例在Oracle(大型网站数据库平台)应作如下处理:

　　　 　　　 Update Student set class=deccode (inyeare,’1993’,’8’,’1994’,’7’,’1995’,’6’,’0 ‘) ;

 (3)日期运算

在mssql(WINDOWS平台上强大的数据库平台)中对于日期的处理不能数值型有很大区，其主要实现的函数有DATEADD、DATEDIFF；而在Oracle(大型网站数据库平台)中把日期当作数值来处理，其主要处理函数有ADD_MONTH、MONTH_BETWEEN、D1(+-*/)D2等,下面列出其替换方法

DATEADD(YEAR,1,pubdate)

ADD_MONTHS(D1,12)

DATEADD(MONTH,3,pubdate)

ADD_MONTHS(D1,3)

DATEADD(DAY,13,pubdate)

D1 + 13

DATEADD(DAY,-3,pubdate)

D1 – 3

DATEADD(HOUR,6,pubdate)

D1 + 6/24

DATEADD(MINUTES,24,pubdate)

D1 + 24/1440

DATEDIFF(minute, D1, D2)

(D2-D1)*1440

DATEDIFF(hour, D1, D2)

(D2-D1)*24

DATEDIFF(month, D1, D2)

MONTHS_BETWEEN(D1,D2)

DATENAME(month, getdate())

TO_CHAR(sysdate,’MONTH’)

DATENAME(year, getdate())

TO_CHAR(sysdate,’YEAR’)

DATENAME(day,getdate())

TO_CHAR(sysdate,’DAY’)

DATEDART(month,getdate())

TO_CHAR(sysdate,’MM’)

DATEDART(year,getdate())

TO_CHAR(sysdate,’YYYY’)

DATEDART(day,getdate())

TO_CHAR(sysdate,’DD’)

GETDATE（）

sysdate

 

<4>、从T-SQL向PL/SQL迁移方案

　  通过上述讨论，在从T-SQL向PL/SQL迁移时，在常用语法与函数方面要逐一细致比较

　  后再行调整，特别要注意常用函数怎么进行替换和主体控制结构怎么进行调整。

　 (1)将所有的GETDATE全部转换成为SYSDATE；

   (2）将所有的selct @var = column from table where condition 改成

　　　　　　   select column into var from table where condition;

　　　 将所有的selct @var1 = @var2  改成

　　　　　  　 var1 :=var2;

　 (3)将所有的convert全部转换成为 to_char 或 trunc

　 例一：

　 declare rq1 datetime,rq2 datetime

　 …

　　　 select  je from sr where rq > = convert (char(10),rq1,111)

　　　　 and rq < convert(char(10),rq2,111)

　 应改成:

　 date rq1;

　 date rq2;

　　  select sr into je where rq > = trunc(rq1) and  rq < trunc(rq2);

　 例二：

　 declare rq1 datetime,rq2 datetime

　 …

　 select  je from sr where convert(char(10),rq,111) > = convert (char(10),rq1,111)

　　  and rq < convert(char(10),rq2,111)

　 应改成:

　 date rq1;

　 date rq2;

　　  select sr into je where trunc(rq)> = trunc(rq1) and  trunc(rq) < trunc(rq2);

  或：

　 date rq1;

　 date rq2;

　　  select sr into je where  to_char(rq,’yyyy/mm/dd’)> =to_char(rq1,’yyyy/mm/dd’)　　  and  to_char(rq,’yyyy/mm/dd’) < to_char(rq2,’yyyy/mm/dd’);

(3)PL/SQL不支付时间字段与规则字符串的直接比例

 如在T-SQL中的

  select @je = sr where rq > ‘2001.01.01’是可以的。

而在PL/SQL中

  select sr into je where rq > ‘2001.01.01’;是行不通的，如果要实现比例，则应改成;

  select sr into je where rq > to_date(‘2001.01.01’,’yyyy.mm.dd’);或

  select sr into je where to_char(rq,’yyyy.mm.dd’) > ‘2001.01.01’;

(4)将T-SQL中的DATEDIFF全部改成TRUNC(D1-D2),MONTHS_BETWEEN

　 如select @ts = datediff(day,date1,date2),在PL/SQL中应改为：

　　 ts = trunc(date2 – date1);

　 如select @ys = datediff(month,date1,date2),在PL/SQL中应改为：

　　 ts = months_between(date1 – date2);

(5)DateAdd全部改为D+n 或Add_Months

　 如select date2 = dateadd(day,date1,3),在PL/SQL中应改为：

　　  date2 :=date1 + 3;

　 如select date2 = dateadd(month,date1,6),在PL/SQL中应改为：

　　  date2 :=add_months(date1 ,6);

(6)
<八> 临时表问题
ORALCE8i以上的版本才支持临时表，其创建语法为:

CREATE GLOBAL TEMPORARY TABLE table_name

(clomn1 type,column2 type);而对于ORALCE8i以下的版本不支持，综合考虑，在从sql server(WINDOWS平台上强大的数据库平台)向Oracle(大型网站数据库平台)迁移时，对于临时表，采用以下方案：

　  1、将T-SQL语句中的临时表在后台实际化——即将之创建为正式表，在其本身的列中增加一列作为序号用于标识不同的操作。

2、在将临时表实际化时对所有这样的表都要加“tmp_”前缀。
<九> Oracle(大型网站数据库平台)特别处
<1>、dual的使用

在Oracle(大型网站数据库平台)中可以用这样的语法从后台服务器提取时间值：

　　　  select sysdate into :varible from dual ;

　  <2>、Oracle(大型网站数据库平台)不能在存储过程中建数据表
<十>连接远程的数据库（包括不同服务器）
数据库链接（Database Link）与分布式数据库功能紧密相连。数据库链接允许用户处理远程数据库而不用知道数据是在什么地方。当建立了一个数据库链接之后，提供对远程数据的登录信息。每当使用数据库链接时，在分布式网络上初始化一个对话（Session），以解决对远程数据库对象的引用。

(1)  先创建一个远程数据库的别名，用Oracle(大型网站数据库平台)8 NET EASY CONFIG，也在在文件TNSNAMES.ORA中加入以下格式内容:

别名.WORLD =

(DESCRIPTION =

　  (ADDRESS = (PROTOCOL = NMP)(SERVER = 远程数据服务器名称)(PIPE = ORAPIPE))

(CONNECT_DATA = (SID = 远程数据库名称)))

(2)  创建一数据库链接

语法：

CREATE OR REPLACE [public] DATABASE LINK connect_name

CONNECT TO username IDENTIFIED BY password

USING ‘connect_string’;

　  　 创建数据库链接时具体使用的语法取决于下面两个条件：

n　　　　 数据库链接的“公共”或“私有”状态；

n　　　　 使用缺省还是显式方式登录远程数据库。

 

如果建立的数据库链接是公共所用，则在创建时应指明关键字 PUBLIC；无此参数系统默认为私有;

可以为数据库链接指定用户和密码，如下：

create or replace database link db_link_1

connect to ‘scott’ identified by ‘tiger’

using ‘hq’

则在当前数据库创建了一个指向远程数据库”hq”的链接，连接时所用的用户名和密码为“tiger”;

如果不指定用户和密码，则在应用数据库链接，系统会使用当时的用户进行尝试连接，创建过程如下：

create or replace database link db_link_1

using ‘hq’

（3）远程数据库对象的引用

创建数据库链接以后，在当前数据库就可以访问远程数据库中具有访问权限的对象，引用的方法为将数据库链接名称添加到任何可以访问远程帐号的表或视图上。当将数据库链接名称加添加到表或视图名称上时，必须在数据库链接名称之前使用一个“@”，如：

SELECT * FROM worker@remote_connect;

(3)  使用同义词对远程对象进行访问

对于已创建了数据库链接的远程数据中对象，可以在本地数据库创建其同义词，达到在访问在逻辑上相当天本地数据库对象，使语法简洁，如下：

CREATE SYNONYM worker_syn

 FOR worker@remote_connect;

创建后，对于远程数据库remote_connect的worker表的访问就可以变成如下：

select * from worker_syn;