Oracle PL/SQL/PL SQL/Table of rowtype
Содержание
Close a nested table type
<source lang="sql">
SQL> SQL> create table department
2 ( dept_id number(2), 3 dept_name varchar2(14), 4 no_of_emps varchar2(13) 5 ) 6 /
Table created. SQL> SQL> INSERT INTO department VALUES (10, "ACCOUNTING", "NEW YORK"); 1 row created. SQL> INSERT INTO department VALUES (20, "RESEARCH", "DALLAS"); 1 row created. SQL> INSERT INTO department VALUES (30, "SALES", "CHICAGO"); 1 row created. SQL> INSERT INTO department VALUES (40, "OPERATIONS", "BOSTON"); 1 row created. SQL> SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE
2 CURSOR all_depts IS 3 SELECT * 4 FROM department 5 ORDER BY dept_name; 6 7 TYPE dept_table IS TABLE OF department%ROWTYPE; 8 9 depts dept_table; 10 depts_max PLS_INTEGER; 11 inx1 PLS_INTEGER; 12 BEGIN 13 depts_max := 0; 14 15 depts := dept_table (); 16 17 FOR dept IN all_depts LOOP 18 depts_max := depts_max + 1; 19 depts.extend; 20 depts(depts_max).dept_id := dept.dept_id; 21 depts(depts_max).dept_name := dept.dept_name; 22 depts(depts_max).no_of_emps := dept.no_of_emps; 23 END LOOP; 24 25 depts.extend(5,1); 26 27 FOR inx1 IN 1..depts_max+5 LOOP 28 DBMS_OUTPUT.PUT_LINE (depts(inx1).dept_id ||" " || depts(inx1).dept_name); 29 END LOOP; 30 END; 31 /
10 ACCOUNTING 40 OPERATIONS 20 RESEARCH 30 SALES 10 ACCOUNTING 10 ACCOUNTING 10 ACCOUNTING 10 ACCOUNTING 10 ACCOUNTING PL/SQL procedure successfully completed. SQL> SQL> drop table department; Table dropped. SQL> SQL> SQL> --
</source>
Declare an index-by table variable to hold the employee records in cursor
<source lang="sql">
SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON 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> 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> DECLARE
2 --Declare a cursor that returns all employee records. 3 CURSOR all_emps IS 4 SELECT * 5 FROM emp 6 ORDER BY ename; 7 8 --Define an index-by table type. 9 TYPE emp_table IS TABLE OF emp%ROWTYPE 10 INDEX BY BINARY_INTEGER; 11 12 emps emp_table; 13 emps_max BINARY_INTEGER; 14 BEGIN 15 emps_max := 0; 16 17 FOR emp IN all_emps LOOP 18 emps_max := emps_max + 1; 19 20 emps(emps_max).empno := emp.empno; 21 emps(emps_max).ename := emp.ename; 22 emps(emps_max).JOB := emp.JOB; 23 emps(emps_max).HIREDATE := emp.HIREDATE; 24 emps(emps_max).DEPTNO := emp.DEPTNO; 25 END LOOP; 26 END; 27 /
PL/SQL procedure successfully completed. SQL> SQL> drop table emp; Table dropped. SQL> --
</source>
Loop through a table collection
<source lang="sql">
SQL> SQL> create table department
2 ( dept_id number(2), 3 dept_name varchar2(14), 4 no_of_emps varchar2(13) 5 ) 6 /
Table created. SQL> SQL> INSERT INTO department VALUES (10, "ACCOUNTING", "NEW YORK"); 1 row created. SQL> INSERT INTO department VALUES (20, "RESEARCH", "DALLAS"); 1 row created. SQL> INSERT INTO department VALUES (30, "SALES", "CHICAGO"); 1 row created. SQL> INSERT INTO department VALUES (40, "OPERATIONS", "BOSTON"); 1 row created. SQL> SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE
2 CURSOR all_depts IS 3 SELECT * 4 FROM department 5 ORDER BY dept_name; 6 7 TYPE dept_table IS TABLE OF department%ROWTYPE; 8 9 depts dept_table; 10 depts_max PLS_INTEGER; 11 inx1 PLS_INTEGER; 12 BEGIN 13 depts_max := 0; 14 15 depts := dept_table (); 16 17 FOR dept IN all_depts LOOP 18 depts_max := depts_max + 1; 19 depts.extend; 20 depts(depts_max).dept_id := dept.dept_id; 21 depts(depts_max).dept_name := dept.dept_name; 22 depts(depts_max).no_of_emps := dept.no_of_emps; 23 END LOOP; 24 25 depts.extend(5,1); 26 27 FOR inx1 IN 1..depts_max+5 LOOP 28 DBMS_OUTPUT.PUT_LINE (depts(inx1).dept_id ||" " || depts(inx1).dept_name); 29 END LOOP; 30 31 depts.trim(5); 32 33 depts.delete(1); 34 35 DBMS_OUTPUT.PUT_LINE(depts.count); 36 37 FOR inx1 IN 1..depts_max+5 LOOP 38 IF depts.exists(inx1) THEN 39 DBMS_OUTPUT.PUT_LINE ( 40 depts(inx1).dept_id || 41 " " || depts(inx1).dept_name); 42 END IF; 43 END LOOP; 44 45 END; 46 /
10 ACCOUNTING 40 OPERATIONS 20 RESEARCH 30 SALES 10 ACCOUNTING 10 ACCOUNTING 10 ACCOUNTING 10 ACCOUNTING 10 ACCOUNTING 3 40 OPERATIONS 20 RESEARCH 30 SALES PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table department; Table dropped. SQL> --
</source>
table of employees%rowtype index by binary_integer
<source lang="sql">
SQL> SQL> CREATE TABLE employees
2 ( employee_id number(10) not null, 3 last_name varchar2(50) not null, 4 email varchar2(30), 5 hire_date date, 6 job_id varchar2(30), 7 department_id number(10), 8 salary number(6), 9 manager_id number(6) 10 );
Table created. SQL> SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id)
2 values ( 1001, "Lawson", "lawson@g.ru", "01-JAN-2002","MGR", 30000,1 ,1004);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
2 values ( 1002, "Wells", "wells@g.ru", "01-JAN-2002", "DBA", 20000,2, 1005 );
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
2 values( 1003, "Bliss", "bliss@g.ru", "01-JAN-2002", "PROG", 24000,3 ,1004);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1004, "Kyte", "tkyte@a.ru", SYSDATE-3650, "MGR",25000 ,4, 1005);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1005, "Viper", "sdillon@a .ru", SYSDATE, "PROG", 20000, 1, 1006);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id)
2 values( 1006, "Beck", "clbeck@g.ru", SYSDATE, "PROG", 20000, 2, null);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1007, "Java", "java01@g.ru", SYSDATE, "PROG", 20000, 3, 1006);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1008, "Oracle", "wvelasq@g.ru", SYSDATE, "DBA", 20000, 4, 1006);
1 row created. SQL> SQL> select * from employees; EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID DEPARTMENT_ID SALARY MANAGER_ID
-------------------------------------------------- ------------------------------ --------- ------------------------------ ------------- ---------- ----------
1001 Lawson lawson@g.ru 01-JAN-02 MGR 1 30000 1004
1002 Wells wells@g.ru 01-JAN-02 DBA 2 20000 1005
1003 Bliss bliss@g.ru 01-JAN-02 PROG 3 24000 1004
1004 Kyte tkyte@a.ru 14-JUN-98 MGR 4 25000 1005
1005 Viper sdillon@a .ru 11-JUN-08 PROG 1 20000 1006
1006 Beck clbeck@g.ru 11-JUN-08 PROG 2 20000
1007 Java java01@g.ru 11-JUN-08 PROG 3 20000 1006
1008 Oracle wvelasq@g.ru 11-JUN-08 DBA 4 20000 1006
8 rows selected. SQL> SQL> set serverout on SQL> SQL> declare
2 type myTextTableType is table of varchar2(200)
3 index by binary_integer;
4
5 type my_emp_table_type is table of employees%rowtype
6 index by binary_integer;
7
8 myText_table myTextTableType;
9 myEmpTable my_emp_table_type;
10 begin
11 myText_table(1) := "Some varchar2 value";
12 myText_table(2) := "Another varchar2 value";
13
14 myEmpTable(10).employee_id := 10;
15 myEmpTable(10).last_name := "D";
16 myEmpTable(10).email := "s@s.ru";
17 myEmpTable(10).hire_date := to_date("01-NOV-1996");
18 myEmpTable(10).job_id := "CLERK";
19
20 myEmpTable(20).employee_id := 20;
21 myEmpTable(20).last_name := "B";
22 myEmpTable(20).email := "c@s.ru";
23 myEmpTable(20).hire_date := to_date("01-JAN-1996");
24 myEmpTable(20).job_id := "CLERK";
25
26 dbms_output.put ("We have " ||myText_table.count|| " varchar2""s ");
27 dbms_output.put_line("and " ||myEmpTable.count|| " employees.");
28 dbms_output.put_line("-");
29 dbms_output.put_line("vc2(1)="||myText_table(1));
30 dbms_output.put_line("vc2(2)="||myText_table(2));
31 dbms_output.put_line("-");
32 dbms_output.put_line("myEmpTable(10)="||myEmpTable(10).last_name);
33 dbms_output.put_line("myEmpTable(20)="||myEmpTable(20).last_name);
34
35 end;
36 /
We have 2 varchar2"s and 2 employees. - vc2(1)=Some varchar2 value vc2(2)=Another varchar2 value - myEmpTable(10)=D myEmpTable(20)=B PL/SQL procedure successfully completed. SQL> SQL> drop table employees; Table dropped. SQL> SQL> SQL>
</source>
Use table of rowtype to define data type
<source lang="sql">
SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> SQL> set serverout on SQL> SQL> declare
2 type myTextTableType is table of varchar2(200) index by binary_integer;
3
4 type my_emp_table_type is table of employee%rowtype index by binary_integer;
5
6 l_text_table myTextTableType;
7 myEmpTable my_emp_table_type;
8 begin
9 l_text_table(1) := "varchar2 value";
10 l_text_table(2) := "varchar2 value 2";
11
12 myEmpTable(10).id := 10;
13 myEmpTable(10).first_name := "S";
14 myEmpTable(10).last_name := "D";
15
16 myEmpTable(20).id := 20;
17 myEmpTable(20).first_name := "Chris";
18 myEmpTable(20).last_name := "Beck";
19
20 dbms_output.put ("We have " ||l_text_table.count|| " varchar2""s ");
21 dbms_output.put_line("and " ||myEmpTable.count|| " employees.");
22 dbms_output.put_line("-");
23 dbms_output.put_line("vc2(1)="||l_text_table(1));
24 dbms_output.put_line("vc2(2)="||l_text_table(2));
25 dbms_output.put_line("-");
26 dbms_output.put_line("myEmpTable(10)="||myEmpTable(10).first_name);
27 dbms_output.put_line("myEmpTable(20)="||myEmpTable(20).first_name);
28
29 end;
30 /
We have 2 varchar2"s and 2 employees. - vc2(1)=varchar2 value vc2(2)=varchar2 value 2 - myEmpTable(10)=S myEmpTable(20)=Chris PL/SQL procedure successfully completed. SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL> SQL>
</source>
