課程 一 PL/SQL 基本查詢(xún)與排序
本課重點(diǎn)：

   1、寫(xiě)SELECT語(yǔ)句進(jìn)行數(shù)據(jù)庫(kù)查詢(xún)

   2、進(jìn)行數(shù)學(xué)運(yùn)算

   3、處理空值

   4、使用別名ALIASES

   5、連接列

   6、在SQL PLUS中編輯緩沖，修改SQL SCRIPTS

   7、ORDER BY進(jìn)行排序輸出。

   8、使用WHERE 字段。

   一、寫(xiě)SQL 命令：

     不區(qū)分大小寫(xiě)。

     SQL 語(yǔ)句用數(shù)字分行，在SQL PLUS中被稱(chēng)為緩沖區(qū)。

     最后以；或 / 結(jié)束語(yǔ)句。

     也可以用RUN來(lái)執(zhí)行語(yǔ)句

   二、例1：SQL> SELECT dept_id, last_name, manager_id

            2 FROM s_emp;

         2：SQL> SELECT last_name, salary * 12, commission_pct

            2 FROM s_emp;

            對(duì)于數(shù)值或日期型的字段，可以進(jìn)行相應(yīng)的四則運(yùn)算，優(yōu)先級(jí)與標(biāo)準(zhǔn)的高級(jí)語(yǔ)言相同。

            SQL> SELECT last_name, salary, 12 * (salary + 100)

            2 FROM s_emp;

    三、列的別名ALIASES：

        計(jì)算的時(shí)候特別有用；

        緊跟著列名，或在列名與別名之間加“AS”；

        如果別名中含有SPACE，特殊字符，或大小寫(xiě)，要用雙引號(hào)引起。

        例(因字體原因，讀者請(qǐng)記住：引號(hào)為英文雙引號(hào)Double Quotation)：

        SQL> SELECT last_name, salary,

          2 12 * (salary + 100) ”Annual Salary”

          3 FROM s_emp;

    四、連接符號(hào)：||

        連接不同的列或連接字符串

        使結(jié)果成為一個(gè)有意義的短語(yǔ)：

        SQL> SELECT first_name || ’ ’ || last_name

          2 || ’, ’|| title ”Employees”

          3 FROM s_emp;

    五、管理NULL值：

        SQL> SELECT last_name, title,

           2 salary * NVL(commission_pct,0)/100 COMM

           3 FROM s_emp;

           此函數(shù)使NULL轉(zhuǎn)化為有意義的一個(gè)值，相當(dāng)于替換NULL。

    六、SQL PLUS的基本內(nèi)容，請(qǐng)參考<SQL PLUS 簡(jiǎn)單實(shí)用精髓篇 >

    七、ORDER BY 操作：

        與其他SQL92標(biāo)準(zhǔn)數(shù)據(jù)庫(kù)相似，排序如：

        SELECT expr

         FROM table

           [ORDER BY {column,expr} [ASC|DESC]];

    從Oracle7 release 7.0.16開(kāi)始，ORDER BY 可以用別名。

    另：通過(guò)位置判斷排序：

    SQL> SELECT last_name, salary*12

       2 FROM s_emp

         3 ORDER BY 2;

      這樣就避免了再寫(xiě)一次很長(zhǎng)的表達(dá)式。

    另：多列排序：

    SQL> SELECT last name, dept_id, salary

        2 FROM s_emp

         3 ORDER BY dept_id, salary DESC;

    八、限制選取行：

          SELECT expr

           FROM table

             [WHERE condition(s)]

             [ORDER BY expr];

       例1：

       SQL> SELECT first_name, last_name, start_date

           2 FROM s_emp

           3 WHERE start_date BETWEEN ’09-may-91’

           4 AND ’17-jun-91’;

       例2：

       SQL> SELECT last_name

           2 FROM s_emp

            3 WHERE last_name LIKE ’_a%’;  //顯示所有第二個(gè)字母為 a的last_name

       例3：

       如果有列為NULL

       SQL> SELECT id, name, credit_rating

           2 FROM s_customer

             3 WHERE sales_rep_id IS NULL;

       優(yōu)先級(jí)：

        Order        Evaluated Operator

          1   All comparison operators  (=, <>, >, >=, <, <=, IN, LIKE, IS NULL, BETWEEN)

          2    AND

          3      OR

   

      總結(jié)：我們今天主要學(xué)習(xí)了如何進(jìn)行查詢(xún)SELECT操作，具體的組合查詢(xún)與子查詢(xún)將在以后的課堂中學(xué)習(xí)，同時(shí)希望大家可以工作、學(xué)習(xí)中多多摸索，實(shí)踐!

課程 二 PL/SQL PL/SQL 查詢(xún)行函數(shù)

本課重點(diǎn)：
   1、掌握各種在PL/SQL中可用的ROW函數(shù)

   2、使用這些函數(shù)的基本概念

   3、SELECT語(yǔ)句中使用函數(shù)

   4、使用轉(zhuǎn)換函數(shù)

   注意：以下實(shí)例中標(biāo)點(diǎn)均為英文半角

   一、FUNCTION的作用：

       進(jìn)行數(shù)據(jù)計(jì)算，修改獨(dú)立的數(shù)據(jù)，處理一組記錄的輸出，不同日期顯示格式，進(jìn)行數(shù)據(jù)類(lèi)型轉(zhuǎn)換

       函數(shù)分為：?jiǎn)为?dú)函數(shù)(ROW)和分組函數(shù)

       注意：可以嵌套、可以在SELECT, WHERE, 和 ORDER BY中出現(xiàn)。

       語(yǔ)法：function_name (column|expression, [arg1, arg2,...])

   二、字符型函數(shù)

       1、LOWER 轉(zhuǎn)小寫(xiě)

       2、UPPER

       3、INITCAP  首字母大寫(xiě)

       4、CONCAT  連接字符，相當(dāng)于 ||

       5、SUBSTR  SUBSTR(column|expression,m[,n])

       6、LENGTH   返回字符串的長(zhǎng)度

       7、NVL    轉(zhuǎn)換空值

       其中，1、2經(jīng)常用來(lái)排雜，也就是排除插入值的大小寫(xiě)混用的干擾，如：

        SQL> SELECT first_name, last_name

          2 FROM s_emp

          3 WHERE UPPER(last_name) = ’PATEL’;

       FIRST_NAME LAST_NAME

      -------------------- --------------------

         Vikram    Patel

         Radha     Patel

    三、數(shù)學(xué)運(yùn)算函數(shù)

       1、ROUND

          四舍五入：ROUND(45.923，2)  = 45.92

          ROUND(45.923，0)  = 46

          ROUND(45.923，-1)  = 50

       2、TRUNC

          截取函數(shù)

          TRUNC(45.923，2)= 45.92

          TRUNC(45.923)= 45

          TRUNC(45.923，-1)= 40

       3、MOD  余除

          MOD(1600，300)

       實(shí)例：

       SQL> SELECT ROUND(45.923,2), ROUND(45.923,0),

           2 ROUND(45.923,-1)

           3 FROM SYS.DUAL;

    四、ORACLE 日期格式和日期型函數(shù)：

       1、默認(rèn)格式為DD-MON-YY.

       2、SYSDATE是一個(gè)求系統(tǒng)時(shí)間的函數(shù)

       3、DUAL［'dju:el] 是一個(gè)偽表，有人稱(chēng)之為空表，但不確切。

       SQL> SELECT SYSDATE

          2 FROM SYS.DUAL;

       4、日期中應(yīng)用的算術(shù)運(yùn)算符

       例：SQL> SELECT last_name, (SYSDATE-start_date)/7 WEEKS

             2 FROM s_emp

              3 WHERE dept_id = 43;

       DATE+ NUMBER = DATE

       DATE-DATE= NUMBER OF DAYS

       DATE + (NUMBER/24) = 加1小時(shí)

       5、函數(shù)：

        MONTHS_BETWEEN(date1, date2) 月份間隔，可正，可負(fù)，也可是小數(shù)

        ADD_MONTHS(date,n)  加上N個(gè)月，這是一個(gè)整數(shù)，但可以為負(fù)

        NEXT_DAY(date,‘char’) 如：NEXT_DAY (restock_date,’FRIDAY’),從此日起下個(gè)周五。

        ROUND(date[,‘fmt’])

        TRUNC(date[,‘fmt’])

        解釋下面的例子：

        SQL> SELECT id, start_date,

          2 MONTHS_BETWEEN (SYSDATE,start_date) TENURE,

          3 ADD_MONTHS(start_date,6) REVIEW

           4 FROM s_emp

            5 WHERE MONTHS_BETWEEN (SYSDATE,start_date)<48;

        我們看到：  MONTHS_BETWEEN (SYSDATE,start_date)<48，說(shuō)明至今工作未滿(mǎn)一年的員工。

       LAST_DAY (restock_date) 返回本月的最后一天

      SQL> select round(sysdate,'MONTH') from dual

       ROUND(SYSD

         ----------

          01-11月-01

      round(sysdate,'YEAR') = 01-1月 -02   

      ROUND 之后的值比基值大的最小符合值，大家可以用更改系統(tǒng)時(shí)間的方法測(cè)試，以15天為分界線(xiàn)，也是非常形象的四舍五入，而TRUNC恰好相反，是對(duì)現(xiàn)有的日期的截取。

    五、轉(zhuǎn)換函數(shù)：

      1、TO_CHAR

           使一個(gè)數(shù)字或日期轉(zhuǎn)換為CHAR

      2、TO_NUMBER

           把字符轉(zhuǎn)換為NUMBER

      3、TO_DATE

         字符轉(zhuǎn)換為日期

       這幾個(gè)函數(shù)較為簡(jiǎn)單，但要多多實(shí)踐，多看復(fù)雜的實(shí)例。

      SQL> SELECT ID,TO_CHAR(date_ordered,’MM/YY’) ORDERED

         2 FROM s_ord

         3 WHERE sales_rep_id = 11;

      轉(zhuǎn)換時(shí)，要注意正確的缺省格式：

      SELECT TO_DATE('03-MAR-92') CORRECT FROM DUAL；//正確

       SELECT TO_DATE('031092') CORRECT FROM DUAL；//不正確

      SELECT TO_DATE('031095','MMDDYY') ERRORR FROM DUAL

            輸出 3月10日

      SELECT TO_DATE('031095','DDMMYY') ERRORR FROM DUAL

               輸出 10月3日

      4、實(shí)例：

        select to_char(sysdate,'fmDDSPTH "of" MONTH YYYY AM') TODAYS FROM DUAL;

       TODAYS

        --------------------------------

       SIXTEENTH of 11月 2001 下午

       大小寫(xiě)沒(méi)有什么影響，引號(hào)中間的是不參與運(yùn)算。

      實(shí)例 ：

       SELECT ROUND(SALARY*1.25) FROM ONE_TABLE；

      意義：漲25%工資后，去除小數(shù)位。在現(xiàn)實(shí)操作中，很有意義。

      5、混合實(shí)例：

SQL> SELECT last_name, TO_CHAR(start_date,

2 ’fmDD ”of” Month YYYY’) HIREDATE

3 FROM s_emp

4 WHERE start_date LIKE ’%91’;

LAST_NAME HIREDATE

------------ --------------------

Nagayama 17 of June 1991

Urguhart 18 of January 1991

Havel 27 of February 1991

     這里要注意：fmDD 和 fmDDSPTH之間的區(qū)別。

     SQL> SELECT id, total, date_ordered

         2 FROM s_ord

         3 WHERE date_ordered =

         4 TO_DATE(’September 7, 1992’,’Month dd, YYYY’);

   

    六、獨(dú)立的函數(shù)嵌套

        SQL> SELECT CONCAT(UPPER(last_name),

             2 SUBSTR(title,3)) ”Vice Presidents”

             3 FROM s_emp

             4 WHERE title LIKE ’VP%’;

        * 嵌套可以進(jìn)行到任意深度，從內(nèi)向外計(jì)算。

      例：

       SQL> SELECT TO_CHAR(NEXT_DAY(ADD_MONTHS

         2 (date_ordered,6),’FRIDAY’),

         3 ’fmDay, Month ddth, YYYY’)

         4 ”New 6 Month Review”

         5 FROM s_ord

         6 ORDER BY date_ordered;

        

       SQL> SELECT last_name,

         2 NVL(TO_CHAR(manager_id),’No Manager’)

         3 FROM s_emp

         4 WHERE manager_id IS NULL;

       對(duì)于例子，大家重要的理解，并多做測(cè)試，并注意英文版和中文版在日期上的區(qū)別。

       有些教材上的例子，不要盲目的相信其結(jié)果，實(shí)踐后才有發(fā)言權(quán)，希望大家能夠在學(xué)習(xí)的過(guò)程中不要忽略了用，

多想一想為什么實(shí)例要如此設(shè)計(jì)，在何種情況下應(yīng)用此實(shí)例來(lái)解決問(wèn)題。這樣，我們才真正掌握了知識(shí)。

14:41 | 添加評(píng)論 | 閱讀評(píng)論 (6) | 固定鏈接 | 引用通告 (0) | 寫(xiě)入日志 | 計(jì)算機(jī)與 Internet
PL/Sql循序漸進(jìn)全面學(xué)習(xí)教程--Oracle(2)
 課程 三 從多個(gè)表中提取數(shù)據(jù)
                                                                     

本課重點(diǎn)：
1、SELECT FROM 多個(gè)表，使用等連接或非等連接
2、使用外連接OUTER JOIN
3、使用自連接
注意：以下實(shí)例中標(biāo)點(diǎn)均為英文半角
一、連接的概念：

是指一個(gè)從多個(gè)表中的數(shù)據(jù)進(jìn)行的查詢(xún)。連接一般使用表的主鍵和外鍵。
連接類(lèi)型：
等連接、不等連接、外連接、自連接
二、Cartesian product ：
指的是當(dāng)JOIN條件被省略或無(wú)效時(shí)，所有表的行(交叉)都被SELECT出來(lái)的現(xiàn)象。
Cartesian product可以產(chǎn)生大量的記錄，除非是你有意如此，否則應(yīng)該加上某種條件限制。
SQL> SELECT name, last_name
2 FROM s_dept, s_emp;
300 rows selected. 其中一個(gè)表12行，一個(gè)表25行。

三、簡(jiǎn)單連接查詢(xún)：
SELECT table.column, table.column...
FROM table1, table2
WHERE table1.column1 = table2.column2;

如：SQL> SELECT s_emp.last_name, s_emp.dept_id,
2 s_dept.name
3 FROM s_emp, s_dept
4 WHERE s_emp.dept_id = s_dept.id;
注意：表前綴的重要性：
SQL> SELECT s_dept.id ”Department ID”,
2 s_region.id ”Region ID”,
3 s_region.name ”Region Name”
4 FROM s_dept, s_region
5 WHERE s_dept.region_id = s_region.id;
在WHERE 段中，如果沒(méi)有前綴，兩個(gè)表中都有ID字段，就顯得的模棱兩可，AMBIGUOUS。
這在實(shí)際中應(yīng)該盡量避免。
WHERE 字段中，還可以有其他的連接條件，如在上例中，加上：
INITCAP(s_dept.last_name) = ’Menchu’;
再如：WHERE s_emp.dept_id = s_dept.id AND s_dept.region_id = s_region.id AND s_emp.commission_pct > 0;

四、表別名ALIAS：
1、使用別名進(jìn)行多表查詢(xún) 。
2、僅在這個(gè)查詢(xún)中生效，一旦用了表別名，就不能再用表的原有的名字進(jìn)行連接。
實(shí)例：
SQL> SELECT c.name ”Customer Name”,
2 c.region_id ”Region ID”,
3 r.name ”Region Name”
4 FROM s_customer c, s_region r
5 WHERE c.region_id = r.id;
別名最多可以30個(gè)字符，但當(dāng)然越少越好。最好也能容易識(shí)別。
五、非等連接
非等連接一般用在沒(méi)有明確的等量關(guān)系的兩個(gè)表；
最簡(jiǎn)單的說(shuō)：非等連接就是在連接中沒(méi)有“=”出現(xiàn)的連接。
SQL> SELECT e.ename, e.job, e.sal, s.grade
2 FROM emp e, salgrade s
3 WHERE e.sal BETWEEN s.losal AND s.hisal;
說(shuō)明：Create a non-equijoin to evaluate an employee’s salary grade. The salary 必須在另一個(gè)表中最高和最低之間。
其他操作符<= >= 也可以實(shí)現(xiàn)，但是BETWEEN是非常簡(jiǎn)單實(shí)用的。
BETWEEN ....AND是指閉區(qū)間的，這點(diǎn)要注意 ，請(qǐng)大家測(cè)試。
六、外連接
語(yǔ)法結(jié)構(gòu)：SELECT table.column, table.column
FROM table1, table2
WHERE table1.column = table2.column(+);
實(shí)例：
SQL> SELECT e.last_name, e.id, c.name
2 FROM s_emp e, s_customer c
3 WHERE e.id (+) = c.sales_rep_id
4 ORDER BY e.id;
顯示.....，即使有的客戶(hù)沒(méi)有銷(xiāo)售代表。
* 可以理解為有+號(hào)的一邊出現(xiàn)了NULL，也可以做為合法的條件。
外連接的限制：
1、外連接符只能出現(xiàn)在信息缺少的那邊。
2、在條件中，不能用 IN 或者 OR做連接符。
七、自連接
同一個(gè)表中使用連接符進(jìn)行查詢(xún)；
FROM 的后面用同一個(gè)表的兩個(gè)別名。
實(shí)例：
SQL> SELECT worker.last_name||’ works for ’||
2 manager.last_name
3 FROM s_emp worker, s_emp manager
4 WHERE worker.manager_id = manager.id;
意味著：一個(gè)員工的經(jīng)理ID匹配了經(jīng)理的員工號(hào)，但這個(gè)像繞口令的連接方式并不常用。
以后我們會(huì)見(jiàn)到一種 子查詢(xún)：
select last_name from s_emp where salary=(select max(salary) from s_emp)
也可以看作是一種變向的自連接，但通常我們將其

課程 四 組函數(shù)

 

本課重點(diǎn)：
   1、了解可用的組函數(shù)
   2、說(shuō)明每個(gè)組函數(shù)的使用方法
   3、使用GROUP BY
   4、通過(guò)HAVING來(lái)限制返回組
   注意：以下實(shí)例中標(biāo)點(diǎn)均為英文半角
   一、概念：
      組函數(shù)是指按每組返回結(jié)果的函數(shù)。
      組函數(shù)可以出現(xiàn)在SELECT和HAVING 字段中。
      GROUP BY把SELECT 的結(jié)果集分成幾個(gè)小組。
      HAVING 來(lái)限制返回組，對(duì)RESULT SET而言。
   二、組函數(shù)：(#號(hào)的函數(shù)不做重點(diǎn))
      1、AVG
      2、COUNT
      3、MAX
      4、MIN
      5、STDDEV  #
      6、SUM
      7、VARIANCE  #
     語(yǔ)法：
   SELECT column, group_function
      FROM table
     [WHERE condition]
      [GROUP BY group_by_expression]
       [HAVING group_condition]
         [ORDER BY column];
     實(shí)例1：一個(gè)混合實(shí)例，說(shuō)明所有問(wèn)題：
     SQL> SELECT AVG(salary), MAX(salary), MIN(salary),
      2 SUM(salary)
      3 FROM s_emp
      4 WHERE UPPER(title) LIKE ’SALES%’;
        AVG(SALARY) MAX(SALARY) MIN(SALARY) SUM(SALARY)
        ----------- ----------- ----------- -----------
         1476       1525          1400        7380
     說(shuō)明：很多函數(shù)，我們?cè)谥v函數(shù)的已經(jīng)向大家介紹過(guò)，但在此為何叫分組函數(shù)呢，主要是因?yàn)樗鼈兛梢耘cGROUP BY來(lái)形成對(duì)不同組的計(jì)算，相當(dāng)于在很多值中進(jìn)行挑選。
     * MIN MAX函數(shù)可以接任何數(shù)據(jù)類(lèi)型。
     如果是MIN(last_name), MAX(last_name)，返回的是什么呢？
     千萬(wàn)記住，不是指LAST_NAME的長(zhǎng)度，而是指在FIRST字母的前后順序，第一個(gè)相同，然后比較第二個(gè)，如：xdopt  > cssingkdkdk  >   adopt  > acccc
      實(shí)例2：
       SQL> SELECT COUNT(commission_pct)
         2 FROM s_emp
         3 WHERE dept_id = 31;
         返回所有非空行個(gè)數(shù)
     三、GROUP BY的應(yīng)用：
      先看一個(gè)簡(jiǎn)單實(shí)例：
     SQL> SELECT credit_rating, COUNT(*) ”# Cust”
       2 FROM s_customer
        3 GROUP BY credit_rating;    
      注意這里別名的應(yīng)用，復(fù)習(xí)一下從前的課程，加了引號(hào)后，就可以用特殊字符，但也僅有三個(gè)：#$_，什么對(duì)象的名字都如此。當(dāng)然空格也是可以的。
      復(fù)雜實(shí)例：
      SQL> SELECT title, SUM(salary) PAYROLL
         2 FROM s_emp
          3 WHERE title NOT LIKE ’VP%’
          4 GROUP BY title
          5 ORDER BY SUM(salary);
       這里要注意一下幾個(gè)CLAUSE的先后次序。
       WHERE在這里主要是做參與分組的記錄的限制。
       **另外，如果要選取出來(lái)一個(gè)不加組函數(shù)的列，如上面的TITLE，就要把這個(gè)列GROUP BY !否則要出錯(cuò)的!信息為：ERROR at line 1:
ORA-00937: not a single-group group function
       理論很簡(jiǎn)單，如果不GROUP BY TITLE，顯示哪一個(gè)呢？這個(gè)在試題中經(jīng)常出現(xiàn)。
       結(jié)論：不加分組函數(shù)修飾的列必定要出現(xiàn)在GROUP BY 里。
       錯(cuò)誤實(shí)例：
      SQL> SELECT dept_id, AVG(salary)
        2 FROM s_emp
        3 WHERE AVG(salary) > 2000
        4 GROUP BY dept_id;
         
WHERE AVG(salary) > 2000
*
ERROR at line 3:
ORA-00934: group function is not allowed here
       應(yīng)在GROUP BY 后面加上HAVING AVG(salary) > 2000;
       因?yàn)槭怯脕?lái)限制組的返回。
     多級(jí)分組實(shí)例：
     SQL> SELECT dept_id, title, COUNT(*)
          2 FROM s_emp
          3 GROUP BY dept_id, title;
       就是先按照DEPT_ID分組，當(dāng)DEPT_ID相同的時(shí)候，再按TITLE分組，而COUNT(*)以合成的組計(jì)數(shù)。
      順序?qū)Y(jié)果有決定性的影響。
      
      總結(jié)：本課我們主要學(xué)習(xí)了分組函數(shù)的使用及如何進(jìn)行分組查詢(xún)，我們可以想像一下，SQL SERVER中有COMPUTE BY，來(lái)進(jìn)行分組總數(shù)的計(jì)算，但在ORACLE中是沒(méi)有的。大家可以建立一個(gè)有多個(gè)列，多個(gè)重復(fù)值的表，然后進(jìn)行各種分組的演示，用得多了，自然明了。
    
 

課程 五 子查詢(xún)

本課重點(diǎn)：
   1、在條件未知的情況下采用嵌套子查詢(xún)
   2、用子查詢(xún)做數(shù)據(jù)處理
   3、子查詢(xún)排序
   
   注意：以下實(shí)例中標(biāo)點(diǎn)均為英文半角
  
   一、概述：
      子查詢(xún)是一種SELECT句式中的高級(jí)特性，就是一個(gè)SELECT語(yǔ)句作為另一個(gè)語(yǔ)句的一個(gè)段。我們可以利用子查詢(xún)來(lái)在WHERE字段中引用另一個(gè)查詢(xún)來(lái)攻取值以補(bǔ)充其無(wú)法事先預(yù)知的子結(jié)果。
      子查詢(xún)可以用在WHERE子句，HAING子句，SELECT或DELETE語(yǔ)句中的FROM 子句。
      注意：1、子查詢(xún)必須在一對(duì)圓括號(hào)里。
            2、比較符號(hào)：>, =, 或者 IN.
            3、子查詢(xún)必須出現(xiàn)在操作符的右邊
            4、子查詢(xún)不能出現(xiàn)在ORDER BY里   (試題中有時(shí)出現(xiàn)找哪行出錯(cuò))
    二、子查詢(xún)的執(zhí)行過(guò)程：
        NESTED QUERY      MAIN QUERY
    SQL> SELECT dept_id                       SQL> SELECT last_name, title
       2 FROM s_emp                            2 FROM s_emp
       3 WHERE UPPER(last_name)=’BIRI’;      3 WHERE dept_id =
      這里 ，每個(gè)查詢(xún)只運(yùn)行一次。當(dāng)然，子查詢(xún)要首先被執(zhí)行，大家設(shè)想一下，如果子查詢(xún)中有一個(gè)以上的人的LASTNAME為BIRI，會(huì)如何？-----會(huì)出錯(cuò)，因?yàn)椴荒苡?來(lái)連接。
      ORA-1427: single-row subquery returns more than
       one row
      以上的查詢(xún)也被稱(chēng)之為 單行子查詢(xún)。
      DELECT子查詢(xún)實(shí)例：
     delete from new_table where cata_time > to_date('19990901','yyyymmdd') and pro_name=(
          select pro_name from new_product where pro_addr in ('bj','sh'))
    三、子查詢(xún)中的GROUP 函數(shù)的應(yīng)用
     實(shí)例 1：
     SQL> SELECT last_name, title, salary
       2 FROM s_emp
       3 WHERE salary <
       4 (SELECT AVG(salary)
       5 FROM s_emp);
     實(shí)例2：
      選擇出工資最高的員工的家庭住址：
     select emp_addr from employees where salary =
          (select max(salary) from employees);
      這是一個(gè)簡(jiǎn)單實(shí)用的例子，可以衍生出很多情況，在實(shí)際應(yīng)用經(jīng)常出現(xiàn)，請(qǐng)大家多多思考。
     實(shí)例3：
      SQL> SELECT dept_id, AVG(salary)
         2 FROM s_emp
         3 GROUP BY dept_id
         4 HAVING AVG(salary) >
         5 (SELECT AVG(salary)
         6 FROM s_emp
         7 WHERE dept_id = 32);
     子查詢(xún)被多次執(zhí)行，因?yàn)樗霈F(xiàn)在HAVING 子句中。
        SQL> SELECT title, AVG(salary)
          2 FROM s_emp
          3 GROUP BY title
          4 HAVING AVG(salary) =
          5 (SELECT MIN(AVG(salary))
          6 FROM s_emp
          7 GROUP BY title);
       對(duì)子查詢(xún)，我們了解這么多在理論上已經(jīng)覆蓋了所有的知識(shí)點(diǎn)，對(duì)于UPDATE 和DELETE的子查詢(xún)，不作為重點(diǎn)，但也要練習(xí)掌握。今天到這，謝謝大家。
    

課程 六 運(yùn)行時(shí)應(yīng)用變量

本課重點(diǎn)：

   1、創(chuàng)建一個(gè)SELECT語(yǔ)句，提示USER在運(yùn)行時(shí)先對(duì)變量賦值。

   2、自動(dòng)定義一系列變量，在SELECT運(yùn)行時(shí)進(jìn)行提取。

   3、在SQL PLUS中用ACCEPT定義變量

   

   注意：以下實(shí)例中標(biāo)點(diǎn)均為英文半角

  

   一、概述：

       變量可以在運(yùn)行時(shí)應(yīng)用，變量可以出現(xiàn)在WHERE 字段，文本串，列名，表名等。

       1、我們這里的運(yùn)行時(shí)，指的是在SQL PLUS中運(yùn)行。

       2、ACCEPT ：讀取用戶(hù)輸入的值并賦值給變量

       3、DEFINE：創(chuàng)建并賦值給一個(gè)變量

       4、在做REPORT時(shí)經(jīng)常使用，比如對(duì)某個(gè)部門(mén)的銷(xiāo)售信息進(jìn)行統(tǒng)計(jì)，部門(mén)名稱(chēng)可以以變量代替。

       SQL PLUS不支持對(duì)輸入數(shù)據(jù)的有效性檢查，因此提示要簡(jiǎn)單且不模棱兩可。

   二、應(yīng)用實(shí)例：

       1、SQL> SELECT id, last_name, salary

            2 FROM s_emp

            3 WHERE dept_id = &department_number;

       2、可以在賦值前后進(jìn)行比較：

       SET VERIFY  ON

        .....

         1*  select * from emp where lastname='&last_name'

      輸入 last_name 的值:  adopt

       原值    1:  select * from emp where lastname='&last_name'

       新值    1:  select * from emp where lastname='adopt'

        ----如果在原語(yǔ)句中沒(méi)有單引號(hào)，那么在輸入值的時(shí)候要手工加上單引號(hào)。一般字符和日期型要在語(yǔ)句中加上單引號(hào)。

       SET VERIFY OFF 之后，原值和新值這兩句消失。這在ORACLE8I中是默認(rèn)為ON。

        3、子句為變量：WHERE &condition;  要注意引號(hào)

    三、DEFINE和ACCEPT的應(yīng)用：

     1、SET ECHO OFF   //使內(nèi)容不 顯示在用戶(hù)界面

        ACCEPT p_dname PROMPT ’Provide the department name: ’

         SELECT d.name, r.id, r.name ”REGION NAME”

          FROM s_dept d, s_region r

          WHERE d.region_id = r.id

       AND UPPER(d.name) LIKE UPPER(’%&p_dname%’)

        /

      SET ECHO ON

        存為文件：l7prompt.SQL

        SQL> START l7prompt

           Provide the department name: sales

       

      2、SQL> DEFINE dname = sales

        SQL> DEFINE dname

           DEFINE dname = ”sales” (CHAR)

          SQL> SELECT name

           2 FROM s_dept

           3 WHERE lower(name) = ’&dname’;

        可以正常執(zhí)行了。

        SQL> DEFINE dname  主要是顯示當(dāng)前的變量是否賦值，值是什么。當(dāng)然，我們可以用UNDEFINEGO 來(lái)使變量恢復(fù)初始，不然它會(huì)一直保持下去。

      3、如果變量在SQL SCRIPT文件中確定 ：可以SQL> START l7param President 來(lái)賦值。

     

     總結(jié)：本課主要針對(duì)較古老的SQLPLUS方法，在REPORT和結(jié)果集生成方面使用變量，達(dá)到方便操作，動(dòng)態(tài)修改的目的。

14:39 | 添加評(píng)論 | 固定鏈接 | 引用通告 (0) | 寫(xiě)入日志 | 計(jì)算機(jī)與 Internet
PL/Sql循序漸進(jìn)全面學(xué)習(xí)教程--Oracle(3)
課程 七   其他數(shù)據(jù)庫(kù)對(duì)象

SEQUENCE

創(chuàng)建實(shí)例：

　

SQL> CREATE SEQUENCE s_dept_id

2 INCREMENT BY 1

3 START WITH 51

4 MAXVALUE 9999999

5 NOCACHE

6 NOCYCLE;

Sequence created.

　　1、NEXTVAL和CURRVAL的用法

　　  只有在INSERT　中，才可以作為子查詢(xún)出現(xiàn)。

　　以下幾個(gè)方面不可用子查詢(xún)：

　　　　SELECT　子句OF　A　VIEW

　　　　有DISTINCT的出現(xiàn)的SELECT。

　　　　有GROUP　BY，HAVING，ORDER　BY的SELECT　子句。

　　　　SELECT　或DELETE，UPDATE　中的子查詢(xún)。

　　　　DEFAULT選項(xiàng)中不能用。

　　2、編輯SEQUENCE

　　只有OWNER或有ALTER權(quán)限的用戶(hù)才能修改SEQUENCE

　　未來(lái)的NUMBER受修改的影響。

　　不能修改START　WITH，如果變，則要RE－CREATE。

　　修改會(huì)受到某些有效性檢驗(yàn)的限制，如MAXVALUE

　　3、刪除：

　　DROP SEQUENCE sequence;

　　　　　　

　　　　　　ORACLE對(duì)象之INDEX

　　一、INDEX概述：

　　　　是ORACLE的一種數(shù)據(jù)對(duì)象，用POINTER來(lái)加速查詢(xún)行。通過(guò)快速路徑存取方法定位數(shù)據(jù)并減少I(mǎi)/O。　INDEX獨(dú)立于表。INDEX由ORACLE　SERVER來(lái)使用和保持。

　　二、索引如何建立？

　　　1、自動(dòng)：通過(guò)PRIMARY　KEY和UNIQUE KEY約束來(lái)建立。

      2、用戶(hù)手工建立非唯一性索引。

    三、創(chuàng)建方法：

    語(yǔ)法：CREATE INDEX index

ON table (column[, column]...);

    何時(shí)建立INDEX：

    此列經(jīng)常被放到WHERE字段或JOIN來(lái)作條件查詢(xún)。

    此列含有大量的數(shù)據(jù)。

    此列含有大量的空值。

    兩個(gè)或幾個(gè)列經(jīng)常同時(shí)放到WHERE字段進(jìn)行組合查詢(xún)

    表很大而且只有少于2-4% 的ROW可能被查詢(xún)的時(shí)候。

    以下情況不要建立索引：

    表很小；

    表被更新頻繁。

     四、查看已經(jīng)存在的索引：

     1、USER_INDEXES可以查詢(xún)索引名和類(lèi)型。

     2、USER_IND_COLUMNS包含索引名、表名、列名。

     實(shí)例：

     SQL> SELECT ic.index_name, ic.column_name,

2 ic.column_position col_pos, ix.uniqueness

3 FROM user_indexes ix, user_ind_columns ic

4 WHERE ic.index_name = ix.index_name

5 AND ic.table_name = ’S_EMP’;

     五、刪除索引：

     DROP INDEX index;

            SYNONYMS 同義詞

    語(yǔ)法：

     CREATE [PUBLIC] SYNONYM synonym for object;

     注意：此對(duì)象不能包含在一個(gè)包里；

           一個(gè)私有的同義詞不能與同一USER的其他對(duì)象重名。

    DROP SYNONYM D_SUM； 

  課程 八 用戶(hù)訪(fǎng)問(wèn)控制
                                  
本課重點(diǎn)：
   1、創(chuàng)建用戶(hù)
   2、創(chuàng)建角色來(lái)進(jìn)行安全設(shè)置
   3、使用GRANT或REVOKE 來(lái)控制權(quán)限
   
   注意：以下實(shí)例中標(biāo)點(diǎn)均為英文半角
  
   一、概述：
       ORACLE通過(guò)用戶(hù)名和密碼進(jìn)行權(quán)限控制。
     數(shù)據(jù)庫(kù)安全：系統(tǒng)安全和數(shù)據(jù)安全
     系統(tǒng)權(quán)限：使用戶(hù)可以訪(fǎng)問(wèn)數(shù)據(jù)庫(kù)
     對(duì)象權(quán)限：操縱數(shù)據(jù)庫(kù)中的對(duì)象
     SCHEMA：各種對(duì)象的集合
   二、系統(tǒng)權(quán)限：
      1、超過(guò)80個(gè)權(quán)限可用。
      2、DBA有最高的系統(tǒng)權(quán)限：
               CREATE NEW USER
               REMOVE USERS
               REMOVE ANY TABLE
               BACKUP ANY TABLE
   三、創(chuàng)建用戶(hù)
      1、CREATE USER user IDENTIFIED BY password;
      2、系統(tǒng)權(quán)限：CREATE SESSION Connect to the database.
                    CREATE TABLE Create tables in the user’s schema.
                   CREATE SEQUENCE Create a sequence in the user’s schema.
                   CREATE VIEW Create a view in the user’s schema.
                   CREATE PROCEDURE Create a stored procedure, function, or package in
                   the user’s schema. 
      3、授權(quán)用戶(hù)系統(tǒng)權(quán)限：
        GRANT privilege [, privilege...] TO user [, user...];
        GRANT CREATE TABLE TO SCOTT；
   四、角色的使用
     1、概念：角色是一組權(quán)限的命名，可以授予給用戶(hù)。這樣就如同給了某個(gè)用戶(hù)一個(gè)權(quán)限包。
     2、創(chuàng)建、授予給角色：
        CREATE ROLE MANAGER；
        GRANT CREATE TABLE，CREATE VIEW TO MANAGER；
        GRANT MANAGER TO CLARK
   五、修改密碼：
      ALTER USER user IDENTIFIED BY password;
   六、對(duì)象權(quán)限：
     1、語(yǔ)句：
 GRANT {object_priv(, object_priv...)|ALL}[(columns)]
ON object
TO {user[, user...]|role|PUBLIC}
[WITH GRANT OPTION];
     2、實(shí)例：
    最簡(jiǎn)單：
    SQL> GRANT select
2 ON s_emp
3 TO sue, rich;
    稍復(fù)雜：
     SQL> GRANT update (name, region_id)
      2 ON s_dept
      3 TO scott, manager;
  SQL> GRANT select, insert
2 ON s_dept
3 TO scott
4 WITH GRANT OPTION; 

課程 九 聲明變量
                                  
本課重點(diǎn)：
   1、了解基本的PLSQL塊和區(qū)域
   2、描述變量在PLSQL中的重要性
   3、區(qū)別PLSQL與非PLSQL變量
   4、聲明變量
   5、執(zhí)行PLSQL塊
   
   注意：以下實(shí)例中標(biāo)點(diǎn)均為英文半角
  
   一、概述：
       1、PLSQL 塊結(jié)構(gòu)：
       DECLARE --- 可選
        變量聲明定義
       BEGIN  ---- 必選
        SQL 和PLSQL 語(yǔ)句
       EXCEPTION ---- 可選
       錯(cuò)誤處理
       END；---- 必選
   二、實(shí)例：
       declare
       vjob varchar(9);
       v_count number:=0;
       vtotal date:=sysdate +7;
       c_tax constant number(3,2):=8.25;
       v_valid boolean not null:=true;
      begin
       select sysdate into vtotal from dual;
         end;
           /
      上例中，如果沒(méi)有這個(gè)SELECT語(yǔ)句，會(huì)如何？
       出錯(cuò)，說(shuō)明必須有STATEMENTS
       如果： select sysdate from dual into vtotal ;
       同樣，也不行。而且變量與賦值的類(lèi)型要匹配。
    三、%TYPE的屬性
       聲明一個(gè)變量使之與數(shù)據(jù)庫(kù)某個(gè)列的定義相同或與另一個(gè)已經(jīng)定義過(guò)的變量相同
       所以%TYPE要作為列名的后綴：如：
      v_last_name s_emp.last_name%TYPE;
       v_first_name s_emp.first_name%TYPE; --這樣做的好處是我們不必去知曉此列的類(lèi)型與定義
       或：v_balance NUMBER(7,2);
       v_minimum_balance v_balance%TYPE := 10;
    四、聲明一個(gè)布爾類(lèi)型的變量
       1 只有TRUE、FALSE、NULL可以賦值給BOOLEAN變量
       2 此變量可以接邏輯運(yùn)算符NOT、AND、OR。
       3、變量只能產(chǎn)生TRUE、FALSE、NULL。
       實(shí)例：
      VSAL1：=50000；
      VSQL2：=60000；
      VCOMMSAL BOOLEAN：=(VSAL1<VSQL2)；
      --其實(shí)是把TRUE賦值給此變量。
    五、LOB 類(lèi)型的變量
    共有CLOB、BLOB、BFILE、NCLOB幾種，這里不做為重點(diǎn)。
    六：使用HOST VARIABLES
    SQL> variable n number
     SQL> print n
    ：n=v_sal /12;
   :n這個(gè)加了：前綴的變量不是PLSQL變量，而是HOST。
    七、以下幾個(gè)PLSQL聲明變量，哪個(gè)不合法？
    A 、DECLARE
          V_ID NUMBER(4)；
    B、DECLARE
          V_X，V_Y，V_Z VARCHAR2(9)；
    C、DECLARE
       V_BIRTH DATE NOT NULL；
    D、DECLARE
       V_IN_STOCK  BOOLEAN：=1；
    E、DECLARE
       TYPE NAME_TAB IS TABLE OF VARCHAR2(20)
            INDEX BY BINARY_INTEGER；
        DEPT_NAME NAME_TAB；
    上面的習(xí)題我會(huì)在下章給出答案，這也正是聲明變量的規(guī)則和難點(diǎn)。
  

課程 十 寫(xiě)執(zhí)行語(yǔ)句
                                  
本課重點(diǎn)：
   1、了解PLSQL執(zhí)行區(qū)間的重要性
   2、寫(xiě)執(zhí)行語(yǔ)句
   3、描述嵌套塊的規(guī)則
   4、執(zhí)行且測(cè)試PLSQL塊
   5、使用代碼慣例
   
   注意：以下實(shí)例中標(biāo)點(diǎn)均為英文半角
  
   一、PLSQL 塊的語(yǔ)法規(guī)則：
     1、語(yǔ)句可以跨躍幾行。
     2、詞匯單元可以包括：分隔符、標(biāo)識(shí)符、文字、和注釋內(nèi)容。
     3、分隔符：
      +-*/=<>||....
     4、標(biāo)識(shí)符：
      最多30個(gè)字符，不能有保留字除非用雙引號(hào)引起。
      字母開(kāi)頭，不與列同名。
     5、文字串：如 V_ENAME:='FANCY';要用單引號(hào)括起來(lái)。
        數(shù)值型可以用簡(jiǎn)單記數(shù)和科學(xué)記數(shù)法。
     6、注釋內(nèi)容：?jiǎn)涡袝r(shí)用--    多行用/*   */
        與C很相似
   二、SQL函數(shù)在PL/SQL的使用：
     1、可用的：
      單行數(shù)值型、字符型和轉(zhuǎn)換型，日期型。
     2、不可用的：
      最大、最小、DECODE、分組函數(shù)。
      實(shí)例：
      BEGIN
         SELECT TO_CHAR(HIREDATE,'MON,DD,YYYY') FROM EMP;
      END;
      V_comment:=user||':'||sysdate; -- 會(huì)編譯出錯(cuò)
      V_comment:=user||':'||to_char(sysdate); --正確
      如果有可能，PLSQL都會(huì)進(jìn)行數(shù)據(jù)一致性的轉(zhuǎn)換，但ORACLE推薦你應(yīng)該進(jìn)行顯示的轉(zhuǎn)換，因?yàn)檫@樣會(huì)提高性能。
   三、嵌套塊和變量作用區(qū)域
     1、執(zhí)行語(yǔ)句允許嵌套時(shí)嵌套。
     2、嵌套塊可以看作正常的語(yǔ)句塊。
     3、錯(cuò)誤處理模塊可以包括一個(gè)嵌套塊
      4、exponential指數(shù) 邏輯、算數(shù)、連接、小括號(hào)
     5、看正面實(shí)例：
   declare
vjob varchar(9);
v_count number:=0;
vtotal date:=sysdate +7;
c_tax constant number(3,2):=8.25;
v_valid boolean not null:=true;
ttt vtotal%type;
begin
--select sysdate into vtotal from dual;--體會(huì)有無(wú)此句與結(jié)果的影響
dbms_output.put_line (vtotal);
end;
/
   注意：在執(zhí)行塊之前，要在SQL PLUS中執(zhí)行：SET SERVEROUTPUT ON
   三、以實(shí)例來(lái)說(shuō)明函數(shù)的參數(shù)聲明作用域
  declare
   v_weight number(3):=600;
v_message varchar2(255):='product10000';
begin
declare
   --sub-block
v_weight number(3):=1;
v_message varchar2(255):='pro300';
begin
v_weight:=v_weight +1;
end;
v_weight:=v_weight +1;
v_message:=v_message || 'my name';
end;
/
  子塊中的V_WEIGHT值為 2
   我們可以在子塊中加入：dbms_output.put_line('subblock value is '||v_weight);
    在主體中加入：dbms_output.put_line('main value is '||v_weight);
    我們發(fā)現(xiàn)MAINBLOCK中V_WEIGHT為 601
   改動(dòng)：
   1、在主塊的聲明中加  v_date date default sysdate;
   在子塊中加入：dbms_output.put_line('subblock date value is '||v_date);
   執(zhí)行結(jié)果：subblock date value is 22-11月-01
   ****說(shuō)明：主塊中的變量，如果子塊中沒(méi)有同名變量聲明，則繼承主塊中的聲明和初始化值；
   2、在子塊中加入：v_sub char(9);
      dbms_output.put_line('subblock char value is '||v_sub);
      此時(shí)正常輸出。
      在主塊中加入：dbms_output.put_line('main char value is '||v_sub);
      輸出：ORA-06550: 第 21 行, 第 45 列:
      PLS-00201: 必須說(shuō)明標(biāo)識(shí)符 'V_SUB'
      說(shuō)明：
      子塊中聲明的變量主塊中并不知曉，因此出錯(cuò)。
      了解了此實(shí)例，一切情況的變量的值的走向就都明了了。
  

課程 十一 與ORACLE SERVER交互
                                  
本課重點(diǎn)：
   1、在PLSQL中成功的寫(xiě)SELECT語(yǔ)句
   2、動(dòng)態(tài)聲明PLSQL變量類(lèi)型與SIZE
   3、在PLSQL中寫(xiě)DML語(yǔ)句
   4、在PLSQL中控制事務(wù)
   5、確定DML操作的結(jié)果
   
   注意：以下實(shí)例中標(biāo)點(diǎn)均為英文半角
  
   一、PLSQL中的SQL語(yǔ)句：
     SELECT、DML、COMMIT、ROLLBACK、SAVEPOINT、CURSOR
     特殊強(qiáng)調(diào)：PLSQL不支持DCL，不要問(wèn)為什么。(DBMS_SQL package allows you to issue DDL and DCL statements.)
   二、SELECT
     SELECT select_list
INTO variable_name | record_name
FROM table
WHERE condition;
    例：
SQL> r
  1  declare
  2  v_deptno number(2);
  3  v_loc varchar2(15);
  4  begin
  5     select deptno,loc
  6       into v_deptno,v_loc
  7      from dept
  8      where dname='SALES';
  9  DBMS_OUTPUT.PUT_LINE (V_deptno ||' and '||v_loc);
 10* end;
30 and CHICAGO
     選取字段與變量個(gè)數(shù)和類(lèi)型要一致。聲明的變量一定要在SIZE上大于返回的賦值，否則提示緩沖區(qū)溢出。
     如果SELECT語(yǔ)句沒(méi)有返回值：ORA-01403: 未找到數(shù)據(jù)
                               ORA-06512: 在line 5
     如果有多個(gè)值返回：ORA-01422: 實(shí)際返回的行數(shù)超出請(qǐng)求的行數(shù)
     這些我們到了錯(cuò)誤處理時(shí)會(huì)逐一講解。
     例：
     上面的例子可以改為：
     declare
   v_deptno dept.deptno%type;
   v_loc dept.loc%type;
    begin
     select deptno,loc
        into v_deptno,v_loc
        from dept
       where dname='SALES';
   DBMS_OUTPUT.PUT_LINE (V_deptno ||' and '||v_loc);
 end;
/
      這樣，可以在未知其他字段大小和類(lèi)型的時(shí)候定義變量，提高兼容性。
    三、DML 操作：
     1、實(shí)例：
   declare
 v_empno emp.empno%type;
 begin
    select max(empno)
     into v_empno
     from emp;
    v_empno:=v_empno+1;
     insert into emp(empno,ename,job,deptno)
       values(v_empno,'asdfasdf','ddddd',10);
 end;
/
   這樣也可以實(shí)現(xiàn)如SEQUENCE一樣的編號(hào)唯一遞增。
   2、更新和刪除：
     這個(gè)較為簡(jiǎn)單：
     DECLARE
       V_DEPTNO EMP.DEPtno%type :=10;
   begin
      delete from emp
        where deptno=v_deptno;
    end;
/
    大家多多實(shí)踐即可掌握。
    PLSQL首先檢查一個(gè)標(biāo)識(shí)符是否是一個(gè)數(shù)據(jù)庫(kù)的列名，如果不是，再假定它是一個(gè)PLSQL的標(biāo)識(shí)符。所以如果一個(gè)PLSQL的變量名為ID，列中也有個(gè)ID，如：
SELECT date_ordered, date_shipped
INTO date_ordered, date_shipped
FROM s_ord
WHERE id = id;
    就會(huì)返回TOO MANY ROWS，這是要盡量避免的。   
    四、SQL CURSOR
    游標(biāo)是一個(gè)獨(dú)立SQL工作區(qū)，有兩種性質(zhì)的游標(biāo)：
    隱式游標(biāo)：  當(dāng)PARSE 和EXECUTE 時(shí)使用隱式游標(biāo)。
    顯式游標(biāo)：  是由程序員顯式聲明的。
    游標(biāo)的屬性：
    SQL%ROWCOUNT：一個(gè)整數(shù)值，最近SQL語(yǔ)句影響的行數(shù)。
    SQL%FOUND    BOOLEAN屬性，如果為T(mén)RUE，說(shuō)明最近的SQL STATEMENT有返回值。
    SQL%NOTFOUND  與SQL%FOUND相反
    SQL%ISOPEN   在隱式游標(biāo)中經(jīng)常是FALSE，因?yàn)閳?zhí)行后立即自動(dòng)關(guān)閉了。
    SQL> variable row_de number
    SQL> r
  1  declare
  2    v_deptno number:=10;
  3    begin
  4      delete from emp where
  5       deptno=v_deptno;
  6       :row_de:=sql%rowcount;
  7* end;
PL/SQL 過(guò)程已成功完成。
SQL>  print row_de  --這是一個(gè)SQL PLUS變量
    ROW_DE
----------
         4
     這時(shí)其實(shí)并沒(méi)有真正的刪除，而是需要 COMMIT或ROLLBACK，來(lái)完成事務(wù)。

14:37 | 添加評(píng)論 | 固定鏈接 | 引用通告 (0) | 寫(xiě)入日志 | 計(jì)算機(jī)與 Internet
PL/Sql循序漸進(jìn)全面學(xué)習(xí)教程--Oracle(4)
課程 十二 編寫(xiě)控制結(jié)構(gòu)語(yǔ)句
                                  
本課重點(diǎn)：
   1、結(jié)構(gòu)控制的的用途和類(lèi)型
   2、IF 結(jié)構(gòu)
   3、構(gòu)造和標(biāo)識(shí)不同的循環(huán)
   4、使用邏輯表
   5、控制流和嵌套
   
   注意：以下實(shí)例中標(biāo)點(diǎn)均為英文半角
  
   一、控制執(zhí)行流
       可以是分支和循環(huán)：IF THEN END IF
      IF condition THEN
statements;
[ELSIF condition THEN
statements;]
[ELSE
statements;]
END IF;
        例子：IF V_ENAME='OSBORNE' THEN
                  V_MGR:=22;
              END IF;
        這里我們可以注意，PLSQL和C語(yǔ)言或JAVA在條件上的不同，=代表關(guān)系運(yùn)算，而：=代表賦值。
      看一個(gè)函數(shù)：
   create    FUNCTION calc_val
(v_start IN NUMBER)
RETURN NUMBER
IS
BEGIN
IF v_start > 100 THEN
RETURN (2 * v_start);
ELSIF v_start >= 50 THEN
RETURN (.5 * v_start);
ELSE
RETURN (.1 * v_start);
END IF;
END calc_val;
       現(xiàn)在，雖然我們尚未講解CREATE 函數(shù)或過(guò)程，但可以看到IF 條件在其中的作用。
     二、注意LOGIC TABLE中的邏輯對(duì)應(yīng)關(guān)系
    1、NOT、AND、OR
    2、任何表達(dá)式中含有空值結(jié)果都為 NULL
    3、連接字符串中含有空值會(huì)把NULL作為 EMPTY STRING
        declare
   v_deptno dept.deptno%type;
   v_loc dept.loc%type;
V_FLAG BOOLEAN ;
V_REC BOOLEAN :=FALSE; --此值改為T(mén)RUE、NULL、FALSE進(jìn)行不同的比較
V_AVA BOOLEAN:=NULL;
    begin
V_FLAG:=V_REC AND V_AVA;
IF V_FLAG=TRUE THEN
   DBMS_OUTPUT.PUT_LINE ('TRUE');
ELSIF V_FLAG=FALSE THEN
   DBMS_OUTPUT.PUT_LINE ('FALSE');
ELSE
  DBMS_OUTPUT.PUT_LINE ('NULL');
END IF;
 end;
/
   值得注意的是：NULL AND FALSE ---> FALSE
   這是在實(shí)踐中總結(jié)出來(lái)的。
   三、基本循環(huán)基礎(chǔ)：
   1、LOOP
statement1;
statement2;
. . .
EXIT [WHEN condition];
END LOOP;
   v_ord_id s_item.ord_id%TYPE := 101;
v_counter NUMBER (2) := 1;
BEGIN
. . .
LOOP
INSERT INTO s_item (ord_id, item_id)
VALUES (v_ord_id, v_counter);
v_counter := v_counter + 1;
EXIT WHEN v_counter > 10;
END LOOP;
    2、FOR循環(huán)：
  FOR index IN [REVERSE] lower_bound..upper_bound LOOP
statement1;
statement2;
. . .
END LOOP;
        
    實(shí)例：DECLARE
  V_LOWER NUMBER:=1;
   V_UPPER NUMBER:=23;
BEGIN
DBMS_OUTPUT.PUT_LINE('');
   FOR I IN V_LOWER..V_UPPER LOOP
DBMS_OUTPUT.PUT_LINE(I);
END LOOP;
END;
/
    3、WHILE 循環(huán)：
   WHILE condition LOOP
statement1;
statement2;
. . .
END LOOP;
   4、循環(huán)是可以多層嵌套的。可以用<<LABEL>>做循環(huán)的標(biāo)簽。
...
BEGIN
<<Outer–loop>>LOOP
v_counter :=v_counter+1;
EXIT WHEN v_counter>10;
<<Inner–loop>>LOOP
...
EXIT Outer_loop WHEN total_done = ’YES’;
–– Leave both loops
EXIT WHEN inner_done = ’YES’;
–– Leave inner loop only
...
END LOOP Inner_Loop;
...
END LOOP Outer_loop;
END;
         
    總結(jié)：本章內(nèi)容較為繁雜，雖然不是很難，而且多數(shù)與其他高級(jí)語(yǔ)言有某種共性，但大家要多多練習(xí)，用實(shí)踐來(lái)檢驗(yàn)對(duì)某些含糊的猜測(cè)。

課程 十三 使用組合數(shù)據(jù)類(lèi)型* 游標(biāo)操縱數(shù)據(jù)
  本課重點(diǎn)：
   1、創(chuàng)建用戶(hù)自定義的PLSQL記錄
   2、利用%ROWTYPE屬性來(lái)創(chuàng)建記錄
   3、創(chuàng)建PLSQL表
   4、描述記錄、表、記錄的表之間的區(qū)別
       注意：以下實(shí)例中標(biāo)點(diǎn)均為英文半角
      一、合成數(shù)據(jù)類(lèi)型
      1、類(lèi)型分為PLSQL記錄和PLSQL表
      2、包含內(nèi)部組件
      3、可重用
   二、PLSQL記錄
     與3GL中的記錄結(jié)構(gòu)相似
     與數(shù)據(jù)庫(kù)表是兩回事
     是一個(gè)方便的途徑FETCH一些行FROM一個(gè)表來(lái)進(jìn)行相關(guān)處理。
     標(biāo)準(zhǔn)語(yǔ)法格式我們暫不介紹，因?yàn)槊勘緯?shū)上均有。
     看例子：
        declare
vjob varchar(9);
v_count number:=0;
vtotal date:=sysdate +7;
c_tax constant number(3,2):=8.25;
v_valid boolean not null:=true;
ttt vtotal%type;
type emp_record_type is record
  (empno number not null:=100,
    ename emp.ename%type,
     job  emp.job%type);
emp_record emp_record_type;
begin
--select sysdate into vtotal from dual;--體會(huì)有無(wú)此句與結(jié)果的影響
dbms_output.put_line (vtotal);
end;
/
     主要看TYPE RECORD出現(xiàn)的位置。每一個(gè)例子都是可以成功執(zhí)行的。
    我們也可以利用原有的表結(jié)構(gòu)：
    DECLARE
       EMP_RECORD EMP%ROWTYPE；               
                       游標(biāo)操縱數(shù)據(jù)
    PLSQL游標(biāo)提供了一種從數(shù)據(jù)庫(kù)提取多行數(shù)據(jù)，然后對(duì)每行數(shù)據(jù)進(jìn)行單獨(dú)處理的方法。
    一、兩種游標(biāo)：
      顯式游標(biāo)
      隱式游標(biāo)
    二、顯式游標(biāo)：操縱步驟如下：聲明游標(biāo)、打開(kāi)游標(biāo)、從游標(biāo)中取回?cái)?shù)據(jù)、關(guān)閉游標(biāo)
    三、聲明游標(biāo)：
    DECLARE CURSOR_NAME
     IS
    SELECT STATMENT
       能夠控制游標(biāo)的，唯一參數(shù)是INIT.ORA中的OPEN_CURSORS，我原來(lái)以為是客戶(hù)端最多可以打開(kāi)多少個(gè)游標(biāo)，但有本書(shū)上講這是用于管理游標(biāo)的內(nèi)存的數(shù)量。
    DECLARE
      CURSOR C_NAME
     IS
     SELECT ENAME FROM EMP
      WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT
          WHERE CITY_ID=‘BJ’)
   --- 說(shuō)明游標(biāo)可以用子查詢(xún)
   四、打開(kāi)游標(biāo)
     OPEN CURSOR_NAME；
     這時(shí)游標(biāo)將它的指針指向活動(dòng)集的開(kāi)始，指針指向第一條記錄的前面是因?yàn)樗€沒(méi)有執(zhí)行FETCH命令。如果試圖打開(kāi)一個(gè)已經(jīng)打開(kāi)的游標(biāo)，將出錯(cuò)：
    ORA-06511：PL/SQL：CURSOR ALREADY OPEN
   我們可以這樣：
    IF NOT C_NAME%ISOPEN
    THEN
    OPEN C_NAME；
    END IF；
    五、從游標(biāo)中取回?cái)?shù)據(jù)
    FETCH CURSOR_NAME INTO RECOR-LIST；
    關(guān)閉游標(biāo)：CLOSE CURSOR_NAME
   六、實(shí)例：
   DECLARE
    myname varchar2(22);
      CURSOR C_NAME
     IS
     SELECT ENAME FROM EMP;
begin
IF NOT C_NAME%ISOPEN
    THEN
    OPEN C_NAME;
end if;
LOOP
FETCH c_name into myname;
dbms_output.put_line (myname);
exit when c_name%notfound;
end loop;
close c_name;
end;
/--我們將對(duì)以上程序進(jìn)行變形，形成復(fù)雜的光標(biāo)利用。
DECLARE
    myname varchar2(22);
    thisdeptno scott.emp.deptno%type;
      CURSOR C_NAME
     IS
     SELECT ENAME,deptno FROM EMP order by deptno desc;
begin
IF NOT C_NAME%ISOPEN
    THEN
    OPEN C_NAME;
end if;
LOOP
FETCH c_name into myname,thisdeptno;
dbms_output.put_line (myname||','||thisdeptno || ',' || to_char(c_name%rowcount));
exit when c_name%notfound;
end loop;
dbms_output.put_line ('the Total record is fetched is ' || to_char(c_name%rowcount));
close c_name;
end;
/
      我們?cè)黾幼兞浚M(jìn)行用了排序，使用了光標(biāo)屬性，大家看結(jié)果發(fā)生的變化，想想為什么。
   實(shí)例精華!!!：   DECLARE
    myname varchar2(22);
    ii number;
    thisdeptno scott.emp.deptno%type;
      CURSOR C_NAME
     IS
     SELECT * FROM EMP order by deptno desc;
     emp_record c_name%rowtype;
begin
ii:=1;
    for emp_record in c_name loop
 dbms_output.put_line(ii);
ii:=ii+1;
end loop;
end;
/
--這里使用了游標(biāo)FOR循環(huán)，在FOR循環(huán)的開(kāi)始，進(jìn)行、和END LOOP，分別隱式進(jìn)行了游標(biāo)的打開(kāi)、FETCH和CLOSE。
   我們甚至可以不聲明游標(biāo)：FOR emp_record in (SELECT * FROM DEPT) loop
    這種技術(shù)被稱(chēng)為顯式游標(biāo)的自動(dòng)化。
    在上面，我們可以將一個(gè)表的所有字段輸出,如我們將PUT_LINE的II改為emp_record.ename，就可以輸出一個(gè)字段內(nèi)容。
     這種方式非常簡(jiǎn)單而且效率較高。
   ----------------
為了測(cè)試光標(biāo)屬性的重要性，我們做一個(gè)以下的過(guò)程：
 create or replace PROCEDURE change_salary
 (v_emp_id IN NUMBER, -- formal parameters
 v_new_salary IN NUMBER)
 IS
 BEGIN -- begin PL/SQL block
 UPDATE emp
 SET sal = v_new_salary
 WHERE empno = v_emp_id;
 COMMIT;
 END change_salary;
/
      這樣，我們?cè)谀涿麎K中，
UPDATE DEPT
    SET DNAME='MY DEPT' WHERE ....;
  IF SQL%FOUND THEN
     COMMIT;
  ELSE
  change_salary(7369,9000);
END IF;
  我們看到我們通過(guò)流程控制了不同的執(zhí)行結(jié)果，對(duì)于過(guò)程，我們可以用以下幾種方法調(diào)用：
在SQLPLUS中：CALL change_salary(7369,9000);
             EXECUTE change_salary(7369,9000);
在一個(gè)塊中，如：
    begin
change_salary(7369,9000);
end;

14:35 | 添加評(píng)論 | 固定鏈接 | 引用通告 (0) | 寫(xiě)入日志 | 計(jì)算機(jī)與 Internet
PL/Sql循序漸進(jìn)全面學(xué)習(xí)教程--Oracle(5)
最后一課 異常處理本章重點(diǎn)：    
     1、定義PLSQL異常
     2、列舉不同的異常處理方法
     3、捕獲非預(yù)期的錯(cuò)誤
     4、描述異常的影響
     5、定制異常的返回信息    一、PLSQL異常處理
     異常是由ORACLE錯(cuò)誤或顯式的拋出一個(gè)錯(cuò)誤產(chǎn)生的。
     如何處理：
     用一個(gè)處理程序來(lái)捕獲它；
     將它傳遞給CALLING ENVIRONMENT
    二、異常的類(lèi)型：
     ORACLE SERVER 預(yù)定義錯(cuò)誤
     非ORACLE SERVER 預(yù)定義錯(cuò)誤，但也是ORACLE SERVER 的標(biāo)準(zhǔn)錯(cuò)誤
     用戶(hù)自定義異常
    三、捕捉異常的要點(diǎn)：
    Place the WHEN OTHERS clause after all other exception handling clauses.

    You can have at most one WHEN OTHERS clause.
     Begin exception-handling section of the block with the keyword EXCEPTION.
   Define several exception handlers, each with their own set of actions, for the
block.
     When an exception occurs, PL/SQL will process only one handler before leaving
the block.
    EXCEPTION
WHEN exception1 [OR exception2 . . .] THEN
statement1;
    四、常用錯(cuò)誤：
     NO_DATA_FOUND ORA-01403
     TOO_MANY_ROWS ORA-01422
     INVALID_CURSOR ORA-01001
     ZERO_DIVIDE ORA-01476
     DUP_VAL_ON_INDEX ORA-00001
 
    五、實(shí)例
    PROCEDURE elim_inventory
(v_product_id IN s_product.id%TYPE) IS
v_id s_product.id%TYPE;
BEGIN
SELECT id
INTO v_id
FROM s_product
WHERE id = v_product_id;
DELETE FROM s_inventory
WHERE product_id = v_product_id;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
ROLLBACK;
TEXT_IO.PUT_LINE(TO_CHAR(v_product_id)||
’ is invalid.’);
WHEN TOO_MANY_ROWS THEN
ROLLBACK;
TEXT_IO.PUT_LINE(’Data corruption in S_PRODUCT.’);
WHEN OTHERS THEN
ROLLBACK;
TEXT_IO.PUT_LINE(’Other error occurred.’);
END elim_inventory;
      在SCOTT環(huán)境中使用要稍加改動(dòng)
    六、使用non-predefined Oracle7 Server error
    DECLARE
       E_PRO   EXCEPTION;
       PRAGMA EXCEPTION_INIT(E_PRO,ERROR_NUMBER);
    BEGIN
   ......
     EXCEPTION
      WHEN E_PRO THEN
       DBMS_OUTPUT.PUT_LINE('ASDLKFJKASDJFASJDFLKASDF');
      ......
    END;
    七、用戶(hù)自定義
    exception EXCEPTION;
   RAISE exception;    EXCEPTION
      WHEN E_PRO THEN
       DBMS_OUTPUT.PUT_LINE('ASDLKFJKASDJFASJDFLKASDF');
      ......
    END;     這里，只有用戶(hù)自定義異常是要顯式聲明的，其他兩個(gè)不用。
    在SUN OS5.8中，進(jìn)行SVRMGRL> OERR ORA 01840  可返回信息
    或查錯(cuò)誤代碼：
    HTTP://TECHNET.ORACLE.COM/DOC/SERVER.815/A67785/E1500.HTM
        題外話(huà)，create public database link otlink connect to system identified
    by manager using 'oratest';
        說(shuō)回來(lái)，以下兩個(gè)函數(shù)：
   SQLCODE ----Returns the numeric value for the error code. You can assign it
to a NUMBER variable.
   SQLERRM ----Returns character data containing the message associated with
the error number.
    一般這樣，
   EXCEPTION
     ... WHEN OTHERS THEN
            ROLLBACK;
            v_error_code:=SQLCODE;
            V_ERROR_MESSAGE:=SQLERRM;
            INSERT INTO ........
      END;
    八、調(diào)用外圍環(huán)境
    SQLPLUS 
    PROCEDURE BUILDER
    DEVELOPER 2000
    OTHER .........
    ---- 也就是把ERROR NUMBER和MESSAGE輸出到SCREEN。
    九、使用RAISE_APPLICATION_ERROR
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20201,'NO MATCH RECORD YOU WANNA');
END;
    當(dāng)然，RAISE_APPLICATION_ERRO也是可以放在EXECUTE區(qū)的，IF 。。。 THEN
                   RAISE_APPLICATION_ERRO()；
              END IF；
    這也是很好理解