ORACLE ERP开发基础之Oracle数据库基础文档格式.docx

1、select to_char（sysdate,hh24:mm:ss5.取日期时间的其他部分DATEPART 和 DATENAME 函数 （第一个参数决定）to_char函数 第二个参数决定下表补充说明SQL与ORACLE在取参数时的区别6.当月最后一天比较烦琐，先求当月的第一天，然后求得下月的第一天，最后减一得到当月最后一天。select LAST_DAY（sysdate） value from dual7.本星期的某一天（比如星期日）week函数SELECT Next_day（sysdate,7） vaule FROM DUAL;8.字符串转时间可以直接转或者select cast（2004

2、-09-08as datetime） valueO:SELECT To_date（2008-01-05 22:09:38,yyyy-mm-dd hh24-mi-ss） vaule FROM DUAL;9.求两日期某一部分的差（比如秒）select datediff（ss,getdate（）,getdate（）+12.3） value直接用两个日期相减（比如d1-d2=12.3）SELECT （d1-d2）*24*60*60 vaule FROM DUAL;10.根据差值求新的日期（比如分钟）select dateadd（mi,8,getdate（） valueSELECT sysdate+8/

3、60/24 vaule FROM DUAL;11.当月第一天select dateadd（getdate,-day） select trunc（sysdate,mm） from dual;随机取前10条不同的记录Oracle有提供一个函数来实现取随机数：DBMS_RANDOMSELECT DBMS_RANDOM.VALUE FROM DUAL;返回0-1之间的随机数，因为DBMS_RANDOM是默认使用时钟作为种子，来实现取随机数的。select * from（select * from hek_test_tb order by dbms_random.value（1,10） where ro

4、wnum10TRUNC函数Oracle与SQL SERVER在日期比较方面有重大区别。例：含有日期+时间的字段BEGINDATE与仅含有日期的字段在比较时ENDDATE。SQL SERVER:BEGINDATE=ENDDATEORACLE:TRUNC（BEGINDATE,DD）=ENDDATE ORACLE必须先截断时间，然后再进行比较。如果没有这样做，这将会是一个巨大的BUG。修改表的一些常用语法添加列：alter table hek_test_headers add col_test number;修改列：alter table hek_test_lines modify litem va

5、rchar（40） not null;删除列：alter table hek_test_lines drop columns col_test;重命名列：alter table hek_test_lines rename column col_test to col_test2;添加主键：alter table HEK_TEST_LINES add constraint pk_test primary key （LINEID）;添加外键：alter table hek_test_lines add constraint fk_test foreign key（hid） references h

6、ek_test_headers（hid）失效主键：alter table hek_test_lines disable constraint pk_test;失效外键：alter table hek_test_lines disable constraint fk_test;删除主键：alter table hek_test_lines drop constraint pk_test cascade;删除外键：alter table HEK_TEST_LINES drop constraint fk_test;舍入函数三个舍入函数：round（）、floor（）、ceil（）Round（）：实

7、现四舍五入，允许设置保留的位数，这个也最常用的四舍五入函数。Floor（）：实现取整。一般的程序语言是整数除以整数，返回的仍是整数。PL/SQL想得比较多。Ceil（）：实现近似值。Ceil会直接近似取整，如果想保留小数，就要自己动手写个函数了。select 9/4 from dual;select CEIL（9/4） from dual;select round（9/4,0） from dual;实现类似BREAK语句在没有LOOPEND LOOP时，是不能使用EXIT的。但可以通过GOTO语句实现。declaret integer;begint:=&t;DBMS_OUTPUT.PUT_LI

8、NE（T=|t）;if t=1then DBMS_OUTPUT.PUT_LINE（Goto!）;goto GOTOS;elseNO Goto!goto NoGoto;end if; for i in 1.10 loopDBMS_OUTPUT.put_line（i=|i）;end loop test; NULL;end;Oracle定时器ORACLE 9i及其以前版本，都是使用DBMS_JOB来实现任务调度。10g官方推荐使用DBMS_SCHEDULER。1.1 JOB创建1.1.1 先创建一个存储过程create or replace procedure usp_test_prisBEGIN

9、update t_test set tname=test where tid =110;END;1.1.2 在pl/sql developer中创建jobv_job number;sys.dbms_job.submit（job=v_job,what = usp_test_pr;,next_date = to_date（22-12-2008, dd-mm-yyyy）,interval =sysdate+1/1440 -每隔一分钟执行一次commit;1.2 删除JOBexec DBMS_JOB.remove（JOB=&job_number）;-输入job_number1.3 查询所有的JOBse

10、lect * from dba_jobs j order by j.JOB descOver分析查询Over函数，其实也可以转换成嵌套查询来实现。准备测试数据create table t_test（ tid int, tname varchar2（20）, tsalary number（8,2）, tdeptno int, primary key（tid） ）;insert into t_test values（1,小王,4500.21,3）;insert into t_test values（2,小张,4200,3）;insert into t_test values（3,小K,3000,3

11、）;insert into t_test values（4,小Q,8500.5,4）;insert into t_test values（5,小T,1520.5,4）;insert into t_test values（6,小丁,3000,5）;insert into t_test values（7,小李insert into t_test values（8,小KK;SELECT * FROM T_TEST;-1 1 小王 4500.21 32 2 小张 4200.00 33 3 小K 3000.00 34 4 小Q 8500.50 45 5 小T 1520.50 46 6 小丁 3000.0

12、0 57 7 小李 3000.00 58 8 小KK 3000.00 5-求工资占部门总工资额的比率select tname, tsalary,tsalary/sum（tsalary）over（partition by tdeptno） per from t_test-1 小王 4500.21 0.2032 小张 4200.00 0.04833 小K 3000.00 0.74884 小Q 8500.50 0.46845 小T 1520.50 0.53166 小丁 3000.00 0.33337 小李 3000.00 0.33338 小KK 3000.00 0.3333-当然也可以不使用over

13、，实现同样的效果select a.tname,a.tsalary,a.tsalary/b.ttl per,a.tdeptnofrom t_test a,（select tdeptno,sum（tsalary） ttl from t_test group by tdeptno） bwhere a.tdeptno=b.tdeptno1 小王 4500.21 0.203 32 小张 4200.00 0.0483 33 小K 3000.00 0.7488 34 小Q 8500.50 0.4684 45 小T 1520.50 0.5316 46 小丁 3000.00 0.3333 57 小李 3000.

14、00 0.3333 58 小KK 3000.00 0.3333 5-求工资排名SELECT ROWNUM ser ,TNAME,TSALARY,TDEPTNOFROM（SELECT * FROM t_test order by tsalary desc）-注意这样求出的排名，有点问题。就是工资一样的没有处于第一排名，这是由rownum的性质决定。-1 小Q 8500.50 42 小王 4500.21 33 小张 4200.00 34 小K 3000.00 35 小KK 3000.00 56 小李 3000.00 57 小丁 3000.00 58 小T 1520.50 4-要实现真正的排名，应该

15、使用rank或者dense_rank- rank（）和dense_rank（）的区别是：-rank（）是跳跃排序，有两个第二名时接下来就是第四名-dense_rank（）l是连续排序，有两个第二名时仍然跟着第三名select dense_rank（）over（order by tsalary desc） ser,tname,tsalary,tdeptno from t_test-3 小张 4200.00 3 4 小K 3000.00 34 小KK 3000.00 54 小李 3000.00 54 小丁 3000.00 55 小T 1520.50 4-上面是工资在全体部门的排名，如果要求部门排名

16、的话。select tname,tsalary,tdeptno,dense_rank（）over（partition by tdeptnoorder by tsalary desc ） serfrom t_test1 小王 4500.21 3 12 小张 4200.00 3 23 小K 3000.00 3 34 小Q 8500.50 4 15 小T 1520.50 4 26 小丁 3000.00 5 17 小李 3000.00 5 18 小KK 3000.00 5 1-直接实现行汇总select tname,tsalary,tdeptno,sum（tsalary）over（partition

17、by null） ttl from t_test如下：1 小王 4500.21 3 30721.212 小张 4200.00 3 30721.213 小K 3000.00 3 30721.214 小Q 8500.50 4 30721.215 小T 1520.50 4 30721.216 小丁 3000.00 5 30721.217 小李 3000.00 5 30721.218 小KK 3000.00 5 30721.21Oracle层次树查询Oracle层次树是通过Connect by 条件 Start with 条件 来实现。这一功能非常好用，比如ERP中的BOM、HR中的组织架构，就算是这

18、类的典型应用了。不过，Oracle EBS11i中好象没实现，BOM也没有使用树这种组件。下面就做一个简单的MRP试算过程，来说明层次树的应用。1.1 建一个简单BOM表。create table hek_bom（ master_id varchar2（20）, master_name varchar2（50）, sub_id varchar（20）, sub_name varchar（20） ）1.2 放入测试数据。insert into hek_bom values（0001V1卡车10001V1发动机10002V1车架 insert into hek_bom values（10003V1

19、车轮0002V2卡车10004V2车架0003V3卡车0004V4卡车10005V2发动机10006V1活塞10007V1火花器10008V1橡胶片10009V1螺丝1.3 查询一下明细：select t.* from hek_bom t for update-1 0001 V1卡车 10001 V1发动机2 0001 V1卡车 10002 V1车架3 0001 V1卡车 10003 V1车轮4 0002 V2卡车 10001 V1发动机5 0002 V2卡车 10002 V1车轮6 0002 V2卡车 10004 V2车架7 0003 V3卡车 10004 V1发动机8 0004 V4卡车

20、10005 V2发动机9 10001 V1发动机 10006 V1活塞10 10001 V1发动机 10007 V1火花器11 10006 V1活塞 10008 V1橡胶片12 10006 V1活塞 10009 V1螺丝1.4 问题：求V1螺丝料品有哪几层产品用到。select level,t.* from hek_bom t connect by prior t.master_id=t.sub_id start with t.sub_id=-注意这条SQL语名的语法，connect by prior t.master_id=t.sub_id表示优先从子节点到父节点。-start with t

21、.sub_id=相当于where t.sub_id=-1 10006 V1活塞 10009 V1螺丝2 10001 V1发动机 10006 V1活塞3 0001 V1卡车 10001 V1发动机1.5 问题：求：V1卡车的BOM结构：select level,t.* from hek_bom t connect by prior t.sub_id=t.master_id start with t.master_name=-connect by prior t.sub_id=t.master_id表示优先从父节点查询到子节点。-3 10006 V1活塞 10008 V1橡胶片4 10006 V1

22、活塞 10009 V1螺丝5 10001 V1发动机 10007 V1火花器6 0001 V1卡车 10002 V1车架7 0001 V1卡车 10003 V1车轮通过这两个例子，已经可以很形象地说明connect by 的典型应用了。Merge into应用Merge into适用于数据量非常大的表，做insertupdate动作。比起insert into select效率上要更高些。当然merge into也提供了when matched then的条件规范。基本语法：Truncate tableTruncate table与delete * from table作用是一样，都是删除表中全部数据。但Delete是与事务关联的，所以Truncate table会快很多。另外Oracle书上说，truncate 会把 highwatermark 回归至 0 ，当下一次再插入新资料时就会快一些。这个功能有时间可以测试一下。需