熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

ORACLE數據庫的基本語法集錦

2013-11-13 16:24:54  來源: 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
  • 上一篇文章:

  • 下一篇文章:
  • 推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.