Oracle Database self-Summary

1. When connecting to the remote server from Oracle, you must install the client and overwrite the D: \ oracle \ product \ 10.2.0 \ db_1 \ NETWORK \ ADMIN \ tnsnames. ora file.

If the connection fails, restart the OracleServiceORCL service. For example:

The oracle Database Service must be enabled.

1. Create or replace view name. A simple view allows you to perform DML operations.
2. There are no auto-increment columns in oracle, but in MySQL.
3. nvl is equivalent to isnull in mssql.
4. to_char (sysdate, 'yyyy-mm-dd') is equivalent to convert (getdate (), 'yyyy-mm-dd') in mssql; TO_DATE is equivalent to Year (), month (), Day ()
5. decode (isautocreate, 1, 'yes', 'no'); decode (name, 'hangsan', 'zhangsan', 'lisi', 'lily '[, 'noname']) is similar to the case when statement.
6. | Type + connector in mssql. Rownum is equivalent to top
7 sysdate is equivalent to getdate () in mssql ();
8. ORACLE multi-Table associated update statement. Http://www.examda.com/oracle/zhonghe/20100103/094306360.html
9. In Mssql, select id, * from tb, and select t. *, t. id from tb t in orcale database;
10. When you insert, delete, and update data in pl/SQL, remember to add commit at the end.
11. Create or replace view name. A simple view allows you to perform DML operations.
12. There are no auto-increment columns in oracle, but in MySQL.
13. nvl is equivalent to isnull in mssql.
14. to_char (sysdate, 'yyyy-mm-dd') is equivalent to convert (getdate (), 'yyyy-mm-dd') in mssql; TO_DATE is equivalent to Year (), month (), Day ()
15. decode (isautocreate, 1, 'yes', 'no'); decode (name, 'hangsan', 'zhangsan', 'lisi', 'lily '[, 'noname']) is similar to the case when statement.
16. | Type + connector in mssql. Rownum is equivalent to top
17 sysdate is equivalent to getdate () in mssql ();
18. ORACLE multi-Table associated update statement. Http://www.examda.com/oracle/zhonghe/20100103/094306360.html
19. Make sure to commit the insert, delete, and update statements of the data volume of the lifecycle CLE;
20. Questions about group by: (1) fields not in aggregate functions must be in group by statements. Select id, name form a group by id, name (positive) select id, name from a group by id (error)
(2): The fields in group by do not necessarily all appear in the field list, but they must have an aggregate function. Select id, count (*) from a group by id, name (positive)
21. How does one automatically add one field in Oracle?
Implementation Method:

Create a sequence with a minimum value of 1 and a serial number with a maximum value of 999999999 to automatically cycle
Create sequence name
Increment by 1
Start with 1
Max value 999999999
Cycle;

When inserting data into a table, the SQL statement is written as follows:

SQL> insert into table name values (sequence name. nextval, column 1 value, column 2 value );
22. The user control finds the parent form.
// Form frm = this. FindForm ();
FrmF1ReportConfig config = (frmF1ReportConfig) this. FindForm ();
23. The alias of the subquery table in the oracle database cannot be as, and the alias of the table after the subquery can be unavailable. the alias must appear in the subquery of the SQL database.
The alias double quotation marks in the oracle database are optional.
Select * from (select sysdate from dual) as "A" (error)
Select * from (select sysdate from dual) as A (error)
Select * from (select sysdate from dual) (positive)
Select * from (select sysdate from dual) A (positive)
Select sysdate as "time" from dual (positive)
Select sysdate time from dual (positive)
Select sysdate as time from dual (positive)
24. In pl/SQL, If You Want To manually modify a record queried by select. You need to query the rowid column. Seelct t. rowid, t. * from table t;
25. Unable to initialize the Oracle call interface + ORACLE cannot work normally.
Solution: Right-click the PL/SQL icon and choose Properties> compatibility> tick the place where you enter as an administrator to run PL/SQL again.

26. Each element in the having clause must also appear in the select list. Some database exceptions, such as oracle.
Http://lymygee.blog.163.com/blog/static/292760692010714113923813/