Oracle PL/SQL/Object Oriented Database/Object Column
Содержание
- 1 Alter a table with user-defined object to upgrade including data
- 2 Check object table column type
- 3 Format column in the object
- 4 Nested type Column
- 5 Query column with user-defined type
- 6 Reference nested data type in select statement
- 7 Reference type column
- 8 The Object Type Column Objects
- 9 This script demonstrates column objects.
- 10 use user-defined type as the column type
- 11 Use varray in a table
Alter a table with user-defined object to upgrade including data
<source lang="sql">
SQL> SQL> create or replace
2 type person as object( 3 first_name varchar2(100), 4 last_name varchar2(100) ) 5 /
Type created. SQL> SQL> SQL> create table person_table(
2 name person, 3 age number ) 4 /
Table created. SQL> SQL> SQL> SQL> alter table person_table upgrade including data; Table altered. SQL> SQL> SQL> drop table person_table; Table dropped. SQL> SQL> SQL> drop type person; Type dropped. SQL> SQL>
</source>
Check object table column type
<source lang="sql">
SQL> SQL> create or replace type address_type
2 as object 3 ( city varchar2(30), 4 street varchar2(30), 5 state varchar2(2), 6 zip number 7 ) 8 /
Type created. SQL> create or replace type person_type
2 as object 3 ( name varchar2(30), 4 dob date, 5 home_address address_type, 6 work_address address_type 7 ) 8 /
Type created. SQL> create table people1 of person_type
2 /
Table created. SQL> desc people1
Name Null? Type ----------------------------------------------------------------------------------- -------- -------------------------------------------------------- NAME VARCHAR2(30) DOB DATE HOME_ADDRESS ADDRESS_TYPE WORK_ADDRESS ADDRESS_TYPE
SQL> select name, segcollength
2 from sys.col$ 3 where obj# = ( select object_id 4 from user_objects 5 where object_name = "PEOPLE1" ) 6 /
NAME SEGCOLLENGTH
------------
SYS_NC_OID$ 16 SYS_NC_ROWINFO$ 1 NAME 30 DOB 7 HOME_ADDRESS 1 SYS_NC00006$ 30 SYS_NC00007$ 30 SYS_NC00008$ 2 SYS_NC00009$ 22 WORK_ADDRESS 1 SYS_NC00011$ 30 SYS_NC00012$ 30 SYS_NC00013$ 2 SYS_NC00014$ 22 14 rows selected. SQL> SQL> drop table people1; Table dropped. SQL> drop type person_type; Type dropped. SQL> drop type address_type; Type dropped. SQL> --
</source>
Format column in the object
<source lang="sql">
SQL> -- Use discrete attribute names SQL> SQL> CREATE OR REPLACE TYPE address_obj as OBJECT(
2 street VARCHAR2(20), 3 city VARCHAR2(20), 4 state CHAR(2), 5 zip CHAR(5)); 6 /
Type created. SQL> SQL> SQL> CREATE TABLE emp (empno NUMBER(3),
2 name VARCHAR2(20), 3 address ADDRESS_OBJ);
Table created. SQL> SQL> SQL> INSERT INTO emp VALUES (101, "Adam", ADDRESS_OBJ("1 A St.","Mobile","AL","36608")); 1 row created. SQL> SQL> COLUMN name FORMAT a9 SQL> COLUMN empno FORMAT 999999 SQL> COLUMN address FORMAT a50 SQL> SQL> SQL> SELECT empno, name, address FROM emp;
EMPNO NAME ADDRESS(STREET, CITY, STATE, ZIP)
--------- --------------------------------------------------
101 Adam ADDRESS_OBJ("1 A St.", "Mobile", "AL", "36608")
SQL> SQL> SQL> drop table emp; Table dropped. SQL> SQL> SQL>
</source>
Nested type Column
<source lang="sql">
SQL> create or replace type addressType as object
2 ( 3 Street VARCHAR2(50), 4 City VARCHAR2(25), 5 State CHAR(2), 6 Zip NUMBER 7 ); 8 /
SQL> create or replace type personType as object
2 ( 3 Name VARCHAR2(25), 4 Address addressType 5 ); 6 /
SQL> create table CUSTOMER
2 ( 3 cid NUMBER, 4 Person personType 5 );
SQL> SQL> insert into CUSTOMER values(444, personType("Name",addressType("1 RD", "Van", "MA", "10002"))); SQL> SQL> SQL> drop table customer; SQL> drop type personType; SQL> drop type addressType; SQL>
</source>
Query column with user-defined type
<source lang="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 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> 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 as table of emp_type;
2 /
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> SQL> SQL> insert into dept_and_emp
2 select dept.*, 3 CAST( multiset( select empno, ename, job, mgr, hiredate, sal, comm from emp ) AS emp_tab_type ) 4 from dept 5 /
4 rows created. SQL> select deptno, dname, loc, d.emps AS employees
2 from dept_and_emp d 3 where deptno = 10 4 /
SQL> SQL> SQL> drop table emp cascade constraints; Table dropped. SQL> drop table dept cascade constraints; 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. SQL> SQL> --
</source>
Reference nested data type in select statement
<source lang="sql">
SQL> create type addressType as object
2 (Street VARCHAR2(50), 3 City VARCHAR2(25), 4 State CHAR(2), 5 Zip NUMBER); 6 /
SQL> SQL> create type personType as object
2 (Name VARCHAR2(25), 3 Address addressType); 4 /
SQL> SQL> SQL> create or replace type personType as object
2 (Name VARCHAR2(25), 3 Address addressType); 4 /
SQL> SQL> SQL> create table myemp
2 (cid NUMBER, 3 Person personType);
SQL> SQL> SQL> insert into myemp values
2 (1,personType("SomeName",
3 addressType("StreetValue","CityValue","ST",11111)));
SQL> SQL> SQL> select C.Person.Name,
2 C.Person.Address.City
3 from myemp C
4 where C.Person.Address.City like "C%";
PERSON.NAME PERSON.ADDRESS.CITY
-------------------------
SomeName CityValue
SQL> SQL> SQL> drop table myemp; SQL> drop type personType; SQL> drop type addressType; SQL> SQL>
</source>
Reference type column
<source lang="sql">
SQL> CREATE or replace TYPE BookType;
2 /
Type created. SQL> PROMPT Now we can create ProductType successfully Now we can create ProductType successfully SQL> SQL> CREATE OR REPLACE TYPE ProductType AS OBJECT (
2 item_id NUMBER(10), 3 num_in_stock NUMBER(10), 4 reorder_status VARCHAR2(20), 5 price REF BookType); 6 /
Type created.
</source>
The Object Type Column Objects
<source lang="sql">
SQL> CREATE OR REPLACE TYPE address_obj as OBJECT(
2 street VARCHAR2(20), 3 city VARCHAR2(20), 4 state CHAR(2), 5 zip CHAR(5)); 6 /
Type created. SQL> CREATE TABLE emp (empno NUMBER(3),
2 name VARCHAR2(20), 3 address ADDRESS_OBJ);
Table created. SQL> SQL> SQL> INSERT INTO emp VALUES (101, "Adam", ADDRESS_OBJ("1 A St.","Mobile","AL","36608")); 1 row created. SQL> SQL> SELECT * FROM emp;
EMPNO NAME
--------------------
ADDRESS(STREET, CITY, STATE, ZIP)
101 Adam
ADDRESS_OBJ("1 A St.", "Mobile", "AL", "36608")
SQL> SQL> SQL> drop table emp; Table dropped. SQL> SQL> SQL>
</source>
This script demonstrates column objects.
<source lang="sql">
SQL> SQL> SET SERVEROUTPUT ON SIZE 1000000 SQL> SQL> SQL> CREATE OR REPLACE TYPE BookType AS OBJECT (
2 rebate NUMBER (10, 4), 3 price NUMBER (10, 2), 4 MEMBER FUNCTION discount_price 5 RETURN NUMBER 6 ) 7 INSTANTIABLE FINAL; 8 /
Type created. SQL> SQL> CREATE OR REPLACE TYPE BODY BookType
2 AS 3 MEMBER FUNCTION discount_price 4 RETURN NUMBER 5 IS 6 BEGIN 7 RETURN (SELF.price * (1 - SELF.rebate)); 8 END discount_price; 9 END; 10 /
Type body created. SQL> SQL> CREATE TABLE bookTable (
2 item_id NUMBER(10) PRIMARY KEY, 3 num_in_stock NUMBER(10), 4 reorder_status VARCHAR2(20 CHAR), 5 price BookType 6 );
Table created. SQL> SQL> INSERT INTO bookTable VALUES (1, 10, "IN STOCK", BookType (.1, 75)); 1 row created. SQL> SQL> commit; Commit complete. SQL> SQL> DECLARE
2 v_price BookType;
3 BEGIN
4 SELECT price INTO v_price FROM bookTable WHERE item_id = 1;
5
6 DBMS_OUTPUT.put_line ("Price BEFORE update: " || v_price.discount_price);
7 v_price.rebate := .2;
8
9 UPDATE bookTable SET price = v_price;
10
11 DBMS_OUTPUT.put_line ("Price AFTER update: " || v_price.discount_price);
12 ROLLBACK;
13 END;
14 /
Price BEFORE update: 67.5 Price AFTER update: 60 PL/SQL procedure successfully completed. SQL> SQL> SELECT i.price.price, i.price.rebate FROM bookTable i; PRICE.PRICE PRICE.REBATE
------------
75 .1
1 row selected. SQL> SQL> SELECT i.price.discount_price() FROM bookTable i; I.PRICE.DISCOUNT_PRICE()
67.5
1 row selected. SQL> SQL> drop table bookTable; Table dropped. SQL>
</source>
use user-defined type as the column type
<source lang="sql">
SQL> SQL> create table dept
2 (deptno number(2) primary key, 3 dname varchar2(14), 4 loc varchar2(13) 5 );
Table created. SQL> SQL> SQL> create table emp
2 (empno number(4) primary key, 3 ename varchar2(10), 4 job varchar2(9), 5 mgr number(4) references emp, 6 hiredate date, 7 sal number(7, 2), 8 comm number(7, 2), 9 deptno number(2) references dept 10 );
Table 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 /
SQL> SQL> create or replace type emp_tab_type as table of emp_type;
2 /
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;
SQL> SQL> SQL> drop table dept_and_emp; Table dropped. SQL> drop table emp cascade constraints; Table dropped. SQL> drop table dept cascade constraints; Table dropped. SQL> drop type emp_tab_type; Type dropped. SQL> drop type emp_type; Type dropped. SQL> SQL> SQL> --
</source>
Use varray in a table
<source lang="sql">
SQL> CREATE or replace TYPE addressType AS OBJECT (
2 street VARCHAR2(15), 3 city VARCHAR2(15), 4 state CHAR(2), 5 zip VARCHAR2(5) 6 ); 7 /
Type created. SQL> SQL> CREATE or replace TYPE addressTypeVArray AS VARRAY(2) OF VARCHAR2(50);
2 /
SQL> SQL> CREATE or replace TYPE addressTypeNestedTable AS TABLE OF addressType;
2 /
Type created. SQL> SQL> -- SQL> CREATE GLOBAL TEMPORARY TABLE empTempTable (
2 id INTEGER PRIMARY KEY, 3 fname VARCHAR2(10), 4 lname VARCHAR2(10), 5 addresses addressTypeVArray 6 );
Table created. SQL> SQL> CREATE TABLE empTable (
2 id INTEGER PRIMARY KEY, 3 fname VARCHAR2(10), 4 lname VARCHAR2(10), 5 addresses addressTypeNestedTable 6 ) 7 NESTED TABLE 8 addresses 9 STORE AS 10 nested_addresses2 TABLESPACE users;
Table created. SQL> SQL> SQL> -- equal/not equal example SQL> CREATE OR REPLACE PROCEDURE equal_example AS
2 TYPE charTable IS TABLE OF VARCHAR2(10);
3 emp1 charTable;
4 emp2 charTable;
5 emp3 charTable;
6 result BOOLEAN;
7 BEGIN
8 emp1 := charTable("A", "B", "C");
9 emp2 := charTable("A", "B", "C");
10 emp3 := charTable("B", "C", "D");
11
12 result := emp1 = emp2;
13 IF result THEN
14 DBMS_OUTPUT.PUT_LINE("emp1 equal to emp2");
15 END IF;
16
17 END equal_example;
18 /
Procedure created. SQL> SQL> drop type addressType force; Type dropped. SQL> drop type addressTypeVArray force; Type dropped. SQL> drop TYPE addressTypeNestedTable force; Type dropped. SQL> drop TABLE empTable; Table dropped.
</source>
