不知道從哪里得到這個文檔，有用就放上來了 －gwb
數據庫端sql語法的遷移
以下為常用的sql語法遷移，包括數據類型、id列向sequence遷移、表（主鍵、外鍵、check、unique、default、index）、游標、存儲過程、函數、觸發器、常用sql語法與函數幾個方面，考慮sql server的實際情況，沒有涉及oracle特有的package、exception等。在以下的描述中，將sql server的transact-sql簡稱為t-sql。在oracle中，其語法集稱為pl/sql。
<一> 數據類型的遷移
<1>、oracle端語法說明
在oracle中，分析其數據類型，大致可分為數字、字符、日期時間和特殊四大類。其中，數字類型有number；字符類型有char與varchar2；日期時間類型只有date一種；除此之外，long、raw、long raw、blob、clob和bfile等數據類型都可視為特殊數據類型。
<2>、sql server端語法說明
在sql server中，參照上面對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向oracle的遷移方案
比較oracle與sql server在數據類型上的不同，當從sql server向oracle遷移時，可以做如下調整：
sql server
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端語法說明
在sql server中，可以將數據庫中的某一字段定義為identity列以做主鍵識別，如：
jlbh numeric(12,0) identity(1,1) /*記錄編號字段*/
constraint pk_tbl_example primary 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 primary 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向oracle的遷移方案
根據以上分析，當從sql server向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端語法說明
有如下sql server語句：
/* ------------------------ 創建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 primary 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 primary 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的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向oracle的遷移方案
比較這兩段sql代碼，可以看出，在創建表及其主鍵、外鍵、check、unique、default、index時，sql server 與oracle的語法大致相同,但時遷移時要注意以下情況:
（1） oracle定義表字段的default屬性要緊跟字段類型之后，如下：
create table mz_ghxx
( ghlxh number primay key ,
rq date default sysdate not null,
….
)
而不能寫成
create table mz_ghxx
( ghlxh number primay 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端語法說明
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、隱式游標
mssqlserver中對于數據操縱語句受影響的行數，有一個全局的變量：@@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 server 一樣，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向oracle的遷移方案
比較上述sql代碼，在遷移過程中要做如下調整：
（1）t-sql對cursor的聲明在主體代碼中，而pl/sql中對cursor的聲明與變
量聲明同步，都要在主體代碼（begin關鍵字）之前聲明，所以在遷移時要
將游標聲明提前,mssql server的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 server類型的游標，如果游標取出的值沒有參與運算的，全部采用for循環方式來替換；而對于取出的值還要進行其它運算的，可以采用直接在定義變量位置定義變量。
(6)mssql中對于同一游標重復定義幾次的情況在oracle中可通過游標變量來解決.如下：
mssql server 中：
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端語法說明
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向oracle的遷移方案
通過比較上述sql語法的差異，在遷移時必須注意以下幾點：
1、對于有返回單值的mssql存儲過程，在數據庫移值最好轉換成oralce的函數；對于mssql有大量數據的處理而又不需返回值的存儲過程轉換成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、存儲過程的調用要注意：在mssqlserver中的格式為“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端語法說明
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向oracle的遷移方案
1、通過比較上面sql語法的不同并考慮現有sql server的實際編程風格，在從
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中id列向oracle的sequence
轉換的問題。
另外，由于上面的原因，我們不能在after行級觸發器中修改 :new，因為該
語句已經被處理了。通常，:new僅僅在before行級觸發器中被修改，而:old
永遠不會被修改，僅僅可以從它讀出數據。
此外，:new和:old記錄僅僅在行級觸發器內部是有效的。如果試圖要從語句
級觸發器進行引用，將會得到一個編譯錯誤。因為語句級觸發器只執行一次
——盡管語句要處理許多行——所以:new和:old是沒有意義的，因為怎么確
定它們引用的會是哪一行呢？
<七> 常用sql語法與函數
<1>、sql server端常用語法說明
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, [email protected]_n
（2）while @@fetch_status = 0
begin
other_statements
fetch [next from] cursor_name into @variable_1, [email protected]_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的名稱
7> @@version ： sql server和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與oracle比較注意幾個語法轉換
(1)isnull與 nvl
在mssql中為了替換空值常用isnull函數，如isnull(@dno,”00”)表示當變量@dno的值為空時，則用”00”替換其值;在oracle 中，同樣的功能用nvl實現，如上述例可以用nvl(dno,”00”)來替換。
(2)case 與 deccode
case在mssql中可以用以對某個值進行多個判斷分支進行處理，簡化了代碼如下：
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中對于日期的處理不能數值型有很大區，其主要實現的函數有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向oracle遷移時，對于臨時表，采用以下方案：
1、將t-sql語句中的臨時表在后臺實際化——即將之創建為正式表，在其本身的列中增加一列作為序號用于標識不同的操作。
2、在將臨時表實際化時對所有這樣的表都要加“tmp_”前綴。
<九> oracle特別處
<1>、dual的使用
在oracle中可以用這樣的語法從后臺服務器提取時間值：
select sysdate into :varible from dual ;
<2>、oracle不能在存儲過程中建數據表
<十>連接遠程的數據庫（包括不同服務器）
數據庫鏈接（database link）與分布式數據庫功能緊密相連。數據庫鏈接允許用戶處理遠程數據庫而不用知道數據是在什么地方。當建立了一個數據庫鏈接之后，提供對遠程數據的登錄信息。每當使用數據庫鏈接時，在分布式網絡上初始化一個對話（session），以解決對遠程數據庫對象的引用。
(1) 先創建一個遠程數據庫的別名，用oracle8 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 [email protected]_connect;
(3) 使用同義詞對遠程對象進行訪問
對于已創建了數據庫鏈接的遠程數據中對象，可以在本地數據庫創建其同義詞，達到在訪問在邏輯上相當天本地數據庫對象，使語法簡潔，如下：
create synonym worker_syn
for [email protected]_connect;
創建后，對于遠程數據庫remote_connect的worker表的訪問就可以變成如下：
select * from worker_syn;
