的基本语法集锦计划

-- 表

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 ;

-- 存储过程

create or replace produce dd(v_id in poy_id%type)

as

begin

end

dd;

-- 函数

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

-- 三种触发器的定义

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 :s = :s 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 :s :s then

reaise_replaction_err(-2002,\"关键字不能修改\")

end if

end if

end

-- 定义游标

declare

cursor aa is

select names,num from test;

begin

for bb in aa

loop

if s = \"ORACLE\" then

end if

end loop;

end

-- 速度优化，前一语句不后一语句的速度快几十倍

select names,dates

from test,b

以创新为根本 where s = s(+) and

s is null and

tes date(\'\',\'yyyy-mm-dd\')

select names,dates

from test

where names not in ( select names

from b

where dates to_date(\'\',\'yyyy-mm-dd\'))

-- 查找重复记录

select names,num

from test

where rowid != (select max(rowid)

from test b

where s = s and

m = m)

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

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

-- 序列号的产生

create sequence row_id

minvalue 1

maxvalue

start with 1

increment by 1

insert into test values(row_xtval,....)