1,我们一般做导出的思路就是,根据我们想要的数据,全部查询出来,然后导出来,这个对数据量很大的时候会很慢,这里我提出来的思想就是分页和缓冲实现动态输出。
2.普通的我就不说了,下面我说一下分页和内存刷新思想。代码如下:
$conn = oci_connect('fin_data', 'fin_data', "(DEscriptION=(ADDRESS=(PROTOCOL =TCP)(HOST=192.168.6.65)(PORT = 1521))(CONNECT_DATA =(SID=hqygdw)))");
$sqlName = "select * from user_tab_columns where Table_Name='S_SALE_RECEIVABLE_DETAIL' order by column_name";
$filedName = oci_parse($conn, $sqlName); oci_execute($filedName, OCI_DEFAULT); // 行数 OCI_DEFAULT表示不要自动commit
header('Content-Type: application/vnd.ms-excel;charset=gbk'); header('Content-Disposition: attachment;filename=test_' . time() . '.csv'); header('Cache-Control: max-age=0');
$fp = fopen('php://output', 'a'); $head = array(); $i = 0; while ($row = oci_fetch_array($filedName, OCI_ASSOC + OCI_RETURN_NULLS)) { $head[$i] = iconv('utf-8', 'gbk', $row['COLUMN_NAME']); $i++; } fputcsv($fp, $head);
$selectCount = "select count(*) as counts from S_SALE_RECEIVABLE_DETAIL t "; $resultCount = oci_parse($conn, $selectCount); // 配置SQL语句,执行SQL oci_execute($resultCount, OCI_DEFAULT); // 行数 OCI_DEFAULT表示不要自动commit $count = oci_fetch_array($resultCount, OCI_ASSOC + OCI_RETURN_NULLS);
//$count['COUNTS']=10; $pageNum = 50000; $pagePre = ceil($count['COUNTS'] / $pageNum); for ($i = 1; $i <= $pagePre; $i++) { $pageStart = ($i - 1) * $pageNum + $pageNum; $pageEnd = ($i - 1) * $pageNum;
$select = "SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM S_SALE_RECEIVABLE_DETAIL) A WHERE ROWNUM <={$pageStart} ) WHERE RN >= {$pageEnd}";
$result = oci_parse($conn, $select); // 配置SQL语句,执行SQL oci_execute($result, OCI_DEFAULT); // 行数 OCI_DEFAULT表示不要自动commit $count =0; while ($rowResult = oci_fetch_array($result, OCI_ASSOC + OCI_RETURN_NULLS)) { // print_r(explode(',',$rowResult));exit; fputcsv($fp,$rowResult); $count++; if($count>=$pageNum){ ob_flush(); flush(); } } }
fputcsv($fp, $head);
|
请发表评论