php 오라클 select

<?php
/*
 * 오라클 접속 테스트
 * */


function ec($str){
    echo $str."<br>";
}

$conn = @oci_connect('아이디', '패스워드', '호스트/SID', 'UTF8');


if(!$conn) {
    echo "No Connection ".oci_error();
    exit;
} else {
    echo "Connect Success!";
}
ec( "--------------------------");



//조회 할 테이블
$tableName = '테이블 명';



/*==============================
 * 멀티 쿼리
 ==============================*/

/* 알수 없는 전체 컬럼에 대한 조회(프록시 등 직접 접근이 어려울 때) */

//컬럼 갯수 조회
//$query = "select count(*) AS 컬럼수 from USER_TAB_COLUMNS where table_name ='".$tableName."'";
//$stid = oci_parse($conn, $query);
//oci_execute($stid);
//
//list($colCnt) = oci_fetch_row($stid);
//ec($tableName.' 테이블 컬럼 수 : '.$colCnt.'개');
//
//
//
//$query = "select * from ".$tableName." where rownum <= 20 ";
//$stid = oci_parse($conn, $query);
//oci_execute($stid);
//
//
//while($row = oci_fetch_array($stid))
//{
//    for($i=0; $i<$colCnt; $i++){
//        echo $row[$i]."/";
//    }
//    echo "<br>";
//}
//
//ec( "Total Count : " . oci_num_rows($stid));
//




/* 기본 */
$query = "select EMP_NO AS EMP_NO별칭, NAME AS NAME별칭 from ".$tableName." where rownum <= 20 order by EMP_NO asc ";
$stid = oci_parse($conn, $query);
oci_execute($stid);

while($row = oci_fetch_array($stid))
{
    ec($row["EMP_NO별칭"]);
    ec($row[1]);
}
ec( "Total Count : " . oci_num_rows($stid));


ec( "--------------------------");

//또는 아래 처럼..
$query = "select EMP_NO AS EMP_NO별칭, NAME AS NAME별칭 from ".$tableName." where rownum <= 20 order by EMP_NO asc ";
$stid = oci_parse($conn, $query);
oci_execute($stid);

while(($row = oci_fetch_row($stid)) != false) {
    ec($row[0]);
    ec($row[1]);
}

ec( "Total Count : " . oci_num_rows($stid));



ec( "--------------------------");


/*==============================
 * 단일 쿼리
 ==============================*/

/* one row */
$query = "select EMP_NO AS EMP_NO별칭, NAME AS NAME별칭 from ".$tableName." where rownum = 1 order by EMP_NO asc ";
$stid = oci_parse($conn, $query);
oci_execute($stid);

list($rec1, $rec2) = oci_fetch_row($stid);
ec("rec1 : ".$rec1 . " / rec2 : ".$rec2);


//또는 아래 처럼..
$query = "select EMP_NO AS EMP_NO별칭, NAME AS NAME별칭 from ".$tableName." where rownum = 1 order by EMP_NO asc ";
$stid = oci_parse($conn, $query);
oci_execute($stid);

$row = oci_fetch_row($stid);
ec("row[0] : ".$row[0] . " / row[1] : " . $row[1]);



ec( "--------------------------");


/* one rec 한개 컬럼*/
$query = "select EMP_NO AS EMP_NO별칭, NAME AS NAME별칭 from  ".$tableName." where rownum = 1 order by EMP_NO asc ";
$stid = oci_parse($conn, $query);
oci_execute($stid);

list($rec) = oci_fetch_row($stid);
ec("rec : ".$rec);


//또는 아래 처럼..
$query = "select EMP_NO, NAME from ".$tableName." where rownum = 1 order by EMP_NO asc ";
$stid = oci_parse($conn, $query);
oci_execute($stid);

$row = oci_fetch_row($stid);
ec("row[0] : ".$row[0]);



// 오라클 접속 닫기
oci_free_statement($stid);

// 오라클에서 로그아웃
oci_close($conn);

?>

 

 

Alias사용 예

 

※예제

SELECT 컬럼1, 컬럼2 별칭 FROM emp

SELECT 컬럼1, 컬럼2 as 별칭 FROM emp

SELECT 컬럼1, 컬럼2 "별칭" FROM emp

SELECT 컬럼1, 컬럼2 "별  칭" FROM emp

SELECT 컬럼1, 컬럼2 "*별칭*" FROM emp