New PA takes you easy to play with Oracle database

A friend who has been in touch with software development may know that Oracle can be used for data storage during development because Oracle has a fast processing speed and a particularly high level of security. But there is a disadvantage is the more expensive, only a large company can be contacted, see here may be a lot of friends must want to know Oracle, the following new PA will take you into the world of Oracle.

Let's begin by introducing the processing of strings in Oracle

char varchar varchar2 nvarchar long clob These are the string types commonly used in Oracle, and in this case there are more varchar and varchar2, but they are different.

VarChar type is a string type developed by the International Standard organization ANSI

VARCHAR2 is a custom-defined string type for Oracle company and currently has the same varchar and VARCHAR2 functionality, Oracle recommends no VARCHAR2 only varchar in Varchar2,mysql, VARCHAR2 must specify size when used, char does not specify size, default is 1

The difference between char and varchar

New PA noticed char represents a fixed-length string, varchar represents a variable length string, C1--char (Ten)--C1 opens 10 characters of space, not enough 10, to add space. C2--varchar (+)-opens up space for up to 10 lengths according to the actual length of the C2

Chinese storage issues

Open Space size units are bytes, how many characters can be stored, and encoding about, GBK: Chinese accounts for two bytes, letters and numbers occupy one byte.

UTF-8: Chinese accounts for 3 bytes, letters and numbers account for one byte

Unicode: Both letters and Chinese account for two bytes

Usage Recommendation: VARCHAR2 defines length with maximum value: number of characters

nvarchar type

nvarchar uses Unicode encoded storage

nvarchar (5): can store 5 characters (man or alphanumeric)

String handling functions

Oracle provides a number of strings, values, datetime, etc., which are not described in detail here. If you want to know more, you can talk to your new brother.

Numeric operations

A numeric type in Oracle that uses number to define integers, floating-point numbers. Number: You can store integers or decimals, number (p): integers that represent P-bits, Number (P,s): represents floating-point numbers, S represents decimal places, and p represents the number of significant digits

--s>0: Long reserved S decimal places to the right of the decimal point

--s=0: truncate reserved integers on decimal rounding

--s<0: Rounding 0 on the left s bit of the decimal point

When P<s, is a decimal less than 1, the decimal point retains the S bit, the right of the decimal point s-p 0 to 0.0XX

Operation of date and time

Sysdate:oracle Server current system time, Systimestamp: Returns the current system time of the Oracle server is a timestamp type. Replace the string station with Date,to_date (S.format): Converts the S string to the date type in format. The default format for Oracle is: DD-MM-RR, English environment: ' 01-jan-90 ', Chinese Environment: ' January-January-90 '

Date converted to string

To_char (Date,format): Displays a string that converts date to format. To_timestamp and to_date are similar, you can convert a string to a timestamp () type

Other date functions

Last_day (date): Returns the last day of the current time month, Next_day (date, day of the week): From the date time, the day of the next one weeks (the date closest to date), Months_between (DATE1,DATE2) : Calculates how many months the date1-date2 differs. Extract: Gets the specified year, month, day and other information.

Null value operation

Null: All types of strings can be null, which is also important and is often encountered in development.

1 "Insert write null

2 "Null to make a conditional query

Well, the new PA today introduced here, more about the oracle of some in-depth application, welcome and new PA Exchange.

New PA takes you easy to play with Oracle database