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,....)

相關文章】

• Oracle中用SQL語句實現進位間互相轉換

• 如何避免Oracle資料庫密碼出現@符號

• Oracle中的to_date參數含義