DBIx::PLSQLHandler - PL/SQL procedural language handler.
use DBIx::PLSQLHandler; my $plsql = new DBIx::PLSQLHandler( connection => $connection, plsql => " DECLARE debit_amt CONSTANT NUMBER(5,2) := 500.00; BEGIN SELECT a.bal INTO :acct_balance FROM accounts a WHERE a.account_id = :acct AND a.debit > debit_amt; :extra_info := 'debit_amt: ' || debit_amt; END;" ); my $result_set = $plsql->execute(acct => 000212); # $result_set->{acct_balance}; $result_set->{extra_info} ... do some stuff or use DBIx::Connection; ... my $plsql = $connection->plsql_handler( plsql => " DECLARE debit_amt CONSTANT NUMBER(5,2) := 500.00; BEGIN SELECT a.bal INTO :acct_balance FROM accounts a WHERE a.account_id = :acct AND a.debit > debit_amt; :extra_info := 'debit_amt: ' || debit_amt; END;" );
Base class for PLSQL blocks hyandler(SQL Procedural Language). It allows use independetly specyfig Procedural Language SQL dialect like PL/SQL (Oracle, mySQL), PL/pgSQL (PostgreSQL) It uses ":" placeholers to bind variables in or out or inout.
By default it bind variable is defined as varchar, however you can change it by specyfing your types in bind_variables parameter.
my $plsql_handler = new DBIx::PLSQLHandler( name => 'int_test', connection => $connection, plsql => "BEGIN :var1 := :var2 + :var3; :var4 := 'long text'; END;", bind_variables => { var1 => {type => 'SQL_INTEGER'}, var4 => {type => 'SQL_VARCHAR', width => 30} } );
In Oracle database it uses an anonymous PLSQL block, In mysql procedure wraps the plsql block. In postgresql function wraps the plsql block. Name for the procedure/function wrapper is created as 'anonymous_' + $self->name
Plsql block
Keeps information about binds variables and its types.
Ordered list for binding in variables
Ordered list for binding in out variables
Ordered list for binding out variables
default type binding
default width binding
Initialises handler.
Parses plsql for binding variables. TODO replace this naive implementations.
Adds default variable meta data.
Returns plsql block name (used to create plsql block procedure or function wrapper)
Return bind variable order
Returns bind_in_variables + bind_inout_variables
Returns bind_inout_variables + bind_out_variables
Returns variable definition for plsql block stub
Returns variable type precision, takes bind variable name.
Block source, used for comparision against database wrapper source.
Parses plsql code and replaces :var to var
Checks if plsql_block has been changed and return true otherwise false.
The DBIx::PLSQLHandler module is free software. You may distribute under the terms of either the GNU General Public License or the Artistic License, as specified in the Perl README file.
DBIx::QueryCursor DBIx::SQLHandler
Adrian Witas, adrian@webapp.strefa.pl
To install DBIx::Connection, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBIx::Connection
CPAN shell
perl -MCPAN -e shell install DBIx::Connection
For more information on module installation, please visit the detailed CPAN module installation guide.