MySQL与oracle数据库的一些区别

1、 组函数用法规则

mysql中组函数在select语句中可以随意使用,但在oracle中 如果查询语句中有组函数,那其他列名必须是组函数处理过的,或者是group by子句中的列 否则报错。

eg:

select name,count(money) from user ;这个放在mysql中没有问题 在oracle中就有问题了.............

2、自动增长的数据类型处理

MYSQL有自动增长的数据类型,插入记录时不用操作此字段,会自动获得数据值。ORACLE没有自动增长的数据类型,需要建立一个自动增长的序列号,插入记录时要把序列号的下一个值赋于此字段。

CREATE SEQUENCE 序列号的名称 (最好是表名+序列号标记) INCREMENT BY 1 START WITH 1 MAXVALUE 99999 CYCLE NOCACHE;

其中最大的值按字段的长度来定, 如果定义的自动增长的序列号 NUMBER(6) , 最大值为999999

INSERT 语句插入这个字段值为: 序列号的名称.NEXTVAL。 Continue reading

如何收集及删除列的统计信息

本文只涉及使用dbm_stats来收集或删除列的统计信息的一些命令,以备查询。

测试表如下(实验环境为10.2.0.4):

SQL> create table test(i int,a varchar2(30));
Table created.

SQL> insert into test select rownum,object_name from all_objects;
9907 rows created.

简单的说,列的统计信息,主要包括两种类型:

  • 只有基本信息:收集的统计信息只有1个桶(bucket)
  • 包含柱状图信息:收集的统计信息包含2到254个桶

也就是说,如果想收集列的基本信息,同时不希望收集柱状图,则需要指定bucket的size为1:

SQL> exec dbms_stats.gather_table_stats(user, 'TEST',
cascade=>false, method_opt=>'for columns i size 1');

PL/SQL procedure successfully completed.                                                                             

SQL> select column_name,num_distinct,num_buckets,low_value,high_value,density,avg_col_len,histogram
  2  from user_tab_columns where table_name='TEST';

COLUMN_NAM NUM_DISTINCT NUM_BUCKETS LOW_VALUE  HIGH_VALUE    DENSITY AVG_COL_LEN HISTOGRAM
---------- ------------ ----------- ---------- ---------- ---------- ----------- ---------------
I                  9907           1 C102       C26408     .000100939           4 NONE
A                                                                                NONE

如果要收集列的柱状图信息,则bucket的个数必须大于等于2(最多不超过254)

SQL> exec dbms_stats.gather_table_stats(user, 'TEST',
cascade=>false, method_opt=>'for columns i size 2');

PL/SQL procedure successfully completed.

SQL> select column_name,num_distinct,num_buckets,low_value,high_value,density,avg_col_len,histogram
  2  from user_tab_columns where table_name='TEST';

COLUMN_NAM NUM_DISTINCT NUM_BUCKETS LOW_VALUE  HIGH_VALUE    DENSITY AVG_COL_LEN HISTOGRAM
---------- ------------ ----------- ---------- ---------- ---------- ----------- ---------------
I                  9907           2 C102       C26408     .000100939           4 HEIGHT BALANCED
A                                                                                NONE

如果要删除列已有的柱状图信息而保留列的基本统计信息,则需要重新收集bucket为1的统计信息

SQL> exec dbms_stats.gather_table_stats(user, 'TEST',
cascade=>false, method_opt=>'for columns i size 1');

PL/SQL procedure successfully completed.

SQL> select column_name,num_distinct,num_buckets,low_value,high_value,density,avg_col_len,histogram
  2  from user_tab_columns where table_name='TEST';

COLUMN_NAM NUM_DISTINCT NUM_BUCKETS LOW_VALUE  HIGH_VALUE    DENSITY AVG_COL_LEN HISTOGRAM
---------- ------------ ----------- ---------- ---------- ---------- ----------- ---------------
I                  9907           1 C102       C26408     .000100939           4 NONE
A                                                                                NONE

这个操作明显不太合理,重新收集统计信息的代价有时候是很大的,所以Oracle11g对此做出了改进,允许只删除柱状图而保留基本统计信息,命令语法如下:

exec dbms_stats.delete_column_stats('', '
', '', col_stat_type=>'HISTOGRAM');

而要彻底删除整个列的统计信息,则需要调用delete_column_stats过程

SQL> exec dbms_stats.delete_column_stats(user, 'TEST', 'I');

PL/SQL procedure successfully completed.

SQL> select column_name,num_distinct,num_buckets,low_value,high_value,density,avg_col_len,histogram
  2  from user_tab_columns where table_name='TEST';

COLUMN_NAM NUM_DISTINCT NUM_BUCKETS LOW_VALUE  HIGH_VALUE    DENSITY  AVG_COL_LEN HISTOGRAM
---------- ------------ ----------- ---------- ---------- ----------  ----------- ---------------
I                                                                                 NONE
A                                                                                 NONE

可以在同一个过程中收集多个列的统计信息,并且可以为不同的列指定不同的bucket个数:

SQL> exec dbms_stats.gather_table_stats(user, 'TEST',
cascade=>false, method_opt => 'for columns size 1 T for columns size 2 A');

PL/SQL procedure successfully completed.

SQL> select column_name,num_distinct,num_buckets,low_value,high_value,density,avg_col_len,histogram
  2  from user_tab_columns where table_name='TEST';

COLUMN_NAM NUM_DISTINCT NUM_BUCKETS LOW_VALUE  HIGH_VALUE              DENSITY AVG_COL_LEN HISTOGRAM
---------- ------------ ----------- ---------- -------------------- ---------- ----------- ---------------
I                  9907           1 C102       C26408               .000100939           4 NONE
A                  7376           2 41         5F75746C245F6C6E635F .000185239          18 HEIGHT BALANCED
                                               696E645F7061727473

值得注意的是,从上一篇文章我们知道,9i的dbms_stats中,method_opt的默认值是FOR ALL COLUMNS SIZE 1,也就是收集列的基本统计信息而不收集柱状图信息,而10g的默认值则变成了FOR ALL COLUMNS SIZE AUTO,则Oracle在收集列的基本信息之外,还会根据情况收集某些列的柱状图。

如何根据rowid获取extent_id

我们知道,rowid是由四部分组成的,分别是data_object_id,file_id,block_number和row_number,通过oracle提供的dbms_rowid包可以很方便的将一串rowid解析出上述四部分的内容。然后根据这些信息,则可以获取其extent_id。

SYS@datac>declare
  2  v_block_id number;
  3  v_file_id number;
  4  v_object_id number;
  5  v_extent_id number;
  6  v_object_name varchar2(30);
  7  v_owner varchar2(30);
  8  v_rowid varchar2(20):='AAACrKAAXAAAAzUAAH';
  9  begin
 10  select dbms_rowid.ROWID_BLOCK_NUMBER(v_rowid),
 11         dbms_rowid.ROWID_RELATIVE_FNO(v_rowid),
 12         dbms_rowid.ROWID_OBJECT(v_rowid)
 13   into v_block_id,v_file_id,v_object_id
 14  from dual;
 15
 16  select owner,object_name
 17    into v_owner,v_object_name
 18  from dba_objects
 19  where data_object_id=v_object_id;
 20
 21  select extent_id into v_extent_id
 22  from dba_extents
 23  where owner=v_owner
 24  and segment_name=v_object_name
 25  and file_id=v_file_id
 26  and v_block_id between block_id and block_id+blocks-1;
 27
 28  dbms_output.put_line('         rowid: '||v_rowid);
 29  dbms_output.put_line('       file_id: '||v_file_id);
 30  dbms_output.put_line('      block_id: '||v_block_id);
 31  dbms_output.put_line('data_object_id: '||v_object_id);
 32  dbms_output.put_line('         owner: '||v_owner);
 33  dbms_output.put_line('   object_name: '||v_object_name);
 34  dbms_output.put_line('     extent_id: '||v_extent_id);
 35  end;
 36  /
         rowid: AAACrKAAXAAAAzUAAH
       file_id: 23
      block_id: 3284
data_object_id: 10954
         owner: NINGOO
   object_name: TEST
     extent_id: 5

将上述代码打包到一个shell脚本中,rowid通过参数传入,则可以更方便日常环境中使用。工欲善其事,必先利其器,将经验转化为工具,利用工具提升效率,才能做一个Lazy DBA

$ tbsql rowid AAACrKAAXAAAAzUAAH
         rowid: AAACrKAAXAAAAzUAAH
       file_id: 23
      block_id: 3284
data_object_id: 10954
         owner: NINGOO
   object_name: TEST
     extent_id: 5

$ tbsql rowid AAACrKAAZAAABiiAAR
         rowid: AAACrKAAZAAABiiAAR
       file_id: 25
      block_id: 6306
data_object_id: 10954
         owner: NINGOO
   object_name: TEST
     extent_id: 7

如何获得Oracle用户创建和授权语句

有时候,我们需要在不同的库中复制用户定义,比如需要在一个测试库中创建和产品库中同名的用户,并且拥有同样的权限。或者在同一个库中创建一个不同名的用户,但是和另外一个用户拥有同样的权限等。换句话说,就是需要获得某个用户的创建和授权语句。

可以通过SQL从一些数据字典中查询到授权信息,生成授权语句:

undefine user_name
set pagesize 1000
select 'grant '||tt.granted_role||' to '||tt.grantee||';' as SQL_text
from dba_role_privs tt where tt.grantee=(upper('&&user_name'))
union all
select 'grant '||tt.privilege||' to '||tt.grantee||';'
from dba_sys_privs tt where tt.grantee=(upper('&&user_name'))
union all
select 'grant '||tt.privilege||' on '||owner||'.'||table_name||' to '||tt.grantee||';'
from dba_tab_privs tt where tt.grantee=(upper('&&user_name'))
union all
select 'alter user '||tt.user_name||' quota '||maxblocks*blocksize||' on '||ts_name||';'
from KU$_TSQUOTA_VIEW tt where tt.user_name=(upper('&&user_name'));

 

另外,通过Oracle提供的dbms_metadata包,可以获得更加详细准确的创建用户以及授权的DDL语句,注意在9i中dbms_output.put_line中限制一行不能超过255个字符,所以如果某些授权语句超长,可能无法打印出来,折衷的办法可能,先将结果插入的临时表然后select出来,或者将一行截断循环打印,或者干脆使用前面的SQL语句直接查数字字典表就没有这个限制了:

set serveroutput on size 1000000
set verify off
undefine user_name
declare
 v_name varchar2(30) := upper('&user_name');
 no_grant exception;
 pragma exception_init( no_grant, -31608 );
begin
 dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);
 dbms_output.enable(1000000);
 dbms_output.put_line(dbms_metadata.get_ddl('USER',v_name));
 begin
   dbms_output.put_line(dbms_metadata.get_granted_ddl('SYSTEM_GRANT',v_name));
 exception
   when no_grant then dbms_output.put_line('-- No system privs granted');
 end;
 begin
   dbms_output.put_line(dbms_metadata.get_granted_ddl('ROLE_GRANT',v_name));
 exception
   when no_grant then dbms_output.put_line('-- No role privs granted');
 end;
 begin
   dbms_output.put_line(dbms_metadata.get_granted_ddl('OBJECT_GRANT',v_name));
 exception
   when no_grant then dbms_output.put_line('-- No object privs granted');
 end;
 begin
  dbms_output.put_line(dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA',v_name));
 exception
   when no_grant then dbms_output.put_line('-- No tablespace quota specified');
 end;
 dbms_output.put_line(dbms_metadata.get_granted_ddl('DEFAULT_ROLE', v_name ));
exception
 when others then
  if SQLCODE = -31603 then dbms_output.put_line('-- User does not exists');
  else raise;
  end if;
end;
/

运行结果如下:

输入 user_name 的值:  NinGoo

   CREATE USER "NINGOO" IDENTIFIED BY VALUES
'S:76033D49338E38166B0C090A4447B3D58A70B16C2001A39D7AA844B25616;DABAE35759'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP";
  GRANT UNLIMITED TABLESPACE TO "NINGOO";
   GRANT "CONNECT" TO "NINGOO";
   GRANT "RESOURCE" TO "NINGOO";
   GRANT "DBA" TO "NINGOO";
  GRANT "PLUSTRACE" TO "NINGOO";
  GRANT SELECT ON "SYS"."V_$PROCESS" TO "NINGOO";
  GRANT SELECT ON "SYS"."V_$SESSION" TO "NINGOO";

  DECLARE
  TEMP_COUNT NUMBER;
  SQLSTR VARCHAR2(200);
BEGIN
  SQLSTR :=
'ALTER USER "NINGOO" QUOTA 1048576000 ON "USERS"';
  EXECUTE IMMEDIATE
SQLSTR;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -30041 THEN
      SQLSTR
:= 'SELECT COUNT(*) FROM USER_TABLESPACES
              WHERE TABLESPACE_NAME =
''USERS'' AND CONTENTS = ''TEMPORARY''';
      EXECUTE IMMEDIATE SQLSTR INTO
TEMP_COUNT;
      IF TEMP_COUNT = 1 THEN RETURN;
      ELSE RAISE;
      END
IF;
    ELSE
      RAISE;
    END IF;
END;
/

   ALTER USER "NINGOO" DEFAULT ROLE ALL;

PL/SQL 过程已成功完成。

新一篇: Oracle数据库的安全性措施概述

 一、Oracle中的实体完整性
   
Oracle在CREATE TABLE语句中提供了PRIMARY KEY子句,供用户在建表时指定关系的主码列。例如,在学生-选课数据库中,要定义Student表的Sno为主码,可使用如下语句:

CREATE TABLE student
      (sno 
NUMBER(8
),
       sanme 
VARCHAR(20
),
       sage 
NUMBER(20
),
       
CONSTRAINT pk_sno PRIMARY KEY(sno));

       其中,PRIMARY KEY(Sno)表示是Student表的主码。PK_SNO是此主码约束名。

    若要在SC表中定义(Sno,Cno)为主码,则用下面语句建立SC表:

CREATE TABLE sc
      (sno 
NUMBER(8),
       cno 
NUMBER(2),
       grade 
NUMBER(2),
       
CONSTRAINT pk_sc PRIMARY KEY(sno,cno));

      
    用PRIMARY KEY语句定义了关系的主码后,每当用户程序对主码列进行更新操作时,系统自动进行完整性检查,凡操作使主码值为空或使主码值在表中不唯一,系统拒绝此操作,从而保证了实体完整性。

    二、Oracle中的参照完整性
   
Oracle的CREATE TABLE语句也可以定义参照完整性规则,即用FOREIGN KEY子句定义哪些列为外码列,用REFERENCES子句指明这些外码相应于哪个表的主码,用ON DELETE CASCADE子句指明在删除被参照关系的元组时,同时删除参照关系中相应的远祖。

    例如,使用如下SQL语句建立EMP表:

CREATE TABLE emp
      (empno 
NUMBER(4),
       ename 
VARCHAR(10),
       job 
VARCHAR2(9),
       mgr 
NUMBER(4),
       sal 
NUMBER(7,2),
       deptno 
NUMBER(2),
       
CONSTRAINT fk_deptno
       
FOREIGN KEY(deptno)
       
REFERENCES dept(deptno));

       
    则表明EMP是参照表,DEPT为其被参照表,EMP表中Deptno为外码,它相应于DEPT表中的主码Deptno。当删除或修改DEPT表中某个元组的主码时要检查EMP中是否有元组的DEPTNO值等于DEPT中要删除的元组的Deptno值,如没有,接受此操作;否则系统拒绝这一更新操作。

    如果用如下SQL语句建立EMP表:

CREATE TABLE emp
      (empno 
NUMBER(4),
       ename 
VARCHAR(10),
       job 
VARCHAR2(9),
       mgr 
NUMBER(4),
       sal 
NUMBER(7,2),
       deptno 
NUMBER(2),
       
CONSTRAINT fk_deptno
       
FOREIGN KEY(deptno)
       
REFERENCES dept(deptno)
       
ON DELETE CASCADE);

       
    当要修改DEPT表中的Deptno值时,先要检查EMP表中有无元组的Deptno值与之对应,若没有,系统接受这个修改操作,否则,系统拒绝此操作。

    当要删除DEPT表中某个元组时,系统也要检查EMP表,若找到相应元组则将它们也随之删除。

    三、Oracle中用户定义的完整性
   
除实体完整性和参照完整性外,应用系统中往往还需要定义与应用有关的完整性限制。例如:要求某一列的值不能取空值,要在表中是唯一的,要在某个取值范围中等。Oracle允许用户在建表时定义下列完整性约束:
      列值非空(NOT NULL短语)
    列值唯一(UNIQUE短语)
    检查列值是否满足一个布尔表达式(CHECK短语)

    例1 建立部门表DEPT,要求部门名称Dname列取值唯一,部门编号Deptno列为主码。

CREATE TABLE dept
      (deptno 
NUMBER,
       dname 
VARCHAR(9CONSTRAINT u1 UNIQUE,
       loc 
VARCHAR(10),
       
CONSTRAINT pk_dept PRIMARY KEY (deptno));

      
其中CONSTRAINT U1 UNIQUE表示约束名为U1,该约束要求Dname列值唯一。

    例2 建立学生登记表Student,要求学号在90000~99999之间,年龄<29,性别只能是“男”或“女”,姓名非空。

CREATE  TABLE student
      (sno 
NUMBER(5CONSTRAINT c1 CHECK (sno BETWEEN 90000 AND 99999),
       sname 
VARCHAR(20CONSTRAINT c2 NOT NULL,
       sage 
NUMBER(3CONSTRAINT c3 CHECK (sage<29),
       ssex 
VARCHAR(2CONSTRAINT c4 CHECK(ssex IN('','')));

      

    例3 建立职工表EMP,要求每个职工的应发工资不得超过3000元。应发工资实际上就是实发工资列Sal与扣除项Deduct之和。

CREATE  TABLE emp
      (eno 
NUMBER(4),
       ename 
VARCHAR(10),
       job 
VARCHAR(8),
       sal 
NUMBER(7,2),
       deduct 
NUMBER(7,2),
       deptno 
NUMBER(2),
       
CONSTRAINT c1 CHECK (sal + deduct <= 3000));

      
    在Oracle中,除列值非空、列值唯一、检查列值是否满足一个布尔表达式外,用户还可以通过触发器(Trigger)来实现其他完整性规则。所谓数据库触发器,就是一类靠事件驱动的特殊过程,一旦由某个用户定义,任何用户对该数据的增、删、改操作均由服务器自动激活相应的触发器,在核心层进行集中的完整性控制。

    定义数据库触发器的语句是CREATE TRIGGER。

    例4 为教师表Teacher定义完整性规则“教授的工资不得低于1000元,如果低于1000元,自动改为1000元”。

CREATE TRIGGER update_sal
   BEFORE 
INSERT OR UPDATE OF sal, pos
   
ON teacher
   
FOR EACH ROW
   
WHEN (:NEW.pos = '教授')                             /**//* 某教员晋升为教授 */
BEGIN
   
IF :NEW.sal < 1000
   
THEN
      :NEW.sal :
= 1000;
   
END IF;
END;

       
    综上所述,Oracle提供了CREATE TABLE语句CREATE TRIGGER语句定义完整性约束条件,其中用CREATE TABLE语句可以定义很复杂的完整性约束条件。完整性约束条件一旦定义好,Oracle会自动执行相应的完整性检查,对于违反完整性约束条件的操作或者拒绝执行或者执行事先定义的操作。
  一、Oracle中的实体完整性
   
Oracle在CREATE TABLE语句中提供了PRIMARY KEY子句,供用户在建表时指定关系的主码列。例如,在学生-选课数据库中,要定义Student表的Sno为主码,可使用如下语句:

CREATE TABLE student
      (sno 
NUMBER(8
),
       sanme 
VARCHAR(20
),
       sage 
NUMBER(20
),
       
CONSTRAINT pk_sno PRIMARY KEY(sno));

       其中,PRIMARY KEY(Sno)表示是Student表的主码。PK_SNO是此主码约束名。

    若要在SC表中定义(Sno,Cno)为主码,则用下面语句建立SC表:

CREATE TABLE sc
      (sno 
NUMBER(8),
       cno 
NUMBER(2),
       grade 
NUMBER(2),
       
CONSTRAINT pk_sc PRIMARY KEY(sno,cno));

      
    用PRIMARY KEY语句定义了关系的主码后,每当用户程序对主码列进行更新操作时,系统自动进行完整性检查,凡操作使主码值为空或使主码值在表中不唯一,系统拒绝此操作,从而保证了实体完整性。

    二、Oracle中的参照完整性
   
Oracle的CREATE TABLE语句也可以定义参照完整性规则,即用FOREIGN KEY子句定义哪些列为外码列,用REFERENCES子句指明这些外码相应于哪个表的主码,用ON DELETE CASCADE子句指明在删除被参照关系的元组时,同时删除参照关系中相应的远祖。

    例如,使用如下SQL语句建立EMP表:

CREATE TABLE emp
      (empno 
NUMBER(4),
       ename 
VARCHAR(10),
       job 
VARCHAR2(9),
       mgr 
NUMBER(4),
       sal 
NUMBER(7,2),
       deptno 
NUMBER(2),
       
CONSTRAINT fk_deptno
       
FOREIGN KEY(deptno)
       
REFERENCES dept(deptno));

       
    则表明EMP是参照表,DEPT为其被参照表,EMP表中Deptno为外码,它相应于DEPT表中的主码Deptno。当删除或修改DEPT表中某个元组的主码时要检查EMP中是否有元组的DEPTNO值等于DEPT中要删除的元组的Deptno值,如没有,接受此操作;否则系统拒绝这一更新操作。

    如果用如下SQL语句建立EMP表:

CREATE TABLE emp
      (empno 
NUMBER(4),
       ename 
VARCHAR(10),
       job 
VARCHAR2(9),
       mgr 
NUMBER(4),
       sal 
NUMBER(7,2),
       deptno 
NUMBER(2),
       
CONSTRAINT fk_deptno
       
FOREIGN KEY(deptno)
       
REFERENCES dept(deptno)
       
ON DELETE CASCADE);

       
    当要修改DEPT表中的Deptno值时,先要检查EMP表中有无元组的Deptno值与之对应,若没有,系统接受这个修改操作,否则,系统拒绝此操作。

    当要删除DEPT表中某个元组时,系统也要检查EMP表,若找到相应元组则将它们也随之删除。

    三、Oracle中用户定义的完整性
   
除实体完整性和参照完整性外,应用系统中往往还需要定义与应用有关的完整性限制。例如:要求某一列的值不能取空值,要在表中是唯一的,要在某个取值范围中等。Oracle允许用户在建表时定义下列完整性约束:
      列值非空(NOT NULL短语)
    列值唯一(UNIQUE短语)
    检查列值是否满足一个布尔表达式(CHECK短语)

    例1 建立部门表DEPT,要求部门名称Dname列取值唯一,部门编号Deptno列为主码。

CREATE TABLE dept
      (deptno 
NUMBER,
       dname 
VARCHAR(9CONSTRAINT u1 UNIQUE,
       loc 
VARCHAR(10),
       
CONSTRAINT pk_dept PRIMARY KEY (deptno));

      
其中CONSTRAINT U1 UNIQUE表示约束名为U1,该约束要求Dname列值唯一。

    例2 建立学生登记表Student,要求学号在90000~99999之间,年龄<29,性别只能是“男”或“女”,姓名非空。

CREATE  TABLE student
      (sno 
NUMBER(5CONSTRAINT c1 CHECK (sno BETWEEN 90000 AND 99999),
       sname 
VARCHAR(20CONSTRAINT c2 NOT NULL,
       sage 
NUMBER(3CONSTRAINT c3 CHECK (sage<29),
       ssex 
VARCHAR(2CONSTRAINT c4 CHECK(ssex IN('','')));

      

    例3 建立职工表EMP,要求每个职工的应发工资不得超过3000元。应发工资实际上就是实发工资列Sal与扣除项Deduct之和。

CREATE  TABLE emp
      (eno 
NUMBER(4),
       ename 
VARCHAR(10),
       job 
VARCHAR(8),
       sal 
NUMBER(7,2),
       deduct 
NUMBER(7,2),
       deptno 
NUMBER(2),
       
CONSTRAINT c1 CHECK (sal + deduct <= 3000));

      
    在Oracle中,除列值非空、列值唯一、检查列值是否满足一个布尔表达式外,用户还可以通过触发器(Trigger)来实现其他完整性规则。所谓数据库触发器,就是一类靠事件驱动的特殊过程,一旦由某个用户定义,任何用户对该数据的增、删、改操作均由服务器自动激活相应的触发器,在核心层进行集中的完整性控制。

    定义数据库触发器的语句是CREATE TRIGGER。

    例4 为教师表Teacher定义完整性规则“教授的工资不得低于1000元,如果低于1000元,自动改为1000元”。

CREATE TRIGGER update_sal
   BEFORE 
INSERT OR UPDATE OF sal, pos
   
ON teacher
   
FOR EACH ROW
   
WHEN (:NEW.pos = '教授')                             /**//* 某教员晋升为教授 */
BEGIN
   
IF :NEW.sal < 1000
   
THEN
      :NEW.sal :
= 1000;
   
END IF;
END;

       
    综上所述,Oracle提供了CREATE TABLE语句CREATE TRIGGER语句定义完整性约束条件,其中用CREATE TABLE语句可以定义很复杂的完整性约束条件。完整性约束条件一旦定义好,Oracle会自动执行相应的完整性检查,对于违反完整性约束条件的操作或者拒绝执行或者执行事先定义的操作。

在 Oracle 和 PHP 中使用 LOB

作者:Harry Fuecks

是否达到 4,000 字节的极限? 我们先来了解一下 LOB......

本文相关下载:
Oracle 数据库 10g
Zend Core for Oracle
Apache HTTP Server 1.3 和更高版本

使用 VARCHAR2 这样的 Oracle 类型是完全可以的,但如果您要一次性存储的数据量超过它的 4,000 字节的极限,情况将会如何? 要完成此任务,您需要 Oracle 的某个 Long 对象 (LOB) 类型,为此您应了解如何使用 PHP API 来处理 LOB。 这对于不熟悉它的人来说是很困难的。

 

在这篇“Oracle+PHP 指南”操作文档中,您将了解可用的 LOB 类型以及与它们相关的问题,然后将探讨 PHP 中常见 LOB 操作示例。

 

Oracle 中的 Long 对象

 

Oracle 提供了以下 LOB 类型:

  • BLOB,用于存储二进制数据
  • CLOB,用于使用数据库字符集编码存储字符数据
  • NCLOB,用于使用国家字符集存储 Unicode 字符数据。 注意,您将在本文中使用的 PHP OCI8 扩展当前不支持 NCLOB。
  • BFILE,用于引用存在于操作系统的文件系统中的外部文件

LOB 的更深一层的子类别是临时 LOB(可以为 BLOB、CLOB 或 NCLOB),它在被释放之前一直存储在临时表空间中。

 

注意,较旧版本的 Oracle 分别为字符和二进制数据提供了 LONG 和 LONG RAW 类型。 在 Oracle9i 中,LOB 取代了这两个类型。

 

LOB 存储。 对于 BLOB、CLOB 和 NCLOB 类型,Oracle 数据库 10g 能够在单个值中最多存储 128TB 数据,具体情况取决于为 LOB 定义的数据库块大小和“块”设置。

 

LOB 本身由两个元素构成: LOB 内容和 LOB 定位器(它是指向 LOB 内容的“指针”)。 这种划分对于 Oracle 高效地存储和管理 LOB 是必需的,它反映在用于对 LOB 执行 INSERTUPDATESELECT 操作的 PHP API 中(如下所示)。

 

对于内部 LOB 类型(即 BFILE 以外的类型),如果 LOB 小于 4KB,则 Oracle 将 LOB 的内容“整齐”地存储在表中(与行的剩余部分存储在一起)。 默认情况下,大于 4KB 的 LOB“不规则”地存储在表的表空间中。 该方法可以快速检索到小型 LOB,而对于大型 LOB,访问时间将比较长,但扫描表时的总体性能保持不变。

 

适用于 LOB 存储和访问并可以提高性能的可选方法还有很多(如内存缓存和缓冲),具体使用哪一个应根据应用程序的具体情况而定。 有关进一步的信息,请参阅 Oracle 文档中的 LOB 性能指南Oracle 数据库应用程序开发人员指南 - 大型对象

 

有关 LOB 使用方面的限制。 LOB 类型在使用方面存在一些限制,最重要的限制体现在它们在 SQL 语句中的使用。 您不能在以下任意查询中使用 LOB 类型。

SELECT DISTINCT <lob_type>
ORDER BY <lob_type>
GROUP BY <lob_col>

将 LOB 类型列用于表连接、UNIONINTERSECTIONMINUS 语句也是非法的。

 

在 LOB 使用的其他方面存在更多限制,例如,您不能将 LOB 用作主键列。 有关详细信息,请再次参阅 Oracle 数据库应用程序开发人员指南 - 大型对象

 

CLOB 和字符集

 

数据库的默认字符集由参数 NLS_CHARACTERSET 定义,应使用该字符集对位于 CLOB 中的文本进行编码。 使用以下 SQL 确定数据库字符集编码:

SELECT value FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET'

鉴于 PHP 中缺乏对 NCLOB 的支持,因此您可能需要考虑将 Unicode 编码(如 UTF-8)用作数据库字符,这可以通过以下语句实现(假设您有足够的权限):

ALTER DATABASE CHARACTER SET UTF8

注意: 在您不了解影响的情况下不要尝试该操作,尤其是如果现有数据或应用程序代码使用其他字符集。 有关更多信息,请参阅 Oracle 全球化支持指南全球化 Oracle PHP 应用程序概述

 

使用 LOB

 

此处将主要介绍 PHP 的 OCI8 扩展。 还有一点值得注意的是,Oracle 提供了 DBMS_LOB 程序包,其中包含用于在 PL/SQL 中使用 LOB 的并行过程和函数。

 

PHP OCI8 扩展在全局 PHP 命名空间中注册一个称作“OCI-Lob”的 PHP 类。 当您执行 SELECT 语句时(例如,其中的某一列的类型为 LOB),PHP 将把它自动绑定到 OCI-Lob 对象实例。 引用 OCI-Lob 对象后,可以调用类似 load()save() 这样的方法来访问或修改 LOB 的内容。

 

可用的 OCI-Lob 方法将取决于 PHP 的版本,PHP5 特别提供了 read()seek()append() 等方法。 PHP 手册对提供可用 OCI-Lob 方法的 PHP 版本号介绍得不够明确,因此,如果您存在疑问,可以使用以下脚本进行确认。

<?php 
foreach (get_class_methods('OCI-Lob') as $method ) {
    print "OCI-Lob::$method()\n";
}
?>

在我的系统上运行 PHP 5.0.5 时,我获得了以下方法列表:

OCI-Lob::load()
OCI-Lob::tell()
OCI-Lob::truncate()
OCI-Lob::erase()
OCI-Lob::flush()
OCI-Lob::setbuffering()
OCI-Lob::getbuffering()
OCI-Lob::rewind()
OCI-Lob::read()
OCI-Lob::eof()
OCI-Lob::seek()
OCI-Lob::write()
OCI-Lob::append()
OCI-Lob::size()
OCI-Lob::writetofile()
OCI-Lob::writetemporary()
OCI-Lob::close()
OCI-Lob::save()
OCI-Lob::savefile()
OCI-Lob::free()

实际上,PHP 4.x OCI8 扩展只支持读取或写入完整的 LOB,这是 Web 应用程序中最常见的用法。 PHP5 对此进行了扩展,从而可以读取和写入 LOB 的“块”,同时还使用 setBuffering()getBuffering() 方法支持 LOB 缓冲。 PHP5 还提供了独立函数 oci_lob_is_equal()oci_lob_copy()

 

此处的示例将使用新的 PHP5 OCI 函数名(例如,用 oci_parse 代替 OCIParse)。 这些示例使用以下序列和表:

CREATE SEQUENCE mylobs_id_seq
    NOMINVALUE
    NOMAXVALUE
    NOCYCLE
    CACHE 20
    NOORDER
INCREMENT BY 1;

CREATE TABLE mylobs (
    id NUMBER PRIMARY KEY,
    mylob CLOB
)

注意,此处的大多数示例都使用 CLOB,但同一逻辑几乎完全可以应用于 BLOB。

 

插入 LOB

 

要使用 INSERT 插入一个内部 LOB,首先需要使用相应的 Oracle EMPTY_BLOBEMPTY_CLOB 函数来初始化 LOB,您无法更新一个包含 NULL 值的 LOB。

 

初始化后,请将该列绑定到 PHP OCI-Lob 对象,然后通过该对象的 save() 方法更新 LOB 内容。

 

以下脚本提供了一个示例,用于从 INSERT 查询中返回 LOB 类型:

<?php 
// connect to DB etc...

$sql = "INSERT INTO
        mylobs
          (
id,
            mylob
          )
       VALUES
          (
            mylobs_id_seq.NEXTVAL,
            --Initialize as an empty CLOB
            EMPTY_CLOB()
          )
       RETURNING
          --Return the LOB locator
          mylob INTO :mylob_loc";

$stmt = oci_parse($conn, $sql);

// Creates an "empty" OCI-Lob object to bind to the locator
$myLOB = oci_new_descriptor($conn, OCI_D_LOB);

// Bind the returned Oracle LOB locator to the PHP LOB object
oci_bind_by_name($stmt, ":mylob_loc", $myLOB, -1, OCI_B_CLOB);

// Execute the statement using , OCI_DEFAULT - as a transaction
oci_execute($stmt, OCI_DEFAULT)
    or die ("Unable to execute query\n");

// Now save a value to the LOB
if ( !$myLOB->save('INSERT: '.date('H:i:s',time())) ) {

    // On error, rollback the transaction
    oci_rollback($conn);

} else {

    // On success, commit the transaction
    oci_commit($conn);

}

// Free resources
oci_free_statement($stmt);
$myLOB->free();

// disconnect from DB etc.
?>

注意该示例如何使用事务,它使用 OCI_DEFAULT 常量指示 oci_execute 等待 oci_commitoci_rollback。 这一点很重要,因为我在 INSERT 中分两个阶段执行操作 - 首先创建行,然后更新 LOB。

 

注意,如果我使用了 BLOB 类型,则唯一需要更改(假设有一个 BLOB 列)的就是 oci_bind_by_name 调用:

oci_bind_by_name($stmt, ":mylob_loc", $myLOB, -1, OCI_B_BLOB);

或者,您也可以直接绑定字符串而不必指定 LOB 类型;

<?php 
// etc.

$sql = "INSERT INTO
          mylobs
          (
id,
            mylob
          )
        VALUES
          (
            mylobs_id_seq.NEXTVAL,
:string
          )
";

$stmt = oci_parse($conn, $sql);

$string = 'INSERT: '.date('H:i:s',time());

oci_bind_by_name($stmt, ':string', $string);

oci_execute($stmt)
    or die ("Unable to execute query\n");

// etc.
?>

该方法极大地简化了代码,并且在要写入 LOB 的数据相对较小的情况下比较合适。 相反,如果要将大型文件的内容传送到 LOB 中,则可以遍历该文件的内容,方法是对 PHP LOB 对象调用 write()flush() 以写入较小的块,而不是在单个实例中将整个文件保存在内存中。

 

选择一个 LOB

 

如果 SELECT 查询包含一个 LOB 列,PHP 将自动把该列绑定到 OCI-Lob 对象。例如:

<?php 
// etc.

$sql = "SELECT
          *
FROM
          mylobs
        ORDER BY
Id
";

$stmt = oci_parse($conn, $sql);

oci_execute($stmt)
    or die ("Unable to execute query\n");

while ( $row = oci_fetch_assoc($stmt) ) {
    print "ID: {$row['ID']}, ";

    // Call the load() method to get the contents of the LOB
    print $row['MYLOB']->load()."\n";
}

// etc.
?>

可以进一步简化以上代码,方法是使用 OCI_RETURN_LOBS 常量(与 oci_fetch_array() 结合使用)并指示它用 LOB 对象的值替换这些对象:

while ( $row = oci_fetch_array($stmt, OCI_ASSOC+OCI_RETURN_LOBS) ) {
    print "ID: {$row['ID']}, {$row['MYLOB']}\n";
}

更新 LOB

 

要使用 UPDATE 更新 LOB,也可以在 SQL 中使用“RETURNING”命令(与上面的 INSERT 示例一样),但一个更简单的方法是使用 SELECT ... FOR UPDATE

<?php 
// etc.

$sql = "SELECT
           mylob
FROM
           mylobs
WHERE
           id = 3
        FOR UPDATE /* locks the row */
";

$stmt = oci_parse($conn, $sql);

// Execute the statement using OCI_DEFAULT (begin a transaction)
oci_execute($stmt, OCI_DEFAULT) 
    or die ("Unable to execute query\n");

// Fetch the SELECTed row
if ( FALSE === ($row = oci_fetch_assoc($stmt) ) ) {
    oci_rollback($conn);
    die ("Unable to fetch row\n");
}

// Discard the existing LOB contents
if ( !$row['MYLOB']->truncate() ) {
    oci_rollback($conn);
    die ("Failed to truncate LOB\n");
}

// Now save a value to the LOB
if ( !$row['MYLOB']->save('UPDATE: '.date('H:i:s',time()) ) ) {

    // On error, rollback the transaction
    oci_rollback($conn);

} else {

    // On success, commit the transaction
    oci_commit($conn);

}

// Free resources
oci_free_statement($stmt);
$row['MYLOB']->free();

// etc.
?>

INSERT 一样,我需要使用事务执行 UPDATE。 一个重要的额外步骤是调用 truncate()。 使用 save() 更新 LOB 时,它将替换 LOB 的内容,范围从开头一直到新数据的长度。 这意味着较旧的内容(如果它比新内容长)可能仍保留在 LOB 中。

 

对于 PHP 4.x(其中未提供 truncate()),以下替换解决方案使用 Oracle 的 EMPTY_CLOB() 函数删除 LOB 中的任何现有内容,然后将新数据保存到其中。

$sql = "UPDATE
           mylobs
SET
            mylob = EMPTY_CLOB()
WHERE
           id = 2403
        RETURNING
            mylob INTO :mylob
";

$stmt = OCIParse($conn, $sql);

$mylob = OCINewDescriptor($conn,OCI_D_LOB);

OCIBindByName($stmt,':mylob',$mylob, -1, OCI_B_CLOB);

// Execute the statement using OCI_DEFAULT (begin a transaction)
OCIExecute($stmt, OCI_DEFAULT) 
    or die ("Unable to execute query\n");

if ( !$mylob->save( 'UPDATE: '.date('H:i:s',time()) ) ) {

    OCIRollback($conn);
    die("Unable to update lob\n");

}

OCICommit($conn);
$mylob->free();
OCIFreeStatement($stmt);

使用 BFILES

 

使用 BFILE 类型时,INSERTUPDATE 将该文件在数据库服务器(可能与 Web 服务器不在同一计算机上)的文件系统中的位置告知 Oracle,而不是传递文件内容。 使用 SELECT 语句,您可以通过 Oracle 读取 BFILE 的内容(如果您愿意),也可以调用 DBMS_LOB 程序包中的函数和过程来获取有关文件的信息。

 

BFILE 的主要优点是能够直接从文件系统中访问原始文件,同时仍然可以使用 SQL 定位文件。 例如,这意味着 Web 服务器可以直接提供映射,而我可以跟踪包含 BFILES 的表与“users”表(指示哪些用户上载了文件)之间的关系。

 

在示例中,我首先需要更新上面使用的表模式;

ALTER TABLE mylobs ADD( mybfile BFILE )

然后,我需要使用 Oracle 注册一个目录别名(这需要管理权限)并授予对该目录的读取权限:

CREATE DIRECTORY IMAGES_DIR AS '/home/harryf/public_html/images'
GRANT READ ON DIRECTORY IMAGES_DIR TO scott

我现在可以使用 INSERT 插入一些如下所示的 BFILE 名称:

<?php 
// etc.

// Build an INSERT for the BFILE names
$sql = "INSERT INTO
        mylobs
          (
id,
            mybfile
          )
       VALUES
          (
            mylobs_id_seq.NEXTVAL,
            /*
            Pass the file name using the Oracle directory reference
            I created called IMAGES_DIR
            */
            BFILENAME('IMAGES_DIR',:filename)
          )";

$stmt = oci_parse($conn, $sql);

// Open the directory
$dir = '/home/harryf/public_html/images';
$dh = opendir($dir)
    or die("Unable to open $dir");

// Loop through the contents of the directory
while (false !== ( $entry = readdir($dh) ) ) {

    // Match only files with the extension .jpg, .gif or .png
    if ( is_file($dir.'/'.$entry) && preg_match('/\.(jpg|gif|png)$/',$entry) ) {

        // Bind the filename of the statement
        oci_bind_by_name($stmt, ":filename", $entry);

        // Execute the statement
        if ( oci_execute($stmt) ) {
            print "$entry added\n";
        }        
    }

}

如果需要,我可以通过 Oracle 读取 BFILE 的内容,方法与我在上面选择 CLOB 时所采用的方法相同。 或者,如果我需要获得文件名,则可以直接从文件系统中访问它们,我可以调用如下所示的 DBMS_LOB.FILEGETNAME 过程:

<?php 
// etc.

$sql = "SELECT
id
FROM
          mylobs
WHERE
          -- Select only BFILES which are not null
          mybfile IS NOT NULL;

$stmt1 = oci_parse($conn, $sql);

oci_execute($stmt1)
    or die ("Unable to execute query\n");

$sql = "DECLARE
          locator BFILE;
          diralias VARCHAR2(30);
          filename VARCHAR2(30);

BEGIN

SELECT
            mybfile INTO locator
FROM
            mylobs
WHERE
            id = :id;

          -- Get the filename from the BFILE
          DBMS_LOB.FILEGETNAME(locator, diralias, filename);

          -- Assign OUT params to bind parameters
          :diralias:=diralias;
          :filename:=filename;

END;";

$stmt2 = oci_parse($conn, $sql);

while ( $row = oci_fetch_assoc ($stmt1) ) {

    oci_bind_by_name($stmt2, ":id", $row['ID']);
    oci_bind_by_name ($stmt2, ":diralias", $diralias,30);
    oci_bind_by_name ($stmt2, ":filename", $filename,30);

    oci_execute($stmt2);
    print "{$row['ID']}: $diralias/$filename\n";

}
// etc.
?>

此外,您可以使用 DBMS_LOB.FILEEXISTS 函数找出操作系统已经删除但在数据库中仍然引用的文件。

 

结论

 

本方法文档向您介绍了 Oracle 数据库 10g 提供的不同类型的 LOB,希望您现在已经了解了它们在将大型数据实体高效存储在数据库中这一方面所起到的作用。 您还学习了如何使用 PHP 的 OCI8 API 处理 LOB,其中涵盖了在使用 Oracle 和 PHP 进行开发时将遇到的常见使用情形。

 


Harry Fuecks [http://www.phppatterns.com] 于 1999 年接触 PHP,此后作为 PHP 开发人员和撰稿人而名声鹊起。他通过 Sitepoint 开发人员网络发布了大量初级和中级 PHP 文章,著有 The PHP Anthology一书。

在 Oracle 和 PHP 中绑定变量

作者:Larry Ullman

通过绑定变量提高 Oracle 驱动的 PHP 应用程序的速度和安全性。

本文相关下载:
Oracle 数据库 10g
Oracle Instant Client
Oracle JDeveloper PHP Extension
Zend Core for Oracle

想必您一定知道,当前的大多数网站都依赖数据库,只是方式各有不同。 无论您正在构建的站点需要论坛、电子商务组件、包含大量文章和信息还是仅仅从访问者那里获得反馈,您都很可能会通过某种方式并入数据库。 尽管数据库很重要并通常是不可或缺的,但使用它们会影响(通常是不利影响) Web 应用程序的两个方面: 性能和安全性。 了解何时以及如何在 PHP 中绑定变量将对改善这两个问题方面大有帮助。

 

如果您曾经对 Web 项目进行过测试,想必您一定会知道数据库交互通常是要求最高的过程。 在数据库中运行查询时,Oracle 必须先对查询进行分析,以确保它的语法正确,然后才执行实际的查询。 即使您运行多个相似查询也必须先进行分析:

SELECT * FROM movies WHERE movie_id=1
SELECT * FROM movies WHERE movie_id=26
SELECT * FROM movies WHERE movie_id=5689

尽管这三个查询之间的唯一差别体现在所获取的精确记录上,但 Oracle 仍将单独处理它们,并在执行之前分别对它们进行分析。 绑定变量的第一个好处是,Oracle 只需分析查询一次,而不管它究竟使用不同的值运行了多少次。 这种在脚本方法方面的改变可以极大地提高性能。

 

作为 Web 开发人员,您通常遇到的第二个问题是站点的安全性。 由于该问题体现在很多方面,因此找到解决它的方法无异于一场永无休止但却至观重要的战役。 在数据库驱动的站点中,许多查询都依赖于外部值,如用户从表单中提交的值、在 URL 中传递给页面的值等等。 此类查询很容易受到 SQL 注入攻击的破坏。 (“SQL 注入攻击”是指恶意用户在尝试破坏查询的过程中向 PHP 脚本提供无效数据。) 如果对查询的处理方法不当,恶意用户便有可能从生成的错误消息中了解一些有关脚本、数据库或服务器的信息。 以如下所示的查询为例:

SELECT * FROM movies WHERE movie_id=$_GET['id']

想必您在看到该查询时一定会吃惊不已,因为它的安全性实在是太差了。 用户只需更改 URL(例如,将 http://www.example.com/movie.php?id=23 更改为 http://www.example.com/movie.php?id=HaHa!)便可以破坏此查询。 当然,查询中使用的所有数据都应进行验证,但每当在查询中使用变量时,如果变量的值不同于预期的值,便有可能导致出现错误。 由于绑定变量与实际的查询分离,因此可以大大降低 SQL 注入攻击的可能性。

 

在这篇“Oracle+PHP 指南”操作文档中,您将了解如何在 PHP 脚本中执行 Oracle 查询时绑定变量。 通过将以下技巧和代码示例应用于您自己的 Web 应用程序,您可以轻松地提高它们的性能和安全性。

 

背景知识/概述

 

实际示例对于演示绑定变量的用法再合适不过了。 我所要介绍的是我几年前编写的一个应用程序,通过它,高尔夫专业服务公司可以规定他们的球场提供的开球时段和收费标准。 例如,他们可能规定在某个特定的周六,可利用的开球时段为上午 7 点到下午 4 点,间隔为 10 分钟,下午 2 点之前的收费标准为 50 美元,2 点之后为 40 美元。 这些值源于 HTML 表单;负责处理的 PHP 脚本随后在数据库表中为每个开球时段创建一个记录(这样,高尔夫球手便可以从记录列表中在线选择一个时间)。 仅仅为了表示一天,该进程就可能需要 50 个或更多个极其相似的 INSERT 查询,这种情况下使用绑定变量将比较合适。

 

可以使用以下 SQL 语句创建该示例的简化表结构(没有其他表,我已经删除了标识键等内容):

CREATE TABLE teetimes (
	teetime DATE,
	rate NUMBER(5,2)
)

显而易见,可以通过多种方法对该示例进行扩展。 但现在最重要的是,本操作文档中的代码假设您已经建立了这样一个表并可以从 PHP 脚本连接并填充它。

 

以下步骤将演示实现绑定变量需要执行的确切操作。 最终代码将通过一系列步骤构建,并对每个进程进行分析,以便您了解它的用途。 在 PHP 脚本中并入绑定变量分为如下所示的基本步骤:

  1. 建立所使用的查询。
  2. 针对绑定变量重写查询。
  3. 在 Oracle 中分析基本查询。
  4. 在 PHP 中向变量分配值。
  5. 执行查询。

第 1 步: 定义查询

 

清单 1 是将一些记录插入到 teetimes 表中的通用 PHP 脚本概要。 与 Oracle 相关的代码假设您使用 PHP 5,其中的 OCI 函数所采用的命名模式和语法与 PHP 4 中的 OCI 函数略有不同,但一致性更高。如果您使用较旧版本的 PHP,请查看 PHP 手册以了解正确的函数和语法(如果需要的话)。 此外,由于 PHP 与 Oracle 之间的通信可能存在很多疑难问题,因此您需要阅读此疑难解答指南以了解其他用于处理环境变量的方法。

 

清单 1


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
        "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
	<meta http-equiv="content-type" content="text/html; charset=iso-8859-15" />
	<title>Binding Variables with PHP</title>

</head>
<body>
<h3>Entering Tee Times</h3>
<?php // bind1.php - listing 1

// Establish the environmental variables.
$sid = 'test10g';
$home = '/Users/oracle/10gEAR2/orahome';

putenv("ORACLE_HOME=$home");
putenv("ORACLE_SID=$sid");
putenv("TNS_ADMIN=$home/network/admin");

// Create the array of data to be inserted.
// This data represents what __should__ come from an HTML form.
$teetimes = array();
$date = '2005-08-20';

// Loop through each available hour in the day.
for ($hour = 7; $hour <! 16; $hour++) {

    // Loop through each hour in 10 minute increments.
    for ($minute = 0; $minute <! 60; $minute += 10) {

        // Create the date and time value.
        $this_time = "$date $hour:$minute";

        // Add a 0 if necessary.
        if ($minute <! 10) $this_time .= '0';

        // Determine the rate to use.
        $rate = ($hour <! 14) ? 50.00 : 40.00;

        // Add this teetime and rate to the array.
        $teetimes[$this_time] = $rate;

    }

}

//echo '<!pre>' . print_r ($teetimes, 1) . '<!/pre>'; // For debugging

// Connect to Oracle.

$c = oci_pconnect ('scott', 'tiger', $sid) OR die 
  ('Unable to connect to the database.ERROR: <!pre>' . print_r(oci_error(),1) . '<!/pre><!/body><!/html>');

// Insert each record into the table.
foreach ($teetimes as $time => $rate) {

    // Make the query, for example:
    /* INSERT INTO teetimes (teetime, rate) VALUES (TO_DATE('2005-08-21 15:00', 'yyyy-mm-dd hh24:mi'), 40.00); */
    $q = "INSERT INTO teetimes (teetime, rate) VALUES (TO_DATE('$time', 'yyyy-mm-dd hh24:mi'), $rate)";

    // Run the query.
	$s = oci_parse($c, $q);
     oci_execute ($s);

}

// 关闭连接。

oci_close($c);

// Query to confirm the results:
/* SELECT TO_CHAR(teetime, 'MONTH DD, YYYY HH:MI AM') AS "Tee Time", rate FROM teetimes ORDER BY teetime ASC */
?>
</body>
</html>

该脚本的用途很简单: 假设它将从表单中收到一组日期、时间、增量和费用。 应采用相应的语法组装该数据,然后将其插入到数据库中。 由于尚未创建 HTML 表单,因此该脚本将自动生成一组代表性数据。 然后,将每个单独的开球时间插入到 Oracle 的循环中。 尽管该脚本可以高效、正常地运行,但采用绑定变量可以显著提高它的性能。

 

第 2 步: 使用标识符重新定义查询

 

在清单 1 中可以看到,该查询最初定义为:

INSERT INTO teetimes (teetime, rate) VALUES (TO_DATE('$time', 'yyyy-mm-dd hh24:mi'), $rate)

其中,$time$rate 是从生成的数据数组中提取的。 现在必须转换此查询,以便用占位符代替变量来表示不断变化的数据。 要使用的语法为 :marker,其中的 marker 可以是任何标识符。 在这个特殊实例中,应将该查询转换为

INSERT INTO teetimes (teetime, rate) VALUES (TO_DATE(:t, 'yyyy-mm-dd hh24:mi'), :r)

在下面的清单 2(即最终的结果)中,该查询已经从它在清单 1 中的位置移走。现在,在循环的外部定义它,这是因为只需定义它一次(与为插入的每个记录定义一次相反)。 还要注意的是,在绑定变量时,您甚至可以删除通常情况下所需的引号(例如,TO_DATE() 函数中的第一个参数只是 :t,而非 '$time'。) 这是因为变量实际上与查询语法分离。

 

清单 2


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
        "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
	<meta http-equiv="content-type" content="text/html; charset=iso-8859-15" />
	<title>Binding Variables with PHP</title>
</head>
<body>
<h3>Entering Tee Times</h3>
<?php 

// Establish the environmental variables.
$sid = 'test10g';
$home = '/Users/oracle/10gEAR2/orahome';

putenv("ORACLE_HOME=$home");
putenv("ORACLE_SID=$sid");
putenv("TNS_ADMIN=$home/network/admin");

// Create the array of data to be inserted.
// This data represents what __should__ come from an HTML form.
$teetimes = array();
$date = '2005-08-21';

// Loop through each available hour in the day.
for ($hour = 7; $hour < 16; $hour++) {

    // Loop through each hour in 10 minute increments.
    for ($minute = 0; $minute < 60; $minute += 10) {

        // Create the date and time value.
        $this_time = "$date $hour:$minute";

        // Add a 0 if necessary.
        if ($minute < 10) $this_time .= '0';

        // Determine the rate to use.
        $rate = ($hour < 14) ? 50.00 : 40.00;

        // Add this teetime and rate to the array.
        $teetimes[$this_time] = $rate;

    }

}

//echo '<pre>' . print_r ($teetimes, 1) . '</pre>'; // For debugging

// Connect to Oracle.

$c = oci_pconnect ('scott', 'tiger', $sid) OR die 
  ('Unable to connect to the database.ERROR: <pre>' . print_r(oci_error(),1) . '</pre></body></html>');

// Define the query.
$q = "INSERT INTO teetimes (teetime, rate) VALUES (TO_DATE(:t, 'yyyy-mm-dd hh24:mi'), :r)";

// Parse the query.

$s = oci_parse($c, $q);

// Bind the values.

oci_bind_by_name($s, ':t', $time, 16);
oci_bind_by_name($s, ':r', $rate, 5);

// Insert each record into the table.
foreach ($teetimes as $time => $rate) {

    // Execute the query.
    oci_execute ($s);

}

// 关闭连接。

oci_close($c);

// Query to confirm  the results:
/* SELECT TO_CHAR(teetime, 'MONTH DD, YYYY HH:MI AM') AS "Tee Time", rate FROM teetimes ORDER BY teetime ASC */

?>
</body>
</html>

第 3 步: 在 Oracle 中分析查询

 

使用 Oracle 的标识符定义查询后,应通过 Oracle 对它进行分析。 Oracle 对每个查询均进行分析,以确保语法正确。 在 PHP 脚本中,请使用 oci_parse() 函数(在 PHP 4 中,请使用 OCIParse()),并为该函数提供数据库连接和查询作为它的参数:

$s = oci_parse ($c, $q);

分析结果仍分配给表示该语句的变量 ($s),这与您使用非绑定变量处理查询的方法完全相同。

 

第 4 步: 将 PHP 变量与标识符关联

 

如果您留意的话,并且如果您属于善于提问的一类人,那么您现在很想知道 PHP 中的值如何作为查询的一部分运行。 您可以使用 oci_bind_by_name() 函数(在 PHP 4 中为 OCIBindByName())执行此操作。 该函数将语句资源作为它的第一个参数,将标识符的名称作为它的第二个参数,并将 PHP 变量的名称(也可以是文字值)作为它的第三个参数。例如:

oci_bind_by_name($s, ':t, $time);
oci_bind_by_name($s, ':r, $rate);

为了确保安全并最大限度地降低出现 Oracle 错误的可能性,最好使用第四个可选参数: 要插入的数据的最大长度。 最终的绑定行为:

oci_bind_by_name($s, ':t, $time, 16);
oci_bind_by_name($s, ':r, $rate, 5);

以上使用的数字分别对应于 $time$rate 的最大合理长度。 (或者,如果将 -1 用于第四个参数,PHP 将把变量的当前长度用作最大长度。) 您会再次发现,在该示例脚本(参阅清单 2)中,这两行位于 foreach 循环之前。 这似乎有点另人不解,因为 $time$rate 此时并没有值。 下面解释了这样做的原因: 在实际运行查询时,这些行指示 Oracle 将 $time 中存储的值用于 :t,并将 $rate 中存储的值用于 :r。 只要在执行查询时这两个变量包含值,一切便会正常进行。

 

第 5 步: 执行绑定查询

 

最后一步是针对要插入的每一组值执行查询。 在将要访问每个数组元素的循环中,您只需分别将相应的值分配给 $time$rate,然后即可执行查询。 同一 oci_execute() 函数(或 PHP 4 中的 OCIExecute())使用与未绑定版本相同的语法执行以下代码:

oci_execute ($s);

该操作再次在 foreach 循环中执行,其中的 $time$rate 包含它们的相应值。 有关最终的代码,请参见完整的清单 2。

 

我应指出使用该方法的其他几个好处。 为了方便起见,任何尾随的空白符将从插入的值中删除。 更重要的是,您不必使用 addslashes() 或 Magic Quotes 对有问题的字符进行转义(实际上,您根本不应使用),这是因为变量值实际上并不是查询的一部分。

 

结论

 

在本操作文档中,您了解了如何使用绑定变量轻松地提高数据库驱动的 Web 应用程序的安全性和性能。 实际并入该技术只涉及几个额外的代码行,而不需要任何特殊的 PHP 扩展或库。

 

但对于绑定变量,必须注意两件事:

  1. 只有那些定期运行、在语法上相同但具有不同值(例如本特殊示例)的查询才能获得快速的性能。 对于复杂难懂的一次性查询,性能方面的好处非常小甚至没有。 为了最终决定是否应在特殊的 Web 应用程序中使用绑定变量,请执行一些基准测试来测试总体效果。
  2. 安全性的增强并不意味着取代您自己的标准安全措施。 应始终对查询中使用的任何数据进行验证 - 尤其是来自 $_POST$_GET$_COOKIE 中的数据。

您还应知道的是,实际上您可以采用两种方法在 PHP 和 Oracle 之间使用绑定变量。 本文介绍的方法称作绑定参数,这意味着查询参数绑定到变量(有人还将它称作准备语句)。 另一个方法是绑定结果,它是另一种从数据库中检索值(在运行 SELECT 查询之后执行)的方法。

 

有关如何提高 Oracle 和 PHP Web 应用程序的性能的详细信息,请查找有关存储过程(另一种自动化进程的方法)。 谈到性能,您还应了解索引并对表使用索引。 最后,出于整齐目的,应考虑调用 oci_free_statement() 函数(在 PHP 4 中为 OCIFreeStatement())以释放与 PHP 中的脚本关联的资源。

 


Larry UllmanDMC Insights Inc.(一家专门研究信息技术的公司)数字媒体技术部门的总监以及 Web 开发人员主管。 Larry 居住在华盛顿特区的郊外,并曾经编写过多部有关 PHP、SQL、Web 开发和其他计算机技术方面的书籍。

利用 Oracle 和 PHP 轻松处理树

作者:Nick Bollweg

利用一流的查询和函数,轻松处理层次数据。

本文相关下载:
示例代码和清单
Oracle 数据库 10g Express 版
Oracle 即时客户端
为 PHP 提供的 Oracle JDeveloper 扩展

2005 年 12 月发表

几乎每一种数据驱动的应用程序都依赖于某种形式的、不同复杂程度的层次数据:产品类别中的产品、文件夹中的消息、部门中的员工。当然,某些时候您将需要显示这些数据来创建一个目录、收件箱或组织架构的图表。利用 Oracle 提供的特定供应商的 SQL 扩展和 PHP 在数组处理方面的出色能力,您可以检索并显示一个树,并且以简洁和易于维护的方式对树进行内在的高度优化。

因为本文讨论的查询和函数都包含较少的过程,而注重提供更条理清晰,易于理解的代码,因此这篇方法文档在实施的时候以及重构现有代码的时候非常有用。如果您的数据拥有树状的数据形式(目前已经显示或者要取其值),那么本方法文档将会很有价值。使用最新推出的优秀 RDBMS 的用户非常幸运,因为新的特性使得一些棘手的处理层次数据的任务变得更为容易 — 虽然自 Oracle8i 起的版本都拥有基本的底层功能。

了解数据

基本的问题是大多数用户都想以更有意义的方式使用和显示存储在平表中的数据。此类中最常见的一些数据形式有:

  • 分类:王国、语系、阶级或国家、城市、县、州
  • 系谱:祖父、父亲、孩子
  • 机构:总裁、经理、员工或类别、子类别、项目、子项目

每个标准查询的结果行中的值和位置仅指该行,但层次查询返回的结果中的行在树形结构中的一个位置。为了从非层次结果中获取这种结构信息,必须遍历每一个值,在这个过程中进行检查并构建另一种数据结构。避免这个过程而让 Oracle 做它最擅长的事情可消除开发人员与数据交互的步骤。

在下面的示例中,您将使用以上数据形式中的最后一个 — 企业机构。对于具体的数据,我们可使用在 Oracle 数据库 10g Express 版(入门数据库)提供的相对简单的 HR 数据库片段。

 

图 1如果您使用了包含 OCI8 扩展的 PHP 编译版本,那么使用以下查询和方法将无需任何特殊的设置。使用数据库抽象类(例如 Pear:DBADOdb 或 PHP 5.1 的 PDO)可以提高开发效率,只需更少的代码就可以实现同样的功能。但本方法文档中介绍的层次方法是一个 SQL 扩展,仅适用于 Oracle 用户(并非不鼓励这种方法)。您的代码将不能移植到其他供应商的 RDMBS,而这是使用抽象类的主要目标之一。既然所有的抽象类都将实现类似的功能,下面的示例将使用基础的 OCI 方法。

CONNECT BY 连接

第一个查询使用了 CONNECT BY

SELECT ENAME, JOB, EMPNO, MGR
FROM EMP 
CONNECT BY MGR = PRIOR EMPNO
START WITH MGR IS NULL

这里要注意的重要的元素是:

  • CONNECT BY:这将告诉查询处理程序,您需要获取层次结构。以下表达式将告诉处理程序它需要查看哪些列才能理解该层次结构。
  • PRIOR:这个特殊的保留字将指示以下值位于一个在行链中位置较高的行中。在本例中,您将查询一个员工的经理是表中的另一个员工的情景。
  • START WITH:该子句表示层次结构中的起始位置。该数据的特性决定 NULL 表示一个没有上级的员工,因此您将查找经理字段为 NULL 的员工。在数据建模和实施期间作其它选择时可能需要更多的考虑。下面讨论了 NOCYCLE

然而,当运行了查询时,结果仍然看起来像一张没有明显顺序的平表:

ENAME      JOB            EMPNO        MGR
---------- --------- ---------- ----------
KING       PRESIDENT       7839
JONES      MANAGER         7566       7839
SCOTT      ANALYST         7788       7566
ADAMS      CLERK           7876       7788
FORD       ANALYST         7902       7566
SMITH      CLERK           7369       7902
...

再仔细地看一下:在总裁之后的前三名员工中的每一个都直接位于其各自的经理的下面。然后,模式变得更复杂:Ford 是一名经理为 Jones 的分析人员,但他位于 Adams 下面。这是以扁平方式显示树的根本问题,因为在父亲下面只能有一个孩子。为了帮助我们理解这种结构,您需要在使用层次查询时自动提供几个虚列中的第一个。在上述查询中修改选中的列的列表,包含 LEVEL,这将生成:

ENAME      JOB            EMPNO        MGR      LEVEL
---------- --------- ---------- ---------- ----------
KING       PRESIDENT       7839                     1
JONES      MANAGER         7566       7839          2
SCOTT      ANALYST         7788       7566          3
ADAMS      CLERK           7876       7788          4
FORD       ANALYST         7902       7566          3
SMITH      CLERK           7369       7902          4
...

每一行现在都带了一个标志,指示它在树状结构中的深度;Ford 和 Scott 拥有相同的级别,而没有更低级别的员工出现在他们之间。他们是树状结构中的同级项。这是您以最佳的方式处理结果所需的基本信息.您需要执行的一个额外的任务是对结果排序 — 如果您按照 ENAME (BY ENAME) 对您仔细构建的 CONNECT BY 查询排序 (ORDER),那么这种巧妙的树状结构将被破坏。为了解决这个问题,在 ORDER BY 中添加了 SIBLINGS — 在 Oracle9i 中引进的一个构造,根据您设定的规则,把一个共同父项下面的每一个行集放到一个经过排序的列表中。

SELECT ENAME, JOB, EMPNO, MGR, LEVEL
FROM EMP
CONNECT BY MGR = PRIOR EMPNO
START WITH MGR IS NULL
ORDER SIBLINGS BY ENAME;

查询已经准备就绪,让我们来获取数据。在获取结果之前,您需要与数据库连接,用上面的查询创建一条语句,并让 Oracle 来解析它。(关于完整的程序清单,请查看示例代码。)在准备好语句之后,您就可以获取结果了:

$nrows = oci_fetch_all($stmt, $results, 0, 0, 
				OCI_FETCHSTATEMENT_BY_ROW);

注意您必须使用 OCI_FETCHSTATEMENT_BY_ROW 标记来将数据放到基于行的表示中而不是默认的基于列的表示中。这对于以易于管理的结构提供待编写的代码非常关键。

 

处理数据

 

接下来,您必须编写这种模式中最费力的部分。array_map 使您能够用非常少的句法开销以高度优化的方式将一个用户回调函数应用到数组的每一个元素上。这实现了编程人员时间和处理程序时间节省之间的平衡,使您能够简洁地编写更快速的代码。本示例编造了一个机构项目列表,显示了每一位员工及其工作。这种列表易于显示,此外,它们很好地简化了 CSS,符合 CSS 的风格。

function treeFunc( $current ){
	// the previous row's level, or null on the first row
	global $last;

	// structural elements
	$openItem =	'<li>';
	$closeItem =	'</li>';
	$openChildren =	'<ul>';
	$closeChildren =	</ul>
	$structure = "";

	if( !isset( $current['LEVEL'] ) ){
		// add closing structure(s) equal to the very last
		// row's level; this will only fire for the "dummy"
		return str_repeat($closeItem.$closeChildren, 
			$last);
	}

	// add the item itself
	$item = "{$current['ENAME']} <i>{$current['JOB']}</i>";

	if ( is_null( $last ) ) {
		// add the opening structure in the case of
		// the first row
		$structure .= $openChildren; 
	} elseif ( $last < $current['LEVEL'] ) {
		// add the structure to start new branches
		$structure .= $openChildren;
	} elseif ( $last > $current['LEVEL'] ){
		// add the structure to close branches equal to the 
		// difference between the previous and current 			
		// levels
		$structure .= $closeItem.
			   str_repeat( $closeChildren.$closeItem, 
				$last - $current['LEVEL'] );
	} else {
		$structure .= $closeItem;
	}

	// add the item structure
	$structure .= $openItem;

	// update $last so the next row knows whether this row is
	// really its parent
	$last = $current['LEVEL'];

	return $structure.$item;
}

上述代码中的大部分反映了行的级别;这与该行上面的行的级别相结合,一次性告诉了您您需要知道的关于该数据的所有信息。如果当前级别高于前面的级别,那么树需要增高。 如果当前级别更低或相同,那么树需要变宽 — 虽然在哪个级别依赖于情况是前者还是后者。理解本部分内容的一个问题是,因为每一行只知道前一行的级别,因此所有未尾的格式化都必须由随后的行来完成。在最后一行的情况下,必须插入一个“虚拟”行来清理格式化。

array_map 完成工作

 

现在您获得了一组结果和一个知道如何处理它的函数。您在此编写的 PHP 的目标是使您能够以尽可能方便的方式将这两者结合起来:

// you need this value accessible inside the formatting 
// function; in an object-oriented approach, this can
// be a class variable
global $last;

// set a value not possible in a LEVEL column to allow the 
// first row to know it's "firstness"
$last = null;

// add a dummy "row" to cap off formatting
$results[] = array();

// invoke our formatting function via callback
$formatted = array_map("treeFunc", $results);array_map( "treeFunc", $results );

//output the results
echo implode("\n", $formatted);implode( "\n", $formatted );

上述代码使您能够将数据库和结果集的交互操作与个别行的格式化操作隔离开,使代码保持易读和易于维护。它的结果是:

• KING PRESIDENT 
• BLAKE MANAGER 
• ALLEN SALESMAN 
• JAMES CLERK 
• MARTIN SALESMAN 
• TURNER SALESMAN 
• WARD SALESMAN 
• CLARK MANAGER 
• MILLER CLERK 
• JONES MANAGER 
• FORD ANALYST 
• SMITH CLERK 
• SCOTT ANALYST 
• ADAMS CLERK

Oracle9i 和 Oracle 10g 的特殊特性

Oracle9i 可完成一些以前需要构建数据结构并迭代遍历它的任务。SYS_CONNECT_BY_PATH 函数将获取一个列名,并将该列名附加到所有子项的值上(加上一个分隔符)。利用它,您可以根据行在层次结构中的位置来构建唯一的 ID,并能够展开分隔符来确定某个 n 代父项或者实施许多其他的技巧。

如果您想知道某行的第一个父项,Oracle 10g 为您提供了一种更为简单的方法:CONNECT_BY_ROOT。在任意级别上,这个关键字都将返回特定列之前的第 1 级父项的值,这与上面的 PRIOR 非常类似。此外,Oracle 10g 推出了一些构造,以消除为避免常见的层次结构问题而采取的一些变通方法和大量检查工作。典型而言,如果查询处理程序发现它多次检查同一行以确定其父项,那么它将抛出错误。一些数据结构不可避免地包含这种循环,数据所有者认为它们是有益的。在默认这一情况的前提下,在 CONNECT BY 后添加 NOCYCLE 将允许返回结果,此外还允许填充虚列 CONNECT_BY_ISCYCLE,进行分析或显示。

 

CONNECT_BY_ISLEAF 与 CONNECT_BY_ISCYCLE 类似,是一个虚列,它提供特定行是否有子行的信息。这可以在过程中稍后为基于 CSS 的格式化提供特别有用的分支。

下一步做什么?

利用页面上的 HTML,仍然可以进行一些有趣且重要的选择。树是否需要变为活动的,允许最终用户展开和合并分支?您是否想以更具空间性的方式进行输出?是否将对包含不同字段值的行进行不同的格式化?这些选择全部取决于您的数据和输出目标,可以通过修改 treeMethod 中的不同的结构元素来快速地实现它们。

// construct an array acceptable as a callback type
$cbm = new ConnectByMapper();
$formatted = array_map(array($cbm,"treeMethod"), $results);array_map( array( $cbm,"treeMethod" ), $results );

在示例代码中还包含了解决该问题的一种面向对象的方法,这为进一步的实验提供了一个基础。

结论

 

虽然没有在 SQL 的所有实现中提供,但层次查询方法拥有足够的好处,值得鼓励明智的开发人员来了解和使用它们,特别是如果数据包含了多层的层次结构。PHP 特别适用于 SQL 查询的输出,它支持以面向功能和面向对象的方式来处理数据,可供您或下一个维护者方便地对其代码进行增强。请在您的下一次开发过程中考虑这些好处,或者针对现有代码采取相应的方法,以获得这些好处!

使用 Oracle 和 PHP 执行事务

使用 Oracle 和 PHP 执行事务
作者:Harry Fuecks

如何使用 Oracle 数据库的强大功能可靠地管理多对多关系。

 

本文相关下载:
Oracle 数据库 10g
Zend Core for Oracle
Apache HTTP Server 1.3 和更高版本

2005 年 11 月发表

通过事务,您可以可靠、一致地更改数据库,在应用程序必须管理多对多关系或以批处理形式执行多个查询的情况下,事务可起到至关重要的作用。

 

本“Oracle+PHP 指南”方法文档介绍了事务的必要性以及如何通过 PHP 的 OCI 扩展使用 Oracle 的事务服务,同时还着重介绍了某些潜在的“问题”。

 

事务的必要性

 

假设您维护一个使用 Oracle 存储条目的 web 记录应用程序。数据库模式的某个部分为“blogs”定义了一个表,并为“categories”定义了另一个表,后者通过表“blogs_to_categories”与“blogs”保持多对多关系。以下查询描述了这些表:

CREATE SEQUENCE blog_id_seq
INCREMENT BY 1;
/

CREATE TABLE blogs (
id NUMBER PRIMARY KEY,
title VARCHAR2(200),
date_published DATE,
text CLOB
);
/

CREATE SEQUENCE category_id_seq
INCREMENT BY 1;
/

CREATE TABLE categories (
id NUMBER PRIMARY KEY,
name VARCHAR2(30) UNIQUE
);
/

CREATE TABLE blogs_to_categories (
blog_id INTEGER NOT NULL
REFERENCES blogs(id),
category_id INTEGER NOT NULL
REFERENCES categories(id),
PRIMARY KEY (blog_id, category_id)
);
/

对于使用网志的用户而言,要发布网志条目,他们可以将其分配给“php”、“programming”和“web”这三个类别。同时,阅读网志的用户可以选择按类别(不是默认的“按日期”)查看条目。

 

现在,如果网志作者要更新分配给现有条目的类别(可能是将该条目添加到“oracle”类别,同时在太具普遍性的“web”类别中将该条目删除),则需要从 blogs_to_categories 表中删除并插入行。

 

就 SQL 而言,这可能类似如下所示:

-- Delete all existing category links to this blog entry
DELETE FROM blogs_to_categories WHERE blog_id = 5

-- Insert a single link between a blog and a category
INSERT INTO blogs_to_categories
(blog_id, category_id)
VALUES
    (5, 2)

此插入查询将针对网志条目归档到的每个类别重复一次。

 

但在执行删除查询后,如果 web 服务器与数据库之间发生故障,从而导致无法插入时,该怎么办?这种情况下,您将得到一个无类别的网志条目。

 

为避免该问题的发生,需要以事务的形式执行语句,指示 Oracle 将多个语句作为单个实体进行处理。

 

ACID 事务

 

对于数据库,术语 ACID 定义数据库正确处理事务所必须拥有的属性。这些属性大致说明如下:

  • 原子性 - 事务要么必须彻底完成,要么根本不完成;不允许部分完成事务。
  • 一致性 - 必须始终遵守数据库约束。
  • 孤立性 - 多个事务不应影响彼此的数据。例如,如果事务 B 正在插入到 blogs_to_categories 表中,则不允许事务 A 删除类别。
  • 持久性 - 如果事务已经完成(提交),则不会丢失数据(可以从故障中恢复)。

以下是有关术语的说明:提交事务意味着完成事务,并应用事务对数据进行的任何更改。回滚事务意味着结束事务并撤消它所做的任何更改,以便数据库返回到它在事务开始前所处的状态。也就是说,在出现错误时回滚事务,而在成功时提交事务。

 

通过使用事务,可以放心地执行多个查询(因为您知道因系统故障而丢失数据这样的风险是不存在的),同时解决并发性方面的问题。尤其对于并发访问情形,Oracle 将允许其他用户“读取”事务正在写入的行。执行 read 语句的用户看到的数据将保持不变:也就是说,尽管在事务内部已经执行了任何 INSERTUPDATE 语句,但在提交事务并应用更改之前,用户一直会“看到”原始数据。换言之,写入方不阻止读取方,从而避免了性能瓶颈。

 

使用 PHP 的 Oracle 事务

 

就事务而言,用于 Oracle 的 PHP 扩展有两个行为模式。默认的“自动提交”模式在每个语句执行后(即在每次调用 oci_execute() 函数之后)自动执行提交。

 

“提交模式”由传递给 OCIExecute() 的第二个可选参数控制,该参数是常量并且可以为 OCI_COMMIT_ON_SUCCESS(默认的自动提交行为)或 OCI_DEFAULT(指示 PHP 等待对 oci_commit()oci_rollback() 的显式调用)。

 

在下面这个简单示例中,尝试向 categories 表中添加一些数据,并了解一下在您违反该操作的约束时将发生什么情况:

<?php 
$conn = oci_connect('SCOTT','TIGER') or die;

// Some categories
$categories = array(
'php',
'oracle',
'programming',
'web',
'this string is too long for the name column'
);

$sql = "INSERT INTO categories
(id, name)
VALUES
(category_id_seq.nextval,:category)
        ";

$stmt = oci_parse($conn,$sql);

foreach ( $categories as $category ) {

oci_bind_by_name($stmt,':category', $category,30);

// Note the OCI_DEFAULT parameter
if ( !oci_execute($stmt,OCI_DEFAULT) ) {

// If we have a problem, rollback then die
oci_rollback($conn);
die;
    }

}

// If we got this far, it's OK to commit
oci_commit($conn);// etc.
?>

就其当前格式而言,该示例将失败,因为它尝试插入的最后一个类别违反了名称列限 30 字符的约束。您将看到一个类似如下的错误:

Warning: oci_execute() [function.oci_execute]:OCIStmtExecute: 
ORA-01461:can bind a LONG value only for insert into a LONG
column in /home/harry/public_html/create_categories.php on line
25

如果现在尝试选择 categories 表的内容,则会看到并未插入任何类别。尝试删除以上脚本中传递给 oci_execute() 的参数 OCI_DEFAULT 将发生什么情况?删除过长的类别名称并重新运行该脚本将插入记录。

 

注意,每当调用 oci_execute()(从事务开始的位置一直到显式提交或回滚的位置)时,请确保传递 OCI_DEFAULT 常量。否则,oci_execute() 将使用它的默认行为,即并非只提交该单个查询,还将提交位于它之前的所有查询(起始于事务的开始位置)。此处一个重要的例外情况是,无论您传递给 oci_execute() 的常量是什么,DDL 语句(如那些以 CREATEDROPALTER 开头的语句)将始终自动进行提交,

 

还应注意,当 PHP 脚本终止执行时,Oracle 将自动回滚任何未完成的事务。有了默认的自动提交行为,通常不会使用该方法,但如果脚本达到内存时间限制突然终止时,则未完成事务将回滚。这适用于临时连接和持久连接。

 

以下示例演示了在执行任务过程中,因查询时不小心而可能出现的常见问题。以下脚本使用可表示 HTML 表单的 POST 的示例数据执行三个语句。首先,它向 blogs 表中插入一行,然后获得该表中的行数以反馈给用户。在执行 count 查询时,它未能将 OCI_DEFAULT 传递给 oci_execute()。最后,它尝试向 blogs_to_categories 表中插入两个类别 ID 以便与网志条目建立关联。由于 categories 表中不存在某个类别 ID,因此 Oracle 将引发一个有关违反引用约束的错误。

<?php 
$conn = oci_connect('SCOTT','TIGER') or die;

/* Start by inserting a blog entry */

// An array representing a submitted blog entry
$blog = array(
'id'=>NULL,
'title'=>'Hello World!',
'text'=>'This is my first blog entry',
'categories' => array(1,999) // Note second ID does not exist
);

$sql = "INSERT INTO blogs
(id,title,date_published,text)
VALUES
(blog_id_seq.nextval,:title,SYSDATE,:text)
RETURNING
id INTO :id ";

$stmt = oci_parse($conn,$sql);

oci_bind_by_name($stmt,':title', $blog['title'],200);
oci_bind_by_name($stmt,':id', $blog['id'],32);
oci_bind_by_name($stmt,':text', $blog['text']);

// Note OCI_DEFAULT - begin a transaction
if ( !oci_execute($stmt,OCI_DEFAULT) ) {
oci_rollback($conn);
exit(1);
}

/* Now count the total number of blog entries */

$sql = "SELECT COUNT(*) AS num_entries FROM blogs";

$stmt = oci_parse($conn,$sql);

// Automatically populates the $num_entries variable
oci_define_by_name($stmt,"NUM_ENTRIES",$num_entries);

// Whoops forgot the OCI_DEFAULT!
oci_execute($stmt);

oci_fetch($stmt);

$result = "Blog added.There are now $num_entries blogs\n";

/* Now insert the categories related to the entry */

$sql = "INSERT INTO blogs_to_categories
(blog_id, category_id)
VALUES
(:blog_id, :category_id)";

$stmt = oci_parse($conn,$sql);

// Loop through the categories
foreach ( $blog['categories'] as $category_id ) {

oci_bind_by_name($stmt,':blog_id', $blog['id'],32);
oci_bind_by_name($stmt,':category_id', $category_id,32);

// Note OCI_DEFAULT again
if ( !oci_execute($stmt,OCI_DEFAULT) ) {
oci_rollback($conn);
exit(1);

    }

}

oci_commit($conn);
echo $result

?>

在执行 SELECT 语句时,为计算 blog 表中的行数,该脚本指示 Oracle 自动提交。这样,SELECT 和前一个 INSERT(用于启动事务)将立刻完成。对于第三个语句,它启动一个全新事务并在遇到约束违规时回滚插入的类别 ID。因此,尽管插入了网志条目,但却丢失了它的类别。应将这三个查询作为单个工作单元执行;在遇到约束违规时,所有插入均回滚并且用户指示他们需要更正提供的类别并再次提交该表单。

 

要正确处理事务,应将 OCI_DEFAULT 常量传递给所有三个 oci_execute() 调用。下面的更改解决了此问题:

 
图 1 

事务孤立和连接

 

当事务开始执行时,Oracle 获得数据库的部分“快照”(称作回滚段),以便其他进程在事务执行期间可以获得一个一致的数据库视图。这意味着执行事务的进程与在该事务外部运行的其他进程将看到不同的数据库状态。在很少的情况下,您可能希望在执行事务的同时访问“外部状态”中的数据。为此,您需要使用 oci_new_connect() 函数,该函数可以确保您建立一个新的数据库连接,而对 oci_connect() 的重复调用将全部返回相同的数据库连接资源。通过新连接执行的语句将成为在已经建立的连接中运行的孤立事务。

 

以下脚本对此进行了演示:

<?php 
$conn = oci_connect("SCOTT", "TIGER") or die;

$categories = array(
'transactions',
'sessions',
);

$sql = "INSERT INTO categories
(id, name)
VALUES
(category_id_seq.nextval,:category)
        ";

$stmt = oci_parse($conn,$sql);

foreach ( $categories as $category ) {

oci_bind_by_name($stmt,':category', $category,30);

if ( !oci_execute($stmt,OCI_DEFAULT) ) {
oci_rollback($conn);
die();
    }

}

// Establish a new, temporary Oracle connection
$newSess = oci_new_connect("SCOTT", "TIGER") or die;

$sql = "SELECT COUNT(*) AS num_cats FROM categories";

$stmt = oci_parse($newSess,$sql);

oci_define_by_name($stmt,"NUM_CATS",$oldnum_cats);

// No need for OCI_DEFAULT here...
oci_execute($stmt);

oci_fetch($stmt);

// Logoff the session (technically not needed as it will be cleaned up anyway)
oci_close($newSess);

// Do the same for the primary session
$stmt = oci_parse($conn,$sql);

oci_define_by_name($stmt,"NUM_CATS",$newnum_cats);

// Here we do need OCI_DEFAULT
oci_execute($stmt,OCI_DEFAULT);

oci_fetch($stmt);

// If we got this far, it's OK to commit
oci_commit($conn);

echo "Before I had $oldnum_cats.Now I have $newnum_cats\n";
?>

变量 $oldnum_cats 的值尽管是在事务启动后获取的,但却包含事务启动前 categories 表中的行数。这是因为我使用 OCINLogon() 创建的会话独立于正在其中运行事务的主会话,它具有不同的事务上下文。

 

结论

 

如果您以前并不了解事务的必要性,那么通过本文的学习您现在应该对此有所了解,即事务使您可以可靠、一致地更改数据库。您还了解了如何通过 PHP 的 OCI 扩展执行事务(注意它的默认自动提交行为存在的潜在问题)以及如何在单独的事务上下文中执行查询。

 


Harry Fuecks [http://www.phppatterns.com] 于 1999 年接触 PHP,此后作为 PHP 开发人员和馔稿人而声名鹊起。他通过 Sitepoint 开发人员网络发布了大量初级和中级 PHP 文章,著有 The PHP Anthology (SitePoint) 一书。

将您的意见发送给我们