跳轉到內容

Oracle 資料庫/SQL 速查表

Add links
來自華夏公益教科書

此“速查表”涵蓋了 Oracle DBA 執行基本查詢和執行基本任務所需的大部分基本功能。它還包含 PL/SQL 程式設計師經常用來編寫儲存過程的資訊。該資源可作為 Oracle 新手的入門指南，或作為有經驗的 Oracle 使用者的參考。

網路上存在大量有關 Oracle 的資訊。我們開發了此資源，以便程式設計師和 DBA 更容易在一個地方找到大多數基礎知識。超出“速查表”範圍的主題通常會提供指向進一步研究的連結。

其他 Oracle 參考資料

SELECT

[edit | edit source]

SELECT 語句用於檢索從一個或多個表、物件表、檢視、物件檢視或物化檢視中選擇的行。

   SELECT *
   FROM beverages
   WHERE field1 = 'Kona'
   AND field2 = 'coffee'
   AND field3 = 122;

SELECT INTO

[edit | edit source]

Select into 將name、address 和phone number 的值從employee 表中取出，並將其放入v_employee_name、v_employee_address 和v_employee_phone_number 變數中。

這僅在查詢匹配單個專案時有效。如果查詢未返回任何行，則會引發NO_DATA_FOUND 內建異常。如果查詢返回多行，則 Oracle 會引發異常TOO_MANY_ROWS。

 SELECT name,address,phone_number
 INTO v_employee_name,v_employee_address,v_employee_phone_number
 FROM employee
 WHERE employee_id = 6;

INSERT

[edit | edit source]
 一位華夏公益教科書使用者建議將此書籍或章節與Oracle 資料庫/表 合併。
請在討論頁面 上討論是否應該進行此合併。

INSERT 語句用於向資料庫表中新增一行或多行新資料。

使用 VALUES 關鍵字插入 

 INSERT INTO table_name VALUES ('Value1', 'Value2', ... );
 INSERT INTO table_name( Column1, Column2, ... ) VALUES ( 'Value1', 'Value2', ... );

使用 SELECT 語句插入 

 INSERT INTO table_name( SELECT Value1, Value2, ... from table_name );
 INSERT INTO table_name( Column1, Column2, ... ) ( SELECT Value1, Value2, ... from table_name );

DELETE

[edit | edit source]

DELETE 語句用於刪除表中的行。

刪除與條件匹配的行 

 DELETE FROM table_name WHERE some_column=some_value
 DELETE FROM customer WHERE sold = 0;

UPDATE

[edit | edit source]

UPDATE 語句用於更新表中的行。

更新該表的整列 

 UPDATE customer SET state='CA';

更新表的特定記錄，例如 

 UPDATE customer SET name='Joe' WHERE customer_id=10;

當 paid 列大於零時，將 invoice 列更新為已支付。 

 UPDATE movies SET invoice='paid' WHERE paid > 0;

序列

[edit | edit source]

序列是多個使用者可以用來生成唯一整數的資料庫物件。序列生成器生成順序編號，這可以幫助自動生成唯一的主鍵，並在多行或多表之間協調鍵。

建立序列

[edit | edit source]

序列的語法是 

 CREATE SEQUENCE sequence_name
     MINVALUE value
     MAXVALUE value
     START WITH value
     INCREMENT BY value
     CACHE value;

例如 

 CREATE SEQUENCE supplier_seq
     MINVALUE 1
     MAXVALUE 999999999999999999999999999
     START WITH 1
     INCREMENT BY 1
     CACHE 20;

修改序列

[edit | edit source]

按一定量遞增序列 

 ALTER SEQUENCE <sequence_name> INCREMENT BY <integer>;
 ALTER SEQUENCE seq_inc_by_ten  INCREMENT BY 10;

更改序列的最大值 

 ALTER SEQUENCE <sequence_name> MAXVALUE <integer>;
 ALTER SEQUENCE seq_maxval  MAXVALUE  10;

設定序列迴圈或不迴圈 

 ALTER SEQUENCE <sequence_name> <CYCLE | NOCYCLE>;
 ALTER SEQUENCE seq_cycle NOCYCLE;

配置序列以快取值 

 ALTER SEQUENCE <sequence_name> CACHE <integer> | NOCACHE;
 ALTER SEQUENCE seq_cache NOCACHE;

設定是否按順序返回值 

 ALTER SEQUENCE <sequence_name> <ORDER | NOORDER>;
 ALTER SEQUENCE seq_order NOORDER;
 ALTER SEQUENCE seq_order;

從字串生成查詢

[edit | edit source]

有時需要從字串建立查詢。也就是說，如果程式設計師想要在執行時（動態生成 Oracle 查詢）建立查詢，基於特定的一組情況等等。

應注意不要將使用者提供的資料直接插入到動態查詢字串中，而沒有首先嚴格審查資料中的 SQL 跳脫字元；否則，您將面臨資料注入攻擊的重大風險。

這是一個非常簡單的動態查詢示例。當然，還有很多不同的方法可以做到這一點；這只是一個功能示例。

 PROCEDURE oracle_runtime_query_pcd IS
     TYPE ref_cursor IS REF CURSOR;
     l_cursor        ref_cursor;

     v_query         varchar2(5000);
     v_name          varchar2(64);
 BEGIN
     v_query := 'SELECT name FROM employee WHERE employee_id=5';
     OPEN l_cursor FOR v_query;
     LOOP
        FETCH l_cursor INTO v_name;
        EXIT WHEN l_cursor%NOTFOUND;
     END LOOP;
     CLOSE l_cursor;
 END;

字串操作

[edit | edit source]

長度

[edit | edit source]

Length 返回一個整數，表示給定字串的長度。它可以稱為：length b、length c、length 2 和 length 4。

length( string1 );

SELECT length('hello world') FROM dual;
this returns 11, since the argument is made up of 11 characters including the space

SELECT lengthb('hello world') FROM dual;
SELECT lengthc('hello world') FROM dual;
SELECT length2('hello world') FROM dual;
SELECT length4('hello world') FROM dual;
these also return 11, since the functions called are equivalent

Instr

[edit | edit source]

Instr (in string) 返回一個整數，指定子字串在字串中的位置。程式設計師可以指定他們要檢測的字串的哪個出現，以及起始位置。不成功的搜尋返回 0。

instr( string1, string2, [ start_position ], [ nth_appearance ] )

instr( 'oracle pl/sql cheatsheet', '/');
this returns 10, since the first occurrence of "/" is the tenth character

instr( 'oracle pl/sql cheatsheet', 'e', 1, 2);
this returns 17, since the second occurrence of "e" is the seventeenth character

instr( 'oracle pl/sql cheatsheet', '/', 12, 1);
this returns 0, since the first occurrence of "/" is before the starting point, which is the 12th character

替換

[edit | edit source]

Replace 在字串中查詢，將一個字串替換為另一個字串。如果沒有指定其他字串，則會刪除替換字串引數中指定的字串。

replace( string1, string_to_replace, [ replacement_string ] );
replace('i am here','am','am not');
this returns "i am not here"

子字串

[edit | edit source]

Substr (substring) 返回給定字串的一部分。"start_position" 基於 1，而不是基於 0。如果 "start_position" 為負數，則 substr 從字串末尾開始計數。如果沒有給出 "length"，則 substr 預設為字串的剩餘長度。

substr( string, start_position [, length])

SELECT substr( 'oracle pl/sql cheatsheet', 8, 6) FROM dual;
返回 "pl/sql" 因為 "pl/sql" 中的 "p" 在字串中處於第 8 個位置（從字串中的 "o" 開始計數為 1）
SELECT substr( 'oracle pl/sql cheatsheet', 15) FROM dual;
返回 "cheatsheet" 因為 "c" 在字串中處於第 15 個位置，而 "t" 是字串中的最後一個字元。
SELECT substr('oracle pl/sql cheatsheet', -10, 5) FROM dual;
返回 "cheat" 因為 "c" 是字串中的第 10 個字元，從字串的末尾開始計數，"t" 為位置 1。

修剪

[edit | edit source]

這些函式可用於從字串中過濾不需要的字元。預設情況下，它們會刪除空格，但也可以指定一個字元集進行刪除。

trim ( [ leading | trailing | both ] [ trim-char ] from string-to-be-trimmed );
trim ('   removing spaces at both sides     ');
this returns "removing spaces at both sides"

ltrim ( string-to-be-trimmed [, trimming-char-set ] );
ltrim ('   removing spaces at the left side     ');
this returns "removing spaces at the left side     "

rtrim ( string-to-be-trimmed [, trimming-char-set ] );
rtrim ('   removing spaces at the right side     ');
this returns "   removing spaces at the right side"

DDL SQL

[edit | edit source]

表格

[edit | edit source]

建立表格

[edit | edit source]

建立表格的語法如下 

CREATE TABLE [table name]
      ( [column name] [datatype], ... );

例如 

 CREATE TABLE employee
       (id int, name varchar(20));

新增列

[edit | edit source]

新增列的語法如下 

ALTER TABLE [table name]
      ADD ( [column name] [datatype], ... );

例如 

 ALTER TABLE employee
       ADD (id int);

修改列

[edit | edit source]

修改列的語法如下 

ALTER TABLE [table name]
      MODIFY ( [column name] [new datatype] );

ALTER table 語法和示例

例如 

 ALTER TABLE employee
       MODIFY( sickHours s float );

刪除列

[edit | edit source]

刪除列的語法如下 

ALTER TABLE [table name]
      DROP COLUMN [column name];

例如 

 ALTER TABLE employee
       DROP COLUMN vacationPay;

約束

[edit | edit source]
約束型別和程式碼
[edit | edit source]
型別 程式碼 型別描述 作用於級別
C 表格上的檢查 列
O 檢視上的只讀 物件
P 主鍵 物件
R 引用 AKA 外部索引鍵 列
U 唯一鍵 列
V 檢視上的檢查選項 物件
顯示約束
[edit | edit source]

以下語句顯示系統中的所有約束 

 SELECT
 	table_name,
 	constraint_name,
 	constraint_type
 FROM user_constraints;
選擇引用約束
[edit | edit source]

以下語句顯示所有引用約束（外部索引鍵），包括源表和目標表/列對 

 SELECT
 	c_list.CONSTRAINT_NAME as NAME,
 	c_src.TABLE_NAME as SRC_TABLE,
 	c_src.COLUMN_NAME as SRC_COLUMN,
 	c_dest.TABLE_NAME as DEST_TABLE,
 	c_dest.COLUMN_NAME as DEST_COLUMN
 FROM ALL_CONSTRAINTS c_list,
      ALL_CONS_COLUMNS c_src,
      ALL_CONS_COLUMNS c_dest
 WHERE c_list.CONSTRAINT_NAME = c_src.CONSTRAINT_NAME
   AND c_list.R_CONSTRAINT_NAME = c_dest.CONSTRAINT_NAME
   AND c_list.CONSTRAINT_TYPE = 'R'
在表格上設定約束
[edit | edit source]

使用 CREATE TABLE 語句建立檢查約束的語法如下 

CREATE TABLE table_name
(
    column1 datatype null/not null,
    column2 datatype null/not null,
    ...
    CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE]
);

例如 

 CREATE TABLE suppliers
 (
     supplier_id  numeric(4),
     supplier_name  varchar2(50),
     CONSTRAINT check_supplier_id
     CHECK (supplier_id BETWEEN 100 and 9999)
 );
表格上的唯一索引
[edit | edit source]

使用 CREATE TABLE 語句建立唯一約束的語法如下 

CREATE TABLE table_name
(
    column1 datatype null/not null,
    column2 datatype null/not null,
    ...
    CONSTRAINT constraint_name UNIQUE (column1, column2, column_n)
);

例如 

 CREATE TABLE customer
 (
     id   integer not null,
     name varchar2(20),
     CONSTRAINT customer_id_constraint UNIQUE (id)
 );
新增唯一約束
[編輯 | 編輯原始碼]

唯一約束的語法是 

ALTER TABLE [table name]
      ADD CONSTRAINT [constraint name] UNIQUE( [column name] ) USING INDEX [index name];

例如 

 ALTER TABLE employee
       ADD CONSTRAINT uniqueEmployeeId UNIQUE(employeeId) USING INDEX ourcompanyIndx_tbs;
新增外部索引鍵約束
[編輯 | 編輯原始碼]

外部索引鍵約束的語法是 

ALTER TABLE [table name]
      ADD CONSTRAINT [constraint name] FOREIGN KEY (column,...) REFERENCES table [(column,...)] [ON DELETE {CASCADE | SET NULL}]

例如 

 ALTER TABLE employee
       ADD CONSTRAINT fk_departament FOREIGN KEY (departmentId) REFERENCES departments(Id);
刪除約束
[編輯 | 編輯原始碼]

刪除 (移除) 約束的語法是：[1]

ALTER TABLE [table name]
      DROP CONSTRAINT [constraint name];

例如 

 ALTER TABLE employee
       DROP CONSTRAINT uniqueEmployeeId;

索引

[編輯 | 編輯原始碼]

索引是一種以更高效率檢索記錄的方法。索引為索引列中出現的每個值建立一個條目。預設情況下，Oracle 建立B 樹 索引。

建立索引

[編輯 | 編輯原始碼]

建立索引的語法是 

CREATE [UNIQUE] INDEX index_name
    ON table_name (column1, column2, . column_n)
    [ COMPUTE STATISTICS ];

UNIQUE 指示索引列中的值組合必須唯一。

COMPUTE STATISTICS 指示 Oracle 在建立索引期間收集統計資訊。然後，最佳化器將使用這些統計資訊來選擇執行語句時的最佳執行計劃。

例如 

 CREATE INDEX customer_idx
     ON customer (customer_name);

在此示例中，已在名為 customer_idx 的 customer 表上建立了一個索引。它僅包含 customer_name 欄位。

以下程式碼建立了包含多個欄位的索引 

 CREATE INDEX customer_idx
     ON supplier (customer_name, country);

以下程式碼在建立索引時收集統計資訊 

 CREATE INDEX customer_idx
     ON supplier (customer_name, country)
     COMPUTE STATISTICS;

建立基於函式的索引

[編輯 | 編輯原始碼]

在 Oracle 中，您不僅可以為列建立索引。您還可以建立基於函式的索引。

建立基於函式的索引的語法是 

CREATE [UNIQUE] INDEX index_name
    ON table_name (function1, function2, . function_n)
    [ COMPUTE STATISTICS ];

例如 

 CREATE INDEX customer_idx
     ON customer (UPPER(customer_name));

已建立了一個基於 customer_name 欄位大寫評估的索引。

為了確保 Oracle 最佳化器在執行您的 SQL 語句時使用此索引，請確保 UPPER(customer_name) 不評估為 NULL 值。為此，請將 UPPER(customer_name) IS NOT NULL 新增到您的 WHERE 子句，如下所示

 SELECT customer_id, customer_name, UPPER(customer_name)
 FROM customer
 WHERE UPPER(customer_name) IS NOT NULL
 ORDER BY UPPER(customer_name);

重新命名索引

[編輯 | 編輯原始碼]

重新命名索引的語法是 

ALTER INDEX index_name
    RENAME TO new_index_name;

例如 

 ALTER INDEX customer_id
     RENAME TO new_customer_id;

在此示例中，customer_id 重新命名為 new_customer_id。

收集索引上的統計資訊

[編輯 | 編輯原始碼]

如果需要在首次建立索引後收集統計資訊，或者想要更新統計資訊，始終可以使用 ALTER INDEX 命令來收集統計資訊。您收集統計資訊是為了讓 Oracle 能有效地使用索引。這將重新計算表大小、行數、塊數、段數並更新字典表，以便 Oracle 在選擇執行計劃時能夠有效地使用資料。

在索引上收集統計資訊的語法是 

ALTER INDEX index_name
    REBUILD COMPUTE STATISTICS;

例如 

 ALTER INDEX customer_idx
     REBUILD COMPUTE STATISTICS;

在此示例中，為名為 customer_idx 的索引收集了統計資訊。

刪除索引

[編輯 | 編輯原始碼]

刪除索引的語法是 

   DROP INDEX index_name;

例如 

    DROP INDEX customer_idx;

在此示例中，customer_idx 被刪除了。

[編輯 | 編輯原始碼]

使用者管理

[編輯 | 編輯原始碼]

建立使用者

[編輯 | 編輯原始碼]

建立使用者的語法是 

   CREATE USER username IDENTIFIED BY password;

例如 

   CREATE USER brian IDENTIFIED BY brianpass;

授予許可權

[編輯 | 編輯原始碼]

授予許可權的語法是 

   GRANT privilege TO user;

例如 

   GRANT dba TO brian;

更改密碼

[編輯 | 編輯原始碼]

更改使用者密碼的語法是 

   ALTER USER username IDENTIFIED BY password;

例如 

   ALTER USER brian IDENTIFIED BY brianpassword;

匯入和匯出

[編輯 | 編輯原始碼]

有兩種方法可以備份和還原資料庫表和資料。'exp' 和 'imp' 工具是更簡單的工具，適用於較小的資料庫。如果資料庫結構變得更加複雜或非常大（例如大於 50 GB），則使用 RMAN 工具更合適。

使用 IMP 匯入轉儲檔案

[編輯 | 編輯原始碼]

此命令用於從 'exp' 工具建立的 *.dmp 檔案中匯入 Oracle 表和表資料。請記住，這是一個從命令列透過 $ORACLE_HOME/bin 執行的命令，而不是在 SQL*Plus 中執行的命令。

匯入轉儲檔案的語法是 

   imp KEYWORD=value

您可以為關鍵字使用許多引數。

要檢視所有關鍵字，請執行以下操作 

   imp HELP=yes

示例 

   imp brian/brianpassword FILE=mydump.dmp FULL=yes

PL/SQL

[編輯 | 編輯原始碼]

運算子

[編輯 | 編輯原始碼]

算術運算子

[編輯 | 編輯原始碼]
  • 加法：+
  • 減法：-
  • 乘法：*
  • 除法：/
  • 冪 (僅限 PL/SQL)：**
示例
[編輯 | 編輯原始碼]

為客戶 ID 為 5 的所有員工加薪 5%

UPDATE employee SET salary = salary * 1.05
                  WHERE customer_id = 5;

確定所有員工的稅後工資

SELECT wage – tax FROM employee;

比較運算子

[編輯 | 編輯原始碼]
  • 大於：>
  • 大於或等於：>=
  • 小於：<
  • 小於或等於：<=
  • 等效：=
  • 不等式： != ^= <> ¬= (取決於平臺)
示例
[編輯 | 編輯原始碼]
SELECT name, salary, email FROM employees WHERE salary > 40000;

SELECT name FROM customers WHERE customer_id < 6;

字串運算子

[編輯 | 編輯原始碼]
  • 連線：||

create or replace procedure addtest( a in varchar2(100), b in varchar2(100), c out varchar2(200) ) IS begin C:=concat(a,'-',b);

日期運算子

[編輯 | 編輯原始碼]
  • 加法：+
  • 減法：-

型別

[編輯 | 編輯原始碼]

基本 PL/SQL 型別

[編輯 | 編輯原始碼]

標量型別（在包 STANDARD 中定義）：NUMBER、CHAR、VARCHAR2、BOOLEAN、BINARY_INTEGER、LONG\LONG RAW、DATE、TIMESTAMP 及其包含間隔的家族）

複合型別（使用者定義型別）：TABLE、RECORD、NESTED TABLE 和 VARRAY

LOB 資料型別 : 用於儲存大量非結構化資料

%TYPE – 錨定型別變數宣告

[編輯 | 編輯原始碼]

錨定型別宣告的語法為 

<var_name> <obj>%type [not null][:= <init-val>];

例如 

name Books.title%type;   /*  name is defined as the same type as column 'title' of table  Books */

commission number(5,2) := 12.5;

x commission%type;   /*  x is defined as the same type as variable 'commission' */

注意

  1. 錨定變數允許在 <obj> 型別發生變化時，自動同步錨定變數的型別與 <obj> 的型別。
  2. 錨定型別在編譯時進行評估，因此請重新編譯程式以反映錨定變數中 <obj> 型別發生的變化。

集合

[編輯 | 編輯原始碼]

集合是相同型別元素的有序組。它是一個通用概念，涵蓋列表、陣列和其他熟悉的型別。每個元素都有一個唯一的下標，確定其在集合中的位置。

--Define a PL/SQL record type representing a book:
TYPE book_rec IS RECORD
   (title                   book.title%TYPE,
    author                  book.author_last_name%TYPE,
    year_published          book.published_date%TYPE);

--define a PL/SQL table containing entries of type book_rec:
Type book_rec_tab IS TABLE OF book_rec
     INDEX BY BINARY_INTEGER;

my_book_rec  book_rec%TYPE;
my_book_rec_tab book_rec_tab%TYPE;
...
my_book_rec := my_book_rec_tab(5);
find_authors_books(my_book_rec.author);
...

使用集合有很多很好的理由。

  • 顯著提高執行速度，這得益於透明的效能提升，包括新的最佳化編譯器、更好地整合的本地編譯以及幫助數字密集型應用程式的新資料型別。
  • FORALL 語句變得更加靈活和有用。例如，FORALL 現在支援非連續索引。
  • 正則表示式以三種新函式（REGEXP_INSTR、REGEXP_REPLACE 和 REGEXP_SUBSTR）和用於比較的 REGEXP_LIKE 運算子的形式在 PL/SQL 中可用[2]。
  • 集合得到了改進，包括集合相等性比較和對巢狀表的集合操作的支援。

參考資料

[編輯 | 編輯原始碼]
  1. ↑ http://www.psoug.org/reference/constraints.html
  2. ↑ 有關此問題的更多資訊，請參閱 Jonathan Gennick 的“First Expressions”

儲存邏輯

[編輯 | 編輯原始碼]

函式

[編輯 | 編輯原始碼]

函式必須向呼叫者返回值。

函式的語法為 

CREATE [OR REPLACE] FUNCTION function_name [ (parameter [,parameter]) ]
RETURN [return_datatype]
IS
    [declaration_section]
BEGIN
    executable_section
    return [return_value]

    [EXCEPTION
        exception_section]
END [function_name];

例如 

CREATE OR REPLACE  FUNCTION to_date_check_null(dateString IN VARCHAR2, dateFormat IN VARCHAR2)
RETURN DATE IS
BEGIN
    IF dateString IS NULL THEN
        return NULL;
    ELSE
        return to_date(dateString, dateFormat);
    END IF;
END;

過程

[編輯 | 編輯原始碼]

過程與函式的區別在於，它不能向呼叫者返回值。

過程的語法為 

CREATE [OR REPLACE] PROCEDURE procedure_name [ (parameter [,parameter]) ]
IS
    [declaration_section]
BEGIN
    executable_section
    [EXCEPTION
        exception_section]
END [procedure_name];

建立過程或函式時，可以定義引數。可以宣告三種類型的引數

  1. IN – 過程或函式可以引用該引數。引數的值不能被過程或函式覆蓋。
  2. OUT – 過程或函式不能引用該引數，但引數的值可以被過程或函式覆蓋。
  3. IN OUT – 過程或函式可以引用該引數，並且引數的值可以被過程或函式覆蓋。

您還可以宣告一個 DEFAULT 值；

CREATE [OR REPLACE] PROCEDURE procedure_name [ (parameter [IN|OUT|IN OUT] [DEFAULT value] [,parameter]) ]

以下是一個簡單的過程示例 

   /* purpose: shows the students in the course specified by courseId */

   CREATE OR REPLACE Procedure GetNumberOfStudents
      ( courseId IN number, numberOfStudents OUT number )
   IS

       /* although there are better ways to compute the number of students,
          this is a good opportunity to show a cursor in action            */

       cursor student_cur is
       select studentId, studentName
           from course
           where course.courseId = courseId;
       student_rec    student_cur%ROWTYPE;

   BEGIN
       OPEN student_cur;
       LOOP
           FETCH student_cur INTO student_rec;
           EXIT WHEN student_cur%NOTFOUND;
           numberOfStudents := numberOfStudents + 1;
       END LOOP;
       CLOSE student_cur;

   EXCEPTION
   WHEN OTHERS THEN
         raise_application_error(-20001,'An error was encountered – '||SQLCODE||' -ERROR- '||SQLERRM);
   END GetNumberOfStudents;

匿名塊

[編輯 | 編輯原始碼]
DECLARE
 x NUMBER(4) := 0;
BEGIN
  x := 1000;
  BEGIN
    x := x + 100;
  EXCEPTION
    WHEN OTHERS THEN
      x := x + 2;
  END;
  x := x + 10;
  dbms_output.put_line(x);
EXCEPTION
  WHEN OTHERS THEN
    x := x + 3;
END;

向儲存邏輯傳遞引數

[編輯 | 編輯原始碼]

向儲存過程傳遞引數有三種基本語法：位置表示法、命名錶示法和混合表示法。

以下示例針對每種基本引數傳遞語法呼叫此過程

CREATE OR REPLACE PROCEDURE create_customer( p_name IN varchar2,
                                             p_id IN number,
                                             p_address IN varchar2,
                                             p_phone IN varchar2 ) IS
BEGIN
    INSERT INTO customer ( name, id, address, phone )
    VALUES ( p_name, p_id, p_address, p_phone );
END create_customer;
位置表示法
[編輯 | 編輯原始碼]

以與在過程中宣告的順序相同的順序指定相同引數。這種表示法很簡潔，但如果以錯誤的順序指定引數（尤其是文字），則很難檢測到錯誤。如果過程的引數列表發生更改，則必須更改程式碼。

create_customer('James Whitfield', 33, '301 Anystreet', '251-222-3154');
命名錶示法
[編輯 | 編輯原始碼]

為每個引數指定其名稱及其值。箭頭 (=>) 充當關聯運算子。引數的順序無關緊要。這種表示法更詳細，但使您的程式碼更易於閱讀和維護。如果過程的引數列表發生更改，例如引數重新排序或添加了新的可選引數，則有時可以避免更改程式碼。對於呼叫其他人的 API 或為其他人定義 API 的任何程式碼，命名錶示法是一種很好的實踐。

create_customer(p_address => '301 Anystreet', p_id => 33, p_name => 'James Whitfield', p_phone => '251-222-3154');
混合表示法
[編輯 | 編輯原始碼]

使用位置表示法指定第一個引數，然後切換到對最後一個引數使用命名錶示法。可以使用這種表示法呼叫具有某些必需引數，然後是某些可選引數的過程。

create_customer(v_name, v_id, p_address=> '301 Anystreet', p_phone => '251-222-3154');

表函式

[編輯 | 編輯原始碼]
CREATE TYPE object_row_type as OBJECT (
  object_type VARCHAR(18),
  object_name VARCHAR(30)
);

CREATE TYPE object_table_type as TABLE OF object_row_type;

CREATE OR REPLACE FUNCTION get_all_objects
  RETURN object_table_type PIPELINED AS
BEGIN
    FOR cur IN (SELECT * FROM all_objects)
    LOOP
      PIPE ROW(object_row_type(cur.object_type, cur.object_name));
    END LOOP;
    RETURN;
END;

SELECT * FROM TABLE(get_all_objects);

流程控制

[編輯 | 編輯原始碼]
條件運算子
[編輯 | 編輯原始碼]
  • and: AND
  • or: OR
  • not: NOT
示例
[編輯 | 編輯原始碼]

IF salary > 40000 AND salary <= 70000 THEN() ELSE IF salary>70000 AND salary<=100000 THEN() ELSE()

If/then/else

[編輯 | 編輯原始碼]
IF [condition] THEN
    [statements]
ELSEIF [condition] THEN
    [statements}
ELSEIF [condition] THEN
    [statements}
ELSEIF [condition] THEN
    [statements}
ELSEIF [condition] THEN
    [statements}
ELSEIF [condition] THEN
    [statements}
ELSEIF [condition] THEN
    [statements}
ELSEIF [condition] THEN
    [statements}
ELSE
    [statements}
END IF;

陣列

[編輯 | 編輯原始碼]
關聯陣列
[編輯 | 編輯原始碼]
  • 強型別陣列，作為記憶體表很有用
示例
[編輯 | 編輯原始碼]
  • 非常簡單的示例，索引是訪問陣列的鍵，因此除非您打算使用陣列中每一行的資料，否則無需遍歷整個表。
  • 索引也可以是數值。
DECLARE
    -- Associative array indexed by string:

    -- Associative array type
    TYPE population IS TABLE OF NUMBER
        INDEX BY VARCHAR2(64);
    -- Associative array variable
    city_population  population;
    i                VARCHAR2(64);
BEGIN
    -- Add new elements to associative array:
    city_population('Smallville')  := 2000;
    city_population('Midland')     := 750000;
    city_population('Megalopolis') := 1000000;

    -- Change value associated with key 'Smallville':
    city_population('Smallville') := 2001;

    -- Print associative array by looping through it:
    i := city_population.FIRST;

    WHILE i IS NOT NULL LOOP
        DBMS_OUTPUT.PUT_LINE
            ('Population of ' || i || ' is ' || TO_CHAR(city_population(i)));
        i := city_population.NEXT(i);
    END LOOP;

    -- Print selected value from a associative array:
    DBMS_OUTPUT.PUT_LINE('Selected value');
    DBMS_OUTPUT.PUT_LINE('Population of');
END;
/

-- Printed results:
Population of Megalopolis is 1000000
Population of Midland is 750000
Population of Smallville is 2001
  • 更復雜的示例，使用記錄
DECLARE
    -- Record type
    TYPE apollo_rec IS RECORD
    (
        commander   VARCHAR2(100),
        launch      DATE
    );
    -- Associative array type
    TYPE apollo_type_arr IS TABLE OF apollo_rec INDEX BY VARCHAR2(100);
    -- Associative array variable
    apollo_arr apollo_type_arr;
BEGIN
    apollo_arr('Apollo 11').commander := 'Neil Armstrong';
    apollo_arr('Apollo 11').launch :=   TO_DATE('July 16, 1969','Month dd, yyyy');
    apollo_arr('Apollo 12').commander := 'Pete Conrad';
    apollo_arr('Apollo 12').launch :=   TO_DATE('November 14, 1969','Month dd, yyyy');
    apollo_arr('Apollo 13').commander := 'James Lovell';
    apollo_arr('Apollo 13').launch :=   TO_DATE('April 11, 1970','Month dd, yyyy');
    apollo_arr('Apollo 14').commander := 'Alan Shepard';
    apollo_arr('Apollo 14').launch :=   TO_DATE('January 31, 1971','Month dd, yyyy');

    DBMS_OUTPUT.PUT_LINE(apollo_arr('Apollo 11').commander);
    DBMS_OUTPUT.PUT_LINE(apollo_arr('Apollo 11').launch);
end;
/

-- Printed results:
Neil Armstrong
16-JUL-69

APEX

[編輯 | 編輯原始碼]

Oracle Application Express 也稱為 APEX，是一個基於 Web 的軟體開發環境，執行在 Oracle 資料庫上。

字串替換

[編輯 | 編輯原始碼]
  • 在 SQL 中: :VARIABLE
  • 在 PL/SQL 中: V('VARIABLE') 或 NV('VARIABLE')
  • 在文字中: &VARIABLE.

參考資料

[編輯 | 編輯原始碼]
檢索自 "https://wikibook.tw/w/index.php?title=Oracle_Database/SQL_Cheatsheet&oldid=4414931"
華夏公益教科書