Oracle詳細使用-有解無憂

學習Oracle的使用（和mysql使用規則一樣用“略”代表）
- select陳述句的使用：略，
- create創建表的使用：略，但是可以在表名前可以添加模式名稱，可以復制表作為一個新表
```
  create table table_name as (
  select * from other_name
);
```
- alter修改表結構的使用：
  - 增加一列：略，
  - 修改一個或者多列的屬性或者約束：略，另外Oracle提供【不】可見屬性[in]visible
  - 洗掉一列或者多列（物理洗掉）
```
   alter table table_name drop (col1);
   alter table table_name drop(col1, col2) 
```
  - 修改列名名稱：
```
  alter table table_name rename column old_name to new_name;
```
  - 修改表名名稱：
```
  alter table table_name rename to new_name
```
- 洗掉表資料drop：
  - 可以將表資料洗掉放置回收站中，若在后面添加purge，可以完全洗掉
```
  drop table table_name purge;
```
  - oracle還提供了表級聯約束條件，多個表如果有關聯，則必須先洗掉子表，后洗掉父表，
  - Oracle提供洗掉多個表的功能
```
  begin
    for rec in 
      (select table_name from all_tables
       where table_name like 'test_%')
    loop
      execute immediate 'drop table ' || rec.table_name || 'cascade constrains';
    end loop;
  end;
  /
```
- 洗掉列set unused column:（邏輯洗掉）
  - 作用是將選中的列設定不可用，這樣在資料操作時這列的資料是不可見的
```
  alter table table_name set unused column column_name;
```
  - 執行完上述操作之后，可以以另一種方式物理洗掉列
```
  alter table table_name drop unused columns;
```

學習Oracle的使用
- modify修改列
  - 在修改列的程序中，不能隨意修改型別長度
```
  alter table table_name modify column_name 型別(長度)
```
  - 若文本的長度大于設定的長度，執行時就會報錯，可以使用替代函式裁剪資料（型別是varchar2）：
```
  update tahle_name
  set column_name=replace(column_name, "替代前str", "替代后str")
```
  - 可以結合運算式給列設定值
```
  alter table table_name modify column_name 型別(長度) generated always as (生成值的運算式)
```
- 表名稱重命名rename
```
  rename table_name to new_name
```
```
  rename table table_name to new_name
```
- 資料型別
  - 數值型別NUMBER
    - number(pre[, next]) 第一個引數表示整數的位數，第二個引數表示小數的位數，即可以表示整數和小數，若小數的位數超過自定義的位數，系統會四舍五入，number的表現范圍mysql的int，smallint，number，decimal 另外FLOAT型別是數值型別的子類
  - 字串型別char、nchar、varchar2、nvarchar2其實和mysql的char、varchar的語意是一樣的，只不過帶n表示存盤當前國家的Unicode碼
  - 日期型別DATE
    - 日期型別轉換為字串時間to_char(date型別)
    - 字串時間轉換為日期型別to_date("字串時間", "字串格式")
    - 在插入陳述句涉及日期時，可以DATE指明字串時間，或者使用to_date
- 資料操作
  - 插入資料
    - 單條資料插入：略，
    - 多條資料插入
      - 在有條件時，all代表的意思是如果都滿足條件，都要執行插入操作；first即只在第一次滿足條件時插入資料
      - 無條件：
        
        insert all into table_name(col1, col2) values (val1, val2) into table_name(col1, col2) values (val1, val2) into table_name(col1, col2) values (val1, val2) 子查詢陳述句;
      - 有條件：
        
        insert first when condition1 then into table_name(col1, col2) values (val1, val2) when condition2 then into table_name(col1, col2) values (val1, val2) else into table_name(col1, col2) values (val1, val2) 子查詢陳述句;
  - 更新陳述句update和洗掉陳述句delete：略，在洗掉資料時，若兩表之間存在關聯，必須先洗掉子表中的資料，后洗掉父表的資料；若在創建子表的程序中指定on delete cascade表級約束，在洗掉父表時，會連同與子表關聯的資料都會洗掉
- 資料合并 merge陳述句就是同時執行多個陳述句（操作物件就是目標表），并將得到的資料集合并在目標表中， merge陳述句的語法： python """ merge into target_table using source_table on search_condition when matched then 資料操作1 when not matched then 資料操作2 """ 如果資料操作是更新，盡量不要操作同一行資料如果資料操作是洗掉，則會洗掉on條件和where條件匹配的行記錄
- 資料排序order by：略，另外Oracle提供了nulls last約束，可以將null的行記錄放置在最后
- 資料去重distinct：略，
- 資料集記錄數限制： 12版本以上使用fetch next 數量 rows [only / with ties], 12版本以下使用where rownum <= 數量可以在前面添加偏移量offset 數量，和mysql操作相同
- 通配符%和：略，如果匹配%或者 則使用escape str格式化后面一個字符即可：
```
  select * from table_name where column_name like '%25!%%' escape '!'
```
- group by分組語意和mysql一樣，只不過group by陳述句是在where條件之前
- having陳述句是和group by搭配使用，它們是連在一起的

學習Oracle的使用
- 資料運算子
  - exits的回傳值是True or False，也是存在的意思，如果是針對性的查詢資料建議使用in陳述句
  - any用法其實和JavaScript的some方法的語意一樣
  - all用法其實和JavaScript的every方法的語意一樣
  - union的用法和mysql一樣，兩個資料集的列數和型別一定要相等，列名可以不同，
```
    select name1, add_time
    from table_name1 t1
    union
    select name2, add_time
    from table_name2 t2
```
  - intersect行記錄交集兩個資料集的列數和型別一定要相等，列名可以不同帥選的都是行記錄和列名相同的行保存
  - minus行記錄差集兩個資料集的列數和型別一定要相等，列名可以不同帥選的都是行記錄和列名相同的行保存
- 資料表連接 on條件執行完主表的資料不變，該連接的還是得連接 where條件執行物件是在多表連接完后的資料集
  - inner join內連接，using(column)等同于on后欄位值相等，column必須存在于兩個表中
```
  select * 
  from table_name1 t1
  inner join table_name2 t2 on t1.id = t2.id
  select *
  from table_name1 t1
  inner join table_name2 t2 using(id)
```
  - left join左連接：略
```
  select *
  from table_name1 t1
  left join table_name2 t2 on t1.id = t2.id
  and t1.name = "str"
  select *
  from table_name1 t1
  left join table_name2 t2 on t1.id = t2.id
  where t1.name = "str"
```
  - right join右連接：略
  - cross join笛卡爾連接，這個可以沒有條件
```
  select *
  from table_name1 t1
  cross join table_name2 t2
```
  - 自連接：指的是兩張相同的表某一列或者多列進行內連接
- 資料庫約束
  - 主鍵：
    - 列級約束：略
    - 表級約束：略，但是若指定主鍵約束名，表示方式：constraint primary_key_name primary key (col1, ...)
    - 添加主鍵：
```
  alter table table_name add constraint primary_key_name 
  primary key (col1)
```
    - 洗掉drop/啟用enable/禁用disable主鍵：
```
  alter table table_name drop constraint primary_key_name 
```
```
  alter table table_name drop primary key
```
  - 外鍵：
    - 只能是表級約束：foreign key(column) references main_table(main_column) on delete [cascade/set null]
    - 添加外鍵：
```
  alter table table_name add constraint foreign_key_name 
  foreign key (col1) references table_name(col1)
```
    - 洗掉drop/啟用enable/禁用disable外鍵：
```
  alter table table_name drop constraint foreign_key_name 
```
  - not null：略
  - unique：
    - 列級約束：略
    - 表級約束：略，但是若指定唯一約束名，表示方式：constraint unique_name unique (col1, ...)
    - 添加唯一：
```
  alter table table_name add constraint unique_name 
  unique (col1)
```
    - 洗掉drop/啟用enable/禁用disable唯一：
```
  alter table table_name drop constraint unique_name 
```
  - 檢查約束check 只有符合check后的運算式的要求才能更新或者新增資料
    - 列級約束：column_name data_type check (expression)
    - 表級約束：若指定check約束名，表示方式：constraint check_name check (expression)
    - 添加主鍵：
```
  alter table table_name add constraint check_name 
  check (expression)
```
    - 洗掉drop/啟用enable/禁用disable主鍵：
```
  alter table table_name drop constraint check_name 
  alter table table_name drop primary key
```
- 程序
  - 創建程序 """ create [or replace] procedure procedure_name [(parameter[, parameter])] is declare [declaration_section] begin executable_section [exception exception_section] end
```
[procedure_name]; """

  create or replace procedure insert_user
    (id in user.id%type, 
     name in user.name%type,
     res out number,
     res_msg out varchar2(20))
  is
  begin 
    res:=1;
    res_msg:='插入資料成功';
    insert into user values (id, name);
    exception
      when dup_val_on_index then 
        res:=-2000;
        res_msg:='插入資料重復';
      when others then
        res:=sqlcode;
        res_msg:=sqlerrm;
  end insert_user;
  /
```
  - 呼叫程序
```
  declare 
    res number;
    res_msg varchar2(20);
  begin 
    insert_user(101, '胡先森', res, res_msg);
    dbms_output.put_line('狀態碼' || res || ', 狀態資訊' || res_msg);
  end;
  /
```
  - 洗掉程序
```
  drop procedure insert_user
```
- 游標
  - 游標宣告
    - 無引數游標：該游標的結果集是所有的course_name并存在c1中，其course_name與name_in匹配
```
  cursor c1
    is 
      select course_number
      from course
      where course_name = name_in;
  for data in c1 loop:
    ...
  end loop;
```
    - 帶引數游標：該游標的結果集是所有的course_number，其subject與通過引數傳遞給游標的subject_name相匹配
```
 cursor c2(subject_name in varchar2)
      is
        select course_number
        from course
        where subject = subject_name
```
    - 帶return子句的游標：回傳值是course表的科目是chinese的所有列
```
  cursor c3
    return course%rowtype
  is
    select *
    from course
    where subject = 'chinese'
```
  - 游標打開
```
  open c1;
```
  - 游標提取 fetch cursor_name into variable_list; cursor_name：游標名稱 variable_list：游標所需要的引數
```
  fetch c1 into output_number;
```
  - 游標關閉
```
  close c1;
```
  - 游標屬性
    - %ISOPEN
      - 如果游標處于打開狀態，則回傳TRUE;如果游標處于關閉狀態，則回傳FALSE，
    - %FOUND
      - 如果宣告了游標，但不打開，則回傳INVALID_CURSOR，或者游標已關閉，
      - 如果游標處于打開狀態，則回傳NULL，但未執行提取，
      - 如果執行成功，則回傳TRUE，如果沒有行被回傳，則回傳FALSE，
    - %NOTFOUND
      - 如果宣告了游標，但不打開，則回傳INVALID_CURSOR，或者游標已關閉，
      - 如果游標處于打開狀態，則回傳NULL，但未執行提取，
      - 如果執行了成功的提取，則回傳FALSE，如果沒有行被回傳，則回傳TRUE，
    - %ROWCOUNT
      - 如果宣告了游標，但不打開，則回傳INVALID_CURSOR,或者游標已關閉，
      - 回傳獲取的行數，
      - 除非遍歷整個游標，否則ROWCOUNT屬性不會給出真正的行數，換句話說，不應該依賴這個屬性來告訴游標在打開后有多少行，
```
    create or replace function select_all
      (name_in in varchar2)
      return number
    is 
      output_number number;
      cursor c1
      is 
        select course_number
        from course
        where course_name = name_in;
    begin 
        open c1;
        fetch c1 into output_number;
        if c1%notfound then 
          output_number := 9999;
        end if;
        close c1;
      return output_number;
    end;
```

學習Oracle的使用

mysql觸發器語法： create trigger trigger_name after/before insert/update/delete on table_name for each row begin sql陳述句; end; 注：這里的new指的是order_table新增一行資料的物件,一般是after之后的操作物件；old一般指的是before之前的操作物件
```
  create or replace trigger insert_trigger
  before update on goods_table
  for each row
  declare 
    num number;
  begin 
    insert into order_table values (:gid, num);
  end;
  /
```
oracle觸發器 instead of 只能作用于視圖中的行級觸發器上語法： create [or replace] trigger trigger_name after/before/instead of [insert [or update [or delete]]] on table_name [for each row] 行級觸發器 [declare 變數宣告] begin [可以夾帶條件判斷，如下] [if inserting then] 資料操作陳述句 [elsif updating then] 資料操作陳述句 [elsif deleting then] 資料操作陳述句 [end if;] exception 發生例外時執行的陳述句 end;
```
  create or replace trigger insert_trigger
  before update on goods_table
  for each row
  declare 
    num number;
  begin 
    insert into order_table values (:gid, num);
  end;
  /
```
主鍵自增觸發器
```
create or replace trigger auto_increment
  before insert on table_name  
  for each row
declare
  -- local variables here
begin
  select seq_id.nextval into :new table_id from dual;
end auto_increment;
insert into table_name(col1, col2, col2);
```
表級觸發器：和上述語法及操作一樣，只不過沒有for each row，而且是不能使用:old和:new

create or replace trigger insert_trigger
before update on goods_table

begin 
insert into order_table (id, num)
select gid, num from goods_table;
end;
/

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/7316.html

標籤：Oracle

上一篇：Oracle資料庫scott用戶無法匯入資料的解決方法

下一篇：匯出大資料方法。批量導BOM