oracle集合与记录
oracle集合与记录
海贼爱音乐 发表于4个月前
oracle集合与记录
  • 发表于 4个月前
  • 阅读 9
  • 收藏 0
  • 点赞 0
  • 评论 0

腾讯云 新注册用户 域名抢购1元起>>>   

摘要: PL/SQL中有标量类型和组合类型,组合类型分为集合与记录。
  1. 集合。集合是相同类型元素的组合。数据库中相当于"多行单列", 类似于数组, 使用唯一的下标来标识其中的每个元素 > In a collection, the internal components always have the same data type, and are called elements. You can access each element of a collection variable by its unique index, with this syntax: variable_name(index). To create a collection variable, you either define a collection type and then create a variable of that type or use %TYPE.

关联数组(索引表)Associative array(or index-by table),下标无限制,可以为负数,元素个数无限制 > TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY key_type;
type_name:用户自定义数据类型的名字
element_type:索引表中元素类型
key_type:索引表元素下标的数据类型(BINARY_INTEGER,PLS_INTEGER,VARCHAR2)

declare
 --显式游标
 cursor cur_dept is
   select department_name from departments where rownum < 6;
 --关联数组
 type type_dept is table of departments.department_name%type index by pls_integer;
 v_dept type_dept;
 i      pls_integer := 0;
begin
 for c in cur_dept loop
   i := i + 1;
   v_dept(i) := c.department_name;
   dbms_output.put_line('插入' || v_dept(i));
 end loop;
end;

嵌套表(Nested table),下标从1开始,通过构造器初始化,大小可以自动增长。用extend方法可以扩展元素个数

DECLARE
 CURSOR cur_dept IS
   SELECT department_name FROM departments WHERE ROWNUM < 6;
 TYPE type_dept IS TABLE OF departments.department_name%TYPE;
 --构造器,可以无参也可带参
 v_dept type_dept := type_dept();
 i      PLS_INTEGER := 0;
BEGIN
 FOR c IN cur_dept LOOP
   --extend方法
   v_dept.extend;
   i := i + 1;
   v_dept(i) := c.department_name;
   dbms_output.put_line('插入' || v_dept(i));
 END LOOP;
 --据说v_dept.first..v_dept.last这种方式不好当数据出现null值时。
 --first/last方法返回集合上下标,count方法返回集合个数。
 FOR j IN 1 .. v_dept.count LOOP
   dbms_output.put_line('展示' || v_dept(j));
 END LOOP;
END;

变长数组VARRAY(variable-size array),元素之间是紧密排列,通过构造器初始化,元素有序排列。 > type type_name IS {varray|varying array}(maxinum_size) OF element_type[not null];
> type_name:可变数组的类型名
> maxinum_size:可变数组元素个数的最大值
> element_type:数组元素的类型

DECLARE
  TYPE type_var IS VARRAY(10) OF INT;
  v_var type_var := type_var();
BEGIN
  --dbms_output.put_line(varr.count);  
  FOR i IN 1 .. 5 LOOP
    v_var.extend;
    v_var(i) := i + 1;
  END LOOP;

  FOR i IN 1 .. 5 LOOP
    dbms_output.put_line(v_var(i));
  END LOOP;
END;
  • 区别
    输入图片说明
  1. 记录。单行多列的标量构成的复合结构。可以看做是一种用户自定义数据类型。组成类似于多维数组。将一个或多个标量封装成一个对象进行操作。是一种临时复合对象类型。 记录可以直接赋值。RECORD1 :=RECORD2; 记录不可以整体比较。 记录不可以整体判断为空。 > In a record, the internal components can have different data types, and are called fields. You can access each field of a record variable by its name, with this syntax: variable_name.field_name. To create a record variable, you either define a RECORD type and then create a variable of that type or use %ROWTYPE or %TYPE.
declare
  type rec_emp is record(
    v_city     locations.city%type,
    v_deptname departments.department_name%type);
  rec1 rec_emp;
  rec2 rec_emp;
begin
  SELECT loc.CITY, dept.DEPARTMENT_NAME
    INTO rec1
    FROM LOCATIONS loc, departments dept
   where loc.LOCATION_ID = dept.LOCATION_ID
     and dept.DEPARTMENT_ID = 20;
  SELECT loc.CITY, dept.DEPARTMENT_NAME
    INTO rec2
    FROM LOCATIONS loc, departments dept
   where loc.LOCATION_ID = dept.LOCATION_ID
     and dept.DEPARTMENT_ID = 30;
  DBMS_OUTPUT.put_line(rec1.v_city || '---' || rec1.v_deptname);
  DBMS_OUTPUT.put_line(rec2.v_city || '---' || rec2.v_deptname);
end;
DECLARE
  v_dept departments%rowtype;
BEGIN
  SELECT * INTO v_dept FROM departments WHERE department_id = 30;
  dbms_output.put_line(v_dept.department_name || v_dept.manager_id);
END;

3.多行多列用记录+集合。

declare
  type rec_dept is record(
    v_id   departments.department_id%type,
    v_name departments.department_name%type,
    v_city locations.city%type);
  type coll_dept is table of rec_dept index by pls_integer;
  v_dept coll_dept;
  i      pls_integer := 1;
begin
  for j in (select dept.department_id,dept.department_name,loc.city from
    departments dept,locations loc where dept.location_id=loc.location_id
    and dept.department_id<50) loop
    v_dept(i) := j;
    i := i + 1;
  end loop;
  for k in 1 .. v_dept.count loop
    dbms_output.put_line(v_dept(k).v_id || v_dept(k).v_name || v_dept(k).v_city);
  end loop;
end;
共有 人打赏支持
粉丝 0
博文 68
码字总数 17192
×
海贼爱音乐
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: