【PLSQL】綁定變數，動態SQL，硬解析和軟解析，plsqlsql

【PLSQL】綁定變數，動態SQL，硬解析和軟解析，plsqlsql
************************************************************************  ****原文：blog.csdn.net/clark_xu 徐長亮的專欄************************************************************************1.1 變數

在匿名塊或者預存程序中定義的變數為局部變數，及範圍在整個匿名塊或預存程序中。運行結束，則該變數就不存在了；

         綁定變數

         在PLSQL的SQL中直接私人綁定變數：bv_name，不需要定義

1.1.1  綁定變數的定義

--綁定變數的定義

variable I number

begin

         for I in 1 .. 1000 loop

         execute immediate ‘insert into test values(:i)’ using I;

         :I :=I; ---綁定變數的賦值

         end loop

         dbms_output.put_line(:i);

         commit;

end;

print i

exec :i=:=10000 --- 綁定變數的賦值

print ：I；

3.3 PLSQL的SQL分類

靜態SQL：

         --在PLSQL塊中使用的SQL語句在編譯的時候是明確的，SQL語句在PLSQL編輯階段編譯；

動態SQL：

         --PLSQL編譯時間SQL是不確定的，如根據使用者輸入參數的不同而執行不同的操作，

         編譯器對動態語句不處理；在啟動並執行時候，動態建立SQL語句

3.3.1 靜態SQL

--一次硬分析，一次軟分析，1000次執行

create or replace procedure proc1

ls

begin

         for I in 1 .. 1000 loop

         insert into test values(I);

         end loop;

         commit;

end;

begin proc1 end;

3.3.2 本地動態SQL(未使用綁定變數)

1000此硬分析，1000此軟分析,1000次執行

create procedure proc1 ls

begin

         for I In 1  ..1000 loop

         execute immediate ‘insert into test values(‘||I||’)’;

end loop;

commit;

end;

---編譯過程的時候，不編譯sql語句

begin proc1 end;

3.3.3 本地動態SQL（使用綁定變數）

--1次硬分析，1000次軟分析，1000次執行

create procedure proc1 ls

begin

         for I In 1  ..1000 loop

         execute immediate ‘insert into test values(：i)’ using i;

end loop;

commit;

begin proc1 end;

3.4 SQL語句的處理過程

語法檢查：syntax check

語義檢查：semantic check

         --諸如檢查sql語句中的訪問對象是否儲存，該使用者是否具備相應的許可權；

對sql語句進行解析parse

         --利用內部演算法對sql進行解析，產生解析樹（parse tree）及執行計畫（execution plan）

執行sql,返回結果：execute and  return;

3.4.1 硬解析和軟解析

oracle利用內部hash演算法來獲得該sql的hash值，然後在library cache裡尋找是否存在該hash值；

假設存在，則將此sql與cache中的進行比較；假設“相同”，就將利用已有的解析數和執行計畫，而忽略了最佳化器的相關工作，這就是軟解析的過程；

如果上面兩個減少中任由一個不成立，那麼最佳化器都將進行建立解析樹，產生執行計畫的動作，這個過程都叫硬解析；

3.5 對應預存程序的操作

建立並編譯過程：

         create or replace procedure

編譯過程

         alter procedure procname compile;

調用過程

         用匿名子程式調用，直接寫過程名

         用有名子程式調用，直接寫過程名；

刪除過程：

         drop procedure

3.5.1 過程的使用案例

建立預存程序

create or replace procedure p_account

(p_id number,p_realname out varchar2,p_age out number)

ls

begin

         select real_name,round((sysdate-birthdate)/360) into p_realname,p_age

         from account

         where id=p_id;

exception

         when no_data_found then

         p_realname :=’no account’;

         p_age：=0；

end;

有名子程式調用：

declare

         v_realname varchar2(20);

         v_age number;

begin

         p_account(1011,v_realname,v_age);

         dbms_output.put_line(v_realname || ‘’ || v_age);

end;

匿名子程式調用,綁定變數：

         variable b_realname varchar2(20)

         variable b_age number

         begin

                   p_account(1011,:b_realname,:b_age)；

         end;

         print b_realname;

         print b_age;

3.6 PLSQL中的靜態sql

         oracle在解析sql時候會把PLSQL中定義的變數轉為綁定變數insert into test values(:b1),減少硬解析的次數；

         server process將執行完的sql cache起來，不關閉，當再執行sql，不需要軟解析；

         過程中的參數會自動轉化為綁定變數；

************************************************************************  ****原文：blog.csdn.net/clark_xu 徐長亮的專欄************************************************************************