Hi Friends,
Today i'm going to discuss few useful object implementation snippets with you. How, you can use objects in your Oracle programming and also demonstrate some object oriented approaches implement in Oracle using Objects.
Let's concentrate on our first case.
Type - 1,
test_m@ORCL>
test_m@ORCL>select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
Elapsed: 00:00:00.00
test_m@ORCL>
test_m@ORCL>
test_m@ORCL>create or replace type address_type as object
2 (
3 street_code varchar2(10),
4 street_name varchar2(50)
5 );
6 /
Type created.
Elapsed: 00:00:00.10
test_m@ORCL>
test_m@ORCL>create table test_obj
2 (
3 empno number(4),
4 ename varchar2(40),
5 address address_type,
6 city varchar2(30),
7 constraints pk_empno primary key(empno)
8 );
Table created.
Elapsed: 00:00:02.40
test_m@ORCL>
test_m@ORCL>
test_m@ORCL>insert into test_obj values(&eno,'&enm',address_type('&st_code','&st_name'),'&city');
Enter value for eno: 1001
Enter value for enm: SATYAKI
Enter value for st_code: 700010
Enter value for st_name: BELEGHATA
Enter value for city: KOLKATA
old 1: insert into test_obj values(&eno,'&enm',address_type('&st_code','&st_name'),'&city')
new 1: insert into test_obj values(1001,'SATYAKI',address_type('700010','BELEGHATA'),'KOLKATA')
1 row created.
Elapsed: 00:00:00.01
test_m@ORCL>/
Enter value for eno: 1002
Enter value for enm: ATANU
Enter value for st_code: 700100
Enter value for st_name: DLF
Enter value for city: KOLKATA
old 1: insert into test_obj values(&eno,'&enm',address_type('&st_code','&st_name'),'&city')
new 1: insert into test_obj values(1002,'ATANU',address_type('700100','DLF'),'KOLKATA')
1 row created.
Elapsed: 00:00:00.01
test_m@ORCL>/
Enter value for eno: 1003
Enter value for enm: PRANAB
Enter value for st_code: 700079
Enter value for st_name: VIP ROAD
Enter value for city: KOLKATA
old 1: insert into test_obj values(&eno,'&enm',address_type('&st_code','&st_name'),'&city')
new 1: insert into test_obj values(1003,'PRANAB',address_type('700079','VIP ROAD'),'KOLKATA')
1 row created.
Elapsed: 00:00:00.01
test_m@ORCL>
test_m@ORCL>commit;
Commit complete.
Elapsed: 00:00:00.01
test_m@ORCL>
test_m@ORCL>select e.empno,
2 e.ename,
3 cast(e.address.street_code as varchar2(10)) street_code,
4 cast(e.address.street_name as varchar2(50)) street_name,
5 e.city
6 from test_obj e;
EMPNO ENAME STREET_COD STREET_NAME CITY
---------- ---------------------------------------- ---------- -------------------------------------------------- ------------------------------
1001 SATYAKI 700010 BELEGHATA KOLKATA
1002 ATANU 700100 DLF KOLKATA
1003 PRANAB 700079 VIP ROAD KOLKATA
Elapsed: 00:00:00.03
test_m@ORCL>
test_m@ORCL>
test_m@ORCL>Lets concentrate on our second ventures where we will look into other way of accessing these various objects -
Type - 2 (Overloading),
test_m@ORCL> test_m@ORCL>create or replace package test_overloadng 2 is 3 x number(5); 4 procedure test_a( 5 a in number, 6 b in number, 7 c out number 8 ); 9 10 procedure test_a( 11 a in varchar2, 12 b in varchar2, 13 c out varchar2 14 ); 15 end; 16 / Package created. Elapsed: 00:00:00.05 test_m@ORCL> test_m@ORCL> test_m@ORCL>create or replace package body test_overloadng 2 is 3 procedure test_a( 4 a in number, 5 b in number, 6 c out number 7 ) 8 is 9 x number(10); 10 begin 11 x := a + b; 12 c := x; 13 end; 14 procedure test_a( 15 a in varchar2, 16 b in varchar2, 17 c out varchar2 18 ) 19 is 20 x varchar2(300); 21 begin 22 x := a||b; 23 c := x; 24 end; 25 end; 26 / Package body created. Elapsed: 00:00:00.04 test_m@ORCL> test_m@ORCL> test_m@ORCL>set serveroutput on test_m@ORCL> test_m@ORCL> test_m@ORCL>declare 2 v number(10); 3 w varchar2(300); 4 begin 5 test_overloadng.test_a(10,20,v); 6 dbms_output.put_line( 'Value of V : ' ||v); 7 test_overloadng.test_a( 'Satyaki ' , 'De' ,w); 8 dbms_output.put_line( 'Value of W : ' ||w); 9 end; 10 / Value of V : 30 Value of W : Satyaki De PL/SQL procedure successfully completed. Elapsed: 00:00:00.10 test_m@ORCL> test_m@ORCL>
Type - 3 (Polymorphism),
test_m@ORCL>
test_m@ORCL>select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
Elapsed: 00:00:00.00
test_m@ORCL>
test_m@ORCL>CREATE TYPE DEMO_ST AS OBJECT(
2 ID NUMBER ,
3 MEMBER PROCEDURE THIS_PROC
4 )
5 NOT FINAL NOT INSTANTIABLE
6 /
Type created.
Elapsed: 00:00:00.42
test_m@ORCL>
test_m@ORCL>CREATE TYPE DEMO_T1 UNDER DEMO_ST(
2 OVERRIDING MEMBER PROCEDURE THIS_PROC
3 )
4 /
Type created.
Elapsed: 00:00:00.36
test_m@ORCL>
test_m@ORCL>CREATE TYPE DEMO_T2 UNDER DEMO_ST(
2 OVERRIDING MEMBER PROCEDURE THIS_PROC
3 )
4 /
Type created.
Elapsed: 00:00:00.18
test_m@ORCL>
test_m@ORCL>CREATE TYPE BODY DEMO_T1
2 AS
3 OVERRIDING MEMBER PROCEDURE THIS_PROC
4 AS
5 BEGIN
6 DBMS_OUTPUT.PUT_LINE('DEMO_T1');
7 END;
8 END;
9 /
Type body created.
Elapsed: 00:00:00.09
test_m@ORCL>
test_m@ORCL>CREATE TYPE BODY DEMO_T2
2 AS
3 OVERRIDING MEMBER PROCEDURE THIS_PROC
4 AS
5 BEGIN
6 DBMS_OUTPUT.PUT_LINE('DEMO_T2');
7 END;
8 END;
9 /
Type body created.
Elapsed: 00:00:00.03
test_m@ORCL>
test_m@ORCL>CREATE OR REPLACE PROCEDURE DEMO_PRC(
2 P_OBJ DEMO_ST
3 )
4 AS
5 V_OBJ DEMO_ST := P_OBJ ;
6 BEGIN
7 V_OBJ.THIS_PROC;
8 END;
9 /
Procedure created.
Elapsed: 00:00:00.04
test_m@ORCL>
--
-- TAKES AN OBJECT, NOT A PROCEDURE PER SE
-- BUT ALLOWS THE PROCEDURE TO BE PASSED WRAPPED IN AN
-- OBJECT
--
test_m@ORCL>
test_m@ORCL>BEGIN
2 DEMO_PRC(DEMO_T1(1));
3 DEMO_PRC(DEMO_T2(1));
4 END;
5 /
DEMO_T1
DEMO_T2
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.09
test_m@ORCL>
test_m@ORCL>
test_m@ORCL>Type - 4 (Use Of Member Function),
scott@ORCL> scott@ORCL>create table employees 2 ( 3 id number primary key, 4 name varchar2(30) not null, 5 emp_status varchar2(1) not null 6 ); Table created. Elapsed: 00:00:00.19 scott@ORCL> scott@ORCL> scott@ORCL>create or replace type employee as object 2 ( 3 id number, 4 name varchar2(30), 5 emp_status varchar2(1), 6 member function exempt return varchar2 7 ); 8 / Type created. Elapsed: 00:00:00.15 scott@ORCL> scott@ORCL> scott@ORCL>create or replace type body employee 2 is 3 member function exempt return varchar2 4 is 5 begin 6 return self.name||' '||self.emp_status; 7 end; 8 end; 9 / Type body created. Elapsed: 00:00:00.11 scott@ORCL>
Now, to create a view called employees_view (Which will be shown later in this thread) we need the following privileges given by sys as follows -
In Sys,
BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production PL/SQL Release 11.1.0.6.0 - Production CORE 11.1.0.6.0 Production TNS for 32-bit Windows: Version 11.1.0.6.0 - Production NLSRTL Version 11.1.0.6.0 - Production sys@ORCL>grant create any view to scott, test_m; Grant succeeded. sys@ORCL> sys@ORCL> sys@ORCL>
Now, lets move into our user -
In Scott,
scott@ORCL> scott@ORCL>create or replace view employees_view of employee 2 with object identifier(id) 3 as 4 select id, 5 name, 6 emp_status 7 from employees; View created. Elapsed: 00:00:00.31 scott@ORCL> scott@ORCL> scott@ORCL>select id, 2 name, 3 emp_status, 4 o.exempt() 5 from employees_view o; no rows selected Elapsed: 00:00:00.04 scott@ORCL> scott@ORCL> scott@ORCL>declare 2 l_var varchar2(100); 3 l_obj employee := employee(1, 'E1', 'a'); 4 begin 5 l_var := l_obj.exempt(); -- works 6 l_var := l_obj.exempt; -- works with out parentheses too 7 dbms_output.put_line(l_var); 8 end; 9 / E1 a PL/SQL procedure successfully completed. Elapsed: 00:00:00.02 scott@ORCL> scott@ORCL>
Type - 5 (Use Of Member Procedure),
scott@ORCL>create or replace type TMyType is object( 2 some_value number, 3 4 member function Display return varchar2, 5 static procedure MyProc( n number ) 6 ); 7 / Type created. scott@ORCL> scott@ORCL> create or replace type body TMyType as 2 3 member function Display return varchar2 is 4 begin 5 return( 6 TO_CHAR( self.some_value, '999,999,999,990.00' ) 7 ); 8 end; 9 10 static procedure MyProc( n number ) is 11 begin 12 DBMS_OUTPUT.put_line( 13 TO_CHAR( n, '999,999,999,990.00' ) 14 ); 15 end; 16 end; 17 / Type body created. scott@ORCL> scott@ORCL> scott@ORCL> set serveroutput on scott@ORCL> begin 2 TMyType.MyProc( 123456789.12 ); 3 end; 4 / 123,456,789.12 PL/SQL procedure successfully completed. scott@ORCL>
Hope this thread will give some wise ways of implementing Oracle Objects.
Keep following - very soon i'll be coming back with another topic here. Till then - Bye.
Satyaki.