Method Example I

view source
print?
01SQL> CREATE OR REPLACE TYPE  book_t  AS OBJECT (
02  2    title      VARCHAR(64),
03  3    author     VARCHAR(32),
04  4    price      NUMBER(5, 2),
05  5    stock_qty  NUMBER(4),
06  6    MEMBER FUNCTION  get_discount  RETURN NUMBER );
07 
08Type created.
09 
10SQL> CREATE OR REPLACE TYPE BODY  book_t  AS
11  2    MEMBER FUNCTION  get_discount  RETURN NUMBER IS
12  3      clearance  number := 40;
13  4      nominal    number := 10;
14  5    BEGIN
15  6      IF  stock_qty < 100  THEN
16  7        RETURN  round( ( price * nominal / 100 ), 2 );
17  8      ELSE
18  9        RETURN  round( ( price * clearance / 100 ), 2 );
19 10      END IF;
20 11    END;
21 12  END;
22 13  /
23 
24Type body created.
25 
26SQL> CREATE TABLE  books  OF  book_t;
27 
28Table created.
29 
30SQL> INSERT INTO  books  VALUES(
31  2    book_t( 'Oracle 12c: The Complete Reference',
32  3      'Kevin Loney', 44.99, 345 ) );
33 
341 row created.
35 
36SQL> SELECT  b.price, b.price - b.get_discount( )
37  2    FROM  books b  WHERE  author = 'Kevin Loney';
38 
39     PRICE    B.PRICE-B.GET_DISCOUNT( )
40----------    -------------------------
41     44.99                        26.99
42 
43 
44SQL> VARIABLE  x  NUMBER;
45 
46SQL> BEGIN
47  2    SELECT  b.price - b.get_discount( )  INTO  :x
48  3      FROM  books b  WHERE  author = 'Kevin Loney';
49  4  END;
50  5  /
51 
52PL/SQL procedure successfully completed.
53 
54SQL> PRINT  :x;
55 
56         X
57----------
58     26.99

ROUND(n [, m]) returns n rounded to m places right of the decimal point; e.g., ROUND(15.193, 1) = 15.2.




      I’m not a big fan of stairs. They are always up to something.