Create type and use it in inner query : Object « Object Oriented Database « Oracle PL / SQL






  1. Oracle PL / SQL
  2. Object Oriented Database
  3. Object

Create type and use it in inner query

    
SQL> create or replace type myScalarType as object( cnt number, average number )
  2  /

Type created.

SQL>
SQL> select username, a.data.cnt, a.data.average
  2    from (
  3      select username, (select myScalarType( count(*), avg(object_id) ) from all_objects b where b.owner = a.username ) data from all_users a ) A
  4  /

USERNAME                         DATA.CNT DATA.AVERAGE
------------------------------ ---------- ------------
SYS                                  6520   5009.74064
SYSTEM                                422   6095.87678
OUTLN                                   7   1172.57143
DIP                                     0
TSMSYS                                  2       8606.5
INV15                                   2      16237.5
DBSNMP                                 46   9592.65217
INV10                                   2      16227.5
CTXSYS                                338   9877.92012
XDB                                   334   10800.7485
ANONYMOUS                               0

USERNAME                         DATA.CNT DATA.AVERAGE
------------------------------ ---------- ------------
MDSYS                                 458   11667.2009
HR                                     34      12104.5
FLOWS_FILES                            11   12717.2727
FLOWS_020100                         1085    12813.424
JAVA2S                                530   16254.6849
INV11                                   2      16229.5
INV12                                   2      16231.5
INV13                                   2      16233.5
INV14                                   2      16235.5
PLSQL                                   0
INV16                                   2      16239.5

USERNAME                         DATA.CNT DATA.AVERAGE
------------------------------ ---------- ------------
INV17                                   2      16241.5
INV18                                   2      16243.5
INV19                                   2      16245.5
INV20                                   2      16247.5
DEFINER                                 4      16250.5

27 rows selected.

SQL>
SQL>
SQL>








Related examples in the same category

1.Create Object
2.CREATE OR REPLACE TYPE
3.Create a stored type which is visible to SQL and PL/SQL.
4.reference user-defined data type in another block
5.Student type
6.Point type
7.Use user-defined type as parameter
8.This script demonstrates complex objects
9.Name type
10.Behavior of dependent objects.
11.Build data type with another user type
12.Create the object and collection types
13.Create types and then use it in pl/sql block
14.Combine user-defined type to create new type
15.PriceType becomes the datatype of the price attribute in the ProductType object type
16.Use self to reference member variable in constructor
17.One to list using object references