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

T¹o ®Þnh d¹ng ®Çu ra cho SQL*Plus Néi dung T¹o c¸c query víi tham sè ®Çu vµo Söa ch÷a m«i tr­êng SQL* Plus theo ý muèn T¹o ®Çu ra theo ý muèn T¹o vµ thùc thi script file CÊt thay ®æi vÒ m«i tr­êng B¸o c¸o t­¬ng t¸c I want to input query values at runtime. ...sal = ? … … deptno = ? … .. ename = ? ... User BiÕn thay thÕ Sö dông biÕn thay thÕ ®Ó l­u gi÷ t¹m gi¸ trÞ biÕn: Single ampersand (&) Double ampersand (&&) DEFINE vµ ACCEPT Göi biÕn tíi c©u lÖnh SQL Tù ®éng thay ®æi headers vµ footers. Sö dông biÕn thay thÕ & Sö dông biÕn víi tiÒn tè &: 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 c©u lÖnh SET VERIFY HiÓn thÞ gi¸ trÞ text cña dßng lÖnh tr­íc vµ sau khi thay thÕ biÕ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 ... BiÕn thay thÕ d÷ liÖu kiÓu date vµ character Sö dông dÊu nh¸y ®¬n cho d÷ liÖu date vµ character 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Ø ®Þnh tªn cét, biÓu thøc vµ text Sö dông biÕn thay thÕ trong tr­êng hîp §iÒu kiÖn WHERE MÖnh ®Ò ORDER BY M« t¶ cét Tªn b¶ng Cho toµn bé c©u lÖnh SELECT ChØ ®Þnh tªn cét, biÓu thøc vµ text 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 biÕn thay thÕ && Sö dông kÝ hiÖu (&&) nÕu muèn sö dông l¹i gi¸ trÞ cña biÕn 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. §Þnh nghÜa biÕn ng­êi dïng Cã thÓ ®Þnh nghÜa tr­íc c¸c biÕn b»ng: DEFINE: T¹o biÕn ng­êi dïng kiÓu VARCHAR2 ACCEPT: §äc gi¸ trÞ vµo cña ng­êi dïng vµ ®¨ vµo biÕn ®Þnh nghÜa tr­íc NÕu muèn ®Þnh nghÜa biÕn bao gåm c¶ kho¶ng trèng, ph¶i ®ãng gi¸ trÞ ®ã víi dÊu nh¸y ®¬n C©u lÖnh ACCEPT T¹o vµ customize dßng nh¾c user vµo d÷ liÖu §Þnh nghÜa b¾t buéc mét biÕn kiÓu date hay character Èn gi¸ trÞ vµo cña ng­êi dïng v× lÝ do b¶o mËt ACCEPT variable [datatype] [FORMAT format] [PROMPT text] [HIDE] Sö dông c©u 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 C©u lÖnh DEFINE vµ UNDEFINE Mét biÕn vÉn ®­îc ®Þnh nghÜa tíi khi: Sö dông lÖnh UNDEFINE Tho¸t khái SQL*Plus Cã thÓ kiÓm tra thay ®æi víi lÖnh DEFINE §Ó ®Þnh nghÜa biÕn cho mäi session sö dông file login.sql ®Ó biÕn t¹o ra vµo thêi ®iÓm khëi t¹o. Sö dông c©u lÖnh DEFINE T¹o biÕn l­u gi÷ tªn phßng DEFINE DEPTNAME = "sales" (CHAR) SQL> DEFINE deptname = sales SQL> DEFINE deptname SQL> SELECT * 2 FROM dept 3 WHERE dname = UPPER('&deptname'); Customizing biÕn m«i tr­êng SQL*Plus Sö dông c©u lÖnh SET ®Ó ®iÒu khiÓn session hiÖn t¹i KiÓm tra c¸c biÕn m«i tr­êng ®· khëi t¹o b»ng lÖnh SHOW SQL> SET ECHO ON SQL> SHOW ECHO echo ON SET system_variable value C¸c biÕn cña c©u lÖnh SET ARRAYSIZE {20 | n} COLSEP {_ | text} FEEDBACK {6 | n |OFF | ON} HEADING {OFF | ON} LINESIZE {80 | n} LONG {80 | n} PAGESIZE {24 | n} PAUSE {OFF | ON | text} TERMOUT {OFF | ON} CÊt Customizations trong file login.sql TÖp login.sql chøa c©u lÖnh SET vµ c¸c c©u lÖnh SQL sÏ ch¹y vµo lóc ®¨ng nhËp Cã thÓ söa tÖp login.sql ®Ó thªm lÖnh SET vµo tÖp login C©u lÖnh ®Þnh d¹ng SQL*Plus COLUMN [column option] TTITLE [text | OFF | ON] BTITLE [text | OFF | ON] BREAK [ON report_element] C©u lÖnh ®èi víi cét §iÒu khiÓn sù hiÓn thÞ cña cét CLE[AR]: Xãa ®Þnh d¹ng cét FOR[MAT] format: Thay ®æi ®Þnh d¹ng cét HEA[DING] text: Khëi t¹o tiªu ®Ò cét JUS[TIFY] {align}: C¨n lÒ cét COL[UMN] [{column|alias} [option]] Sö dông c©u lÖnh COLUMN T¹o ®Çu ®Ò cét . HiÓn thÞ khëi t¹o cho cét Xãa khëi t¹o cho cét COLUMN ename HEADING 'Employee|Name' FORMAT A15 COLUMN sal JUSTIFY LEFT FORMAT $99,990.00 COLUMN mgr FORMAT 999999999 NULL 'No manager' COLUMN ename COLUMN ename CLEAR KiÓu ®Þnh d¹ng cét Result N/A 1234 01234 $1234 L1234 1234.00 1,234 Example N/A 999999 099999 $9999 L9999 9999.99 9,999 Element An 9 0 $ L . , Description Sets a display width of n Single zero-suppression digit Enforces leading zero Floating dollar sign Local currency Position of decimal point Thousand separator Sö dông c©u lÖnh TTITLE vµ BTITLE HiÓn thÞ headers vµ footers. TTI[TLE] [text|OFF|ON] SQL> TTITLE 'Salary|Report' Khëi t¹o ®Çu ®Ò cña b¸o c¸o Khëi t¹o ch©n cña b¸o c¸o SQL> BTITLE 'Confidential' Tæng kÕt Sö dông c¸c biÕn thay thÕ ®Ó l­u gi¸ trÞ t¹m thêi Sö dông lÖnh SET ®Ó ®iÒu khiÓn m«i tr­êng SQL*Plus Sñ dông lÖnh COLUMN ®Ó ®Þnh d¹ng cét hiÓn thÞ Sö dông TTITLE vµ BTITLE hiÓn thÞ headers vµ footers.