ORACLE PLSQL 表变量

PLSQL 查询表变量

SELECT UNIQUE data_id FROM TABLE(l_data_rec);

-- 查询表变量, 需要在包头声明
CREATE OR REPLACE PACKAGE cux_test_2020 IS
  TYPE rec_data IS RECORD(data_id NUMBER
                         ,data_name VARCHAR2(240));
  TYPE tbl_data IS TABLE OF rec_data INDEX BY BINARY_INTEGER;
END cux_test_2020;
/
DECLARE
  l_data_rec cux_test_2020.tbl_data;
  CURSOR c_data IS
    SELECT UNIQUE data_id FROM TABLE(l_data_rec);
BEGIN
    l_data_rec(1).data_id := 1;
    l_data_rec(2).data_id := 1;
    l_data_rec(1).data_id := 2;
    for rec in c_data loop
        dbms_output.put_line('data_id : '||rec.data_id  );
    end loop;
 
END;

plsql中自定义map的遍历

一般都是像第一个案例, INDEX BY BINARY_INTEGER
但是有时候会有以varchar做key的需求, 其实就是想做一个类似java的map
那么遍历方式就要转换一下思路了

--  INDEX BY VARCHAR 
-- https://blog.csdn.net/haofeifei111/article/details/21075501?depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-4&utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-4
DECLARE
  v_table_name VARCHAR2(100);
  TYPE type_array_str IS TABLE OF VARCHAR2(32) INDEX BY VARCHAR(32);
  v_array_table type_array_str;
BEGIN
  v_array_table('STATIC_AGG_HOUR') := 'PART_AGGHOUR_';
  v_array_table('STATIC_AGG_PROVINCE_DAY') := 'PART_PROV_';
  v_table_name := v_array_table.first;
  LOOP
    EXIT WHEN v_table_name IS NULL;
    dbms_output.put_line(v_array_table(v_table_name));
    dbms_output.put_line(v_table_name);
    v_table_name := v_array_table.next(v_table_name);
  END LOOP;
END;

现有表类型变量

TYPE emp_table IS TABLE OF emp%ROWTYPE;
TYPE emp_name_table IS TABLE OF emp.ename%TYPE;
emptable emp_table := emp_table();
rec_data cux_ap_inv_sum_iface%rowtype;