Oracle PLsql table用法

使用表前先什么RECORD类型来存储多列

在创建table类型来存储多行就可以实现表格的存储

例如：

---------------------------------------------------------------

--写一段程序，要求实现以下要求

--1、定义一个数组类型变量，包含字段

--employee_id,employee_name,email,salary,department_name

--2、循环输出employee_id从100到140的员工信息

--Department name:XXX

--employee_id:XX employee_name:XXX

--email:xxx salary : xxx

 

DECLARE

  TYPE emp_record_type IS RECORD(

    employee_id     employees.employee_id%TYPE,

    employee_name   employees.last_name%TYPE,

    email           employees.email%TYPE,

    salary          employees.salary%TYPE,

    department_name departments.department_name%TYPE);

  emp_record emp_record_type;

 

  TYPE rec_table_type IS TABLE OF emp_record_type INDEX BY BINARY_INTEGER;

  rec_table rec_table_type;

BEGIN

  FOR i IN 100 .. 140

  LOOP

    SELECT e.employee_id,

           e.last_name,

           e.email,

           e.salary,

           d.department_name

      INTO rec_table(i)

      FROM employees   e,

           departments d

     WHERE employee_id = i

       AND e.department_id = d.department_id;

 

  END LOOP;

  FOR i IN rec_table.first .. rec_table.last

  LOOP

    dbms_output.put_line(rec_table(i).employee_id ||" "||

                         rec_table(i).employee_name ||" "||

                         rec_table(i).email ||" "||

                         rec_table(i).salary ||" "||

                          rec_table(i).department_name);

  END LOOP;

END;

 

-------------------------------------------------------------------------------

结果如下：