Oracle PL/SQL Tutorial/Object Oriented/Update
Версия от 13:45, 26 мая 2010; (обсуждение)
ORA-22908: reference to NULL table value
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 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> 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> create or replace type emp_tab_type
2 as table of emp_type
3 /
Type created.
SQL> create table dept_and_emp
2 (deptno number(2) primary key,
3 dname varchar2(14),
4 loc varchar2(13),
5 emps emp_tab_type
6 )
7 nested table emps store as emps_nt;
Table created.
SQL>
SQL> alter table emps_nt add constraint emps_empno_unique unique(empno)
2 /
Table altered.
SQL>
SQL> insert into dept_and_emp
2 select dept.*,
3 CAST( multiset( select empno, ename, job, mgr, hiredate, sal, comm from emp
4 where emp.deptno = dept.deptno ) AS emp_tab_type )
5 from dept
6 /
4 rows created.
SQL>
SQL>
SQL>
SQL>
SQL> update table( select emps from dept_and_emp where deptno = 1) set comm = 100
2 /
update table( select emps from dept_and_emp where deptno = 1) set comm = 100
*
ERROR at line 1:
ORA-22908: reference to NULL table value
SQL>
SQL>
SQL> drop table dept cascade constraint;
Table dropped.
SQL> drop table emp;
Table dropped.
SQL> drop table dept_and_emp;
Table dropped.
SQL> drop type emp_tab_type;
Type dropped.
SQL> drop type emp_type;
Type dropped.
Update user-defined type column
SQL>
SQL>
SQL> create table emp
2 ( empno NUMBER(4) constraint E_PK primary key
3 , ename VARCHAR2(8)
4 , init VARCHAR2(5)
5 , job VARCHAR2(8)
6 , mgr NUMBER(4)
7 , bdate DATE
8 , sal NUMBER(6,2)
9 , comm NUMBER(6,2)
10 , deptno NUMBER(2) default 10
11 ) ;
Table created.
SQL> insert into emp values(1,"Tom","N", "TRAINER", 13,date "1965-12-17", 800 , NULL, 20);
1 row created.
SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30);
1 row created.
SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30);
1 row created.
SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20);
1 row created.
SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30);
1 row created.
SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30);
1 row created.
SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10);
1 row created.
SQL> insert into emp values(8,"Smart","SCJ", "TRAINER", 4,date "1959-11-26", 3000, NULL, 20);
1 row created.
SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10);
1 row created.
SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30);
1 row created.
SQL> insert into emp values(11,"Ana","AA", "TRAINER", 8,date "1966-12-30", 1100, NULL, 20);
1 row created.
SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30);
1 row created.
SQL> insert into emp values(13,"Fake","MG", "TRAINER", 4,date "1959-02-13", 3000, NULL, 20);
1 row created.
SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10);
1 row created.
SQL>
SQL>
SQL>
SQL> create table e
2 as
3 select empno, ename, init, mgr, deptno
4 from emp;
Table created.
SQL>
SQL>
SQL> create or replace type numberlist_t
2 as varray(4) of varchar2(20);
3 /
Type created.
SQL>
SQL> column numlist format a60
SQL>
SQL> alter table e add (numlist numberlist_t);
Table altered.
SQL>
SQL> describe e
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(8)
INIT VARCHAR2(5)
MGR NUMBER(4)
DEPTNO NUMBER(2)
NUMLIST NUMBERLIST_T
SQL>
SQL> update e
2 set numlist = numberlist_t("1234","06-78532","029-8532")
3 where empno = 9;
1 row updated.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> drop table e;
Table dropped.
SQL> drop table emp;
Table dropped.
SQL>
Updating a Row in the Table with object type column
SQL>
SQL> CREATE Or Replace TYPE ProductType AS OBJECT (
2 id NUMBER,
3 name VARCHAR2(15),
4 description VARCHAR2(22),
5 price NUMBER(5, 2),
6 days_valid NUMBER
7 )
8 /
Type created.
SQL> CREATE TABLE products (
2 product ProductType,
3 count NUMBER
4 )
5 /
Table created.
SQL>
SQL> INSERT INTO products (product,count) VALUES (
2 ProductType(1, "AA", "BBB", 3.95, 10),50
3 );
1 row created.
SQL>
SQL> INSERT INTO products (product,count) VALUES (
2 ProductType(2, "CC", "DDDD", 2.99, 5),25
3 );
1 row created.
SQL>
SQL> SELECT product
2 FROM products;
PRODUCT(ID, NAME, DESCRIPTION, PRICE, DAYS_VALID)
------------------------------------------------------
PRODUCTTYPE(1, "AA", "BBB", 3.95, 10)
PRODUCTTYPE(2, "CC", "DDDD", 2.99, 5)
SQL>
SQL>
SQL> UPDATE products p
2 SET p.product.description = "new D"
3 WHERE p.product.id = 1;
1 row updated.
SQL>
SQL> SELECT product
2 FROM products;
PRODUCT(ID, NAME, DESCRIPTION, PRICE, DAYS_VALID)
--------------------------------------------------------
PRODUCTTYPE(1, "AA", "new D", 3.95, 10)
PRODUCTTYPE(2, "CC", "DDDD", 2.99, 5)
SQL>
SQL>
SQL> drop table products;
Table dropped.
SQL>