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>
      &nbsp; [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 .....;
&nbsp;                      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開始）
例：&nbsp; 
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 from t_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
    &nbsp;   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] ;

&nbsp; 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來引用該變量