Oracle PL/SQL Tutorial/PL SQL Data Types/Object

Материал из SQL эксперт
Перейти к: навигация, поиск

Using Objects in PL/SQL

   <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> 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> CREATE OR REPLACE PACKAGE product_package AS

 2    TYPE ref_cursor_typ IS REF CURSOR;
 3    FUNCTION get_products RETURN ref_cursor_typ;
 4    PROCEDURE insert_product (
 5      p_id          IN object_products.id%TYPE,
 6      p_name        IN object_products.name%TYPE,
 7      p_description IN object_products.description%TYPE,
 8      p_price       IN object_products.price%TYPE,
 9      p_days_valid  IN object_products.days_valid%TYPE
10    );
11  END product_package;
12  /

Package created. SQL> SQL> SQL> CREATE OR REPLACE PACKAGE BODY product_package AS

 2    FUNCTION get_products
 3    RETURN ref_cursor_typ IS
 4      products_ref_cursor ref_cursor_typ;
 5    BEGIN
 6      OPEN products_ref_cursor FOR
 7        SELECT VALUE(op)
 8        FROM object_products op;
 9      RETURN products_ref_cursor;
10    END get_products;
11
12    PROCEDURE insert_product (
13      p_id          IN object_products.id%TYPE,
14      p_name        IN object_products.name%TYPE,
15      p_description IN object_products.description%TYPE,
16      p_price       IN object_products.price%TYPE,
17      p_days_valid  IN object_products.days_valid%TYPE
18    ) AS
19      product ProductType :=
20        ProductType(
21          p_id, p_name, p_description, p_price, p_days_valid
22        );
23    BEGIN
24      INSERT INTO object_products VALUES (product);
25      COMMIT;
26    EXCEPTION
27      WHEN OTHERS THEN
28        ROLLBACK;
29    END insert_product;
30  END product_package;
31  /

Package body created. SQL> SQL> CALL product_package.insert_product(101, "AAA", "AAA and AAA", 1.50, 20); Call completed. SQL> SQL> SELECT product_package.get_products

 2  FROM dual;

GET_PRODUCTS


CURSOR STATEMENT : 1 CURSOR STATEMENT : 1 VALUE(OP)(ID, NAME, DESCRIPTION, PRICE, DAYS_VALID)


PRODUCTTYPE(1, "AAA", "BBB", 2.99, 5) PRODUCTTYPE(101, "AAA", "AAA and AAA", 1.5, 20)

SQL> SQL> select * from object_products;

ID NAME            DESCRIPTION                 PRICE DAYS_VALID

--- --------------- ---------------------- ---------- ----------

 1 AAA             BBB                          2.99          5
      1. AAA AAA and AAA 1.5 20

SQL> SQL> drop table object_products; Table dropped. SQL></source>