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 endcreate 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 1insert into test values(row_id.nextval,....) |
相关文章】
http://www.htsjk.com/shujukujc/19189.html
www.htsjk.Com
true
http://www.htsjk.com/shujukujc/19189.html
NewsArticle
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、存...
本站文章为和通数据库网友分享或者投稿,欢迎任何形式的转载,但请务必注明出处.
同时文章内容如有侵犯了您的权益,请联系QQ:970679559,我们会在尽快处理。