Oracle PL/SQL/Object Oriented Database/Update
Содержание
UPDATE a Table that Contains Row Objects (TCRO)
SQL> -- UPDATE a Table that Contains Row Objects (TCRO)
SQL>
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> CREATE TABLE address_table OF ADDRESS_OBJ;
Table created.
SQL>
SQL> CREATE TABLE client (name VARCHAR2(20),
2 address REF address_obj scope is address_table);
Table created.
SQL>
SQL>
SQL>
SQL> INSERT INTO client VALUES ("Jones",null);
1 row created.
SQL>
SQL>
SQL> UPDATE client SET address =
2 (SELECT REF(aa)
3 FROM address_table aa
4 WHERE aa.city LIKE "Mob%")
5 WHERE name = "Jones";
1 row updated.
SQL>
SQL>
SQL> SELECT * FROM client;
NAME ADDRESS
--------- --------------------------------------------------
Jones
SQL>
SQL>
SQL>
SQL> drop table client;
Table dropped.
SQL>
SQL> drop table address_table;
Table dropped.
SQL>
SQL>
SQL>
update calls the constructor in the SET clause
SQL> --.
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE address AS OBJECT (
2 street_1 VARCHAR2(40),
3 street_2 VARCHAR2(40),
4 city VARCHAR2(40),
5 state_abbr VARCHAR2(2),
6 zip_code VARCHAR2(5),
7 phone_number VARCHAR2(10),
8 MEMBER PROCEDURE ChangeAddress (
9 st_1 IN VARCHAR2, st_2 IN VARCHAR2, cty IN VARCHAR2,
10 state IN VARCHAR2, zip IN VARCHAR2),
11 MEMBER FUNCTION getStreet (line_no IN number) RETURN VARCHAR2,
12 MEMBER FUNCTION getCity RETURN VARCHAR2,
13 MEMBER FUNCTION getStateAbbr RETURN VARCHAR2,
14 MEMBER FUNCTION getPostalCode RETURN VARCHAR2,
15 MEMBER FUNCTION getPhone RETURN VARCHAR2,
16 MEMBER PROCEDURE setPhone (newPhone IN VARCHAR2)
17 );
18 /
Type created.
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE BODY address AS
2 MEMBER PROCEDURE ChangeAddress (
3 st_1 IN VARCHAR2, st_2 IN VARCHAR2, cty IN VARCHAR2,
4 state IN VARCHAR2, zip IN VARCHAR2) IS
5 BEGIN
6 IF (st_1 IS NULL) THEN
18 RAISE_application_error(-20001,"The new Address is invalid.");
19 ELSE
20 street_1 := st_1;
21 street_2 := st_2;
22 city := cty;
23 state_abbr := upper(state);
24 zip_code := zip;
25 END IF;
26 END;
27
28 MEMBER FUNCTION getStreet (line_no IN number)
29 RETURN VARCHAR2 IS
30 BEGIN
31 IF line_no = 1 THEN
32 RETURN street_1;
33 ELSIF line_no = 2 THEN
34 RETURN street_2;
35 ELSE
36 RETURN " ";
37 END IF;
38 END;
39
40 MEMBER FUNCTION getCity RETURN VARCHAR2 IS
41 BEGIN
42 RETURN city;
43 END;
44
45 MEMBER FUNCTION getStateAbbr RETURN VARCHAR2 IS
46 BEGIN
47 RETURN state_abbr;
48 END;
49
50 MEMBER FUNCTION getPostalCode RETURN VARCHAR2 IS
51 BEGIN
52 RETURN zip_code;
53 END;
54
55 MEMBER FUNCTION getPhone RETURN VARCHAR2 IS
56 BEGIN
57 RETURN phone_number;
58 END;
59
60 MEMBER PROCEDURE setPhone (newPhone IN VARCHAR2) IS
61 BEGIN
62 phone_number := newPhone;
63 END;
64 END;
65 /
Type body created.
SQL>
SQL> set serveroutput on
SQL>
SQL> set echo on
SQL>
SQL> CREATE OR REPLACE TYPE appartment AS OBJECT (
2 BldgName VARCHAR2(40),
3 BldgAddress address,
4 BldgMgr INTEGER,
5 MEMBER PROCEDURE ChangeMgr (NewMgr IN INTEGER),
6 ORDER MEMBER FUNCTION Compare (OtherAppartment IN appartment)
7 RETURN INTEGER
8 );
9 /
Type created.
SQL>
SQL> CREATE OR REPLACE TYPE BODY appartment AS
2 MEMBER PROCEDURE ChangeMgr(NewMgr IN INTEGER) IS
3 BEGIN
4 BldgMgr := NewMgr;
5 END;
6
7 ORDER MEMBER FUNCTION Compare (OtherAppartment IN appartment)
8 RETURN INTEGER IS
9 BldgName1 VARCHAR2(40);
10 BldgName2 VARCHAR2(40);
11 BEGIN
12 BldgName1 := upper(ltrim(rtrim(BldgName)));
13 BldgName2 := upper(ltrim(rtrim(OtherAppartment.BldgName)));
14
15 IF BldgName1 = BldgName2 THEN
16 RETURN 0;
17 ELSIF BldgName1 < BldgName2 THEN
18 RETURN -1;
19 ELSE
20 RETURN 1;
21 END IF;
22 END;
23 END;
24 /
Type body created.
SQL>
SQL> CREATE TABLE appartments OF appartment;
Table created.
SQL>
SQL> set echo on
SQL>
SQL> INSERT INTO appartments
2 values (appartment("Victor Building",
3 address("203 Washington Square"," ","Lansing",
4 "MI","48823"," "),
5 597));
1 row created.
SQL>
SQL> INSERT INTO appartments
2 values (appartment("East Storage Shed",
3 address("1400 A Rd","","Lansing","MI","48823",""),
4 598));
1 row created.
SQL> INSERT INTO appartments
2 values (appartment("Headquarters Building",
3 address("150 West Jefferson","","Detroit","MI","48226",""),
4 599));
1 row created.
SQL>
SQL>
SQL>
SQL> UPDATE appartments b
2 SET b = appartment(BldgName,BldgAddress,598)
3 WHERE BldgName = "Victor Building";
1 row updated.
SQL>
SQL> drop table appartments;
Table dropped.
SQL> drop type appartment;
Type dropped.
SQL>
SQL> --
UPDATE Data in a Table of Row Objects
SQL> -- UPDATE Data in a Table of Row Objects
SQL>
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> CREATE TABLE address_table OF ADDRESS_OBJ;
Table created.
SQL>
SQL> INSERT INTO address_table VALUES ("4 D St.", "Gulf Breeze","FL","32563");
1 row created.
SQL>
SQL> SELECT *
2 FROM address_table;
STREET CITY ST ZIP
-------------------- -------------------- -- -----
4 D St. Gulf Breeze FL 32563
SQL>
SQL>
SQL>
SQL> UPDATE address_table
2 SET zip = 32514
3 WHERE zip = 32504;
0 rows updated.
SQL>
SQL> UPDATE address_table
2 SET street = "11 A Dr"
3 WHERE city LIKE "Gulf%";
1 row updated.
SQL>
SQL>
SQL> SELECT *
2 FROM address_table;
STREET CITY ST ZIP
-------------------- -------------------- -- -----
11 A Dr Gulf Breeze FL 32563
SQL>
SQL>
SQL> drop table address_table;
Table dropped.
SQL>
SQL>
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>
Update using the PL/SQL variables
SQL>
SQL> CREATE OR REPLACE TYPE StudentList AS TABLE OF NUMBER(5);
2 /
Type created.
SQL>
SQL>
SQL> CREATE TABLE library_catalog (
2 catalog_number NUMBER(4),
3 num_copies NUMBER,
4 num_out NUMBER,
5 checked_out StudentList)
6 NESTED TABLE checked_out STORE AS co_tab;
Table created.
SQL>
SQL>
SQL> DECLARE
2 v_StudentList1 StudentList := StudentList(10000, 10002, 10003);
3 v_StudentList2 StudentList := StudentList(10000, 10002, 10003);
4 v_StudentList3 StudentList := StudentList(10000, 10002, 10003);
5 BEGIN
6 -- First insert rows with NULL nested tables.
7 INSERT INTO library_catalog (catalog_number, num_copies, num_out)
8 VALUES (1000, 20, 3);
9 INSERT INTO library_catalog (catalog_number, num_copies, num_out)
10 VALUES (1001, 20, 3);
11 INSERT INTO library_catalog (catalog_number, num_copies, num_out)
12 VALUES (1002, 10, 3);
13 INSERT INTO library_catalog (catalog_number, num_copies, num_out)
14 VALUES (2001, 50, 0);
15 INSERT INTO library_catalog (catalog_number, num_copies, num_out)
16 VALUES (3001, 5, 0);
17 INSERT INTO library_catalog (catalog_number, num_copies, num_out)
18 VALUES (3002, 5, 1);
19
20 UPDATE library_catalog
21 SET checked_out = v_StudentList1
22 WHERE catalog_number = 1000;
23 UPDATE library_catalog
24 SET checked_out = v_StudentList2
25 WHERE catalog_number = 1001;
26 UPDATE library_catalog
27 SET checked_out = v_StudentList3
28 WHERE catalog_number = 1002;
29
30 -- And update the last row using a new variable.
31 UPDATE library_catalog
32 SET checked_out = StudentList(10009)
33 WHERE catalog_number = 3002;
34 END;
35 /
PL/SQL procedure successfully completed.
SQL>
SQL> DELETE FROM library_catalog
2 WHERE catalog_number = 3001;
1 row deleted.
SQL>
SQL>
SQL>
SQL> select * from library_catalog;
CATALOG_NUMBER NUM_COPIES NUM_OUT
-------------- ---------- ----------
CHECKED_OUT
--------------------------------------------------------------------------------
1000 20 3
STUDENTLIST(10000, 10002, 10003)
1001 20 3
STUDENTLIST(10000, 10002, 10003)
1002 10 3
STUDENTLIST(10000, 10002, 10003)
CATALOG_NUMBER NUM_COPIES NUM_OUT
-------------- ---------- ----------
CHECKED_OUT
--------------------------------------------------------------------------------
2001 50 0
3002 5 1
STUDENTLIST(10009)
SQL>
SQL> drop table library_catalog;
Table dropped.
SQL>
SQL>
Use type constructor in update statement
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE address AS OBJECT (
2 street_1 VARCHAR2(40),
3 street_2 VARCHAR2(40),
4 city VARCHAR2(40),
5 state_abbr VARCHAR2(2),
6 zip_code VARCHAR2(5),
7 phone_number VARCHAR2(10),
8 MEMBER PROCEDURE ChangeAddress (
9 st_1 IN VARCHAR2, st_2 IN VARCHAR2, cty IN VARCHAR2,
10 state IN VARCHAR2, zip IN VARCHAR2),
11 MEMBER FUNCTION getStreet (line_no IN number) RETURN VARCHAR2,
12 MEMBER FUNCTION getCity RETURN VARCHAR2,
13 MEMBER FUNCTION getStateAbbr RETURN VARCHAR2,
14 MEMBER FUNCTION getPostalCode RETURN VARCHAR2,
15 MEMBER FUNCTION getPhone RETURN VARCHAR2,
16 MEMBER PROCEDURE setPhone (newPhone IN VARCHAR2)
17 );
18 /
Type created.
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE BODY address AS
2 MEMBER PROCEDURE ChangeAddress (
3 st_1 IN VARCHAR2, st_2 IN VARCHAR2, cty IN VARCHAR2,
4 state IN VARCHAR2, zip IN VARCHAR2) IS
5 BEGIN
6 IF (st_1 IS NULL) THEN
18 RAISE_application_error(-20001,"The new Address is invalid.");
19 ELSE
20 street_1 := st_1;
21 street_2 := st_2;
22 city := cty;
23 state_abbr := upper(state);
24 zip_code := zip;
25 END IF;
26 END;
27
28 MEMBER FUNCTION getStreet (line_no IN number)
29 RETURN VARCHAR2 IS
30 BEGIN
31 IF line_no = 1 THEN
32 RETURN street_1;
33 ELSIF line_no = 2 THEN
34 RETURN street_2;
35 ELSE
36 RETURN " ";
37 END IF;
38 END;
39
40 MEMBER FUNCTION getCity RETURN VARCHAR2 IS
41 BEGIN
42 RETURN city;
43 END;
44
45 MEMBER FUNCTION getStateAbbr RETURN VARCHAR2 IS
46 BEGIN
47 RETURN state_abbr;
48 END;
49
50 MEMBER FUNCTION getPostalCode RETURN VARCHAR2 IS
51 BEGIN
52 RETURN zip_code;
53 END;
54
55 MEMBER FUNCTION getPhone RETURN VARCHAR2 IS
56 BEGIN
57 RETURN phone_number;
58 END;
59
60 MEMBER PROCEDURE setPhone (newPhone IN VARCHAR2) IS
61 BEGIN
62 phone_number := newPhone;
63 END;
64 END;
65 /
Type body created.
SQL> CREATE TABLE employee(
2 emp_id INTEGER,
3 emp_name VARCHAR2(32),
4 supervised_by INTEGER,
5 pay_rate NUMBER(9,2),
6 pay_type CHAR,
7 home_address address);
Table created.
SQL>
SQL>
SQL> INSERT INTO employee
2 (emp_id, emp_name,pay_rate,pay_type,home_address)
3 VALUES (597,"Matthew Higgenbottom",120000,"S",
4 address("101 Maple","","Mio","MI","48640","5173943551"));
1 row created.
SQL>
SQL>
SQL> set echo on
SQL>
SQL> UPDATE employee e
2 SET e.home_address = address(e.home_address.street_1,
3 e.home_address.street_2, e.home_address.city,
4 e.home_address.state_abbr, e.home_address.zip_code,
5 "5173433333")
6 WHERE emp_id = 597;
1 row updated.
SQL>
SQL> COMMIT;
Commit complete.
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL> --
Using UPDATE with Columns
SQL> --Using UPDATE with TYPEed Columns
SQL>
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> 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> UPDATE emp e
2 SET e.address.zip = "34210"
3 WHERE e.address.city LIKE "Mobile%";
1 row updated.
SQL>
SQL>
SQL>
SQL> SELECT name, e.address.city FROM emp e WHERE e.address.state = "AL";
NAME ADDRESS.CITY
--------- --------------------
Adam Mobile
SQL>
SQL> drop table emp;
Table dropped.
SQL>
SQL>