스톡프러시저를 이용한 php 예제
Posted by Albert 4853Day 20Hour 30Min 57Sec ago [2012-01-05]
ok.rar=================================================================
test 데이타베이스 생성
=================================================================
create database test
=================================================================
users 테이블 작성
=================================================================
CREATE TABLE IF NOT EXISTS `users` (
`users_id` int(10) unsigned NOT NULL auto_increment,
`first_name` varchar(100) NOT NULL,
`last_name` varchar(100) NOT NULL,
PRIMARY KEY (`users_id`)
)
=================================================================
get_user 프로시저 작성
=================================================================
DELIMITER $$
USE `test`$$
DROP PROCEDURE IF EXISTS `get_user`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_user`(
IN userId INT,
OUT firstName VARCHAR(100),
OUT lastName VARCHAR(100)
)
BEGIN
SELECT first_name, last_name
INTO firstName, lastName
FROM users
WHERE users_id = userId;
END$$
DELIMITER ;
=================================================================
get_users
=================================================================
DELIMITER $$
USE `test`$$
DROP PROCEDURE IF EXISTS `get_users`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_users`()
BEGIN
SELECT *
FROM users;
END$$
DELIMITER ;
==============================================================================
Sample php 파일소스
==============================================================================
<?
$mysqli = new mysqli('localhost', 'root', 'apmsetup', 'test');
print '<h3>MYSQLI: simple select</h3>';
$rs = $mysqli->query( 'SELECT * FROM users ' );
while($row = mysqli_fetch_array($rs))
{
echo $row['users_id'];
}
print '<h3>MYSQLI: calling sp with out variables</h3>';
$rs = $mysqli->query( 'CALL get_user(1,@first_name,@lastName);' );
$rs = $mysqli->query( 'SELECT @first_name as fst, @lastName as lst' );
while($row = mysqli_fetch_array($rs))
{
echo $row['fst'];
}
print '<h3>MYSQLI: calling sp returning a recordset</h3>';
$rs = $mysqli->query( 'CALL get_users()' );
while($row = mysqli_fetch_array($rs))
{
echo $row['users_id'];
}
?>