《oracle pl/sql programming》 第7章 working with data

原创 2007年10月08日 17:23:00
1.命名
在使用變量或常量之前,需要先對它進行聲明,聲明應遵循以下規則:
·    名稱的寬度在30個字符以內
·    名稱以字母開頭,后續只可以是字母、數字以及$,#,_
·    名稱忽略大小寫(雙引號命名法除外)
 
2.數據類型
Character:char,varchar2,...
Number
Date,Timestamp,interval
Boolean
Binary(二進制):BLOB,BFILE
ROWID
REF cursor
Internet DataType
"Any" DataType
User-Defined DataType
 
3.聲明數據
聲明變量
name datatype [NOT NULL] [default_assignment];
example:
    DECLARE
       -- Simple declaration of numeric variable
       l_total_count NUMBER;
 
       -- Declaration of number that rounds to nearest hundredth (cent):
       l_dollar_amount NUMBER (10,2);
 
       -- A single date value, assigned a default value of "right now"
       -- and it can never be NULL
       l_right_now DATE NOT NULL DEFAULT SYSDATE;
 
       -- Using the assignment operator for the default value specification
       l_favorite_flavor VARCHAR2(100) := 'Anything with chocolate, actually';
 
       -- Two-step declaration process for associative array.
       -- First, the type of table:
       TYPE list_of_books_t IS TABLE OF book%ROWTYPE INDEX BY BINARY_INTEGER;
 
       -- And now the specific list to be manipulated in this block:
       oreilly_oracle_books list_of_books_t;
 
聲明常量
    name CONSTANT datatype [NOT NULL] := | DEFAULT default_value;
example:
    DECLARE
       -- The current year number; it's not going to change during my session.
       l_curr_year CONSTANT PLS_INTEGER :=
          TO_NUMBER (TO_CHAR (SYSDATE, 'YYYY'));
 
       -- Using the DEFAULT keyword
       l_author CONSTANT VARCHAR2(100) DEFAULT 'Bill Pribyl';
 
       -- Declare a complex datatype as a constant
       -- this isn't just for scalars!
       l_steven CONSTANT person_ot :=
         person_ot ('HUMAN', 'Steven Feuerstein', 175, '09-23-1958');
 
指示器聲明
兩種形式
    variable name type attribute%TYPE [optional default value assignment];
    variable name table_name | cursor_name%ROWTYPE [optional default value
    assignment];
example:
    l_company_id company.company_id%TYPE;
 
    DECLARE
       l_book book%ROWTYPE;
    BEGIN
       SELECT * INTO l_book
          FROM book
        WHERE isbn = '1-56592-335-9'
       process_book (l_book);
    END;
 
    DECLARE
       CURSOR book_cur IS
          SELECT author, title FROM book
           WHERE isbn = '1-56592-335-9'
 
       l_book book_cur%ROWTYPE;
    BEGIN
       OPEN book_cur;
       FETCH book_cur INTO l_book;
       process_book (l_book);
    END;
 
不能使用類型指示器的情況(NOT NULL)
    DECLARE
       max_available_date DATE NOT NULL :=
                L*_DAY (ADD_MONTHS (SYSDATE, 3));
       last_ship_date max_available_date%TYPE;
以上代碼將產生編譯錯誤:
   PLS_00218: a variable declared NOT NULL must have an initialization assignment.
 
4.自定義子數據類型
兩種形式:限制型/非限制型
限制型舉例:
    SUBTYPE POSITIVE IS BINARY_INTEGER RANGE 1 .. 2147483647;
非限制型舉例:
    SUBTYPE FLOAT IS NUMBER;
    CREATE OR REPLACE PACKAGE utility
    AS
       SUBTYPE big_string IS VARCHAR2(32767);
       SUBTYPE big_db_string IS VARCHAR2(4000);
    END utility;
 
5.類型轉換
隱式轉換:pl/sql運行時引擎自動轉換
example:
有效的隱式轉換
    DECLARE
       a_number NUMBER;
    BEGIN
       a_number := '125';
    END;
以下隱式轉換將產生異常:
    DECLARE
       a_number NUMBER;
    BEGIN
       a_number := 'abc';
    END;
隱式轉換的可轉換性表格:
顯示轉換:代碼中利用轉換函數執行轉換
example:
    DECLARE
       hd_display VARCHAR2 (30);
    BEGIN
       hd_display := CAST (SYSDATE AS VARCHAR2);
    END;
類型轉換函數列表:
ASCIISTR,CAST,CHARTOROWID,CONVERT,FROM_TZ,HEXTORAW,MULTISET,NUMTODSINTERVAL,NUMTOYMINTERVAL,RAWTOHEX, RAWTONHEX,REFTOHEX,ROWIDTOCHAR, ROWIDTONCHAR,TABLE,THE,TO_BINARY_FLOAT,TO_BINARY_DOUBLE,TO_CHAR, TO_ NCHAR (number version),TO_CHAR, TO_NCHAR (date version),TO_CHAR, TO_NCHAR (character version),TO_BLOB,TO_CLOB, TO_NCLOB,TO_DATE,TO_DSINTERVAL,TO_LOB,TO_MULTI_BYTE,TO_NUMBER,TO_RAW,TO_SINGLE_BYTE,TO_TIMESTAMP,TO_TIMESTAMP_TZ,TO_YMINTERVAL,TRANSLATE ... USING,UNISTR
 
CAST函數對個數據類型的可轉換性
 
 
 
收藏助手
不良信息举报
您举报文章:《oracle pl/sql programming》 第7章 working with data
举报原因:
原因补充:

(最多只允许输入30个字)