Oracle Forms 10g中基于LOV实现PRODUCTS数据块过滤的开发需求

Got it, let's walk through building this simple product form with all the functionality you need. I'll break it down into actionable, easy-to-follow steps:

First, let's lay the foundation with properly linked data blocks:

• Create a data block named PRODUCTS using your PRODUCTS table as the source. Include all essential fields (product ID, name, description, and the critical COMPANIES_PARTNERS_ID).
• Add a secondary data block COMPANIES_PARTNERS based on your partner table, pulling in COMPANIES_PARTNERS_ID and a user-friendly display field like PARTNER_NAME.
• Set up a master-detail relationship between the two blocks: use COMPANIES_PARTNERS.COMPANIES_PARTNERS_ID as the master key, and map it to PRODUCTS.COMPANIES_PARTNERS_ID as the detail foreign key. This ensures the product list updates automatically when a partner is selected.

Next, create a user-friendly List of Values (LOV) for the partner picker:

• Create a new LOV using the COMPANIES_PARTNERS data block as its source.
• Set the display item to PARTNER_NAME (so users see readable partner names instead of raw IDs) and the return item to PRODUCTS.COMPANIES_PARTNERS_ID.
• Attach this LOV to the COMPANIES_PARTNERS_ID item in the PRODUCTS block—don't forget to enable the "LOV Enabled" property for the item.

We need the product list to show all items when no partner is selected, and only the chosen partner's products when a selection is made. Add a trigger to handle this logic:

• On the PRODUCTS.COMPANIES_PARTNERS_ID item, create a WHEN-VALIDATE-ITEM trigger with this PL/SQL code:

  IF :PRODUCTS.COMPANIES_PARTNERS_ID IS NULL THEN
      -- Show all products when no partner is selected
      SET_BLOCK_PROPERTY('PRODUCTS', DEFAULT_WHERE, '');
  ELSE
      -- Filter products to the selected partner
      SET_BLOCK_PROPERTY('PRODUCTS', DEFAULT_WHERE, 
          'COMPANIES_PARTNERS_ID = ' || :PRODUCTS.COMPANIES_PARTNERS_ID);
  END IF;
  -- Refresh the product block to apply the filter immediately
  EXECUTE_QUERY;
  

• Pro tip: If COMPANIES_PARTNERS_ID is a string (not numeric), wrap the value in single quotes in the WHERE clause: 'COMPANIES_PARTNERS_ID = ''' || :PRODUCTS.COMPANIES_PARTNERS_ID || ''''

Now let's set up the search functionality with the button next to the search input:

• Create a text item (e.g., P_SEARCH) in the form's header section—this will be your search input field.
• Add a button (e.g., BTN_SEARCH) directly to the right of the search box. Set its label to "Search" or use a magnifying glass icon if your form builder supports it.
• Create a WHEN-BUTTON-PRESSED trigger on BTN_SEARCH to combine the partner filter with the search term. Here's the code:

  DECLARE
      l_where_clause VARCHAR2(1000);
  BEGIN
      l_where_clause := '';
      
      -- Add partner filter if a partner is selected
      IF :PRODUCTS.COMPANIES_PARTNERS_ID IS NOT NULL THEN
          l_where_clause := 'COMPANIES_PARTNERS_ID = ' || :PRODUCTS.COMPANIES_PARTNERS_ID;
      END IF;
      
      -- Add search term filter if input is provided
      IF :P_SEARCH IS NOT NULL THEN
          IF l_where_clause IS NOT NULL THEN
              l_where_clause := l_where_clause || ' AND ';
          END IF;
          -- Adjust the search fields to match your product table (e.g., product_name, description)
          l_where_clause := l_where_clause || '(PRODUCT_NAME LIKE ''%' || :P_SEARCH || '%'' OR DESCRIPTION LIKE ''%' || :P_SEARCH || '%'')';
      END IF;
      
      -- Apply the combined filter and refresh the product list
      SET_BLOCK_PROPERTY('PRODUCTS', DEFAULT_WHERE, l_where_clause);
      EXECUTE_QUERY;
  END;
  

• For extra convenience, add a WHEN-KEY-ENTER trigger on P_SEARCH that calls the same code as the search button—so users can press Enter to search without clicking the button.

• Arrange the partner LOV item, search box, and search button in the form's header for easy access.
• Display the PRODUCTS block's items in a clean grid or list below the header, so users can view and edit product details clearly.

内容的提问来源于stack exchange，提问作者Sergio Sánchez