PHP数据库视图与存储过程调用

PHP数据库视图与存储过程调用视图和存储过程是数据库的进阶功能。视图简化复杂查询存储过程封装业务逻辑。今天说说PHP中调用视图和存储过程。创建和使用视图。php$pdo-exec(CREATE OR REPLACE VIEW user_order_summary ASSELECT u.id, u.name, COUNT(o.id) as order_count, SUM(o.total) as total_spentFROM users uLEFT JOIN orders o ON u.id o.user_idGROUP BY u.id, u.name);$summary $pdo-query(SELECT * FROM user_order_summary ORDER BY total_spent DESC)-fetchAll();print_r($summary);?调用存储过程。php// 创建存储过程$pdo-exec(CREATE PROCEDURE get_user_orders(IN userId INT)BEGINSELECT * FROM orders WHERE user_id userId;END);$pdo-exec(CREATE PROCEDURE create_order(IN userId INT,IN total DECIMAL(10,2),OUT orderId INT)BEGININSERT INTO orders (user_id, total) VALUES (userId, total);SET orderId LAST_INSERT_ID();END);// PHP调用$stmt $pdo-prepare(CALL get_user_orders(?));$stmt-execute([1]);$orders $stmt-fetchAll();print_r($orders);// 带输出参数的调用$stmt $pdo-prepare(CALL create_order(?, ?, orderId));$stmt-execute([1, 299.99]);$result $pdo-query(SELECT orderId as id)-fetch();echo 新订单ID: {$result[id]}\n;?使用函数。php$pdo-exec(CREATE FUNCTION calculate_discount(amount DECIMAL(10,2))RETURNS DECIMAL(10,2)DETERMINISTICBEGINDECLARE discount DECIMAL(10,2);IF amount 1000 THEN SET discount amount * 0.1;ELSEIF amount 500 THEN SET discount amount * 0.05;ELSE SET discount 0;END IF;RETURN discount;END);$stmt $pdo-query(SELECT calculate_discount(1500) as discount);echo 折扣: . $stmt-fetchColumn() . \n;?视图和存储过程对PHP开发是透明。视图就像普通表一样查询存储过程通过CALL调用。视图适合封装复杂查询存储过程适合事务性操作。视图和存储过程更适合在数据库层面做数据聚合和处理。