Introduction to Oracle database development technical experience (1)

Introduction: Oracle DatabaseAs a large database management system, it has been the largest share of the world's high-end databases in recent years. Its powerful and complete database management functions, as well as the continuous efforts of ORACLE companies to innovate, IT has always become the focus of attention in the IT industry. The database platform of Lingao Nuclear Power Plant uses ORACLE7.3 as the back-end platform. The front-end selects DEVELOPER 2000 and DESIGNER 2000 from ORACLE as the development tools and adopts the popular CLIENT/SERVER mode. The following is the experience accumulated by developers in their daily development work. Now, we can use it for your reference.

I. oracle SQL PLUS usage tips:

---- ① Search for duplicate records:

[Pre] select drawing, dsno from EM5_PIPE_PREFAB

Where rowid! = (Select max (ROWID) FROM EM5

_ PIPE_PREFAB D

WHERE EM5_PIPE_PREFAB.DRAWING = D. DRAWING AND

EM5_PIPE_PREFAB.DSNO = D. DSNO );

[/Pre]

---- After executing the preceding SQL statement, all records with the same and repeated DRAWING and DSNO can be displayed.

---- Delete duplicate records:

[Pre] delete from EM5_PIPE_PREFAB

Where rowid! = (Select max (ROWID) FROM EM5

_ PIPE_PREFAB D

WHERE EM5_PIPE_PREFAB.DRAWING = D. DRAWING AND

EM5_PIPE_PREFAB.DSNO = D. DSNO );

[/Pre]

---- After the preceding SQL statement is executed, all records with the same and repeated DRAWING and DSNO can be deleted.

---- ② Compile all views quickly

---- After the database is poured into the new server (the database is rebuilt), you need to re-compile the view, because the connection between the table view in the tablespace to other tablespaces may fail, you can use the language features of PL/SQL to quickly compile.

[Pre] SQL> spool on. SQL

SQL> SELECT 'alter view' | TNAME | 'compile; 'FROM TAB;

SQL> SPOOL OFF

[/Pre]

Run ON. SQL. SQL> @ ON. SQL

Of course, you can also quickly authorize and create synonyms, such:

[Pre] SQL> SELECT 'Grant SELECT on'

| TNAME | 'to username;' from tab;

SQL> SELECT 'create SYNONYM

'| TNAME |' for username. '| TNAME |'; 'from tab;

[/Pre]

③ Use outer join to increase the query speed of table join

When connecting tables (commonly used for views), you can use the following methods to query data:

[Pre] SELECT PAY_NO, PROJECT_NAME

FROM

Where a. PAY_NO not in (select pay _

No from B WHERE VALUE> = 120000 );

[/Pre]

---- However, if Table A has 10000 records and table B has 10000 records, it takes 30 minutes to complete the query. This is mainly because NOT IN requires A comparison, A total of 10000*10000 comparisons are required before the results can be obtained. After the external join is used, the time can be shortened to about 1 minute.

---- ⑤ How to use the new and old values of columns in database Triggers

---- In database triggers, the column value of the trigger base table is almost always used. If a statement requires the pre-modification value of a column, use: OLD, use the NEW value after a column is modified, and use: NEW. For example: OLD. DEPT_NO,: NEW. DEPT_NO.