示例程序

数据插入

此示例包含 bindValue 和 bindParam 按位绑定和按名绑定的四种用法。bindValue 与 bindParam 的区别在于 bindParam 只能绑定变量且支持绑定输出型参数。

示例 1

$pdo = new PDO("xugusql:ip=127.0.0.1;port=5138;db=SYSTEM", "SYSDBA", "SYSDBA");
$stmt = $pdo->prepare("INSERT INTO tab_test (name, created) values(?, ?)");
$stmt->bindValue(1, "Tom");
$stmt->bindValue(2, "2000-01-01");
echo $stmt->execute();

示例 2

$pdo = new PDO("xugusql:ip=127.0.0.1;port=5138;db=SYSTEM", "SYSDBA", "SYSDBA");
$stmt = $pdo->prepare("INSERT INTO tab_test (name, created) values(:name, :created)");
$stmt->bindValue("name", "Tom");
$stmt->bindValue("created", "2000-01-01");
echo $stmt->execute();

示例 3

$pdo = new PDO("xugusql:ip=127.0.0.1;port=5138;db=SYSTEM", "SYSDBA", "SYSDBA");
$stmt = $pdo->prepare("INSERT INTO tab_test (name, created) values(?, ?)");
$name = "Tom";
$created = "2000-01-01";
$stmt->bindParam(1, $name);
$stmt->bindParam(2, $created);
echo $stmt->execute();

示例 4

$pdo = new PDO("xugusql:ip=127.0.0.1;port=5138;db=SYSTEM", "SYSDBA", "SYSDBA");
$stmt = $pdo->prepare("INSERT INTO tab_test (name, created) values(:name, :created)");
$name = "Tom";
$created = "2000-01-01";
$stmt->bindParam("name", $name);
$stmt->bindParam("created", $created);
echo $stmt->execute();

数据查询

示例 1

$pdo = new PDO("xugusql:ip=127.0.0.1;port=5138;db=SYSTEM", "SYSDBA", "SYSDBA");

$stmt = $pdo->query("SELECT id, name, created FROM tab_test");
while($row = $stmt->fetch()) {
    echo "{$row['ID']}, {$row['NAME']}, {$row['BIRTHDAY']}" . PHP_EOL;
}

示例 2

$pdo = new PDO("xugusql:ip=127.0.0.1;port=5138;db=SYSTEM", "SYSDBA", "SYSDBA");

$stmt = $pdo->query("SELECT id, name, created FROM tab_test");
$stmt->bindColumn(1, $id);
$stmt->bindColumn(2, $name);
$stmt->bindColumn(3, $created);

while($stmt->fetch()) {
    echo "$id, $name, $created" . PHP_EOL;
}

示例 3

$pdo = new PDO("xugusql:ip=127.0.0.1;port=5138;db=SYSTEM", "SYSDBA", "SYSDBA");

$stmt = $pdo->prepare("SELECT id, name, created FROM tab_test WHERE id = :id");
$stmt->bindValue("id", 1);
$stmt->execute();

while($row = $stmt->fetch()) {
    echo "{$row['ID']}, {$row['NAME']}, {$row['BIRTHDAY']}" . PHP_EOL;
}

存储过程调用

-- 创建存储过程
CREATE OR REPLACE PROCEDURE proc_add(a int, b int, sum OUT int)
AS 
BEGIN 
	sum := a + b;
END;

$pdo = new PDO("xugusql:ip=127.0.0.1;port=5138;db=SYSTEM", "SYSDBA", "SYSDBA");

$stmt = $pdo->prepare("EXEC proc_add(?, ?, ?)");
$stmt->bindValue(1, 100);
$stmt->bindValue(2, 200);
$stmt->bindParam(3, $sum, PDO::PARAM_INPUT_OUTPUT);
$stmt->execute();

echo $sum;//输出 300

存储函数调用

调用函数除了使用如下示例中的 EXEC，还可以使用 SELECT func_name(param1[, ...]) 的方式。

-- 创建函数
CREATE OR REPLACE FUNCTION func_add(a int, b int)
RETURN int
AS 
BEGIN 
	RETURN a + b;
END;

$pdo = new PDO("xugusql:ip=127.0.0.1;port=5138;db=SYSTEM", "SYSDBA", "SYSDBA");

$stmt = $pdo->prepare("EXEC func_add(?, ?)");
$stmt->bindValue(1, 100);
$stmt->bindValue(2, 200);
$stmt->bindParam(3, $sum, PDO::PARAM_INPUT_OUTPUT);
$stmt->execute();

echo $sum;//输出 300

大对象插入

此示例为插入 BLOB 类型数据的示例代码。若需插入 CLOB 类型数据，插入方法与插入字符串的方法一致。

$pdo = new PDO("xugusql:ip=127.0.0.1;port=5138;db=SYSTEM", "SYSDBA", "SYSDBA");

$lob = fopen("img.png", "rb");

$stmt = $pdo->prepare("INSERT INTO tab_lob (lob) VALUES (:lob)");
$stmt->bindValue(":lob", $lob, PDO::PARAM_LOB);
$stmt->execute();

fclose($lob);

大对象查询

此示例为查询 BLOB 类型数据的的示例代码。若需查询 CLOB 类型数据，方法相同。需要注意的是，如果使用 bindColumn 的方式获取 LOB 数据，在 fetch 时务必使用 PDO::FETCH_BOUND。

$pdo = new PDO("xugusql:ip=127.0.0.1;port=5138;db=SYSTEM", "SYSDBA", "SYSDBA");

$stmt = $pdo->query("SELECT lob FROM tab_lob WHERE id = 1");
$stmt->bindColumn(1, $lob, PDO::PARAM_LOB);
$stmt->fetch(PDO::FETCH_BOUND);

$file = fopen("img_out.png", "wb");

while(!feof($lob))
    fwrite($file, fgets($lob));

fclose($file);

或者

$pdo = new PDO("xugusql:ip=127.0.0.1;port=5138;db=SYSTEM", "SYSDBA", "SYSDBA");

$stmt = $pdo->query("SELECT lob FROM tab_lob WHERE id = 1");
$row = $stmt->fetch();

$file = fopen("img_out.png", "wb");

while (!feof($row[0]))
    fwrite($file, fgets($row[0]));

fclose($file);