【IT168 技术文档】
ORACLEERP开发基础之前言
http://tech.it168.com/a2009/0427/274/000000274048.shtml
ORACLE ERP开发之OracleForms基础(一)Forms设置部分
http://tech.it168.com/a2009/0428/274/000000274178.shtml
ORACLEERP开发基础之OracleForms基础(二)FORMS代码部份
http://tech.it168.com/a2009/0428/274/000000274236.shtml
ORACLE ERP开发基础之Oracle Report基础
http://tech.it168.com/a2009/0429/274/000000274343.shtml
常用函数
substr函数
Example:SELECT substr('teach',0,3) FROM DUAL
Effect: tea
Example: SELECT substr('teach',3,3) FROM DUAL
Effect: ach
Lpad/Rpad函数
Example: SELECT LPAD('TEACH',4) FROM DUAL
Effect: teac
Example: SELECT LPAD('TEACH',10,'5') FROM DUAL
Effect: 55555TEACH
日期函数
Oracle的日期函数比较丰富，不象SQL SERVER只有dateadd、datediff之类。
1.系统时间
SQL：select getdate() value
Oracle：select sysdate value from dual
2.前后几日
都支持直接与整数相加减
3.求日期
SQL：select convert(char(10),getdate(),20) value
Oracle：select trunc(sysdate) value from dual
select to_char(sysdate,'yyyy-mm-dd') value from dual
4.求时间
SQL：select convert(char(8),getdate(),108) value
Oracle：select to_char(sysdate,'hh24:mm:ss') value from dual
5.取日期时间的其他部分
SQL：DATEPART 和 DATENAME 函数 (第一个参数决定)
Oracle：to_char函数 第二个参数决定
下表补充说明SQL与ORACLE在取参数时的区别
6.当月最后一天
SQL：比较烦琐，先求当月的第一天，然后求得下月的第一天，最后减一得到当月最后一天。
Oracle：select LAST_DAY(sysdate) value from dual
7.本星期的某一天(比如星期日)
SQL：week函数
Oracle：SELECT Next_day(sysdate,7) vaule FROM DUAL;
8.字符串转时间
SQL：可以直接转或者select cast('2004-09-08'as datetime) value
O:SELECT To_date('2008-01-05 22:09:38','yyyy-mm-dd hh24-mi-ss') vaule FROM DUAL;
9.求两日期某一部分的差(比如秒)
SQL：select datediff(ss,getdate(),getdate()+12.3) value
Oracle：直接用两个日期相减(比如d1-d2=12.3)
SELECT (d1-d2)*24*60*60 vaule FROM DUAL;
10.根据差值求新的日期(比如分钟)
SQL：select dateadd(mi,8,getdate()) value
Oracle：SELECT sysdate+8/60/24 vaule FROM DUAL;
11.当月第一天
SQL：select dateadd(getdate,-day)
O: select trunc(sysdate,'mm') from dual;
随机取前10条不同的记录
Oracle有提供一个函数来实现取随机数：DBMS_RANDOM
SELECT DBMS_RANDOM.VALUE FROM DUAL;
返回0--1之间的随机数，因为DBMS_RANDOM是默认使用时钟作为种子，来实现取随机数的。
select * from(select * from hek_test_tb order by dbms_random.value(1,10)) where rownum<10
TRUNC函数
Oracle与SQL SERVER在日期比较方面有重大区别。
例：含有日期+时间的字段BEGINDATE与仅含有日期的字段在比较时ENDDATE。
SQL SERVER:BEGINDATE<=ENDDATE
ORACLE:TRUNC(BEGINDATE,‘DD‘)<=ENDDATE ORACLE必须先截断时间，然后再进行比较。如果没有这样做，这将会是一个巨大的BUG。
修改表的一些常用语法
添加列：alter table hek_test_headers add col_test number;
修改列：alter table hek_test_lines modify litem varchar(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 hek_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()：实现四舍五入，允许设置保留的位数，这个也最常用的四舍五入函数。
Floor()：实现取整。一般的程序语言是整数除以整数，返回的仍是整数。PL/SQL想得比较多。
Ceil()：实现近似值。Ceil会直接近似取整，如果想保留小数，就要自己动手写个函数了。
select 9/4 from dual;
select CEIL(9/4) from dual;
select round(9/4,0) from dual;
实现类似BREAK语句
在没有LOOP…END LOOP时，是不能使用EXIT的。但可以通过GOTO语句实现。
declare
t integer;
begin
t:=&t;
DBMS_OUTPUT.PUT_LINE('T='||t);
if t=1
then DBMS_OUTPUT.PUT_LINE('Goto!');
goto GOTOS;
else
DBMS_OUTPUT.PUT_LINE('NO Goto!');
goto NoGoto;
end if;
<> for i in 1..10 loop
DBMS_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_pr
is
BEGIN update t_test set tname='test' where tid =110;
END;
1.1.2 在pl/sql developer中创建job
declare
v_job number;
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
exec DBMS_JOB.remove(JOB=>&job_number);--输入job_number
commit;
1.3 查询所有的JOB
select * from dba_jobs j order by j.JOB desc
Over分析查询
Over函数，其实也可以转换成嵌套查询来实现。
准备测试数据
create table t_test( tid int, tname varchar2(20), tsalary number(8,2), tdeptno int, primary key(tid) );
begin
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);
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,'小李',3000,5);
insert into t_test values(8,'小KK',3000,5);
end;;
SELECT * FROM T_TEST;
-----------------------------------------------------------------------------------------------
1 1 小王 4500.21 3
2 2 小张 4200.00 3
3 3 小K 3000.00 3
4 4 小Q 8500.50 4
5 5 小T 1520.50 4
6 6 小丁 3000.00 5
7 7 小李 3000.00 5
8 8 小KK 3000.00 5
--求工资占部门总工资额的比率
select tname, tsalary,tsalary/sum(tsalary)over(partition by tdeptno) per from t_test
-------------------------------------------------------------------------------------------
1 小王 4500.21 0.38462642978203
2 小张 4200.00 0.358967915960483
3 小K 3000.00 0.256405654257488
4 小Q 8500.50 0.848268635864684
5 小T 1520.50 0.151731364135316
6 小丁 3000.00 0.333333333333333
7 小李 3000.00 0.333333333333333
8 小KK 3000.00 0.333333333333333
--当然也可以不使用over，实现同样的效果
select a.tname,a.tsalary,a.tsalary/b.ttl per,a.tdeptno
from t_test a,(select tdeptno,sum(tsalary) ttl from t_test group by tdeptno) b
where a.tdeptno=b.tdeptno
1 小王 4500.21 0.38462642978203 3
2 小张 4200.00 0.358967915960483 3
3 小K 3000.00 0.256405654257488 3
4 小Q 8500.50 0.848268635864684 4
5 小T 1520.50 0.151731364135316 4
6 小丁 3000.00 0.333333333333333 5
7 小李 3000.00 0.333333333333333 5
8 小KK 3000.00 0.333333333333333 5
--求工资排名
SELECT ROWNUM ser ,TNAME,TSALARY,TDEPTNO
FROM(SELECT * FROM t_test order by tsalary desc)
--注意这样求出的排名，有点问题。就是工资一样的没有处于第一排名，这是由rownum的性质决定。
----------------------------------------------------------------------------------------------------
1 小Q 8500.50 4
2 小王 4500.21 3
3 小张 4200.00 3
4 小K 3000.00 3
5 小KK 3000.00 5
6 小李 3000.00 5
7 小丁 3000.00 5
8 小T 1520.50 4
--要实现真正的排名，应该使用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
------------------------------------------------------------------------
1 小Q 8500.50 4
2 小王 4500.21 3
3 小张 4200.00 3 4 小K 3000.00 3
4 小KK 3000.00 5
4 小李 3000.00 5
4 小丁 3000.00 5
5 小T 1520.50 4
--上面是工资在全体部门的排名，如果要求部门排名的话。
select tname,tsalary,tdeptno,dense_rank()over(partition by tdeptno
order by tsalary desc ) ser
from t_test
------------------------------------------------------------------------
1 小王 4500.21 3 1
2 小张 4200.00 3 2
3 小K 3000.00 3 3
4 小Q 8500.50 4 1
5 小T 1520.50 4 2
6 小丁 3000.00 5 1
7 小李 3000.00 5 1
8 小KK 3000.00 5 1
--直接实现行汇总
select tname,tsalary,tdeptno,sum(tsalary)over(partition by null) ttl from t_test
如下：
1 小王 4500.21 3 30721.21
2 小张 4200.00 3 30721.21
3 小K 3000.00 3 30721.21
4 小Q 8500.50 4 30721.21
5 小T 1520.50 4 30721.21
6 小丁 3000.00 5 30721.21
7 小李 3000.00 5 30721.21
8 小KK 3000.00 5 30721.21
Oracle层次树查询
Oracle层次树是通过Connect by [条件] Start with [条件] 来实现。这一功能非常好用，比如ERP中的BOM、HR中的组织架构，就算是这类的典型应用了。不过，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 放入测试数据。
begin
insert into hek_bom values('0001','V1卡车','10001','V1发动机');
insert into hek_bom values('0001','V1卡车','10002','V1车架'); i
nsert into hek_bom values('0001','V1卡车','10003','V1车轮');
insert into hek_bom values('0002','V2卡车','10001','V1发动机');
insert into hek_bom values('0002','V2卡车','10002','V1车轮');
insert into hek_bom values('0002','V2卡车','10004','V2车架');
insert into hek_bom values('0003','V3卡车','10004','V1发动机');
insert into hek_bom values('0004','V4卡车','10005','V2发动机');
insert into hek_bom values('10001','V1发动机','10006','V1活塞');
insert into hek_bom values('10001','V1发动机','10007','V1火花器');
insert into hek_bom values('10007','V1活塞','10008','V1橡胶片');
insert into hek_bom values('10007','V1活塞','10009','V1螺丝');
end;
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卡车 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='10009'
--注意这条SQL语名的语法，connect by prior t.master_id=t.sub_id表示优先从子节点到父节点。
--start with t.sub_id='10009'相当于where t.sub_id='10009'
---------------------------------------------
1 10006 V1活塞 10009 V1螺丝
2 10001 V1发动机 10006 V1活塞
3 0001 V1卡车 10001 V1发动机
4 0002 V2卡车 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='V1卡车'
--connect by prior t.sub_id=t.master_id表示优先从父节点查询到子节点。
---------------------------------------------------------------------------------------------
1 0001 V1卡车 10001 V1发动机
2 10001 V1发动机 10006 V1活塞
3 10006 V1活塞 10008 V1橡胶片
4 10006 V1活塞 10009 V1螺丝
5 10001 V1发动机 10007 V1火花器
6 0001 V1卡车 10002 V1车架
7 0001 V1卡车 10003 V1车轮
―――――――――――――――――――――――――――――――――――――
通过这两个例子，已经可以很形象地说明connect by 的典型应用了。
Merge into应用
Merge into适用于数据量非常大的表，做insert\update动作。比起insert into select效率上要更高些。当然merge into也提供了when matched then的条件规范。
基本语法：
Truncate table
Truncate table与delete * from table作用是一样，都是删除表中全部数据。但Delete是与事务关联的，所以Truncate table会快很多。另外Oracle书上说，truncate 会把 highwatermark 回归至 0 ，当下一次再插入新资料时就会快一些。这个功能有时间可以测试一下。需要注意的是Truncate table不是PL/SQL直接使用，必须使用动态SQL来执行，并且truncate table是无须commit语句的。
begin
execute immediate 'truncate table hek_table';
end;
取不重复的记录行
表结构：table
ID NAME
110 AA
120 AA
125 BB
126 BB
129 CC
取得如下记录：
110 AA
125 BB
129 CC
如果是MySQL实现这个比较简单。因为MySQL Group By支持多字段。
Select ID,NAME from TABLE group by NAME 但Oracle不支持此用法。
可以做到的，目前只有Select NAME,MAX(ID) FROM TABLE GROUP BY NAME EBSOracle
统计EBS表数量及Oracle概念
转到Oracle ERP有段时间了，突然被一个问题问倒。Oracle ERP有多少张表?统计表的数量首先想到数据字典，可是select了半天，发现对一些概念有点乱。特总结如下：
首先，要明白几个Oracle核心概念。这些概念2年前在读《Oracle8i初学者指南》中明白了一些，无耐后来转做JAVA开发，这些东西又还给作者了^_^。
―解释数据库、表空间、数据文件、表、数据的最好办法是想象一个装满东西的柜子。数据库就是柜子，柜中的抽屉是表空间，抽屉中的文件夹是数据文件，文件夹中的纸是表，写在纸上的信息就是数据‖。 这是《Oracle8i初学者指南》关于这些概念的理解。这样理解并没有错，但事实远非如此简单。
1.核心概念：数据库(Scheme)、数据文件、表空间(Tablespace)、表(table)、用户(user)。
1.1数据库是数据文件、控制文件、日志文件等组成的。属于物理文件的范畴。一个Scheme可以多个Tablespace。对于不同的应用，Oracle也推荐创建不同的表空间，以提高性能。例：DBA备份时可以按表空间进行备份。
1.2表空间是逻辑文件的范畴，Scheme是通过Tablespace进行管理的。这也是Oracle与SQL SERVER在设计理念上的最大区别。一个表空间一般只对应一个数据文件，但也可以有多个数据文件。例：给表空间增容。
1.3表也是逻辑文件的范畴。一个表空间可以多张表，一张表一般只能属于一个表空间。当然Oracle也有提供分区表，来实现一张表存放在多个表空间中。
1.4一张表只能属于一个用户。即用户与表是1对多的关系。Oracle是通过synonym来实现不同用户创建的表进行互访的。
1.5一个用户通过配额(Quota)可以拥有多个表空间，但只能有一个默认的表空间。 引申一下数据字典的概念，数据字典就是存放Oracle数据对象的表。一般是通过数据字典视图来查询，有三种视图:user_、all_、dba_。
2.查看全部表的数量
select count(*) from dba_tables -–因为有做过大量的二次开发，所以直接这样得到的数量是不准确的。
3.查看当前用户所有表的数量
select count(*) from all_tables
4.查看当前用户创建表的数量
select count(*) from user_tables
5.ebs11i对应的表空间是APPS_TS_TX_DATA，通过对表空间进行表的统计，算是比较准确的。
select count(*) from dba_tables dt where dt.tablespace_name='APPS_TS_TX_DATA'
得到EBS表的数量为“15997”。
Oracle的基本概念
EBS死锁之解决办法
1.查看死锁的进程
SELECT c.owner
,c.object_name ,
c.object_type
,fu.user_name locking_fnd_user_name
,fl.start_time locking_fnd_user_login_time
,vs.module
,vs.machine
,vs.osuser
,vlocked.oracle_username
,vs.SID
,vp.pid
,vp.spid AS os_process
,vs.serial#
,vs.status
,vs.saddr
,vs.audsid
,vs.process
FROM fnd_logins fl
,fnd_user fu
,v$locked_object vlocked
,v$process vp
,v$session vs
,dba_objects c
WHERE vs.SID = vlocked.session_id AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr AND vp.spid = fl.process_spid(+) AND vp.pid = fl.pid(+) AND fl.user_id = fu.user_id(+)
--AND c.object_name LIKE '%' || UPPER('&tab_name_leaveblank4all') || '%' AND NVL(vs.status ,'XX') != 'KILLED';
2.Kill掉进行程
alter system kill session 'SID,serial#';
--SID、serial# 可以从上面的查询结果中找到。
PL/SQL过程调用表示
PL/SQL过程调用支持两种参数表示方式，一种是位置表示法，如C、JAVA都是使用这种方式。第二种是名称表示法，这个恐怕只有PL/SQL使用，当然PL/SQL也是从Ada那边学来的。 名称表示法示例(注：仅限于过程，名称表示法不适用于函数。切记!)：
创建存储过程:
create or replace procedure test_proc(i_temp number,j_temp number,c_temp varchar)
is
t varchar(30);
begin t := i_temp || '' || j_temp || c_temp;
dbms_output.put_line(t);
end;
调用：
begin test_proc(j_temp=>10,i_temp => 60,c_temp => 'test');
end;
PL/SQL调用Java类
作为JAVA程序员，对ORACLE的javasource一直是想找个机会下手的。呵呵!风高月夜，此时正下手之机。Oracle有提供一个叫“外部例程”来实现与其他编程语言的访问，但ORACLE特别集成了JAVA。例如：可以将业务逻辑写在JAVA类中，通PL SQL进行调用，从而实现了业务逻辑层与数据库分离。
1.HelloWorld例子
1.0创建java source
create or replace and compile java source named JTest as
public class JTest
{
public static String entry(String s,int i)
{
return "First "+i+" hello"+s;
}
}
1.1创建调用function
create or replace function read_string (t in varchar2,i number)
return varchar2 is language java name ' JTest.entry(java.lang.String,int) return t';
--注：function的形参应与调用的JAVA类中方法的形参相同
1.2调用测试
SELECT read_string(' world',10) from dual
注意：在ORACLE中，JAVA SOURCE可以拥有非static方法，但PLSQ/包调用的方法必须是static的。
2.再来一个实用一点的例子(JDBC) 2.1创建JAVA类
create or replace and compile java source named testjdbc as
import java.io.IOException; import java.sql.Connection;
import java.sql.DriverManager; import java.sql.PreparedStatement; i
mport java.sql.ResultSet; import java.sql.SQLException;
/**
* @author jarwang
* @since 2008-6-12
* @version xx1.0
*/
public class TestJDBC{
public static String queryTable(){ String temp="";
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null; try{ conn = getConn();
pstmt = conn.prepareStatement("select hname from test ");
rs = pstmt.executeQuery();
while(rs.next()){ temp += rs.getString("hname");
} }catch(Exception ioex){ System.out.println("failure: "+ioex);
}finally{ closeConn(conn,pstmt,rs);
} return temp;
} public static void insertTable(String s){ Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = "insert into test (hname)values('"+s+"')";
try{ conn = getConn(); pstmt = conn.prepareStatement(sql);
pstmt.execute();
conn.commit();
}catch(Exception oex){ System.out.println(" has failure: "+oex);
try{
conn.rollback();
}catch(Exception ex){ ex.printStackTrace();
}
}finally{ try{ pstmt.close(); conn.close();
}catch(Exception ex){ ex.printStackTrace(); }
}
} public static Connection getConn(){ Connection conn = null;
String user = "";
String pwd = "";
String url = "jdbc:oracle:thin:@ip:sid"; t
ry{ Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(url,user,pwd);
conn.setAutoCommit(false);
System.out.println(" OPEN DATABASE SUCCESS ");
}catch(SQLException ex){ System.out.println(" OPEN DATABASE FAILURE: "+ex);
}catch(Exception e){ System.out.println("JDBC CLASS LOADE FAILURE: "+e);
} return conn;
} public static void closeConn(Connection conn,PreparedStatement pstmt,ResultSet rs){ try{ rs.close();
pstmt.close();
conn.close();
}catch(Exception e){ e.printStackTrace();
}
}
}
create or replace package TestJDBC is -- Author
2.2创建PL/SQL包
create or replace package TestJDBC is
-- Author : JARWANG
-- Created : 2008-6-12 10:24:51
-- Purpose : TestJDBC
procedure insert_table(t varchar2);
function query_table return varchar2;
end TestJDBC;
create or replace package body TestJDBC is
procedure insert_table(t varchar2) is
language java name 'TestJDBC.insertTable(java.lang.String)';
function query_table return varchar2 is language java name 'TestJDBC.queryTable() return String';
end TestJDBC;
2.3测试调用
declare t varchar2(3000);
begin TestJDBC.insert_table('JDBC例子');
t := TestJDBC.query_table();
dbms_output.put_line('t='||t);
declare t varchar2(3000);
end;
3.调用含有外部的jar的JAVA类
3.1在ORACLE中加载JAR包。
3.1.1上传JAVA类
上传jar包至ORACLE服务器。例：/data/test/test.jar
3.1.2注册JAVA类
CALL DBMS_JAVA.loadjava('/data/test/test.jar');
3.1.3查询是否注册成功
Select
OBJECT_NAME, OBJECT_TYPE, STATUS, SYS.DBMS_JAVA.LONGNAME(OBJECT_NAME) LONGNAME, LAST_DDL_TIME, OBJECT_ID, uo.created
from sys.user_objects uo
where object_type in
('JAVA CLASS', 'JAVA SOURCE', 'JAVA RESOURCE', 'JAVA DATA')
order by uo.created desc
PL/SQL操作EXCEL
PL/SQL操作EXCEL有多种方法，但都不理想。比较了一下，还是通过JAVA的方法比较适用。
1. 使用UTL_FILE包
declare l_file utl_file.file_type;
BEGIN
l_file :=utl_file.fopen('U_FIEL','test1.xls','w');
utl_file.put_line(l_file,'jobs表导出数据');
utl_file.fflush(l_file);
end;
说明：此方法操作文本尚可，对于EXCEL这种COM文件并不可行。网络大多数所谓的PL/SQL操作EXCEL， 也是使用cvs这种简单的格式。如果要读入稍微复杂点的EXCLE模板文件，utl_file就挂掉了。
2. 使用OLE2
declare
application OLE2.OBJ_TYPE;
workbooks OLE2.Obj_Type;
workbook OLE2.Obj_Type;
worksheets OLE2.Obj_Type;
worksheet OLE2.Obj_Type;
args OLE2.List_Type;
cell OLE2.Obj_Type;
begin
application:=OLE2.CREATE_OBJ('Excel.Application');
OLE2.Set_Property(application,'Visible','True');
workbooks := OLE2.GET_OBJ_PROPERTY(application,'Workbooks');
workbook:=OLE2.INVOKE_OBJ(workbooks,'Add');
worksheets := OLE2.GET_OBJ_PROPERTY(workbook, 'Worksheets');
worksheet:=OLE2.INVOKE_OBJ(worksheets,'Add');
args:=OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, 4);
OLE2.ADD_ARG(args, 2);
cell := OLE2.GET_OBJ_PROPERTY(worksheet,'CELLS',args);
OLE2.DESTROY_ARGLIST(args); OLE2.Set_Property(cell, 'Value', 'Hello Excel!');
args:=OLE2.CREATE_ARGLIST; OLE2.ADD_ARG(args, 5);
OLE2.ADD_ARG(args, 3);
cell := OLE2.GET_OBJ_PROPERTY(worksheet,'CELLS',args);
OLE2.DESTROY_ARGLIST(args);
OLE2.Set_Property(cell, 'Value', 'Hello Excel!');
exception when others then FND_MESSAGE.DEBUG('ERROR:'||SQLERRM);
end;
说明：Object Link and embed，有学过VB/VC的同学们应该很熟悉。可惜ORACLE本身并不支持OLE2，OLE2是ORACLE FORMS集成的一个OLE接口。所以限制此方法的使用范围。另外更重要的OLE2无法在B/S架构中使用，只能在传统的FORM C/S程序中使用。
3. 使用ORACLE COM函数 这种方法就与通过extproc来实现与excle通信，这种方法直接与window api打交道。大致的的代码是：ordcom.CreateObject(‘Excel.Application‘, 0, servername,applicationToken); 测试了一下，似乎只能要求ORACLE的宿主系统必须是window。这也就有严重的局限性了。
4. 使用JAVA存储过程(通过POI包，这也是本文的重点)
4.1至http://poi.apache.org 下载POI包，注意与ORACLE JVM版本匹配(旧版本的POI下载：
http://archive.apache.org/dist/jakarta/poi/release/bin/)。至ORACL官网下载JDBC驱动，这一步不是必须的，一般安装ORACLE时，都有内置了JDBC包。
4.2 在ORACLE注册POI。
4.2.1先将POI包上传至ORACLE服务器。目录为：$ORACLE_HOME/javavm/lib ，例：/data/book/bookdb/9.2.0/javavm/lib，必须是javavm/lib这个目录或其下层目录。
4.2.2使用DBMS_JAVA注册
call dbms_java.loadjava('-r -v -definer -g public /data/book/bookdb/9.2.0/javavm/lib/poi-2.5.1-final-20040804.jar');
call dbms_java.loadjava('-r -v -definer -g public /data/book/bookdb/9.2.0/javavm/lib/poi-contrib-2.5.1-final-20040804.jar');
call dbms_java.loadjava('-r -v -definer -g public /data/book/bookdb/9.2.0/javavm/lib/poi-scratchpad-2.5.1-final-20040804.jar');
注：这个问题折腾了我很久，注册JAR包有两种方法。1.使用DBMS_JAVA，这种方法要自行上传jar包到服务器上。2.是使用loadjava。这也是能google到最多的方法，但loadjava在ORACLE9i以后就不单独提供了，而是集成在JDeveloper中，而可恶的JDeveloper是有版本兼容性的问题。
4.2.3查询一下是否注册成功(注：如果注册成功，STATUS应该为VALID)。
Select OBJECT_NAME, OBJECT_TYPE, STATUS, SYS.DBMS_JAVA.LONGNAME(OBJECT_NAME) LONGNAME, LAST_DDL_TIME, OBJECT_ID, uo.created
from sys.user_objects uo
where object_type in
('JAVA CLASS', 'JAVA SOURCE', 'JAVA RESOURCE', 'JAVA DATA')
and uo.object_name like '%poi%'
4.3创建JAVA SOURCE
create or replace and compile java source named testjdbc as
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.*;
import java.io.*;
public class TestJDBC{
public static void exportExcel(){
try{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("new sheet");
wb.setSheetName( 0, "SomeUnicodeName", HSSFWorkbook.ENCODING_UTF_16 );
HSSFRow r = sheet.createRow( 0 );
HSSFCell c = r.createCell( (short)0 );
c.setCellValue("X中国");
// Write the output to a file FileOutputStream fileOut = new FileOutputStream("/data/book/k.xls");
wb.write(fileOut);
fileOut.close();
}catch(Exception e){
System.out.println("poi error :"+e);
e.printStackTrace();
}
}
}
4.4创建调用的PL/SQL包
create or replace package TestJDBC is
procedure exportExcel;
procedure insert_table(t varchar2);
function query_table return varchar2;
end TestJDBC;
create or replace package body TestJDBC is
procedure exportExcel is
language java name 'TestJDBC.exportExcel()';
end TestJDBC;
4.5测试PL/SQL块
begin
testjdbc.exportExcel;
end;
5. 使用其他编程语言操作 说明：此方法完全脱离了ORACLE，已超出了ORACLE的范围，本文不讨论。
PL/SQL异常机制
Exception，一些中文教材译为例外，我认为这种翻译很失水准。Exception大多数的程序语言都这个捕获机制，但基本都是译为异常。为什么在PL/SQL中就搞成“例外”.
一般的PL程序员都这样写异常处理：
Declare ……
Begin ……
Exception
When others
then Raise_application_error(-20000,‘has an error‘||sqlerrm);
End;
ORACLE有提供两个函数来实现捕获异常信息，SQLCODE：错误代码、SQLERRM：错误描述。事实上，上述写法主要是方便，也没有根本性的错误。但PL/SQL与JAVA相似，异常捕获也是有层次的。OTHERS会捕获到ORACLE全部的异常，这对于大型数据库而言，也是会影响到效率的。
实现自定义异常。
Declare
i number:=1;
Cu_ex Exception;
Begin
insert into test_table values(2,22);
这样如果发生自定异常，就不会捕获ORACLE的全部异常了。在PL/SQL如果没有定义异常，那么就相当于JAVA中抛出异常THROWS，抛出的异常由调用该块的PL/SQL程序负责处理。 另外，异常与事务，ORACLE的书上都只说异常是不会中止事务。但拿上面那段代码去测试，就会异常发生时事务是有回滚的。那么是否说明异常不会中止事务的说法不正确的? 再测试下面这段代码，异常就不会中止事务了。
BEGIN
Declare i number:=1;
Cu_ex Exception;
Begin insert into test_table values(2,22);
If (i=1) then Raise Cu_ex;
End if;
DBMS_OUTPUT.put_line('TEST'); commit;
Exception When Cu_ex then Raise_application_error(-20000,'has a customer error'||sqlerrm);
When others then Raise_application_error(-20001,'has an error'||sqlerrm);
End;
EXCEPTION WHEN OTHERS THEN
NULL;
END;
原因：Raise_application_error(-20000,'has a customer error'||sqlerrm)这段语句会再次抛出异常，但在外层块中将这个异常捕获到。所以异常不会传回调用环境，事务也就不会由中止(注意外层块中是null)。
总结：异常一般是不会中止事务的，但如果PL/SQL块将异常抛出，则事务会回传给调用环境，由服务器自动进行事务中止操作。
PL/SQL面向对象编程
自转到ORACLE开始，就开始对ORACLE OOP有非常高的热情，于是写了一个DEMO。先从创建TYPE开始，TYPE与PACKAGE非常相似。TYPE与JAVA中的CLASS可以等同。
1、 创建TYPE规范
create or replace type hek_send_mail_obj as object( mail_host varchar2(20),
mail_port integer,
member function test_f(i number)
return varchar2 )instantiable not final;
2、创建TYPE主体
create or replace type body hek_send_mail_obj as
member function test_f(i number) return varchar2 is
begin
return 'success visit ' ||i||' ' || self.mail_host||' '||self.mail_port;
end;
end;
3、进行调用
Declare
--有多少个数据成员，在实例化TYPE时，必须都初始化，这一点跟JAVA的构造器类似，只是JAVA没有强制要求。
t hek_send_mail_obj:= hek_send_mail_obj(' 192.168.1.110',44);
begin
--调用TYPE的方法test_f dbms_output.put_line(t.test_f(1));
end;
注：上面创建的TYPE都是临时对象，随着块的结束，对象生命也结束了。下面将会说明如何创建持久对象。
4、持久化TYPE(也就是创建对象表)
create table hek_send_mail_tbj of hek_send_mail_obj;
5、在对象表中INSERT些数据
insert into hek_send_mail_tbj values('192.168.210.232',25);
insert into hek_send_mail_tbj values('192.168.210.232',80);
insert into hek_send_mail_tbj values('192.168.210.232',35);
6、进行调用
select tb.mail_host,tb.mail_port,tb.test_f(2) from hek_send_mail_tbj tb
注：①这边我们可以直接象关系表一样使用对象表，也不必进行初始化。ORACLE会自动初始化对象。 ②TYPE不能使用PACKAGE的对象等等，如：UTL_SMTP。
PL/SQL实现字符串转成数组
这个功能在JAVA中比较简单，String有提供一个split的方法实现此功能。在PL/SQL其实也不难，用substr与instr联合使用就可以实现。
function varchar2_to_array(ls varchar2) return list_table is
Type list_table is table of varchar2(200);
tl list_table := list_table(NULL);
i integer :=0;
j integer :=0;
v_ls varchar2(1000);
begin i := instr(ls,',');
if i=0 then tl(1) := ls; goto ex; else v_ls :=ls;
end if;
while i>0 loop tl(j) := substr(v_ls,1,i-1); v_ls := substr(v_ls,i+1,length(v_ls));
i := instr(v_ls,',');
tl.extend; if i=0 then j := j+1;
tl(j) := v_ls; goto ex;
end if;
end loop;
<> return tl;end varchar2_to_array;
Oracle的优化器
跟DBA在随聊时，提到一个概念CBO，觉得很新奇。追问之下，得到下面这篇文章。
Oracle的优化器有两种优化方式,即基于规则的优化方式(Rule-Based Optimization，简称为RBO)和基于代价的优化方式(Cost-Based Optimization，简称为CBO)，在Oracle8及以后的版本,Oracle强列推荐用CBO的方式
RBO方式：优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的，当一个where子句中的一列有索引时去走索引。
CBO方式：它是看语句的代价(Cost),这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小、有少行、每行的长度等信息。这些统计信息起初在库内是没有的，是做analyze后才出现的，很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因些应及时更新这些信息。
注意：走索引不一定就是优的，比如一个表只有两行数据，一次IO就可以完成全表的检索,而此时走索引时则需要两次IO,这时全表扫描(full table scan)是最好
优化模式包括Rule、Choose、First rows、All rows四种方式：
Rule：基于规则的方式。
Choolse：默认的情况下Oracle用的便是这种方式。指的是当一个表或或索引有统计信息，则走CBO的方式，如果表或索引没统计信息，表又不是特别的小，而且相应的列有索引时，那么就走索引，走RBO的方式。
First Rows：它与Choose方式是类似的，所不同的是当一个表有统计信息时，它将是以最快的方式返回查询的最先的几行，从总体上减少了响应时间。 All Rows:也就是我们所说的Cost的方式，当一个表有统计信息时，它将以最快的方式返回表的所有的行，从总体上提高查询的吞吐量。没有统计信息则走RBO的方式。
设定选用哪种优化模式：
A.Instance级别 通过在initSID.ora文件中设定OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS如果没设定OPTIMIZER_MODE参数则默认用的是Choose方式。
B.Sessions级别 通过ALTER SESSION SET OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS来设定。
C.语句级别用Hint(/*+ ... */)来设定
为什么表的某个字段明明有索引，但执行计划却不走索引?
1、优化模式是all_rows的方式
2、表作过analyze，有统计信息
3、表很小，上文提到过的，Oracle的优化器认为不值得走索引。
PowerDesigner
PowerDesigner一款非常好的数据库建模工具。。如果要用UML之类面向对象的东东，可以使用Rational Rose。当然也可以考虑直接使用JDeveloper10g以上版本，UML工具在JDeveloper已经有集成了。只是在ORACLE ERP很少用到面向对象，所以首推powerdesigner。PowerDesigner功能比Oracle Designer强大多了。
1.从代码反向生成E-R视图
2.
