laravel

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use PDO;

class TestController extends Controller
{
    public function index()
    {
        $pdo = DB::getPdo();
        $int = 1;
        $res = 0;
        $stmt = $pdo->prepare("EXEC dbo.test :int,:res");
        $stmt->bindParam(':int', $res, PDO::PARAM_INT);
        $stmt->bindParam(':res', $res, PDO::PARAM_STR | PDO::PARAM_INPUT_OUTPUT, 400);
        $stmt->execute();
        dd($res);
    }
}

参考：

• https://stackoverflow.com/questions/71321858/api-laravel-call-store-procedure-with-in-and-out-parameter-using-oci8-or-pdo
• https://laracasts.com/discuss/channels/general-discussion/running-stored-procedures

thinkphp 5.0

tp5 需要修改原码才可以使用：

• 找到文件 thinkphp/library/think/db/Connection.php
• 第 388 行，在 catch (\PDOException $e) 内，修改为如下代码：
  if ($procedure == true) { return; } elseif ($this->isBreak($e)) { return $this->close()->query($sql, $bind, $master, $pdo); }

控制器执行：

<?php

namespace app\api\controller;

use PDO;
use think\Db;

class Test extends Common {
    public function index() {
        $int = 1;
        $res = 1;
        Db::query('exec test :int,:res', [
            'int' => $int,
            'res' => [&$res, PDO::PARAM_STR | PDO::PARAM_INPUT_OUTPUT, 4000],
        ]);
        print_r($res);
    }
}

参考：

• https://blog.csdn.net/Drug_/article/details/95474776

附

以上示例使用的存储过程如下：

ALTER PROC [dbo].[test]
-- 创建：CREATE PROC [dbo].[test]
 @IntInput int,
 @StrResult varchar(20) out
as
begin
         if (@IntInput>1)
           Set
        @StrResult = '>1'
        else 
            Set
        @StrResult = '<=1'
end

SQL 执行：

declare @strResult varchar(20)
exec test -1,@strResult output
print @strResult