ORACLE ERP开发基础之Oracle数据库基础.docx
- 文档编号:7244079
- 上传时间:2023-05-11
- 格式:DOCX
- 页数:27
- 大小:94.70KB
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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- ORACLE ERP开发基础之Oracle数据库基础 ERP 开发 基础 数据库