OracleEbs开发总结.docx
《OracleEbs开发总结.docx》由会员分享，可在线阅读，更多相关《OracleEbs开发总结.docx（18页珍藏版）》请在冰点文库上搜索。
OracleEbs开发总结
技术心得
一、SQL查询：
1、”列出同部门中工资高于1000的员工数量超过2人的部门，显示部门名字、地区名称”.查询语句如下：
selectdistinctdept.department_name,loc.city
fromemployeesemp,departmentsdept,locationsloc
whereemp.department_id=dept.department_id
anddept.location_id=loc.location_id
andEXISTS（
select1
fromemployeesemp2
whereemp2.department_id=emp.department_id
andemp2.salary>1000
groupbyemp2.department_id
havingcount（emp2.department_id）>2
）
通常我们只需要对记录逐行的筛选，对于分组数据我们只能使用分组语句avg、max等,也就是说如果你想在select中得到这个属性那么“它们“也必须出现在groupby中或者你只想得到一个统计数据.回顾上面的例子,我们也可以用以下语句完成:
selectdept.department_name,loc.city,count（*）
fromemployeesemp,departmentsdept,locationsloc
whereemp.department_id=dept.department_id
anddept.location_id=loc.location_id
andemp.salary>1000
groupbydept.department_name,loc.city
havingcount（*）>2
对于groupby来说每一条emp.department_id必对应唯一dept.department_id、dept.department_name因此不论groupbydepartment_id还是groupbydepartment_name,loc.city达到的效果是一样的
2、用一条语句查询出scott.emp表中每个部门工资前三位的数据:
selectdepartment_id,max（salary）max_salary,max（decode（rank,2,salary,salary））mid_salary,min（salary）min_salary
from
（
selectdepartment_id,salary,rankfrom
（selectemp.department_id,emp.employee_id,emp.salary,row_number（）over（partitionbyemp.department_idorderbyemp.salary）asrank
fromemployeesemp）E
whereE.rank<=3
）
groupbydepartment_id
rownumber（）over（patitionbycolumn1orderbycolumn2）,表示以column1分组对column2排序,row_number可用于筛选重复项.
3、哪些员工跟Den（FIRST_NAME）、Rephaely（Last_Name）不在同一个部门.
此处可能存在没有部门的员工，应该用NoExists筛选deptno与该员工不等的记录.若要求空值可使用NOEXISTS若不要求空值可用EXISTS,尽量用EXISTS取代IN、ANY、ALL等操作（可提高性能）,注意空值的处理!
!
!
4、在多表连接查询中，子查询最多只可嵌套一层否则Oracle无法识别
5、forhandlein:
游标
二、PL/SQl存储过程
1、游标的使用:
显式游标的使用分为四步,声明、打开、循环、关闭.打开游标：
就是执行游标所对应的SELECT语句，将其查询结果放入工作区，并且指针指向工作区的首部，标识结果集合。
如果游标查询语句中带有FORUPDATE选项，OPEN语句还将锁定数据库表中游标结果集合对应的数据行。
如下所示:
DECLARE
CURSORc4（dept_idNUMBER,j_idVARCHAR2）
IS
SELECTfirst_namef_name,hire_dateFROMemployees
WHEREdepartment_id=dept_idANDjob_id=j_id;
--声明游标定义记录变量，比声明记录类型变量要方便，不容易出错
v_emp_recordc4%ROWTYPE;
BEGIN
--OPENc4（90,'AD_VP'）;
/*LOOP
FETCHc4INTOv_emp_record;
IFc4%FOUNDTHEN
DBMS_OUTPUT.PUT_LINE（v_emp_record.f_name||'的雇佣日期是'
||v_emp_record.hire_date）;
ELSE
DBMS_OUTPUT.PUT_LINE（'已经处理完结果集了'）;
EXIT;
ENDIF;
ENDLOOP;*/
/*CLOSEc4;---关闭游标*/
FORc1INc4（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）;
ENDLOOP;
END;
以FORc1INc_cursor使用游标，c1会自动遍历每行记录,不用像显式游标一样打开游标后在循环中使用FETCH将表征多行记录的游标的值传递出来,FOR语句相当于OPEN与LOOP、FETCH的综合使用,且不必人为的关闭.
2、隐式游标的处理:
显式游标主要是用于对查询语句的处理，尤其是在查询结果为多条记录的情况下；而对于非查询语句，如修改、删除操作，则由ORACLE系统自动地为这些操作设置游标并创建其工作区.
隐式游标的名字为SQL，这是由ORACLE系统定义的。
对于隐式游标的操作，如定义、打开、取值及关闭操作，都由ORACLE系统自动地完成，无需用户进行处理。
用户只能通过隐式游标的相关属性，来完成相应的操作。
格式调用为：
SQL%
注：
INSERT,UPDATE,DELETE,SELECT语句中不必明确定义游标。
隐式游标属性
属性
值
SELECT
INSERT
UPDATE
DELETE
SQL%ISOPEN
FALSE
FALSE
FALSE
FALSE
SQL%FOUND
TRUE
有结果
成功
成功
SQL%FOUND
FALSE
没结果
失败
失败
SQL%NOTFUOND
TRUE
没结果
失败
失败
SQL%NOTFOUND
FALSE
有结果
成功
失败
SQL%ROWCOUNT
返回行数，只为1
插入的行数
修改的行数
删除的行数
3、异常处理:
分类:
（1）、系统预定义异常,直接引用异常名，并处理即可
（2）、非预定义的异常处理:
将定义好的异常情况与标准的Oracle错误联系起来,使用EXCEPTION_INIT语,PRAGMAEXCEPTION_INIT（<异常情况>,<错误代码>）.
（3）、用户自定义的异常处理:
无错误代码，需要在本程序块完成捕捉与处理.
DECLARE
v_empnoemployees.employee_id%TYPE:
=&empno;
no_resultEXCEPTION;
BEGIN
UPDATEemployeesSETsalary=salary+100WHEREemployee_id=v_empno;
IFSQL%NOTFOUNDTHEN
RAISEno_result;
ENDIF;
EXCEPTION
WHENno_resultTHEN
DBMS_OUTPUT.PUT_LINE（'你的数据更新语句失败了!
'）;
WHENOTHERSTHEN
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、PRAGMAAUTONOMOUS_TRANSACTION
ORACLE8i可以支持事务处理中的事务处理的概念．这种子事务处理可以完成它自己的工作，独立于父事务处理进行提交或者回滚．通过使用这种方法，开发者就能够这样的过程，无论父事务处理是提交还是回滚，它都可以成功执行．
（1）使用自动事务处理:
DROPTABLElogtable;
CREATETABLElogtable（
Usernamevarchar2（20）,
Dassate_timedate,
Megevarchar2（60）
）;
CREATETABLEtemp_table（Nnumber）;
CREATEORREPLACEPROCEDURElog_message（p_messagevarchar2）
AS
PRAGMAAUTONOMOUS_TRANSACTION;
BEGIN
INSERTINTOlogtableVALUES（user,sysdate,p_message）;
COMMIT;
ENDlog_message;
BEGIN
Log_message（'Abouttoinsertintotemp_table'）;
INSERTINTOtemp_tableVALUES
（1）;
LOG_message（'Rollbacktoinsertintotemp_table'）;
ROLLBACK;
END;
select*fromlogtable;
select*fromtemp_table（查询结果无数据）
（2）不使用自动事务处理:
CREATEORREPLACEPROCEDURElog_message（p_messagevarchar2）
AS
BEGIN
INSERTINTOlogtableVALUES（user,sysdate,p_message）;
COMMIT;
ENDlog_message;
（select*fromtemp_table查询结果有数据）
三、报表开发:
流程:
1、产生数据源:
以PL/SQL或SQl获取数据源，并用ReportsDeveloper工具创建数据模型并分组.
（ReportsBuilder生成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'）
此外加上报表要求查询的日期条件:
anddecode（cila.type_code,'GL',ciha.gl_date,'AP',ciha.invoice_date,NULL）betweento_date（p_Start_Date,'yyyy-mm-ddHH24:
MI:
SS'））and（
to_date（p_End_Date,'yyyy-mm-ddHH24:
MI:
SS'））;！
！
!
注意’HH24:
MI:
SS’必不可少，因为请求程序要求的数据一定是带时分秒的数据否则解析报表时会报错.如下所示:
**Starts**23-08-201111:
53:
42
**Ends**23-08-201111:
53:
42
ORA-01830:
日期格式图片在转换整个输入字符串之前结束
2、根据借方数量与借方金额求出借方单价同理求贷方单价，每发生一笔，统计当前数量与当前金额，然后得出当前数量当前总价与当前单价
其中借方与贷方金额的获取，如:
select（
casecila.inv_type
when'IN'then（
casecila.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的设置开始我们会定义一个安全性配置文件和设置当前的安全性配置文件，该配置文件中定义了一系列组织访问的权限控制，我们可以使用下面的语句查询出当前使用的安全性控制文件
SELECTsecurity_profile_name,
business_group_id,
view_all_orgnization_flag
FROMper_security_profiles
WHEREsecurity_profile_id=
to_number（fnd_profile.value（‘XLA_MO_SECURITY_PROFILE_LEVEL’））;
可以通过以下语句获取当前安全性配置文件和当前用户在当前职责下可访问的OU
SELECTper.orgnazation_id,organization_id,
hr.NAMEname
FROMper_orgnazation_listper,
Hr_operating_unitshr
WHEREper.secutity_profile_id=
to_number（fnd_profile.VALUE（‘XLA_MO_SECURITY_PROFILE_LEVEL’））
ANDhr.orgnization_id=per.orgnization_id
ANDhr.usable_flagisnull;
如我在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中输入查询语句如下：
SELECTouv.Org_Id,
ouv.operating_unit,
ouv.Org_Desc
FROMCUX_OPERATING_UNITS_V2ouv
WHEREmo_global.check_access（ouv.org_id）='Y'
这样便通过VPD筛选出符合要求的安全数据.筛选出来之后怎么用呢?
首先在用户打开浏览器后通过FORM中的parameter变量获取:
procedurepre_form
is
l_default_org_idnumber;
l_default_ou_namevarchar2（240）;
l_ou_countnumber;
l_security_profile_idnumber;
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，如果没有找到，则报错
ifnvl（l_ou_count,0）<=0then
fnd_message.debug（'错误001:
没有找到相应的OU，请联系系统管理员或开发人员！
'）;
raiseform_trigger_failure;
endif;
endpre_form;
获取上述三个参数后，就能参照业务需求完成触发器与界面的编写了
2、FORM开发概览:
（1）、FormBuilder安装:
配置TNSNAME,路径:
Oracle_Home/network/admin/tnsnames.ora
配置FORMS_PATH:
如C:
\OA_FORMS;C:
\OA_RESOURCE这里是下载服务器上引用的库文件与标准Form存放的本地路径,可在同一文件夹下创建本机的工作目录如C:
\evelopement以方便管理.
配置NLS_LANG:
修改注册表：
LocalMachine/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、删除多余的对象：
DataBlocks下的两个块——BLOCKNAME、DETAILBLOCK,Canvases下的一个画布——BLOCKNAME;
3、修改Windows的名称，通常可以取名为MAIN_WIN;
4、修改2个触发器一个程序单元:
PRE_FORM、WHEN-NEW-FORM-INSTANCE,与app_%ProgramUnits下的close_window触发器（注意尖括号不保留，如''改为'SALES_ORDER'）;
5、创建Block数据块，可使用向导不过用向导创建出来的数据块与视图中字段的名字相同，因此在视图创建的过程中最好取定适合的名字以避免更改.
6、设置Block属性及其Subclass，设置item属性及其Subclass;
7、设置画布属性及其Subclass,注意其与Window的关系设置；并调整画布的布局;
8、编写数据操作触发器ProgramUnit，使用CUX_PLSQL_AUTOCREAT指定块名基表名以完成基本数据记录操作的触发器，其中必用到View中定义的row_id字段,使用FORUPDATEOFNOWAIT语句锁记录。
（如果使用基表创建数据块，则只能使用基表的主键而非row_id取记录并完成update、delete与insert逻辑，或者不编写该触发器）;
9、在数据块的触发器中调用8
升级会员 