oracle 建立表，序列，索引，檢視，觸發器，函式，儲存過程，定時器，包體

wuyongde0922發表於2013-12-09
Oracle索引觸發器函式儲存過程定時器
oracle 建立表table：
create table abin1(
id number(20,0) not null,
name varchar2(100)not null,
pwd nvarchar2(100) not null,
create_time date,
constraint pk_abin1 primary key(id)
)

oracle建立索引（index）：
create index myname on abin1(name);

oracle建立序列sequence：
create sequence abin1_seq
minvalue 1
maxvalue 999999999
start with 1
increment by 1
cache 20;
建立觸發器：
create or replace trigger abin1_tri
before insert on abin1
for each row
begin
select abin1_seq.nextval into :new.id from dual;
end;


測試一條記錄：
insert into abin1 (name,pwd,create_time) values ('abin','lee',sysdate); 
呵呵，這裡插入了資料，主鍵自增了，說明成功了。


建立儲存過程procedure：
create or replace procedure abin1_pro
is
cursor mycur is select t.* from abin1 t;
abin mycur%rowtype;
begin
     open mycur;
     loop
        fetch mycur into abin;
        if(abin.name='abin')then
              update abin1 t set t.name='abining',t.pwd=abin.pwd,t.create_time=sysdate where t.id=abin.id;
              commit;
        end if;
        exit when mycur%NOTFOUND;
     end loop;
        if(mycur%ISOPEN)then
             close mycur;
        end if;
end;


測試儲存過程示例：
declare
begin
        abin1_pro;
end;


建立oracle函式function：
create or replace function abin_func
return number
is
total number;
begin
select count(1) into total from abin1 t;
return(total);
end;


建立測試函式示例：
declare
total number;
begin
      total:=abin_func;
      dbms_output.put_line(total);
end;


oracle建立檢視：
create or replace view abin1_view
as
select t.* from abin1 t;


oracle建立package：
create or replace package abin_pac is
procedure abinpac;
end;


oracle建立package body：
create or replace package body abin_pac is
procedure abinpac is
total number;
begin
 select count(1) into total from abin1;
 dbms_output.put_line(total);
end;
end;

測試程式碼：
begin
 abin_pac.abinpac;
end;

相關文章