oracle中關于替代變量,accpt，綁定變量，字元變量

<code>此文檔介紹兩個事情，一個是替代變量，另一個就是了解一下硬解析和軟解析對于變量來說declare定義的好還是variable定義的好</code>

<code>在oracle 中，對于一個送出的sql語句,存在兩種可選的解析過程, 一種叫做硬解析,一種叫做軟解析.一個硬解析需要經解析,制定執行路徑,優化通路計劃等許多的步驟.硬解釋不僅僅耗費大量的cpu，更重要的是會占據重要的們闩（latch）資源，嚴重的影響系統的規模的擴大（即限制了系統的并發行），而且引起的問題不能通過增加記憶體條和cpu的數量來解決。之是以這樣是因為門闩是為了順序通路以及修改一些記憶體區域而設定的，這些記憶體區域是不能被同時修改。當一個sql語句送出後，oracle會首先檢查一下共享緩沖池（shared pool）裡有沒有與之完全相同的語句，如果有的話隻須執行軟分析即可，否則就得進行硬分析。</code>

<code> </code><code>而唯一使得oracle 能夠重複利用執行計劃的方法就是采用綁定變量。綁定變量的實質就是用于替代sql語句中的常量的替代變量。綁定變量能夠使得每次送出的sql語句都完全一樣。</code>

<code> </code><code>前兩天看到有人在pub上問在sqlplus中通過define和variable定義的變量的差別。其實define定義的我</code>

<code>了解不是變量而是字元常量，通過define定義之後，在通過&或者&&引用的時候不需要輸入了，僅此而已。</code>

<code>oracle在執行的時候自動用值進行了替換；而variable定義的是綁定變量,上面已經提到。 </code>

<code>綁定變量引用的時候用":" ,替代變量引用的時候用"&";</code>

<code>綁定變量初始化 exec :num1:=2,替代變量預設類型為char </code>

<code>替換變量（僅用于SQL *Plus或者用于原理和SQL *Plus相同的開發工具）：</code>

<code>臨時存儲值</code>

<code>利用它可以達到建立通用腳本的目的</code>

<code>利用它可以達到和使用者互動，故在SQL *Plus中又稱互動式指令</code>

<code>替換變量的格式式在變量名稱前加一個&，以便在運作SQL指令時提示使用者輸入替換資料，然後按輸入資料運作SQL指令</code>

<code>生命周期：單次引用中，不需要聲明，如果替換字元或日期類型，最好用單引号擴起</code>

<code>使用範圍：where、order by、清單達式、表名、整個SELECT 語句中</code>

<code>生命周期：整個會話（session連接配接）,不需要聲明</code>

<code>（3）define ：“define 變量名=變量值”eg：DEFINE a = clark;</code>

<code>生命周期：整個會話，預先聲明，使用時用&引用聲明的變量</code>

<code>define variable=使用者建立的CHAR類型的值：define 變量名=值；</code>

<code>define 變量名：檢視變量指令。 </code>

<code>undefine 變量名：清除變量</code>

<code>define：檢視在目前會話中所有的替換變量和它們的值</code>

<code>（4）accept</code>

<code>預先聲明，可以客戶化提示資訊，使用時用&引用聲明的變量。</code>

<code>accept 變量名name number/char/date prompt '提示資訊内容'即：ACC[EPT] variable </code>

<code>[NUM[BER] | CHAR | DATE] [FOR[MAT] format] [DEF[AULT] default] [PROMPT </code>

<code>PROMPT指令：用于輸出提示使用者的資訊，以便使使用者了解腳本檔案的功能和運作情況</code>

<code>PAUSE指令：用于暫停腳本檔案的運作</code>

<code>HIDE選項：用于隐藏使用者的輸入，使别人不可見，安全</code>

<code>這條指令的意思是：當plsql程式段執行到變量name的時候，此時需要使用者的互動才能繼續執行下去，plsql程式段會顯示“提示資訊内容”讓使用者輸入相關資訊（如果指定hide選項，那麼在接下去使用者輸入的東西将被用星号顯示出來增加安全，有點像輸入密碼），使用者輸入的内容被接收到并且把它付給name，關于在“提示資訊内容”下使用者輸入的内容的類型，plsql程式段開發人員來通過number/char/date指定，變量name得到正确的值以後，繼續執行相關下面的程式！</code>

<code>例：accept a char prompt '請輸入員工的雇傭時間(yyyy-mm-dd):' hide </code>

<code>例：accept a char prompt 'input a:' hide</code>

<code>set verify on/off; #verify：是否給出原值及新值提示。</code>

<code>具體請參看下面的例子：</code>

<code>plsql程式1：</code>

<code>declare </code>

<code> </code><code>v_sal number(6,2); </code>

<code> </code><code>v_ename emp.ename%type:='&ename'; </code>

<code>begin </code>

<code> </code><code>select sal into v_sal from emp </code>

<code>where lower(ename)=lower(v_ename); </code>

<code> </code><code>update emp set sal=v_sal + 200 </code>

<code> </code><code>where lower(ename)=lower(v_ename); </code>

<code>plsql程式2：</code>

<code> </code><code>v_ename emp.ename%type:='&&ename'; </code>

<code>secureCRT的一個會話中先執行程式2，再次執行程式1，會發現直接PL/SQL procedure successfully completed.</code>

<code> </code><code>而不讓我輸入ename，将set verify off也不行</code>

<code> </code><code>另一個打開會話将set verify off後，每次執行程式1都會讓你輸入ename。</code>

<code>這就是在前面一個會話執行程式2的時候已經将ename，儲存為了會話的變量，而不是plsql程式的變量。</code>

<code>另外一個案例完整的accept例子</code>

<code>CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,</code>

<code> </code><code>ENAME VARCHAR2(10),</code>

<code> </code><code>JOB VARCHAR2(9),</code>

<code> </code><code>MGR NUMBER(4),</code>

<code> </code><code>HIREDATE DATE,</code>

<code> </code><code>SAL NUMBER(7, 2),</code>

<code> </code><code>COMM NUMBER(7, 2),</code>

<code> </code><code>DEPTNO NUMBER(2));</code>

<code>INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, TO_DATE('23-12-2013', 'DD-MM-YYYY'), 800, NULL, 20);</code>

<code>INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-02-1981', 'DD-MM-YYYY'), 1600, 300, 30);</code>

<code>INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('22-02-1981', 'DD-MM-YYYY'), 1250, 500, 30);</code>

<code>INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839, TO_DATE('22-04-1981', 'DD-MM-YYYY'), 2975, NULL, 20);</code>

<code>INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-09-1981', 'DD-MM-YYYY'), 1250, 1400, 30);</code>

<code>INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('01-03-1981', 'DD-MM-YYYY'), 2850, NULL, 30);</code>

<code>INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839,TO_DATE('09-05-1981', 'DD-MM-YYYY'), 2450, NULL, 10);</code>

<code>INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('09-12-1982', 'DD-MM-YYYY'), 3000, NULL, 20);</code>

<code>INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL,TO_DATE('17-11-1981', 'DD-MM-YYYY'), 5000, NULL, 10);</code>

<code>INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('08-09-1981', 'DD-MM-YYYY'), 1500, 0, 30);</code>

<code>INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788,TO_DATE('12-06-1983', 'DD-MM-YYYY'), 1100, NULL, 20);</code>

<code>INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698,TO_DATE('13-12-1981', 'DD-MM-YYYY'), 950, NULL, 30);</code>

<code>INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566,TO_DATE('13-12-1981', 'DD-MM-YYYY'), 3000, NULL, 20);</code>

<code>INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-03-1982', 'DD-MM-YYYY'), 1300, NULL, 10);</code>

<code>prompt C R E A T E N E W E M P L O Y E E R E C O R D</code>

<code>prompt</code>

<code>prompt Enter the employee's information:</code>

<code>accept l_ename char format a10 prompt '名字: '</code>

<code>accept l_empno number format '9999' prompt '編号 #: '</code>

<code>accept l_sal number format '99999.99' prompt 'Salary [1000]: ' default '1000.00'</code>

<code>accept l_comm number format '99999.99' prompt 'Commission % [0]: ' default '0'</code>

<code>accept l_hired date format 'mm/dd/yyyy' prompt 'Hire date (mm/dd/yyyy): '</code>

<code>prompt List of available jobs:</code>

<code>select distinct job</code>

<code> </code><code>order by job</code>

<code>accept l_job char format a9 prompt 'Job: '</code>

<code>prompt List of managers and employee numbers:</code>

<code>select empno, ename</code>

<code> </code><code>order by ename</code>

<code>accept l_mgr number format '9999' prompt 'Manager''s Employee #: '</code>

<code>prompt List of department numbers and names:</code>

<code>select deptno, dname</code>

<code> </code><code>order by deptno</code>

<code>accept l_dept number format '99' prompt 'Department #: '</code>

<code>insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)</code>

<code>values (&l_empno, '&l_ename', '&l_job', &l_mgr,</code>

<code> </code><code>to_date('&l_hired','mm/dd/yyyy'), &l_sal, &l_comm, &l_dept)</code>

<code>select * from emp where empno=&l_empno</code>

<code>drop table emp;</code>

本文轉自 aklaus 51CTO部落格，原文連結:http://blog.51cto.com/aklaus/1950055

oracle中關于替代變量,accpt，綁定變量，字元變量

繼續閱讀

ASP程式設計中20個非常有用的例子

龍珠訓練營task04

阿裡雲天池龍珠計劃SQL訓練營打卡

阿裡雲天池龍珠計劃SQL訓練營day1

賦予使用者dba權限--解釋以上：

實驗樓sql進階之成績管理系統的資料操作(window)

Oracle的基本操作

為什麼要選擇UniDAC

SQL優化SQL語句優化的目的

JAVA高效程式設計指南

windows不能在本地計算機上運作oracleDbConsoleorcl

關于SQL語言

SQL語言基礎：常用的資料查詢語句

neo4j之cypher使用文檔

sqlServer根據經緯查距離

Oracle 批量查詢傳入List 傳回List