/ 
DBIx-Connection-0.04
River stage one • 2 direct dependents • 2 total dependents
/ DBIx::PLSQLHandler

NAME

DBIx::PLSQLHandler - PL/SQL procedural language handler.

SYNOPSIS

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;"
);

DESCRIPTION

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

ATTRIBUTES

plsql

Plsql block

bind_variables

Keeps information about binds variables and its types.

bind_in_variales

Ordered list for binding in variables

bind_inout_variales

Ordered list for binding in out variables

bind_out_variales

Ordered list for binding out variables

default_type

default type binding

default_width

default width binding

METHODS

new
initialise

Initialises handler.

initialise_bind_variables

Parses plsql for binding variables. TODO replace this naive implementations.

set_binding_order
add_default_variable_info

Adds default variable meta data.

plsql_block_name

Returns plsql block name (used to create plsql block procedure or function wrapper)

create_plsql_block_binding
get_variable_binding_order

Return bind variable order

binded_in_variables

Returns bind_in_variables + bind_inout_variables

binded_out_variables

Returns bind_inout_variables + bind_out_variables

get_sql_variable_definition

Returns variable definition for plsql block stub

type_precision

Returns variable type precision, takes bind variable name.

block_source

Block source, used for comparision against database wrapper source.

parsed_plsql

Parses plsql code and replaces :var to var

check_block_changed

Checks if plsql_block has been changed and return true otherwise false.

COPYRIGHT AND LICENSE

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.

SEE ALSO

DBIx::QueryCursor DBIx::SQLHandler

AUTHOR

Adrian Witas, adrian@webapp.strefa.pl