dbd::oracle.3

视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.

character set will be marked as \s-1UTF\-8\s0 to ensure correct handling by perl.
.PP
For Oracle to return data in the \s-1AL32UTF8\s0 character set the
\&\s-1NLS_LANG\s0 or \s-1NLS_NCHAR\s0 environment variable \fImust\fR be set as described
in the previous section.
.PP
When fetching \s-1NCHAR\s0, \s-1NVARCHAR\s0, or \s-1NCLOB\s0 data from Oracle, DBD::Oracle
will set the perl \s-1UTF\-8\s0 flag on the returned data if either \s-1NLS_NCHAR\s0
is \s-1AL32UTF8\s0, or \s-1NLS_NCHAR\s0 is not set and \s-1NLS_LANG\s0 is \s-1AL32UTF8\s0.
.PP
When fetching other character data from Oracle, DBD::Oracle
will set the perl \s-1UTF\-8\s0 flag on the returned data if \s-1NLS_LANG\s0 is \s-1AL32UTF8\s0.
.PP
\&\fBSending Data using Placeholders\fR
.PP
Data bound to a placeholder is assumed to be in the default client
character set (specified by \s-1NLS_LANG\s0) except for a few special
cases. These are listed here with the highest precedence first:
.PP
If the \f(CW\*(C`ora_csid\*(C'\fR attribute is given to \fIbind_param()\fR then that
is passed to Oracle and takes precedence.
.PP
If the value is a Perl Unicode string (\s-1UTF\-8\s0) then DBD::Oracle
ensures that Oracle uses the Unicode character set, regardless of
the \s-1NLS_LANG\s0 and \s-1NLS_NCHAR\s0 settings.
.PP
If the placeholder is for inserting an \s-1NCLOB\s0 then the client \s-1NLS_NCHAR\s0
character set is used. (That's useful but inconsistent with the other behaviour
so may change. Best to be explicit by using the \f(CW\*(C`ora_csform\*(C'\fR
attribute.)
.PP
If the \f(CW\*(C`ora_csform\*(C'\fR attribute is given to \fIbind_param()\fR then that
determines if the value should be assumed to be in the default
(\s-1NLS_LANG\s0) or \s-1NCHAR\s0 (\s-1NLS_NCHAR\s0) client character set.
.PP
.Vb 3
\&   use DBD::Oracle qw( SQLCS_IMPLICIT SQLCS_NCHAR );
\&   ...
\&   $sth\->bind_param(1, $value, { ora_csform => SQLCS_NCHAR });
.Ve
.PP
or
.PP
.Vb 1
\&   $dbh\->{ora_ph_csform} = SQLCS_NCHAR; # default for all future placeholders
.Ve
.PP
\&\fBSending Data using \s-1SQL\s0\fR
.PP
Oracle assumes the \s-1SQL\s0 statement is in the default client character
set (as specified by \s-1NLS_LANG\s0). So Unicode strings containing
non-ASCII characters should not be used unless the default client
character set is \s-1AL32UTF8\s0.
.Sh "DBD::Oracle and Other Character Sets and Encodings"
.IX Subsection "DBD::Oracle and Other Character Sets and Encodings"
The only multi-byte Oracle character set supported by DBD::Oracle is
\&\*(L"\s-1AL32UTF8\s0\*(R" (and \*(L"\s-1UTF8\s0\*(R"). Single-byte character sets should work well.
.SH "SYS.DBMS_SQL datatypes"
.IX Header "SYS.DBMS_SQL datatypes"
DBD::Oracle has built-in support for \fB\s-1SYS\s0.DBMS_SQL.VARCHAR2_TABLE\fR
and \fB\s-1SYS\s0.DBMS_SQL.NUMBER_TABLE\fR data types. The simple example is here:
.PP
.Vb 11
\&    my $statement=\*(Aq
\&    DECLARE
\&        tbl     SYS.DBMS_SQL.VARCHAR2_TABLE;
\&    BEGIN
\&        tbl := :mytable;
\&        :cc := tbl.count();
\&        tbl(1) := \e\*(Aqdef\e\*(Aq;
\&        tbl(2) := \e\*(Aqijk\e\*(Aq;
\&        :mytable := tbl;
\&    END;
\&    \*(Aq;
\&
\&    my $sth=$dbh\->prepare( $statement );
\&
\&    my @arr=( "abc" );
\&
\&    $sth\->bind_param_inout(":mytable", \e@arr, 10, {
\&            ora_type => ORA_VARCHAR2_TABLE,
\&            ora_maxarray_numentries => 100
\&    } ) );
\&    $sth\->bind_param_inout(":cc", \e$cc, 100 ) );
\&    $sth\->execute();
\&    print       "Result: cc=",$cc,"\en",
\&        "\etarr=",Data::Dumper::Dumper(\e@arr),"\en";
.Ve
.IP "\s-1OCI_VARCHAR2_TABLE\s0" 4
.IX Item "OCI_VARCHAR2_TABLE"
\&\s-1SYS\s0.DBMS_SQL.VARCHAR2_TABLE object is always bound to array reference.
( in \fIbind_param()\fR and \fIbind_param_inout()\fR ). When you bind array, you need
to specify full buffer size for \s-1OUT\s0 data. So, there are two parameters:
\&\fImax_len\fR (specified as 3rd argument of \fIbind_param_inout()\fR ),
and \fIora_maxarray_numentries\fR. They define maximum array entry length and
maximum rows, that can be passed to Oracle and back to you. In this
example we send array with 1 element with length=3, but allocate space for 100
Oracle array entries with maximum length 10 of each. So, you can get no more
than 100 array entries with length <= 10.
.Sp
If you set \fImax_len\fR to zero, maximum array entry length is calculated
as maximum length of entry of array bound. If 0 < \fImax_len\fR < length( \f(CW$some_element\fR ),
truncation occur.
.Sp
If you set \fIora_maxarray_numentries\fR to zero, current (at bind time) bound
array length is used as maximum. If 0 < \fIora_maxarray_numentries\fR < scalar(@array),
not all array entries are bound.
.IP "\s-1OCI_NUMBER_TABLE\s0" 4
.IX Item "OCI_NUMBER_TABLE"
\&\s-1SYS\s0.DBMS_SQL.NUMBER_TABLE object handling is much alike \s-1ORA_VARCHAR2_TABLE\s0.
The main difference is internal data representation. Currently 2 types of
bind is allowed : as C\-integer, or as C\-double type. To select one of them,
you may specify additional bind parameter \fIora_internal_type\fR as either
\&\fB\s-1SQLT_INT\s0\fR or \fB\s-1SQLT_FLT\s0\fR for C\-integer and C\-double types.
Integer size is architecture-specific and is usually 32 or 64 bit.
Double is standard \s-1IEEE\s0 754 type.
.Sp
\&\fIora_internal_type\fR defaults to double (\s-1SQLT_FLT\s0).
.Sp
\&\fImax_len\fR is ignored for \s-1OCI_NUMBER_TABLE\s0.
.Sp
Currently, you cannot bind full native Oracle \s-1NUMBER\s0(38). If you really need,
send request to dbi-dev list.
.Sp
The usage example is here:
.Sp
.Vb 11
\&    $statement=\*(Aq
\&    DECLARE
\&            tbl     SYS.DBMS_SQL.NUMBER_TABLE;
\&    BEGIN
\&            tbl := :mytable;
\&            :cc := tbl(2);
\&            tbl(4) := \-1;
\&            tbl(5) := \-2;
\&            :mytable := tbl;
\&    END;
\&    \*(Aq;
\&    
\&    $sth=$dbh\->prepare( $statement );
\&    
\&    if( ! defined($sth) ){
\&            die "Prapare error: ",$dbh\->errstr,"\en";
\&    }
\&    
\&    @arr=( 1,"2E0","3.5" );
\&    
\&    # note, that ora_internal_type defaults to SQLT_FLT for ORA_NUMBER_TABLE .
\&    if( not $sth\->bind_param_inout(":mytable", \e@arr, 10, {
\&                    ora_type => ORA_NUMBER_TABLE,
\&                    ora_maxarray_numentries => (scalar(@arr)+2),
\&                    ora_internal_type => SQLT_FLT
\&              } ) ){
\&            die "bind :mytable error: ",$dbh\->errstr,"\en";
\&    }
\&    $cc=undef;
\&    if( not $sth\->bind_param_inout(":cc", \e$cc, 100 ) ){
\&            die "bind :cc error: ",$dbh\->errstr,"\en";
\&    }
\&    
\&    if( not $sth\->execute() ){
\&            die "Execute failed: ",$dbh\->errstr,"\en";
\&    }
\&    print   "Result: cc=",$cc,"\en",
\&            "\etarr=",Data::Dumper::Dumper(\e@arr),"\en";
.Ve
.Sp
The result is like:
.Sp
.Vb 8
\&    Result: cc=2
\&            arr=$VAR1 = [
\&              \*(Aq1\*(Aq,
\&              \*(Aq2\*(Aq,
\&              \*(Aq3.5\*(Aq,
\&              \*(Aq\-1\*(Aq,
\&              \*(Aq\-2\*(Aq
\&            ];
.Ve
.Sp
If you change bind type to \fB\s-1SQLT_INT\s0\fR, like:
.Sp
.Vb 1
\&    ora_internal_type => SQLT_INT
.Ve
.Sp
you get:
.Sp
.Vb 8
\&    Result: cc=2
\&            arr=$VAR1 = [
\&              1,
\&              2,
\&              3,
\&              \-1,
\&              \-2
\&            ];
.Ve
.SH "Other Data Types"
.IX Header "Other Data Types"
DBD::Oracle does not \fIexplicitly\fR support most Oracle data types.
It simply asks Oracle to return them as strings and Oracle does so.
Mostly.  Similarly when binding placeholder values DBD::Oracle binds
them as strings and Oracle converts them to the appropriate type,
such as \s-1DATE\s0, when used.
.PP
Some of these automatic conversions to and from strings use \s-1NLS\s0
settings to control the formating for output and the parsing for
input. The most common example is the \s-1DATE\s0 type. The default \s-1NLS\s0
format for \s-1DATE\s0 might be DD-MON-YYYY and so when a \s-1DATE\s0 type is
fetched that's how Oracle will format the date. \s-1NLS\s0 settings also
control the default parsing of strings into \s-1DATE\s0 values. An error
will be generated if the contents of the string don't match the
\&\s-1NLS\s0 format. If you're dealing in dates which don't match the default
\&\s-1NLS\s0 format then you can either change the default \s-1NLS\s0 format or, more
commonly, use \s-1TO_CHAR\s0(field, \*(L"format\*(R") and \s-1TO_DATE\s0(?, \*(L"format\*(R")
to explicitly specify formats for converting to and from strings.
.PP
A slightly more subtle problem can occur with \s-1NUMBER\s0 types. The
default \s-1NLS\s0 settings might format numbers with a fullstop ("\f(CW\*(C`.\*(C'\fR\*(L")
to separate thousands and a comma (\*(R"\f(CW\*(C`,\*(C'\fR") as the decimal point.
Perl will generate warnings and use incorrect values when numbers,
returned and formatted as strings in this way by Oracle, are used
in a numeric context.  You could explicitly convert each numeric
value using the \s-1TO_CHAR\s0(...) function but that gets tedious very
quickly. The best fix is to change the \s-1NLS\s0 settings. That can be
done for an individual connection by doing:
.PP
.Vb 1
\&  $dbh\->do("ALTER SESSION SET NLS_NUMERIC_CHARACTERS = \*(Aq.,\*(Aq");
.Ve
.PP
There are some types, like \s-1BOOLEAN\s0, that Oracle does not automatically
convert to or from strings (pity).  These need to be converted
explicitly using \s-1SQL\s0 or \s-1PL/SQL\s0 functions.
.PP
Examples:
.PP
.Vb 7
\&   # DATE values
\&   my $sth0 = $dbh\->prepare( <<SQL_END );
\&   SELECT username, TO_CHAR( created, ? )
\&      FROM all_users
\&      WHERE created >= TO_DATE( ?, ? )
\&   SQL_END
\&   $sth0\->execute( \*(AqYYYY\-MM\-DD HH24:MI:SS\*(Aq, "2003", \*(AqYYYY\*(Aq );
\&
\&   # BOOLEAN values
\&   my $sth2 = $dbh\->prepare( <<PLSQL_END );
\&   DECLARE
\&      b0 BOOLEAN;
\&      b1 BOOLEAN;
\&      o0 VARCHAR2(32);
\&      o1 VARCHAR2(32);
\&
\&      FUNCTION to_bool( i VARCHAR2 ) RETURN BOOLEAN IS
\&      BEGIN
\&         IF    i IS NULL          THEN RETURN NULL;
\&         ELSIF i = \*(AqF\*(Aq OR i = \*(Aq0\*(Aq THEN RETURN FALSE;
\&         ELSE                          RETURN TRUE;
\&         END IF;
\&      END;
\&      FUNCTION from_bool( i BOOLEAN ) RETURN NUMBER IS
\&      BEGIN
\&         IF    i IS NULL THEN RETURN NULL;
\&         ELSIF i         THEN RETURN 1;
\&         ELSE                 RETURN 0;
\&         END IF;
\&      END;
\&   BEGIN
\&      \-\- Converting values to BOOLEAN
\&      b0 := to_bool( :i0 );
\&      b1 := to_bool( :i1 );
\&
\&      \-\- Converting values from BOOLEAN
\&      :o0 := from_bool( b0 );
\&      :o1 := from_bool( b1 );
\&   END;
\&   PLSQL_END
\&   my ( $i0, $i1, $o0, $o1 ) = ( "", "Something else" );
\&   $sth2\->bind_param( ":i0", $i0 );
\&   $sth2\->bind_param( ":i1", $i1 );
\&   $sth2\->bind_param_inout( ":o0", \e$o0, 32 );
\&   $sth2\->bind_param_inout( ":o1", \e$o1, 32 );
\&   $sth2\->execute();
\&   foreach ( $i0, $b0, $o0, $i1, $b1, $o1 ) {
\&      $_ = "(undef)" if ! defined $_;
\&   }
\&   print "$i0 to $o0, $i1 to $o1\en";
\&   # Result is : "\*(Aq\*(Aq to \*(Aq(undef)\*(Aq, \*(AqSomething else\*(Aq to \*(Aq1\*(Aq"
.Ve
.SH "PL/SQL Examples"
.IX Header "PL/SQL Examples"
Most of these \s-1PL/SQL\s0 examples come from: Eric Bartley <[e-mail]>.
.PP
.Vb 7
\&   /*
\&    * PL/SQL to create package with stored procedures invoked by
\&    * Perl examples.  Execute using sqlplus.
\&    *
\&    * Use of "... OR REPLACE" prevents failure in the event that the
\&    * package already exists.
\&    */
\&
\&    CREATE OR REPLACE PACKAGE plsql_example
\&    IS
\&      PROCEDURE proc_np;
\&
\&      PROCEDURE proc_in (
\&          err_code IN NUMBER
\&      );
\&
\&      PROCEDURE proc_in_inout (
\&          test_num IN NUMBER,
\&          is_odd IN OUT NUMBER
\&      );
\&
\&      FUNCTION func_np
\&        RETURN VARCHAR2;
\&
\&    END plsql_example;
\&  /
\&
\&    CREATE OR REPLACE PACKAGE BODY plsql_example
\&    IS
\&      PROCEDURE proc_np
\&      IS
\&        whoami VARCHAR2(20) := NULL;
\&      BEGIN
\&        SELECT USER INTO whoami FROM DUAL;
\&      END;
\&
\&      PROCEDURE proc_in (
\&        err_code IN NUMBER
\&      )