Oracle查询层次结构数据.docx

1、Oracle查询层次结构数据在 Oracle Database 11g中查询层次结构数据The previous articles in this introductory PL/SQL series focused on working with strings and numbers in PL/SQL-based applications. Without a doubt, strings and numbers are important, but it is certainly a very rare application that does not also rely on da

2、tes. You need to keep track of when events occurred, when people were born, and much more.As a result, you will quite often need to Declare variables and constants for dates Use built-in functions to display and modify date values Perform computations on datesA date is also a considerably more compl

3、ex datatype than a string or a number. It has multiple parts (year, month, day, hour, and so on), and there are many rules about what constitutes a valid date. This article gives you all the information you need in order to begin working with dates in your PL/SQL programs.Dates, Time Stamps, and Int

4、ervals in PL/SQLMost applications require the storage and manipulation of dates and times. Unlike strings and numbers, dates are quite complicated: not only are they highly formatted data, but there are also many rules for determining valid values and valid calculations (leap days and years, dayligh

5、t saving time changes, national and company holidays, date ranges, and so on).Fortunately, Oracle Database and PL/SQL provide a set of true date and time datatypes that store both date and time information in a standard internal format, and they also have an extensive set of built-in functions for m

6、anipulating the date and time.There are three datatypes you can use to work with dates and times: DATEThis datatype stores a date and a time, resolved to the second. It does not include the time zone. DATE is the oldest and most commonly used datatype for working with dates in Oracle applications. T

7、IMESTAMPTime stamps are similar to dates, but with these two key distinctions: (1) you can store and manipulate times resolved to the nearestbillionthof a second (9 decimal places of precision), and (2) you can associate a time zone with a time stamp, and Oracle Database will take that time zone int

8、o account when manipulating the time stamp. INTERVALWhereas DATE and TIMESTAMP record a specific point in time, INTERVAL records and computes a timeduration. You can specify an interval in terms of years and months, or days and seconds.Listing 1 includes example variables whose declaration is based

9、on these datatypes.Code Listing 1:Declaring DATE, TIMESTAMP, and INTERVAL variablesDECLARE l_today_date DATE := SYSDATE; l_today_timestamp TIMESTAMP := SYSTIMESTAMP; l_today_timetzone TIMESTAMP WITH TIME ZONE := SYSTIMESTAMP; l_interval1 INTERVAL YEAR (4) TO MONTH := 2011-11; l_interval2 INTERVAL DA

10、Y (2) TO SECOND := 15 00:30:44;BEGIN null;END;Working with intervals and time stamps with time zones can be very complicated; relatively few developers will need these more advanced features. This article focuses on the core DATE and TIMESTAMP types, along with the most commonly used built-in functi

11、ons.Choosing a datatype.With such an abundance of riches, how do you decide which of these date-and-time datatypes to use? Here are some guidelines: Use one of the TIMESTAMP types if you need to track time down to a fraction of a second. You can, in general, use TIMESTAMP in place of DATE. A time st

12、amp that does not contain subsecond precision takes up 7 bytes of storage, just as a DATE datatype does. When your time stamp does contain subsecond data, it takes up 11 bytes of storage. Use TIMESTAMP WITH TIME ZONE if you need to keep track of the session time zone in which the data was entered. U

13、se TIMESTAMP WITH LOCAL TIME ZONE if you want the database to automatically convert a time between the database and session time zones. Use DATE when its necessary to maintain compatibility with an existing application written before any of the TIMESTAMP datatypes were introduced. Use datatypes in y

14、our PL/SQL code that correspond to, or are at least compatible with, the underlying database tables. Think twice, for example, before reading a TIMESTAMP value from a table into a DATE variable, because you might lose information (in this case, the fractional seconds and perhaps the time zone).Getti

15、ng the current date and time.PL/SQL developers often need to retrieve and work with the current date and time. Most developers use the classic SYSDATE function, but Oracle Database now offers several functions to provide variations of this information, as shown in Table 1.FunctionTime ZoneDatatype R

16、eturnedCURRENT_DATESessionDATECURRENT_TIMESTAMPSessionTIMESTAMP WITH TIME ZONELOCALTIMESTAMPSessionTIMESTAMPSYSDATEDatabase serverDATESYSTIMESTAMPDatabase serverTIMESTAMP WITH TIME ZONETable 1:SYSDATE and other options for working with the current date and timeListing 2 displays the values returned

17、by calls to SYSDATE and SYSTIMESTAMP.Code Listing 2:Calls to SYSDATE and SYSTIMESTAMP and the returned valuesBEGIN DBMS_OUTPUT.put_line (SYSDATE); DBMS_OUTPUT.put_line (SYSTIMESTAMP); DBMS_OUTPUT.put_line (SYSDATE - SYSTIMESTAMP);END;/Here is the output:07-AUG-1107-AUG-11 08.46.16.379000000 AM -05:0

18、0-000000000 00:00:00.379000000Because I have passed dates and time stamps to DBMS_OUTPUT.PUT_LINE, Oracle Database implicitly converts them to strings, using the default format masks for the database or the session (as specified by the National Language Settings NLS_DATE_FORMAT parameter). A default

19、 installation of Oracle Database sets the default DATE format to DD-MON-YYYY. The default TIMESTAMP format includes both the date offset and the time zone offset.Note that it is possible to perform date arithmetic: I subtract the value returned by SYSTIMESTAMP from the value returned by SYSDATE. The

20、 result is anintervalthat isvery close(but not quite equal) to zero.Converting dates to strings and strings to dates.As with TO_CHAR for numbers, you use another version of the TO_CHAR function to convert a date or a time stamp to a string. And, again as with numbers, Oracle Database offers a large

21、set of format elements to help you tweak that string so it appears exactly as you need it. Here are some examples:1. Use TO_CHAR without a format mask. If you do not include a format mask, the string returned by TO_CHAR will be the same as that returned when Oracle Database performs an implicit conv

22、ersion:BEGIN DBMS_OUTPUT.put_line ( TO_CHAR (SYSDATE); DBMS_OUTPUT.put_line ( TO_CHAR (SYSTIMESTAMP);END;/ 07-AUG-1107-AUG-11 08.55.00.470000000 AM -05:002. Use TO_CHAR to display the full names of both the day and the month in the date:BEGIN DBMS_OUTPUT.put_line (TO_CHAR (SYSDATE, Day, DDth Month Y

23、YYY);END;/Sunday , 07TH August 2011Note: The language used to display these names is determined by the NLS_DATE_LANGUAGE setting, which can also be specified as the third argument in the call to TO_CHAR, as inBEGIN DBMS_OUTPUT.put_line ( TO_CHAR (SYSDATE, Day, DDth Month YYYY, NLS_DATE_LANGUAGE=Span

24、ish);END;/Domingo , 07TH Agosto 20113. Answers to the ChallengeHere are the answers to the PL/SQL Challenge questions in last issues “Working with Numbers in PL/SQL” article:Answer 1:The plch_ceil_and_floor function always returns either 1 or 0: 0 if the number passed to the function is an integer,

25、1 otherwise.Answer 2:(a) and (b) are correct; (c) is incorrect.For full explanations of both of these answers, , register or log in, and click theClosed/Takentab in Play a Quiz, or go tobit.ly/r1SwvP.4. Use TO_CHAR to display the full names of both the day and the month in the datebut without all th

26、ose extra spaces in the date-as-string. Oracle Database, by default, pads the string with spaces to match the maximum length of the day or the month. In most situations, you dont want to include that extra text, and Oracle Database offers a format element modifier, FM, to control blank and zero padd

27、ing. In the following block, I prefix the format mask with FM and remove the 0 (before 7) and extra spaces after August:5. 6. BEGIN7. DBMS_OUTPUT.put_line (8. TO_CHAR (SYSDATE, 9. FMDay, DDth Month YYYY);10. END;11. /12. Sunday, 7TH August 2011You can also use the format mask to extract just a porti

28、on of, or information about, the date, as shown in the following examples:1. What quarter is it?2. TO_CHAR (SYSDATE, Q)3. What is the day of the year (1-366) for todays date?4. TO_CHAR (SYSDATE, DDD)5. What are the dateand timeof a DATE variable? (This is a very common requirement, because the defau

29、lt format mask for a date doesnotinclude the time component, which means that asking DBMS_OUTPUT.PUT_LINE to display a date leaves out the time.)BEGIN DBMS_OUTPUT.put_line ( TO_CHAR (SYSDATE, YYYY-MM-DD HH24:MI:SS);END;/You can also use EXTRACT to extract and return the value of a specified element

30、of a date. For example1. What year is it?EXTRACT (YEAR FROM SYSDATE)2. What is the day for todays date?EXTRACT (DAY FROM SYSDATE) To convert a string to a date, use the TO_DATE or the TO_TIMESTAMP built-in function. Provide the string and Oracle Database returns a date or a time stamp, using the def

31、ault format mask for the session:DECLARE l_date DATE;BEGIN l_date := TO_DATE (12-JAN-2011);END ;If the string you provide does not match the default format, Oracle Database will raise an exception:DECLARE l_date DATE;BEGIN l_date := TO_DATE (January 12 2011);END;/ORA-01858: a non-numeric character was found where a numeric was expectedYou should not assume that the literal value you provide in your call to TO_DATE matches the default format. What if the format changes over time? Instead, always provide