perl DBI高级编程
转载自: http://docstore.mik.ua/orelly/linux/dbi/ch06_01.htm
获取查询结果的数据类型。
Chapter 6. Advanced DBI
Contents:
Handle Attributes and Metadata
Handling LONG/LOB Data
Transactions, Locking, and Isolation
This chapter covers some of the more advanced topics of using DBI, including the ability to alter the way in which the database and statement handles function on-the-fly, as well as how to use explicit transaction handling within your database. These topics are not strictly necessary for basic DBI usage, but they contain useful information that will allows you to maximize the potential of your DBI programs.
6.1. Handle Attributes and Metadata
In addition to methods associated with database and statement handles, the DBI also defines attributes for these handles that allow the developer to examine or fine-tune the environment in which the handles operate. Some attributes are unique to either database or statement handles, and some are common to both.
The attribute values of a handle can be thought of as a hash of key/value pairs, and can be manipulated in the same way as you would manipulate an ordinary hash via a reference. Here are a few examples using the AutoCommit attribute:
### Set the database handle attribute "AutoCommit" to 1 (e.g., on)
$dbh->{AutoCommit} = 1;
### Fetch the current value of "AutoCommit" from the handle
$foo = $dbh->{AutoCommit};
Fetching attributes as hash values, rather than as method calls, has the added bonus that the hash lookup can beinterpolated inside double-quoted strings:
### Print the current value of "AutoCommit" from the handle
print "AutoCommit: $dbh->{AutoCommit}\n";
With AutoCommit enabled, that would print:
AutoCommit: 1
as you might expect. Actually, since AutoCommit is a boolean attribute, it would print 1 after any value that Perl considers true had been assigned to it.
After a false value was assigned, you may reasonably expect a 0 to be printed, but you might be surprised to see:
AutoCommit:
That's because Perl uses an internal representation of false that is both a numeric zero and an empty string at the same time. When used in a string context, the empty string is printed. In a numeric context, the zero is used.
When getting or setting an attribute value, the DBI automatically checks that the attribute name you are using and generates an error if it's not known.[55] Similarly, any attempts to set a read-only attribute will result in an error. Be aware, however, that these errors are reported using die() regardless of the setting of the RaiseError attribute, and are thus potentially fatal. That's another good reason to use eval {...} blocks, as we discussed in Chapter 4, "Programming with the DBI ".
[55]Driver-specific attributes, e.g., those that start with a lowercase letter, are a special case. Any get or set of a driver-specific attribute that hasn't been handled by the driver is handled by the DBI without error. That makes life easier for driver developers. On the other hand, you need to take extra care with the spelling.
A statement handle is known as a child, or kid, of its parent database handle. Similarly, database handles are themselves children of their parent driver handle. Child handles inherit some attribute values from parent handles. The rules for this behavior are defined in a common-sense manner and are as follows:
-
A statement handle will inherit (copy) the current values of certain attributes from its parent database handle.
-
If that new statement handle then has its attribute values altered, this affects neither the parent database handle nor any other statement handles. The changes are contained entirely within the altered statement handle.
-
Changes to attributes within a database handle do not affect any of its existing child statement handles. The database handle attribute changes only affect future statement handles created from that database handle.
The DBI specification in Appendix A, "DBI Specification " should be consulted for complete information on which attributes are inherited.
6.1.1. Passing Attributes to DBI Methods
Handles carry with them their set of current attribute values that methods often use to control how they behave. Many methods are defined to also accept an optional reference to a hash of attribute values.
This is primarily an escape mechanism for driver developers and their users, and so does not always work in the way you might think. For example, you might expect this code:
$dbh->{RaiseError} = 1;
...
$dbh->do( $sql_statement, undef, { RaiseError => 0 } ); # WRONG
to turn off RaiseError for the do() method call. But it doesn't! Attribute parameters are ignored by the DBI on alldatabase handle and statement handle method calls. You don't even get a warning that the attribute has been ignored.
If they're ignored, then what's the point in having them? Well, the DBI itself ignores them, but the DBD driver that processed the method call may not. Or then again, it may! Attribute hash parameters to methods are hints to the driver and typically only usefully hold driver-specific attributes.[56]
[56]It's possible that a future version of the DBI may look for certain non-driver-specific attributes, such asRaiseError.
That doesn't apply to the DBI->connect() method call because it's not a driver method, it's a DBI method. Its attribute hash parameter, \%attr , is used to set the attributes of the newly created database handle. We gave some examples usingRaiseError in Chapter 4, "Programming with the DBI ", and we give more in the following section.
6.1.2. Connecting with Attributes
One of Perl's many catch phrases is "there's more than one way to do it," and the DBI is no exception. In addition to being able to set attributes on a handle by simple assignment and by the attribute parameter of the connect() method (as shown earlier), the DBI provides another way.
You can include attribute assignments in the data source name parameter of the connect() method. For example:
$dbh = DBI->connect( "dbi:Oracle:archaeo", "username", "password" , {
RaiseError => 1
});
can also be expressed as:
$dbh = DBI->connect( "dbi:Oracle(RaiseError=>1):archaeo", '', '');
You can't have any space before the opening parenthesis or after the closing one before the colon, but you can have spaces within the parentheses. You can also use just = instead of => if you prefer. If you want to set more than one attribute then use a comma to separate each one.
The attribute settings in the data source name parameter take precedence over those in the attribute parameter. This can be very handy when you want to override a hardcoded attribute setting, such as PrintError. For example, this code will leave PrintError on:
$dbh = DBI->connect( "dbi:Oracle(PrintError=>1):archaeo", '', '', {
PrintError => 0
});
But what's the point of just hardcoding the attribute setting in two different places? This example is not very useful as it stands, but we could let the application accept the data source name parameter from the command line as an option, or leave it empty and use the DBI_DSN environment variable. That makes the application much more flexible.
6.1.3. The Significance of Case
You may have noticed that some attribute names use all uppercase letters, like NUM_OF_FIELDS, while others use mixed case letters, like RaiseError. If you've seen any descriptions of individual database drivers you may have also noticed some attribute names that use all lowercase letters, like ado_conn and ora_type.
There is a serious method behind the apparently inconsistent madness. The letter case used for attribute names is significant and plays an important part in the portability of DBI scripts and the extensibility of the DBI itself. The letter case of the attribute name is used to signify who defined the meaning of that name and its values, as follows:
UPPER_CASEAttribute names that use only uppercase letters and underscores are defined by external standards, such as ISO SQL or ODBC.
The statement handle TYPE attribute is a good example here. It's an uppercase attribute because the values it returns are the standard portable datatype numbers defined by ISO SQL and ODBC, and not the nonportable native database datatype numbers.
MixedCaseAttribute names that start with an uppercase letter but include lowercase letters are defined by the DBI specification.
lower_case-
Attribute names that start with a lowercase letters are defined by individual database drivers. These are known asdriver-specific attributes.
Because the meanings are assigned by driver authors without any central control, it's important that two driver authors don't pick the same name for attributes with different behaviors. To ensure this, driver-specific attributes all begin with a prefix that identifies the particular driver. For example, DBD::ADO attributes all begin with ado_ ,DBD::Informix attributes begin with ix_, etc.
For example, most drivers provide a driver-specific version of the statement handle TYPE attribute that returns the native database datatype numbers instead of the standard ones. DBD::Oracle calls it ora_type, DBD::Ingres calls iting_ingtype, and DBD::mysql calls it mysql_type. The prefix also makes it easier to find driver-specific code in applications when maintaining them.
Driver-specific attributes play an important role in the DBI. They are an escape valve. They let drivers expose more of the special functionality and information that they have available without having to fit it inside the fairly narrow DBI straitjacket.
6.1.4. Common Attributes
Common attributes are those that can be queried and set within both database and statement handles. This section discusses some of the most commonly used attributes, including:
PrintError-
The PrintError attribute, when enabled, will cause the DBI to issue a warning when a DBI method returns with an error status. This functionality is extremely useful for rapid debugging of your programs, as you may not have written explicit return value checking code after every DBI statement.
The printed error string lists the class of the database driver through which the DBI method was dispatched, the method that caused the error to occur, and the value of $DBI::errstr. The following message was generated when theprepare() method did not successfully execute against an Oracle7 database using the DBD::Oracle driver:
DBD::Oracle::db prepare failed: ORA-00904: invalid column name (DBD: error possibly near <*> indicator at char 8 in ' SELECT <*>nname, location, mapref FROM megaliths ') at /opt/WWW/apache/cgi-bin/megalith/megadump line 79.PrintError uses the standard Perl function called warn() to render the error message. Therefore, you could use a $SIG{_ _WARN_ _} error handler or an error handling module such as CGI::ErrorWrap to re-route the error messages fromPrintError.
This attribute is enabled by default.
-
The RaiseError attribute is similar in style to its PrintError cousin, but differs slightly in operation. WhereasPrintError simply displayed a message when the DBI detected an error had occurred, RaiseError usually kills the program stone-dead.
RaiseError uses the standard Perl function die() to throw the exception and exit. This means you can use eval to catch the exception and deal with it yourself.[57] This is an important and valuable error handling strategy for larger applications and is highly recommended when using transactions.
[57]It also allows you to define a $SIG{_ _DIE_ _} handler, which handles the die() call instead of the Perl default behavior.
The format of the error message printed by RaiseError is identical to that of PrintError. If both PrintError andRaiseError are defined, PrintError will be skipped if no $SIG{_ _DIE_ _} handler is installed.[58]
[58]A future release may also skip PrintError if RaiseError is set and the current code is executing within an eval.
RaiseError is disabled by default.
-
This attribute regulates the behavior of the underlying database driver regarding the CHAR datatype in fixed-width and blank-padded character columns. By setting this attribute to a true value, any CHAR columns returned by a SELECTstatement will have any trailing blanks chopped off. No other datatypes are affected even when trailing blanks are present.
Setting ChopBlanks usually occurs when you simply want to remove trailing spaces from data without having to write some explicit truncation code either in the original SQL statement or in Perl.
This can be a very handy mechanism when dealing with old databases that tend to use fixed-width, blank-padded CHARtypes more often than VARCHAR types. The blank padding added by the database tends to get in the way.
This attribute is currently disabled by default.
-
Many databases support BLOB (binary large object), LONG, or similar datatypes for holding very long strings or large amounts of binary data in a single field. Some databases support variable-length long values over 2,000,000,000 bytes in length.
Since values of that size can't usually be held in memory, and because databases can't usually know in advance the length of the longest LONG that will be returned from a SELECT statement (unlike other datatypes), some special handling is required. In this situation, the value of the LongReadLen attribute is used to determine how much buffer space to allocate when fetching such fields.
LongReadLen typically defaults to or a small value like 80, which means that little or no LONG data will be fetched at all. If you plan to fetch any LONG datatypes, you should set LongReadLen within your application to slightly more than the length of the longest long column you expect to fetch. Setting it too high just wastes memory.[59]
[59]Using a value which is a power of two, such as 64 KB, 512 KB, 8 MB etc., can actually cause twice that amount to be taken on systems that have poor memory allocators. That's because a few extra bytes are needed for housekeeping information and, because the dumb allocator only works with powers of two, it has to double the allocation to make room for it.
The LongTruncOk attribute is used to determine how to behave if a fetched value turns out to be larger than the buffer size defined by LongReadLen. For example, if LongTruncOk is set to a true value, (e.g., "truncation is okay") the over-long value will be silently truncated to the length specified by LongReadLen, without an error.
On the other hand, if LongTruncOk is false then fetching a LONG data value larger than LongReadLen is treated as an error. If RaiseError is not enabled then the fetch call retrieving the data will appear to fail in the usual way, which looks like the end of data has been reached.
LongTruncOk is set to false by default, which causes overly long data fetches to fail. Be sure to enable RaiseError or check for errors after your fetch loops.
We'll discuss handling LONG data in more detail in later in this chapter.
The DBI specification in Appendix A, "DBI Specification " provides a complete list of all the common attributes defined within the DBI.
6.1.5. Database Handle Attributes
Database handle attributes are specific to database handles and are not valid for other types of handles. They include:
AutoCommit-
The AutoCommit database handle attribute can be used to allow your programs to use fine-grained transaction behavior (as opposed to the default "commit everything" behavior).
The functionality of this attribute is closely tied into the way in which DBI defines transaction control. Therefore, a complete description of this parameter can be found later in this chapter.
-
The Name database handle attribute holds the "name" of the database. Usually the same as the "dbi:DriverName:..." string used to connect to the database, but with the leading "dbi:DriverName:" removed.
The DBI Specification in Appendix A, "DBI Specification " provides a complete list of all the database handle attributes defined within the DBI. We'll discuss statement handle attributes in a moment, but first we'll explore database metadata.
6.1.6. Database Metadata
Database metadata is high-level information, or "data about data," stored within a database describing that database. This information is extremely useful for dynamically building SQL statements or even generating dynamic views of the database contents.
The metadata stored by a database, and the way in which it's stored, varies widely between different database systems. Most major systems provide a system catalog , consisting of a set of tables and views that can be queried to get information about all the entities in the database, including tables and views. There are two common problems with trying to query the system catalog directly: they can be complex and difficult to query, and the queries are not portable to other types of database.
The DBI should provide a range of handy methods to access this information in a portable way, and one day it will. However, currently it only provides two methods that can be executed against a valid database handle to extract entity metadata from the database.
The first of these methods is called tables() , and simply returns an array containing the names of tables and views within the database defined by the relevant database handle. The following code illustrates the use of this method:
### Connect to the database
my $dbh = DBI->connect( 'dbi:Oracle:archaeo', 'stones', 'stones' );
### Get a list of tables and views
my @tables = $dbh->tables();
### Print 'em out
foreach my $table ( @tables ) {
print "Table: $table\n";
}
Connecting to a MySQL database would generate:
Table: megaliths Table: media Table: site_types
However, connecting to an Oracle database would generate:
Table: STONES.MEGALITHS Table: STONES.MEDIA Table: STONES.SITE_TYPES
In both cases, if the database contains other tables, they'd be included in the output.
Oracle stores all names in uppercase by default, so that explains one of the differences, but what about the "STONES." that's been prefixed to each table name?
Oracle, like most other big database systems, supports the concept of schemas . A schema is a way of grouping together related tables and other database objects into a named collection. In Oracle each user gets their own schema with the same name as the user. (Not all databases that support schemas take this approach.)
If an Oracle user other than stones wanted to refer to the media table then, by default, they would need to fully qualifythe table name by adding the stones schema name, e.g., stones.media. If they didn't then the database would think they were refering to a media table in their own schema.
So, the leading STONES in the output is the name of the schema that the tables are defined in. Returning the fully qualified table names is important because the tables() method will return the names of all the tables owned by all the users that it can discover.
The other method used to retrieve database metadata is called table_info() , and returns more detailed information about the tables and views stored within the database.
When invoked, table_info() returns a prepared and executed statement handle that can be used to fetch information on the tables and views in the database. Each row fetched from this statement handle contains at least the following fields in the order listed:[60]
[60]Database drivers are free to include additional columns of information in the result data.
This field contains the table qualifier identifier. In most cases this will be undef (NULL).
This field contains the name of the owner of the table. If your database does not support multiple schema or table owners, this field will contain undef (NULL).
This field contains the name of the table and should never be undef.
This field contains the ``type'' of entity signified by this row. The possible values include TABLE, VIEW, SYSTEMTABLE, GLOBAL TEMPORARY, LOCAL TEMPORARY, ALIAS, SYNONYM, or some database driver-specific identifier.
This field contains a description or comment about the table. This field may be undef (NULL).
Therefore, if we wished to list some basic information on the tables contained within the current schema or database, we can write the following program that uses table_info() to retrieve all the table information, then formats the output:
#!/usr/bin/perl -w
#
# ch06/dbhdump: Dumps information about a SQL statement.
use DBI;
### Connect to the database
my $dbh = DBI->connect( "dbi:Oracle:archaeo", "username", "password" , {
RaiseError => 1
} );
### Create a new statement handle to fetch table information
my $tabsth = $dbh->table_info();
### Print the header
print "Qualifier Owner Table Name Type Remarks\n";
print "========= ======== =============================== ===== =======\n\n";
### Iterate through all the tables...
while ( my ( $qual, $owner, $name, $type, $remarks ) =
$tabsth->fetchrow_array() ) {
### Tidy up NULL fields
foreach ($qual, $owner, $name, $type, $remarks) {
$_ = "N/A" unless defined $_;
}
### Print out the table metadata...
printf "%-9s %-9s %-32s %-6s %s\n", $qual, $owner, $name, $type, $remarks;
}
exit;
Running this program against our megalithic database on an Oracle database produces the following output:
Qualifier Owner Table Name Type Remarks ========= ======== =============================== ===== ======= N/A STONES MEDIA TABLE N/A N/A STONES MEGALITHS TABLE N/A N/A STONES SITE_TYPES TABLE N/A
This form of metadata is not tremendously useful, as it lists only metadata about the objects within the database, and not the structure of the objects themselves (such as table column names). Extracting the structure of each table or view within the database requires us to look to a different type of metadata, which is available via statement handles.
6.1.7. Statement Handle Attributes or Statement Metadata
Statement handle attributes are specific to statement handles, and inherit any inheritable attributes from their parent database handle. Many statement handle attributes are defined as being read-only because they simply describe the prepared statement or its results.
In theoretical terms, these attributes should be defined when the statement handle is prepared, but in practical terms, you should only rely on the attribute values after the statement handle has been both prepared and executed. Similarly, with a few drivers, fetching all the data from a SELECT statement or explicitly invoking the finish() method against a statement handle may cause the values of the statement handle attributes to be no longer available.
The DBI specification in Appendix A, "DBI Specification " provides a complete list of all the statement handle attributes defined within the DBI.
NUM_OF_FIELDS-
This attribute is set to contain the number of columns that will be returned by a SELECT statement. For example:
$sth = $dbh->prepare( " SELECT name, location, mapref FROM megaliths " ); $sth->execute(); print "SQL statement contains $sth->{NUM_OF_FIELDS} columns\n";Non-SELECT statements will contain the attribute value of zero. This allows you to quickly determine whether or not the statement is a SELECT statement.
-
The NAME attribute contains the names of the selected columns within the statement. The attribute value is actually a reference to an array, with length equal to the number of fields in the original statement.
For example, you can list all the column names of a table like this:
$sth = $dbh->prepare( "SELECT * FROM megaliths" ); $sth->execute(); for ( $i = 1 ; $i <= $sth->{NUM_OF_FIELDS} ; $i++ ) { print "Column $i is called $sth->{NAME}->[$i-1]\n"; }The names contained within the attribute array are the column names returned by the underlying database.
There are two additional attributes relating to the column names. NAME_uc contains the same column names as the NAMEattribute, but with any lowercase characters converted to uppercase. Similarly the NAME_lc attribute has any uppercase characters converted to lowercase. Generally these attributes should be used in preference to NAME.
-
The TYPE attribute contains a reference to an array of integer values representing the international standard values for the respective datatypes. The array of integers has a length equal to the number of columns selected within the original statement, and can be referenced in a similar way to the NAME attribute example shown earlier.
The standard values for common types are:
SQL_CHAR 1 SQL_NUMERIC 2 SQL_DECIMAL 3 SQL_INTEGER 4 SQL_SMALLINT 5 SQL_FLOAT 6 SQL_REAL 7 SQL_DOUBLE 8 SQL_DATE 9 SQL_TIME 10 SQL_TIMESTAMP 11 SQL_VARCHAR 12 SQL_LONGVARCHAR -1 SQL_BINARY -2 SQL_VARBINARY -3 SQL_LONGVARBINARY -4 SQL_BIGINT -5 SQL_TINYINT -6 SQL_BIT -7 SQL_WCHAR -8 SQL_WVARCHAR -9 SQL_WLONGVARCHAR -10
While these numbers are fairly standard,[61] the way drivers map their native types to these standard types varies greatly. Native types that don't correspond well to one of these types may be mapped into the range officially reserved for use by the Perl DBI: -9999 to -9000.
[61]Some are ISO standard, others are Microsoft ODBC de facto standard. Seeftp://jerry.ece.umassd.edu/isowg3/dbl/SQL_Registry and search for "SQL Data Types," or the types names of interest, on http://search.microsoft.com/us/dev/ and browse the results.
- PRECISION
-
The PRECISION attribute contains a reference to an array of integer values that represent the defined length or size of the columns in the SQL statement.
There are two general ways in which the precision of a column is calculated. String datatypes, such as CHAR andVARCHAR, return the maximum length of the column. For example, a column defined within a table as:
location VARCHAR2(1000)
would return a precision value of 1000.
Numeric datatypes are treated slightly differently in that the number of significant digits is returned. This may have no direct relationship with the space used to store the number. Oracle, for example, stores numbers with 38 digits of precision but uses a variable length internal format of between 1 and 21 bytes.
For floating-point types such as REAL, FLOAT, and DOUBLE, the maximum ``display size'' can be up to seven characters greater than the precision due to concatenated sign, decimal point, the letter ``E,'' a sign, and two or three exponent digits.
-
The SCALE attribute contains a reference to an array of integer values that represents the number of decimal places in the column. This is obviously only of any real use with floating-point numbers. Integers and non-numeric datatypes will return zero.
-
The NULLABLE attribute contains a reference to an array of integer values that tells us whether or not a column may contain a NULL value. The elements of the attribute array each contain one of three values:
- 0
-
The column cannot contain a NULL value.
- 1
-
The column can contain a NULL value.
- 2
-
It is unknown if the column can contain a null value.
-
The NUM_OF_PARAMS attribute contains the number of parameters (placeholders) specified within the statement.
Common uses for these statement handle attributes are to format and display data fetched from queries dynamically and tofind out information about the tables stored within the database.
The following script performs the latter operation by first creating a statement handle that fetches information on all tables, as discussed earlier in Section 6.1.6, "Database Metadata ", and then iterating through each table listing the table structure via the statement metadata:
#!/usr/bin/perl -w
#
# ch06/tabledump: Dumps information about all the tables.
use DBI;
### Connect to the database
my $dbh = DBI->connect( "dbi:Oracle:archaeo", "username", "password" , {
RaiseError => 1
});
### Create a new statement handle to fetch table information
my $tabsth = $dbh->table_info();
### Iterate through all the tables...
while ( my ( $qual, $owner, $name, $type ) = $tabsth->fetchrow_array() ) {
### The table to fetch data for
my $table = $name;
### Build the full table name with quoting if required
$table = qq{"$owner"."$table"} if defined $owner;
### The SQL statement to fetch the table metadata
my $statement = "SELECT * FROM $table";
print "\n";
print "Table Information\n";
print "=================\n\n";
print "Statement: $statement\n";
### Prepare and execute the SQL statement
my $sth = $dbh->prepare( $statement );
$sth->execute();
my $fields = $sth->{NUM_OF_FIELDS};
print "NUM_OF_FIELDS: $fields\n\n";
print "Column Name Type Precision Scale Nullable?\n";
print "------------------------------ ---- --------- ----- ---------\n\n";
### Iterate through all the fields and dump the field information
for ( my $i = 0 ; $i < $fields ; $i++ ) {
my $name = $sth->{NAME}->[$i];
### Describe the NULLABLE value
my $nullable = ("No", "Yes", "Unknown")[ $sth->{NULLABLE}->[$i] ];
### Tidy the other values, which some drivers don't provide
my $scale = $sth->{SCALE}->[$i];
my $prec = $sth->{PRECISION}->[$i];
my $type = $sth->{TYPE}->[$i];
### Display the field information
printf "%-30s %5d %4d %4d %s\n",
$name, $type, $prec, $scale, $nullable;
}
### Explicitly deallocate the statement resources
### because we didn't fetch all the data
$sth->finish();
}
exit;
When executed against our megalithic database, the following output is displayed:
Table Information ================= Statement: SELECT * FROM STONES.MEDIA NUM_OF_FIELDS: 5 Column Name Type Precision Scale Nullable? ------------------------------ ---- --------- ----- --------- ID 3 38 0 No MEGALITH_ID 3 38 0 Yes URL 12 1024 0 Yes CONTENT_TYPE 12 64 0 Yes DESCRIPTION 12 1024 0 Yes Table Information ================= Statement: SELECT * FROM STONES.MEGALITHS NUM_OF_FIELDS: 6 Column Name Type Precision Scale Nullable? ------------------------------ ---- --------- ----- --------- ID 3 38 0 No NAME 12 512 0 Yes DESCRIPTION 12 2048 0 Yes LOCATION 12 2048 0 Yes MAPREF 12 16 0 Yes SITE_TYPE_ID 3 38 0 Yes Table Information ================= Statement: SELECT * FROM STONES.SITE_TYPES NUM_OF_FIELDS: 3 Column Name Type Precision Scale Nullable? ------------------------------ ---- --------- ----- --------- ID 3 38 0 No SITE_TYPE 12 512 0 Yes DESCRIPTION 12 2048 0 Yes
This output shows the structural information of entities within our database. We could have achieved the same effect by querying our database's underlying system tables. This would give us more information, but would not be portable.
根据目前得到的结果好像对应问题
Column Name Type Precision Scale Nullable?
------------------------------ ---- --------- ----- ---------
START_TIME 93 75 0 No
STOP_TIME 93 75 0 No
NE_CLASS 8 126 0 Yes
NE_NAME 12 20 0 Yes
INT_ID 3 38 0 No
CITY_ID 3 38 0 Yes
SYS_CALL_ATT 8 126 0 Yes
NETWORK_ATT 8 126 0 Yes
SYS_CALL_SETUP 8 126 0 Yes
CALLED_ANSWER 8 126 0 Yes
PAGING_ATT 8 126 0 Yes
CALLED_RESPONSE 8 126 0 Yes
CALL_ATT 8 126 0 Yes
CALL_SETUP 8 126 0 Yes
OUTGO_CALL_SEIZE 8 126 0 Yes
INCOME_CALL_SEIZE 8 126 0 Yes
ANS2GMOCALLS 8 126 0 Yes
ANS2GMTCALLS 8 126 0 Yes
NVL(SUM(D.CPU_AVG_LOAD),0) 8 126 0 Yes
NVL(SUM(D.CPU_MAX_LOAD),0) 8 126 0 Yes
其中93在oracle里找不到对应的类型(和视图sys.user_tab_cols) 对应。
其中12 oracle数据库中是date,而这里的实际结果确是文本。
NVL(SUM(D.CPU_AVG_LOAD),0) 为浮点数，而在数据库中8是long...
看来对应还是存在一些问题,如有高手知道更好的方法请吝指教。firetw@163.com
