oracle中關(guān)于替代變量,accpt，綁定變量，字符變量

此文檔介紹兩個(gè)事情，一個(gè)是替代變量，另一個(gè)就是了解一下硬解析和軟解析對(duì)于變量來說declare定義的好還是variable定義的好
在oracle 中，對(duì)于一個(gè)提交的sql語句,存在兩種可選的解析過程, 一種叫做硬解析,一種叫做軟解析.一個(gè)硬解析需要經(jīng)解析,制定執(zhí)行路徑,優(yōu)化訪問計(jì)劃等許多的步驟.硬解釋不僅僅耗費(fèi)大量的cpu，更重要的是會(huì)占據(jù)重要的們閂（latch）資源，嚴(yán)重的影響系統(tǒng)的規(guī)模的擴(kuò)大（即限制了系統(tǒng)的并發(fā)行），而且引起的問題不能通過增加內(nèi)存條和cpu的數(shù)量來解決。之所以這樣是因?yàn)殚T閂是為了順序訪問以及修改一些內(nèi)存區(qū)域而設(shè)置的，這些內(nèi)存區(qū)域是不能被同時(shí)修改。當(dāng)一個(gè)sql語句提交后，oracle會(huì)首先檢查一下共享緩沖池（shared pool）里有沒有與之完全相同的語句，如果有的話只須執(zhí)行軟分析即可，否則就得進(jìn)行硬分析。
 而唯一使得oracle 能夠重復(fù)利用執(zhí)行計(jì)劃的方法就是采用綁定變量。綁定變量的實(shí)質(zhì)就是用于替代sql語句中的常量的替代變量。綁定變量能夠使得每次提交的sql語句都完全一樣。
 連接
 前兩天看到有人在pub上問在sqlplus中通過define和variable定義的變量的區(qū)別。其實(shí)define定義的我

理解不是變量而是字符常量，通過define定義之后，在通過&或者&&引用的時(shí)候不需要輸入了，僅此而已。

oracle在執(zhí)行的時(shí)候自動(dòng)用值進(jìn)行了替換；而variable定義的是綁定變量,上面已經(jīng)提到。 



綁定變量引用的時(shí)候用":" ,替代變量引用的時(shí)候用"&";
綁定變量初始化 exec :num1:=2,替代變量默認(rèn)類型為char 

替換變量（僅用于SQL *Plus或者用于原理和SQL *Plus相同的開發(fā)工具）：
臨時(shí)存儲(chǔ)值
利用它可以達(dá)到創(chuàng)建通用腳本的目的
利用它可以達(dá)到和用戶交互，故在SQL *Plus中又稱交互式命令
 
替換變量的格式式在變量名稱前加一個(gè)&，以便在運(yùn)行SQL命令時(shí)提示用戶輸入替換數(shù)據(jù)，然后按輸入數(shù)據(jù)運(yùn)行SQL命令
語法：
（1）& ：“&變量名”eg：&name;
生命周期：?jiǎn)未我弥?，不需要聲明，如果替換字符或日期類型，最好用單引號(hào)擴(kuò)起
使用范圍：where、order by、列表達(dá)式、表名、整個(gè)SELECT 語句中
  www.2cto.com  
（2）&& ：“&&變量名”eg：&&name;
生命周期：整個(gè)會(huì)話（session連接）,不需要聲明
 
（3）define ：“define 變量名=變量值”eg：DEFINE a = clark;
生命周期：整個(gè)會(huì)話，預(yù)先聲明，使用時(shí)用&引用聲明的變量
define variable=用戶創(chuàng)建的CHAR類型的值：define 變量名=值；
define 變量名：查看變量命令。 
undefine 變量名：清除變量
define：查看在當(dāng)前會(huì)話中所有的替換變量和它們的值
 
（4）accept
 
生命周期：整個(gè)會(huì)話
預(yù)先聲明，可以客戶化提示信息，使用時(shí)用&引用聲明的變量。
定義：
accept 變量名name number/char/date prompt '提示信息內(nèi)容'即：ACC[EPT] variable 
[NUM[BER] | CHAR | DATE] [FOR[MAT] format] [DEF[AULT] default] [PROMPT 
text | NOPR[OMPT]] [HIDE]
解釋：
PROMPT命令：用于輸出提示用戶的信息，以便使用戶了解腳本文件的功能和運(yùn)行情況
PAUSE命令：用于暫停腳本文件的運(yùn)行
HIDE選項(xiàng)：用于隱藏用戶的輸入，使別人不可見，安全
這條命令的意思是：當(dāng)plsql程序段執(zhí)行到變量name的時(shí)候，此時(shí)需要用戶的交互才能繼續(xù)執(zhí)行下去，plsql程序段會(huì)顯示“提示信息內(nèi)容”讓用戶輸入相關(guān)信息（如果指定hide選項(xiàng)，那么在接下去用戶輸入的東西將被用星號(hào)顯示出來增加安全，有點(diǎn)像輸入密碼），用戶輸入的內(nèi)容被接收到并且把它付給name，關(guān)于在“提示信息內(nèi)容”下用戶輸入的內(nèi)容的類型，plsql程序段開發(fā)人員來通過number/char/date指定，變量name得到正確的值以后，繼續(xù)執(zhí)行相關(guān)下面的程序！
例：accept a char prompt '請(qǐng)輸入員工的雇傭時(shí)間(yyyy-mm-dd):' hide 
例：accept a char prompt 'input a:' hide
  www.2cto.com  
set verify on/off;  #verify：是否給出原值及新值提示。
 
具體請(qǐng)參看下面的例子：
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的一個(gè)會(huì)話中先執(zhí)行程序2，再次執(zhí)行程序1，會(huì)發(fā)現(xiàn)直接PL/SQL procedure successfully completed.
 而不讓我輸入ename，將set verify off也不行
 另一個(gè)打開會(huì)話 將set verify off后，每次執(zhí)行程序1都會(huì)讓你輸入ename。
這就是在前面一個(gè)會(huì)話執(zhí)行程序2的時(shí)候已經(jīng)將ename，保存為了會(huì)話的變量，而不是plsql程序的變量。


另外一個(gè)案例完整的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 D
prompt
prompt Enter the employee's information:
prompt
accept l_ename char format a10 prompt '名字: '
accept l_empno number format '9999' prompt '編號(hào) #: '
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;

標(biāo)題名稱：oracle中關(guān)于替代變量,accpt，綁定變量，字符變量
分享地址：http://muchs.cn/article26/jehgcg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供電子商務(wù)、企業(yè)網(wǎng)站制作、營(yíng)銷型網(wǎng)站建設(shè)、域名注冊(cè)、微信公眾號(hào)、品牌網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)