ORACLE數據庫的基本語法集錦

　　 表
create table test (names varchar()
                   dates date
                   num   int
                   dou   double);
視圖
create or replace view vi_test as
select * from test;

　　 同義詞
create or replace synonym aa
for dbusrcardaa;

　　 存儲過程
create or replace produce dd(v_id in employeeempoy_id%type)
as
begin
  
end
dd;

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

　　 三種觸發器的定義
create or replace trigger ff
alter delete
on test
for each row
declare
begin
   delete from test;
   if sql%rowcount < or sql%rowcount is null then
      rais_replaction_err(錯誤)
   end if
end

　　create or replace trigger gg
alter insert
on test
for each row
declare
begin
   if :oldnames = :newnames then
      raise_replaction_err(編碼重復);
   end if
end

　　create or replace trigger hh
for update
on test
for each row
declare
begin
  if updating then
     if :oldnames <> :newnames then
 reaise_replaction_err(關鍵字不能修改)
     end if
  end if
end

　　 定義游標
declare
   cursor aa is
      select namesnum from test;
begin
   for bb in aa
   loop
        if bbnames = ORACLE then
       
        end if
   end loop;
  
end

　　 速度優化前一語句不後一語句的速度快幾十倍
select namesdates
from testb
where testnames = bnames(+) and
      bnames is null and
      bdates > date(yyyymmdd)

　　select namesdates
from test
where names not in ( select names
                       from b
                      where dates > to_date(yyyymmdd))
                      

　　 查找重復記錄
select namesnum
from test
where rowid != (select max(rowid)
                 from test b
                where bnames = testnames and
                      bnum = testnum)

　　 查找表TEST中時間最新的前條記錄
select * from (select * from test order by dates desc) where rownum <

　　 序列號的產生
create sequence row_id
minvalue
maxvalue
start with
increment by

　　insert into test values(row_idnextval) 

From:http://tw.wingwit.com/Article/program/Oracle/201311/18291.html