Fun SQL-create a specified data type and fun SQL data type

Fun SQL-create a specified data type and fun SQL data type

Original works, from "Deep Blue blog" blog, deep blue blog: http://blog.csdn.net/huangyanlong/article/details/46908843 

 

Fun SQL -- create a specified data type

 

In an article, I found that "a specified data type can be created", so I want to try to create it.

But it was not as successful as expected ~~

 

Create type MyName as object (first varchar2 (20), second varchar2 (20 ));

 

Create table newtype_test (

ID varchar2 (32 ),

Newname MyName

);

 

Let's take a look at the created table structure: There are two columns!

Look at the data query results: There are three columns!

Select * from newtype_test;

 

 

The following figure shows how to insert data in the experiment. It is not successful in the command line mode. Several attempts are as follows:

SQL> insert into newtype_test (id, newname. first, newname. second) values (2, 'shen', 'lan ');

Insert into newtype_test (id, newname. first, newname. second) values (2, 'shen', 'lan ')

ORA-00904: "NEWNAME". "SECOND": Invalid identifier

SQL> insert into newtype_test (id, newname. first, newname. second) values (2, shen, lan );

Insert into newtype_test (id, newname. first, newname. second) values (2, shen, lan)

ORA-00984: The column is not allowed here

SQL> insert into newtype_test (id, newname. first, newname. second) values (2, (shen), (lan ));

Insert into newtype_test (id, newname. first, newname. second) values (2, (shen), (lan ))

ORA-00984: The column is not allowed here

SQL> insert into newtype_test (id, newname) values (2, 'shenlan ');

Insert into newtype_test (id, newname) values (2, 'shenlan ')

ORA-00932: Data Type inconsistent: Should be HYL. MYNAME, but get CHAR

SQL> insert into newtype_test (id, newname) values (2, shenlan );

Insert into newtype_test (id, newname) values (2, shenlan)

ORA-00984: The column is not allowed here

SQL> insert into newtype_test (id, newname) values (2, (shen, lan ));

Insert into newtype_test (id, newname) values (2, (shen, lan ))

ORA-00907: missing right brackets

SQL> insert into newtype_test (id, newname) values (2, (shen), (lan ));

Insert into newtype_test (id, newname) values (2, (shen), (lan ))

ORA-00913: too many values

SQL> insert into newtype_test (id, newname) values (2, (shen ));

Insert into newtype_test (id, newname) values (2, (shen ))

ORA-00984: The column is not allowed here

SQL> insert into newtype_test (id, newname (first, second) values (2, ('shen', 'lan '));

Insert into newtype_test (id, newname (first, second) values (2, ('shen', 'lan '))

ORA-00917: missing comma

SQL> insert into newtype_test (id, newname, (first, second) values (2, ('shen', 'lan '));

Insert into newtype_test (id, newname, (first, second) values (2, ('shen', 'lan '))

ORA-01747: user. table. column, table. column or column description invalid

SQL> insert into newtype_test (id, newname. (first, second) values (2, ('shen', 'lan '));

Insert into newtype_test (id, newname. (first, second) values (2, ('shen', 'lan '))

ORA-01747: user. table. column, table. column or column description invalid

SQL> insert into newtype_test.newname.first values ('shen ');

Insert into newtype_test.newname.first values ('shen ')

ORA-00926: Missing VALUES keywords

SQL> insert into newtype_test.newname values (shen );

Insert into newtype_test.newname values (shen)

ORA-00942: Table or view does not exist

SQL> insert into newtype_test.newname values (shen, lan );

Insert into newtype_test.newname values (shen, lan)

ORA-00942: Table or view does not exist

SQL> insert into newtype_test.newname (first, second) values (shen, lan );

Insert into newtype_test.newname (first, second) values (shen, lan)

ORA-00942: Table or view does not exist

SQL> insert into newtype_test (hyl. tname. first, hyl. tname. second) values (1, 2 );

Insert into newtype_test (hyl. tname. first, hyl. tname. second) values (1, 2)

ORA-00904: "HYL". "TNAME". "SECOND": Invalid identifier

After trying for a long time, I didn't understand how to insert the data. So I tried to use a tool to insert the data:

Select * from newtype_test for update;

 

It was inserted successfully. The query has the following values:

 

Try to use a tool to reverse export the insert statement, as shown below:

 

Paste the file as follows:

Prompt Importing table newtype_test...

Set feedback off

Set define off

Insertinto newtype_test (ID, NEWNAME. FIRST, NEWNAME. SECOND)

Values ('1', 'huangt', 'yanlong ');

Prompt Done.

Verify that the data is inserted in the above writing mode (this method was actually tried before !), Sure enough, it is still unsuccessful.

What's going on? Therefore, the feudalistic superstition turned this into a script. Execute the following command to check whether an error is returned!

So far, it is feasible to manually insert data through PL/SQL Developer after several attempts. But why is there a problem with executing commands? Is the syntax incorrect ~~

(Vomiting blood ......)

This is the taste of SQL ~~~~

Finally, let's take a look at the query results:

We can see that when querying the newname field, it shows two split columns.

 

Additional time: Thursday, July 16, 2015

Let's make a mistake. The experiment above is a bit hypothetical. Let's take a look at the official documentation to see what the Orthodox explanation is like:

Object Tables

An OracleObject typeIs a user-defined type with a name, attributes, and methods. Object types make it possible to model real-world entities such as MERS MERs and purchase orders as objects in the database.

An object type defines a logical structure, but does not create storage. Example 2-5 creates an object type named department_typ.

Example 2-5 Object Type

Create type department_typ AS OBJECT

(D_name VARCHAR2 (100 ),

D_address VARCHAR2 (200 ));

/

AnObject tableIs a special kind of table in which each row represents an object. theCREATE TABLE statement inExample 2-6 creates an object table named departments_obj_t of the object typedepartment_typ. the attributes (columns) of this table are derived from the definition of the object type. theINSERT statement inserts a row into this table.

Example 2-6 Object Table

Create table departments_obj_t OF department_typ;

Insert into departments_obj_t

VALUES ('hr', '10 Main St, Sometown, ca ');

Like a relational column, an object table can contain in rows of just one kind of thing, namely, object instances of the same declared type as the table. by default, every row object in an object table has an associated logicalObject identifier (OID)That uniquely identifies it in an object table. The OID column of an object table is a hidden column.

Oracle object table. If we redo the above experiment, we can implement it as follows.

Create table myname_test OF MyName;

SELECT * FROM myname_test;

Insert into myname_test VALUES ('huangt', 'yanlong ');

-- This insert operation is acceptable.

Commit;

SELECT * FROM myname_test;

Summary:

Oracle object types are user-defined types with names, properties, and methods. The object type makes it possible to model entities in the real world (such as product managers and project names) as objects in the database. An object table is a special table with each row representing an object.

 

 

 

**************************************** * ** Blue growth series ********************************* *******************

Original works, from the blog of "Deep Blue". You are welcome to reprint them. Please indicate the source (Http://blog.csdn.net/huangyanlong).

Blue growth note-chasing DBA (1): traveling on the road to Shandong

Blue growth notes-Chase DBA (2): Install! Install! Long-lost memories have aroused my new awareness of DBAs.

Blue growth note-chasing DBA (3): importing and exporting data on antiques becomes a problem

Blue growth note-chasing DBA (4): recalling the sorrow of teenagers, and exploring oracle Installation (10g and 11g in Linux)

Blue growth note-chasing DBA (5): Not talking about technology or business, annoying Application Systems

Blue growth note-chasing DBA (6): doing things and being human: Small technology, great human

Blue growth note-Chase DBA (7): Basic commands, foundation stone

Blue growth notes-chasing DBA (8): repicking SP reports and recalling oracle's STATSPACK Experiment

Blue growth note-chasing DBA (9): Chasing DBA, new planning, new departure

Blue growth note-chasing DBA (10): Flying knife defense, familiarity rather than expertise: Playing with middleware Websphere

Blue growth note-chasing DBA (11): It's easy to go home and wake up.

Blue growth notes-Chase DBA (12): seven days and seven gains of SQL

Blue growth note-chasing DBA (13): Coordinating hardware manufacturers, six stories: what you see as "servers, storage, switches ......"

Blue growth note-chasing DBA (14): An unforgettable "Cloud" end, started hadoop deployment

Blue growth note-chasing DBA (15): Who thinks FTP is "simple" and thinks it is a trigger

Blue growth note-chasing DBA (16): DBA also drank alcohol and was rejected

Blue growth note-Chase DBA (17): whether to share or consume, learn to grow in the Post-IOE Era

**************************************** **************************************** **********************************

 

**************************************** ******************************** *****************

Original works, from the blog of "Deep Blue". You are welcome to reprint them. Please indicate the source (Http://blog.csdn.net/huangyanlong).

Football and oracle series (1): 32-way zhoudianbing, overall view of group A Brazil smon process of oracle32 process Alliance

Football and oracle series (2)

Football and oracle series (3): oracle process rankings, the World Cup round is about to fight!

Football and oracle series (4): from Brazil to Germany, think of the different RAC topology comparison! 

Football and oracle series (5): The directX library missing in the voda14 game is similar to the oracle rpm package!

Football and oracle series (6): Asian Cup with database creation-come on, Chinese Team

**************************************** **************************************** **********************************

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.