ORACLE ERP开发基础之Oracle数据库基础.docx
《ORACLE ERP开发基础之Oracle数据库基础.docx》由会员分享，可在线阅读，更多相关《ORACLE ERP开发基础之Oracle数据库基础.docx（27页珍藏版）》请在冰豆网上搜索。
ORACLEERP开发基础之Oracle数据库基础
ORACLEERP开发基础之Oracle数据库基础
常用函数
substr函数
Example:
SELECTsubstr（'teach',0,3）FROMDUAL
Effect:
tea
Example:
SELECTsubstr（'teach',3,3）FROMDUAL
Effect:
ach
Lpad/Rpad函数
Example:
SELECTLPAD（'TEACH',4）FROMDUAL
Effect:
teac
Example:
SELECTLPAD（'TEACH',10,'5'）FROMDUAL
Effect:
55555TEACH
日期函数
Oracle的日期函数比较丰富，不象SQLSERVER只有dateadd、datediff之类。
1.系统时间
SQL：
selectgetdate（）value
Oracle：
selectsysdatevaluefromdual
2.前后几日
都支持直接与整数相加减
3.求日期
SQL：
selectconvert（char（10）,getdate（）,20）value
Oracle：
selecttrunc（sysdate）valuefromdual
selectto_char（sysdate,'yyyy-mm-dd'）valuefromdual
4.求时间
SQL：
selectconvert（char（8）,getdate（）,108）value
Oracle：
selectto_char（sysdate,'hh24:
mm:
ss'）valuefromdual
5.取日期时间的其他部分
SQL：
DATEPART和DATENAME函数（第一个参数决定）
Oracle：
to_char函数第二个参数决定
下表补充说明SQL与ORACLE在取参数时的区别
6.当月最后一天
SQL：
比较烦琐，先求当月的第一天，然后求得下月的第一天，最后减一得到当月最后一天。
Oracle：
selectLAST_DAY（sysdate）valuefromdual
7.本星期的某一天（比如星期日）
SQL：
week函数
Oracle：
SELECTNext_day（sysdate,7）vauleFROMDUAL;
8.字符串转时间
SQL：
可以直接转或者selectcast（'2004-09-08'asdatetime）value
O:
SELECTTo_date（'2008-01-0522:
09:
38','yyyy-mm-ddhh24-mi-ss'）vauleFROMDUAL;
9.求两日期某一部分的差（比如秒）
SQL：
selectdatediff（ss,getdate（）,getdate（）+12.3）value
Oracle：
直接用两个日期相减（比如d1-d2=12.3）
SELECT（d1-d2）*24*60*60vauleFROMDUAL;
10.根据差值求新的日期（比如分钟）
SQL：
selectdateadd（mi,8,getdate（））value
Oracle：
SELECTsysdate+8/60/24vauleFROMDUAL;
11.当月第一天
SQL：
selectdateadd（getdate,-day）
O:
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
DBMS_OUTPUT.PUT_LINE（'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
declare
v_jobnumber;
begin
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;
end;
1.2删除JOB
execDBMS_JOB.remove（JOB=>&job_number）;--输入job_number
commit;
1.3查询所有的JOB
select*fromdba_jobsjorderbyj.JOBdesc
Over分析查询
Over函数，其实也可以转换成嵌套查询来实现。
准备测试数据
createtablet_test（tidint,tnamevarchar2（20）,tsalarynumber（8,2）,tdeptnoint,primarykey（tid））;
begin
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,'小李',3000,5）;
insertintot_testvalues（8,'小KK',3000,5）;
end;;
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.38462642978203
2小张4200.000.358967915960483
3小K3000.000.256405654257488
4小Q8500.500.848268635864684
5小T1520.500.151********5316
6小丁3000.000.333333333333333
7小李3000.000.333333333333333
8小KK3000.000.333333333333333
--当然也可以不使用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.384626429782033
2小张4200.000.3589679159604833
3小K3000.000.2564056542574883
4小Q8500.500.8482686358646844
5小T1520.500.1517313641353164
6小丁3000.000.3333333333333335
7小李3000.000.3333333333333335
8小KK3000.000.3333333333333335
--求工资排名
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
------------------------------------------------------------------------
1小Q8500.504
2小王4500.213
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放入测试数据。
begin
insertintohek_bomvalues（'0001','V1卡车','10001','V1发动机'）;
insertintohek_bomvalues（'0001','V1卡车','10002','V1车架'）;i
nsertintohek_bomvalues（'0001','V1卡车','10003','V1车轮'）;
insertintohek_bomvalues（'0002','V2卡车','10001','V1发动机'）;
insertintohek_bomvalues（'0002','V2卡车','10002','V1车轮'）;
insertintohek_bomvalues（'0002','V2卡车','10004','V2车架'）;
insertintohek_bomvalues（'0003','V3卡车','10004','V1发动机'）;
insertintohek_bomvalues（'0004','V4卡车','10005','V2发动机'）;
insertintohek_bomvalues（'10001','V1发动机','10006','V1活塞'）;
insertintohek_bomvalues（'10001','V1发动机','10007','V1火花器'）;
insertintohek_bomvalues（'10007','V1活塞','10008','V1橡胶片'）;
insertintohek_bomvalues（'10007','V1活塞','10009','V1螺丝'）;
end;
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='10009'
--注意这条SQL语名的语法，connectbypriort.master_id=t.sub_id表示优先从子节点到父节点。
--startwitht.sub_id='10009'相当于wheret.sub_id='10009'
---------------------------------------------
110006V1活塞10009V1螺丝
210001V1发动机10006V1活塞
30001V1卡车10001V1发动机
40002V2卡车10001V1发动机
1.5问题：
求：
V1卡车的BOM结构：
selectlevel,t.*fromhek_bomtconnectbypriort.sub_id=t.master_idstartwitht.master_name='V1卡车'
--connectbypriort.sub_id=t.master_id表示优先从父节点查询到子节点。
---------------------------------------------------------------------------------------------
10001V1卡车10001V1发动机
210001V1发动机10006V1活塞
310006V1活塞10008V1橡胶片
410006V1活塞10009V1螺丝
510001V1发动机10007V1火花器
60001V1卡车10002V1车架
70001V1卡车10003V1车轮
―――――――――――――――――――――――――――――――――――――
通过这两个例子，已经可以很形象地说明connectby的典型应用了。
Mergeinto应用
Mergeinto适用于数据量非常大的表，做insert\update动作。
比起insertintoselect效率上要更高些。
当然mergeinto也提供了whenmatchedthen的条件规范。
基本语法：
Trunca
升级会员 