现在的位置: 首页 > 综合 > 正文

《oracle pl/sql programming》 第11章 record

2013年08月27日 ⁄ 综合 ⁄ 共 3442字 ⁄ 字号 评论关闭

record

 

record表示一個記錄類型。舉例:

創建一個表:

    CREATE TABLE books (

      book_id         INTEGER,

      isbn            VARCHAR2(13),

      title           VARCHAR2(200),

      summary         VARCHAR2(2000),

      author          VARCHAR2(200),

      date_published  DATE,

      page_count      NUMBER

    );

利用record取得這個表的數據:

    DECLARE

       my_book   books%ROWTYPE;

    BEGIN

       SELECT *

         INTO my_book

         FROM books

        WHERE title = 'Oracle PL/SQL Programming, 4th Edition';

    

       IF my_book.author LIKE '%Feuerstein%'

       THEN

          DBMS_OUTPUT.put_line ('Our newest ISBN is ' || my_book.isbn);

       END IF;

    END;

 

有三種類型的record,分別是:

基于表的record;

基于cursor的record;

自定義record;

上例中的my_book是基于表的record;

如下是基于cursor的record:

    DECLARE

       CURSOR my_books_cur IS

          SELECT * FROM books

           WHERE author LIKE '%FEUERSTEIN%';

 

       one_SF_book my_books_cur%ROWTYPE;

如下是一個癮式的基于游標的record應用:

    BEGIN

       FOR book_rec IN (SELECT * FROM books)

       LOOP

          calculate_total_sales (book_rec);

       END LOOP;

    END;

如下是自定義的record:

    DECLARE

       TYPE book_info_rt IS RECORD (

          author books.author%TYPE,

          category VARCHAR2(100),

          total_page_count POSITIVE);

 

       steven_as_author book_info_rt;

 

通過.操作符來訪問record的字段值,如:

    DECLARE

       rain_forest_rec rain_forest_history%ROWTYPE;

    BEGIN

       /* Set values for the record */

       rain_forest_rec.country_code  := 1005;

       rain_forest_rec.analysis_date :=

          ADD_MONTHS (TRUNC (SYSDATE), -3);

       rain_forest_rec.size_in_acres := 32;

       rain_forest_rec.species_lost  := 425;

 

       /* Insert a row in the table using the record values */

       INSERT INTO rain_forest_history

              (country_code, analysis_date, size_in_acres, species_lost)

       VALUES

          (rain_forest_rec.country_code,

           rain_forest_rec.analysis_date,

           rain_forest_rec.size_in_acres,

           rain_forest_rec.species_lost);

       ...

    END;

 

嵌套游標,如下:

    DECLARE

       TYPE phone_rectype IS RECORD

          (intl_prefix   VARCHAR2(2),

           area_code     VARCHAR2(3),

           exchange      VARCHAR2(3),

           phn_number    VARCHAR2(4),

           extension     VARCHAR2(4)

          );

 

       -- Each field is a nested record...

      TYPE contact_set_rectype IS RECORD

          (day_phone#    phone_rectype,

           eve_phone#    phone_rectype,

           fax_phone#    phone_rectype,

           home_phone#   phone_rectype,

           cell_phone#   phone_rectype

          );

    

       auth_rep_info_rec contact_set_rectype;

    BEGIN

    ...

    auth_rep_info_rec.fax_phone#.area_code :=

       auth_rep_info_rec.home_phone#.area_code;

 

record的比較,你不能直接用=比較,這樣是不行的:

    DECLARE

       first_book summer.reading_list_rt := summer.must_read;

       second_book summer.reading_list_rt := summer.wifes_favorite;

    BEGIN

       IF first_book = second_book

       THEN

          lots_to_talk_about;

       END IF;

    END;

而應該這樣:

    DECLARE

       first_book summer.reading_list_rt := summer.must_read;

       second_book summer.reading_list_rt := summer.wifes_favorite;

    BEGIN

       IF     first_book.favorite_author = second_book.favorite_author

          AND first_book.title = second_book.title

          AND first_book.finish_by = second_book.finish_by

       THEN

          lots_to_talk_about;

       END IF;

    END;

 

觸發器里的Pseudo-Records

在觸發器里,提供了OLDNEW這兩個record,OLD變數記錄修改前的值,NEW表示記錄修改后的值,它們被稱為Pseudo-Records。如下例:

    CREATE OR REPLACE TRIGGER check_raise

       AFTER UPDATE OF salary

       ON employee

       FOR EACH ROW

    WHEN  (OLD.salary != NEW.salary) OR

          (OLD.salary IS NULL AND NEW.salary IS NOT NULL) OR

          (OLD.salary IS NOT NULL AND NEW.salary IS NULL)

    BEGIN

       IF :NEW.salary > 100000 THEN ...

 

 

 

 

抱歉!评论已关闭.