ORACLE ERP开发基础之Oracle数据库基础文档格式.docx
- 文档编号:18431695
- 上传时间:2022-12-16
- 格式:DOCX
- 页数:27
- 大小:94.66KB
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,当下一次再插入新资料时就会快一些。
这个功能有时间可以测试一下。
需
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- ORACLE ERP开发基础之Oracle数据库基础 ERP 开发 基础 数据库