oracle中關於替代變數,accpt，綁定變數，字元變數

標籤：字元變數   accept   替代變數   

此文檔介紹兩個事情，一個是替代變數，另一個就是瞭解一下硬解析和軟解析對於變數來說declare定義的好還是variable定義的好在oracle 中，對於一個提交的sql語句,存在兩種可選的解析過程, 一種叫做硬解析,一種叫做軟解析.一個硬解析需要經解析,制定執行路徑,最佳化訪問計劃等許多的步驟.硬解釋不僅僅耗費大量的cpu，更重要的是會佔據重要的們閂（latch）資源，嚴重的影響系統的規模的擴大（即限制了系統的並發行），而且引起的問題不能通過增加記憶體條和cpu的數量來解決。之所以這樣是因為門閂是為了順序訪問以及修改一些記憶體地區而設定的，這些記憶體地區是不能被同時修改。當一個sql語句提交後，oracle會首先檢查一下共用緩衝池（shared pool）裡有沒有與之完全相同的語句，如果有的話只須執行軟分析即可，否則就得進行硬分析。 而唯一使得oracle 能夠重複利用執行計畫的方法就是採用綁定變數。綁定變數的實質就是用於替代sql語句中的常量的替代變數。綁定變數能夠使得每次提交的sql語句都完全一樣。 串連 前兩天看到有人在pub上問在sqlplus中通過define和variable定義的變數的區別。其實define定義的我理解不是變數而是字元常量，通過define定義之後，在通過&或者&&引用的時候不需要輸入了，僅此而已。oracle在執行的時候自動用值進行了替換；而variable定義的是綁定變數,上面已經提到。 替換變數（僅用於SQL *Plus或者用於原理和SQL *Plus相同的開發工具）：臨時儲存值利用它可以達到建立泛型指令碼的目的利用它可以達到和使用者互動，故在SQL *Plus中又稱互動式命令 替換變數的格式式在變數名稱前加一個&，以便在運行SQL命令時提示使用者輸入替換資料，然後按輸入資料運行SQL命令文法：（1）& ：“&變數名”eg：&name;生命週期：單次引用中，不需要聲明，如果替換字元或日期類型，最好用單引號擴起使用範圍：where、order by、列運算式、表名、整個SELECT 語句中  www.2cto.com  （2）&& ：“&&變數名”eg：&&name;生命週期：整個會話（session串連）,不需要聲明 （3）define ：“define 變數名=變數值”eg：DEFINE a = clark;生命週期：整個會話，預先聲明，使用時用&引用聲明的變數define variable=使用者建立的CHAR類型的值：define 變數名=值；define 變數名：查看變數命令。 undefine 變數名：清除變數define：查看在當前會話中所有的替換變數和它們的值 （4）accept 生命週期：整個會話預先聲明，可以客戶化提示資訊，使用時用&引用聲明的變數。定義：accept 變數名name number/char/date prompt ‘提示資訊內容‘即：ACC[EPT] variable [NUM[BER] | CHAR | DATE] [FOR[MAT] format] [DEF[AULT] default] [PROMPT text | NOPR[OMPT]] [HIDE]解釋：PROMPT命令：用於輸出提示使用者的資訊，以便使使用者瞭解指令檔的功能和運行情況PAUSE命令：用於暫停指令檔的運行HIDE選項：用於隱藏使用者的輸入，使別人不可見，安全這條命令的意思是：當plsql程式段執行到變數name的時候，此時需要使用者的互動才能繼續執行下去，plsql程式段會顯示“提示資訊內容”讓使用者輸入相關資訊（如果指定hide選項，那麼在接下去使用者輸入的東西將被用星號顯示出來增加安全，有點像輸入密碼），使用者輸入的內容被接收到並且把它付給name，關於在“提示資訊內容”下使用者輸入的內容的類型，plsql程式段開發人員來通過number/char/date指定，變數name得到正確的值以後，繼續執行相關下面的程式！例：accept a char prompt ‘請輸入員工的僱傭時間(yyyy-mm-dd):‘ hide 例：accept a char prompt ‘input a:‘ hide  www.2cto.com  set verify on/off;  #verify：是否給出原值及新值提示。 具體請參看下面的例子：plsql程式1：[sql]declare     v_sal number(6,2);     v_ename emp.ename%type:=‘&ename‘;  begin     select sal into v_sal from emp  where lower(ename)=lower(v_ename);  if v_sal<2000 then     update emp set sal=v_sal + 200        where lower(ename)=lower(v_ename);  end if;  end;    /  plsql程式2：[sql]declare     v_sal number(6,2);     v_ename emp.ename%type:=‘&&ename‘;  begin     select sal into v_sal from emp  where lower(ename)=lower(v_ename);  if v_sal<2000 then     update emp set sal=v_sal + 200        where lower(ename)=lower(v_ename);  end if;    www.2cto.com  end;    /  secureCRT的一個會話中先執行程式2，再次執行程式1，會發現直接PL/SQL procedure successfully completed. 而不讓我輸入ename，將set verify off也不行 另一個開啟會話 將set verify off後，每次執行程式1都會讓你輸入ename。這就是在前面一個會話執行程式2的時候已經將ename，儲存為了會話的變數，而不是plsql程式的變數。另外一個案例完整的accept例子CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,                  ENAME VARCHAR2(10),                  JOB VARCHAR2(9),                  MGR NUMBER(4),                  HIREDATE DATE,                  SAL NUMBER(7, 2),                  COMM NUMBER(7, 2),                  DEPTNO NUMBER(2));INSERT INTO EMP VALUES (7369, ‘SMITH‘, ‘CLERK‘,    7902, TO_DATE(‘23-12-2013‘, ‘DD-MM-YYYY‘), 800, NULL, 20);INSERT INTO EMP VALUES (7499, ‘ALLEN‘, ‘SALESMAN‘, 7698, TO_DATE(‘20-02-1981‘, ‘DD-MM-YYYY‘), 1600, 300, 30);INSERT INTO EMP VALUES (7521, ‘WARD‘,  ‘SALESMAN‘, 7698, TO_DATE(‘22-02-1981‘, ‘DD-MM-YYYY‘), 1250, 500, 30);INSERT INTO EMP VALUES (7566, ‘JONES‘, ‘MANAGER‘,  7839, TO_DATE(‘22-04-1981‘,  ‘DD-MM-YYYY‘), 2975, NULL, 20);INSERT INTO EMP VALUES (7654, ‘MARTIN‘, ‘SALESMAN‘, 7698,TO_DATE(‘28-09-1981‘, ‘DD-MM-YYYY‘), 1250, 1400, 30);INSERT INTO EMP VALUES (7698, ‘BLAKE‘, ‘MANAGER‘, 7839,TO_DATE(‘01-03-1981‘, ‘DD-MM-YYYY‘), 2850, NULL, 30);INSERT INTO EMP VALUES (7782, ‘CLARK‘, ‘MANAGER‘, 7839,TO_DATE(‘09-05-1981‘, ‘DD-MM-YYYY‘), 2450, NULL, 10);INSERT INTO EMP VALUES (7788, ‘SCOTT‘, ‘ANALYST‘, 7566,TO_DATE(‘09-12-1982‘, ‘DD-MM-YYYY‘), 3000, NULL, 20);INSERT INTO EMP VALUES (7839, ‘KING‘, ‘PRESIDENT‘, NULL,TO_DATE(‘17-11-1981‘, ‘DD-MM-YYYY‘), 5000, NULL, 10);INSERT INTO EMP VALUES (7844, ‘TURNER‘, ‘SALESMAN‘, 7698,TO_DATE(‘08-09-1981‘, ‘DD-MM-YYYY‘), 1500, 0, 30);INSERT INTO EMP VALUES (7876, ‘ADAMS‘, ‘CLERK‘, 7788,TO_DATE(‘12-06-1983‘, ‘DD-MM-YYYY‘), 1100, NULL, 20);INSERT INTO EMP VALUES (7900, ‘JAMES‘, ‘CLERK‘, 7698,TO_DATE(‘13-12-1981‘, ‘DD-MM-YYYY‘), 950, NULL, 30);INSERT INTO EMP VALUES (7902, ‘FORD‘, ‘ANALYST‘, 7566,TO_DATE(‘13-12-1981‘, ‘DD-MM-YYYY‘), 3000, NULL, 20);INSERT INTO EMP VALUES (7934, ‘MILLER‘, ‘CLERK‘, 7782,TO_DATE(‘23-03-1982‘, ‘DD-MM-YYYY‘), 1300, NULL, 10);prompt C R E A T E   N E W   E M P L O Y E E   R E C O R Dpromptprompt Enter the employee‘s information:promptaccept l_ename char format a10 prompt ‘名字: ‘accept l_empno number format ‘9999‘ prompt ‘編號 #: ‘accept l_sal number format ‘99999.99‘ prompt ‘Salary [1000]: ‘ default ‘1000.00‘accept l_comm number format ‘99999.99‘ prompt ‘Commission % [0]: ‘ default ‘0‘accept l_hired date format ‘mm/dd/yyyy‘ prompt ‘Hire date (mm/dd/yyyy): ‘prompt List of available jobs:select distinct job  from emp order by job/accept l_job char format a9 prompt ‘Job: ‘prompt List of managers and employee numbers:select empno, ename  from emp order by ename/accept l_mgr number format ‘9999‘ prompt ‘Manager‘‘s Employee #: ‘prompt List of department numbers and names:select deptno, dname  from dept order by deptno/accept l_dept number format ‘99‘ prompt ‘Department #: ‘insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)values (&l_empno, ‘&l_ename‘, ‘&l_job‘, &l_mgr,        to_date(‘&l_hired‘,‘mm/dd/yyyy‘), &l_sal, &l_comm, &l_dept)/select * from emp where empno=&l_empno/drop table emp;

oracle中關於替代變數,accpt，綁定變數，字元變數