yajra / pdo-via-oci8

PHP PDO_OCI functions via OCI8 extension
Other
88 stars 61 forks source link

fix: oci_new_collection implementation #139

Closed yajra closed 5 months ago

yajra commented 5 months ago

Example Laravel Route

    Route::get('varray', function () {
        Schema::dropIfExists('emails');
        Schema::create('emails', function (OracleBlueprint $table) {
            $table->id();
            $table->string('user_id');
            $table->string('friend_name');
            $table->string('email_address');
        });

        DB::statement('CREATE OR REPLACE TYPE FRIEND_ARRAY AS VARRAY(255) OF VARCHAR2(255)');
        DB::statement('CREATE OR REPLACE TYPE EMAIL_ARRAY AS VARRAY(255) OF VARCHAR2(255)');

        DB::statement('
            create or replace procedure update_address_book(
                p_user_id in varchar2,
                p_friend_name friend_array,
                p_email_addresses email_array
            ) is
            begin
             delete from emails where user_id = p_user_id;
             forall i in indices of p_email_addresses
                 insert into emails (user_id, friend_name, email_address)
                 values (p_user_id, p_friend_name(i), p_email_addresses(i));
            end update_address_book;
        ');

        $user_name = 'cjones';
        $friends_names = ['alison', 'aslam'];
        $friends_emails = ['alison@example.com', 'aslam@example.com'];

        /** @var \Yajra\Pdo\Oci8 $pdo */
        $pdo = DB::getPdo();

        $friend_coll = $pdo->getNewCollection('FRIEND_ARRAY');
        $email_coll = $pdo->getNewCollection('EMAIL_ARRAY');

        for ($i = 0; $i < count($friends_names); $i++) {
            $friend_coll->append($friends_names[$i]);
            $email_coll->append($friends_emails[$i]);
        }

        $statement = $pdo->prepare('begin update_address_book(:user_id, :friends, :emails); end;');
        $statement->bindParam(':user_id', $user_name);
        $statement->bindParam(':friends', $friend_coll, OCI_B_NTY);
        $statement->bindParam(':emails', $email_coll, OCI_B_NTY);
        $statement->execute();

        return DB::table('emails')->get();
    });