ORACLE建表语句如下(自动编号栏位+外键定义)：
/*
--drop index SCOTT.utweb_user_funcs_map_IDX1;
drop SEQUENCE scott.utweb_user_funcs_map_autoid;
drop table scott.utweb_user_functions_mapping;
--drop index SCOTT.utweb_functions_IDX1;
drop table scott.utweb_functions;
--drop index SCOTT.utweb_users_IDX1;
drop table scott.utweb_users;
--drop index SCOTT.utweb_visitorlog_IDX1;
drop SEQUENCE scott.utweb_visitorlog_autoid;
drop table scott.utweb_visitorlog;
--drop index SCOTT.utweb_orderconfirmmail_IDX1;
drop SEQUENCE scott.utweb_orderconfirmmail_autoid;
drop table scott.utweb_orderconfirmmail;
*/
-- Create table utweb_functions
create table scott.utweb_functions
(
func_id VARCHAR2(5) not null primary key,
func_name VARCHAR2(50) not null
);
-- Create/Recreate indexes
--create unique index SCOTT.utweb_functions_IDX1 on SCOTT.utweb_functions(func_id);
--primary key会默认生成索引，无需再创建
-- Create table utweb_users
create table scott.utweb_users
(
user_id VARCHAR2(20) not null primary key,
userpwd VARCHAR2(20) not null,
username VARCHAR2(20) not null,
CUSTOMER_ID NUMBER not null,
Contact_id NUMBER not null,
effective_start_date VARCHAR2(10) not null,
effective_end_date VARCHAR2(10)
);
-- Create/Recreate indexes
--create unique index SCOTT.utweb_users_IDX1 on SCOTT.utweb_users(user_id);
--建立自动编号
CREATE SEQUENCE scott.utweb_user_funcs_map_autoid INCREMENT BY 1
START WITH 1
MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE
CACHE 20 NOORDER;
-- Create table utweb_user_functions_mapping
create table scott.utweb_user_functions_mapping
(
autoid NUMBER not null primary key,
func_id VARCHAR2(5) not null,
user_id VARCHAR2(20) not null,
effective_start_date VARCHAR2(10) not null,
effective_end_date VARCHAR2(10),
CONSTRAINT fk_func_id
FOREIGN KEY (func_id)
REFERENCES scott.utweb_functions(func_id),
CONSTRAINT fk_user_id
FOREIGN KEY (user_id)
REFERENCES scott.utweb_users(user_id)
);
-- Create/Recreate indexes
--create unique index SCOTT.utweb_user_funcs_map_IDX1 on SCOTT.utweb_user_functions_mapping(autoid);
--建立自动编号
CREATE SEQUENCE scott.utweb_visitorlog_autoid INCREMENT BY 1
START WITH 1
MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE
CACHE 20 NOORDER;
-- Create table utweb_visitorlog
create table scott.utweb_visitorlog
(
autoid NUMBER not null primary key,
logdate VARCHAR2(10) not null,
logtime VARCHAR2(8) not null,
fromip VARCHAR2(20),
fromaddr VARCHAR2(200),
remark VARCHAR2(100)
);
-- Create/Recreate indexes
--create unique index SCOTT.utweb_visitorlog_IDX1 on SCOTT.utweb_visitorlog(autoid);
--建立自动编号
CREATE SEQUENCE scott.utweb_orderconfirmmail_autoid INCREMENT BY 1
START WITH 1
MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE
CACHE 20 NOORDER;
-- Create table utweb_orderconfirmmail
create table scott.utweb_orderconfirmmail
(
autoid NUMBER not null primary key,
empmail VARCHAR2(20) not null,
empname VARCHAR2(20) not null,
remark VARCHAR2(100)
);
-- Create/Recreate indexes
--create unique index SCOTT.utweb_orderconfirmmail_IDX1 on SCOTT.utweb_orderconfirmmail(autoid);
PHP操作ORACLE代码如下(网页编码UTF8+ORACLE数据库编码GB2312之间的转换)：
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
</head>
<body>
<?php
//echo "123测试test";
$dbCls=new DbClass();
$dbh=$dbCls->ConnDB();
if($dbh==null){echo "连接数据库失败<br>";return;}
//插入或删除某条记录
$strSql="delete from scott.utweb_orderconfirmmail";
$bRet=$dbCls->updatedb($dbh,$strSql);
echo "<br>";
var_dump($bRet);
//自动增长栏位：scott.utweb_orderconfirmmail_autoid.nextval
$strSql="INSERT INTO scott.utweb_orderconfirmmail VALUES(scott.utweb_orderconfirmmail_autoid.nextval,'TEST@TOM.COM','TEST','REMARK TEST')";
$bRet=$dbCls->updatedb($dbh,$strSql);
echo "<br>";
var_dump($bRet);
//查询相关记录
$strSql="select * from scott.utweb_orderconfirmmail";
$arr=$dbCls->querydb($dbh,$strSql);
for($i=0;$i<count($arr);$i++)
{
echo $arr[$i][0]."==".$arr[$i][1]."<br>";
}
?>
</body>
</html>
<?php
//转换数组编码如:TransEncoding($arr,"GB2312","utf-8");//$arr数组从GB2312转为utf8
function TransEncoding(&$array, $charset_in, $charset_out)
{
if ($charset_in == $charset_out)
{
return $array;
}
if (is_array($array))
{
foreach ($array as $key => $value)
{
if (is_string($value))
{
$array[$key] = iconv($charset_in, $charset_out, $value);
}
else if (is_array($value))
{
TransEncoding($array[$key], $charset_in, $charset_out);
}
}
}
else if (is_string($array))
{
$array = iconv($charset_in, $charset_out, $array);
}
return $array;
}
class DbClass{
public $DBserver = "IP/数据库实例名称"; //数据库服务器
public $DBuser = "用户名"; //用户名
public $DBpwd = "密码"; //密码
public $webEncoding="UTF-8";//网页编码
public $dbEncoding="GB2312";//数据库编码
//连接数据库－－返回连接句柄
function ConnDB()
{
try
{
$dbh=new PDO("oci:dbname=$this->DBserver","$this->DBuser","$this->DBpwd");
return $dbh;
}
catch(PDOException $e)
{
echo iconv("GB2312","UTF-8",$e->getMessage());
return null;
}
}
//执行插入或删除SQL语句，返回是否执行成功的结果
function updatedb($dbh,$strSql)
{
//编码要先转换为数据库的编码，编码才不会乱掉
$strSql=iconv($this->webEncoding,$this->dbEncoding,$strSql);
$bRet=false;
try
{
$dbh->beginTransaction();
$stmt=$dbh->prepare($strSql);
$btmp=$stmt->execute();
// var_dump($btmp);
if($btmp)
$bRet=true;
$dbh->commit();
}
catch(PDOException $e)
{
$dbh->rollBack();
$bRet=false;
echo $e->getMessage();
}
return $bRet;
}
//执行查询SQL语句，返回满足条件的记录
function querydb($dbh,$strSql)
{
$Result = null;
$stmt = $dbh->prepare($strSql);
if($stmt)
{
if($stmt->execute())
{
//以索引的方式返回记录值，下标从0开始
while($Rec = $stmt->fetch(PDO::FETCH_NUM))
{
$Result[] = $Rec;
}
}
}
$dbh = null;
//编码要先转换为WEB的编码，才不会乱码
TransEncoding($Result,$this->dbEncoding,$this->webEncoding);
return $Result;
}
}
?>
