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

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

new

initialise

Initialises handler.

initialise_bind_variables

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

set_binding_order

default_variable_info

Adds default variable meta data.

plsql_block_name

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

plsql_block_declaration

bind_variable_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

variable_declaration

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

is_block_changed

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