record

最新推荐文章于 2025-07-18 14:40:01 发布

转载最新推荐文章于 2025-07-18 14:40:01 发布 · 94 阅读

本文介绍了在Oracle PL/SQL中如何定义和使用记录，包括基于表、游标的方式及程序员自定义记录的方法。通过实例展示了如何创建记录、填充数据并访问记录字段。

Records

a record is composed of one or more fields. There are three different ways to define a record:

1. Table-based 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
    );

I can then easily create a record based on this table, populate it with a query from the database, and then access the

individual columns through the record's fields:

    DECLARE
       my_book   books%ROWTYPE; -- RECORD definition
    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;

2. Cursor-based record

    DECLARE
       CURSOR my_books_cur IS
          SELECT * FROM books
           WHERE author LIKE '%FEUERSTEIN%';

       one_SF_book my_books_cur%ROWTYPE;
3. programmer defined
--select into record directly
    DECLARE
       TYPE customer_sales_rectype IS RECORD
          (customer_id   NUMBER (5),
           customer_name customer.name%TYPE,
           total_sales   NUMBER (15,2)
           );
       top_customer_rec customer_sales_rectype;
    BEGIN
       /* Move values directly into the record: */
       SELECT customer_id, name, SUM (total_sales)
         INTO top_customer_rec
         FROM cust_sales_roundup
        WHERE sold_on < ADD_MONTHS (SYSDATE, -3)
         GROUP BY customer_id, name;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23895263/viewspace-677180/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23895263/viewspace-677180/