首页
JAVA笔记
数据库笔记
混乱记忆
小站记
您现在的位置
oracle简单实用手册
简介: oracle实用操作手册主要记录对表、表字段、表索引、触发器、表空间、权限操作

 oracle实用操作手册主要记录对表、表字段、表索引、触发器、表空间、权限操作

select * from user_tables;
desc mytable;
select * from user_constraints where table_name='MYTABLE'
select * from  mytable;

drop table mytable;
create  table mytable(
id int,
name varchar(10)
)

alter table mytable add password varchar(16);
alter table mytable modify (password not null);
alter table mytable modify (password null);
alter table mytable modify (password int);--修改类型时列中要求无数据
alter table mytable modify (password varchar(20));
alter table mytable add constraint mycheck check(password='pass');
alter table mytable add constraint myunique unique (password,name);--表约束
alter table mytable add constraint mypk primary key (id,name,password);
alter table mytable add constraint fk_test foreign key(id) references mytable(id); 
alter table mytable rename column password to pass;
alter table mytable drop constraint mycheck;
alter table mytable drop constraint myunique;
alter table mytable drop constraint mypk;
alter table mytable drop column pass;

comment on TABLE tableName is '表注释';
comment on COLUMN tableName.columnName is '字段注释';
--查询表注释 : select table_name, comments from user_tab_comments where table_name ='MYTABLE';
--查询字段注释: select table_name,column_name, comments from user_col_comments where table_name ='MYTABLE';


create index myindex on mytable(id); --添加索引
create index myindex on mytable(id) tablespace INDX; --指定表空间
drop index myindex;

select * from user_tablespaces;

create sequence mysequence
     start with 1      --起始值
     increment by 1    --步长
     maxvalue 30       --最大值
     minvalue 1        --最小值
     cycle             --循环
     cache 4           --缓存4个数
    
insert into mytable values(mysequence.nextval,'a');

/**
不要去通过视图来修改基表,视图只用于数据的查询
能否通过视图去改表中的数据呢???
         有条件的可以:
           <1>建立视图的select语句必须是简单的select语句
               简单:不能是多表的查询
                    不能有分组函数
           <2>建立视图的时候不能带with readonly关键字
           **/
drop view myview;
create view myview as select * from mytable; --创建视图
create view myview as select * from mytable with read only;--只读视图
create view myview as select * from mytable where name='name' with check option;

select * from myview;

触发器类型:
1、 语句触发器
2、 行触发器
3、 INSTEAD OF 触发器
4、 系统条件触发器
5、 用户事件触发器

 

1、 语句触发器
是在表上或者某些情况下的视图上执行的特定语句或者语句组上的触发器。能够与INSERT、UPDATE、

DELETE或者组合上进行关联。但是无论使用什么样的组合,各个语句触发器都只会针对指定语句激活一次

。比如,无论update多少行,也只会调用一次update语句触发器。

例子:
需要对在表上进行DML操作的用户进行安全检查,看是否具有合适的特权。
Create table foo(a number);

Create trigger biud_foo
 Before insert or update or delete
  On foo
Begin
 If user not in (‘DONNY’) then
  Raise_application_error(-20001, ‘You don’t have access to modify this table.’);
 End if;
End;
/

即使SYS,SYSTEM用户也不能修改foo表

[试验]
对修改表的时间、人物进行日志记录。

1、 建立试验表
create table employees_copy as select *from hr.employees

2、 建立日志表
create table employees_log(
  who varchar2(30),
  when date);

3、 在employees_copy表上建立语句触发器,在触发器中填充employees_log 表。
Create or replace trigger biud_employee_copy
  Before insert or update or delete
   On employees_copy
 Begin
  Insert into employees_log(
   Who,when)
  Values( user, sysdate);
 
 End;
 /
4、 测试
update employees_copy set salary= salary*1.1;

select *from employess_log;

5、 确定是哪个语句起作用?
即是INSERT/UPDATE/DELETE中的哪一个触发了触发器?
可以在触发器中使用INSERTING / UPDATING / DELETING 条件谓词,作判断:
begin
  if inserting then
   -----
  elsif updating then
   -----
  elsif deleting then
   ------
  end if;
end;

if updating(‘COL1’) or updating(‘COL2’) then
  ------
end if;

[试验]
1、 修改日志表
alter table employees_log
  add (action varchar2(20));

2、 修改触发器,以便记录语句类型。
Create or replace trigger biud_employee_copy
  Before insert or update or delete
   On employees_copy
 Declare
  L_action employees_log.action%type;
 Begin
  if inserting then
   l_action:=’Insert’;
  elsif updating then
   l_action:=’Update’;
  elsif deleting then
   l_action:=’Delete’;
  else
   raise_application_error(-20001,’You should never ever get this error.’);

  Insert into employees_log(
   Who,action,when)
  Values( user, l_action,sysdate);
 End;
 /

3、 测试
insert into employees_copy( employee_id, last_name, email, hire_date, job_id)
 values(12345,’Chen’,’Donny@hotmail’,sysdate,12);

select *from employees_log

update employees_copy set salary=50000 where employee_id = 12345;

2、 行触发器
是指为受到影响的各个行激活的触发器,定义与语句触发器类似,有以下两个例外:
1、 定义语句中包含FOR EACH ROW子句
2、 在BEFORE……FOR EACH ROW触发器中,用户可以引用受到影响的行值。
比如:

定义:
create trigger biufer_employees_department_id
 before insert or update
  of department_id
  on employees_copy
 referencing old as old_value
     new as new_value
 for each row
 when (new_value.department_id<>80 )
begin
 :new_value.commission_pct :=0;
end;
/

 

create or replace trigger mytrigger
before insert or update or delete
on mytable
for each row
declare   --要定义变量时需要declare
myrow mytable%rowtype;
begin
   myrow.pass :=:new.pass;   --不能进行行复制
   dbms_output.put_line(myrow.pass);
    if inserting then
        insert into test values(1,:new.name,'1');
    else if updating then
        insert into test values(2,:old.name,:old.pass); --old表示久
        insert into test values(3,:new.name,:new.pass); --new表示新
    end if;
    end if;

end;
show errors;

 

 

Referencing 子句:
执行DML语句之前的值的默认名称是 :old ,之后的值是 :new
insert 操作只有:new
delete 操作只有 :old
update 操作两者都有

referencing子句只是将new 和old重命名为new_value和old_value,目的是避免混淆。比如操作一个名为

new的表时。
作用不很大。

 

[试验]:为主健生成自增序列号

drop table foo;
create table foo(id number, data varchar2(20));
create sequence foo_seq;

create or replace trigger bifer_foo_id_pk
 before insert on foo
 for each row
begin
 select foo_seq.nextval into :new.id from dual;
end;
/

insert into foo(data) values(‘donny’);
insert into foo values(5,’Chen’);
select * from foo;

3、 INSTEAD OF 触发器更新视图

Create or replace view company_phone_book as
 Select first_name||’, ’||last_name name, email, phone_number,
employee_id emp_id
From hr.employees;

尝试更新email和name
update hr.company_phone_book
 set name=’Chen1, Donny1’
where emp_id=100

create or replace trigger update_name_company_phone_book
INSTEAD OF
Update on hr.company_phone_book
Begin
 Update hr.employees
  Set employee_id=:new.emp_id,
   First_name=substr(:new.name, instr(:new.name,’,’)+2),
   last_name= substr(:new.name,1,instr(:new.name,’,’)-1),
   phone_number=:new.phone_number,
   email=:new.email
 where employee_id=:old.emp_id;
end;

 

4、 系统事件触发器
系统事件:数据库启动、关闭,服务器错误

create trigger ad_startup
 after startup
  on database
begin
 -- do some stuff
end;
/


5、 用户事件触发器
用户事件:用户登陆、注销,CREATE / ALTER / DROP / ANALYZE / AUDIT / GRANT / REVOKE /

RENAME / TRUNCATE / LOGOFF

例子:记录删除对象

1. 日志表
create table droped_objects(
 object_name varchar2(30),
 object_type varchar2(30),
 dropped_on date);

2.触发器
create or replace trigger log_drop_trigger
 before drop on donny.schema
begin
 insert into droped_objects values(
  ora_dict_obj_name,  -- 与触发器相关的函数
  ora_dict_obj_type,
  sysdate);
end;
/


3. 测试
create table drop_me(a number);
create view drop_me_view as select *from drop_me;
drop view drop_me_view;
drop table drop_me;

select *from droped_objects


禁用和启用触发器
alter trigger <trigger_name> disable;
alter trigger <trigger_name> enable;

事务处理:
在触发器中,不能使用commit / rollback
因为ddl语句具有隐式的commit,所以也不允许使用

视图:
select * from user_triggers


游标

set serveroutput on
declare
cursor mycursor is select * from emp;
myrowdata mycursor%rowtype;
begin
open mycursor;
loop
 fetch mycursor into myrowdata;
dbms_output.put_line(myrowdata.job||'-'||myrowdata.sal);
exit when mycursor%notfound;
end loop;
close mycursor;
end;

%found --fetch 有数据则为true;
%notfound fetch 无数据则为true;


层次查询
--level 伪列 层次查询的时候可以使用的  层的编号

  select lpad('+',level,' ')||ename from emp
     connect by prior empno = mgr --父子关系 父结点中的empno = 子节点中的mgr
     start with mgr is null;--从 mgr is null的节点 开始遍历


表空间
    1、先查询空闲空间

  select tablespace_name,file_id,block_id,bytes,blocks from dba_free_space;

  2、增加Oracle表空间

  先查询数据文件名称、大小和路径的信息,语句如下:

  select tablespace_name,file_id,bytes,file_name from dba_data_files;

  3、修改文件大小语句如下

  alter database datafile '需要增加的数据文件路径,即上面查询出来的路径 'resize 800M;

  4、创建Oracle表空间

  create tablespace test datafile '/home/app/oracle/oradata/oracle8i/test01.dbf' size 8M autoextend on next 5M maxsize 10M;

    5、为表空间增加数据文件:

  alter tablespace sales add datafile '/home/app/oracle/oradata/oracle8i/sales02.dbf' size 800M autoextend on next 50M maxsize 1000M;

    6、建立用户
    create user userName identified by userPass;   

    6、设置用户默认表空间
    alter user userName default tablespace tablSpaceName;

    7、建表时指定表空间
       create table tableTest(id int) tablespace tableSpaceName;
 
    8、赋予表空间的使用权
 grant resource to userName;

    9、Oracle查询用户表:select * from user_all_tables

  10、Oracle查询所有函数和储存过程的定义语句:select * from user_source

  11、Oracle查询所有用户:select * from all_users;

    12、Oracle 查所有管理员用户select * from dba_users

  13、Oracle查看当前用户连接:select * from v$Session

  14、Oracle查看当前用户权限:select * from session_privs

 

 

 

部分特性操作
    1、查看当前游标数
       show parameter open_cursors;
    1.1、查看当前已经打开的游标数
       select count(*) from v$open_cursor;

    2、修改系统开启游标总数
   --    alter system set open_cursor = 300 scope = both;
       alter system set open_cursors=1200  scope=both;
       alter system set session_cached_cursors=60 scope=spfile;


数据误删的回复
ALTER TABLE FND_EMPLOYEE_T ENABLE row movement ;
flashback table FND_EMPLOYEE_T to timestamp to_timestamp('2013-03-28 1:40:00','yyyy-mm-dd hh24:mi:ss');