Home » SQL & PL/SQL » SQL & PL/SQL » member procedure execution (oracle 11g)
member procedure execution [message #664638] Tue, 25 July 2017 10:20 Go to next message
ynkr999
Messages: 7
Registered: May 2017
Junior Member

CREATE OR REPLACE TYPE new_type2 AS OBJECT
(
title VARCHAR2(12),
author VARCHAR2(40),
price number(5,2),
stock_qty Number(12),
Member procedure new_discount1
)
/

CREATE OR REPLACE TYPE BODY new_type2 AS
MEMBER procedure new_discount1 IS
discount number(8);
netprice number(8);
BEGIN
IF title = 'science' THEN
discount := round(30*price/100);
netprice := price-discount;
dbms_output.put_line('the net price is :'||netprice);
END IF;
IF title = 'social' THEN
discount := round(40*price/100);
netprice := price-discount;
dbms_output.put_line('the net price is :'||netprice);
END IF;
END new_discount1;
END;
/


SQL> create table book_disc1 of new_type2;

Table created.


begin
insert into book_disc1 values('science','smruti',300,3);
insert into book_disc1 values('social','smruti',400,2);
commit;
end;
/

sir i am learning oracle.i am totally new to oracle.

sir in the above code i have created one object type with member procedure.on that object type, i have created one table.
then i have inserted two sample records into taht table.then i want to execute member procedure.could you tell me please,is there any way to execute above member procedure. if not possible waht is the correct way.


Thanks and regards
==================
Naveen
Re: member procedure execution [message #664639 is a reply to message #664638] Tue, 25 July 2017 12:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I could help you but as you refuse to follow the forum rules, format your posts and feedback to those who spend time to help you, I won't.

Re: member procedure execution [message #664640 is a reply to message #664639] Tue, 25 July 2017 13:12 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You need to learn to Read The Fine Manual

http://docs.oracle.com/database/122/LNPLS/CREATE-TYPE-statement.htm#LNPLS01375
Re: member procedure execution [message #664953 is a reply to message #664638] Tue, 15 August 2017 00:16 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I have provided an example of a way to execute the procedure that you have provided. Below that I have also provided a simpler example using a function instead of a procedure.

-- using procedure that you provided:
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE TYPE new_type2 AS OBJECT
  2    (title	   VARCHAR2(12),
  3  	author	   VARCHAR2(40),
  4  	price	   number(5,2),
  5  	stock_qty  Number(12),
  6  	Member	   procedure new_discount1);
  7  /

Type created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE TYPE BODY new_type2
  2  AS
  3    MEMBER procedure new_discount1
  4    IS
  5  	 discount  number(8);
  6  	 netprice  number(8);
  7    BEGIN
  8  	 IF title = 'science' THEN
  9  	   discount := round(30*price/100);
 10  	   netprice := price-discount;
 11  	   dbms_output.put_line('the net price is :'||netprice);
 12  	 END IF;
 13  	 IF title = 'social' THEN
 14  	   discount := round(40*price/100);
 15  	   netprice := price-discount;
 16  	   dbms_output.put_line('the net price is :'||netprice);
 17  	 END IF;
 18    END new_discount1;
 19  END;
 20  /

Type body created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> create table book_disc1 of new_type2
  2  /

Table created.

SCOTT@orcl_12.1.0.2.0> begin
  2    insert into book_disc1 values('science','smruti',300,3);
  3    insert into book_disc1 values('social','smruti',400,2);
  4    commit;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM book_disc1
  2  /

TITLE        AUTHOR                                        PRICE  STOCK_QTY
------------ ---------------------------------------- ---------- ----------
science      smruti                                          300          3
social       smruti                                          400          2

2 rows selected.

SCOTT@orcl_12.1.0.2.0> DECLARE
  2    v_new_type2  new_type2;
  3  BEGIN
  4  	 dbms_output.put_line
  5  	   (rpad ('title', 13) || rpad ('author', 41) || lpad ('price', 7) || lpad ('stock_qty', 13));
  6  	 dbms_output.put_line ('--------------------------------------------------------------------------');
  7    FOR i IN (SELECT * FROM book_disc1) LOOP
  8  	 v_new_type2 := new_type2 (i.title, i.author, i.price, i.stock_qty);
  9  	 dbms_output.put_line
 10  	   (rpad (i.title, 13) || rpad (i.author, 41) || lpad (i.price, 7) || lpad (i.stock_qty, 13));
 11  	 v_new_type2.new_discount1;
 12  	 dbms_output.put_line ('--------------------------------------------------------------------------');
 13    END LOOP;
 14  END;
 15  /
title        author                                     price    stock_qty
--------------------------------------------------------------------------
science      smruti                                       300            3
the net price is :210
--------------------------------------------------------------------------
social       smruti                                       400            2
the net price is :240
--------------------------------------------------------------------------

PL/SQL procedure successfully completed.

-- simpler method using function:
SCOTT@orcl_12.1.0.2.0> DROP TABLE book_disc1
  2  /

Table dropped.

SCOTT@orcl_12.1.0.2.0> DROP TYPE new_type2
  2  /

Type dropped.

SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE TYPE new_type2 AS OBJECT
  2    (title	   VARCHAR2(12),
  3  	author	   VARCHAR2(40),
  4  	price	   number(5,2),
  5  	stock_qty  Number(12),
  6  	Member	   FUNCTION new_discount1
  7  		     (SELF    new_type2)
  8  		      RETURN  NUMBER);
  9  /

Type created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE TYPE BODY new_type2
  2  AS
  3    MEMBER FUNCTION new_discount1
  4  	 (SELF	  new_type2)
  5  	  RETURN  NUMBER
  6    IS
  7    BEGIN
  8  	 RETURN price -
  9  	   (CASE WHEN title = 'science' THEN 30
 10  		 WHEN title = 'social' THEN 40
 11  	    END * price / 100);
 12    END new_discount1;
 13  END;
 14  /

Type body created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> create table book_disc1 of new_type2
  2  /

Table created.

SCOTT@orcl_12.1.0.2.0> begin
  2    insert into book_disc1 values('science','smruti',300,3);
  3    insert into book_disc1 values('social','smruti',400,2);
  4    commit;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> COLUMN author FORMAT A15
SCOTT@orcl_12.1.0.2.0> SELECT t.* FROM book_disc1 t
  2  /

TITLE        AUTHOR               PRICE  STOCK_QTY
------------ --------------- ---------- ----------
science      smruti                 300          3
social       smruti                 400          2

2 rows selected.

SCOTT@orcl_12.1.0.2.0> SELECT t.*, t.new_discount1() net_price FROM book_disc1 t
  2  /

TITLE        AUTHOR               PRICE  STOCK_QTY  NET_PRICE
------------ --------------- ---------- ---------- ----------
science      smruti                 300          3        210
social       smruti                 400          2        240

2 rows selected.
Previous Topic: Code Help: Inserting Records based on records in another table meeting certain parameters
Next Topic: MISSING DATE
Goto Forum:
  


Current Time: Fri Mar 29 07:29:22 CDT 2024