Học Oracle cơ bản (Bài 8-Variables)

Queries víi c¸c biÕn thay thÕ Môc ®Ých Sau bµi häc nµy, b¹n cã thÓ: T¹o c¸c query cã biÕn vµo X¸c ®Þnh c¸c biÕn ®ang cã T­¬ng t¸c víi ng­êi dïng T«i muèn ®­a d÷ liÖu vµo lóc thùc hiÖn lÖnh query ...sal = ? … … deptno = ? … .. ename = ? ... Ng­êi dïng BiÕn thay thÕ Sö dông c¸c biÕn cña SQL*Plus ®Ó l­u c¸c gi¸ trÞ t¹m thêi Single ampersand (&) Double ampersand (&&) C¸c lÖnh DEFINE vµ ACCEPT ChuyÓn c¸c gi¸ trÞ biÕn gi÷a c¸c lÖnh SQL. Sö dông & Sö dông biÕn cã (&) ë tr­íc ®Ó nh¾c ng­êi dïng vÒ gi¸ trÞ cña biÕn. SQL> SELECT empno, ename, sal, deptno 2 FROM emp 3 WHERE empno = &employee_num; Enter value for employee_num: 7369 EMPNO ENAME SAL DEPTNO --------- ---------- --------- --------- 7369 SMITH 800 20 Sö dông lÖnh SET VERIFY Thay ®æi viÖc hiÓn thÞ khi thay thÕ biÕn víi gi¸ trÞ cña nã SQL> SET VERIFY ON SQL> SELECT empno, ename, sal, deptno 2 FROM emp 3 WHERE empno = &employee_num; Enter value for employee_num: 7369 old 3: WHERE empno = &employee_num new 3: WHERE empno = 7369 ... C¸c gi¸ trÞ ngµy vµ ký tù víi biÕn thay thÕ Sö dông dÊu ngoÆc ®¬n cao cho gi¸ trÞ ngµy vµ ký tù SQL> SELECT ename, deptno, sal*12 2 FROM emp 3 WHERE job='&job_title'; Enter value for job_title: ANALYST ENAME DEPTNO SAL*12 ---------- --------- --------- SCOTT 20 36000 FORD 20 36000 ChØ ra tªn cét, biÓu thøc, … khi thùc hiÖn lÖnh. Sö dông c¸c biÕn thay thÕ trong: §iÒu kiÖn WHERE MÖnh ®Ò ORDER BY BiÓu thøc Tªn b¶ng ChØ tªn cét, biÓu thøc, khi thùc hiÖn SELECT SQL> SELECT empno, ename, job, &column_name 2 FROM emp 3 WHERE &condition 4 ORDER BY &order_column; Enter value for column_name: sal Enter value for condition: sal>=3000 Enter value for order_column: ename EMPNO ENAME JOB SAL --------- ---------- --------- --------- 7902 FORD ANALYST 3000 7839 KING PRESIDENT 5000 7788 SCOTT ANALYST 3000 Sö dông (&&) ®Ó thay thÕ Sö dông (&&) nÕu b¹n muèn sö dông l¹i gi¸ trÞ biÕn mµ kh«ng muèn gâ l¹i gi¸ trÞ. SQL> SELECT empno, ename, job, &&column_name 2 FROM emp 3 ORDER BY &column_name; Enter value for column_name: deptno EMPNO ENAME JOB DEPTNO --------- ---------- --------- --------- 7839 KING PRESIDENT 10 7782 CLARK MANAGER 10 7934 MILLER CLERK 10 ... 14 rows selected. LÖnh DEFINE T¹o biÕn tr­íc nhê sö dông: DEFINE: T¹o mét biÕn tr­íc ACCEPT: §äc d÷ liÖu vµo vµ l­u trong mét biÕn X¸c ®Þnh mét biÕn tr­íc cã kho¶ng trèng, b¹n ph¶i ®Ó vµo trong hai dÊu ngoÆc ®¬n cao khi sö dông lÖnh DEFINE Using the DEFINE Command Create a variable to hold the department name. DEFINE DEPTNAME = "sales" (CHAR) Sö dông biÕn nh­ mäi biÕn kh¸c SQL> DEFINE deptname = sales SQL> DEFINE deptname SQL> SELECT * 2 FROM dept 3 WHERE dname = UPPER('&deptname'); LÖnh ACCEPT T¹o c©u nh¾c theo ý b¹n khi gâ gi¸ trÞ vµo. X¸c ®inh kiÓu biÕn Che th«ng tin gâ vµo ®Ó an toµn ACCEPT variable [datatype] [FORMAT format] [PROMPT text] [HIDE] Sö dông lÖnh ACCEPT ACCEPT dept PROMPT 'Provide the department name: ' SELECT * FROM dept WHERE dname = UPPER('&dept') / Provide the department name: Sales DEPTNO DNAME LOC --------- -------------- ------------- 30 SALES CHICAGO LÖnh DEFINE vµ UNDEFINE Mét biÕn cßn tån t¹i cho ®Õn khi: Sö dông lÖnh UNDEFINE ®Ó xo¸ nã. Ra khái SQL*Plus KiÓm tra biÕn b»ng lÖnh DEFINE Thay ®æi tÖp login.sql ®Ó c¸c biÕn ®­îc t¹o mçi khi b¾t ®Çu phiªn lµm viÖc. Tãm t¾t Sö dông &, && ®Ó thay thÕ gi¸ trÞ khi thùc hiÖn lÖnh SQL Sö dông DEFINE vµ ACCEPT ®Ó t¹o biÕn thay thÕ Sö dông UNDEFINE ®Ó bá biÕn Sö dông tÖp LOGIN.SQL ®Ó t¹o biÕn khi b¾t ®Çu phiªn lµm viÖc.