Geek Answers Handbook

What is the difference between the syntax "create or replace type" oracle "type type_name is ..."

I am new to pl / sql. now i have a question about oracle type . I saw two types of type :

CREATE OR REPLACE TYPE "TYPE_NAME1" AS OBJECT ( temp_trans_id number(10), trans_id number(10), resion_id number(10) )

or

 type new_type_name is record( column1 number, column2 varchar2(50) ); variable_name new_type_name;

what's the difference? Thank you so much.

+4
source share
2 answers

In addition to the previous answer, there are significant differences in the scope of the declaration.

When you declare a type in PL / SQL, for example the record type in your example, it can only be used for PL / SQL. If it is declared locally to a procedure or function, then it can only be used in this routine; if it is declared in the package body, it can only be used in this package; if it is declared in the package header, it can be used by any PL / SQL code that has access to the package. In no case can you refer to SQL statements, even built-in PL / SQL code .

When creating an object type or other schema level type definitions, such as nested tables, it can be used in both SQL and PL / SQL. As a very simple example, you can define a table definition in an object definition:

 SQL> CREATE OR REPLACE TYPE "TYPE_NAME1" AS OBJECT 2 ( 3 temp_trans_id number(10), 4 trans_id number(10), 5 resion_id number(10) 6 ) 7 / Type created. SQL> create table type_name1_tab of type_name1; Table created. SQL> desc type_name1_tab Name Null? Type ----------------------------------------- -------- ---------------------------- TEMP_TRANS_ID NUMBER(10) TRANS_ID NUMBER(10) RESION_ID NUMBER(10)

see here for documentation of the CREATE TYPE statement and links to additional documentation on the various uses of object types.

+7
source

A record type is a type that can be used as a record. It has a set of typed fields, but more on that. The type of object is quite different. It also has a set of fields, but it can also contain executable methods that will act in the context of the instance of your object (and yes, you can also have static methods). It looks like an object in Java. Some (but, of course, not all) differences from other object-oriented systems that I have seen:

  • No interfaces
  • No personal methods

In your example, TYPE_NAME1 and new_type_name seem very similar because you did not use anything specific for the object types for the object type (TYPE_NAME1). For more information, see the Oracle page on object types .

+3
source

More articles:


All Articles