Oracle 基础入门教材

建表
create table <table_name>(
column_name1 column_type [not null] [check (expression)]
     [default value] [primary key][references <table_name>(column_name)],
column_name2 column_type [not null] [check (expression)]
     [default value] [primary key][references <table_name>(column_name)]
....
[constraint primary_name
  primary key(column_name1,column_name2...)
]
[unique(<column_name1,column_name2...>)]
                           );

create table <table_name>
                as
             select * from <other_table_name> where <condition>
---------------------------------------------------------------------------------------
改表结构
1 alter table <table_name> add(column_name column_type);
2 alter table <table_name> modify(column_naem column_type);
---------------------------------------------------------------------------------------
删表
drop table <table_name>;
truncate table <table_name>;
---------------------------------------------------------------------------------------
表的重命名
rename <table_name1> to <table_name2>;
---------------------------------------------------------------------------------------
插入数据
1 insert into <table_name> (column_name1, column_name2, ....)
     values  (column_value1, column_value2,.... );
2 insert into <table_name>
     values  (column_value1, column_value2,.... );     
3 insert into <table_name> (column_name1, column_name2, ....)
         select expression1,expression2, ....
          from  <table_name>
          [where <condition_expression>];
---------------------------------------------------------------------------------------
表的定义与数据插入
create table <table_name> (column_name1, column_name2, ....)
             [ as child_select];
---------------------------------------------------------------------------------------
修改数据
1 update <table_name> [other_name]
    set  column_name1= expression1,column2=expression2, ....
    [where <condition_expression>];
2 update <table_name> [other_name]
    set  column_name1,column2, .... =child_select
    [where <condition_expression>];
---------------------------------------------------------------------------------------
删除数据
delete from <table_name>
      [where <condition_expression>];
---------------------------------------------------------------------------------------
数据查询
select [all| distinct] <[table_name.*]|expression1,expression2,...>
   from table_name1.* [other_name1],table_name2.* [other_name2],...
   [where <condition_expression>]
   [connect by <expression> [startwith] <condition_expression> ]
   [group by expression1,expression2,....]
   [having <condition>]
   [{union|intersect|minus} select...]
   [
    order by {expression1|labor1} [asc|edsc],{expression2|labor2}
    [asc|edsc] for update of column_name1,column_name2,...[no wait]
   ];
---------------------------------------------------------------------------------------
索引
  create [unique] index <index_name> on
                      <table_name> <column_name1,column_name2,...>
  tabspace tabspace_name;
 
  drop index <index_name>;
---------------------------------------------------------------------------------------
视图
create
or replace view <view_name> [view_column_name]
    as  <child_select>
        [with check option;]
          [with read only;]

drop view <view_name>;
---------------------------------------------------------------------------------------
存储过程
  ====================存储过程主要用于处理复杂的业务，而且易于维护
  ====================创建存(储过程/函数/包)的用户必须具有 CREATE PROCEDURE
  ==================== 或 CREATE ANY PROCEDURE 的权限
  create [or replace] procedure <procedure_name>
                        (<var_name> in/out/inout <var_type>)
  as/is
    var_name var_type
   begin
     ...
     exception
     ...
   end;
  execute procedure_name(value1,value2...);

其中exception中：可用  when <condition> then .....;
                       when <condition1> or <condition2> then .......;
                       when OTHERS then .......;
其中<condition>包括： 
        condition value        sqlcode                        condition       
        CURSOR_ALREADY_OPEN         (-6511)                试图打开一个已打开的光标
        DUP_VAL_ON_INDEX        (-1)                唯一索引中的数据重复
        INVALID_CURSOR                (-1001)                使用未打开的光标
        INVALID_NUMBER                (-1722)                字符串转换成数据时出错
        LOGIN_DENIDE                (-1017)               
        NO_DATE_FOUND                (-1403)                select语句基于的条件检索数据不存在
        NOT_LOGGED_ON                (-1012)
        PROGRAM_ERROR                (-6501)
        STORAGE_ERROR                (-6500)
        STORAGE_ERROR                (-0051)
        TOO_MANY_ROWS                (-1427)                使用隐式光标时一次检测到多行数据
        TRANSACTION_BACKED_OUT        (-0061)
        VALUE_ERROR                (-1476)                指定目标域的长度小于待放入其中的数据长度
        ZERO_DIVID               
        OTHERS
        EXCEPTION
  
        raise_application_error (<error_code>,<error_text>) 用于返回自定义错误信息
                其中error_code 的编码范围在 -20001 到 -20999 之间
     可用 grant procedure on <procedure_name> to <user_object>   赋权
     运行 execute <procedure_name>(<value1>,<value2>,...)
     重新编译：alter procedure <procedure_name> compile;
--------------------------------------------------------------------------------------------  
用connect by 遍历家族树
        { 使用connect by的顺序，select   ,from  ,where ,start with ,connect by ,order by }
     //where子句将从树中删掉单个节点，但保留它的后代， 而connect by 的限定将删除单个节点
     //及其后代。
        level 是表示接点层数的伪列（从1开始）
例： 
1）         select  node_value
        from  t_tree
        start with node_value='root_nod'
        connect by parent_node=prior node_value                //遍历节点root_nod 的子节点，不包括
                and node_value !='no_use_nod'                //node_value 等于'no_use_nod'节点及其子节点
2）
        select  node_value
        from  t_tree
        where node_value !='no_use_nod'                        //遍历节child_nod的父节点，不包括
        start with node_value='child_nod'                //node_value 等于'no_use_nod'节点
        connect by node_value=prior parent_node       
-----------------------------------------------------------------------------------------------
定义函数：
    create or replace function <function_name> (<var_name> in/out/inout <var_type>)
    return <data_type> {as/is}
      <data_name>  <data_type>;
        ...
        begin
        ....
        exception
        end;
                             
---------------------------------------------------------------------------------------
包：
   包是集中到单独一单元的一组过程、函数、变量、常量、数据指针、例外列表和sql语句。
   dbms_output 包括三个调用函数 put , put_link ,new_link  在使用dbms_output 之前，必须
                                首先发出命令 set serveroutput on
   建立包的定义：
   create [or replace] package  <[user].package_name>
        {is|as}
        <some package specification;>
   end <[user].package_name>
   建立包的内容：
   create [or replace] package body  <[user].package_name>
        {is|as}
        <some package body specification;>
    end <[user].package_name>
    或
   create [or replace] package body  <[user].package_body_name>
        {is|as}
        <some package body specification;>
    begin
        sql_command;   //初始化包的命令
    end <[user].package_body_name>

    调用方式：<[user].package_name>.<[user].package_body_name>
---------------------------------------------------------------------------------------
数据拷贝：
        copy from
        [<remote usename>/<remove password>@<connect_string>]
        {append | create | insert | replace }
        table name
        using subquery;
   例：
        set copycommit 1
        set arraysize 1000    //每一千条记录提交一次
       
        copy fromt_branch@dhzx-     //???????It is wrong
        create t_new_branch -
        using -
        select * from t_new_branch           //每行中的  - 表示与下一行连接
-------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
触发器：
        触发器的执行对用户来说是透明的。
        建立触发器需具有表的alter 或 alter any table 的权限
   合法的触发器类型有14种
        BEFORE INSERT row        BEFORE INSERT statement
        AFTER INSERT  row        AFTER INSERT  statement
        BEFORE UPDATE row        BEFORE UPDATE statement
        AFTER  UPDATE row        AFTER  UPDATE statement       
        BEFORE DELETE row        BEFORE DELETE statement
        AFTER  DELETE row        AFTER  DELETE statement
        INSTEAD OF    row        INSTEAD   OF  statement
    创建触发器的语法：
---------------------------------------------------------------------------------------------
  显示光标：declare cursor <cursor_name> is
                <sql_command>；
            open <cursor_name>;
            fetch <cursor_name> into var1,var2...;       
            close <cursor_name>;
  oracle的隐式游标是sql,所有游标都有四个属性：%NOT FOUND,%FOUND,%ROWCOUNT,%ISOPEN
  这些属性可以存取有关的insert,update,delete,insert into的语句执行信息。
   例：游标名%ROWCOUNT,或sql%ISOPEN
----------------------------------------------------------------------------------------
  伪列
  rowid, rownum, user
---------------------------------------------------------------------------------------
pb 端调用：declare <procedure_logic_name> for <procedure_name>
                 (:var_1,:var_2,....);
          execute <procedure_logic_name>;
          fetch <procedure_logic_name> INTO :var....;
          CLOSE <procedure_logic_name>;
---------------------------------------------------------------------------------------
同义词
  create synonym <synonym_name>
            for  <object_name>
  create public synonym <public_synonym_name>
               for <schema.object_name>  
 
  drop [public] synonym <syonoym_name>
---------------------------------------------------------------------------------------
序列
  create sequence  [schema.]sequence_name [option] ;

  option:
    INCREMENT BY, MINVALUE, NOMINVALUE ,MAXVALUE ,NOMAXVALUE ,
    START WITH ,CYCLE ,NOCYCLE ,CACHE ,NOCACHE ,ORDER ,NOORDER

  access: CURRVAL ,NEXTVAL
  调用方式：[schema.]sequence_name.nextval //取下一个序列号
---------------------------------------------------------------------------------------
创建数据库连接：
create [public] database link <dbl_name>
connect to <user_id> identified by <user_password>
using <link_string>;
删除
drop [public] database link <dbl_name>;
调用方式：
<table_name>@<dbl_name>
或建立远程连接的同义词：例： create synonym <table_name> for <table_name>@<dbl_name>

应避免在使用 connect by,start with 和 prior等关键字查询中使用数据库连接，
使用数据连接进行树状结构的查询，多数回失败。
----------------------------------------------------------------------------------------

创建用户
  create user <user_name> {identified by <password>|  externally};
  alter user <user_name> identified by <password>;
---------------------------------------------------------------------------------------
授权与回收
  database:
  grant connect|resouce|dba to <user_name> identified by <password>
                       [with grant/admin option];
  revoke connect|resouce|dba from <user_name>;

  table:
  grant all|alter|delete|index|insert|select on [user_name.]
       <table_name> to <user_name1>[user_name2,..] [with grant option]
  revoke all|alter|delete|index|insert|select on
       <table_name> from <user_name>
  
  grant {system privilege|role} [,{system privilege|role},...]
         [ on <object>]
        to {user|role} [,{user|role},...] 
         [with admin option]

  revoke {system privelege|role} [,{system pribilege|role},...]
        from {user|role} [,{user|role},...]

  privilege include: insert,update,delete,alter,references,all,
                     index,execute,select,read 
  publec means:all user   such as:
                             grant select on table1 to public;
       
  create public synonym <synonym_name> for <object>;                                                                 
---------------------------------------------------------------------------------------
角色：
  create role <role_name>
   [not identified| identified [by <password> | externally]]; 

  alter role <role_name> identified [by <password> | externally]|not identified];
   
  alter user <user_name> default role {[role1,role2,...]
               [all|all except role1,role2,...] [none]};

  set role <role_name> [identified by <password>];

  set role none;

  drop role <rloe_name>;
---------------------------------------------------------------------------------------
授权制定的列：
  grant <privillege> (colunm_name1,column_name2,...) on <table_name>
        to {user|role} [,{user|role},...] 
---------------------------------------------------------------------------------------
块语句：
  BEGIN
      ....
  END
---------------------------------------------------------------------------------------

语法结构
--for
for <condition> loop
    ...
end loop;

where <condition>
   ...

--loop1
<command>;
loop
   <command>;
   if <condition> then
        exit;
   end if
    ...
end loop;
--loop2
<command>;
loop
   <command>;
   exit when <condition>
    ....
end loop;
--loop3
   <command>;
   while <condition> loop
    ....
   end loop;
--loop
   <command>;
   for <var> in const1..constn loop
    ....
   end loop;

--if
--if
if <condition> then
   ...
else
   ...
end if
---------------------------------------------------------------------------------------
数据类型
char(size)定长字符型，最大长度为2000，缺省为1
varchar(size)旧的oracle版本中可变长字符串型
varchar2(size)可变长字符串，size最大值为4000
number数值型包括40位空间,又作number(*) 或 smallint
number(size,d)定长数值型
date 有效日期为公元前4712年1月1日到公元后4712年12月31日
long, raw,long raw,mlslable,bolb,clob,nclob,bfile
---------------------------------------------------------------------------------------
比较运算符
  = ,!=,^=,<>,>,<,>=,<=, between..and.. ,in,
  like,is null,is not null,exist,not exist
---------------------------------------------------------------------------------------
通配符
% ,_
---------------------------------------------------------------------------------------
函数
  decode(<condition>,<state1>,<value1>,<state2>,<value2>,...,<value_else>)
  nvl(x1,x2)       //if x1 is not null then return x1 ,else return x2;
  avg,count,max,min,sum,num
  to_date(char_value,chg_type)     cc
                                   yyyy,yyy,yy,y
                                   q //季度
                                   mm,month,mon
                                   ww,w//周
                                   ddd,dd,d,day,dy
                                    am,pm
                                   hh12,hh24
                                   mi
                                   ss
  to_char(date_value,chg_type)
  sign(<number_value>)             //取符号 ，返回1，0，-1
  abs(n); ceil(n);//上取整  floor(n);// 下取整 mod(m,n); sign(n);
  round(n[,m]);//四舍五入  sqrt(n)
  ascII($); chr(n);//相应ascII码的字符 inicap($);//每个词头大写
  length($); lower($); upper($); instr($1,$2);//$2在$1中出现的第一个位置
  lpad($1,n[,$2]); //$1从左边用$2补至长度n,$2 默认为空格
  ltrim($1,n[,$2]);//同lpad 相反    $1||$2  //字符串连接 
  user// 返回当前的用户名
  add_months(date,n);  last_date(day); monts_between(date1,date2);
  next_day(date,char);trunc(date);//截区date中的时间

  is null,is not null //例 where name is null
  decode(expression,value1,result1,value2,result2,....,default_result)   
  truncate table table_name;//不可恢复的删除表中所用数据
运算符：
  +,-,*,/,**,<>,!=,^=,<,>,=

  ||,:=,--
外连接
(+)
集合运算
union (交), intersect(并),minus(差)
---------------------------------------------------------------------------------------
提交
  commit;
  set autocommit on;
 
  alter;audit;connect;create;disconnect;drop;exit;grant;noaudit;quit;
  revake;rename;  //以上命令在执行完后,自动执行commit 命令
回退
  rollback;
  SAVEPOINT point1;
  ROLLBACK TO SAVEPOINT point1;
---------------------------------------------------------------------------------------
使用spool的例子：
set feedback off;
set heading off;
spool yyy.txt
   select 'delete from'||table_name||'@dbl_hcss'||';'
       from  user_tables where table_name like 'T_%';                
    select 'insert into'||table_name||'@dbl_hcss'||'select * from '||table_name||';'
       from  user_tables where table_name like 'T_%';
spool off;
--spool all_table_records.txt
--start yyy.txt
--spool off
---------------------------------------------------------------------------------------
公用视图：
user_source   用户所有对象的源代码
---------------------------------------------------------------------------------------
sqlplus 中接受变量
ACCEPT p_val PROMPT'Please input a branchname:';
用&p_val来引用该变量

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/kinglht/archive/2007/09/03/1769926.aspx