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);
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 :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

-- 定义游标
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

-- 速度优化，前一语句不后一语句的速度快几十倍
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
from test
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 8888888888888888888888
start with 1
increment by 1

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

 

 

create table EMP
(
  EMPNO    NUMBER(4) not null,
  ENAME    VARCHAR2(10),
  JOB      VARCHAR2(9),
  MGR      NUMBER(4),
  HIREDATE DATE,
  SAL      NUMBER(7,2),
  COMM     NUMBER(7,2),
  DEPTNO   NUMBER(2)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
comment on column EMP.EMPNO
  is '雇员编号';
comment on column EMP.ENAME
  is '雇员的名称';
comment on column EMP.JOB
  is '职位';
comment on column EMP.MGR
  is '该员工的上级的编号';
comment on column EMP.HIREDATE
  is '入职日期';
comment on column EMP.SAL
  is '薪水';
comment on column EMP.COMM
  is '奖金';
comment on column EMP.DEPTNO
  is '部门编号';
alter table EMP
  add constraint PK_EMP primary key (EMPNO)
  using index
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table EMP
  add constraint FK_DEPTNO foreign key (DEPTNO)
  references DEPT (DEPTNO);

 

 

 

********************************************************************

oracle--sign

取数字n的符号,大于0返回1,小于0返回-1,等于0返回0

1. SQL> select sign(100),sign(-100),sign(0) from dual;  
2.   
3. SIGN(123) SIGN(-100)   SIGN(0)  
4. --------- ---------- ---------  
5.     1           -1         0 
   

********************************************************************

 

 decode 用法：