Oracle基本命令

一、表空间：

  1.创建表空间：
    《表空间名为school，存储数据库路径是E:\ajh\Oracle\data\school.dbf，大小为10M，不能自由增长》
     create tablespace school datafile 'E:\ajh\Oracle\data\school.dbf' size 10m autoextend off;

    《表空间名为teacher，同时创建多个存储数据库teacher1.dbf,teacher2.dbf》
     create tablespace teacher datafile 'e:/ajh/oracle/teacher1.dbf' size 1m, 'e:/ajh/oracle/teacher2.dbf' size 2m autoextend off;

  2.修改表空间：
    《将school表空间的数据文件大小变为100m》
     alter database datafile 'E:\ajh\Oracle\data\school.dbf' resize 100m;
 
    《为school表空间增加一个数据文件student.dbf》
     alter tablespace school add datafile 'e:\ajh\oracle\data\student.dbf' size2m autoextend off;

    《将表空间中的一个数据文件移动到另一位置》
     将表空间离线
     alter tablespace school offline;
     手动剪切、粘贴到另一位置
     修改表空间
     alter tablespace school rename datafile 'e:\ajh\oracle\data\student.dbf' to 'e:\ajh\oracle\student.dbf'; 
     将表空间在线
     alter tablespace school online;

    《将表空间中的数据文件重命名》
     先离线表空间
     再通过资源管理器修改数据文件名称
     然后通过alter tablespace teacher rename datafile 'e:\ajh\oracle\teacher1.dbf' to 'e:\ajh\oracle\teacher3.dbf';
     或alter database rename file 'e:\ajh\oracle\teacher2.dbf' to 'e:\ajh\oracle\teacher4.dbf';
     最后将表空间上线

  3.删除表空间:
    《表空间名为school，including contents只删数据文件内容，datafiles删文件》
     drop tablespace school including contents and datafiles;

二、用户：

  1.创建用户:
    《用户名为:david，密码为：davidpass，默认表空间为：school,临时表空间为：student》
     create user david identified by davidpass default tablespace school temporary tablespace student;

  2.修改用户口令:
    《将用户david的密码改为:password,若口令为数字,需用双引号括起来》
     alter user david identified by password;

  3.删除用户：
    《删除用户david,若该用户拥有模式对象必须使用cascade选项》
     drop user david cascade;

  4.为用户授权：
    《授权用户为：david, 授权：创建会话》
     grant create session to david;

    《授权用户为：david, 授权：允许连接数据库，可创建、删除表或其他对象》
     grant connect to david;

    《授权用户为：david, 授权：允许使用数据库中的空间》
     grant resource to david;

    《授权用户为：david, 授权：允许创建序列，包含在connect连接角色中》
     grant create sequence to david;

    《为user_a用户设置查询user_b用户中的表table_c，需在user_b用户中为user_a设置权限》
     grant select on user_b.table_c to user_a;

    《在ajh用户中为an用户设置删除my_test表的权限，并且an用户可将此权限授予其他用户》 
     grant delete on my_test to an with grant option;
   
  5.撤销授权
    《撤销对用户an的已有(可删除ajh.my_test表记录)的授权》
     revoke delete on ajh.my_test from an;

三、表操作

  1.查询
    《查询当前用户下的所有表》
     select * from tab;

    《查询system表空间中的所有表》
     select * from user_all_tables; 或select *from user_tables;

    《所有用户的表》
     select * from all_all_tables;

    《所有表空间》
     select * from user_tablespaces;

    《查看account表的结构》
     desc account;
 
    《查询my_test表时指定列的别名》
     select id "ID号",name "姓名" from my_test;

    《查询my_test表中无重复的行》
     select distinct * from my_test;

    《在an用户下查询ajh用户中的表my_test的内容(需授权)》
     select * from ajh.my_test;

    《显示每个学生3门课的总分》
     select name,sum(sub1 + sub2 + sub3) from students group by name;

    《显示每个学生的学号、姓名、班级、英语成绩及英语成绩在所在班级的排名，成绩相同的排名相同，并列的排名不影响总的名次》
     select roll_no,name,class,sub2,rank()over(partition by class order by sub2 desc) rank from students;

  2.创建表
    《创建表account》
     create table account(acct varchar2(20);

    《创建表my_test表的副本you_test，但不包含表中的记录》
     create table you_test as select * from my_test where 1=2;

  3.插入表
    《向表account中插入一条记录》
     insert into accout values('00001');

    《从my_test表中选择记录插入到you_test表中》
     insert into you_test select * from my_test where id = '1';

  4.删除表
    《只删you_test表的记录，不删结构》
     truncate table you_test;   (rollback不可回滚)
     或 delete you_test;    (rollback可回滚) 

    《删除全表you_test》
     drop table you_test; 

  5.更新表
    《将my_test表的id为'00002'的记录name改为'yaoming'》
     update my_test set name = 'yaoming' where id = '00002';

四、事务
    1.提交命令 commit
    2.保存点    saveponint
    3.回滚命令  rollback 

五、运行外部文件
    1.运行外部SQL文件
    @'E:\ajh\Oracle\P237学生表.sql'  

    2.导入外部数据文件
    在DOS--cmd下运行：
    E:\ajh\oracle\>D:\oracle\ora90\BIN\sqlldr system/tcaccp control = mark.ctl log = a.log
    前面为mark.ctl文件的路径          sqlldr为运行命令     log = a.log是日志文件

六、锁
    1.行级锁(只对用户正在访问的行进行锁定，其他用户可以更新同一表中该行之外的数据)
    select * from employee where empno = 'E006' for update;   (锁定)
    select * from employee where empno = 'E006' for update wait 5;   (等待5秒，防无限等待)

    2.表级锁：
    共享锁 (仅允许其他用户查询表中的行，但不允许插入、更新或删除行)
    lock table employee in share mode [nowait];
    行共享锁 (允许多个用户同时更新同一表中的不同行)
    lock table employee in row share mode [nowait];
    行排他 ()
    lock table employee in row exclusive mode [nowait];
    共享行排他 ()
    lock table employee in share row exclusive mode [nowait];
    排他 (仅允许查询)
    lock table employee in exclusive mode [nowait];

七、表分区
    1.范围分区
    create table employee_ajh
    (
         emp_id varchar2(5) primary key,
         ename varchar2(25),
         dateofjoining date
    )
    partition by range(dateofjoining)
    (
      partition p1 values less than (to_date('01/04/2001','dd/mm/yyyy')) tablespace t1,
      partition p2 values less than (to_date('01/09/2005','dd/mm/yyyy')) tablespace t2,
       partition p3 values less than (maxvalue) tablespace t3
    )
  
    2.散列分区
    ......
    partition by hash(product_id)
    (
     partition p1,
     partition p2,
     partition p3
    )
    或
    ''''''
    partition by hash(product_id)
    partitions 4 [store in (t1,t2,t3)]

    注：user_tab_partitions 表可查询分区表
    select table_name,partition_name from user_tab_partitions;
 
    3.复合分区
    create table Annual_ajh
    (
     product_id varchar2(5),
     sales_date date
    )
    partition by range(sales_date)
    subpartition by hash(product_id)
    subpartitions 8
    (
     partition p1 values less than (to_date('01/09/2004','dd/mm/yyyy'))
    )
    /
    4.列表分区
    ......
    partition by list(department)
    (
     partition p1 values ('Accounts'),
     partition p2 values ('Management')
    )
    /

    5.插入记录
    insert into employee_ajh values ('001','ajh','31/8月/2004');

    6.查询分区记录
    select * from employee_ajh partition (p2);

    7.删除分区记录
    delete from employee_ajh partition (p2);

八、分区维护
    1.添加分区(如果在表的开始或中间，或是最高分区边界是maxvalue，则应使用split partition语句)
    alter table employee_ajh add partition p4 values less than(to_date('01/04/2006','dd/mm/yyyy'));
 
    2.删除分区
    alter table employee_ajh drop partition p4;

    3.截断分区(删除表分区中的所有记录)
    alter table employee_ajh truncate partition p3;

    4.合并分区
    alter table employee_ajh merge partitions p21,p22 into partition p2;

    5.拆分分区
    alter table employee_ajh split partition p2 at (to_date('01/04/2000','dd/mm/yyyy')) into

(partition p21,partition p22)

    6.移动分区(将p3分区移动到work表空间中)
 alter table employee_ajh move partition p3 tablespace work;

九、同义词
    1.创建私有同义词(tick)
    create synonym tick for ticket_header;

    2.创建公有同义词(pub_tick)(在system用户下为ajh用户的ticket_header表创建)
    create public synonym pub_tick for ajh.ticket_header;

     (先为ajh授权，方可在ajh用户下创建公有同义词)
    grant create public synonym to ajh;
    create public synonym pub_tick for ticket_header;

    3.删除同义词
    drop synonym tick;

    grant drop public synonym to ajh;
    drop public synonym pub_tick;

十、序列
    1.创建序列(步长为2，正为升序，负为降序，开始值25，最大值75，最小值25，
      到头后会继续从头开始生成值，不用预先分配序列号)
    create sequence new_seq
    increment by 2
    start with 25
    maxvalue 75
    minvalue 25
    cycle
    nocache;

    2.访问序列
      (nextval第一次为开始值，后继续增长)
      select new_seq.nextval from dual;

      (currval显示当前序列)
      select new_seq.currval from dual;

    3.更改序列(将序列的步长变为5)
    alter sequence new_seq increment by 5;

    4.删除序列
    drop sequence new_seq;

十一、视图
    1.创建视图
      (可根据day列升序排列)
      create view fleet as select * from fleet_header order by day;

      (with check option指定只能插入或更新视图可以访问的行，constraint指定约束的列名)
      create or replace view fleet as select * from fleet_header with check option constraint name;
 
      (with read only指定不能在此视图上执行任何修改操作,只读)
      create or replace view fleet as select * from fleet_header with read only;

    2.修改视图(or replace表示如果该对象存在，则重新创建)
      create or replace view fleet as select day,route_id from fleet_header;

    3.创建带有错误的视图(将会有一个警告信息)(product表不存在)
      create force view myview as select * from product;

    4.联接视图(若最后有+号，则表示是外联接)
      create view double(fleet_id,origin,ticket_no,destionation)
      as select f.fleet_id,origin,ticket_no,destination
      from fleet_header f,ticket_header t
      where f.fleet_id = t.fleet_id(+);

    5.键保留表
      如果视图包含了一个表的主键，并且也是这个视图的主键，则这个键被保留，这个表称为键保留表。
      包含外联接的视图通常不包含键保留表，除非外部联接生成非空的值。
      只有键保留表才能使用DML语句。
      通过数据字典视图user_updatable_columns可以确定联接视图中可更新的列。

    6.更新视图中的行
      update fleet set name = 'ajh';

    7.删除视图
      drop view fleet;

    8.查询视图信息
      user_views数据字典

十二、索引
    1.创建标准索引(为ticket_header表的adults列建立索引)
      create index aud_index on ticket_header(adults);

    2.创建唯一索引(可确保place_name列中任意两行的值都不相同，一般是主键列)
      create unique index place_ind on place_header(place_name);

    3.创建组合索引
      create index comp_index on route_header(route_id,route_no);

    4.创建反向键索引(反转索引列中的每一个字节，优点在于可以将插入操作分布在整个索引上)
      create index rev_ind on route_header(route_no) reverse;

      (将反向键索引重建为标准索引)
      alter index rev_index rebuild noreverse;

    5.创建位图索引(用于低基数列，也就是列的取值只能在可以列举的有限的范围内)
      create bitmap index bit_ind on route_header(cat_code);

    6.创建基于函数的索引(指定大写作为查询条件)
      create index ucase_name_ind on fleet_header (upper(name));
      select * from fleet_header where upper(name) = 'SMITH';

    7.索引中的分区
      局部分区索引(可通过user_segments数据字典查询)
      create index myind on order_mast(orderno) local;
 
      全局分区索引(不能在散列分区或子分区建立全局索引)
      create index myind on order_mast(orderno) global;

      全局非分区索引
      是在分区上创建的全局索引，它类似于非分区表上的索引，索引的结构不会被分割。

    8.删除索引
      drop index emp_ind1;

    9.获得索引的信息
      user_indexes  获取有关用户已创建索引的详细信息
      user_ind_partitions 获取有关用户已创建的分区索引的详细信息
      user_ind_columns 获取有关列(用户的索引是基于这些列创建的)详细信息

十三、动态SQL
  
 execute immediate语句只能用于处理返回单行或没有返回的SQL语句。

    以下示例执行了带参数的select语句。
      declare
 sql_stmt varchar2(20);
 stu_id number := 111;
 stu_rec student%rowtype;
      begin
 sql_stmt := 'select * from student where stuid = :id';
 execute immediate sql_stmt into stu_rec using stu_id;
      end;

十四、异常
    1.异常种类：
 access_into_null 未初始化对象
 case_not_found  case语句中的选项与用户输入的数据不匹配
 collection_is_null 对尚未初始化的表或数组赋值
 cursor_already_open 用户试图重新打开已经打开的游标
 dup_val_on_index 用户试图将重复的值存储在使用唯一索引的数据库列中
 invalid_cursor  执行非法游标运算(如打开一个尚未打开的游标)
 invalid_number  将字符串转换为数字时
 login_denied  输入的用户名或密码无效
 no_data_found  表中不存在请求的行
 storage_error  内存损坏或PL/SQL耗尽内在时
 too_many_rows  有多行返回语句时
 value_error  产生大小限制错误，如变量中的列值超出变量的大小
 zero_divide  以零作除数时

    2.用户自定义的异常：
 可使用RAISE语句显示引发。
 
    3.引发应用程序错误
 可向应用程序抛出异常，便于捕获，使用过程：
 raise_application_error(error_number,error_message);
 error_number  表示异常指定的编号，必须是介于-20000和20999之间。
 error_message 表示用户为异常指定的消息文本。长度可达2048字节。
 
十五、游标
    1.游标属性：
 %found  DML语句影响一行或多行时，返回TRUE
 %notfound 没有影响任何行时，返回TRUE
 %rowcount 返回DML语句影响的行数
 %isopen  返回游标是否已经打开

    2.显式游标：
 declare
 --声明变量
 my_stu_mark studentinfo.mark%type;
 --声明游标
 cursor stu_cur is
        select mark from studentinfo where clsid = 'c'; 
 begin
 --打开游标
      open stu_cur;
 --循环，从游标中获取记录
      loop
          fetch stu_cur into my_stu_mark;
           exit when stu_cur%notfound;
           dbms_output.put_line(my_stu_mark);
      end loop;
 --关闭游标
      close stu_cur;
 end;   

    3.使用显式游标删除或更新
 declare
 new_mark number;
 --声明游标
 cursor stu_cur is select mark from studentmark
        where clsid = 'sql' and testdate < (sysdate - 365)
     --必须使用select...for update语句
          for update of mark;
 begin
   --打开游标
       open stu_cur;
       loop
          fetch stu_cur into new_mark;
          exit when stu_cur%notfound;  
          update studentmark set mark = 90
   --执行delete和update时使用where current of 子句指定游标的当前行
          where current of stu_cur;
       end loop;
      dbms_output.put_line(stu_cur%rowcount);
   --关闭游标
      close stu_cur;
      commit;
 end;

    4.循环游标：
 隐式打开游标，自动从活动集中获取行，然后在处理完所有行时关闭游标。
 自动创建%rowtype类型的变量并将此变量用作记录索引。
 declare
 cursor stu_cur is
          select mark from studentinfo where clsid = 'c';
 begin
    --stu_mark便是%rowtype类型的变量
        for stu_mark in stu_cur
        loop
              dbms_output.put_line(stu_mark.mark);
        end loop;
 end;
 
    5.REF游标：
 
 declare
 --声明ref cursor类型的游标stu_tea_cur,如果后面有return record_type,则表示该游标为强类型REF游标
 type stu_tea_cur is ref cursor;
 --声明ref cursor类型的游标变量refcur
 refcur stu_tea_cur;
 --声明变量
 p_table varchar2(10);
 p_name varchar2(10);
 p_address varchar2(20);
 selection varchar2(2);
 selectname varchar2(10);
 begin
      selection := '&selection';
      if selection ='1' then
         p_table := 'teacher';
      elsif selection = '2' then
         p_table := 'student';
      end if;
    --打开游标
    open refcur for
         'select name,address from '|| p_table;
         --从游标中获取记录，赋给变量
      fetch refcur into p_name,p_address;
    --如果发现记录，则循环显示，再获取记录
      while refcur%found loop
          dbms_output.put_line(p_name || ' : ' || p_address);
          fetch refcur into p_name,p_address;
      end loop;    
    --关闭游标
     close refcur;

          --根据姓名显示地址
    selectname := '&name';
          --再次打开游标
   open refcur for
          --此处可为组合的字符串，表名可动态加载，可用占位符动态传参
           'select address from '|| p_table || ' where name = :2'
   --指定参数
    using selectname;
          --获取记录
   loop
    fetch refcur into p_address;
  --没有记录则退出         
           exit when refcur%notfound
     dbms_output.put_line(p_address);
       fetch refcur into p_address;
    end loop;
   --关闭游标
   close refcur;    
 end;