Oracle PL/SQL Tutorial/Collections/Table of Type
Содержание
Forall in value of table of type
<source lang="sql">
SQL> SQL> CREATE TABLE employee (
2 employee_id NUMBER, 3 last_name VARCHAR2(30), 4 first_name VARCHAR2(30), 5 salary NUMBER);
Table created. SQL> INSERT INTO employee (employee_id, last_name, first_name, salary)VALUES (1, "G", "J", 100000); 1 row created. SQL> SQL> INSERT INTO employee (employee_id, last_name, first_name, salary)VALUES (2, "G", "H", 100000); 1 row created. SQL> SQL> SQL> SQL> SELECT employee_id FROM employee WHERE salary = 10000; no rows selected SQL> SQL> DECLARE
2 TYPE employee_aat IS TABLE OF employee.employee_id%TYPE INDEX BY PLS_INTEGER; 3 4 l_employees employee_aat; 5 6 TYPE indices_aat IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER; 7 8 l_employee_indices indices_aat; 9 BEGIN 10 l_employees (-77) := 7820; 11 l_employees (13067) := 7799; 12 l_employees (99999999) := 7369; 13 -- 14 l_employee_indices (100) := -77; 15 l_employee_indices (200) := 99999999; 16 -- 17 FORALL l_index IN VALUES OF l_employee_indices 18 UPDATE employee SET salary = 10000 19 WHERE employee_id = l_employees (l_index); 20 END; 21 /
PL/SQL procedure successfully completed. SQL> SQL> SELECT employee_id FROM employee WHERE salary = 10000; no rows selected SQL> SQL> SQL> drop table employee; Table dropped. SQL> SQL></source>
Select user-defined type into table collection of user-defined types
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10), 3 JOB VARCHAR2(9), 4 MGR NUMBER(4), 5 HIREDATE DATE, 6 SAL NUMBER(7, 2), 7 COMM NUMBER(7, 2), 8 DEPTNO NUMBER(2));
Table created. SQL> SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> SQL> SQL> CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) ); Table created. SQL> SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK"); 1 row created. SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS"); 1 row created. SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO"); 1 row created. SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON"); 1 row created. SQL> SQL> SQL> create or replace type emp_type
2 as object 3 (empno number(4), 4 ename varchar2(10), 5 job varchar2(9), 6 mgr number(4), 7 hiredate date, 8 sal number(7, 2), 9 comm number(7, 2) 10 ); 11 /
Type created. SQL> SQL> SQL> create or replace type emp_tab_type
2 as table of emp_type 3 /
Type created. SQL> SQL> SQL> create or replace type dept_type
2 as object 3 ( deptno number(2), 4 dname varchar2(14), 5 loc varchar2(13), 6 emps emp_tab_type 7 ) 8 /
Type created. SQL> SQL> create or replace view dept_or
2 of dept_type 3 with object identifier(deptno) 4 as 5 select deptno, dname, loc, 6 cast ( multiset ( 7 select empno, ename, job, mgr, hiredate, sal, comm 8 from emp 9 where emp.deptno = dept.deptno ) 10 as emp_tab_type ) 11 from dept 12 /
View created. SQL> SQL> declare
2 l_emps emp_tab_type; 3 begin 4 select p.emps into l_emps from dept_or p where deptno = 10; 5 6 for i in 1 .. l_emps.count 7 loop 8 l_emps(i).ename := lower(l_emps(i).ename); 9 end loop; 10 11 update dept_or set emps = l_emps where deptno = 10; 12 end; 13 /
declare
ERROR at line 1: ORA-01733: virtual column not allowed here ORA-06512: at line 11
SQL> SQL> drop type dept_type; Type dropped. SQL> SQL> drop type emp_tab_type; Type dropped. SQL> SQL> drop type emp_type; Type dropped. SQL> SQL> drop table emp; Table dropped. SQL> SQL> drop table dept; Table dropped. SQL> SQL></source>
Table of type
<source lang="sql">
SQL> SQL> create table employee (
2 id number not null, 3 employee_type_id number not null, 4 external_id varchar2(30) not null, 5 first_name varchar2(30) not null, 6 middle_name varchar2(30), 7 last_name varchar2(30) not null, 8 name varchar2(100) not null, 9 birth_date date not null, 10 gender_id number not null );
Table created. SQL> SQL> SQL> SQL> SQL> declare
2 TYPE name_table is table of employee.name%TYPE index by binary_integer; 3 TYPE name_record is record ( dim2 name_table ); 4 TYPE dim1 is table of name_record index by binary_integer; 5 t_dim1 dim1; 6 begin 7 t_dim1(1).dim2(1) := "AAA"; 8 t_dim1(1).dim2(2) := "BBB"; 9 10 t_dim1(2).dim2(1) := "CCC"; 11 t_dim1(2).dim2(2) := "DDD"; 12 13 dbms_output.put_line (t_dim1(1).dim2(1)); 14 dbms_output.put_line (t_dim1(1).dim2(2)); 15 dbms_output.put_line (t_dim1(2).dim2(1)); 16 dbms_output.put_line (t_dim1(2).dim2(2)); 17 end; 18 /
AAA BBB CCC DDD PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> drop table employee; Table dropped. SQL> SQL></source>
Table of user-defined types
<source lang="sql">
SQL> CREATE TYPE SKILL_TYPE AS OBJECT (
2 SKILL_ID NUMBER(7,0), 3 SKILL_NAME VARCHAR2(20), 4 SKILL_DESC VARCHAR2(100) 5 ); 6 /
Type created. SQL> SQL> CREATE TYPE SKILL_TABLE_TYPE AS TABLE OF SKILL_TYPE;
2 /
Type created. SQL> SQL> DECLARE
2 SKILL_TABLE SKILL_TABLE_TYPE; 3 BEGIN 4 SKILL_TABLE := SKILL_TABLE_TYPE(); 5 SKILL_TABLE.EXTEND(5); 6 SKILL_TABLE(1) := SKILL_TYPE(100,"ROLLERBLADING","REQUIRED FOR TAPE MOUNTING") ; 7 SKILL_TABLE(2) := SKILL_TYPE(101,"NINTENDO","USAF - CLASSIFIED") ; 8 SKILL_TABLE(3) := SKILL_TYPE(102,"KARATE","MANAGEMENT TRAINING") ; 9 END; 10 /
PL/SQL procedure successfully completed. SQL> SQL> DROP TYPE SKILL_TYPE FORCE; Type dropped. SQL> DROP TYPE SKILL_TABLE_TYPE; Type dropped.</source>
Use LOOP to output all elements in a table collection
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE birthday (
2 party_date DATE, 3 fav_present VARCHAR2(100) 4 );
Table created. SQL> SQL> INSERT INTO birthday VALUES ("01-OCT-92", "A"); 1 row created. SQL> INSERT INTO birthday VALUES ("01-OCT-98", "B"); 1 row created. SQL> SQL> DECLARE
2 TYPE name_tt IS TABLE OF birthday.fav_present%TYPE INDEX BY BINARY_INTEGER; 3 the_best name_tt; 4 indx PLS_INTEGER; 5 BEGIN 6 indx := the_best.FIRST; 7 LOOP 8 EXIT WHEN indx IS NULL; 9 DBMS_OUTPUT.PUT_LINE (the_best(indx)); 10 indx := the_best.NEXT (indx); 11 END LOOP; 12 END; 13 /
PL/SQL procedure successfully completed. SQL> SQL> drop table birthday; Table dropped. SQL> SQL></source>
Use of PL/SQL tables of types
<source lang="sql">
SQL> SQL> SQL> create table employee (
2 id number not null, 3 employee_type_id number not null, 4 external_id varchar2(30) not null, 5 first_name varchar2(30) not null, 6 middle_name varchar2(30), 7 last_name varchar2(30) not null, 8 name varchar2(100) not null, 9 birth_date date not null, 10 gender_id number not null );
Table created. SQL> SQL> declare
2
3
4 TYPE name_table IS TABLE OF employee.name%TYPE INDEX BY BINARY_INTEGER;
5
6 t_name name_table;
7
8 n_name binary_integer;
9
10 begin
11 t_name(1) := "AAA";
12 t_name(10) := "BBB";
13
14 DBMS_OUTPUT.PUT_LINE(t_name(1));
15 DBMS_OUTPUT.PUT_LINE(t_name(10));
16
17 DBMS_OUTPUT.PUT_LINE("There are "||t_name.count()||" elements.");
18 n_name := t_name.first();
19
20 DBMS_OUTPUT.PUT_LINE("The first element is "||n_name||".");
21 n_name := t_name.next(n_name);
22
23 DBMS_OUTPUT.PUT_LINE("The next element is "||n_name||".");
24 n_name := t_name.last();
25
26 DBMS_OUTPUT.PUT_LINE("The last element is "||n_name||".");
27 n_name := t_name.prior(n_name);
28
29 DBMS_OUTPUT.PUT_LINE("The prior element is "||n_name||".");
30
31 if t_name.exists(1) then
32 DBMS_OUTPUT.PUT_LINE("Element 1 exists.");
33 end if;
34
35 DBMS_OUTPUT.PUT_LINE("I""m deleting element 10");
36
37 t_name.delete(10);
38
39 DBMS_OUTPUT.PUT_LINE("There are "||t_name.count()||" elements.");
40
41 if not t_name.exists(10) then
42 DBMS_OUTPUT.PUT_LINE("Element 10 no longer exists.");
43 end if;
44
45 DBMS_OUTPUT.PUT_LINE("There are "||t_name.count()||" elements.");
46
47 DBMS_OUTPUT.PUT_LINE("I""m deleting all elements");
48
49 t_name.delete();
50
51 DBMS_OUTPUT.PUT_LINE("There are "||t_name.count()||" elements.");
52
53 end;
54 /
AAA BBB There are 2 elements. The first element is 1. The next element is 10. The last element is 10. The prior element is 1. Element 1 exists. I"m deleting element 10 There are 1 elements. Element 10 no longer exists. There are 1 elements. I"m deleting all elements There are 0 elements. PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table employee; Table dropped. SQL> SQL> SQL></source>
