The experience of using trees in Oracle Form Builder

A brief introduction of oracle| and tree
The Developer 6.0 version provides the concept of hierarchy tree (hierarchy trees), the Htree control is very convenient, only need a small amount of programming to achieve the purpose of the display hierarchy.



The following are important in the tree's unique properties:

L multiple selection (multi-selection): Whether multiple nodes of the tree are allowed to be selected at once. If not, the first selected node is deselected when the second node is selected.

L Record Group: Specifies the name of the record group for the spanning tree.



A brief introduction to tree-related triggers (built-in):

L FUNCTION Get_tree_node_property (item_name VARCHAR2, node node, property number);

Function: Get Properties of tree node

Some of the property has the following:

Node_state:expanded_node (Extended node)

Collapsed_node (Shrink node)

Leaf_node (leaf node)--Note: cannot expand or shrink

Node_depth: The level of nodes in the tree.

Node_label: Display text for nodes

Node_icon: Icon for node

Node_value: The value of the node.

Example:

DECLARE

Htree ITEM;

Node_value VARCHAR2 (100);

BEGIN

--Get the tree

Htree: = Find_item (' tree_block.htree3 ');

--Gets the value of the currently selected node

Node_value: = Ftree.get_tree_node_property (Htree,: SYSTEM. Trigger_node, Ftree.node_value);

...

End;

Note: SYSTEM. Trigger_node refers to the currently selected tree node.

L FUNCTION Get_tree_property (item_name varchar2,property number);

Function: Get the properties of the tree

Some of the property has the following:

DATASOURCE

Record_group

Query_text

Node_count: Returns the number of nodes in the tree.

Selection_count

Allow_empty_branches

Allow_multi-select

L PROCEDURE set_tree_node_property (item_name varchar2,node ftree. Node,property number,value VARCHAR2);

Features: Setting the properties of a tree node

L PROCEDURE Set_tree_property (item_name varchar2,property number, value VARCHAR2);

PROCEDURE Set_tree_property (item_name varchar2,property number, value recordgroup);

Features: Setting the properties of a tree

L PROCEDURE Populate_tree (Item_name VARCHAR2);

Function: Empty the existing data in the tree and regenerate the tree based on the record group or data query.

L PROCEDURE add_tree_data (item_name varchar2,node ftree. NODE, Offset_type number,offset number,data_source number,data VARCHAR2);

Function: Add the data in the tree under the specified node

Note: Use more trouble.

L FUNCTION Find_tree_node (item_name varchar2,earch_string VARCHAR2, Search_type number,search_by number,search_r Oot node,start_point NODE);

Function: Locate the node that displays text or values that conform to search_string.

Parameters:

Search_type:find_next

Find_next_child

Search_by:node_label

Node_value

Search_root: The root node of the query, typically the Ftree.root_node

Start_point: The starting node of the lookup, typically the Ftree.root_node

L FUNCTION Add_tree_node (item_name varchar2,node ftree. NODE, Offset_type number,offset number,state number,label VARCHAR2, Icon Varchar2,value VARCHAR2);

Function: Adds a tree node.

Offset_type: Specifies the branch type of the node, Parent_offset and Sibling_offset

Offset: Specifies the location of the new node,

Parent_offset:1.. N

Last_child

Sibling_offset:next_node

Previous_node

State:expanded_node (Extended node)

Collapsed_node (Shrink node)

Leaf_node (leaf node)

L PROCEDURE Delete_tree_node (item_name varchar2,node NODE);

Function: Delete tree node

L FUNCTION get_tree_node_parent (item_name varchar2,node NODE);

Function: Gets the parent node of the specified node.

L FUNCTION get_tree_selection (item_name varchar2,selection number);

Function: Gets the node in the selected state.

L PROCEDURE set_tree_selection (item_name varchar2,node node, selection_type number);

Function: Specifies the selected state of a single node

Parameters:

selection_type:select_on

Select_off

Select_toggle



The trigger for the form's Run-time state:

L when-tree-node-activated: Triggered when the user clicks the node or presses the [ENTER] key when the nodes are selected.

L when-tree-node-expanded: Triggering when a node expands or shrinks

L when-tree-node-selected: Triggered when node is selected or deselected
Second, the way to build the tree
Tree controls are generally placed in a single control block (note: cannot be placed in a block of data), it is easy to place the tree on the canvas (CANVAS), and the tree's properties do not need to be set if it is not necessary.



There are several ways to build a tree:

L generate by setting a record group or data query property before running

L through the Add_tree_data trigger to achieve

L run state, through Add_tree_node and other triggers to achieve

L run state by adding or deleting data elements of a group of records to implement



Analysis:

First, direct operation of the tree

Description: Find_tree_node finds the specified node, Add_tree_node to add its subordinate nodes.

Disadvantages: Programming is more complex, not flexible operation, and error-prone.

Advantages: The process of adding nodes can be controlled to achieve some special requirements.

Example:

The cursor,emp_cur of the--dept_cur is the cursor of the employee.

Htree: = Find_item (' tree_view.tree_emp ');

Open dept_cur;

Loop

Fetch dept_cur into AA;

Exit when Dept_cur%notfound;

Del_node: = Ftree.find_tree_node (HTREE,AA.KJMC, Ftree.find_next, Ftree.node_label, Ftree.root_node, Ftree.ROOT_NODE) ;

--Delete the unit node and its child nodes

IF not Ftree.id_null (Del_node) Then

Ftree.delete_tree_node (Htree, Del_node);

End IF;

End Loop;

Close dept_cur;



--based on the first level node of the unit generation tree obtained with cursor

Open dept_cur;

Loop

Fetch dept_cur into AA;

Exit when Dept_cur%notfound;

New_node: = Ftree.add_tree_node (Htree, Ftree.root_node, Ftree.parent_offset, Ftree.last_child, Ftree.EXPANDED_NODE, Aa.dname, ", Aa.deptno);

End Loop;

Close dept_cur;

--The lower node of the build tree based on employee cursor

Open emp_cur;

Loop

Fetch emp_cur into BB;

Exit when Emp_cur%notfound;

Find_node: = Ftree.find_tree_node (Htree, BB.KJBH, Ftree.find_next,

Ftree.node_value, Ftree.root_node, Ftree.root_node);

New_node: = Ftree.add_tree_node (Htree, Find_node, Ftree.parent_offset, Ftree.last_child, Ftree.EXPANDED_NODE, Bb.ename, ", bb.empno);

End Loop;

Close emp_cur;

--Get the root node of the tree

SS: = Ftree.get_tree_property (Htree,ftree. Node_count);

--loop until all nodes in the tree are expanded

For J in 1..ss LOOP

Exp_node: = Ftree.find_tree_node (Htree, ");

state: = Ftree.get_tree_node_property (Htree, J, Ftree.node_state);

IF state = Ftree.collapsed_node THEN

Ftree.set_tree_node_property (Htree, J, Ftree.node_state, Ftree.expanded_node);

End IF;

End LOOP;

Second, dynamic record group

The data format of the group of records used by the hierarchy tree:

+-car

|

--airplane

| -boeing

| -boeing



Initial state

Number of layers

Display text

Icon

Value

-1 (Shrink node)

1

' Car '

''

' Car '

0 (leaf node)

2

' Honda '

''

' Civic '

1 (Expand node)

1

' Airplane '

''

' Plane '

0

2

' Boeing '

''

' 747 '

0

2

' Boeing '

''

' 757 '



There are two different ways to generate a group of records.

1. Generate record group from query

Description: Use the tree query statement (connect By...prior...start with ...). Generates a group of records and sets the properties of the tree to be generated.

Advantages: Simple programming, convenient.

Disadvantage: Only applies if you can construct a tree-like query statement.

Example:

V_ignore number;

Rg_emps Recordgroup;

Begin

Rg_emps: = Find_group (' Emps ');

--Empty the data if not empty

If not Id_null (rg_emps) Then

Delete_group (Rg_emps);

End If;

--Constructing a record group

Rg_emps: = Create_group_from_query (' Emps ',

' Select 1, Level, ename, NULL, To_char (empno) ' | |

' from emp ' | |

' Connect by prior empno = Mgr ' | |

' Start with job = ' PRESIDENT '];

V_ignore: = Populate_group (Rg_emps);

Ftree.set_tree_property (' Tree_view.tree_emp ', Ftree.record_group, rg_emps);

End

2, using row and column data to directly construct the record group

Description: A group of records is generally a row-and-column structure that adds cell data directly to a group of records in a circular fashion.

Advantages: You can directly control the style of the record group.

Disadvantages: For multi-layer structure, programming is also more complex.

Example:

--Unit cursor

Cursor Cursor_dept is

Select Dname, Deptno from Dept order by Dname;

--Employee cursor

Cursor Cursor_emp (p_dno number) is

Select Ename, empno from emp where deptno = P_dno order by ename;

V_i number;

V_ignore number;



Rg_emps Recordgroup;

Rg_depts Recordgroup;



V_init_state Groupcolumn;

V_level Groupcolumn;

V_label Groupcolumn;

V_icon Groupcolumn;

V_value Groupcolumn;

Begin

Rg_depts: = Find_group (' depts ');

--If there is data, empty the record group

If not Id_null (rg_depts) Then

Delete_group (rg_depts);

End If;



Rg_depts: = Create_group (' depts ');

--Here, customize the data type and length of the columns in the record group you need

--Initial state (to expand, shrink, or leaf node)

V_init_state: = Add_group_column (rg_depts, ' init_state ', number_column);

--Number of layers

V_level: = Add_group_column (rg_depts, ' level ', number_column);

--Display text

V_label: = Add_group_column (rg_depts, ' label ', Char_column, 40);

--Icon

V_icon: = Add_group_column (rg_depts, ' icon ', Char_column, 20);

--Value

V_value: = Add_group_column (rg_depts, ' value ', Char_column, 5);



V_i: = 1;

For Deptrec in Cursor_dept loop

Add_group_row (rg_depts, v_i);

Set_group_number_cell (V_init_state, v_i, 1);

Set_group_number_cell (V_level, v_i, 1);

Set_group_char_cell (V_label, V_i, deptrec.dname);

Set_group_char_cell (V_icon, V_i, NULL);

Set_group_char_cell (V_value, V_i, To_char (Deptrec.deptno));

V_i: = v_i + 1;



For Emprec in Cursor_emp (Deptrec.deptno) loop

Add_group_row (rg_depts, v_i);

Set_group_number_cell (V_init_state, v_i, 1);

Set_group_number_cell (V_level, v_i, 2);

Set_group_char_cell (V_label, V_i, emprec.ename);

Set_group_char_cell (V_icon, V_i, NULL);

Set_group_char_cell (V_value, V_i, To_char (emprec.empno));

V_i: = v_i + 1;

End Loop;

End Loop;

Ftree.set_tree_property (' tree_view.tree_org ', Ftree.record_group, rg_depts);

End

Conclusion: As far as possible, the parent-child structure should be placed on a table in database design, so it is most simple and practical to use the query to generate a record group to regenerate the tree. If this is not possible, a tree can also be generated by constructing the record group directly. If no special requirements, generally do not take the direct operation of the tree way.

Note: The authors typically place the tree-spanning program in the "program unit" of form builder, where needed, to refresh the tree in real time.

Reference: Developer 6.0 Pl/sql Library: NAVIGATE.PLL
III. Legacy Issues
Because developer simplifies the design of trees, some operations such as the transfer and copying of trees are not easy to implement. The subject still needs to be studied.