Oracle-Ebs-开发心得.doc

技术心得一、SQL查询：1、”列出同部门中工资高于1000的员工数量超过2人的部门，显示部门名字、地区名称”.查询语句如下：select distinct dept.department_name,loc.cityfrom employees emp,departments dept,locations locwhere emp.department_id=dept.department_idand dept.location_id=loc.location_idand EXISTS( select 1 from employees emp2 where emp2.department_id=emp.department_id and emp2.salary1000 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 locwhere emp.department_id=dept.department_idand dept.location_id=loc.location_idand emp.salary1000group by dept.department_name,loc.cityhaving 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_salaryfrom( 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_idrownumber()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，这是由ORACLE 系统定义的。对于隐式游标的操作，如定义、打开、取值及关闭操作，都由ORACLE 系统自动地完成，无需用户进行处理。用户只能通过隐式游标的相关属性，来完成相应的操作。格式调用为： SQL%注：INSERT, UPDATE, DELETE, SELECT 语句中不必明确定义游标。隐式游标属性属性值SELECTINSERTUPDATEDELETESQL%ISOPENFALSEFALSEFALSEFALSESQL%FOUNDTRUE有结果成功成功SQL%FOUNDFALSE没结果失败失败SQL%NOTFUONDTRUE没结果失败失败SQL%NOTFOUNDFALSE有结果成功失败SQL%ROWCOUNT返回行数，只为1插入的行数修改的行数删除的行数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_TRANSACTIONORACLE8i 可以支持事务处理中的事务处理的概念这种子事务处理可以完成它自己的工作，独立于父事务处理进行提交或者回滚通过使用这种方法，开发者就能够这样的过程，无论父事务处理是提交还是回滚，它都可以成功执行(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)ASBEGIN 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 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 ouv.Org_Id, ouv.operating_unit, ouv.Org_Desc FROM CUX_OPERATING_UNITS_V2 ouvWHERE mo_global.check_access(_id) = Y这样便通过VPD筛选出符合要求的安全数据.筛选出来之后怎么用呢?首先在用户打开浏览器后通过FORM中的parameter变量获取: procedure pre_form isl_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触发器（注意尖括号不保留，如改为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、输入JoinCondition：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将记录标记为有效。表单与块的确