Oracle PL/SQL Tutorial/Object Oriented/Delete
DELETE from object table where clause reference object"s attributes
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE address AS OBJECT
2 (line1 VARCHAR2(20),
3 line2 VARCHAR2(20),
4 city VARCHAR2(20),
5 state_code VARCHAR2(2),
6 zip VARCHAR2(13),
7 MEMBER FUNCTION get_address RETURN VARCHAR2,
8 MEMBER PROCEDURE set_address
9 (addressLine1 VARCHAR2,
10 addressLine2 VARCHAR2,
11 address_city VARCHAR2,
12 address_state VARCHAR2,
13 address_zip VARCHAR2)
14 );
15 /
Type created.
SQL> CREATE OR REPLACE TYPE BODY address AS
2 MEMBER FUNCTION get_address RETURN VARCHAR2
3 IS
4 BEGIN
5 RETURN (SELF.line1||" "||SELF.line2||" "||SELF.city||", "||
6 SELF.state_code||" "||SELF.zip);
7 END get_address;
8 MEMBER PROCEDURE set_address (addressLine1 VARCHAR2,
9 addressLine2 VARCHAR2,
10 address_city VARCHAR2,
11 address_state VARCHAR2,
12 address_zip VARCHAR2)
13 IS
14 BEGIN
15 line1 :=addressLine1;
16 line2 :=addressLine2;
17 city :=address_city;
18 state_code :=address_state;
19 zip :=address_zip;
20 END set_address;
21 END;
22 /
Type body created.
SQL>
SQL> CREATE TABLE address_master OF address;
Table created.
SQL>
SQL> INSERT INTO address_master VALUES (address("19 J","Rd","Vancouver","NJ","00000"));
1 row created.
SQL>
SQL> select * from address_master;
LINE1 LINE2 CITY ST
-------------------- -------------------- -------------------- --
ZIP
-------------
19 J Rd Vancouver NJ
00000
1 row selected.
SQL>
SQL> BEGIN
2 DELETE FROM address_master a WHERE (a.line1 IS NULL)AND (a.line2 IS NULL);
3 END;
4 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table address_master;
Table dropped.
SQL>
SQL>
Deleting a Row from the object_products Table
SQL>
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>
SQL> CREATE TABLE object_products OF ProductType
2 /
Table created.
SQL>
SQL> INSERT INTO object_products (
2 id, name, description, price, days_valid
3 ) VALUES (
4 1, "AAA", "BBB", 2.99, 5
5 );
1 row created.
SQL>
SQL> select * from object_products;
ID NAME DESCRIPTION PRICE DAYS_VALID
--- --------------- ---------------------- ---------- ----------
1 AAA BBB 2.99 5
SQL>
SQL> DELETE FROM object_products
2 WHERE id = 1;
1 row deleted.
SQL>
SQL> select * from object_products;
no rows selected
SQL>
SQL> drop table object_products;
Table dropped.
SQL>
Deleting a Row from 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>
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> delete from products p
2 WHERE p.product.id = 1;
1 row deleted.
SQL>
SQL>
SQL> SELECT product
2 FROM products;
PRODUCT(ID, NAME, DESCRIPTION, PRICE, DAYS_VALID)
--------------------------------------------------------
PRODUCTTYPE(2, "CC", "DDDD", 2.99, 5)
SQL>
SQL> drop table products;
Table dropped.
SQL>