. NET Programmers Count Oracle's unique exotic spots

Nonsense

It's been about N years since the last time I contacted Oracle database, and the way Oracle works and some of the dots is special, it feels like a wonderful thing! Recently regained memory, has been tossing Oracle, because Oracle is different, so you want to record here Oracle is different from other databases and some of the use of Oracle process encountered in a bit, but also to the Oracle unfamiliar to the students have some knowledge.

Navigation

• Installing and creating a database
• Oracle Wonderful Spot
  • Qualifier
  • Case sensitive
  • Character type comparison case
  • Dual table
  • Cannot execute multiple SQL
  • Sys_guid function
  • Number Type
  • Self-increment implementation
  • Time Type
  • ROWNUM Understanding
  • Bit arithmetic
• Conclusion

Installing and creating a database

Oracle installation files are downloaded from the official website on the line, the installation process is basically the way to click on the next thing, in this is not much to say.
After installing, according to the software urine sex, generally will rely on some services, Oracle will also have it? We open the service:

Hey, I really found a few more with the word "Oracle" service, although do not know what to use, but certainly very important, we know that there is such a service is good. If these services do not start, we manually put them all to start, anyway, the boot is correct.
Oracle has a number of ways to create databases, and I learned the simplest one. Is the use of Oracle's own Database Configuration Assistant graphical interface tool:

After entering the database name, the next step is good.
After the database is created, let's look at what database files Oracle has generated for us. If you do not specify a database file to save the directory when you create the database by using metabase Configuration Assistant, it is saved by default in the folder named Oradata in the Oracle installation directory. Open the Oradata folder:

As we can see, each database that Oracle creates for us is saved in a single folder, and we'll see what's in it:

Generated several. DBF and. Log suffix files, these estimates are Oracle's master data files and log files, as a small white, we are familiar with.
At the same time, we went to service management, and unexpectedly found a new service:

These two service name suffixes are the data names we created, and we don't know what to do with them, although we know that's all right, hey.
By building a database, we can officially start the Oracle journey. I am a man of extreme fear of command line, so I found the Visual interface tool Navicat connect Oracle. Oracle's basic syntax is not introduced, a random search on the internet is a bunch, and then I will only record some of Oracle's different base points.

Oracle Miracle Point Qualifier

SQL Server and MYSQL qualifiers are [] and ', respectively, and the Oracle is double quotes "". When writing SQL statements, they are supported without qualifiers. But in Oracle, we have to understand that if the object name (table name/column name) in SQL is not enclosed in double quotation marks, Oracle will automatically turn the object name into uppercase when parsing the SQL statement, with double quotation marks. For example, a sql:select * from Table,oracle will be converted to select * from "table" when it is run and then executed. Another example is when creating a table SQL is: Create TABLE MyTable. The resulting table name is MYTABLE, not MYTABLE, because no qualifier is automatically capitalized, if a qualifier such as CREATE TABLE "MYTABLE" is added: The table name created is MYTABLE. Because Oracle is case-sensitive, it is important to understand the difference between double quotes and no double quotes.

Case sensitive

Unlike other databases, Oracle is strictly case-sensitive with table/column names. For example, there is a table named MyTable, query Sql:select * from "MyTable" will not execute, the report does not exist error. Oracle requires strict SQL statements and must be case-sensitive, so changing SQL to select * from "MyTable" will be successful. According to our custom, when writing SQL, there is no qualifier, that is written: SELECT * from MyTable, this is also not successful execution, because Oracle automatically turn to select * from "MyTable" after execution, obviously table MyTable is does not exist. That's why it's common for Oracle development to have this unspoken rule: when you build a table, the table name and field are all capitalized! This facilitates handwritten SQL, but also for unified specifications! This feature of Oracle is really not suitable for programmers who are accustomed to SQL Server or MYSQL. At the beginning of this, I was in my heart 10,000 alpaca passing by ...

Character type comparison case

In SQL Server, character Type field value comparisons are case-insensitive, but ... The evil Oracle is case-sensitive! For example: Select Case is ' a ' = ' a ' then 1 else 0 end from dual; Returns 0 instead of 1!
OK, table name/column name is case-sensitive I endured, but this ... I really want to swear!

Dual table

Dual table in Oracle is a magic table, its real table name is uppercase dual, but in order to see comfortable, we still use dual lowercase.
Just after contacting Oracle, familiar with the basic syntax we usually learn some of its functions, such as upper, lower, trim, cast, which we can write in SQL Server, select Upper (' a '), lower (' a '), cast (1 as TargetType), but in Oracle, I'm sorry, this write is not directly executed. Oracle stipulates that any SELECT query statement must be looked up from a Table object, and it is clear that select upper (' a '), lower (' a '), cast (1 as TargetType) are syntactically incorrect in Oracle's view, Because there is no table object for the specified query! So we have to change this: select Upper (' a '), lower (' a '), cast (1 as TargetType) from dual. In fact, I was thinking, orcale you can not smart point? If we do not write the FROM XX clause, you will default to use the dual table is not OK?
There is no more vomit, let's see what the hell is dual! We directly execute the SELECT * from dual to see what's Inside:

As above, we see that dual is actually a regular table of "unusual", except that it contains only a field and a row of data, so it is not difficult for us to understand why select Upper (' a '), lower (' a '), cast (1 as TargetType) The from dual can find out the data.
In fact, we can also customize a single field and a row of data table, completely can do to replace the dual table, but, Oracle itself has helped us to build such a dual table, we are in the construction is completely unnecessary. But why is it that dual is "unusual"? Since it is a table, is it possible for us to add and revise this table? Hee Hee We try to insert a piece of data: INSERT into dual (DUMMY) VALUES (' Y '):

Insufficient authority, haha, it seems that the orcale is to control the table. Well, we know how to use this watch, and more information about dual we don't have to care too much (in fact, I would like to know when Oracle built such a table named dual, the word translates what meaning, has not understood-).

Cannot execute multiple SQL

Many of the data support batch SQL execution, but it is not supported, said Oracle wonderful is not too much! Maybe there is a reunion asked, I in PL/SQL or Navicat Why can I execute multiple statements at once? That's because when we write SQL, we use semicolons to separate them, and PL/Navicat automatically helps us to take them apart, which is actually a one-piece send to Oracle.

Sys_guid function

There is no GUID type in the Oracle type dictionary, but it provides a function to generate a GUID: Sys_guid. But what I'm trying to say is, there's nothing X to do with this function! Because it returns a binary type! When we design the table, we can't always use the binary key! At the same time, the Oracle access driver I use is oracle.manageddataaccess, and this driver's DataReader does not support getguid at all.

Number Type

The number type is a bit "wide" in Oracle, and we can understand that all numeric types can be represented by numbers. But there are some things that are appropriate to know better. If a number Type field does not specify a precision, the data type obtained by the oracle.manageddataaccess driver DataReader will be of type Decimal. Although you can also call GetInt32, GetInt64 and other methods to get the value, but there will be loss of data conversion. Therefore, when we design the table, for the numeric type, we'd better specify the precision as well. Because precision is specified, the Oracle.manageddataaccess driver uses the appropriate C # type storage based on precision, for example, if a field type is number (9,0), the Oracle driver uses the int type to store the value if a field type is Number (4,0), the Oracle driver uses the Int16 type to store the value. From the output below we can see:

At the same time, specifying the accuracy, I would like to estimate the database will also save a certain amount of space, in short, if you do Oracle development, I recommend to develop the habit of specifying precision.

Self-increment implementation

Both SQL Server and MYSQL have self-enhanced identity columns, which is convenient and Oracle does not support (I heard that the new Oracle is starting to support). If you want to achieve self-increment, you have to take advantage of sequence implementations. I give the Chloe.orm extended Oracle Provider support sequence, but there is a bad point, every time you insert data, you have to first isolate the sequence values from the database, and then insert the real data table, which is more than SQL Server database interaction, a little bit uncomfortable.

Time Type

There are two types of Oracle time types, date and timestamp.
The date type of Oracle differs from the date type of SQL Server. SQL Server has a date type that is accurate to date and is true, while Oracle's date type is accurate to seconds, which can be said to be a time type, not a date. However, Oracle this wonderful, it has a precision to the millisecond level of the timestamp type! But it differs from the date type:

1. If we want to use the To_char function to get the millisecond portion of a time, we usually write this to_char (date, ' FF3 '), which is not supported if a field is of date type, because the date type is only accurate to seconds. However, the timestamp type can be to_char (timestamp, ' FF3 ') to extract the number of milliseconds.
2. The data subtraction of two date types returns the number of days of the type of # (which is very good, not supported by SQL Server and MYSQL), and two timestamp types, which returns the Oracle A special timestamp type interval day to SECOND, I was really drunk--. Return interval day to second type, we really can't use ah ... Because I'm developing Oracle Provider for chloe.orm, I need support for two days/hours/minutes/seconds/milliseconds, I really don't know how to extract it from Interval day to second! In the end, it can only be supported in a flexible way, but how much it feels like the Oracle design is no language. By the way Navicat, in the Navicat Finder does not support the To_timestamp function (Oracle is supported), pit me and so small white for several days!

ROWNUM Understanding

Oracle paging can be used with SQL Server-like row_number () functions or with Oracle-specific ROWNUM paging. Usually, we are using the ROWNUM method, after all, this is the Oracle recommended syntax.

rownum is not a real column, it is a pseudo-column that Oracle dynamically adds to each row of the query when it executes the query. We must understand in depth to use it well. Since the ROWNUM column is not a real-life table, when is it defined? A complete query SQL statement consists of 5 parts (select, from, where, group, order), when executed, these 5 parts are sequential, that is, from the from data set to scan each row of data-->where conditional filtering-->group-- >order-->select,oracle when scanning a data set, each scan to a line to its number (starting from 1), that is, set ROWNUM, the row number action is the Where Condition filter before the where filter, so we write SQL can be filtered in the Where condition using ROWNUM, such as where rownum<10.

This is not difficult to understand, but one thing we have to know is that each scan to a line Oracle to the row number, assuming 10, and then the where condition filtering, if not meet the Where condition, the data will be discarded, and then continue to scan the next row of data, But to the next line of data is the number of the uplink does not meet the criteria data, that is, or 10, and so on, so that only the where condition filtered by the number of the result set is starting from 1 and sequential. As long as we understand the generative mechanism of ROWNUM, we can write SQL well.
In order to deepen my understanding of the ROWNUM mechanism, let us first analyze a simple sql:select * from the users where rownum>1, this SQL run in Oracle is never results, because Oracle from the table user s to scan the first row of data, give its number 1, and then rownum>1 to determine the filter does not meet the criteria, so the first row of data is discarded, and then sweep the second row of data, the second row of data is the number of 1, and then by rownum>1 to determine whether filtering or non-conformance, and so on, So you'll never find any data. Therefore, if rownum filtering is required in the Where condition, only the <, <= conditions can be used.

Given the ROWNUM generation mechanism, we have to use ROWNUM to do this in a nested way, which is like this:

SELECT * FROM (   select t.*, ROWNUM RN from   (SELECT * to MYTABLE ORDER BY id DESC) T   where ROWNUM <=) whe Re RN >= 21

Bit arithmetic

Oracle supports and operations (Bitand functions), but does not support or operate, I [email protected]#￥%^&*!... (10,000 alpaca is omitted here)

Conclusion

Oracle in the database ranks is a wonderful one, give me the feeling is the user experience is poor! I don't really have a crush on it. Finally, to leave a small gift for everyone to share a two time difference between the function to everyone:

Create or Replace function Datetimediff (    inDateTime1 in TIMESTAMP,    inDateTime2 in TIMESTAMP,    ininterval in VARCHAR  --D/h/m/s/ms) return Numberas    ret number;    Diffmillisecond number;    Intervaldivisor Number;begindiffmillisecond: = (CAST (inDateTime1 as Date)-cast (inDateTime2 as Date)) * 24 * 60 * 60 * 1000+ Cast (To_char (inDateTime1, 'ff3') as number)-cast (To_char (inDateTime2, 'ff3') as number); Intervaldivisor: = (case Inintervalwhen 'd' then (* + *) when 'H' then (* + *) W Hen 'm' then 1000when ' at the When 's'

Ms

--Divide by 0 to cause abnormal ret: =diffmillisecond/intervaldivisor;return ret;end;

I don't know much about Oracle, which is not the best way to estimate the time difference, but I can barely find the two time difference. If any students have better ideas, hope to share, thx!

. NET Programmers Count Oracle's unique exotic spots