Oracle Unleashed- P24

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

Thêm vào BST

Báo xấu

57
lượt xem 4
download

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

Oracle Unleashed- P24: 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- P24

expression Can be any valid Oracle Basic variable or expression. The return value is of the Long data type. You use the CSNG function to convert a string or expression into a value of data type Single. CSNG ( expression ) expression Can be any valid Oracle Basic variable or expression. The return value is of the Single data type. You use the CSTR function to convert a string or expression into a value of data type String. CSTR ( expression ) expression Can be any valid Oracle Basic variable or expression. The return value is of the String data type. You use the CVDATE function to convert a string or expression into a value of data type Date. CVDATE ( expression ) expression Can be any valid Oracle Basic variable or expression. The return value is of the Date data type. You use the FIX function to convert a numeric expression into an integer by truncating its fractional part. FIX ( expression ) expression Can be any valid Oracle Basic variable or expression. The return value is of the Integer data type.
You use the INT function to convert a numeric expression into an integer by rounding to the nearest value. The rounding operation uses the absolute value of the expression to perform the round operation and then reapplies the sign of the original expression to the rounded value. INT ( expression ) expression Can be any valid Oracle Basic variable or expression. The return value is of the Integer data type. You use the STR function to convert a number or numeric expression into a value of data type String. STR provides a leading space for positive numbers. STR ( expression ) expression Can be any valid Oracle Basic variable or expression. The return value is of the String data type. Date Functions You use the DATE function to return the current system date of your operating system as a String data type. No arguments are required. DATE () The return value is of the String data type. You use the DATEADD function to return a time or date value as the result of adding any number of time intervals to a date variable or expression. DATEADD ( time_interval, num_interval, date ) time_interval A string expression that indicates the type of time interval to add to the date. s or S—Second n or N—Minute h or H—Hour
d or D—Day y or Y—Day of year ww or WW—Week w or W—Weekday m or M—Month q or Q—Quarter yyyy or YYYY—Year num_interval The number of time intervals to add to the specified date. date The date to add the time intervals to. The return value is of the Date data type. You use the DATEDIFF function to return the number of time intervals between two specified dates. DATEDIFF ( time_interval, first_date, second_date ) time_interval A string expression that indicates the type of time interval to add to the date. first_date A valid Oracle Basic date variable or expression. second_date A valid Oracle Basic date variable or expression. The return value is of the Date data type. You use the DATEPART function to return the portion of a date value that corresponds to a specified time interval. DATEPART ( time_interval, date ) time_interval A string expression that indicates the type of time interval to add to the date.
date A valid Oracle Basic date variable or expression. The return value is of the Date data type. You use the DATESERIAL function to return the complete date with integer arguments representing year, month, and day. DATESERIAL ( year, month, day ) year An integer representing the year. month An integer representing the month. day An integer representing the day. The return value is of the Date data type. You use the DAY function to return a long value between 1 and 31 representing the day portion of the specified date value. DAY ( date ) date A valid Oracle Basic date variable or expression. The return value is of the Long data type. You use the HOUR function to return an integer value between 0 (12:00 a.m.) and 23 (11:00 p.m.) representing the hour portion of the specified date value. HOUR ( date ) date A valid Oracle Basic date variable or expression. The return value is of the String data type. You use the MINUTE function to return an integer value between 0 and 59 representing the minute portion of the specified date value.
MINUTE ( date ) date A valid Oracle Basic date variable or expression. The return value is of the Integer data type. You use the MONTH function to return an integer value between 1 and 12 representing the month portion of the specified date value. MONTH ( date ) date A valid Oracle Basic date variable or expression. The return value is of the Integer data type. You use the NOW function to return the current system date of your operating system as a Date data type. No arguments are required. NOW () The return value is of the Date data type. You use the SECOND function to return an integer value between 0 and 59 representing the second portion of the specified date value. SECOND ( date ) date A valid Oracle Basic date variable or expression. The return value is of the Integer data type. You use the SYSDATE function to return the current system date of your operating system as a Date data type. It is identical to the NOW function. No arguments are required. SYSDATE () The return value is of the Date data type. You use the TIME function to return the current system time of your operating system as a String data type. No arguments are required.
TIME () The return value is of the String data type. You use the TIMESERIAL function to return the complete time with integer arguments representing hour, minute, and second. TIMESERIAL ( hour, minute, second ) hour An integer representing the hour. minute An integer representing the minute. second An integer representing the second. The return value is of the Date data type. You use the TIMEVALUE function to convert a string into a date return value. TIMEVALUE ( time_value ) time_value A valid Oracle Basic date variable or expression representing a time value. The return value is of the Date data type. You use the WEEKDAY function to return an integer value between 1 (Sunday) and 7 (Saturday) representing the day of the week portion of the specified date value. WEEKDAY ( date ) date A valid Oracle Basic date variable or expression. The return value is of the Integer data type. You use the YEAR function to return an integer between 99 and 10,000, inclusive, that represents the year portion of the specified date value. YEAR ( date )
date A valid Oracle Basic date variable or expression. The return value is of the Integer data type. File Input and Output Functions You use the CURDIR function to return the current path for the default drive (volume). CURRDIR [[(] vol_id [)]] vol_id A string representing the drive or volume to search. The return value is of the String data type. You use the EOF function to test if the file pointer is at the end-of-file marker for the indicated file handle. EOF ( filenum ) filenum An integer number between 1 and 256 representing a file handle as a result of opening a file using the OPEN command. The return value is of the Integer data type. You use the FREEFILE function to return an integer value between 1 and 256, inclusive, representing the next unused file number. No arguments are required. FREEFILE () The return value is of the Integer data type. You use the INPUT function to return a string of characters read from a file opened in the input or binary mode. INPUT ( n, filenum ) n The number of characters to read from the file. filenum An integer number between 1 and 256 representing a file handle as a result of opening a file using the OPEN command. The return value is of the String data type.
You use the LOC function to return the current location of the file pointer for the indicated file handle. LOC ( filenum ) filenum An integer number between 1 and 256 representing a file handle as a result of opening a file using the OPEN command. The return value is of the Positive Integer data type. You use the LOF function to return the length (in bytes) of the specified file handle. LOF ( filenum ) filenum An integer number between 1 and 256 representing a file handle as a result of opening a file using the OPEN command. The return value is of the Positive Integer data type. You use the SEEK function to return the position of the next input or output operation for the specified file handle. SEEK ( filenum ) filenum An integer number between 1 and 256 representing a file handle as a result of opening a file using the OPEN command. The return value is of the Long data type between 1 and 2,147,483,647. Financial Functions You use the DDB function to return the amount of an asset's depreciation for a single, specified period using the double- declining balance method. DDB ( asset_cost, salvage, life, period ) asset_cost The initial value paid for the asset. salvage The projected value of the asset at the end of its life expectancy. life The length of time the asset is expected to be useful.
period The length of time to calculate the asset's depreciation over. The return value is of the Double data type. You use the FV function to return the future value of an asset, such as an annuity, using a constant interest rate and constant periodic payments. FV ( rate, periods, payment, pv, due_end ) rate The fixed interest rate you estimate that you will be paying over the life of the annuity. periods The total number of payment periods over the life of the annuity. payment The fixed amount of the periodic payment. pv The amount of the annuity. due_end 0 indicates that payments are due at the end of each period. 1 indicates that payments are due at the beginning of each period. The return value is of the Double data type. You use the IPMT function to return the amount applied to interest from a specified periodic payment on an annuity at a fixed interest rate and fixed periodic payments. IPMT ( rate, which_period, periods, pv, fv, due_end ) rate The fixed interest rate you estimate that you will be paying over the life of the annuity. which_period The period to calculate the amount applied to principal. periods The total number of payment periods over the life of the annuity. pv The amount of the annuity. fv The amount of the annuity to be unpaid at the end of the life of the annuity. due_end 0 indicates that payments are due at the end of each period. 1 indicates that payments are due at the beginning of each period.
The return value is of the Double data type. You use the IRR function to return the internal rate of return for an investment, which is its rate of return based on a series of periodic cash flows, both payments and receipts. IRR ( cash_flow_array, estimate ) An array that contains the values of a series of payments and receipts over the life of an investment. cash_flow_array There must be at least one payment and one receipt. estimate The fixed interest rate you estimate that you will be using over the life of the cash flow. The return value is of the Double data type. You use the MIRR function to return the modified internal rate of return on an investment, which is the rate of return represented by a series of periodic cash flows (payments and receipts) when the interest rates for payments and receipts are different. MIRR ( cash_flow_array, borrow_rate, reinvest_rate ) An array that contains the values of a series of payments and receipts over the life of an investment. cash_flow_array There must be at least one payment and one receipt. borrow_rate The interest rate you pay to borrow money. reinvest_rate The interest rate you expect to earn when reinvesting cash received. The return value is of the Double data type. You use the NPER function to return the number of periods for an annuity with fixed interest rates and fixed periodic payments. NPER ( rate, periods, pv, fv, due_end ) rate The fixed interest rate you estimate that you will be paying over the life of the annuity. periods The total number of payment periods over the life of the annuity. pv The amount of the annuity.
fv The amount of the annuity to be unpaid at the end of the life of the annuity. due_end 0 indicates that payments are due at the end of each period. 1 indicates that payments are due at the beginning of each period. The return value is of the Double data type. You use the NPV function to return the net present value of an investment using a constant discount rate and a series of cash flow values (payments and receipts). NPV ( discount_rate, cash_flow_array ) discount_rate The discount rate that applies over the whole period being considered. cash_flow_array An array that contains the values of a series of payments and receipts over the life of an investment. There must be at least one payment and one receipt. The return value is of the Double data type. You use the PMT function to return the payment for an annuity with a fixed interest rate and fixed periodic payment. PMT ( rate, periods, pv, fv, due_end ) rate The fixed interest rate you estimate that you will be paying over the life of the annuity. periods The total number of payment periods over the life of the annuity. pv The amount of the annuity. fv The amount of the annuity to be unpaid at the end of the life of the annuity. due_end 0 indicates that payments are due at the end of each period. 1 indicates that payments are due at the beginning of each period. The return value is of the Double data type. You use the PPMT function to return the amount applied to the principal during one payment period for an annuity. PPMT ( rate, which_period, periods, pv, fv, due_end )
rate The fixed interest rate you estimate that you will be paying over the life of the annuity. which_period The period to calculate the amount applied to principal. periods The total number of payment periods over the life of the annuity. pv The amount of the annuity. fv The amount of the annuity to be unpaid at the end of the life of the annuity. due_end 0 indicates that payments are due at the end of each period. 1 indicates that payments are due at the beginning of each period. The return value is of the Double data type. You use the PV function to return the present value of an annuity with a fixed interest rate and fixed periodic payments. PV ( rate, periods, payment, fv, due_end ) rate The fixed interest rate you estimate that you will be paying over the life of the annuity. periods The total number of payment periods over the life of the annuity. payment The fixed amount of the periodic payment. fv The amount of the annuity to be unpaid at the end of the life of the annuity. due_end 0 indicates that payments are due at the end of each period. 1 indicates that payments are due at the beginning of each period. The return value is of the Double data type. You use the RATE function to return the interest rate per period for an annuity. RATE ( periods, payment, pv, fv, due_end, estimate ) periods The total number of payment periods over the life of the annuity. payment The fixed amount of the periodic payment.
pv The amount of the annuity. fv The amount of the annuity to be unpaid at the end of the life of the annuity. 0 indicates that payments are due at the end of each period. 1 indicates that payments are due at the due_end beginning of each period. estimate The fixed interest rate you estimate that you will be paying over the life of the annuity. The return value is of the Double data type. You use the SLN function to return the depreciation of an asset's value for a single period using the straight-line method. SLN ( asset_cost, salvage, life ) asset_cost The initial value paid for the asset. salvage The projected value of the asset at the end of its life expectancy. life The length of time the asset is expected to be useful. The return value is of the Double data type. You use the SYD function to return the depreciation of an asset for a specified period using the sum-of-years-digits method. SYD ( asset_cost, salvage, life, period ) asset_cost The initial value paid for the asset. salvage The projected value of the asset at the end of its life expectancy. life The length of time the asset is expected to be useful. period The length of time to calculate the asset's depreciation over. The return value is of the Double data type.
General Functions You use the ENVIRON function to return the string that corresponds to a specified Windows operating system environment variable. This function returns NULL on the Macintosh. ENVIRON ( environment ) environment A string variable or expression that is an environment variable, such as PATH. The return value is of the String data type. You use the HEX function to return a string value representing the hexadecimal value (base 16) for a decimal or numeric expression (base 10). HEX ( numeric_expression ) numeric_expression Any valid Oracle Basic number or numeric expression. The return value is of the String data type. You use the OCT function to return a string value representing the octal value (base 8) for a decimal or numeric expression (base 10). OCT ( numeric_expression ) numeric_expression Any valid Oracle Basic number or numeric expression. The return value is of the String data type. You use the PAGENUM function to return the number of the page being printed from within a report. No arguments are required. PAGENUM () The return value is of the Integer data type. You use the RND function to return a single precision number in the range 0
ctrl_num A number or numeric expression that controls the value returned by the random number generator. ctrl_num < 0 always returns the same number. ctrl_num = 0 returns the last number. ctrl_num > 0 returns the next number. ctrl_num omitted returns the next number. The return value is of the Single data type. You use the SPC function to return the specified number of space characters. You can only use this function in the PRINT # command. SPC ( count ) count The number of spaces to generate. The return value is of the String data type. You use the SYSTEMNAME function to return the name of the operating system on which Power Objects is executed. Possible values are Windows and Macintosh as string values. No arguments are required. SYSTEMNAME () The return value is of the String data type. You use the TIMER function to return a long integer representing the number of seconds that have elapsed since midnight on the current day. No arguments are required. TIMER () The return value is of the Long data type. Mathematical Functions You use the ABS function to return the absolute value of a number or numeric expression. ABS ( numeric_expression )
numeric_expression Any valid Oracle Basic number or numeric expression. The return value is of the Positive Integer data type. You use the ATN function to return the arctangent (in radians) of a number or numeric expression. ATN ( numeric_expression ) numeric_expression Any valid Oracle Basic number or numeric expression. The return value is of the Double data type. You use the COS function to return the cosine of an angle represented in radians. COS ( numeric_expression ) numeric_expression Any valid Oracle Basic number or numeric expression. The return value is of the Double data type. You use the EXP function to return the value of e (the natural logarithm, 2.71828) raised to the specified power. EXP ( numeric_expression ) numeric_expression Any valid Oracle Basic number or numeric expression. The return value is of the Double data type. You use the LOG function to return the natural logarithm of a positive number or positive numeric expression. LOG ( numeric_expression ) numeric_expression Any valid Oracle Basic number or numeric expression. The return value is of the Double data type.
You use the SIN function to return the sine of an angle represented in radians. SIN ( numeric_expression ) numeric_expression Any valid Oracle Basic number or numeric expression. The return value is of the Double data type. You use the SQR function to return the square root of a positive number or positive numeric expression. SQR ( numeric_expression ) numeric_expression Any valid Oracle Basic number or numeric expression. The return value is of the Double data type. You use the TAN function to return the tangent of an angle represented in radians. TAN ( numeric_expression ) numeric_expression Any valid Oracle Basic number or numeric expression. The return value is of the Double data type. Selection Functions You use the CHOOSE function to return an expression as the result of the value of an indexed expression. CHOOSE ( index, expression [, expression ...] ) A numeric expression that evaluates to a number between 1 and the number of expression in the choose index list. expression Can be any valid Oracle Basic variable or expression. This function returns the data type of the resulting expression.
You use the IIF function to return a given value when a test condition evaluates to true and another value when it evaluates to false. IIF ( expression, val_true, val_false ) expression Can be any valid Oracle Basic variable or expression. val_true Value to return if expression is true. val_false Value to return if expression is false. This function returns the data type of the resulting true or false value. You use the SWITCH function to return the value of the expression associated with the first test expression that evaluates to true. SWITCH ( expression, result [, expression, result ...] ) A valid Oracle Basic expression to evaluate. You can nest up to seven expressions. Each is evaluated in expression left-to-right order. result The value to be returned if its corresponding expression evaluates to true. The return value is of the same data type as the data type of the result. SQL Functions You use the SqlErrClass function to return the error classification for the most recent EXEC SQL operation. No arguments are required. SqlErrClass () The return value is of the Integer data type. You use the SqlErrCode function to return the error code for the most recent EXEC SQL operation. No arguments are required. SqlErrCode () The return value is of the Long data type. You use the SqlErrText function to return the error text for the most recent EXEC SQL operation. No arguments are required.
SqlErrText () The return value is of the String data type. You use the SqlLookup function to provide a single value as the result of a custom EXEC SQL method. This function is most often used as a derived value for data-bound fields. SqlLookup ( [session_name,] sql_statement ) session_name The name of a database session as an object reference. This cannot be a String data type. sql_statement Any valid SQL statement. This function returns the value specified in the SQL statement using its comparable Oracle Basic data type. You use the SqlRowCount function to return the row count for the most recent EXEC SQL operation. No arguments are required. SqlRowCount () The return value is of the Integer data type. String Functions You use the ASC function to return the ANSI code for the first character of a string or string expression. ASC ( string_expression ) string_expression A string expression, constant, or variable that can be interpreted as a string. The return value is of the Integer data type. You use the CHR function to return the character as a string that corresponds to the ANSI code specified. CHR ( charcode ) charcode ANSI code for a character to be repeated. The return value is of the String data type.
You use the FORMAT function to convert a numeric, string, or date expression into a string value and format it according to the specified format string. FORMAT ( NumDateString, format_string ) NumDateString A number, date, or string variable or expression that will be formatted according to the format_string. format_string A string of Oracle Basic formatting characters. For more information, refer to the Oracle Power Objects online help under the topic format and the subtopic Format Mask Characters. The return value is of the String data type. You use the INSTR function to return the integer offset of a search string within a test string. INSTR ( [start,] search_str, search_test ) start The character offset within the search string to begin searching. search_str The string to be searched. search_test The substring that is being searched for. The return value is of the Integer data type. You use the LCASE function to return a string with all the uppercase letters converted to lowercase. LCASE ( string_expression ) string_expression A string expression, constant, or variable that can be interpreted as a string. The return value is of the String data type. You use the LEFT function to return the specified number of characters starting at the left of the string. LEFT ( string_expression, len ) string_expression A string expression, constant, or variable that can be interpreted as a string.