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;