Oracle Summary 1

1. Show operation time
Set timing on;

2 nvl (Comm, 0). If comm is empty, 0 is displayed; otherwise, comm is used.
3 when groupy, having, and order by exist at the same time, group by must appear first, and then
Is having, and order
4 select * from (select A1. *, rownum rn from (select * from Scott. EMP) A1 where rownum <= 10) Where rn> = 6 (rownum not supported

)
If you specify a query column, you only need to modify the underlying (select * from Scott. EMP)
 
5 insert into KKK (a, B, c) Select a, B, c from EMP; (this can be suitable for mass data input)

6. Set read-only transactions
Set transcation read only; if a sets a read-only transaction
The new DML is ineffective.

 

7. Export tables
Export your own table
Exp userid = Scott/xxx @ xxx tables = (a, B) file = c: \ XXX. dmp;
Export other solutions
Exp userid = system/XX @ xxx tables = (Scott. A, Scott. B) file = c: \ XXX. dmp
Export only the table structure without importing data
Exp userid = system/XX @ xxx tables = (Scott. A, Scott. B) file = c: \ XXX. dmp rows = N;
If you want to export a large table, add the parameter direct = y
Export Scheme
Exp userid = system/XX @ xxx owner = (Scott, system) file = c: \ XXXX. dmp;
8. Data Dictionary View
Including user_xxx, all_xxx, and dba_xxx

9. view User Roles

Select * From dba_role_privs where grantee = 'Scott ';

View the system permissions of a role
Select * From dba_sys_privs where grantee = 'connection'
View the object permissions of a role
Select * From dba_tab_privs where grantee = 'connection'
View the roles of a user
Select * From dba_role_privs where grantee = 'username ';

10-page Stored Procedure

Create or replace package tespackage
Type test_cursor is ref cursor;
End testpackage;

Create or replace procedure fenye
(Tablename in varchar2,
Pagesize in number,
Pagenow in number,
Myrows out number, total number of records
Mypagecount out number total number of pages
P_cursor out tespackage. test_cursor -- returned record set
)
Is
V_ SQL varchar2 (10000 );
 
V_begin number: = (pageNow-1) * pagesize + 1;
V_end number: = pagenow * pagesize;
Begin
V_ SQL: = 'select * from (select T1. *, rownum rn from (select * from' | tablename
| ') T1 where rownum <=' | v_end | ') Where rn> =' | v_begin;
Open p_cursor for v_ SQL;
-- Calculate myrows and mypagecount
V_ SQL: = 'select count (*) from' | tablename;
Execute immediate v_ SQL into myrows;
If Mod (myrows, pagesize) = 0 then
Mypagecount: = myrows/pagesize;
Else
Mypagecount: = myrows/pagesize + 1;
End if;
Close p_cursor;
End;

End;