数据库入门

初始表空间以及用户的创建

//创建临时表空间

create temporary tablespace user_temp
tempfile 'D:\DataBase\user_temp.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;

//创建数据表空间
create tablespace user_data
logging
datafile 'D:\DataBase\user_data.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;

//创建用户并指定表空间
create user user_dba identified by password
default tablespace user_data
temporary tablespace user_temp;

//给用户授予权限

grant connect,resource,dba to username;

//以后以该用户登录，创建的任何数据库对象都属于user_temp 和user_data表空间，这就不用在每创建一个对象给其指定表空间了

建表后为表添加主键

alter table t
add constraint pk_id primary key(object_id);

更改列名和属性

//更改列名
alter table tablename remane column a to b;
//更改属性
alter table records modify(a timestamp);
//增加一列
alter table records add(a numeric(8));

简单触发器

create or replace TRIGGER pointchange
AFTER INSERT ON records
FOR EACH ROW
DECLARE
  tag NUMERIC(6);
BEGIN
  SELECT COUNT(cno) INTO tag FROM MEM_CARD WHERE cno = :new.cno;
  if (tag > 0) THEN
    UPDATE mem_card
    SET point = point + :new.cchange
    WHERE cno = :new.cno;    
  else 
    RAISE_APPLICATION_ERROR(-20001,'没有该会员');
  end if;
END;