oracle操作命令

创建用户

//启动监听
lsnrctl start
//切换oracle用户
su oracle
//以操作系统权限认证的oracle sys管理员登陆
sqlplus / as sysdba
//启动oracle数据库
startup
//system用户登录
connect system@prod
//创建用户
create user gree identified by gree;
//给用户赋权
grant connect,resource,dba to gree;
//新用户登录
connect gree@prod;

解锁用户

//解锁用户
alter user scott account unlock;
//设置Scott用户密码
alter user scott identified by tiger;

创建、删除表空间

//删除表空间
drop tablespace ts1;
//创建表空间
create tablespace ts1 datafile '/home/oracle/tb/ts1.dbf' size 50M;
//修改当前用户默认表空间为ts1
alter database default tablespace ts1;

创建表

create table class(id number,name varchar2(32));
select * from class;
select id,name from class;

创建序列

CREATE SEQUENCE sequence //创建序列名称
[INCREMENT BY n] //递增的序列值是 n 如果 n 是正数就递增,如果是负数就递减 默认是 1
[START WITH n] //开始的值,递增默认是 minvalue 递减是 maxvalue
[{MAXVALUE n | NOMAXVALUE}] //最大值  
[{MINVALUE n | NOMINVALUE}] //最小值
[{CYCLE | NOCYCLE}] //循环/不循环
[{CACHE n | NOCACHE}];//分配并存入到内存中

create sequence class_seq minvalue 1 nomaxvalue increment by 1 start with 1 nocache;
//序列调用，获取序列的下一个值
select class_seq.nextval from dual;
//查看当前序列的值
select class_seq.currval from dual;

创建触发器

create or replace trigger class_trigger_insertid
before
  select class_seq.nextval into:new.id from dual;
end;
//创建表，序列，触发器后，才能实现表id自增长

触发器的其他使用

1、设置自增

//创建t_booktype表添加主键无命名
create table t_booktype(
id number,
typename varchar2(32),
primary key(id)
);
//查找主键名
select * from user_cons_columns;
SYS_C0011016
//删除主键约束
alter table t_booktype drop constraint SYS_C0011016;
//添加主键约束
alter table t_booktype add constraint pk_t_booktype primary key(id);
//创建序列
create sequence tbooktype_seq minvalue 1 nomaxvalue increment by 1 start with 1 nocache;
//触发器
create or replace trigger tbooktype_trigger_insertid
before insert on t_booktype for each row
begin
select tbooktype_seq.nextval into:new.id from dual; 
end;
insert into t_booktype(typename) values('英语');
insert into t_booktype(typename) values('计算机');
select * from t_booktype;

//创建表t_book添加外键有命名
create table t_book(
id number primary key,
bookname varchar(32),
typeid number,
constraint fk_typeid foreign key(typeid) references t_booktype(id)
);
//创建序列
create sequence tbook_seq minvalue 1 nomaxvalue increment by 1 start with 1 nocache;
//创建触发器
create or replace trigger tbook_trigger_insertid
before insert on t_book for each row
begin
select tbook_seq.nextval into:new.id from dual;
end;
insert into t_book(bookname,typeid) values('疯狂英语',1);
insert into t_book(bookname,typeid) values('7天精通scala',2);
select * from t_book;

2、设置权限

//使用触发器设置权限
create or replace trigger delete_book
before delete on t_book
begin
if user!='GREE' then
  raise_application_error(-20001,'权限不足，只能是GREE用户');
end if;
end;

3、通过触发器统计操作日志

//创建t_book_oplog表
create table t_book_oplog(
actionuser varchar2(32),
actionname varchar2(32),
actiontime date
);
//创建触发器
create or replace trigger tr_op_tbook
after insert or update or delete on t_book
begin
if updating then
  insert into t_book_oplog values(user,'update',sysdate);
else if inserting then
  insert into t_book_oplog values(user,'insert',sysdate);
else if deleting then
  insert into t_book_oplog values(user,'delete',sysdate);
end if;
end if;
end if;
end;
insert into t_book(bookname,typeid) values('5天精通java',2);
update t_book set bookname='10天精通java' where id = 3;
select * from t_book_oplog;

函数

1、不传值

create or replace function getopcount return number as 
begin
  declare op_count number;
  begin
    select count(*) into op_count from t_book_oplog;
    return op_count;
  end;
end getopcount;
select getopcount() from dual;

2、传值

create or replace function getopcountby(opname varchar) return number as
begin
  declare op_count number;
  begin
    select count(*) into op_count from t_book_oplog where actionname= + opname;
    return op_count;
  end;
end getopcountby;
select getopcountby('insert') from dual;

create or replace function getopcountby2(opname varchar) return number as
begin
  declare op_count number;
  query_sql varchar2(300);
  begin
    query_sql:='select count(*) from t_book_oplog where actionname='||''''||opname||'''';
    execute immediate query_sql into op_count;
    return op_count;
  end;
end getopcountby2;
select getopcountby2('insert') from dual;