Summary of common Oracle knowledge

The database environment of the project development was Oracle some time ago. As a SQLer, it indicates various inconveniences. So at the beginning, I took some detours and wasted some time. Therefore, I want to summarize these common things, which is a summary of my own learning. I also hope to provide some help to my friends who are using Oracle for the first time. 1. Create an auto-incrementing master

The database environment of the project development was Oracle some time ago. As a SQLer, it indicates various inconveniences. So at the beginning, I took some detours and wasted some time. Therefore, I want to summarize these common things, which is a summary of my own learning. I also hope to provide some help to my friends who are using Oracle for the first time. 1. Create an auto-incrementing master

Preface:

Some time ago, the project development database environment was Oracle. As a SQLer, it indicates various kinds of incompatibility. So at the beginning, I took some detours and wasted some time. Therefore, I want to summarize these common things, which is a summary of my own learning. I also hope to provide some help to my friends who are using Oracle for the first time.

1. Create an auto-incrementing primary key

For SQLer, which is used to SQL SERVER graphical interface operations for a long time, it is very troublesome to create a table with a primary key with an auto-incrementing field, but it is also good after getting used to it, in fact, you can also use EA to design the table structure and generate SQL directly. Here, we only want to demonstrate how to create a table through SQL statements.

Example:

1) create a table

Create Table Test (ID int, Name varchar2 (200), Primary Key (ID ))

2) create an auto-incrementing Sequence

Create Sequence Test_Sequence

Increment by 1

Start with 1

Nomaxvalue

Nocycle

3) create a trigger

Create Or Replace Trigger Test_Trigger

Before Insert On Test

For Each Row

Begin

Select Test_Sequence.Nextval Into: new. id From dual;

End;

In the preceding three steps, the table with the auto-increment sequence with the primary key is created.

2. Modify the table name, field name, field type, and length.

During actual development, you may encounter situations where you need to modify the table name and field. The following describes the modifications.

1) modify the Table name: Alter Table Test Rename To Test1

2) modify the field Name Alter Table Test Rename Column Name To Name1

3) adds the Alter Table Test Add Age int field.

4) Delete the field Alter Table Test drop column Age

5) Alter Table Test Modify Age number (8, 2)

3. Time and date

There is no special date data type in the oracle database. Sometimes we only need to store the date, but we will automatically add the date when storing the database. In this case, we can handle the problem as follows:

If trunc is not used, the query result is as follows: select sysdate from dual 12:06:16

After trunc is used, the result is as follows: select trunc (sysdate) from dual 2013-12-22

In this way, we can use the update statement and trunc to change the storage of the date + time (12:03:03) to the date () format, in fact, the internal storage of the database is still "00:00:00", but the query is displayed in the date format.

The following describes how to obtain the current year, month, day, quarter, Week, and day in oracle.

Obtain the current year: select to_char (sysdate, 'yy') from dual

Obtain the current month: select to_char (sysdate, 'mm') from dual

Obtain the current day: select to_char (sysdate, 'dd') from dual

Obtain the current quarter: select to_char (sysdate, 'q') from dual

Obtain the current week: select to_char (sysdate, 'iw') from dual

Obtain the current day of the week: select to_char (sysdate, 'day') from dual

4. Top N

In oracle, top cannot be used, but an alternative solution is to use rownum. For example, we want to obtain the top5 of some information sets. The specific example is as follows:

With as sub (select *, rownow as PX from Test)

Select * from PX where PX <6

This solves the top problem. When using this method to query top N, it is best to sort the data source by certain fields first.

5. Multi-table join update

In actual development, sometimes the field content of a table needs to be updated to the field content of another table. In this case, multi-table join update is used. In SQL Server, we can do this:

Update t set id = t1.id from Test t inner join Test1 t1 on t. no = t1.no

However, this method is not supported in Oracle, and there are also alternative methods available. We can use subqueries to achieve this, as shown below:

Update Test t set t. id = (select t1.id from Test1 t1 where t1.no = t. no) where exists (select * from Test1 t1 where t1.no = t. no)

Note that the subquery value can only be a unique value, not a multi-value. The following where exists is also very important. If not, the result may be incorrect.

In addition, if you want to update, delete, and add operations in the database, remember to submit the commit statement. Otherwise, the modified changes cannot be found in the program.

6. stored procedures and functions

Stored Procedures and functions are frequently used in databases. The syntax in SQL server and oracle is slightly different. Here we only need two simple examples.

Stored Procedure:

General Syntax:

Create Or Replace Procedure stored Procedure name (parm1 in type, parm2 out type ,....)

As

Variable type range; -- (for example, name varchar2 (200 );)

Variable type range; -- (for example, age number (8 );)

Begin

Select * from Test where Name = name; -- stored procedure with judgment

If (age <10) then .......;

Elsif (age> 10 and age <20) then ........;

Else raise NO_DATA_FOUND;

End if;

Exception

When others then Rollback;

End;

Example:

Create Or Replace TestAge (age in int, agegroup out varchar2 (200 ))

As

Age_group varchar2 (200 );

Begion

If (age <= 10) then

Age_group: = "childhood ";

Elsif (age> 10 and age <18)

Age_group: = "Teenagers ";

Else age_group: = "adult ";

End if;

Agegroup: = age_group;

End;

Function:

General Syntax:

Create Or Replace Function name (parameter name in/out parameter type, parameter name in/out parameter type ,.....)

Return type

As parameter name type; -- (for example, name varchar2 (200 );)....

Begin

......

......

Retrun expression;

End function name;

Example:

Create Or Replace Function GetAgeGroup (age in int)

Retrun varchar2

As agegroup varchar2 (200 );

Begin if (age <10) then agegroup: = "childhood ";

Elsif (age> = 10 and age <18) then agegroup = "Youth ";

Else agegroup = "adult ";

End if; retrun agegroup;

End GetAgeGroup;

The content of this section has no significance, but Oracle functions and stored procedures are somewhat different from those of SQL. It is for reference only and can be used separately.

7. Index

Indexes are rarely used in SQL server, because the data volume is not that large. In this project, the data volume involved is large, even in oracle, the query and retrieval speed is not good, so you need to create an index. However, after creating an index, there is no difference with not creating it. After analysis, we learned that there are still many rules for creating an index in oracle, the following briefly describes the precautions.

1) The index should be created in the column of the table involved in the "Where" or "and" section of the SQL statement.

2) use indexes in Order. Note that all columns in Order by are included in the same index and keep the order in the index. All columns in Order by must be defined as non-empty; otherwise, the index is invalid.

3) Avoid changing the index column type.

Example: select * from EMP where EMP_TYPE = '20160301'

In fact, oracle will execute the following statement during execution:

Select * from EMP where EMP_TYPE = to_number ('123 ')

In this way, the type conversion does not occur in the index column, so the index is still valid. However, if it is written in this way, the index will become invalid.

Select * from EMP where to_number (EMP_TYPE) = 123

The index fails because the index column type has changed.

4) note that not all index columns are valid in the Where clause.

For example:

A) The where clause uses "! = "The index is invalid,

B) In addition, the "|" character concatenation may also cause index failure.

C) the same index Column cannot be used for comparison, and the index will also become invalid.

D) is null and is not null will also cause index failure.

E) The use of functions may also cause index failure.

5) Creating indexes on columns that are frequently used for connection speeds up the connection. These columns are mainly foreign keys.

6) Create an index on the primary key column to force the uniqueness of the column and organize the data arrangement structure in the table.

7) it is not suitable for creating indexes for columns whose values change frequently, because increasing the index will reduce the modification performance. Similarly, increasing the modification performance will reduce the index performance.

8) indexes should not be added for columns with few data values, because the data rows in the result set occupy a large proportion of the data rows in the Table. Increasing indexes does not increase the retrieval speed.

Well, today I will summarize it here, and it is also a lot of common knowledge. I hope it will be helpful to my friends who start oracle.