How is the table() function used in Oracle?

2 years ago
Noah Thompson
2 minutes

The table() function in Oracle database is used to convert complex collection types into table types. It takes a collection type as a parameter and returns the table representation of that collection type.

Here are some examples of using the table() function:

  1. Using table-type variables:
DECLARE
  TYPE emp_type IS TABLE OF employees%ROWTYPE;
  emp_tab emp_type;
BEGIN
  SELECT * BULK COLLECT INTO emp_tab FROM employees;
  
  -- 使用table()函数将表类型变量转换为表
  SELECT * FROM TABLE(emp_tab);
END;
/
  1. Use collection types with a single field.
DECLARE
  TYPE emp_names IS TABLE OF employees.last_name%TYPE;
  emp_names_tab emp_names;
BEGIN
  SELECT last_name BULK COLLECT INTO emp_names_tab FROM employees;
  
  -- 使用table()函数将单个字段的集合类型转换为表
  SELECT * FROM TABLE(emp_names_tab);
END;
/
  1. Using nested table types.
DECLARE
  TYPE emp_dept IS TABLE OF employees%ROWTYPE;
  TYPE dept_employees IS TABLE OF emp_dept;
  dept_emp_tab dept_employees;
BEGIN
  SELECT department_id, CAST(MULTISET(SELECT * FROM employees WHERE department_id = d.department_id) AS emp_dept) 
    BULK COLLECT INTO dept_emp_tab
  FROM departments d;
  
  -- 使用table()函数将嵌套表类型转换为表
  SELECT d.department_name, e.*
  FROM TABLE(dept_emp_tab) d, TABLE(d.column_value) e;
END;
/

These examples demonstrate how to use the table() function in a PL/SQL block to convert a collection type to a table type for use in queries. Feel free to modify the code as needed.

bannerAds