-【优秀文档】Oracle翻译注意事项（整理版）

1、oracle翻译注意事项修改历史记录日期版本说明作者2008-7-61. 0新增庄隸强2008-7-91. 1新增庄炎炎强2008-7-171.2新增庄炎炎强2008-7-201.3新增庄炎炎强2008-8-131.4修改庄炎炎强资料提供捉交h期版本说明提交人2008-6-23江小玲2008-7-6庄炎炎强文件变更记录*a -增加m -修订d -删除变更 版本号口期变更类型(a*m*d)修改人变更摘要备注copyright noticecopyright© 2008 gillion technologies ltd.no part of this publication may be

2、 copied without the expressed writtenpcrmission from gill ion technology ltd1.概述木文档目的：对在omcle翻译过程中遇到问题给比较统一的解决方案，同时对于刚接触 oracle的成员快速的学习和认识oracle2适用范e2. 1.机构本规范涉及的机构范闌：产品组成员.使用oracle的项h组2. 2.角色和职责角色职责编码人员依照本文档进行oracle存储过程翻译，学习和认识oracleqa3名词术语4.oracle翻译注意事项全称含义说明：我们将采取对比原有sql的语句来介绍和解释oracle语句和编码要求4. 1

3、. oracle常用函数4.1.1 instr函数的使用概述：oracle的instr函数相当于sql中的charindex函数sql用法格式：ciiarindex (目标字符串，源字符串,起始位置)例子 1: select ciiarindex (' c',' abcde*)输出结果为:3说明：在“源字符串”中查找“忖标字符串”的位置。例子 2： select charindex (' c',' abcdefxyzcqa' ,4)输出结果为：10说明：从“源字符串”的第4个字符(包含第4个字符)中査找“目标字符串”的位置。 oracle

4、用法格式：tnstr(源字符串，目标字符串，起始位置，匹配序号)例子 1： select instr('corporate floor'or', 3, 2) from dual;输出结果：14说明：目标字符串为'or',起始位置为3,収第2个匹配项的位置。默认査找顺序为从左到右。例子2： select txstr (? csorporate floor'or', 3, 2) from dual;输出结果：6例子3： select 1str('corporate floor', 'or', -1, 2) fr

5、om dual;输出结果：5说明：当起始位置为负数的时候，从右边开始查找。例子4： select instr(* corporate floor'or') from dual;输出结果:2说明：此时默认起始位置1,匹配序号为14.1. 2 substr函数的使用概述：oracle的substr函数相当于sql中的si bstrixg函数sql 用法格式：substring ( expression , start , length )例子 1: select substring(* abcdefxyzcqa', 4, 3)输出结果:def说明:select substr

6、ing (* abcdefxyzcqa，, 4)将报错:substring 函数要求有 3 个参数。oracle 用法格式：substr( string, start_position , length )例子 1： select sibstr(* th1s1satest，, 6, 2) from dual;输出结果:sa说明：从第6个字符(包含第6个字符)开始取2位例子 2： select substr('thisisatest', 6) from dual;输出结果:satest说明：从第6个字符(包含第6个字符)开始取到结束例子 3： select substr('

7、;techonthenet', -& 5) from dual;输出结果:onthe说明：当起始位置为负数的时候，从右边开始查找。然后再从左往右取5位4.1. 3 nvl函数的使用概述:oracle的nvl函数相当于sql中的isnull函数sql 用法格式：1snill ( check expression , replticement value )说明:在 check expression 为 null 时将返回 rcplacement value,否则返回 chcck cxprcssion 的值，replacement_value必须与check_expresssion

8、具有相同的类熨。oracle 用法格式：nvl ( check expression , replacement value )说明：用法同上4.1.4 cast函数的使用概述：oracle的cast函数与sql中的相同，将某种数据类型的表达式显式转换为另一种数据类型。说明：对于将某种数据类型的表达式显式转换为另一种数据类型，sql中提供了 cast 和convert两种函数，但是oracle中没有convert函数而是提供其他的函数， 具体创建如下的 to_date、to_char、to_number4. 1. 5 to_date函数的使用概述：是把字符串转换为数据库小的日期类型例子：当我们

9、在翻译的时候定义变量v eieldvalue是-个varcar2 (20)的数据类型时，但是该变量船要 作为一个过滤条件的值时如：open rct1 for select * from air_cc_iieader a where a. fligiit_time >= vj«lighterom; 这时必须把v. fl1gi1tfrom变换为date类型:v fltghtfrom :二 to date(v fteldvalue,* yyyy-mm-dd ,hh24:mt:ss')4. 1. 6 t0_char 和 to_nchar 函数的使用概述：是把日期或数字转换为字符

10、串例子1：同样我们定义了变量v_f1eldvalue的数据类型为varcar2 (20),此时我想对该日期加上一天，再拼 凑到动态的oracle语句中，我们该这么做：open rct1 for * select * from air cc header where expire date<二 to_date(''' | to_char(to_date(v_fieldvalue, * yyyy-mm-dd iih24:mi:ss，) + 1), * yyyy-mm-dd hii24:mi :ss*) | |yyytbh)d iiii24:mi :ss'或者o

11、pen rct1 for ' select * from atr cc header where expire date<=to date(，'' | v_fieldvalue | ''','' yyyy-mm-dd hh24:mi:ss'') + 1'说明：oracle中没有像sql中有convert函数，所以在翻译过程中如果有遇到类似这样的sql语句:convert(varchar(20), ctn_made_date, 120)使用to date函数将其转换为如下写法：to char (ctn

12、made date , * yyyy-mm-dd hh24:mi:ss，)4.1.7 to_number函数的使用概述：是将字符转换为数字4.1.8 length函数的使用概述:oracle m length函数相当于sql的len函数,用于计算字符串长度。4. 1.9 rtrim 和 ltrim 函数概述：oracle的rtrim和ltrim函数与sql相同,rtrim删除字符变量中的尾随空格:ltrim字符删除字 符变量中的起始空格。4.1.10 replace函数的使用概述：replace ('string','s1','s2'), str

13、ing希望被替换的字符或变量,s1被替换的字符串,s2要替换的字符串。例子：select replace(' abc, def, ghi*，：')from dual输出结果:abc:def:ght4. 1. 11 sysdate函数的使用概述：oracle的sysdate类似与sql的getdate()函数的功能，用户获取当前时间。sql用法格式：例子 1: select getdate ()输出结果:2008-07-10 01:28:10. 637oracle用法格式：例子 1： select sysdate from dual输出结果:2008-7-10 1:28:424.

14、 1. 12 add_months ()函数的使用概述：add months (x, y)函数用于计算x加上y个月的结果。如果y是负数,就从x中减去y个月。 例子 1: select add_months (to_date (' 2007-7-7*,' yyyy-mm-dd，), 2) from dual输出结果:2007-9-7例 了 2： select add months (to date('2007-7-7'yyyy-mm-dd'),-2) from dual输出结果:2007-5-74. 1. 13 last_day 0函数的使用概述：last

15、_day(x)函数用于计算包含x的月的最后一天。例子 1： select last day (to date ('2007-7-7'yyyy-mm-dd') from dual;输出结果:2007-7-314. 1. 14 next_day 0函数的使用概述：next_day(x, day)函数用于计算从x开始,下一个day的日期:参数day是一个文本字符串, 例如：星期一 例子 1： select next day (to date ('2008-7-19'yyyy-mm-dd'),'星期一')from dual;输出结果：20

16、08-7-214. 1. 15 round 0函数的使用概述：rol；nd(x,unit)函数用于对x进行取整。默认情况下，x取整为最近的一天。unit是一个可选字 符串参数，它指明要取整的单元。例子1：例子2：例子3：例子4:例子5：例子6：例子7：例子8：select round(to date('2007-5-7'yyyy-mm-dd'),'yy') 输出结果：2007-1-1select round 仃o_date (' 2007-8-7*,' yyyy-mldd'),' yy') 输出结果：2008-1-

17、1select round (to_date(，2007-5-7',' yyyyflm-dd'),' mm*) 输出结果:2007-5-1select roind(to date(' 2007-5-171,' yyyy-mm-dd'), 'mm') 输出结果：2007-6-1select round(to_date( 2007-5-7输出结果:2007-5-7select round(to date(' 2007-5-7输出结果：2007-5-8select round(to_date(* 2007-5-7输出结果

18、:2007-5-7 13:00:00select round (to. date ('2007-5-7输岀结果:2007-5-7 14:00:00fromfromfromll:10:23','yyyyfm-dd13:10:23','yyyy-mm-dd13:10:23*/yyyy-mm-dd13:45:23*/yyyy-mm-dddual;dual;dual;erom dial;111 124:mi:ss'),'dd') from dial;hh24:mt:ss，),，dd,) from dual;hh24:mi:ss')

19、,'hh24') from dual;iii124:mi:ss'),'川 124') from dual;4. 1. 16 trunc ()函数的使用概述：trunc(x, unit)函数用于对x进行截断。默认情况下,x被截断为当天的开始时间。unit是一个可 选字符串参数，它指明要取整的单元。例子 1： select trunc(to date('2007-5-7yyyy-mm-dd'yy)from dual; 输出结果：2007-1-1例子2： select trunc(to date('2007-8-7','

20、 yyyy-mm-dd'),' yy') from dual; 输出结果:2007-1-1例子3： select trunc (to_date ('2007-5-7','yyyy-mmdd'),'mm') from dual; 输出结果:2007-5-1例子4：例子5：select trunc(to datec 2007-5-171,' yyyy-mm-dd'),' mm') 输出结果:2007-5-1select trunc(to_date(，2007-5-7输出结果：from dual:

21、2007-5-711:10:23,/yyyy-mm-ddhh24:mi:ss，)/dd5) from dual;例了6：select trunc(to date('2007-5-7输岀结果:2007-5-7select trunc(to datec* 2007-5-7输出结果：2007-5-7 13:00:00select trunc(to_date(，2007-5-7输出结果：2007-5-7 13:00:0013:10:23','yyyy-mh-dd13:10:23 yyyy-mm-dd13:45:23，yyyy-mm-ddhii24:m1:ss，)/diy ) f

22、rom dial;dual;dual;4. 2. oracle常用运算符4. 2.1变量赋值sql 用法： declarefrtcustnamefrtcusttypenvarchar(200),nvarchar(20)selectfrtcustname - n abc，,©ertcusttype = n' dei；'printfrtcustname+frtcusttype打印结果：abcdeforacle用法：declarebeginv_frtcustname varchar2(200);v_frtcusttype varciiar2 (20);v frtcustna

23、me :=，abc，;-变屋赋值不是用“二”号，而是用“：二”v frtcusttype := 'def'dbms_output. put_line(v_frtcustname v_frtcusttype);或者肓接这样赋值：declare v_frtcustname varchar2(200):二'abc'v frtcusttype varciiar2 (20) :=:beg indbms_output. put丄ine(v_frtcustname v_frtcusttype);end;打印结果:abcdef说明：对于从数据库取出的数据赋值给某个变屋我们一般采

24、取：select colnami: into v 变量 from tablename where condition ;的方式来赋值。例子 1： select bl_type into v_bltype from oz_bk_header where job_order_1d = vjoborderid 但是这样的赋值方式存在隐患，当where的条件不成立时，查询出空行时，此时的赋值将有界常。 于是我们在翻译的时候为防止异常的发生我们-般采用以下两种赋值方式：方式1采用隐身游标来赋值(建议使用该方法):for rec in (select bl_type from oz_bk_header w

25、here job_order_id = vjoborderid)loopv_bl_type := rec. bl.type;end loop;方式2采用捕获开常的方式来赋值：beginselect bl_type into v_bltype from oz_bk_i1eader where job_order_id = vjoborderid;exception others whenv_bltype :='赋默认值'end;但对丁-聚集函数就可以不用使用上述的方式了。如：select count (1) into v_totai.records from tmp_ozjiea

26、der_session;可以不使用下列形式，因为聚集函数都能返回一彳了：for rec in (select count(1) as cn into v_totalrecords from tmp_oz_header_session)loopv count := rec. cn;end loop;4. 2.2字符串连接sql用法：select 'abc' + ' def'输出结果：abcdeforacle 用法：select ' abc' | | ' def' from dual或者select concat ('abc&

27、#39;,' def') from dual输出结果：abcdef4. 3. oracle pl/sql 基础4. 3. 1条件判断语句ifthen- elsifthenend if;declarenum integer := 3;beginif num < 0 thendbms_output. put_line('负数');else if num > 0 thendbms output. put linec 正数');elsedbms_output. put_line(，o');end if;end if;end;说明：当有多个的e

28、lse if的时候，为了使代码简洁、美观，易于浏览我们建议将else if替换 为:elsifo如以上示例可替换如下:declarenum integer := 3;beginif nim < 0 thendbms_output. put_line('负数');elsie num > 0 thendbms output. put linec 正数');elsedbms output. put line ('o');end if;end;4. 3. 2 循环语句 loop. exit. end loop;declarev_num integer

29、 := 1;v_sum integer := 0;beginloopv sum := v sum + v num;dbms output. pit line(v num);if v_nl'm = 4 thenexit;end if;dbms output. put line('+');v_num := v_num + 1;end loop;dbms_output. put_line('=');dbms output. put l1ne(v sum);输出结果：1+2+3+4 = 104. 3. 3 循环语句 loop. exit when . end l

30、oop;declarev.num integer := 1;v_sum integer :二 0;beg inloopv_sum := v_sum + v_num：dbms output. put l1ne(v num);extt when v num 二 4;dbms output. put line('+');v_nl'm := v_num + 1;end loop;dbhs_output. put_l1ne('=');dbms output. put ltne(v sum):end;输出结果:1+2+3+4 = 104. 3. 4 循环语句 whi

31、le. loop. end loop;declarev num integer := 1;v_sum integer := 0;beginwhile v_num <= 4 loopv sum :二 v sum + v num;dbms output. put line(v num);if v_num < 4 thendbms_output. put_line(' +');end if;v num :二 v num + 1;end loop;dbms_output. put_line(，二');dbms_output. put丄ine(v_sum);end;输

32、出结果:1+2+3+4 = 104. 3. 5 循环语句 for. in. loop. end loop;declarev_num integer:v sum integer :二 0;beg 1nfor v_nl'm in 1 . 4 loopv_sum := v_sum + v_num;dbms output. put line(v .num);if v num < 4 thendbms output. put lne('+');end if;end loop;dbms_output. put_l1ne('=');dbms output. pu

33、t ijne(v sum);end;输出结果:1+2+3+4 = 104. 3. 6 游标的使用1声明山于保存列值的变量2. 声明游标3. 打开游标4. 从游标中读取数记录5. 关闭游标例子：方法一(显式游标)：step1 : declare the variablesv_product_id varchar2(20);v_title_name varciiar2 (20);step 2: declare the cursorcursor cur product isselect product id from products order by product id;step 3:open

34、the cursorbeginopen cur product;loopstep 4:fetch the rows fom the cursorfetch cur product into v product id;exit when curroduct%notfound：beginselect tttle name into v tttle name from titles where product td = v product td;end;end loop;step 5:close the cursorclose cur product:方法二(显式游标)：step1 : declar

35、e the variables'productdvarciiar2 (20);v_t1tle_name varciiar2 (20);step 2: declare the cursorcursor cur product isselect product id from products order by product id;step 3:open the cursorbeginopen cur product;step 4:fetch the rows fom the clrsorfetch cur product into v product id;while(cur_prod

36、uct%found) loopbeginselect title name into v title name from titles where product id = v product id;fetch cur product into v product id;end;end loop;step 5:close the cursorclose cur product;end;说明：对比方法一和方法二，体会“notfound”和“found”的属性的用法。游标的另外两个属性为:isopem, %rowcount.这四个属性的意思为:%isopen判断游标是否被打开,如果打开%isope

37、n等于true,否则等于false；%found、wtf0und判断游标所在的行是否有效,如果有效,则%foundd等于true,否则等于false：%rowcount返回当前位置为止游标读取的记录行数。方法三(隐式游标)：v_title_name varchar2(20);beginfor rec in ( select product .id from products order by product_1d) loopbeginselect title kami: into v title name from titles where product id =rec. product_i

38、d;end;end loop;end;说明：利用for循环的功能，可以增强在游标访问记录的能力。当使用for循环时，可以不显式的 打开和关闭游标-一for循环会口动执行这些操作。4. 3.7 序列的使用1 基本语法(1) 创建序列命令create sequence user.sequence_name increment by nstart with nmaxvai.ue n | nomaxvai.ueminvalue n | nominvaluecycle|nocyclecache|nocacheorder|noorder；increment by：指处序列号z间的间隔，该值可为正的或负的整

39、数，但不可为0。序列为升序 忽略该子句时，缺省值为1。start with：指定生成的第一个序列号。在升序时，序列可从比域小值大的值开始，缺省值 为序列的戢小值。对于降序，序列可由比最大值小的值开始，缺省值为序列的最大值。maxvalue：nomaxvall'e：minvalue：nominvalie：cycle：cache：指定序列可生成的最大值。为升序指定最大值为1027,为降序指定最大值为-1。指定序列的最小值。为升序指定最小值为1。为降序指定最小值为-1026。循环使用，用到最大值再返。指定cache的值。如果指定cache值，oracle就可以预先在内存里面放置一些 sequ

40、ence,这样存取的快些。cache里面的取完后，oracle自动再取一组到cache.使用cache或许会跳号， 比如数据库突然不正常down；i (shutdown abort), cache中的sequence就 会丢失。所以可以在create sequence的时候用nocache防止这种情况。order：顺序使用序列值。(2) 更改序列altersequence user.sequence nameincrement by nmaxvalueminvaluen| nomaxvalue n | nohinvaluecycle|nocyclecacilelnocacileorder|no

41、order;修改序列可以：a. 修改未来序列值的增量。b. 设置或撤消垠小值或垠大值。c. 转变缓冲序列的数目。0.指定序列号是否是冇序。（3）删除序列drop sequence user. sequence_name;2. 序列的使用序列提供两个方法,nextval和currval。nextval：取序列的下一个值，-次nextval会増加一次sequence的值。currval：取序列的当前值。但是要注意的是：第一次nextval返冋的是初始值；随后的nextval会门动増加你定义的increment by值，然后返回增加后的值o currval总是返回当前sequence的值, 但是在第

42、-次nextval初始化z后才能使用currval,否则会出错。4. 4.存储过程翻译注意事项4. 4.1原sql的存储过程与oracle存储过程的代码格式比较sql定义存储过程方式：create procedure sp_name（参数1 in/out类熨（长度），一这里的长度是指像varciiar（20）这样的类型,而如果是int就不需要了参数2 in/oit类型（长度）.）asbegin变量声明declare 变暈1类型（k度）变量2类型（长度）一临时表声明 一变量初始化 select 变量 1 = ?变量2=?一游标声明一逻辑处理-异常处理endoracle定义存储过程方式：creat

43、e or replace procedure sp_name（s_参数11n/oit类型,-一类型不允许带长度,如不允许写varci1ar2 （20）, 只能写varchar2,是与sql不同的一个地方s参数2in/oct类型 rct1outpackage,frtnet. rcfcursor, -结果集输出统使川游标，只能为outrct2outpackageertnet. refcursoras一变最声明，oracle变量声明全部放在这个位置，是与sql不同的第二个地方v变量1类型（长度）；v_变量2类型（长度）；-游标初始化cursor cur_游标名 is select;begin 一变量

44、初始化v变量 1 := ?;v_变量2 := ?;begin逻辑处理exceptionwhen others then异常处理end;end sp name;4. 4.2原sql临时表中有自增字段的处理方式 原sql存储过程中临时表包含自增字段：create table #temptable(code 1nt identity ,cust id nvarchar(40)insert into #temptable(cust_id)select clst_id from sb_credit_cusotmergroup by cist.1d比如遇到以上冇h増字段的临时表，在oracle使用恢套子杏

45、询的方式解决:create global temporary table tmp oz search creditcodei nt,cust id nvarchar2 (40)on commit delete rows;insert into tmp_oz_search_credit(code, cust_id)select rownim, cist id erom(select cust idfrom sb credit cusotmergroup by cust_id);4. 4. 3 sql中使用top n语句在oracle中的处理方式原sql语句使用top n：select top 3

46、0 select a. bk panel td, a. bk panel no, a. flight no, a. carrier code, a. pod code,a. flight timefrom air_bk_panel as a-注虑：在oracle中给农沱义农别名时，不允许使丿ijaswhere a. fligiit_no like ' 1%'order by a. flight_time desc比如以上的sq 1语句，在orac 1 e使用嵌套子查询的方式解决：select bk panel id, bk panel no, flight no, carrie

47、r code, pod code, flight timefrom (select a. bk_panel_id, a. bk_panel_no, a. flight_no, a. carrier_code, a. pod_code,a. flight.time from air_bk_panel a 直接在表后面跟上表别名,不允许使用aswhere a. flight一no like ' 1%' order by fl1g11t_t1me desc)where roivncm <=30说明：想了解更多的rownlm的使用可以参见：htlp:/blog. csdn. nc

48、t/zhonyuchcun只/archivc/2008/05/15/2449954. aspx4. 4. 4 sql中使用update from语句在oracle中的处理方式原sql语句使用update from：update #airbkheaderset shipper 二 b. partner_id , consignee_name = b. consignee_namefrom#airbkiieader as ajoin a1r_bk_partner as b on a. job_order_1d = b. job_order_1dwhere a. to id = ' o

49、9; and b. partner type = ' sh'比如以上的sql语句，在oracle有一下两种解决方案：方案一：使用游标(不推荐使用)cursor gl_cl：rsor isselect r. row ii) , b. partner。，b. cons1gnee.namefrom tmp air arrange truck a, atr bk partner bwhere a. job order id = b. job order id and a. 10 id = ' o' and b. partner type = ' sh'o

50、pen g1_cursor;loopeetc1i gl.cursor into g1_row1d, gl.coll, g1.c0l2;exit when g1 cursor%notfound;beginupdate tem_air_bkheaderset shipper = g1 c0l1, consignee name = g1 c0l2where r0w1d = g1 r0w1d;end;end loop:close g1_cursor;方案二：使用子查询(翻译过程中尽最使用该方法，除非该方法行不通才考虑使用游标)update tmp air arrange truck a set (sh

51、ipper, consignee name)-(select b. partner td, b. consignee namefrom air bk partner bwhere b.job_order_id = a. job_order_idand b. partner_type = ' sh')where a. 10 .id = ' o' andexists(select 1 from atr bk partner c wherec. job order id = a. job order id and c. partner type = 'sh&#

52、39;);4. 4.5事物临时表和会话临时表的区别和注意事项可参见文章：oracle临时表的使用:http:/blog. csdn. net/yuzhic/drchive/2008/01/20/2054491. aspxorac 1 e数据库临时表管理心得：htlp:/vww. bit sen, com/oraclo/cxploilurc/200806/144601 2. hlml4. 4. 6 对原sql分页在oracle中处理原sql的处理方式：方式一：使用包含自增字段的临时表处理分页declarepagenl：m 1nt,pages1ze 1ntbegincreate table #a1

53、rbkheader (numint identity,job_order_idnvarchar(20),iiawb_nonvarchar(20),mawb nonvarc11ar(2o)nvarchar(20),nvarchar(20),create table tiatrbkheadertemp (job order idhawb nomawb_nonvarchar(20)insert #a1rbkiieadertemp(job order id, i1awb no, mawb no)select a. job order id, a. hawb no, a. mawb nofrom air

54、 bk header a left join vw airbk partner bon a. job_order_id = b. job_order_id order by a. job_order_idinsert #airbki1eader(job order id, hawb.no, mawb.no)select * from «atrbkheadertempset ©firstrecordnum = (©pagenum - 1) * ©pagesize + 1select nl；m, job_ordeilio. iiawb_no, mawb_no

55、from #airbki1eader where num >= ©firstrecordnum and num < ©firstrecordnum + ©pages1zeend方式二：使用top n处理分页select top ©page size job order id, hawb no, mawb no from air bk headerwhere job_order_id not in(select top (©pages1ze*(pageindex-1) job order _1d from air bk header)

56、oracle处理方式：方式一：使用临时表处理分页create globaltemporary table tmp_air_arrange_trcck(niminteger,job order idvarchar2(20),bk_novarchar2(20),mawb_novarchar2(20)on commit delete rows;insert into tmp air arrange truck(num, job order id, bk no, mawb no)select rownum, job order id, bk no, mawb no from(select a. job

57、 order id, a. bk no, a. mawb nofrom air bk header a left join vw airbk partner bon a. job_order_id = b. job_order_id order by a. job_order_id);一遇到order by或者groud by或者distinct最好像上而那样再嵌套一次，不建议直接写成：insert into tmp atr arrange truck(num, job order id, bk no, mawb no)select rownum, a. job order id, a. bk no, a. mawb nofrom air_bk_header a left join vw_airb