Oracle基本语法

 
1、表

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

2、视图

create or replace view vi_test as          
            select * from test;

3、同义词

create or replace synonym aa          
            for dbusrcard001.aa;

4、存储过程

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

5、函数

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、三种触发器的定义

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,"错误")
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,"编码重复");
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,"关键字不能修改")
end if
end if
end
 

7、定义游标

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、速度优化，前一语句不后一语句的速度快几十倍

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、查找重复记录

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

10、查找表TEST中时间最新的前10条记录

select * from (select * from test order by dates desc) where rownum < 11
 
11、序列号的产生
create sequence row_id           
            minvalue 1           
            maxvalue 9999999999999999999999           
            start with 1
            increment by 1
            insert into test values(row_id.nextval,....)