ORACLE ERP开发基础之Oracle数据库基础文档格式.docx
《ORACLE ERP开发基础之Oracle数据库基础文档格式.docx》由会员分享，可在线阅读，更多相关《ORACLE ERP开发基础之Oracle数据库基础文档格式.docx（27页珍藏版）》请在冰豆网上搜索。
selectto_char（sysdate,'
hh24:
mm:
ss'
5.取日期时间的其他部分
DATEPART和DATENAME函数（第一个参数决定）
to_char函数第二个参数决定
下表补充说明SQL与ORACLE在取参数时的区别
6.当月最后一天
比较烦琐，先求当月的第一天，然后求得下月的第一天，最后减一得到当月最后一天。
selectLAST_DAY（sysdate）valuefromdual
7.本星期的某一天（比如星期日）
week函数
SELECTNext_day（sysdate,7）vauleFROMDUAL;
8.字符串转时间
可以直接转或者selectcast（'
2004-09-08'
asdatetime）value
O:
SELECTTo_date（'
2008-01-0522:
09:
38'
'
yyyy-mm-ddhh24-mi-ss'
）vauleFROMDUAL;
9.求两日期某一部分的差（比如秒）
selectdatediff（ss,getdate（）,getdate（）+12.3）value
直接用两个日期相减（比如d1-d2=12.3）
SELECT（d1-d2）*24*60*60vauleFROMDUAL;
10.根据差值求新的日期（比如分钟）
selectdateadd（mi,8,getdate（））value
SELECTsysdate+8/60/24vauleFROMDUAL;
11.当月第一天
selectdateadd（getdate,-day）
selecttrunc（sysdate,'
mm'
）fromdual;
随机取前10条不同的记录
Oracle有提供一个函数来实现取随机数：
DBMS_RANDOM
SELECTDBMS_RANDOM.VALUEFROMDUAL;
返回0--1之间的随机数，因为DBMS_RANDOM是默认使用时钟作为种子，来实现取随机数的。
select*from（select*fromhek_test_tborderbydbms_random.value（1,10））whererownum<
10
TRUNC函数
Oracle与SQLSERVER在日期比较方面有重大区别。
例：
含有日期+时间的字段BEGINDATE与仅含有日期的字段在比较时ENDDATE。
SQLSERVER:
BEGINDATE<
=ENDDATE
ORACLE:
TRUNC（BEGINDATE,‘DD‘）<
=ENDDATEORACLE必须先截断时间，然后再进行比较。
如果没有这样做，这将会是一个巨大的BUG。
修改表的一些常用语法
添加列：
altertablehek_test_headersaddcol_testnumber;
修改列：
altertablehek_test_linesmodifylitemvarchar（40）notnull;
删除列：
altertablehek_test_linesdropcolumnscol_test;
重命名列：
altertablehek_test_linesrenamecolumncol_testtocol_test2;
添加主键：
altertableHEK_TEST_LINESaddconstraintpk_testprimarykey（LINEID）;
添加外键：
altertablehek_test_linesaddconstraintfk_testforeignkey（hid）referenceshek_test_headers（hid）
失效主键：
altertablehek_test_linesdisableconstraintpk_test;
失效外键：
altertablehek_test_linesdisableconstraintfk_test;
删除主键：
altertablehek_test_linesdropconstraintpk_testcascade;
删除外键：
altertableHEK_TEST_LINESdropconstraintfk_test;
舍入函数
三个舍入函数：
round（）、floor（）、ceil（）
Round（）：
实现四舍五入，允许设置保留的位数，这个也最常用的四舍五入函数。
Floor（）：
实现取整。
一般的程序语言是整数除以整数，返回的仍是整数。
PL/SQL想得比较多。
Ceil（）：
实现近似值。
Ceil会直接近似取整，如果想保留小数，就要自己动手写个函数了。
select9/4fromdual;
selectCEIL（9/4）fromdual;
selectround（9/4,0）fromdual;
实现类似BREAK语句
在没有LOOP…ENDLOOP时，是不能使用EXIT的。
但可以通过GOTO语句实现。
declare
tinteger;
begin
t:
=&
t;
DBMS_OUTPUT.PUT_LINE（'
T='
||t）;
ift=1
thenDBMS_OUTPUT.PUT_LINE（'
Goto!
'
）;
gotoGOTOS;
else
NOGoto!
gotoNoGoto;
endif;
<
>
foriin1..10loop
DBMS_OUTPUT.put_line（'
i='
||i）;
endlooptest;
NULL;
end;
Oracle定时器
ORACLE9i及其以前版本，都是使用DBMS_JOB来实现任务调度。
10g官方推荐使用DBMS_SCHEDULER。
1.1JOB创建
1.1.1先创建一个存储过程
createorreplaceprocedureusp_test_pr
is
BEGINupdatet_testsettname='
test'
wheretid=110;
END;
1.1.2在pl/sqldeveloper中创建job
v_jobnumber;
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删除JOB
execDBMS_JOB.remove（JOB=>
&
job_number）;
--输入job_number
1.3查询所有的JOB
select*fromdba_jobsjorderbyj.JOBdesc
Over分析查询
Over函数，其实也可以转换成嵌套查询来实现。
准备测试数据
createtablet_test（tidint,tnamevarchar2（20）,tsalarynumber（8,2）,tdeptnoint,primarykey（tid））;
insertintot_testvalues（1,'
小王'
4500.21,3）;
insertintot_testvalues（2,'
小张'
4200,3）;
insertintot_testvalues（3,'
小K'
3000,3）;
insertintot_testvalues（4,'
小Q'
8500.5,4）;
insertintot_testvalues（5,'
小T'
1520.5,4）;
insertintot_testvalues（6,'
小丁'
3000,5）;
insertintot_testvalues（7,'
小李'
insertintot_testvalues（8,'
小KK'
;
SELECT*FROMT_TEST;
-----------------------------------------------------------------------------------------------
11小王4500.213
22小张4200.003
33小K3000.003
44小Q8500.504
55小T1520.504
66小丁3000.005
77小李3000.005
88小KK3000.005
--求工资占部门总工资额的比率
selecttname,tsalary,tsalary/sum（tsalary）over（partitionbytdeptno）perfromt_test
-------------------------------------------------------------------------------------------
1小王4500.210.203
2小张4200.000.0483
3小K3000.000.7488
4小Q8500.500.4684
5小T1520.500.5316
6小丁3000.000.3333
7小李3000.000.3333
8小KK3000.000.3333
--当然也可以不使用over，实现同样的效果
selecta.tname,a.tsalary,a.tsalary/b.ttlper,a.tdeptno
fromt_testa,（selecttdeptno,sum（tsalary）ttlfromt_testgroupbytdeptno）b
wherea.tdeptno=b.tdeptno
1小王4500.210.2033
2小张4200.000.04833
3小K3000.000.74883
4小Q8500.500.46844
5小T1520.500.53164
6小丁3000.000.33335
7小李3000.000.33335
8小KK3000.000.33335
--求工资排名
SELECTROWNUMser,TNAME,TSALARY,TDEPTNO
FROM（SELECT*FROMt_testorderbytsalarydesc）
--注意这样求出的排名，有点问题。
就是工资一样的没有处于第一排名，这是由rownum的性质决定。
----------------------------------------------------------------------------------------------------
1小Q8500.504
2小王4500.213
3小张4200.003
4小K3000.003
5小KK3000.005
6小李3000.005
7小丁3000.005
8小T1520.504
--要实现真正的排名，应该使用rank或者dense_rank
--rank（）和dense_rank（）的区别是：
--rank（）是跳跃排序，有两个第二名时接下来就是第四名
--dense_rank（）l是连续排序，有两个第二名时仍然跟着第三名
selectdense_rank（）over（orderbytsalarydesc）ser,tname,tsalary,tdeptnofromt_test
------------------------------------------------------------------------
3小张4200.0034小K3000.003
4小KK3000.005
4小李3000.005
4小丁3000.005
5小T1520.504
--上面是工资在全体部门的排名，如果要求部门排名的话。
selecttname,tsalary,tdeptno,dense_rank（）over（partitionbytdeptno
orderbytsalarydesc）ser
fromt_test
1小王4500.2131
2小张4200.0032
3小K3000.0033
4小Q8500.5041
5小T1520.5042
6小丁3000.0051
7小李3000.0051
8小KK3000.0051
--直接实现行汇总
selecttname,tsalary,tdeptno,sum（tsalary）over（partitionbynull）ttlfromt_test
如下：
1小王4500.21330721.21
2小张4200.00330721.21
3小K3000.00330721.21
4小Q8500.50430721.21
5小T1520.50430721.21
6小丁3000.00530721.21
7小李3000.00530721.21
8小KK3000.00530721.21
Oracle层次树查询
Oracle层次树是通过Connectby[条件]Startwith[条件]来实现。
这一功能非常好用，比如ERP中的BOM、HR中的组织架构，就算是这类的典型应用了。
不过，OracleEBS11i中好象没实现，BOM也没有使用树这种组件。
下面就做一个简单的MRP试算过程，来说明层次树的应用。
1.1建一个简单BOM表。
createtablehek_bom（master_idvarchar2（20）,master_namevarchar2（50）,sub_idvarchar（20）,sub_namevarchar（20））
1.2放入测试数据。
insertintohek_bomvalues（'
0001'
V1卡车'
10001'
V1发动机'
10002'
V1车架'
i
nsertintohek_bomvalues（'
10003'
V1车轮'
0002'
V2卡车'
10004'
V2车架'
0003'
V3卡车'
0004'
V4卡车'
10005'
V2发动机'
10006'
V1活塞'
10007'
V1火花器'
10008'
V1橡胶片'
10009'
V1螺丝'
1.3查询一下明细：
selectt.*fromhek_bomtforupdate
------------------------
10001V1卡车10001V1发动机
20001V1卡车10002V1车架
30001V1卡车10003V1车轮
40002V2卡车10001V1发动机
50002V2卡车10002V1车轮
60002V2卡车10004V2车架
70003V3卡车10004V1发动机
80004V4卡车10005V2发动机
910001V1发动机10006V1活塞
1010001V1发动机10007V1火花器
1110006V1活塞10008V1橡胶片
1210006V1活塞10009V1螺丝
1.4问题：
求V1螺丝料品有哪几层产品用到。
selectlevel,t.*fromhek_bomtconnectbypriort.master_id=t.sub_idstartwitht.sub_id='
--注意这条SQL语名的语法，connectbypriort.master_id=t.sub_id表示优先从子节点到父节点。
--startwitht.sub_id='
相当于wheret.sub_id='
---------------------------------------------
110006V1活塞10009V1螺丝
210001V1发动机10006V1活塞
30001V1卡车10001V1发动机
1.5问题：
求：
V1卡车的BOM结构：
selectlevel,t.*fromhek_bomtconnectbypriort.sub_id=t.master_idstartwitht.master_name='
--connectbypriort.sub_id=t.master_id表示优先从父节点查询到子节点。
---------------------------------------------------------------------------------------------
310006V1活塞10008V1橡胶片
410006V1活塞10009V1螺丝
510001V1发动机10007V1火花器
60001V1卡车10002V1车架
70001V1卡车10003V1车轮
―――――――――――――――――――――――――――――――――――――
通过这两个例子，已经可以很形象地说明connectby的典型应用了。
Mergeinto应用
Mergeinto适用于数据量非常大的表，做insert\update动作。
比起insertintoselect效率上要更高些。
当然mergeinto也提供了whenmatchedthen的条件规范。
基本语法：
Truncatetable
Truncatetable与delete*fromtable作用是一样，都是删除表中全部数据。
但Delete是与事务关联的，所以Truncatetable会快很多。
另外Oracle书上说，truncate会把highwatermark回归至0，当下一次再插入新资料时就会快一些。
这个功能有时间可以测试一下。
需
升级会员 