Pokrewne
- Strona Główna
- James Shore, Shane Warden agile development. filozofia programowania zwinnego pełna wersja
- Prentice Hall The ANSI C Programming Language 2nd ed. by Brian W. Kernighan and Dennis M. Ritchie
- 2 Helion RS 232C Praktyczne programowanie Od Pascala i C do Delphi i Buildera
- [eBook] DirectX 3D Graphics Programming Bible
- Teach Yourself Database Programming with Visual C in 21 Da
- (ebook PDF) Schreiner Object oriented Programming With A
- Advanced 3D Game Programming with DirectX 9
- White Stephen Program (SCAN dal 743)
- Carolyn Hart Smierc na zadanie (2)
- Golding William Wieza (2)
- zanotowane.pl
- doc.pisz.pl
- pdf.pisz.pl
- wrobelek.opx.pl
[ Pobierz całość w formacie PDF ]
.industry_code),'123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','NNNNNNNNNAAAAAAAAAAAAAAAAAAAAAAAAAA') != 'AAANNNN'OR :company.industry_code NOT LIKE '_____99'THENMESSAGE (' Please enter code in format AAANN99.');RAISE FORM_TRIGGER_FAILURE;END IF;With TRANSLATE, I use the search and replace sets to replace all numbers with "N" and all letters with "A".If the user followed the specified format, then TRANSLATE will return AAANNNN.I then include a secondcheck to see if the last two characters of the string are 99.Notice that I used five underscores assingle-character wildcards to make sure the correct number of characters preceded the double nines.I can also use TRANSLATE to support multiple formats on the same character item, and then determineaction in the screen based on the format selected.Suppose that, if a user enters a string in the formatNNNAAA, I need to execute a procedure to generate an industry profile.If the user enters a string in theformat AAA, I need to execute a procedure to calculate annual sales for that specified department.TRANSLATE will easily sort through these formats as follows:formatted_entry :=TRANSLATE (UPPER (:company.criteria),'123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',11.2.5 Verifying String Formats with TRANSLATE 439 [Appendix A] What's on the Companion Disk?'NNNNNNNNNAAAAAAAAAAAAAAAAAAAAAAAAAA');IF formatted_entry = 'NNNAAA'THENgen_ind_profile (:company.criteria);ELSIF formatted_entry = 'AAA'THENcalc_annual_sales (:company.criteria);END IF;If you need to perform pattern or format masking and analysis, TRANSLATE is generally the most efficientand effective solution.11.1 Character Function 12.Date FunctionsDescriptionsCopyright (c) 2000 O'Reilly & Associates.All rights reserved.11.2.5 Verifying String Formats with TRANSLATE 440 Chapter 12441 12.Date FunctionsContents:Date Function DescriptionsDate Function ExamplesThis chapter contains detailed descriptions and extended examples of functions you can use to manipulatedate information in PL/SQL programs.Most applications store and manipulate dates and times.Dates are quite complicated: not only are they highlyformatted, but there are myriad rules for determining valid values and valid calculations (leap days and years,national and company holidays, date ranges, etc.).Fortunately, PL/SQL and the Oracle RDBMS providemany ways to handle date information.PL/SQL provides a true DATE datatype that stores both date and time information.Each date value containsthe century, year, month, day, hour, minute, and second.The DATE datatype does not support the storage offractions of time less than a second in length.The time itself is stored as the number of seconds past midnight.If you enter a date without a time (most applications do not require the tracking of time), the time portion ofthe database value defaults to midnight (12:00:00 AM).PL/SQL validates and stores dates which fall in therange January 1, 4712 B.C.to December 31, 4712 A.D (in Oracle Server 8.0 and higher, the maximum validdate is December 31, 9999).Support for a true date datatype is only half the battle.You also need a language that can manipulate thosedates in a natural and intelligent manner -- as dates.PL/SQL offers a set of eight date functions for just thispurpose, as shown in Table 12.1.With PL/SQL you will never have to write a program which calculates the number of days between two dates.You will not need to write your own utility to figure out the day of the week on which a date falls.Thisinformation, and just about anything else you can think of having to do with dates, is immediately available toyou through built-in functions.The date functions in PL/SQL all take dates, and, in some cases, numbers, forarguments, and all return date values.The only exception is MONTHS_BETWEEN, which returns a number.Table 12.1: The Built-In Date FunctionsName DescriptionADD_MONTHS Adds the specified number of months to a date.LAST_DAY Returns the last day in the month of the specified date.MONTHS_ BETWEEN Calculates the number of months between two dates.NEW_TIME Returns the date/time value, with the time shifted as requested by the specifiedtime zones.NEXT_DAY Returns the date of the first weekday specified that is later than the date.ROUND Returns the date rounded by the specified format unit.SYSDATE Returns the current date and time in the Oracle Server.TRUNC Truncates the specified date of its time portion according to the format unitprovided.12.Date Functions 442 [Appendix A] What's on the Companion Disk?12.1 Date Function DescriptionsThis section describes each date function and includes examples to give you a solid feel for how you can putthe function to use in your programs.NOTE: In the examples in this chapter, a date contained in single quotation marks is acharacter string.PL/SQL converts the string to a true date datatype when it applies thefunction.(This is an implicit conversion.) Date values that are displayed in the formatDD-MON-YYYY and are not contained in single quotation marks represent actual datevalues in the database.A true date value looks like this in the examples:12-DEC-1997A character representation looks like this in the examples:'12-DEC-1997'Remember, a date has its own internal storage format and cannot be viewed or entereddirectly.These examples also assume that the default format mask for dates isDD-MON-YYYY.12.1.1 The ADD_MONTHS functionThe ADD_MONTHS function returns a new date with the specified number of months added to the inputdate.The specification for ADD_MONTHS is as follows:FUNCTION ADD_MONTHS (date_in IN DATE, month_shift NUMBER) RETURN DATEFUNCTION ADD_MONTHS (month_shift NUMBER, date_in IN DATE) RETURN DATEADD_MONTHS is an overloaded function.You can specify the date and the number of months by which youwant to shift that date, or you can list the month_shift parameter first and then the date.Both arguments arerequired.Date ArithmeticPL/SQL allows you to perform arithmetic operations directly on date variables.You may add numbers to adate or subtract numbers from a date.To move a date one day in the future, simply add 1 to the date as shownbelow:hire_date + 1You can even add a fractional value to a date [ Pobierz całość w formacie PDF ]
zanotowane.pl doc.pisz.pl pdf.pisz.pl agnieszka90.opx.pl
.industry_code),'123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','NNNNNNNNNAAAAAAAAAAAAAAAAAAAAAAAAAA') != 'AAANNNN'OR :company.industry_code NOT LIKE '_____99'THENMESSAGE (' Please enter code in format AAANN99.');RAISE FORM_TRIGGER_FAILURE;END IF;With TRANSLATE, I use the search and replace sets to replace all numbers with "N" and all letters with "A".If the user followed the specified format, then TRANSLATE will return AAANNNN.I then include a secondcheck to see if the last two characters of the string are 99.Notice that I used five underscores assingle-character wildcards to make sure the correct number of characters preceded the double nines.I can also use TRANSLATE to support multiple formats on the same character item, and then determineaction in the screen based on the format selected.Suppose that, if a user enters a string in the formatNNNAAA, I need to execute a procedure to generate an industry profile.If the user enters a string in theformat AAA, I need to execute a procedure to calculate annual sales for that specified department.TRANSLATE will easily sort through these formats as follows:formatted_entry :=TRANSLATE (UPPER (:company.criteria),'123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',11.2.5 Verifying String Formats with TRANSLATE 439 [Appendix A] What's on the Companion Disk?'NNNNNNNNNAAAAAAAAAAAAAAAAAAAAAAAAAA');IF formatted_entry = 'NNNAAA'THENgen_ind_profile (:company.criteria);ELSIF formatted_entry = 'AAA'THENcalc_annual_sales (:company.criteria);END IF;If you need to perform pattern or format masking and analysis, TRANSLATE is generally the most efficientand effective solution.11.1 Character Function 12.Date FunctionsDescriptionsCopyright (c) 2000 O'Reilly & Associates.All rights reserved.11.2.5 Verifying String Formats with TRANSLATE 440 Chapter 12441 12.Date FunctionsContents:Date Function DescriptionsDate Function ExamplesThis chapter contains detailed descriptions and extended examples of functions you can use to manipulatedate information in PL/SQL programs.Most applications store and manipulate dates and times.Dates are quite complicated: not only are they highlyformatted, but there are myriad rules for determining valid values and valid calculations (leap days and years,national and company holidays, date ranges, etc.).Fortunately, PL/SQL and the Oracle RDBMS providemany ways to handle date information.PL/SQL provides a true DATE datatype that stores both date and time information.Each date value containsthe century, year, month, day, hour, minute, and second.The DATE datatype does not support the storage offractions of time less than a second in length.The time itself is stored as the number of seconds past midnight.If you enter a date without a time (most applications do not require the tracking of time), the time portion ofthe database value defaults to midnight (12:00:00 AM).PL/SQL validates and stores dates which fall in therange January 1, 4712 B.C.to December 31, 4712 A.D (in Oracle Server 8.0 and higher, the maximum validdate is December 31, 9999).Support for a true date datatype is only half the battle.You also need a language that can manipulate thosedates in a natural and intelligent manner -- as dates.PL/SQL offers a set of eight date functions for just thispurpose, as shown in Table 12.1.With PL/SQL you will never have to write a program which calculates the number of days between two dates.You will not need to write your own utility to figure out the day of the week on which a date falls.Thisinformation, and just about anything else you can think of having to do with dates, is immediately available toyou through built-in functions.The date functions in PL/SQL all take dates, and, in some cases, numbers, forarguments, and all return date values.The only exception is MONTHS_BETWEEN, which returns a number.Table 12.1: The Built-In Date FunctionsName DescriptionADD_MONTHS Adds the specified number of months to a date.LAST_DAY Returns the last day in the month of the specified date.MONTHS_ BETWEEN Calculates the number of months between two dates.NEW_TIME Returns the date/time value, with the time shifted as requested by the specifiedtime zones.NEXT_DAY Returns the date of the first weekday specified that is later than the date.ROUND Returns the date rounded by the specified format unit.SYSDATE Returns the current date and time in the Oracle Server.TRUNC Truncates the specified date of its time portion according to the format unitprovided.12.Date Functions 442 [Appendix A] What's on the Companion Disk?12.1 Date Function DescriptionsThis section describes each date function and includes examples to give you a solid feel for how you can putthe function to use in your programs.NOTE: In the examples in this chapter, a date contained in single quotation marks is acharacter string.PL/SQL converts the string to a true date datatype when it applies thefunction.(This is an implicit conversion.) Date values that are displayed in the formatDD-MON-YYYY and are not contained in single quotation marks represent actual datevalues in the database.A true date value looks like this in the examples:12-DEC-1997A character representation looks like this in the examples:'12-DEC-1997'Remember, a date has its own internal storage format and cannot be viewed or entereddirectly.These examples also assume that the default format mask for dates isDD-MON-YYYY.12.1.1 The ADD_MONTHS functionThe ADD_MONTHS function returns a new date with the specified number of months added to the inputdate.The specification for ADD_MONTHS is as follows:FUNCTION ADD_MONTHS (date_in IN DATE, month_shift NUMBER) RETURN DATEFUNCTION ADD_MONTHS (month_shift NUMBER, date_in IN DATE) RETURN DATEADD_MONTHS is an overloaded function.You can specify the date and the number of months by which youwant to shift that date, or you can list the month_shift parameter first and then the date.Both arguments arerequired.Date ArithmeticPL/SQL allows you to perform arithmetic operations directly on date variables.You may add numbers to adate or subtract numbers from a date.To move a date one day in the future, simply add 1 to the date as shownbelow:hire_date + 1You can even add a fractional value to a date [ Pobierz całość w formacie PDF ]