Oracle APEX级联LOV：Cascading LOVs/Select Lists

原文地址：http://www.inside-oracle-apex.com/oracle-apex-4-0-cascading-lovsselect-lists/

One of the new features of Early Adopter 2 are Cascading LOVs/Select Lists. I’m pretty sure that almost every APEX developer had already the requirement to refresh a child select list when a parent select list was changed. For example you pick a department in the first select list and the second should just show employees of that department. There are two solutions to solve that:

1. Use a “Select List with Submit” for the department field, which will automatically submit and refresh the page when a department is picked. In the Web 2.0 world that’s a little bit outdated and it can also takes quite long to refresh the full page. You also have to take care that your validations and processes do not fire in that specific case. So quite a number of extra conditions are necessary to deal with the auto submit. This solution doesn’t really make the page more maintainable and the user experience is also not very great.
2. But you have heard about the holy grail AJAX anyway. It’s the way to go! The idea is to just refresh the child select list by calling the server to get a restricted list of employees and use browser DOM manipulation to replace the existing select list with the new values. Much better user experience! No full page refresh, the employee select list just magically changes. But it doesn’t come for free. If you have a look at all the Cascading LOV examples which are out there you will notice that you have to write quite a bit of JavaScript code and an On-Demand process which duplicates your LOV statement. So this solution offers great user experience but again makes your page hard to maintain. And I’m sure it will takes you more than 5 minutes to implement that solution…

So both solutions are not really satisfying because the require quite a bit of coding, which makes the application harder to maintain. But with Oracle Application Express 4.0 there is now a third solution for the problem. It gives the user a great UI experience and doesn’t require any additional coding!

Declarative “Cascading LOV” support for all “List of Values” based item types! That means select list, checkbox, radio group and shuttle based page items can automatically be refreshed if a parent field gets changed. (Note: Popup LOV and List Manager will be added as well in the final product) Important to mention here is that the parent field doesn’t have to be a select list, it can be a simple text field, date picker, … as well. Every DOM element which fires a change event will work as parent.

To add “Cascading LOV” support for a child page item, you just have to set the new “Cascading LOV Parent Item(s)” attribute in the “List of Values” section to the name of your parent page item. That’s it! This will tell APEX to automatically refresh the page item with new values each time the parent item gets changed.

So let’s create a simple example where we first select a department and then an employee in that department.

1. Create page item P2_DEPTNO of type “Select List” with the following LOV statement

   select dname,       deptno  from dept order by 1

2. Create page item P2_EMPNO of type “Select List” with the following settings
   • Cascading LOV Parent Item(s): P2_DEPTNO
   • List of values definition:

     select ename,       empno  from emp where deptno = :P2_DEPTNO order by 1

That’s all! Super simple as it should be.

If you have a look at the screenshot above you will see that Cascading LOVs have the attributes “Page Items to Submit” and “Optimize Refresh” as well. Most time you will probably not need to set them. For details about them consult the page item level help.