Oracle存储过程笔记

记录下自己学习的过程，备忘！

以下试验均在Oracle默认账户hr/hr下进行

1. SQL statement在PL/SQL中
        
        (1).用select语句从数据库获取数据，语法：
        
                select select_list(列名的列表)
                
                into {variable_name,[variable_name]...| record_name}
                
                from table
                
                [where condition]

        试验的运行环境：
        [oracle@localhost sql]$ sqlplus hr/hr
        
        SQL*Plus: Release 10.2.0.1.0 - Production on Thu Aug 18 15:10:13 2011
        
        Copyright (c) 1982, 2005, Oracle.  All rights reserved.
        
        
        Connected to:
        Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
        With the Partitioning, OLAP and Data Mining options
        
        SQL> @pro4_1
        
        PL/SQL procedure successfully completed.
        
        SQL> setseroutput on
        SP2-0734: unknown command beginning "setseroutp..." - rest of line ignored.
        SQL> set serveroutput on
        SQL> @pro4_1
        tmp_name's inital name is Hello Harly!
        My name is Ellen
        
        PL/SQL procedure successfully completed.
        
        SQL> l
          1  declare
          2     tmp_name employees.first_name%type := 'Hello Harly!';
          3  begin
          4      dbms_output.put_line(q'[tmp_name's inital name is ]'||tmp_name);
          5     select first_name into tmp_name from employees where rownum<2;
          6     dbms_output.put_line('My name is '||tmp_name);
          7* end;
        SQL>
        
        (2).命名规范(naming convention)
                
                a.Use a naming convention to avoid ambiguity in the where clause.
                
                b.Avoid using database column names as identifiers.
                
                c.The names of local variables and formal parameters take precedence over the
                
                  names of database tables.
                  
                d.The names of database table columns take precedence over the names of local
                
                    variables.

2. Merging Rows(合并行)
        
        试验过程(先建立一张与employees结果一样的表)：
        
        SQL> create table test as select *from employees where rownum<10;

        Table created.
        
        SQL> select employee_id,first_name from test;
        
        EMPLOYEE_ID FIRST_NAME
        ----------- --------------------
                198 Donald
                199 Douglas
                200 Jennifer
                201 Michael
                202 Pat
                203 Susan
                204 Hermann
                205 Shelley
                206 William
        
        9 rows selected.
        
        现在通过匹配employees表来向test表中插入或更新记录，存储过程(merge_demo.sql)如下：
        SQL> @merge_demo
        
        PL/SQL procedure successfully completed.
        
        SQL> l
          1  begin
          2  merge into test t
          3       using employees e
          4       on (e.employee_id = t.employee_id)
          5     when matched then
          6       update set
          7             t.first_name = e.first_name,
          8             t.last_name = e.last_name,
          9             t.email = e.email,
         10             t.phone_number = e.phone_number,
         11             t.hire_date = e.hire_date,
         12             t.job_id = e.job_id,
         13             t.salary = e.salary,
         14             t.commission_pct = e.commission_pct,
         15             t.manager_id = e.manager_id,
         16             t.department_id = e.department_id
         17      when not matched then
         18             insert values(e.employee_id,e.first_name,e.last_name,e.email,
         19                  e.phone_number,e.hire_date,e.job_id,e.salary,
         20                  e.commission_pct,e.manager_id,e.department_id);
         21  commit;
         22* end;
        SQL>

3. SQL Cursor：有隐式指针和显式指针两种.
    
        (1).SQL Cursor attributes for implicit Cursor.        
                
                a.SQL%FOUND  --Boolean attribute
                
                b.SQL%NOTFOUND  --Boolean attribute
                
                c.SQL%ROWCOUNT  --不区分大小写，小写也行
                
                d.update和delete时还可以返回一个结果
                
                eg:
                
                SQL> @test_cursor_attr
                Del 's name is William.
                Del :1 rows.
                mysal is 5200. myname is Douglas.
                1 row updated.
                
                PL/SQL procedure successfully completed.
                
                SQL> l
                  1  declare
                  2     v_rows_deleted varchar2(30);
                  3     v_row_updated varchar2(20);
                  4     v_empno  test.employee_id%type :=206;
                  5     myname test.first_name%type;
                  6     mysal test.salary%type;
                  7  begin
                  8     delete from test
                  9     where employee_id = v_empno
                 10     returning first_name into myname;
                 11
                 12     dbms_output.put_line(q'[Del 's name is ]'||myname||'.');
                 13
                 14     dbms_output.put_line('Del :'||SQL%ROWCOUNT||' rows.');
                 15
                 16     update test set salary = salary*2 where employee_id = 199
                 17      returning salary,first_name into mysal,myname;
                
                 18     --当前SQL所影响的记录,此处是update
                 19     v_rows_deleted := (SQL%ROWCOUNT ||' row updated.');                                 
                 20
                 21     commit;
                 22     dbms_output.put_line('mysal is '||mysal||'. myname is '||myname||'.');
                 23     dbms_output.put_line(v_rows_deleted);
                 24* end;
                SQL>
                
4. IF Statements
        
        语法:
        
        IF condition THEN
            statements;
        [ELSIF condition THEN
            statements;]
        [ELSE
            statements;]
        END IF;
        
        注：条件表达式计算为true，执行then后面的语句；
                条件表达式计算为false或NULL则then后面的语句不被执行；
        eg：(在sqlplus中测试的过程)
        
        SQL> @test_if_clause
        v_num is NULL
        
        PL/SQL procedure successfully completed.
        
        SQL> l
          1  declare
          2  -- v_num number := 10;
          3      v_num number;--默认为null测试
          4  begin
          5     if v_num < 10
          6     then
          7         dbms_output.put_line('v_num<10!');
          8     elsif v_num > 10
          9     then
         10         dbms_output.put_line('v_num>10!');
         11     elsif v_num = 10 then
         12         dbms_output.put_line('v_num=10!');
         13     elsif v_num is NULL then
         14             dbms_output.put_line('v_num is NULL');
         15     end if;
         16* end;
        SQL> 3
          3*     v_num number;--默认为null测试
        SQL> c/er/er :=10
          3*     v_num number :=10;--默认为null测试
        SQL> /
        v_num=10!
        
        PL/SQL procedure successfully completed.
        
        SQL> l
          1  declare
          2  -- v_num number := 10;
          3      v_num number :=10;--默认为null测试
          4  begin
          5     if v_num < 10
          6     then
          7         dbms_output.put_line('v_num<10!');
          8     elsif v_num > 10
          9     then
         10         dbms_output.put_line('v_num>10!');
         11     elsif v_num = 10 then
         12         dbms_output.put_line('v_num=10!');
         13     elsif v_num is NULL then
         14             dbms_output.put_line('v_num is NULL');
         15     end if;
         16* end;
        SQL> 3
          3*     v_num number :=10;--默认为null测试
        SQL> c/10/2
          3*     v_num number :=2;--默认为null测试
        SQL> /
        v_num<10!
        
        PL/SQL procedure successfully completed.
        
5. CASE

        (1).case表达式(一个case表达式选择一个结果并返回)
        
        语法：(--simple case)
        
        CASE selector
        
        WHEN expression1 THEN result1
        
        WHEN expression2 THEN result2
        ...
        WHEN expressionn THEN resultn
        
      [ELSE returnN+1]
        
        END;
        
        eg:
        SQL> @test_case_1
        Enter value for grade: a
        Grade is A. Appraisal:Excellent
        
        PL/SQL procedure successfully completed.
        
        SQL> l
          1  declare
          2     v_grade char(1) := upper('&grade');
          3     v_appraisal varchar2(20);
          4  begin
          5     v_appraisal := case v_grade
          6             WHEN 'A' THEN 'Excellent'
          7             WHEN 'B' THEN 'Very Good'
          8             WHEN 'C' THEN 'Good'
          9             ELSE  'No such grade'
         10        END;
         11     dbms_output.put_line('Grade is '||v_grade||'. Appraisal:'||v_appraisal);
         12* end;
        SQL>
        
        另外一种写法：searched case
        eg：
            v_appraisal := case
                        when v_grade = 'A' then 'Excellent'
                        when v_grade in ('B','C') then 'Good'
                        else 'No so such grade'
                end;
        
        (2).case statements
        
        case语句与case表达式的区别就在于case语句是以end case结尾，其他的没什么区别：
        
        CASE
        ...
        ...
        END CASE;
        
        和case表达式一样，也有两种写法：simple case和searched case
        
6. Handling NULLS
            
     (1).Logic Table:

    

            
    

     注意：在求表达式时短路的情况(short-Circuit)

    
     (2).NULL statements
             
             语法：NULL;        
             ( improving program readability.
                 using the NULL statement after a label.)    
                 
7. Loop statements

        三种循环类型：
        (1).Basic Loop
        
        eg:
        
            SQL> @test_basic_loop
            
            PL/SQL procedure successfully completed.
            
            SQL> l
              1  declare
              2       v_countryid loc.country_id%type := 'CA';
              3       v_loc_id  loc.location_id%type;
              4       v_counter number := 1;
              5       v_new_city  loc.city%type := 'ShenZhen';
              6  begin
              7     select max(location_id) into v_loc_id
                                  from loc where country_id = v_countryid;
              8     Loop
              9        insert into loc(location_id,city,country_id)
                                          values((v_loc_id+v_counter),v_new_city,v_countryid);
             10        v_counter := v_counter+1;
             11        Exit when v_counter > 3;    --没有此条件，则形成死循环
             12     end loop;
             13     commit;
             14* end;
            SQL> select location_id,city,country_id from loc where country_id='CA';
            
            LOCATION_ID CITY                           CO
            ----------- ------------------------------ --
                   1800 Toronto                        CA
                   1900 Whitehorse                     CA
                   1901 ShenZhen                       CA
                   1902 ShenZhen                       CA
                   1903 ShenZhen                       CA
        
        (2).While Loop
        
                针对上面的例子改写的while loop：
                
                while v_counter <= 3 LOOP
                        insert into loc(location_id,city,country_id)
                          values((v_loc_id+v_counter),v_new_city,v_countryid);
                  v_counter := v_counter+1;
              END LOOP;    
        
        (3).For Loop
                
                语法：
                
                for counter in [reverse] lower_bound..upper_bound LOOP  --reverse倒过来
                        ...
                        statement1;
                    ...
                END LOOP;
                
                同样针对上面的例子改写：
                for i in 1..3 LOOP  --此处的i默认定义，并不需要显示定义，且步进为1
                        insert into loc(location_id,city,country_id)
                          values((v_loc_id + i),v_new_city,v_countryid);
                END LOOP;                
                                                            
.......................未完待续！！！