Oracle basic syntax collection

1. Table

create table test (names varchar2(12),                    dates date,                    num   int,                    dou   double);

2. View

create or replace view vi_test as select * from test;

3. Synonyms

create or replace synonym aa for dbusrcard001.aa;

4. Stored Procedure

create or replace produce dd(v_id in employee.empoy_id%type) as begin    end dd;

5. Functions

create or replace function ee(v_id in employee%rowtype) return varchar(15) is var_test varchar2(15); begin   return var_test; exception when others then    end

6. Definitions of Three triggers

Create or replace trigger ff Alter delete On test For each row Declare Begin Delete from test; If SQL % rowcount <0 or SQL % rowcount is null then Rais_replaction_err (-20004, "error ") End if End Create or replace trigger gg Alter insert On test For each row Declare Begin If: old. names =: new. names then Raise_replaction_err (-2003, "Duplicate encoding "); End if End Create or replace trigger hh For update On test For each row Declare Begin If updating then If: old. names <>: new. names then Reaise_replaction_err (-2002, "the keyword cannot be modified ") End if End if End

7. Define a cursor

declare    cursor aa is       select names,num from test; begin    for bb in aa    loop         if bb.names = "ORACLE" then                 end if    end loop;    end

8. Speed Optimization: the speed of the previous statement is dozens of times faster than that of the next statement

select names,dates from test,b where test.names = b.names(+) and       b.names is null and       b.dates > date('2003-01-01','yyyy-mm-dd')   select names,dates from test where names not in ( select names                        from b                       where dates > to_date('2003-01-01','yyyy-mm-dd'))

9. Search for duplicate records

select names,num from test where rowid != (select max(rowid)                  from test b                 where b.names = test.names and                       b.num = test.num)

10. Search for the first 10 latest records in the TEST table

select * from (select * from test order by dates desc) where rownum < 11

11. Generation of serial numbers

create sequence row_id minvalue 1 maxvalue 9999999999999999999999 start with 1 increment by 1 insert into test values(row_id.nextval,....)

Related Articles]

• Using SQL statements in Oracle to implement inter-System Conversion

• How to avoid the @ symbol in the Oracle Database Password

• Description of the to_date parameter in Oracle