Working With Oracle on Windows: Part 2 - User Defined Schema Objects
(Page 6 of 7 )
The DDL (i.e. Data Definition Library) contains the set of commands that allow us to create, drop tables and establish new schema objects. To create an object we can type the following,
CREATE OR REPLACE TYPE firstType AS OBJECT
(
v_id NUMBER,
v_firstName VARCHAR2(25),
v_surname VARCHAR2(30)
)
/
We have now created a new schema object in the DDL. Now to create a table we can type,
CREATE TABLE firstTable of firstType
/
This will create the same table the only difference is that the DDL doesn’t create an object this time. The reason why you would create a schema object is so that you can create a number table that has the same attributes. Another reason why you’d use schema objects is the simplicity of inputting data into a table.
Let’s input data into our firstTable table using DML (i.e. Data Manipulation Language). We could simply insert data like this
INSERT INTO firstTable VALUES (1,’Kim’,’Tyson’);
How could we use schema objects for inputting data into a table? The answer is again very simple. We create a schema object and embedded the user defined schema object into the table.
CREATE OR REPLACE TYPE t_address AS OBJECT
(
v_streetNumber NUMBER,
v_streetName VARCHAR2(30),
v_citySuburb VARCHAR2(30),
v_state VARCHAR2(4),
v_postCode NUMBER
)
CREATE TABLE secondTable
(
v_customerId NUMBER,
v_homeAddress t_address,
v_workAddress t_address
)
Now we may insert data using the schema objects as follows,
INSERT INTO secondTable VALUES
( 0001,
t_address(1, ‘Jackson Street’, ‘Sydney’, ‘NSW’, 2000),
t_address(3, ‘Jackson Street’, ‘Sydney’, ‘NSW’, 2000)
)
Did you notice something apart from the fact that this poor guy lives next door from where he works? Yes, you can insert schema object into a field.
Next: Conclusion >>
More Oracle Articles
More By Ben Shepherd
