Oracle PL/SQL Tutorial/Object Oriented/Delete

DELETE from object table where clause reference object"s attributes

   <source lang="sql">

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></source>

Deleting a Row from the object_products Table

   <source lang="sql">

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></source>

Deleting a Row from the Table with object type column

   <source lang="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 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></source>