Oracle pl/SQL programming study notes

Oracle pl/SQL program design study notes this article is mainly to record some confusing or previous knowledge is not clear enough, just select common knowledge points. I. case statement and case expression 1. case statement and case expression both have two modes: simple and search. Simple Type: case exp when res1 then... res2 then... [else...] (end case | end); search type: case when exp1 then... exp2 then... [else...] (end case | end ). 2. If the case statement does not have a match, an error is returned. If the case expression does not exist, null is returned. The case statement ends with end case; the case expression ends with end. 2. for Loop only involves two types of loops: Numeric subscript and cursor. The upper and lower mark of the numeric type is evaluated only before the first loop (including expression calculation and rounding ). 3. After 11g, The continue [label1] [when exp1] statement is added to execute the next loop. Www.2cto.com IV. Exception Handling 1. sqlcode obtains the current exception number, which is 0.2, and sqlerrm obtains the original exception information in a normal way. The maximum length is 512 bytes. If no parameter is specified, the corresponding information of the current sqlcode is returned. The specified sqlcode is returned when a parameter is set. 3. dbms_utility.format_error_stack can contain more than sqlerrm, up to 1899 bytes. Only information corresponding to the current sqlcode can be returned. 4. dbms_utility.format_error_backtrace is added after 10 Gb. The current exception stack is returned. The process name and code line number of the initial exception are traced back to the current call block. You can quickly locate exceptions. We recommend that you use

Dbms_utility.format_error_stack | dbms_utility.format_error_backtrace method output. if an exception is handled during the call, the following two methods should be thrown: raise_application_error (-20008, dbms_utility.format_error_stack | dbms_utility.format_error_backtrace); -- rebuild the exception stack raise_application_error (-20008, 'currently added information', true); -- only added to the current exception stack, not rebuilt. In other ways, the exception stack is rebuilt, and the previous exception information is lost. Therefore, do not use raise [...] to throw an exception unless you have already handled the previous exception. Www.2cto.com 5, character type 1, varchar2. We recommend that all character types Use this type. The maximum length of pl/SQL is 32767 bytes, and the database contains 4000 bytes after 8 I. 2. nvarchar2, similar to varchar2, uses only the national character set and defines the length in characters by default. The maximum length of pl/SQL is 32767 bytes, And the db contains 4000 bytes after 8i. 3. char, which is not recommended because space filling will cause many unexpected problems and will waste space. The maximum length of pl/SQL is 32767 bytes, and the database contains 2000 bytes after 8 I. 4. nchar, similar to char, only uses the national character set. The default length is measured in characters. The maximum length of pl/SQL is 32767 bytes, and the maximum length of 8 bytes in db is 2000 bytes. 5. Other character types are child types defined to be compatible with ansi SQL. 6. For comparison between the char type and "non-varchar2 type" (such as the literal value and char type), first fill the space with the same length before comparison. If it is compared with the varchar2 type, it is directly compared without filling. Vi. numeric type 1, number, the most basic type, financial precise data must be selected, number (p, s), p (1 ~ 38), s (-84 ~ 127). 2, pls_integer, binary_integer, simple_integer. Dedicated for pl/SQL, binary computing mode. We do not recommend binary_integer after 10 Gb, instead of pls_integer. simple_integer is a new type of 11 GB, which simplifies pls_integer and cannot be null without Overflow check, special predicates are not supported, and the speed is very fast. This is because pl/SQL basically does not require the above support. 3. binary_float, binary_double. Binary calculation mode: Single-precision floating-point and double-precision floating-point. f or d is followed by the literal value. 4. simple_float, simple_double. Dedicated for pl/SQL, binary computing mode, new type after 11 GB, also simplifies binary series type, high efficiency. Www.2cto.com 7, date type 1, date, accurate to second 2, timestamp, accurate to 9 decimal seconds, the default is 6 decimal seconds. 3. timestamp with time zone, timestamp4 and timestamp with local time zone in a fixed time zone, which are converted between the client and server to timestamp.5 and interval year [0 ~ 4] to month, the default year is 26, interval day [0 ~ 9] to second [0 ~ 9]. The default value of day is 2, second is 67, yminterval_unconstrained, and dsinterval_unconstrained is used in the parameters and return values of the process. The default precision is no longer used and no precision is lost. 8. Other data types: raw. Character Set conversion is not performed. The maximum pl/SQL value is 32767, and the maximum db value is 2000 bytes. 2. long and long raw are not recommended, instead of large objects. 3. urowid and rowid, which replace rowid with urowid to store various rowids of the database. 4. Record type, based on the cursor, table type, or custom record type, type... is record (...,...,). 5. Join array, type .. is table... index by pls_integer/varchar2 (..). similar to map6 in java, nested table, type... is table ..., it can be used as a db column. Columns outside the table. Similar to database tables, index values are similar to rowid7, varray, type... is varray of..., which can be used as db columns. Columns in the table. Similar to the array in java, the sequence is retained and the length is fixed. Www.2cto.com 8, bfile, blob, clob, nclob, large object, bfile read-only, the file is outside the database, lob can read and write, the file is in the database, and only the locator is saved in the database column. 9. Subtype definition the subtype definition can be subject to special restrictions on the basic type, or can be anchored to the type. The anchored child type does not inherit the not null and default values of the parent type. The anchor type does not inherit the not null and default values of the database column to be anchor. Author hulubo