Oracle Ebs 开发心得

技术心得
一、SQL查询：
1、”列出同部门中工资高于1000的员工数量超过2人的部门，显示部门名字、地区名称”.查询语句如下：
select distinct dept.department_name,loc.city
from employees emp,departments dept,locations loc
where emp.department_id=dept.department_id
and dept.location_id=loc.location_id
and EXISTS(
select1
from employees emp2
where emp2.department_id=emp.department_id
and emp2.salary>1000
group by emp2.department_id
having count(emp2.department_id)>2
)
通常我们只需要对记录逐行的筛选，对于分组数据我们只能使用分组语句avg、max等,也就是说如果你想在select中得到这个属性那么“它们“也必须出现在group by中或者你只想得到一个统计数据.回顾上面的例子,我们也可以用以下语句完成:
select dept.department_name,loc.city,count(*)
from employees emp,departments dept,locations loc
where emp.department_id=dept.department_id
and dept.location_id=loc.location_id
and emp.salary>1000
group by dept.department_name,loc.city
having count(*) > 2
对于group by来说每一条emp.department_id必对应唯一dept.department_id、dept.department_name因此不论group by department_id还是group by department_name,loc.city达到的效果是一样的
2、用一条语句查询出scott.emp表中每个部门工资前三位的数据:
select department_id,max(salary) max_salary,max(decode (rank,2,salary,salary))mid_salary,min(salary) min_salary
from
(
select department_id,salary,rank from
(select emp.department_id,emp.employee_id,emp.salary,row_number()over(partition by emp.department_id order by emp.salary) as rank
from employees emp) E
where E.rank<=3
)
group by department_id
rownumber()over(patition by column1 order by column2),表示以column1分组对column2排序,row_number可用于筛选重复项.
3、哪些员工跟Den(FIRST_NAME)、Rephaely(Last_Name)不在同一个部门.
此处可能存在没有部门的员工，应该用No Exists筛选deptno与该员工不等的记录.若要求空值可使用NO EXISTS若不要求空值可用EXISTS,尽量用EXISTS取代IN、ANY、ALL等操作(可提高性能),注意空值的处理!!!
4、在多表连接查询中，子查询最多只可嵌套一层否则Oracle无法识别
5、for handle in:游标
二、PL/SQl存储过程
1、游标的使用:
显式游标的使用分为四步,声明、打开、循环、关闭.打开游标：就是执行游标所对应的SELECT 语句，将其查询结果放入工作区，并且指针指向工作区的首部，标识结果集合。

如果游标查询语句中带有FOR UPDATE选项，OPEN 语句还将锁定数据库表中游标结果集合对应的数据行。

如下所示:
DECLARE
CURSOR c4(dept_id NUMBER, j_id VARCHAR2)
IS
SELECT first_name f_name, hire_date FROM employees
WHERE department_id = dept_id AND job_id = j_id;
--声明游标定义记录变量，比声明记录类型变量要方便，不容易出错
v_emp_record c4%ROWTYPE;
BEGIN
-- OPEN c4(90, 'AD_VP');
/* LOOP
FETCH c4 INTO v_emp_record;
IF c4%FOUND THEN
DBMS_OUTPUT.PUT_LINE(v_emp_record.f_name||'的雇佣日期是'
||v_emp_record.hire_date);
ELSE
DBMS_OUTPUT.PUT_LINE('已经处理完结果集了');
EXIT;
END IF;
END LOOP;*/
/* CLOSE c4; ---关闭游标*/
FOR c1 IN c4(90,'AD_VP') LOOP
v_emp_record.f_name := c1.f_name;
v_emp_record.hire_date := c1.hire_date;
DBMS_OUTPUT.put_line(c1.f_name||'的雇佣日期是'||c1.hire_date);
END LOOP;
END;
以FOR c1 IN c_cursor使用游标，c1会自动遍历每行记录,不用像显式游标一样打开游标后在循环中使用FETCH将表征多行记录的游标的值传递出来,FOR语句相当于OPEN与LOOP、FETCH的综合使用,且不必人为的关闭.
2、隐式游标的处理:
显式游标主要是用于对查询语句的处理，尤其是在查询结果为多条记录的情况下；而对于非查询语句，如修改、删除操作，则由ORACLE 系统自动地为这些操作设置游标并创建其工作区.
SQL%ISOPEN
FALSE FALSE FALSE FALSE SQL%FOUND
TRUE 有结果 成功 成功 SQL%FOUND
FALSE 没结果 失败 失败
SQL%NOTFUOND
TRUE 没结果 失败 失败 SQL%NOTFOUND
FALSE 有结果 成功 失败 SQL%ROWCOUNT 返回行
数，只为
插入的行数
修改的行数 删除的行数 3、 异常处理:
分类:
(1)、系统预定义异常,直接引用异常名，并处理即可
(2)、非预定义的异常处理:将定义好的异常情况与标准的Oracle 错误联系起来,使用EXCEPTION_INIT 语,PRAGMA EXCEPTION_INIT(<异常情况>,<错误代码>).
(3)、用户自定义的异常处理:无错误代码，需要在本程序块完成捕捉与处理.
DECLARE
v_empno employees.employee_id %TYPE :=&empno;
no_result EXCEPTION;
BEGIN
UPDATE employees SET salary = salary +100 WHERE employee_id = v_empno;
IF SQL %NOTFOUND THEN
RAISE no_result;
END IF ;
EXCEPTION
WHEN no_result THEN
DBMS_OUTPUT.PUT_LINE('你的数据更新语句失败了!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE ||'---'||SQLERRM);
END ;
(4)、用户定义的异常处理, RAISE_APPLICATION_ERROR(error_number,error_message,[keep_errors] ),
重新定义异常错误消息，为应用程序提供了一种与Oracle 交互的方法，这里的error_number 是从-20,000到-20,999之间的参数.可在程序块中自定义异常，并捕捉在其他函数或存储过程中RAISE_APPLICATION_ERROR 抛出的异常，与Oracle 交互.
4、PRAGMA AUTONOMOUS_TRANSACTION
ORACLE8i 可以支持事务处理中的事务处理的概念．这种子事务处理可以完成它自己的工作，独立于父事务处理进行提交或者回滚．通过使用这种方法，开发者就能够这样的过程，无论父事务处理是提交还是回滚，它都可以成功执行．
(1)使用自动事务处理:
DROP TABLE logtable;
CREATE TABLE logtable(
Username varchar2(20),
Dassate_time date,
Mege varchar2(60)
);
CREATE TABLE temp_table(N number);
CREATE OR REPLACE PROCEDURE log_message(p_message varchar2)
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO logtable VALUES(user,sysdate,p_message);
COMMIT;
END log_message;
BEGIN
Log_message('About to insert into temp_table');
INSERT INTO temp_table VALUES(1);
LOG_message('Rollback to insert into temp_table');
ROLLBACK;
END;
select * from logtable;
select * from temp_table(查询结果无数据)
(2)不使用自动事务处理:
CREATE OR REPLACE PROCEDURE log_message(p_message varchar2)
AS
BEGIN
INSERT INTO logtable VALUES(user,sysdate,p_message);
COMMIT;
END log_message;
(select * from temp_table查询结果有数据)
三、报表开发:
流程:
1、产生数据源:
以 PL/SQL或SQl获取数据源，并用Reports Developer工具创建数据模型并分组.
(Reports Builder生成rdf文件，或直接由PL/SQL程序包直接输出xml、html等文件格式)
2、注册并发程序，输出的格式为XML.这样服务器上便保存了数据定义的源文件.
3、设计rtf报表模板，调整模板布局
4、注册数据源和模板，数据源的代码需与并发请求的代码一致
实例:
数量帐报表-进出存明细表
1、编写报表前需了解以下五个表结构:CUX_INV_LINES_ALL、CUX_INV_HEADERS_ALL、CUX_INV_ONHAND_SHIPS_AL、CUX_INV_MATERIAL_ALL、
GL_CODE_COMBINATIONS,其中行表CUX_INV_LINES_ALL存储了报表所需的主要数据,为查询语句的主表，从CUX_INV_ONHAND_SHIPS_ALL表中取出期初数据（保存为距今最近的月份中的一条现有量）,并关联船表的
四个唯一性索引,注意不可加上条件CUX_INV_HEADERS_ALL.PERIOD_NAME=CUX_INV_SHIPS_ALL.PERIOD_NAME,因为只有TYPE_CODE为’GL’类型的记录及总账才存在期间。

因此加上条件
CUX_INV_ONHAND_SHIPS_ALL.period_name <= to_char(to_date(substr(p_Start_Date,1,7),'yyyy-mm'),'yyyy-mm')
此外加上报表要求查询的日期条件:and decode(cila.type_code,'GL',ciha.gl_date,'AP',ciha.invoice_date,NULL)between to_date(p_Start_Date,'yyyy-mm-dd HH24:MI:SS'))and( to_date(p_End_Date,'yyyy-mm-dd HH24:MI:SS')); ！！!注意’HH24:MI:SS’必不可少，因为请求程序要求的数据一定是带时分秒的数据否则解析报表时会报错.如下所示:
**Starts**23-08-2011 11:53:42
**Ends**23-08-2011 11:53:42
ORA-01830: 日期格式图片在转换整个输入字符串之前结束
2、根据借方数量与借方金额求出借方单价同理求贷方单价，每发生一笔，统计当前数量与当前金额，然后得出当前数量当前总价与当前单价
其中借方与贷方金额的获取，如: select (
case cila.inv_type
when'IN'then(
case cila.type_code
when'GL'then
ACCOUNTED_DR
when'AP'then
AP_ACCOUNTED
end
)end
) JieFang_Total
From **** case语句可嵌套，每嵌套一次需用end结束,最后一层case语句中获得所需数据通过这种方法可获得任意一行记录中的某个字段.
3、报表的调试比较简单可用打印语句输出xml语句比较异常数据,或者输出其他提示语句显示程序已经执行到哪。

如本例中我自己得到的输出:
其中的现有量现有单价总价的输出均为空(NOW_NUM、NOW_PRICE、NOW_TOTAL),因为提供的数据中没有一行是既有借方也有贷方金额的，所以对于这种数据要注意空值的处理.
四、FORM开发:
1、关于VPD的理解:
一个用户对应一个或多个职责,一个职责对应一个或多个菜单，一个底层菜单对应一个Function,一个Function对应一个报表、表单或者程序。

一个职责对应一个请求组,一个请求组对应多个报表、程序.
通过安全策略来动态返回一个条件（WHERE子句）使得从行级别对数据进行屏蔽，将一个或多个安全策略与表或视图关联后，就实现了Oracle数据库的VPD功能.在MOAC中使用的是安全性配置文件来实现对OU访问的控制，先定义好安全性配置文件，然后将该文件使用预制文件的形式定义在职责或者用户，让这个用户可以访问安全性配置文件所分配的安全OU。

在MOAC中主要使用的是按组织限制访问，在按组织访问设置的过程中该处定义的OU即是多OU的根本.在多OU的设置开始我们会定义一个安全性配置文件和设置当前的安全性配置文件，该配置文件中定义了一系列组织访问的权限控制，我们可以使用下面的语句查询出当前使用的安全性控制文件
SELECT security_profile_name,
business_group_id,
view_all_orgnization_flag
FROM per_security_profiles
WHERE security_profile_id =
to_number(fnd_profile.value(‘XLA_MO_SECURITY_PROFILE_LEVEL’));
可以通过以下语句获取当前安全性配置文件和当前用户在当前职责下可访问的OU
SELECT nazation_id,organization_id,
name
FROM per_orgnazation_list per,
Hr_operating_units hr
WHERE per.secutity_profile_id =
to_number(fnd_profile.VALUE(‘XLA_MO_SECURITY_PROFILE_LEVEL’))
AND nization_id = nization_id
AND able_flag is null;
如我在CUX_INV_MATERIAL_ALL表中建立的Org_Id字段，可在策略函数中通过此字段产生Where子句，筛选可操作客户化职责下物料维护菜单的OU.当用户进入YD_GL_ALL_总账超级用户
职责时将初始化用户的上下文，通过MO_GLOBAL的一系列方法设置CONTEXT的值(包括访问模式和当前Org_Id)。

然后将该上下文中保存的Org_Id与通过
策略函数筛选的Org_Id组进行比较，若上下文中存在策略函数中定义的安全id,则符合条件并将查询结果输出给用户。

在PL/SQL中查询数据时我们经
常需要初始化上下文的值,在单OU模式下只需要与当前存储的唯一Org_Id比较，所以初始化应用程序环境与上下文后(set_policy_context)即可得到正确数据。

但是当通过多OU访问时，还需要多做一步工作，即向PO_GLOB_ORG_ACCESS_TEMP表中插入当前能操作的ORG_ID.这两项工作均在mo_global.init('SQLAP')中完成。

因此初始化工作可如下：
fnd_global.apps_initialize(user_id => 1230,
resp_id => 50541,
resp_appl_id => 200);
mo_global.init('SQLAP');
系统环境变量由应用、责任、用户等组成，数值上应用>责任>用户,优先级上应用<责任<用户。

以上工作完成后，当用户进入不同的职责中时，此回话便保存了用户的职责id，且系统通过MO_GLOBAL的一些列方法将其值存入PO_GLOB_ORG_ACCESS_TEMP表，然后设置CONTEXT的值(包括访问模式和当前Org_Id).然后便可在FORM中的记录组OPERATING_UNITS中输入查询语句如下：
SELECT _Id,
ouv.operating_unit,
_Desc
FROM CUX_OPERATING_UNITS_V2 ouv
WHERE mo_global.check_access(_id) = 'Y'
这样便通过VPD筛选出符合要求的安全数据.筛选出来之后怎么用呢?
首先在用户打开浏览器后通过FORM中的parameter变量获取:
procedure pre_form
is
l_default_org_id number;
l_default_ou_name varchar2(240);
l_ou_count number;
l_security_profile_id number;
begin
MO_GLOBAL.init(‘CUX’);
mo_utils.get_default_ou(l_default_org_id, l_default_ou_name, l_ou_count);
copy(l_default_org_id,'PARAMETER.mo_default_org_id');
copy(l_default_ou_name,'PARAMETER.mo_default_ou_name');
copy(l_ou_count,'PARAMETER.mo_ou_count');
--判断是否找到了OU，如果没有找到，则报错
if nvl(l_ou_count,0) <=0 then
fnd_message.debug('错误001:没有找到相应的OU，请联系系统管理员或开发人员！');
raise form_trigger_failure;
end if;
end pre_form;
获取上述三个参数后，就能参照业务需求完成触发器与界面的编写了
2、FORM开发概览:
(1)、Form Builder安装:
配置TNSNAME,路径:Oracle_Home/network/admin/tnsnames.ora
配置FORMS_PATH:如C:\OA_FORMS;C:\OA_RESOURCE这里是下载服务器上引用的库文件与标准Form存放的本地路径,可在同一文件夹下创建本机的工作目录如C:\evelopement以方便管理.
配置NLS_LANG:修改注册表：Local Machine/Software/Oracle/NLS_LANG改为AMERICAN_AMERICA.ZHS16GBK，这样开发IDE使用英文，字符集可满足英文、简体中文、繁体中文的需要。

下载必要的库文件与TEMPLATE.fmb存储到FORMS_PATH路径下
(2)、创建数据库对象，要求：表、序列、索引建在应用数据库用户下，表放在数据表空间中，索引放在索引表空间中；视图、包建在APPS下，表和序列需要在APPS下创建别名.
表通常包含以下字段:
1、一个表关键字id,通常与表名，并用每个sequence为表记录获得一个唯一值;
2、创建一个Org_Id,根据不同的开发选用不同层次的组织id，通常用的是职责id,本例的物料维护跑在OU层所以用_ALL命名；
3、5个who字段，由Form的内部机制管理.
视图的创建视应用而定，不过通常视图包含基表中的所有字段以备扩展用,视图中必须包含row_id字段
（3）、基本开发流程
1、将ORACLE_FORM路径下的Template.fmb下载到工作目录下，更改FORM中的NAME属性，并以相同的名字保存到文件夹中;
2、删除多余的对象：Data Blocks下的两个块——BLOCKNAME、DETAILBLOCK, Canvases下的一个画布——BLOCKNAME;
3、修改Windows的名称，通常可以取名为MAIN_WIN;
4、修改2个触发器一个程序单元:PRE_FORM、WHEN-NEW-FORM-INSTANCE,与app_%Program Units下的close_window触发器（注意尖括号不保留，如'<your first window>'改为
'SALES_ORDER'）;
5、创建Block数据块，可使用向导不过用向导创建出来的数据块与视图中字段的名字相同，因此在视图创建的过程中最好取定适合的名字以避免更改.
6、设置Block属性及其Subclass，设置item属性及其Subclass;
7、设置画布属性及其Subclass,注意其与Window的关系设置；并调整画布的布局;
8、编写数据操作触发器Program Unit，使用CUX_PLSQL_AUTOCREAT指定块名基表名以完成基本数据记录操作的触发器，其中必用到View中定义的row_id字段,使用FOR UPDATE OF NO WAIT语句锁记录。

（如果使用基表创建数据块，则只能使用基表的主键而非row_id取记录并完成update、delete与insert逻辑，或者不编写该触发器）;
9、在数据块的触发器中调用8中的存储过程.
10、根据业务需求完成其他触发器的编写.
11、上传编译（排错时注意更新时间），编译代码:frmcmp_batch $CUX_TOP/forms/ZHS/CUX_INV_CODE.fmb apps/apps output_file=$CUX_TOP/forms/ZHS/CUX_INV_CODE.fmx
最终的fmb文件与fmx文件名一致.
12、注册Form，定义Function,加入菜单。

这里的定义过程与之前的职责到菜单、功能的过程相反，注意定义Function时Form、源文件中Form名字、文件名三者要一致.
(4)、参数、LOV、导航条、滚动条、日历、主从块.
1、Parameter:
Parameter是Form级参数，外部程序在调用Form时，也可传递具体的参数值，从而达到对Form的某种控制；如果不考虑外部程序传递，完全可以创建一个不基于数据库、字段不显示的特殊块来替代Parameter，通常对Parameter的初始化需要在Form级触发器Pre-Form中完成. 我们可以根据当前用户的Profile来取得其对应的OU。

2、LOV:
LOV的功能可用向导完成，注意在输入query语句后可选择LOV显示的排版顺序，此外只要在LOV中出现的字段都应该定义在该数据块的基础视图中，并可在向导中设置各个字段的显示宽度、标题等.
3、导航条、滚动条
导航条与滚动条都设置在表单的边框附近，边框应留出0.1mm的距离因此画布的宽度除了各个数据项外应预留0.5mm.此外滚动条属于数据块的属性，添加滚动条前应先明确此滚动条操作的是那一部分数据块.
4、日历:
Form中没有日历控件，日期的选择是通过一个特殊的Windowss实现的，因为系统封装的比较好，我们需要按照如下三个步骤实现日期的选择：
(1)、编写Item的KEY-LISTVAL触发器：calendar.show;
(2)、设置Item的List of Values属性：ENABLE_LIST_LAMP
(3)、设置Item的Validate from List属性：No
5、主从块:
选中主块ORDER_HEADERS下的Relations，从左边工具栏点击“+”选择Detail块为ORDER_LINES、选中Prevent Maserless Operations、输入Join
Condition：ORDER_LINES.HEADER_ID = ORDER_HEADERS.HEADER_ID.确定后，自动完成如下工作：
(1)、创建一个名为“ORDER_HEADERS_ORDER_LINES”的Relation
(2)、创建一个Form级触发器ON-CLEAR-DETAILS
(3)、创建两个主块级触发器ON-POPULATE-DETAILS和ON-CHECK-DELETEMASTER
(4)、创建一个过程Query_Master_Details
(5)、设置从块关联Item的Copy Value from Item属性
第(5)步系统未完成，应手动添加.
(5)、关于FORM中触发器执行机制的相关理解
1、查询:
当用户打开浏览器后，后台捕捉到此动作，并由Pre-Form定位到该表单，在Pre-Form中可先初始化parameter变量，如通过VPD安全性检查后获取
合法用户的Org_Id.用户若执行查询，则先遍历表单中是否有改动先提示用户保存。

而后检查此数据块是否有基础表（视图），通过在基础表(视图)上
建立的SQL语句启动查询触发器而后发送查询请求(由触发器调用通信机制),请求服务器的数据。

数据操作以记录为基本单位。

最终显示保存多少记录
或者删除几条记录。

包括On-Update、On-Delete与On-Insert均是对记录的操作，可参看Program Units中数据的操作.
2、状态控制:
主要是项、记录、块与表单的确认.可设置Form的Validation Unit属性，默认选择item.即每当离开项时确认产生。

确认为有效状态后将其标记为Valid。

焦点移动到某项上时，若值不为NULL,用户输入后产生确认判断该项的数据类型、数据格式、长度,若不合法则标记失败。

若合法则启动Post-Change触发器并启动When-Validate-Item触发器将该项标记为有效。

记录的确认有三种状态:New、Change、Valid.记录的确认只对被改变的记录进行.若记录改变则先确认发生变化的项完成后将其标记为有效，并锁定此
记录，而后启动When-Validate-Record将记录标记为有效。

表单与块的确认:在表单中逐块的完成确认，若无变化则成功。

若有变化则将变化了的块或记录标记为有效.
当用户点击保存后将以上的变化全都提交到数据库中,若无变化则提示表单无变化。

此外记录的删除操作与其他操作是分开处理的，当用户删除某条记录后将立即生效并在服务器的数据库中执行删除操作
3、触发器的一般执行顺序:
进入对象启动的触发器:Pre-Form、Pre-Block、Pre-Record、Pre-Text-Item;离开对象:Post-Form、Post-Block、Post-Record、Post-Text-Item；
移动到不同的对象时启动When-New-Form-Instance、When-New-Block-Instance、When-New-Record-Instance、When-New-Item-Instance.所有导航触发器包括Post-Form、Post-Block、Post-Record、Post-Text-Item, Pre-Form、Pre-Block、Pre-Record、Pre-Text-Item等在When-New-Form-Instance触发器以前启动.导航触发器之后启动When-New-Block-Instance并将控制权交由用户
(6)、其他的注意事项:
1、为主键创建序列，主键不能出现在form中,因为必须先确认Form项，查询时此项为空抛出异常.此后当执行块触发器时调用Program Units中的数据操作，由insert_row完成主键id的赋值
2、若以基表为数据源创建数据块则要么不写数据操作触发器要么以主键作为索引，完成update、delete、insert的数据操作
3、建映射表除了主键ID和必须字段外，不能包含其他表中的无关项，以避免冗余
4、系统对触发器轮询，每当有更改或删除时进行记录锁定，点保存时提交到数据库.轮询过程即表单的确认,离开某项时自动完成确认,
FND_MESSAGE函数包可用于调试.
5、对FORM操作时先对记录锁定，而后完成删除、保存等操作
6、在FORM中操作时若删除一条数据，退出时不做保存，则数据将回滚。

做删除操作时FORM将对行进行锁定,由此可见Form中设置了保存点以保存最新的数据
7、来自多张表中的数据块一般只能执行查询操作。

主从块结构可编写从块的on-update、on-insert触发器.查询机制由系统完成.
8、若操作中表单无法获取焦点，一般可以把所有网页浏览器关闭.。