ȨÀ¸·Î
| ¹«·áȸ¿ø°¡ÀÔ | ¾ÆÀ̵ð/ºñ¹øÃ£±â
ÃßõÀ½¾Ç¹æ¼Û
Ãʰ£´Ü php - oracle ¿¬µ¿ Ŭ·¡½º
12³â Àü
PHP¿¡¼­ ¿À¶óŬ ¿¬µ¿ÇÏ·Á¸é Á» °íÅ뽺·´´Ù.

¹º°¡ mySQLÀ̶û Á» ´Ù¸¥ ºÎºÐµµ ÀÖ´Â°Í °°°í..

ÇÏ¿© Ŭ·¡½º·Î ¸¸µé¾îº¸¾Ò´Ù.



class Oracle
{
  var $DBID = "id";
  var $DBPW = "pw";
  var $DBSID = "sid";

  var $conn;
  var $stmt;
        
  var $error = false;    // ¿¡·¯ ¹ß»ýÇϸé true ·Î ¼öÁ¤µÊ. commit,rollback °áÁ¤¿¡ »ç¿ë
  var $transaction = false;    // true ¸é auto commit ¾ÊÇÔ

  var $bind = array();
  var $data_size = array();

  // php4 ÀÇ »ý¼ºÀÚ
  function Oracle(){    
            
    $this->connect();
  }
  // php5 ÀÇ »ý¼ºÀÚ
  function __construct(){    
            
    $this->connect();
  }
  // php5 ÀÇ ¼Ò¸êÀÚ
  function __destruct(){    
            
    $this->disconnect();
  }

  function connect(){

    if(!$this->conn)
      $this->conn = OCILogon($this->DBID,$this->DBPW,$this->DBSID);
  }
        
  function disConnect(){

    if($this->stmt)
      @OCIFreeStatement($this->stmt);
    if($this->conn)
      @OCILogoff($this->conn);
  }
  // ¹ÙÀε庯¼ö °ª ÁöÁ¤
  // °°Àº °ªÀÌ¶óµµ executeDML() È£ÃâÀü¿¡ ¹Ýµå½Ã ¸Å¹ø È£ÃâÇØ¾ß ÇÔ(executeDML() ÇÔ¼öÈ£ÃâÈÄ ÃʱâÈ­µÇ¹Ç·Î)
  function setBind($bind){

    if(is_array($bind))
      $this->bind = $bind;
    else if($bind)
      $this->bind = array($bind);
  }
  // ¹ÙÀε庯¼ö »çÀÌÁî ÁöÁ¤. ÁöÁ¤¾ÈÇϸé ÇØ´çº¯¼öÀÇ ÃÖ´ë»çÀÌÁî°¡ ±âº»°ªÀÓ
  function setDataSize($data_size){

    if(is_array($data_size))
      $this->data_size = $data_size;
    else if($data_size)
      $this->data_size = array($data_size);
  }
  // Äõ¸®¹® °á°ú¸¦ '´ÙÁ߹迭($rs[Çʵå¸í][À妽º])'·Î ¸®ÅÏÇÑ´Ù
  // $preferch_size´Â °¡Á®¿Ã ·¹ÄÚµå°Ç¼ö¸¦ ÁöÁ¤ÇÔ(¿É¼Ç)
  function selectList($query,$preferch_size=1){
            
    $this->connect();
    $this->stmt = OCIparse($this->conn,$query);

    if($this->stmt){

      $this->bindByName();                                                        
      $this->prefetch($preferch_size);                                        

      if($this->transaction){                        
        @OCIexecute($this->stmt,OCI_DEFAULT);
        $this->error();
      }else{
        @OCIexecute($this->stmt);
      }
      $rows = @OCIFetchStatement($this->stmt,$rs);                
    
      if($rows){                    
        if(!$this->transaction)
          @OCIFreeStatement($this->stmt);
        return $rs;
      }
    }            
    return array();
  }
  // Äõ¸®¹® °á°ú 1°ÇÀ» '¹è¿­($rs[Çʵå¸í or À妽º])'·Î ¸®ÅÏÇÑ´Ù
  // $option Àº OCI_ASSOC(Çʵå¸í) or OCI_NUM(À妽º) À» ÁöÁ¤(¿É¼Ç)
  function selectRow($query,$option=OCI_ASSOC){
            
    $this->connect();
    $this->stmt = OCIparse($this->conn,$query);

    if($this->stmt){

      $this->bindByName();

      if($this->transaction){                        
        @OCIexecute($this->stmt,OCI_DEFAULT);
        $this->error();
      }else{
        @OCIexecute($this->stmt);
      }
      if(@OCIFetchInto($this->stmt,$rs,$option+OCI_RETURN_NULLS)){
        if(!$this->transaction)
          @OCIFreeStatement($this->stmt);
        return $rs;
      }                    
    }            
    return array();
  }
  // ÇѰ³ °ª¸¸ ¸®ÅÏÇÏ´Â Äõ¸®¹®À» ó¸®ÇÑ´Ù
  // ROWID,LOB,FILE µî ¿Ü¿¡´Â ½ºÆ®¸µÀ¸·Î ¹ÝȯÇÑ´Ù
  function selectOne($query){

    $this->connect();
    $this->stmt = OCIparse($this->conn,$query);        

    if($this->stmt){

      $this->bindByName();

      if($this->transaction){                        
        @OCIexecute($this->stmt,OCI_DEFAULT);
        $this->error();
      }else{
        @OCIexecute($this->stmt);
      }
      if(@OCIFetch($this->stmt)){

        // Àμö 1Àº Ä÷³¼ø¼­ À妽º(1ºÎÅÍ ½ÃÀÛÇÔ¿¡ ÁÖÀÇ!,Ä÷³¸í ÁöÁ¤µµ °¡´É)
        $value = @OCIResult($this->stmt,1);    
        if(!$this->transaction)
          @OCIFreeStatement($this->stmt);
        return $value;
      }
    }
  }
  // insert,update,deleteµîÀ» ½ÇÇàÈÄ ¿µÇâ¹ÞÀº ·Î¿ìÀÇ °¹¼ö¸¦ ¸®ÅÏÇÑ´Ù(auto commitÀÏ °æ¿ì¸¸ °¹¼ö¸¦ ¸®ÅÏÇÔ)
  // $transaction=true ÀÏ °æ¿ì ¸¶Áö¸·¿¡ ¹Ýµå½Ã commit() ÇÔ¼ö¸¦ È£ÃâÇÑ´Ù
  function executeDML($sql){

    $this->connect();
    $this->stmt = OCIparse($this->conn,$sql);    
            
    if($this->stmt){

      $this->bindByName();

      if($this->transaction){    // auto commit ÀÌ ¾Æ´Ò °æ¿ì
        @OCIexecute($this->stmt,OCI_DEFAULT);
        $this->error();
      }else{
        @OCIexecute($this->stmt);
        $num = @OCIRowCount($this->stmt);
        @OCIFreeStatement($this->stmt);
                    
        return $num;                    
      }
    }
  }
  // auto commit ¾ÊÇÏ°í ¸í½ÃÀûÀ¸·Î transaction ½ÃÀÛ
  // executeDML() È£ÃâÈÄ ¸¶Áö¸·¿¡ ¹Ýµå½Ã commit() ÇÔ¼ö¸¦ È£ÃâÇÑ´Ù
  function transaction(){

    $this->transaction = true;
  }
  // transaction ¿Ï·áÈÄ commit À̸é true¸¦ ¸®ÅÏÇÔ
  // executeDML() ¿¡¼­ ¿¡·¯°¡ Çϳª¶óµµ ¹ß»ýÇϸé ÀÚµ¿ rollback µÊ
  function commit(){

    if(!$this->error){
      @OCICommit($this->conn);
      $commit = true;
    }else{
      @OCIRollback($this->conn);
      $commit = false;
    }
    if($this->stmt)
      @OCIFreeStatement($this->stmt);

    $this->transaction = false;
    $this->error = false;

    return $commit;
  }
  // $transaction=true ÀÏ °æ¿ì ¸Å¹ø executeDML ¿¡¼­ ÀÚµ¿È£ÃâµÈ´Ù
  // ¿¡·¯ ¹ß»ýÇÒ °æ¿ì commit,rollbackÀÇ ÆÇ´Ü±âÁØÀÎ $error °ªÀ» º¯°æ
  function error(){

    if($error = @OCIError($this->stmt)){                
      //echo "<p> Error is : " . $error["code"] . " - " . $error["message"] . "<p>";
      $this->error = true;
    }
  }
  // :b1,:b2,:b3...¿¡ ¹ÙÀÎµå º¯¼ö ÁöÁ¤
  // ¹ÙÀε庯¼ö¸íÀº ¹Ýµå½Ã :b1,:b2,:b3... À¸·Î ÁöÁ¤ÇÑ´Ù
  function bindByName(){

    $size = sizeof($this->bind);

    for($i=0 ; $i < $size ; $i++){

      $ds = $this->data_size[$i];
      if(!$ds) $ds = -1;

      @OCIBindByName($this->stmt,":b".($i+1),$this->bind[$i],$ds);
    }
    $this->bind = array();                                                        
    $this->data_size = array();
  }
  // ¿À¶óŬ Ŭ¶óÀÌ¾ðÆ® ¹öÆÛ¿¡ ÀúÀåµÇ´Â ·¹ÄÚµåÀÇ ¼ö¸¦ ÁöÁ¤
  // ¿©·¯ ·¹Äڵ带 selectÇÒ °æ¿ì µðÆúÆ® °ªÀÌ À۾Ƽ­ ºñÈ¿À²ÀûÀÌ¸é °¡Á®¿Ã °Ç¼ö¸¸Å­ ÁöÁ¤
  function prefetch($preferch_size){

    if($preferch_size > 1)
      @OCISetPrefetch($this->stmt,$preferch_size);
  }
}

################# ¿©±â¼­ºÎÅÍ »ç¿ë¿¹ ÀÔ´Ï´Ù #################

1. ¿©·¯°ÇÀÇ ·¹Äڵ带 select ÇÒ °æ¿ì
$db = new Oracle();
$query = "select title,name,date from tb where ... »ý·«";
$rs = $db->selectList($query);
for( $i=0 ; $i < sizeof($rs["TITLE"]) ; $i++ ){
    echo $rs["TITLE"][$i] . $rs["NAME"][$i] . $rs["DATE"][$i] . "<br>";
}
* ÁÖÀÇÇÏ½Ç°Ç ²À ¹è¿­¿¡ ´ë¹®ÀÚ·Î ÀûÀ¸¼Å¾ß ÇÑ´Ù´Â °Ì´Ï´Ù

2. ÇѰÇÀÇ ·¹Äڵ带 select ÇÒ °æ¿ì
$query = "select title,name,date from tb where rownum=1";
$rs = $db->selectRow($query);
echo $rs["TITLE"] . $rs["NAME"] . $rs["DATE"];

3. ÇѰ³°ªÀ»  select ÇÒ °æ¿ì
$query = "select count(*) from tb";
$value = $db->selectOne($query);
echo "ÃÑ " . $value . " °Ç ÀÔ´Ï´Ù";

4. insert,update,delete ÇÒ °æ¿ì
$sql = "insert into tb (title,name) values ('$title','$name')";
$rs = $db->executeDML($sql);
if($rs){
echo $rs . " °ÇÀÇ µ¥ÀÌÅ͸¦ ó¸®ÇÏ¿´½À´Ï´Ù";
}    

5. Æ®·£Á§¼Ç ó¸®
$sql1 = "insert into tb1 ... »ý·«";
$sql2 = "insert into tb2 ... »ý·«";
$sql3 = "insert into tb3 ... »ý·«";
$db->transaction();
$db->executeDML($sql1);
$db->executeDML($sql2);
$db->executeDML($sql3);
$rs = $db->commit();
if($rs){
  echo "ó¸® ¼º°ø!";
}else{
  echo "¿¡·¯ ¹ß»ý!";
}
transaction() ÇÔ¼ö¸¦ È£ÃâÇϸé auto commit ÀÌ false °¡ µÇ¸ç ¸¶Áö¸·¿¡ commit() ÇÔ¼ö¸¦ È£ÃâÇÏ¸é ¿¡·¯°¡ Çϳª¶óµµ ¹ß»ýÇϸé ÀÚµ¿À¸·Î ·Ñ¹éµË´Ï´Ù ¹°·Ð ¿¡·¯°¡ ¾øÀ¸¸é Ä¿¹ÔµÇ±¸¿ä ÇÔ¼ö¸íÀÌ commit À̶ó°í ¹«Á¶°Ç Ä¿¹ÔµÇ´Â°Å ¾Æ´Õ´Ï´Ù ^^
transaction() ÇÔ¼ö¸¦ È£Ãâ¾ÊÇϰí executeDML()À» »ç¿ëÇϸé Ç×»ó auto commit ÀÔ´Ï´Ù

6. ¹ÙÀε庯¼ö »ç¿ë
$query = "select name from tb where id=:b1 and pw=:b2";
$bind = array($id,$pw);
$db->setBind($bind);
$name = $db->selectOne($query);
echo $name;

¹ÙÀÎµå º¯¼ö¸íÀº ²À :b1,:b2,:b3... ÀÌ·±½ÄÀ¸·Î ÁöÁ¤Çϸç setBind() ÇÔ¼ö¿¡ ¹è¿­·Î °ªÀ» ³Ñ°ÜÁÝ´Ï´Ù
¹ÙÀε庯¼ö°¡ :b1 ÇѰ³¸¸ »ç¿ëµÈ °æ¿ì´Â ¹è¿­ÀÌ ¾Æ´Ï¶ó °ªÇϳª¸¸ ³Ñ°Üµµ µË´Ï´Ù(¾Æ·¡¿¹ ó·³)
³Ñ°ÜÁø °ªÀº selectXxx(),executeDML()µîÀÇ ÇÔ¼ö»ç¿ëÈÄ¿¡ ÃʱâÈ­µÇ¹Ç·Î ÇÔ¼ö ½ÇÇàÀü¿¡ ¸Å¹ø setBind() ÇÔ¼ö¸¦ È£ÃâÇØ¾ß ÇÕ´Ï´Ù
¾Æ·¡¿¹´Â Ʋ¸° »ç¿ë¿¹ÀÔ´Ï´Ù $cnt2 ¿¡´Â ¾Æ¹«°ªµµ ¾ø½À´Ï´Ù
$query1 = "select count(*) from tb1 where id=:b1";
$query2 = "select count(*) from tb2 where id=:b1";
$db->setBind($id);
$cnt1 = $db->selectOne($query1);
$cnt2 = $db->selectOne($query2);

¾Æ·¡¿¹°¡ ¿ÇÀº »ç¿ë¿¹ÀÔ´Ï´Ù
$db->setBind($id);
$cnt1 = $db->selectOne($query1);
$db->setBind($id);
$cnt2 = $db->selectOne($query2);

7. Æ®·£Á§¼Ç ó¸®¿Í ¹ÙÀÎµå º¯¼ö »ç¿ë
$sql1 = "insert into tb(title,name) values(:b1,:b2)";
$sql2 = "update tb2 set addr=:b1 where id=:b2";
$bind1 = array($title,$name);
$bind2 = array($addr,$id);
$db->transaction();
$db->setBind($bind1);
$db->executeDML($sql1);
$db->setBind($bind2);
$db->executeDML($sql2);
$rs = $db->commit();
if($rs){
  echo "ó¸® ¼º°ø!";
}else{
  echo "¿¡·¯ ¹ß»ý!";
}

ÃßõÃßõ : 232 Ãßõ ¸ñ·Ï
¹øÈ£ Á¦¸ñ
3,046
 Vimeo (ºñ¸Þ¿À) API ¸¦ »ç¿ëÇÏ¿© Ç÷¹À̾î ÄÁÆ®·ÑÇϱâ
3,045
 iframe »ç¿ë½Ã ÇÏ´Ü¿¡ ¹ß»ýÇÏ´Â °ø¹é Á¦°Å¹æ¹ý
3,044
 ¾ÆÀÌÇÁ·¹ÀÓ(iframe) Àüüȭ¸é °¡´ÉÇÏ°Ô Çϱâ
3,043
 ºÎÆ®½ºÆ®·¦(bootstrapk)¿¡¼­ »ç¿ëÇÏ´Â class¸í Á¤¸®
3,042
 ºÎÆ®½ºÆ®·¦ CSS
3,041
 Å©·Ò¿¡¼­ ¸¶Áø Á¶Àý
3,040
 PHP ÇöÀç ÆäÀÌÁöÀÇ µµ¸ÞÀθíÀ̳ª urlµîÀÇ Á¤º¸ ¾Ë¾Æ¿À±â
3,039
 PHP preg match all()
3,038
 PHP ·Î À¥ÆäÀÌÁö ±Ü¾î¿À±â ¸ðµç ¹æ¹ý ÃÑÁ¤¸®!
3,037
 [PHP] ¿ø°ÝÁö ÆÄÀÏ ÁÖ¼Ò ³ëÃâ ¾ÈÇϰí curl·Î ´Ù¿î·Îµå ¹Þ±â
3,036
 PHP ÇÔ¼ö Á¤¸®
3,035
 ¾ÆÀÌÇÁ·¹ÀÓ(iframe) ºñÀ² À¯ÁöÇϸ鼭 Å©±â Á¶ÀýÇÏ´Â ¹æ¹ý
3,034
 PHP ¹è¿­¿¡¼­ ¹«ÀÛÀ§·Î Çϳª »Ì¾ÆÁÖ´Â array rand() ÇÔ¼ö
3,033
 PHP Á¤±Ô½Ä Á¤¸®
3,032
 PHP Á¤±Ô½ÄÀ» Ȱ¿ëÇÑ ÅÂ±× ¹× ƯÁ¤ ¹®ÀÚ¿­ Á¦°Å ¹× ÃßÃâ ¹æ¹ý
3,031
 php Å©·Ñ¸µ ¶Ç´Â ÆÄ½Ì ÇÔ¼ö, Á¤±Ô½Ä ¸ðÀ½
3,030
 Á¦ÀÌÄõ¸® ±âº» ¸í·É¾î
3,029
 À¥ÆäÀÌÁö °¡·Î ¸ðµå¼¼·Î ¸ðµå ÀνÄÇϱâ
3,028
 ¸ð¹ÙÀÏ À¥ È­¸é °­Á¦ ȸÀü(°¡·Î¸ðµå °íÁ¤)
3,027
 [HTML5]¿¡¼­ frameset ´ëü ¹æ¹ý°ú iframe ¼Ó¼º
3,026
 HTML <Audio> »ç¿ë¹ý
3,025
 À©µµ¿ì10 ½Ã½ºÅÛÆÄÀÏ ¼Õ»ó (Ãʰ£´Ü ¿À·ù º¹±¸¹æ¹ý!!)
3,024
 PHP ÆÄÀÏ Á¸Àç ¿©ºÎ ÆÄ¾ÇÇϱâ(·ÎÄà ÆÄÀÏ Á¸Àç ¹× ¿ø°ÝÁö ÆÄÀÏ Á¸Àç)
3,023
 [CSS] ¹Ú½º ¼¼·Î °¡¿îµ¥ Áß¾Ó Á¤·Ä 6°¡Áö
3,022
 CSS Layout ¼öÆò & ¼öÁ÷ Á¤·Ä
¸ñ·Ï
¹ÂÁ÷Æ®·ÎÆ® ºÎ»ê±¤¿ª½Ã ºÎ»êÁø±¸ °¡¾ßµ¿ ¤Ó °³ÀÎÁ¤º¸Ãë±Þ¹æÄ§
Copyright (C) musictrot All rights reserved.