ORACLE的基本文法集錦

版權聲明：本文為部落客原創文章，未經部落客允許不得轉載。 https://blog.csdn.net/chinahuyong/article/details/2573426

ORACLE的基本文法集錦

-- 表

create table test (names varchar2(12),

                   dates date,

                   num   int,

                   dou   double);

-- 視圖

create or replace view vi_test as

select * from test;

-- 同義詞

create or replace synonym aa

for dbusrcard001.aa;

-- 存儲過程

create or replace produce dd(v_id in employee.empoy_id%type)

as

begin

end

dd;

-- 函數

create or replace function ee(v_id in employee%rowtype) return varchar(15)

is

var_test varchar2(15);

  return var_test;

exception when others then

-- 三種觸發器的定義

create or replace trigger ff

alter delete

on test

for each row

declare

   delete from test;

   if sql%rowcount < 0 or sql%rowcount is null then

      rais_replaction_err(-20004,"錯誤")

   end if

create or replace trigger gg

alter insert

   if :old.names = :new.names then

      raise_replaction_err(-2003,"編碼重複");

create or replace trigger hh

for update

  if updating then

     if :old.names <> :new.names then

 reaise_replaction_err(-2002,"關鍵字不能修改")

     end if

  end if

end

-- 定義遊标

   cursor aa is

      select names,num from test;

   for bb in aa

   loop

        if bb.names = "ORACLE" then

        end if

   end loop;

-- 速度優化，前一語句不後一語句的速度快幾十倍

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'))

-- 查找重複記錄

select names,num

where rowid != (select max(rowid)

                 from test b

                where b.names = test.names and

                      b.num = test.num)

-- 查找表TEST中時間最新的前10條記錄

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

-- 序列号的産生

create sequence row_id

minvalue 1

maxvalue 9999999999999999999999

start with 1

increment by 1

insert into test values(row_id.nextval,....)