Oracle Unleashed- P3

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

Thêm vào BST

Báo xấu

62
lượt xem 4
download

Oracle Unleashed- P3

Mô tả tài liệu

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Oracle Unleashed- P3: When I first started using Oracle many years ago, it was possible to know the database and the tools available. With the rash of recent releases of different options for the database and the spate of new tools, only people who wear their underpants over their trousers will be able to know everything there is to know about the Oracle products.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Oracle Unleashed- P3

a cursor parameter, whose value is set when the cursor opens, only during the cursor's declared SQL query. Flexibility within cursor parameters enables the developer to pass different numbers of parameters to a cursor by using the parameter default mechanism. This is illustrated in the following example: CURSOR c_line_item (order_num INTEGER DEFAULT 100, line_num INTEGER DEFAULT 1) IS ... By using the INTEGER DEFAULT declaration, you can pass all, one, or none of the parameters to this cursor depending on the logic flow of your code. Creating Cursor Packages A cursor package is similar to a procedure package in that you specify the cursor and its return attribute, %TYPE or % ROWTYPE, in the package specification area. You then specify the cursor "body" in the package body specification area. Packaging a cursor in this manner gives you the flexibility of changing the cursor body without having to recompile applications that reference the packaged procedure. The following is a cursor package example: CREATE OR REPLACE PACKAGE order_total AS CURSOR c_line_item RETURN line_item.merch_gross%TYPE; ... END order_total; CREATE OR REPLACE PACKAGE BODY order_total AS CURSOR c_line_item RETURN line_item.merch_gross%TYPE SELECT merch_gross FROM line_item WHERE order_num = g_order_num; ... END order_total; In this example, the RETURN variable is the same as the line_item.item_merch_gross column. You can use the % ROWTYPE attribute to specify a RETURN record that mirrors a row in a database table. Procedure Variables The most important feature of any language is how to define variables. Once you've defined the variables, PL/SQL enables you to use them in SQL statements as well as language statements. Definition of constants within PL/SQL Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
follow the same rules. Also, you can define variables and constants as local to one subprogram or global to the entire package you are creating. You must declare variables and constants before referencing them in any other statement. Variable Declaration and Assignment Any PL/SQL or SQL data type is valid for variable definitions. The most commonly used data types are VARCHAR2, DATE, NUMBER (SQL data types), BOOLEAN, and BINARY_INTEGER (PL/SQL data types). PL/SQL scalar and composite data types are discussed in more detail later in this chapter. Local Variables Assume you want to declare two local variables named merch_gross and recip_count. The first, merch_gross, is to hold a ten-digit, floating-point number rounded to two decimal places; recip_count will hold an integer counter. Declare these variables as follows: merch_gross NUMBER; recip_count BINARY_INTEGER; You can also declare merch_gross in this example as NUMBER(10,2) to explicitly show total digits and rounding. However, if it's related to a database field, a declaration of this type must change if the database definition changes. You can use two methods to assign values to variables. The first is using an assignment operator as follows: merch_gross := 10.50; The second method is to use a SQL SELECT or FETCH statement that assigns a database value as follows: SELECT merch_gross INTO merch_gross FROM line_item WHERE order_num = g_order_num; Local Constants Constant declaration is similar to variable declaration except that the CONSTANT keyword must follow the variable name. You must immediately assign a value to the CONSTANT. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
tax_rate CONSTANT NUMBER := 0.03; Global Variables Global variables are defined in the same manner as local variables, but they are defined outside of all procedure definitions. Suppose you want to define variables g_order_num and g_recip_counter to be available to all package subprograms. The following is an example of the syntax: CREATE OR REPLACE PACKAGE BODY order_total AS ... g_order_num NUMBER; g_recip_counter BINARY_INTEGER; ... PROCEDURE ... Notice that these global variables are defined in the package body specification area so as not to be "seen" by applications that call the order_total packaged procedure. If you use variable names that are the same as database column names, results are unpredictable when performing any database operations such as SELECT or UPDATE with the variables. DEFAULT Keyword The DEFAULT keyword enables you to initialize variables without using the assignment operator as in the following example: merch_gross NUMBER DEFAULT 10.50; You can also use the DEFAULT keyword to initialize a subprogram's cursor parameters and fields in user-defined records. Variable and Constant Attributes The two attributes of PL/SQL variables and constants are %TYPE and %ROWTYPE. The %TYPE attribute enables you to declare variables similar to database columns without knowing the data type of the column. You can define merch_gross from the previous example as follows: merch_gross line_item.merch_gross%TYPE; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Defining a variable in this manner enables you to put database changes in effect on the next compilation of a PL/SQL procedure without changing the code. The %ROWTYPE attribute enables you to represent a row in a table with a record type that masks the database columns. Consider the sample database information in Table 5.1. Table 5.1. Sample of data in table LINE_ITEM. Column Name Data order_num 100 line_num 1 merch_gross 10.50 recipient_num 1000 You can define a cursor inside your procedure (see "Declaring Cursors" earlier in the chapter) to pull information from the LINE_ITEM table. Along with the cursor, define a ROWTYPE variable to store the fields in this row as follows: CURSOR c_line_item IS SELECT merch_gross, recipient_num FROM line_item WHERE order_num = g_ordnum; li_info c_line_item%ROWTYPE; To retrieve the data, issue a FETCH. FETCH c_line_item INTO li_info; After the FETCH, use dot notation to access the information pulled from the database. g_order_merch_total := g_order_merch_total + li_info.merch_gross; Scalar Data Types PL/SQL supports a wide range of scalar data types for defining variables and constants. Unlike composite data types, scalar data types have no accessible components. These data types fall into one of the following categories: Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
q Boolean q Date/time q Character q Number Now, take a closer look at the data types in each category. Boolean The BOOLEAN data type, which takes no parameters, is used to store a binary value, TRUE or FALSE. This data type can also store the non-value NULL. You cannot insert or retrieve data from an Oracle database using this data type. Date/Time The data type DATE, which takes no parameters, is used to store date values. These DATE values include time when stored in a database column. Dates can range from 1/1/4712 B.C. to 12/31/4712 A.D. Defaults for the DATE data type are as follows: q Date: first day of current month q Time: midnight Character Character data types include CHAR, VARCHAR2, LONG, RAW, and LONG RAW. CHAR is for fixed-length character data, and VARCHAR2 stores variable-length character data. LONG stores variable-length character strings; RAW and LONG RAW store binary data or byte strings. The CHAR, VARCHAR2, and RAW data types take an optional parameter for specifying length. datatype(max_len) This length parameter, max_len, must be an integer literal, not a constant or variable. Table 5.2 shows maximum lengths and database column widths of character data types. Table 5.2. Character data type maximum lengths and database column widths. Data Type Maximum Length Maximum Database Column Width CHAR 32767 255 VARCHAR2 32767 2000 LONG 32760 2147483647 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
RAW 32767 255 LONG RAW 32760 2147483647 From this table, you can see the constraint on inserting CHAR, VARCHAR2, and RAW data into database columns of the same type. The limit is the column width. However, you can insert LONG and LONG RAW data of any length into similar columns because the column width is much greater. Number There are two data types in the number data type category: BINARY_INTEGER and NUMBER. BINARY_INTEGER stores signed integers with a range of -231 to 231-1. The most common use for this data type is an index for PL/SQL tables. Storage for fixed or floating-point numbers of any size is available using the NUMBER data type. For floating-point numbers, you can specify precision and scale in the following format: NUMBER(10,2) A variable declared in this manner has a maximum of ten digits, and rounding occurs to two decimal places. The precision default is the maximum integer supported by your system, and 0 is the default for scale. The range for precision is 1 to 38 whereas the scale range is -84 to 127. Composite Data Types The two composite data types in PL/SQL are TABLE and RECORD. The TABLE data type enables the user to define a PL/SQL table to be used for array processing. The RECORD data type enables the user to go beyond the %ROWTYPE variable attribute; with it, you specify user-defined fields and field data types. Array Processing The TABLE composite data type provides the developer a mechanism for array processing. Although it's limited to one column of information per PL/SQL table, you can store any number of rows for that column. The word from Oracle is that future versions of PL/SQL will provide more flexibility in the use of tables. In the order_total example, define a PL/SQL table named g_recip_list (the information will be used globally). The following is an illustration of this concept: TYPE RecipientTabTyp IS TABLE OF NUMBER(22) INDEX BY BINARY_INTEGER; ... g_recip_list RecipientTabTyp; To initialize an array, you must first define an array name or TYPE, which in this example is RecipientTabTyp. This TABLE column is defined as NUMBER with a maximum of 22 digits. You can define the column as any valid PL/SQL data type; however, the primary key, or INDEX, must be of type BINARY_INTEGER. After defining the array structure, you can make reference for variable definition as shown with g_recip_list defined as an array of TYPE Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
RecipientTabTyp. Building Arrays Arrays are available as information stores subsequent to initialization of the array. To store information in the array g_recip_list that was defined in the last example, you simply reference the array with a numeric value. This is shown in the following example: g_recip_list(j) := g_recipient_num(i) In this example, i and j are counters with values 1. . .n. Once information is stored in an array, you can access it, also with numeric values, as shown in the example. In this case, rows of g_recipient_num are referenced for storage in g_recip_list. Referencing an uninitialized row in a PL/SQL array causes a NO_DATA_FOUND error (see the section "Exception Handling" later in this chapter). Record Processing The RECORD composite data type provides the developer a mechanism for record processing as described previously. Although you cannot initialize TABLEs at the time of declaration, you can with RECORDs, as illustrated in the following example: TYPE LineRecTyp IS RECORD (merch_gross NUMBER := 0, recip_num NUMBER := 0 ); ... li_info LineRecTyp; Defining a RECORD of TYPE LineRecTyp allows declarations such as li_info of that TYPE as shown. You can use this method of RECORD declaration in place of the li_info declaration in the previous %ROWTYPE example. As with % ROWTYPE, references to RECORD information is accomplished with dot notation. g_order_merch_total := g_order_merch_total + li_info.merch_gross; You can use one of three methods to assign values to records. First, you can assign a value to a record field as you would assign any variable. li_info.merch_gross := 10.50; A second method is to assign all fields at once by using two records that are declared with the same data type. Assume a second LineRecTyp is defined as new_li_info. new_li_info := li_info; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
This statement assigns all fields of new_li_info the values from the same fields of li_info. You cannot assign records of different types to each other. A third method of assigning values to fields of a record is through SQL SELECT or FETCH statements. OPEN c_line_item; ... FETCH c_line_item INTO li_info; In this case, all fields of li_info are assigned values from the information retrieved by the FETCH of cursor c_line_item. Processing Control Every procedural language has control structures that provide processing of information in a logical manner by controlling the flow of information. Available structures within PL/SQL include IF-THEN-ELSE, LOOP, and EXIT- WHEN. These structures provide flexibility in manipulating database information. Loop Control Use of the LOOP statement provides iterative processing based on logical choices. The basic construct for PL/SQL LOOPs is shown in the following example: LOOP (repetitive processing) END LOOP loop_name; To break out of a loop such as this, you must issue an EXIT or GOTO statement based on some processing condition. If you raise a user-defined exception, the LOOP also terminates. Now, examine three types of PL/SQL loops that expressly define LOOP termination conditions. You can name a loop as shown in the example by using a label such as just before the LOOP statement. Although it's not required, labeling does enable you to keep better track of nested loops. WHILE Loops Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
The WHILE loop checks the status of any PL/SQL expression that evaluates to TRUE, FALSE, or NULL at the start of each processing cycle. The following is an example of the use of WHILE loops: WHILE (expression) LOOP (loop processing) END LOOP; As stated, the program evaluates the expression at the start of each loop cycle. The program performs the loop processing if the expression evaluates to TRUE. A FALSE or NULL evaluation terminates the loop. Iterations through the loop are exclusively determined by the evaluation of the expression. Numeric FOR Loops You can control loop iterations with the use of numeric FOR loops. This mechanism enables the developer to establish a range of integers for which the loop will cycle. The following example from the order_total package illustrates numeric FOR loops: FOR i in 1..g_line_counter LOOP (loop processing) END LOOP recip_list; In this example, loop processing cycles over the range of integers 1 through the value of g_line_counter. The value of the loop index i is checked at the start of the loop and incremented at the end of the loop. When i is one greater than g_line_counter, the loop terminates. Cursor FOR Loops Cursor FOR loops combine cursor control and conditional control for manipulation of database information. The loop index, cursor OPEN, cursor FETCH, and cursor CLOSE are all implicit when using cursor FOR loops. Consider the following example: CURSOR c_line_item IS (sql statement) BEGIN FOR li_info IN c_line_item LOOP (retrieved record processing) END LOOP; END; As shown, the program explicitly declares the c_line_item cursor before its reference in the FOR loop. When the program enters the FOR loop, the code implicitly opens c_line_item and implicitly creates the li_info record as if the Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
following declaration were made: li_info c_line_item%ROWTYPE; Once inside the loop, the program can reference the fields of the li_info record that are assigned values by the implicit FETCH inside the FOR loop. Fields of li_info mirror the row retrieved by the c_line_item cursor. When data is exhausted for the FETCH, c_line_item is implicitly closed. You cannot reference the information contained in li_info outside of the cursor FOR loop. Iterative Control The IF-THEN-ELSE structure provides alternative processing paths that depend on certain conditions. For example, consider merchandise orders with multiple-line items where a list of recipients is built. Using conditional and iterative control to build the recipient list, the code is as follows: PROCEDURE init_recip_list IS recipient_num NUMBER; i BINARY_INTEGER; j BINARY_INTEGER := 1; k BINARY_INTEGER; BEGIN g_out_msg := 'init_recip_list'; FOR i in 1..g_line_counter LOOP IF i = 1 THEN g_recip_list(j) := g_recipient_num(i); j := j + 1; g_recip_list(j) := 0; ELSE FOR k in 1..j LOOP Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
IF g_recipient_num(i) = g_recip_list(k) THEN exit; ELSIF k = j THEN g_recip_list(j) := g_recipient_num(i); j := j + 1; g_recip_list(j) := 0; end IF; end LOOP; end IF; end LOOP recip_list; END; In the order_total example, the subprogram init_recip_list builds a list of unique recipient numbers for calculating additional shipping charges. There is a controlling FOR loop that cycles through each recipient number found on a particular order. The g_recip_list array is initialized with the first recipient number, and subsequent numbers are checked against all unique numbers in g_recip_list until a unique list of all recipients is compiled. Also illustrated in this example is the IF-THEN-ELSE extension ELSIF. This statement provides further conditional control with additional constraint checks within the IF-THEN-ELSE structure. Use of ELSIF also requires a THEN statement in executing logic control. Another example of iterative control is the use of the EXIT-WHEN statement that allows completion of a LOOP once certain conditions are met. Consider the example of exiting a cursor fetch loop: open c_line_item; loop fetch c_line_item into li_info; EXIT WHEN (c_line_item%NOTFOUND) or (c_line_item%NOTFOUND is NULL); In this example, the LOOP is terminated when no more data is found to satisfy the select statement of cursor c_line_item. Use of %NOTFOUND or %FOUND can cause infinite loops if you do not check for these attributes evaluating to NULL on an EXIT-WHEN logical check. Exception Handling Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
PL/SQL exception handling is a mechanism for dealing with run-time errors encountered during procedure execution. Use of this mechanism enables execution to continue if the error is not severe enough to cause procedure termination. The decision to enable a procedure to continue after an error condition is one you have to make in development as you consider possible errors that could arise. You must define the exception handler within a subprogram specification. Errors cause the program to raise an exception with a transfer of control to the exception-handler block. After the exception handler executes, control returns to the block in which the handler was defined. If there are no more executable statements in the block, control returns to the caller. User-Defined Exceptions PL/SQL enables the user to define exception handlers in the declarations area of subprogram specifications. You accomplish this by naming an exception as in the following example: ot_failure EXCEPTION; In this case, the exception name is ot_failure. Code associated with this handler is written in the EXCEPTION specification area as follows: EXCEPTION when OT_FAILURE then out_status_code := g_out_status_code; out_msg := g_out_msg; This exception is defined in the order_total example to capture status and associated data for any NO_DATA_FOUND exceptions encountered in a subprogram. The following is an example of a subprogram exception: EXCEPTION when NO_DATA_FOUND then g_out_status_code := 'FAIL'; RAISE ot_failure; Within this exception is the RAISE statement that transfers control back to the ot_failure exception handler. This technique of raising the exception is used to invoke all user-defined exceptions. System-Defined Exceptions Exceptions internal to PL/SQL are raised automatically upon error. NO_DATA_FOUND from the previous example is a system-defined exception. Table 5.3 is a complete list of internal exceptions. Table 5.3. PL/SQL internal exceptions. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Exception Name Oracle Error CURSOR_ALREADY_OPEN ORA-06511 DUP_VAL_ON_INDEX ORA-00001 INVALID_CURSOR ORA-01001 INVALID_NUMBER ORA-01722 LOGIN_DENIED ORA-01017 NO_DATA_FOUND ORA-01403 NOT_LOGGED_ON ORA-01012 PROGRAM_ERROR ORA-06501 STORAGE_ERROR ORA-06500 TIMEOUT_ON_RESOURCE ORA-00051 TOO_MANY_ROWS ORA-01422 TRANSACTION_BACKED_OUT ORA-00061 VALUE_ERROR ORA-06502 ZERO_DIVIDE ORA-01476 In addition to this list of exceptions, there is a catch-all exception named OTHERS that traps all errors for which specific error handling has not been established. This exception is illustrated in the following example: when OTHERS then out_status_code := 'FAIL'; out_msg := g_out_msg || ' ' || SUBSTR(SQLERRM, 1, 60); This technique is used in the order_total sample procedure to trap all procedure errors other than NO_DATA_FOUND. The information passed back to the caller in out_msg is the subprogram name contained in g_out_msg concatenated with the first 60 characters returned from the SQLERRM function by the SUBSTR function. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Both SQLERRM and SUBSTR are internal PL/SQL functions. You can find a complete list of internal functions later in this chapter. SQLERRM only returns a valid message when called inside an exception handler unless an argument is passed to the function that is a valid SQL error number. The Oracle error code is the first part of the message returned from SQLERRM. Next is the text associated with that Oracle error code. In this manner, all errors encountered during procedure execution are trapped and passed back to the application for debug purposes. The following is a sample return error from the order_total procedure: FAIL: init_line_items ORA-01001: invalid cursor This error message (formatted by the application) reveals an illegal cursor operation in the subprogram init_line_items. The portion of the message returned from SQLERRM begins with the ORA-01001 SQL error code. Another error message is illustrated in the following example: FAIL: calc_ship_charges In this case, the subprogram calc_ship_charges had a NO_DATA_FOUND error. This is determined by the fact that no SQL error messages are concatenated with the message text. Comments Although some people think commenting code is unnecessary, there are two methods you can use to place comments within your PL/SQL procedures. The first is for commenting single lines, and the syntax is shown in the following example: --*************** CREATE PACKAGE ORDER_TOTALING *************** A double dash at the start of the line marks the line as a comment. The second method is used to place a sequence of comment statements in a PL/SQL package. /* The following code generates a list of unique recipient numbers from all recipient numbers for a particular order */ A comment block such as this begins with the /* and ends with the */. You can place single-line and multiple-line comments in any portion of PL/SQL code. PL/SQL blocks that are dynamically compiled in Oracle Precompiler applications do not support use of single- line comments. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Stored Procedures You can store PL/SQL code in the Oracle database with the RDBMS Procedural Database Extension. Advantages of using stored procedures include easier maintenance, decreased application size, increased execution speed, and greater memory savings, to name a few. With this in mind, explore the various techniques for accessing stored procedures in the following sections. Referencing Stored Procedures Another big advantage to using stored procedures is the capability to reference the procedure from many different Oracle applications. You can make reference to stored procedures with other stored procedures, database triggers, applications built with Oracle Precompilers, or Oracle tools such as SQL*Forms. The following example calls the order_total procedure from another procedure: order_total.get_order_total (order_num, status_code, message, merch_gross, shipping, taxes, grand_total); The following example shows the same order_total procedure referenced from PRO*C, an Oracle Precompiler application. EXEC SQL BEGIN order_total.get_order_total ( :order_num, :status_code, :message, :merch_gross, :shipping, :taxes, :grand_total); END; END-EXEC; All parameters in this example to the order_total procedure are Oracle bind variables that you must declare before the reference to the package. The final example illustrates a call to the order_total package from a SQL*Forms application. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
BEGIN ... order_total.get_order_total ( order_num, status_code, message, merch_gross, shipping, taxes, grand_total); ... END; Once again, you must declare all variables passed as parameters before calling the procedure. Calling stored procedures with COMMIT, ROLLBACK, or SAVEPOINT statements from SQL*Forms is prohibited and is discussed later in this chapter. Stored Procedure States After compilation, a stored procedure exists in either a valid or invalid state. If you haven't made any changes to the procedure, it is considered valid and may be referenced. If any subprogram or object referenced within a procedure changes, its state becomes invalid. Only procedures in a valid state are available for reference. Referencing a procedure that is invalid causes Oracle to recompile any and all objects called by the referenced procedure. If the recompilation does not succeed, Oracle returns a run-time error to the caller, and the procedure remains in an invalid state. Otherwise, Oracle recompiles the referenced procedure, and if the recompilation is successful, execution continues. Stored procedures are located in the Oracle SGA after compilation. If the SGA is too small for the user base, the procedure might be swapped out and become invalid with no indication to the caller. The first reference to the procedure after it is swapped out causes a recompilation, returning it to a valid state. Overloading The concept of overloading in PL/SQL relates to the idea that you can define procedures and functions with the same Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
name. PL/SQL does not look only at the referenced name, however, to resolve a procedure or function call. The count and data types of formal parameters are also considered. PL/SQL also attempts to resolve any procedure or function calls in locally defined packages before looking at globally defined packages or internal functions. To further ensure calling the proper procedure, you can use the dot notation as illustrated by previous examples on application references to stored procedures. Prefacing a procedure or function name with the package name fully qualifies any procedure or function reference. Commits The COMMIT statement is available to PL/SQL procedures unless you are calling the procedure from a SQL*Forms application. To enable commits within a procedure called by a SQL*Forms application, you must issue the DDL statement ALTER SESSION ENABLE COMMIT IN PROCEDURE before you invoke the PL/SQL object. Because you cannot issue this command from SQL*Forms, you must create a user exit from which you can issue the ALTER SESSION statement and subsequently call the procedure. The following is an example of calling the order_total procedure from SQL*Forms through a user exit: user_exit('order_totl'); In this case, the order_totl routine of the SQL*Forms user exit references the order_total packaged procedure. Issuing a COMMIT from a PL/SQL procedure that is called from SQL*Forms attempts to commit any changes from the forms application as well. Package STANDARD PL/SQL provides various tools in a package named STANDARD for use by developers. These tools include internal functions and internal exceptions. I previously discussed exception handling and two internal functions, SQLCODE and SQLERRM, that provide information for exception reporting and are only valid in exception handlers. Referencing Internal Functions Internal PL/SQL functions exemplify the concept of overloading with respect to naming procedures and functions. Remember that PL/SQL resolves a procedure or function call by matching the number and data types of formal parameters in the reference and not just by reference name. Consider the two internal functions named TO_NUMBER in the following example: function TO_NUMBER (str CHAR [, fmt VARCHAR2, [, nlsparms] ]) return NUMBER function TO_NUMBER (str VARCHAR2 [, fmt VARCHAR2 [, nlsparms] ]) return NUMBER Both functions are named TO_NUMBER, but the data type of the first parameter is CHAR in the first definition and VARCHAR2 in the second. Optional parameters are the same in both cases. PL/SQL resolves a call to the TO_NUMBER function by looking at the data type of the first parameter. You might also have a user-defined procedure or function named TO_NUMBER. In this case, the local definition takes precedence over the internal function definition. You can still access the internal function, however, by using the dot notation as follows: Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
STANDARD.TO_NUMBER ... As shown, prefacing the TO_NUMBER function call with the name of the PL/SQL package STANDARD references the internal function. Internal Functions The function TO_NUMBER is one example of a PL/SQL internal function. Table 5.4 shows a complete list of PL/SQL internal function categories along with default return values. Table 5.4. Internal function categories and common return values. Category Common Return Value Character VARCHAR2 Conversion None Date DATE Miscellaneous None Number NUMBER Character Functions Although most character functions return a VARCHAR2, some functions return other values. Table 5.5 lists available character functions along with a brief description, argument list, and return value if other than the most likely return value for the set of functions. Optional arguments are enclosed in square brackets. All internal character functions take the following form: function ASCII (char VARCHAR2) return VARCHAR2 Table 5.5. Character functions. Function Description Argument(s) Return Value Returns standard collating code for ASCII char VARCHAR2 NUMBER character. CHR Returns character for collating code. num NUMBER Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CONCAT Returns str2 appended to str1. str1 VARCHAR2, str2 VARCHAR2 Returns str1 with the first letter of each INITCAP word in uppercase and all others in str1 VARCHAR2 lowercase. Returns starting position of str2 in str1. str1 VARCHAR2, Search begins at pos for the nth occurrence. str2 VARCHAR2 INSTR If pos is negative, the search is performed [, pos NUMBER backwards. Both pos and n default to 1. [, n NUMBER]] The function returns 0 if str2 is not found. str1 VARCHAR2, Similar to INSTR except pos is a byte str2 VARCHAR2 INSTRB position. [, pos NUMBER [, n NUMBER]] Returns character count in str and for data str CHAR or LENGTH NUMBER type CHAR; length includes trailing blanks. str VARCHAR2 Similar to LENGTH; returns byte count of str CHAR or LENGTHB NUMBER str including trailing blanks for CHAR. str VARCHAR2 str CHAR or CHAR or LOWER Returns str with all letters in lowercase. str VARCHAR2 VARCHAR2 Left pads str to length len with characters str VARCHAR2 in pad, which defaults to a single blank. LPAD len NUMBER Returns first len characters in str if str is [, pad VARCHAR2] longer than len. Returns str with characters removed up to str VARCHAR2 LTRIM first character not in set; set defaults to a [, set VARCHAR2] single blank. Similar to INITCAP except a sort sequence str VARCHAR2 NLS_INITCAP is specified by nlsparms. [, nlsparms VARCHAR2] Similar to LOWER except a sort sequence str VARCHAR2 NLS_LOWER is specified by nlsparms. [, nlsparms VARCHAR2] Similar to UPPER except a sort sequence is str VARCHAR2 NLS_UPPER specified by nlsparms. [, nlsparms VARCHAR2] Returns str in sort sequence specified by str VARCHAR2 NLSSORT RAW nlsparms. [, nlsparms VARCHAR2] Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Returns str1 with all occurrences of str2 str1 VARCHAR2, REPLACE replaced by str3. If str3 is not specified, all str2 VARCHAR2, occurrences of str2 are removed. [str3 VARCHAR2] str VARCHAR2, Similar to LPAD except str is right padded RPAD len VARCHAR2, NUMBER with len sequence of characters in pad. [, pad VARCHAR2] Similar to LTRIM except trailing str VARCHAR2 RTRIM characters are removed from str after the [, set VARCHAR2] first character not in set. SOUNDEX Returns phonetic representation of str. str VARCHAR2 Returns substring of str starting at pos for str VARCHAR2, length len or to the end of str if len is SUBSTR pos NUMBER omitted. For pos < 0, SUBSTR counts [, len NUMBER] backward from the end of str. str VARCHAR2, Similar to SUBSTR except works on bytes, SUBSTRB pos NUMBER not characters. [, len NUMBER] str VARCHAR2, Replaces all occurrences of set1 with set2 TRANSLATE set1 VARCHAR2, characters in str. set2 CHAR str CHAR or UPPER Returns all letters in uppercase. str VARCHAR2 Conversion Functions Table 5.6 lists available conversion functions along with a brief description, argument list, and return value. Optional arguments are enclosed in square brackets. All internal conversion functions are of the following form: function CHARTOROWID (str VARCHAR2) return ROWID Table 5.6. Conversion functions. Function Description Argument(s) Return Value str CHAR or CHARTOROWID Converts str to type ROWID. ROWID str VARCHAR2 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

CÓ THỂ BẠN MUỐN DOWNLOAD