2013年12月4日星期三

Oracle使用者管理

查詢使用者授與角色

SQL> desc user_role_privs;
Name                  Type              Description
USERNAME VARCHAR2(30) Name of the user to whom the role has been granted.
GRANTED_ROLE VARCHAR2(30) Name of the role granted to the user.
ADMIN_OPTION VARCHAR2(3)   Whether the user is able to grant the role to another user or role. Equal to YES or NO.
DEFAULT_ROLE VARCHAR2(3)   Whether the role is enabled by default when the user connects to the database. Equal to YES or NO.
OS_GRANTED VARCHAR2(3)   Whether the role was granted by the operating system.

SELECT * FROM user_role_privs;

2013年10月30日星期三

SQL SERVER 2008 自動備份

日常工作中利用SQL SQLSERVER 2008 的維護計劃對數據庫進行定期的備份，這樣一方面可以對數據庫進行備份保證數據安全另一方面也可以減輕對維護人員的負擔。一般對於WEB 服務器進行維護都是在訪問人數較少的情況下進行的，要對數據進行備份要麼是利用一些備份的軟件或系統自動的備份工作例如windows server 2008 自動的Windows server Backup 功能這種也可以對來對數據庫進行備份具體操作方法請看本站的另一種實現數據庫的定時自動備份是利用SQL SERVER 2008自帶的維護計劃創建一個計劃對數據庫進行備份，下面我們將SQL SERVER 2008自動備份圖解教程分享給大家。

首先需要啓動SQL Server Agent服務，這個服務如果不啓動是無法運行新建作業的，點擊“開始”C“所有程序”C“Microsoft SQL Server 2008”C“啓動SQL Server Management Studio”登錄數據庫，點擊管理C維護計劃C右擊維護計劃嚮導如圖所示：

點擊“維護計劃嚮導”後跳出對話框，如圖所示：

點擊“下一步”如圖所示：

填寫好名稱及相關說明作個記號，點擊“更改” 來設定維護計劃，如圖所示：

可以爲選擇執的時間段，每天、每週、每月可以根據你相應的需求來制定備份的時間，這裏作演示就選擇在每天的0：00進行，點擊“確定”再點“下一步”如圖所示：

　　選擇你需要備份的任務，我這裏就先擇“備份數據庫（完整、差異、事務日誌）”，很明瞭點擊“下一步”如圖所示：

出現剛剛所選擇的三項你可以選擇他們所執行的順序，選好後點擊“下一步”如圖所示：

在數據庫那一列選擇相關數據庫點擊（確定）如圖所示（由於這張圖片較大您可以點擊圖片查看原圖）：

選擇備份的數據庫存放的目錄，設置備份壓縮：有默認服務器設置，壓縮備份等選項，因爲我的數據庫較大所以就選擇壓縮，根據您的實際情況進行操作：點擊”下一步”，下面的操作是對於這前我們所選擇的“維護任務”操作和“上一步”一樣這裏就不截圖說明，最後點擊“下一步”如圖所示：

選擇SQL SERVER 2008自動備份維護計劃的報告文件所存放位置點擊“下一步”如圖所示:

點擊“完成”這樣就完成了SQL SERVER 2008自動備份圖解。
注意：在利用SQL SQLSERVER 2008 的維護計劃對數據庫進行定期的備份時要啓動“SQL SERVER 代理”服務

2013年10月11日星期五

Oracle常數、變數替換及變數繫結

Literals, Substitution Variables and Bind Variables

If you've read anything about writing OLTP applications that talk to Oracle databases, you will know that bind variables are very important.

Each time an SQL statement is sent to the database, an exact text match is performed to see if the statement is already present in the shared pool. If no matching statement is found a hard parse is performed, which is a resource intensive process. If the statement is found in the shared pool this step is not necessary and a soft parse is performed. Concatenating variable values into an SQL statement makes the statement unique, forcing a hard parse. By contrast, using bind variables allow reuse of statements as the text of the statement remains the same. Only the value of the bind variable changes.

Why do we care?

Holding many similar SQL statements in the shared pool is a waste of memory.
Filling the shared pool with similar statements will cause well written statements to get paged out of the shared pool quickly, forcing them to be reparsed also.
Parsing SQL statements is a resource intensive process. Reducing the number of hard parses results in reduced CPU usage.

In the sections below you will see the impact of using literals, substitution variables and bind variables in your code.

Literals

The following example shows the affect of using literals on the shared pool. First the shared pool is cleared of previously parsed statements. Then two queries are issued, both specifying literal values in the WHERE clause. Finally the contents of the shared pool is displayed by querying the V$SQL view.

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

SQL> SELECT * FROM dual WHERE dummy = 'LITERAL1';

no rows selected

SQL> SELECT * FROM dual WHERE dummy = 'LITERAL2';

no rows selected

SQL> COLUMN sql_text FORMAT A60
SQL> SELECT sql_text,
  2         executions
  3  FROM   v$sql
  4  WHERE  INSTR(sql_text, 'SELECT * FROM dual WHERE dummy') > 0
  5  AND    INSTR(sql_text, 'sql_text') = 0
  6  ORDER BY sql_text;

SQL_TEXT                                                     EXECUTIONS
------------------------------------------------------------ ----------
SELECT * FROM dual WHERE dummy = 'LITERAL1'                           1
SELECT * FROM dual WHERE dummy = 'LITERAL2'                           1

2 rows selected.

SQL>

From this we can see that both queries were parsed separately.

Substitution Variables

Substitution variables are a feature of the SQL*Plus tool. They have nothing to do with the way SQL is processed by the database server. When a substitution variable is used in a statement, SQL*Plus requests an input value and rewrites the statement to include it. The rewritten statement is passed to the database. As a result, the database server knows nothing of the substitution variable. The following example illustrates this by repeating the previous test, this time using substitution variables.

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

SQL> SELECT * FROM dual WHERE dummy = '&dummy';
Enter value for dummy: SUBSTITUTION_VARIABLE1
old   1: SELECT * FROM dual WHERE dummy = '&dummy'
new   1: SELECT * FROM dual WHERE dummy = 'SUBSTITUTION_VARIABLE1'

no rows selected

SQL> SELECT * FROM dual WHERE dummy = '&dummy';
Enter value for dummy: SUBSTITUTION_VARIABLE2
old   1: SELECT * FROM dual WHERE dummy = '&dummy'
new   1: SELECT * FROM dual WHERE dummy = 'SUBSTITUTION_VARIABLE2'

no rows selected

SQL> COLUMN sql_text FORMAT A60
SQL> SELECT sql_text,
  2         executions
  3  FROM   v$sql
  4  WHERE  INSTR(sql_text, 'SELECT * FROM dual WHERE dummy') > 0
  5  AND    INSTR(sql_text, 'sql_text') = 0
  6  ORDER BY sql_text;

SQL_TEXT                                                     EXECUTIONS
------------------------------------------------------------ ----------
SELECT * FROM dual WHERE dummy = 'SUBSTITUTION_VARIABLE1'             1
SELECT * FROM dual WHERE dummy = 'SUBSTITUTION_VARIABLE2'             1

2 rows selected.

SQL>

Once again, both statements were parsed separately. As far as the database server is concerned, literals and substitution variables are the same thing.

Exactly the same behavior occurs when scripts contain placeholders to allow parameters to be sent to them from the command line. So for example, imagine a script called "dummy.sql" containing the following.

SELECT * FROM dual WHERE dummy = '&1';

This can be called from SQL*Plus like this.

SQL> @dummy MyValue

When run, the placeholder '&1' will be replaced by the value 'MyValue'. This is just the same as a substitution variable.

Bind Variables

The following example illustrates the affect of bind variable usage on the shared pool. It follows the same format as the previous examples.

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

SQL> VARIABLE dummy VARCHAR2(30);
SQL> EXEC :dummy := 'BIND_VARIABLE1';

PL/SQL procedure successfully completed.

SQL> SELECT * FROM dual WHERE dummy = :dummy;

no rows selected

SQL> EXEC :dummy := 'BIND_VARIABLE2';

PL/SQL procedure successfully completed.

SQL> SELECT * FROM dual WHERE dummy = :dummy;

no rows selected

SQL> COLUMN sql_text FORMAT A60
SQL> SELECT sql_text,
  2         executions
  3  FROM   v$sql
  4  WHERE  INSTR(sql_text, 'SELECT * FROM dual WHERE dummy') > 0
  5  AND    INSTR(sql_text, 'sql_text') = 0
  6  ORDER BY sql_text;

SQL_TEXT                                                     EXECUTIONS
------------------------------------------------------------ ----------
SELECT * FROM dual WHERE dummy = :dummy                               2

1 row selected.

SQL>

This clearly demonstrates that the same SQL statement was executed twice.

Performance Issues

The following example measures the amount of CPU used by a session for hard and soft parses when using literals. The shared pool is flushed and a new session is started. Dynamic SQL is used to mimic an application sending 10 statements to the database server. Notice that the value of the loop index is concatinated into the string, rather than using a bind variable. The CPU usage is retrieved from the V$MYSTAT view by querying the "parse time cpu" statistic. This statistic represents the total CPU time used for parsing (hard and soft) in 10s of milliseconds. The statements present in the shared pool are also displayed.

SQL> CONN sys/password AS SYSDBA
Connected.
SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

SQL> CONN sys/password AS SYSDBA
Connected.
SQL> DECLARE
  2    l_dummy  dual.dummy%TYPE;
  3  BEGIN
  4    FOR i IN 1 .. 10 LOOP
  5      BEGIN
  6        EXECUTE IMMEDIATE 'SELECT dummy FROM dual WHERE dummy = ''' || TO_CHAR(i) || ''''
  7        INTO l_dummy;
  8      EXCEPTION
  9        WHEN NO_DATA_FOUND THEN
 10          NULL;
 11      END;
 12    END LOOP;
 13  END;
 14  /

PL/SQL procedure successfully completed.

SQL> SELECT sn.name, ms.value
  2  FROM   v$mystat ms, v$statname sn
  3  WHERE  ms.statistic# = sn.statistic#
  4  AND    sn.name       = 'parse time cpu';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse time cpu                                                           63

1 row selected.

SQL> COLUMN sql_text FORMAT A60
SQL> SELECT sql_text,
  2         executions
  3  FROM   v$sql
  4  WHERE  INSTR(sql_text, 'SELECT dummy FROM dual WHERE dummy') > 0
  5  AND    INSTR(sql_text, 'sql_text') = 0
  6  AND    INSTR(sql_text, 'DECLARE') = 0
  7  ORDER BY sql_text;

SQL_TEXT                                                     EXECUTIONS
------------------------------------------------------------ ----------
SELECT dummy FROM dual WHERE dummy = '1'                              1
SELECT dummy FROM dual WHERE dummy = '10'                             1
SELECT dummy FROM dual WHERE dummy = '2'                              1
SELECT dummy FROM dual WHERE dummy = '3'                              1
SELECT dummy FROM dual WHERE dummy = '4'                              1
SELECT dummy FROM dual WHERE dummy = '5'                              1
SELECT dummy FROM dual WHERE dummy = '6'                              1
SELECT dummy FROM dual WHERE dummy = '7'                              1
SELECT dummy FROM dual WHERE dummy = '8'                              1
SELECT dummy FROM dual WHERE dummy = '9'                              1

10 rows selected.

SQL>

The results show that 630 milliseconds of CPU time were used on parsing during the session. In addition, the shared pool contains 10 similar statements using literals.

The following example is a repeat of the previous example, this time using bind variables. Notice that the USING clause is used to supply the loop index, rather than concatenating it into the string.

SQL> CONN sys/password AS SYSDBA
Connected.
SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

SQL> CONN sys/password AS SYSDBA
Connected.
SQL> 
SQL> DECLARE
  2    l_dummy  dual.dummy%TYPE;
  3  BEGIN
  4    FOR i IN 1 .. 10 LOOP
  5      BEGIN
  6        EXECUTE IMMEDIATE 'SELECT dummy FROM dual WHERE dummy = TO_CHAR(:dummy)'
  7        INTO l_dummy USING i;
  8      EXCEPTION
  9        WHEN NO_DATA_FOUND THEN
 10          NULL;
 11      END;
 12    END LOOP;
 13  END;
 14  /

PL/SQL procedure successfully completed.

SQL> 
SQL> SELECT sn.name, ms.value
  2  FROM   v$mystat ms, v$statname sn
  3  WHERE  ms.statistic# = sn.statistic#
  4  AND    sn.name       = 'parse time cpu';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse time cpu                                                           40

1 row selected.

SQL> 
SQL> COLUMN sql_text FORMAT A60
SQL> SELECT sql_text,
  2         executions
  3  FROM   v$sql
  4  WHERE  INSTR(sql_text, 'SELECT dummy FROM dual WHERE dummy') > 0
  5  AND    INSTR(sql_text, 'sql_text') = 0
  6  AND    INSTR(sql_text, 'DECLARE') = 0
  7  ORDER BY sql_text;

SQL_TEXT                                                     EXECUTIONS
------------------------------------------------------------ ----------
SELECT dummy FROM dual WHERE dummy = TO_CHAR(:dummy)                 10

1 row selected.

SQL>

The results show that 400 milliseconds of CPU time were used on parsing during the session, less than two thirds the amount used in the previous example. As expected, there is only a single statement in the shared pool.

These simple examples clearly show how replacing literals with bind variables can save both memory and CPU, making OLTP applications faster and more scalable. If you are using third-party applications that don't use bind variables you may want to consider setting the CURSOR_SHARING parameter, but this should not be considered a replacement for bind variables. The CURSOR_SHARING parameter is less efficient and can potentially reduce performance compared to proper use of bind variables.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.

Oracle 預儲程序

[Oracle] 開發 & 使用 Stored Procedures

概觀

PL/SQL 是在資料庫端執行的，因此若是將資料庫處理的程式都寫成 stored procedure，撰寫程式時就不需要對資料庫進行頻繁的連線與資料往來，如此一來效能也會相對有所提昇。

在 PL/SQL 中，程式設計的基本單元為獨立的 stored procedure、function 或是 package：

stored procedure：沒有回傳值。
function：有回傳值。
package：類似 Java 中的 package 或是 .NET 中的 namespace 一樣，可以用來區隔相同名稱的 stored procedure 或是 function。

Procedures & Functions

procedure 與 function 是由三個主要部份所組成，分別是：

宣告部份(declarative-part)
此部份所使用的關鍵字為「DECLARE」，用來定義變數與常數之用。
執行部份(executable-part)
此部份是程式執行的部份，會使用關鍵字「BEGIN」與「END」包住來表示開始與結束。
例外處理部份(exception-handling part)
透過關鍵字「EXCEPTION」來處理程式中可能會發生的錯誤情況。

建立 Stored Procedures & Functions

建立 stored procedure 的語法如下：

CREATE OR REPLACE procedure_name(arg1 data_type, ...)
AS
BEGIN
   ....
END procedure_name;

以下來個建立 procedure 的範例：(官方網站使用說明)

CREATE OR REPLACE PROCEDURE ADD_EVALUATION
(
   EVALUATION_ID   IN    NUMBER,
   EMPLOYEE_ID     IN    NUMBER,
   EVALUATION_DATE IN    DATE,
   JOB_ID          IN    VARCHAR2,
   MANAGER_ID      IN    NUMBER,
   DEPARTMENT_ID   IN    NUMBER
)
AS
BEGIN
   NULL;
END ADD_EVALUATION;

再來個建立 function 的範例：

CREATE OR REPLACE FUNCTION CALCULATE_SCORE
(
   CAT     IN  VARCHAR2,
   SCORE   IN  NUMBER,
   WEIGHT  IN  NUMBER
)
   RETURN NUMBER
AS
BEGIN
   RETURN NULL;
END CALCULATE_SCORE;

刪除 Stored Procedures & Functions

語法更簡單了：

DROP PROCEDURE ADD_EVALUATION;

Packages

procedure 與 function 根據其所在之處，有三種不同的有效範圍：

standalone procedure(function)：不包含於任何的 package 或是 subprogram 中，屬於 schema-level。
packaged subprogram：定義於 package 中，由 package 進行區隔。
local subprogram：定義於 subprogram 或是 PL/SQL 程式碼區段中，這一類的程式無法從外界被呼叫，類似 Inner(Nested) Class。

將 procedure 或是 function 定義於 package 之下有什麼好處呢? 若是 standalone 的 procedure(function)，所能接收的參數型態僅能是向量型態(NUMBER、VARCHAR2、DATE)的變數，若是比較複雜的結構像是 RECORD 就沒辦法了；但若是定義在 package 中就可以囉!

而 package 包含了兩個部份，分別是定義(specification)的部份與實作(body)的部份；而整個作法很類似 C 語言中針對 function 的定義與處理，也就是「在 specification 中定義公用的 procedure(or function) 以及相關參數，並在 body 中實作定義」。

關於 package 的詳細使用方式，可以參考官方網站的文章。

建立 Package

範例(specification)：

--定義 PACKAGE
CREATE OR REPLACE PACKAGE EMP_EVAL
AS
   -- 宣告 PROCEDURE 與 FUNCTION
   PROCEDURE EVAL_DEPARTMENT(DEPARTMENT_ID IN NUMBER);
   FUNCTION CALCULATE_SCORE(EVALUATION_ID  IN NUMBER, PERFORMANCE_ID IN NUMBER) RETURN NUMBER;
END EMP_EVAL;

範例(body)：

--設定 PACKAGE 的實作內容
CREATE OR REPLACE PACKAGE BODY EMP_EVAL
AS
   --PROCEDURE 的實作內容
   PROCEDURE EVAL_DEPARTMENT(DEPARTMENT_ID IN NUMBER)
   AS
   BEGIN
       --實作於此處
       NULL;
   END EVAL_DEPARTMENT;
  
   --FUNCTION 的實作內容
   FUNCTION CALCULATE_SCORE(EVALUATION_ID  IN NUMBER, PERFORMANCE_ID IN NUMBER) RETURN NUMBER
   AS
   BEGIN
       --實作於此處
       RETURN NULL;
   END CALCULATE_SCORE;
END EMP_EVAL;

刪除 Package

DROP PACKAGE EMP_EVAL;

變數(variable)與常數(constant)的定義

撰寫 procedure 與 function 的優點是在 PL/SQL 中，可以使用由 Oracle 提供，但不能用於 table 欄位定義的資料型態，像是 BOOLEAN、RECORD、REF … 等等。

以下用個簡單範例說明 variable 與 constant 的使用方式：

--設定 PACKAGE 的實作內容
CREATE OR REPLACE PACKAGE BODY EMP_EVAL
AS
   --PROCEDURE 的實作內容
   PROCEDURE EVAL_DEPARTMENT(DEPARTMENT_ID IN NUMBER)
   AS
   BEGIN
       --實作於此處
       NULL;
   END EVAL_DEPARTMENT;
  
   --FUNCTION 的實作內容
   FUNCTION CALCULATE_SCORE(EVALUATION_ID  IN NUMBER, PERFORMANCE_ID IN NUMBER) RETURN NUMBER
   AS
       --變數(variable)與常數(constant)宣告於此處
       n_score   NUMBER(1, 0);
      
       --VARIABLE
       n_weight  NUMBER;      
      
       --VARIABLE
       max_score   CONSTANT NUMBER(1, 0) := 9;
      
       --CONSTANT
       max_weight  CONSTANT NUMBER(8, 8) := 1;
      
   BEGIN
       --實作於此處
       RETURN NULL;
   END CALCULATE_SCORE;
END EMP_EVAL;

將變數(or 常數)與資料表欄位型態連結

若是要將 variable 或是 constant 與 table 中欄位的型態設定為相同，為了避免欄位型態的改變而造成程式發生錯誤，可透過「%TYPE」關鍵字來處理，以下舉個例子：

--設定 PACKAGE 的實作內容
CREATE OR REPLACE PACKAGE BODY EMP_EVAL
AS
   --PROCEDURE 的實作內容
   PROCEDURE EVAL_DEPARTMENT(DEPARTMENT_ID IN NUMBER)
   AS
   BEGIN
       --實作於此處
       NULL;
   END EVAL_DEPARTMENT;
  
   --FUNCTION 的實作內容
   --將 EVALUATION_ID 的型態與 SCORES 中的 EVALUATION_ID 欄位型態連結
   --將 PERFORMANCE_ID 的型態與 SCORES 中的 PERFORMANCE_ID 欄位型態連結
   FUNCTION CALCULATE_SCORE(EVALUATION_ID  IN SCORES.EVALUATION_ID%TYPE, PERFORMANCE_ID IN SCORES.PERFORMANCE_ID%TYPE) RETURN NUMBER
   AS
       --變數(variable)與常數(constant)宣告於此處
       --將 n_score 的型態與 SCORES 中的 SCORE 欄位型態連結
       --將 n_weight 的型態與 PERFORMANCE_PARTS 中的 WEIGHT 欄位型態連結
       --常數的部分也是連結到 TABLE 的欄位型態
       n_score   SCORES.SCORE%TYPE;             
      
       --VARIABLE
       n_weight  PERFORMANCE_PARTS.WEIGHT%TYPE; 
      
       --VARIABLE
       max_score   CONSTANT SCORES.SCORE%TYPE := 9;            
      
       --CONSTANT
       max_weight  CONSTANT PERFORMANCE_PARTS.WEIGHT%TYPE := 1;
   BEGIN
       --實作於此處
       RETURN NULL;
   END CALCULATE_SCORE;
END EMP_EVAL;

如此一來，若是 SCORE 中的 EVALUATION_ID、PERFORMANCE_ID 的型態改變，程式中定義的 variable 或是 constant 的型態也會跟著改變囉。

設定變數值

設定變數值有很多方式，以下舉例：

直接指定或是經由計算：

FUNCTION CALCULATE_SCORE(EVALUATION_ID  IN SCORES.EVALUATION_ID%TYPE, PERFORMANCE_ID IN SCORES.PERFORMANCE_ID%TYPE) RETURN NUMBER
AS
   n_score     SCORES.SCORE%TYPE;               
  
   --VARIABLE
   n_weight    PERFORMANCE_PARTS.WEIGHT%TYPE;   
   --VARIABLE
   running_total NUMBER := 0; 
  
   --計算中使用
   max_score   CONSTANT SCORES.SCORE%TYPE := 9; 
  
   --CONSTANT
   max_weight  CONSTANT PERFORMANCE_PARTS.WEIGHT%TYPE := 1;
BEGIN
   --實作於此處
   running_total := max_score * max_weight;
   RETURN running_total;
END CALCULATE_SCORE;

從資料庫中取值：

FUNCTION CALCULATE_SCORE(EVALUATION_ID  IN SCORES.EVALUATION_ID%TYPE, PERFORMANCE_ID IN SCORES.PERFORMANCE_ID%TYPE) RETURN NUMBER
AS
   n_score     SCORES.SCORE%TYPE;               
  
   --VARIABLE
   n_weight    PERFORMANCE_PARTS.WEIGHT%TYPE;   
  
   --VARIABLE
   running_total NUMBER := 0; 
  
   --計算中使用
   max_score   CONSTANT SCORES.SCORE%TYPE := 9; 
  
   --CONSTANT
   max_weight  CONSTANT PERFORMANCE_PARTS.WEIGHT%TYPE := 1;
      
BEGIN
   --實作於此處
   SELECT s.SCORE INTO n_score
   --設定 n_score 的值
   FROM SCORES s
   WHERE EVALUATION_ID = s.EVALUATION_ID;
  
   SELECT p.weight INTO n_weight
   --設定 n_weight 的值
   FROM PERFORMANCE_PARTS p
   WHERE PERFORMANCE_ID = p.PERFORMANCE_ID;
  
   --計算 running_total
   running_total := max_score * max_weight;
  
   --回傳值
   RETURN running_total;
END CALCULATE_SCORE;

從資料庫取值，並將新增一筆資料至另外一個 table

procedure add_eval(employee_id in employees.employee_id%type, today in date)
as
   job_id        employees.job_id%type;
   manager_id    employees.manager_id%type;
   department_id employees.department_id%type;
begin
   --從 employees 表格中取得值
   select e.job_id into job_id
   from employees e
   where employee_id = e.employee_id;
  
   select e.manager_id into manager_id
   from employees e
   where employee_id = e.employee_id;
  
   select e.department_id into department_id
   from employees e
   where employee_id = e.employee_id;
  
   --新增資料
   insert into evaluations values(evaluations_seq.NEXTVAL, employee_id, today, job_id, manager_id, department_id, 0);
end add_eval;

流程控制

IF 與 CASE 的搭配範例：

function eval_frequency(employee_id in employees.employee_id%type) return pls_integer
as
   hire_date   employees.hire_date%type;
   today       employees.hire_date%type;
   eval_freq   pls_integer;
   job_id      employees.job_id%type;
begin
   --設定今天日期
   select sysdate into today from dual;
  
   --取得員工開始工作日期
   select e.hire_date into hire_date from employees e where e.employee_id = employee_id;
  
   --判斷年資是否大於十年
   if((hire_date + (interval '120' month)) < employee_id =" employee_id;">

另外還有 WHILE…LOOP 與 LOOP…EXIT WHEN 兩種不同的用法，以下就不寫這麼長的範例了，直接給 syntax，要會用應該也不難了!

WHILE…LOOP

WHILE condition 
LOOP
    ……
END LOOP;

LOOP…EXIT WHEN

LOOP
    ……
    EXIT WHEN condition
    ……
EXIT LOOP

Oracle 顯示所有表格的資料筆數

Oracle 顯示所有表格的資料筆數可用下列SQL：

select table_name,
to_number(

extractvalue(

xmltype(

dbms_xmlgen.getxml(

'select count(*) c from ||table_name)

)

,'/ROWSET/ROW/C'

)

) count
from user_tables

2013年10月8日星期二

Oracle 資料匯出匯入

從 oracle 10g 之後, 就開始有了 import / export datapump, 據說 performance 更好, 有更多厲害的玩意兒, 所以趁這次升級到 11gR2 的時候, 把一些 scripts 改成了 datapump, 當然也有遇到了一些問題, 所以也做個筆記

(1) 語法的不同

expdp 需要在 database 指定個 directory 物件, 所以就不能像 exp 那樣, 愛 export 到那個目錄就到那個目錄, 這樣是有比較安全, 但是卻沒什麼彈性, 也就是說不能從另外一台 server 做 exp 了

create or replace directory exp_dir as '/net/exp';

grant read, write on directory exp_dir to rd_garlic;

在以前把 schema 匯出是這樣下

exp garlic/12345678 rows=n file=/net/exp/schema.dmp

現在要這樣下

expdp garlic/12345678 content=metadata_only directory=exp_dir dumpfile=schema.dmp reuse_dumpfiles=true

以前要匯出某個 table 的資料要這樣下

exp garlic/12345678 tables=${TABLE} rows=y file=/net/exp/${TABLE}.dmp

現在要這樣下

expdp garlic/12345678 tables=${TABLE} content=all directory=exp_dir dumpfile=${TABLE}.dmp

(2) 砍掉 datapump jobs

以前用 exp 做 export 時, 如果臨時要 cancel 的話, 就把 shell script kill 掉, 然後去 v$session 看看有沒有 session 還在做 exp, 如果有的話就 alter system kill session ..... 就 OK 了, 現在用了 datapump 做 export 的話就得這樣做

SELECT o.status, o.object_id, o.object_type,
o.owner||'.'||object_name "OWNER.OBJECT"
FROM dba_objects o, dba_datapump_jobs j
WHERE o.owner=j.owner_name AND o.object_name=j.job_name ;

這樣可以找出目前還在執行的 datapump, 要看的是 OWNER.OBJECT 這個欄位, 會長的像 USER.SYS_EXPORT_FULL_01

這時要終結這個 job 的話, 要下

drop table USER.SYS_EXPORT_FULL_01 purge;

這樣才能把 datapump jobs 給砍掉

2013年9月21日星期六

寫出更快的SQL

如果在SELECT語句前放上關鍵字EXPLAIN，MySQL將解釋它如何處理SELECT，提供有關表如何聯接和聯接的次序。借助於EXPLAIN，可以知道什麼時候必須為表加入索引以得到一個使用索引來尋找記錄的更快的SELECT。

1、EXPLAIN tbl_name

EXPLAIN tbl_name是DESCRIBE tbl_name或SHOW COLUMNS FROM tbl_name的一個同義詞。

2、EXPLAIN [EXTENDED] SELECT select_options

如果在SELECT語句前放上關鍵字EXPLAIN，MySQL將解釋它如何處理SELECT，提供有關表如何聯接和聯接的次序。

借助於EXPLAIN，可以知道什麼時候必須為表加入索引以得到一個使用索引來尋找記錄的更快的SELECT。

還可以知道優化器是否以一個最佳次序聯接表。為了強制優化器讓一個SELECT語句按照表命名順序的聯接次序，語句應以STRAIGHT_JOIN而不只是SELECT開頭。

EXPLAIN為用於SELECT語句中的每個表返回一行資訊。表以它們在處理查詢過程中將被MySQL讀入的順序被列出。MySQL用一遍掃描多次聯接(single-sweep multi-join)的方式解決所有聯接。這意味著MySQL從第一個表中讀一行，然後找到在第二個表中的一個匹配行，然後在第3個表中等等。當所有的表處理完後，它輸出選中的列並且返回表清單直到找到一個有更多的匹配行的表。從該表讀入下一行並繼續處理下一個表。

當使用EXTENDED關鍵字時，EXPLAIN產生附加資訊，可以用SHOW WARNINGS流覽。該資訊顯示優化器限定SELECT語句中的表和列名，重寫並且執行優化規則後SELECT語句是什麼樣子，並且還可能包括優化過程的其他注解。

EXPLAIN的每個輸出行提供一個表的相關資訊，並且每個行包括下面的列：

id：SELECT識別符。這是SELECT的查詢序列號。

select_type：SELECT類型。

SIMPLE：簡單SELECT(不使用UNION或子查詢)

PRIMARY：最外面的SELECT

UNION：UNION中的第二個或後面的SELECT語句

DEPENDENT UNION：UNION中的第二個或後面的SELECT語句，取決於外面的查詢

UNION RESULT：UNION的結果

SUBQUERY：子查詢中的第一個SELECT

DEPENDENT SUBQUERY：子查詢中的第一個SELECT，取決於外面的查詢

DERIVED：導出表的SELECT(FROM子句的子查詢)

table：表名

type：聯接類型

system：表僅有一行(=系統表)。這是const聯接類型的一個特例。

const：表最多有一個匹配行，它將在查詢開始時被讀取。因為僅有一行，在這行的列值可被優化器剩餘部分認為是常數。const用於用常數值比較PRIMARY KEY或UNIQUE索引的所有部分時。

eq_ref：對於每個來自於前面的表的行組合，從該表中讀取一行。這可能是最好的聯接類型，除了const類型。它用在一個索引的所有部分被聯接使用並且索引是UNIQUE或PRIMARY KEY。eq_ref可以用於使用= 操作符比較的帶索引的列。比較值可以為常量或一個使用在該表前面所讀取的表的列的運算式。

ref：對於每個來自於前面的表的行組合，所有有匹配索引值的行將從這張表中讀取。如果聯接只使用鍵的最左邊的首碼，或如果鍵不是UNIQUE或PRIMARY KEY(換句話說，如果聯接不能基於關鍵字選擇單個行的話)，則使用ref。如果使用的鍵僅僅匹配少量行，該聯接類型是不錯的。ref可以用於使用=或<=>操作符的帶索引的列。

ref_or_null：該聯接類型如同ref，但是添加了MySQL可以專門搜索包含NULL值的行。在解決子查詢中經常使用該聯接類型的優化。

index_merge：該聯接類型表示使用了索引合併優化方法。在這種情況下，key列包含了使用的索引的清單，key_len包含了使用的索引的最長的關鍵元素。

unique_subquery：該類型替換了下面形式的IN子查詢的ref：value IN (SELECT primary_key FROMsingle_table WHERE some_expr);unique_subquery是一個索引查找函數，可以完全替換子查詢，效率更高。

index_subquery：該聯接類型類似於unique_subquery。可以替換IN子查詢，但只適合下列形式的子查詢中的非唯一索引：value IN (SELECT key_column FROM single_table WHERE some_expr)

range：只檢索給定範圍的行，使用一個索引來選擇行。key列顯示使用了哪個索引。key_len包含所使用索引的最長關鍵元素。在該類型中ref列為NULL。當使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比較關鍵字列時，可以使用

range index：該聯接類型與ALL相同，除了只有索引樹被掃描。這通常比ALL快，因為索引檔通常比資料檔案小。

all：對於每個來自於先前的表的行組合，進行完整的表掃描。如果表是第一個沒標記const的表，這通常不好，並且通常在它情況下很差。通常可以增加更多的索引而不要使用ALL，使得行能基於前面的表中的常數值或列值被檢索出。

ossible_keys：possible_keys列指出MySQL能使用哪個索引在該表中找到行。注意，該列完全獨立於EXPLAIN輸出所示的表的次序。這意味著在possible_keys中的某些鍵實際上不能按生成的表次序使用。

key：key列顯示MySQL實際決定使用的鍵(索引)。如果沒有選擇索引，鍵是NULL。要想強制MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len：key_len列顯示MySQL決定使用的鍵長度。如果鍵是NULL，則長度為NULL。注意通過key_len值我們可以確定MySQL將實際使用一個多部關鍵字的幾個部分。

ref：ref列顯示使用哪個列或常數與key一起從表中選擇行。

rows：rows列顯示MySQL認為它執行查詢時必須檢查的行數。

Extra：該列包含MySQL解決查詢的詳細資訊。

Distinct：MySQL發現第1個匹配行後，停止為當前的行組合搜索更多的行。

Not exists：

MySQL能夠對查詢進行LEFT JOIN優化，發現1個匹配LEFT JOIN標準的行後，不再為前面的的行組合在該表內檢查更多的行。

range checked for each record (index map: #)：MySQL沒有發現好的可以使用的索引，但發現如果來自前面的表的列值已知，可能部分索引可以使用。對前面的表的每個行組合，MySQL檢查是否可以使用range或index_merge訪問方法來索取行。

Using filesort：MySQL需要額外的一次傳遞，以找出如何按排序順序檢索行。通過根據聯接類型流覽所有行並為所有匹配WHERE子句的行保存排序關鍵字和行的指針來完成排序。然後關鍵字被排序，並按排序順序檢索行。

Using index：從只使用索引樹中的資訊而不需要進一步搜索讀取實際的行來檢索表中的列資訊。當查詢只使用作為單一索引一部分的列時，可以使用該策略。

Using temporary：為了解決查詢，MySQL需要創建一個臨時表來容納結果。典型情況如查詢包含可以按不同情況列出列的GROUP BY和ORDER BY子句時。

Using where：WHERE子句用於限制哪一個行匹配下一個表或發送到客戶。除非你專門從表中索取或檢查所有行，如果Extra值不為Using where並且表聯接類型為ALL或index，查詢可能會有一些錯誤。

Using sort_union(...), Using union(...), Using intersect(...)：這些函數說明如何為index_merge聯接類型合併索引掃描。

Using index for group-by：類似於訪問表的Using index方式，Using index for group-by表示MySQL發現了一個索引，可以用來查詢GROUP BY或DISTINCT查詢的所有列，而不要額外搜索硬碟訪問實際的表。並且，按最有效的方式使用索引，以便對於每個組，唯讀取少量索引條目。

通過相乘EXPLAIN輸出的rows列的所有值，你能得到一個關於一個聯接如何的提示。這應該粗略地告訴你MySQL必須檢查多少行以執行查詢。當你使用max_join_size變數限制查詢時，也用這個乘積來確定執行哪個多表SELECT語句。

訂閱：文章 (Atom)

標籤

2013年12月4日 星期三

2013年10月30日 星期三

2013年10月11日 星期五