Oracle Stored Procedure

Before running the PHP program, create a stored procedure in SQL*Plus, SQL Developer or by migration:

##Creating the Procedure with Plain SQL

CREATE OR REPLACE PROCEDURE myproc(p1 IN NUMBER, p2 OUT NUMBER) AS
BEGIN
    p2 := p1 * 2;
END;

##Creating the Procedure with migrations

/**
 * Run the migrations.
 *
 * @return void
 */
public function up()
{
    $command = "
        CREATE OR REPLACE PROCEDURE myproc(p1 IN NUMBER, p2 OUT NUMBER) AS
        BEGIN
            p2 := p1 * 2;
        END;
    ";
 
    DB::connection()->getPdo()->exec($command);
}
 
/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    $command = "DROP PROCEDURE myproc";
    DB::connection()->getPdo()->exec($command);
}

##Running procedure the manual way

$pdo = DB::getPdo();
$p1 = 8;
 
$stmt = $pdo->prepare("begin myproc(:p1, :p2); end;");
$stmt->bindParam(':p1', $p1, PDO::PARAM_INT);
$stmt->bindParam(':p2', $p2, PDO::PARAM_INT);
$stmt->execute();
 
return $p2; // prints 16

##Running procedure with shortcut method

$procedureName = 'youpackagename.yourprocedurename';
 
$bindings = [
    'user_id'  => $id,
];
 
$result = DB::executeProcedure($procedureName, $bindings);
 
dd($result);

Edit this page on GitHub