Home » SQL & PL/SQL » SQL & PL/SQL » member procedure execution (oracle 11g)
member procedure execution [message #664638] |
Tue, 25 July 2017 10:20 |
|
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 #664953 is a reply to message #664638] |
Tue, 15 August 2017 00:16 |
|
Barbara Boehmer
Messages: 9097 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.
|
|
|
Goto Forum:
Current Time: Sat Sep 28 06:38:37 CDT 2024
|