[PHP] Oracle, MySQL - PDO 사용방법

[PHP] Oracle, MySQL - PDO 사용방법

 

간결하게 요약해서 작성해보았다.

 

---

1. Oracle, MySQL 데이터베이스 설계하기

 

데이터베이스를 하나 생성한다.

Member를 생성한다.

 

 

그림 1) Oracle SQL Developer에서 Member 테이블 생성하기(Oracle 18c Express Edition)

 

 

그림 2) HeidiSQL에서 Member 테이블 생성하기(MySQL)

 

CREATE TABLE "C##RABBITSUN2"."MEMBER"    ( "ID" VARCHAR2(20 BYTE) NOT NULL ENABLE,  "SUBJECT" VARCHAR2(20 BYTE),   CONSTRAINT "MEMBER_PK" PRIMARY KEY ("ID")   USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)   TABLESPACE "USERS"  ENABLE    ) SEGMENT CREATION IMMEDIATE   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOCOMPRESS LOGGING   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)   TABLESPACE "USERS" ;   CREATE OR REPLACE EDITIONABLE TRIGGER "C##RABBITSUN2"."MEMBER_TRG" BEFORE INSERT ON MEMBER FOR EACH ROW BEGIN   <<COLUMN_SEQUENCES>>   BEGIN        IF INSERTING AND :NEW.ID IS NULL THEN            SELECT MEMBER_SEQ.NEXTVAL INTO :NEW.ID FROM SYS.DUAL;            END IF;   END COLUMN_SEQUENCES; END; / ALTER TRIGGER "C##RABBITSUN2"."MEMBER_TRG" ENABLE;

Oracle SQL

CREATE TABLE `member` (      `id` BIGINT(20) NOT NULL AUTO_INCREMENT,      `subject` VARCHAR(50) NULL DEFAULT NULL,       PRIMARY KEY (`id`)  )  COLLATE='utf8_general_ci'  ENGINE=InnoDB  AUTO_INCREMENT=1;

MySQL SQL

 

 

 

---

2. Oracle, MySQL - PDO 사용방법(소스코드)

 

<?php

/*
 * Subject: PDO
 * Created Date: 2019-12-10
 * Author by: 도도(Dodo) / rabbitsun2@gmail.com
 * Description:
 *
 * Reference:
 * 
 */

include 'MySQLDB.php';
include 'OracleDB.php';

$orcl = new OracleDB('127.0.0.1', 'c##rabbitsun2', '1234');
$orcl->connect();

// SELECT 문(OracleDB)
$query = "SELECT * FROM MEMBER";
$list = $orcl->select($query);

/*
foreach($list as $row){
   
    echo $row["ID"] . "/";
    echo $row["SUBJECT"] . "<br>";
   
}
*/

// INSERT 문(OracleDB)
//$subject = '연습';
//$result = $orcl->insert($subject);

$mysqlDB = new MySQLDB('127.0.0.1', 'rabbit2me', '1234', 'rabbit2me');
$mysqlDB->connect();

$list = $mysqlDB->select($query);

foreach($list as $row){
   echo $row["id"] . "/";
   echo $row["subject"] . "<br>";
}
 
$subject = '연습';
$result = $mysqlDB->insert($subject);

?>

[소스코드: index.php]

 

?>

class MySQLDB{
   
    private $hostName;
    private $userName;
    private $passwd;
    private $tns;
    private $dbh;
   
    public function __construct($hostName, $userName, $passwd, $dbname){
       
        $this->tns = "mysql:host=$hostName;dbname=$dbname";
       
        $this->hostName = $hostName;
        $this->userName = $userName;
        $this->passwd = $passwd;
       
       
    }
   
    public function __destruct(){
        unset($this->dbh);
    }
   
    public function connect(){
       
        $tns = $this->tns;
        $db_username = $this->userName;
        $db_password = $this->passwd;
       
        try{
            $this->dbh = new PDO($this->tns, $db_username, $db_password,
                array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
            //echo "참";
        }catch(PDOException $e){
            echo ($e->getMessage());
        }
       
    }
   
    public function select($query){
       
        $stmt = NULL;
        $dbh = $this->dbh;
       
        if ( $dbh != NULL ){
            $stmt = $dbh->prepare($query);
            $stmt->execute();
            $list = $stmt->fetchAll();
           
            return $list;
        }
       
        return NULL;
       
    }
   
    public function insert($subject){
       
        $stmt = NULL;
        $dbh = $this->dbh;
        $query = "INSERT INTO member(subject) VALUES(:subject)";
       
        if ( $dbh != NULL ){
            $stmt = $dbh->prepare($query);
            $stmt->bindParam(':subject', $subject);
            $stmt->execute();
           
            return true;
        }
       
        return false;
       
    }
   
}

?>

[소스코드: MySQLDB.php]

 

?>
class OracleDB{
   
    private $hostName;
    private $userName;
    private $passwd;
    private $tns;
    private $dbh;
   
    public function __construct($hostName, $userName, $passwd){
       
        $this->tns = "(DESCRIPTION =
            (ADDRESS_LIST =
              (ADDRESS = (PROTOCOL = TCP)(HOST = $hostName)(PORT = 1521))
            )
            (CONNECT_DATA =
              (SERVICE_NAME = xe)
            )
        )";
       
        $this->hostName = $hostName;
        $this->userName = $userName;
        $this->passwd = $passwd;
        $this->dbh = NULL;
       
    }
   
    public function __destruct(){
        unset($this->dbh);
    }
   
    public function connect(){
       
        $tns = $this->tns;
        $db_username = $this->userName;
        $db_password = $this->passwd;
       
        try{
            $this->dbh = new PDO("oci:dbname=". $tns . ";charset=UTF8", $db_username, $db_password);
            //echo "참";
        }catch(PDOException $e){
            echo ($e->getMessage());
        }
       
    }
   
    public function select($query){
       
        $stmt = NULL;
        $dbh = $this->dbh;
       
        if ( $dbh != NULL ){
            $stmt = $dbh->prepare($query);
            $stmt->execute();
            $list = $stmt->fetchAll();
           
            return $list;
        }
       
        return NULL;
       
    }
   
    public function insert($subject){
        $stmt = NULL;
        $dbh = $this->dbh;
        $query = "INSERT INTO MEMBER(SUBJECT) VALUES(:subject)";
       
        if ( $dbh != NULL ){
            $stmt = $dbh->prepare($query);
            $stmt->bindParam(':subject', $subject);
            $stmt->execute();
           
            return true;
        }
       
        return false;
       
    }
   
}

?>

[소스코드: OracleDB.php]