Oracle 11g 学习笔记

 

一些 ASP.NET + Oracle 11g 系统边写边学的随笔，包括 Oracle 的「ROWNUM」、「Sequence 流水号」。 (六) Oracle 的 ROWNUM，等同其它数据库的 SELECT TOP Oracle 不支持 SELECT TOP 语法，若要撷取最大的几笔、或最小的几笔记录，必须用 ROWNUM 关键词并搭配 Subquery。 例如要取最小的 10 笔，可用如下语句： SELECT id, name, ROWNUM FROM (SELECT id, name FROM table ORDER BY id) WHERE ROWNUM <= 10; 若要取最大的 10 笔，就再加上 DESC： SELECT id, name, ROWNUM FROM (SELECT id, name FROM table ORDER BY id DESC) WHERE ROWNUM <= 10; 此外，还可以做一些变化应用： SELECT a.id, a.name, ROWNUM FROM (SELECT id, name FROM table ORDER BY id) a WHERE ROWNUM <= 10 ORDER BY ROWNUM DESC;     Oracle 的 ROWNUM、Top-N Query 官方教学 (英文)： http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html http://www.oracle.com/technology/oramag/oracle/07-jan/o17asktom.html 此外，Oracle 还有两种 ROW_NUMBER() OVER 和 RANK() OVER 语法，亦可将已查询出来的全部数据，再给予连续的流水号。虽然它们无法像 ROWNUM 一样给 WHERE 条件式，但适 合用来撰写 ASP.NET GridView 控件的「分页」功能的 Stored Procedure。有关其用法，可参考： http://i.cn.yahoo.com/gaoxiaoqing2003/blog/p_8/ http://keke-wanwei.javaeye.com/blog/138632   (七) Oracle 的 Sequence，等同其它数据库的 Identity Oracle 不支持 SQL Server 和 Sybase 都支持的 Identity 自动增号字段，也不支持 INSERT INTO 以后立即取得最新一笔记录 Identity 号码的「SELECT @@identity;」语法， 要达成上述功能，必须改用 Sequence (流水号)。 Sequence 不包含在 table 中，某一个 Sequence 亦不和 table 做一对一的对应。要用 Sequence，必须先自己手动建立，语法为： CREATE SEQUENCE seq_name; 或 CREATE SEQUENCE seq_name INCREASE BY 1 START WITH 1 MAXVALUE 9999 NOCACHE NOCYCLE; 若不下参数，预设从 1 开始，每次增号 1，最大值为 10 的 27 次方，存储值达到 MAXVALUE 不会自动重新编号 (若对应至 table 的 Primary Key，此值应采默认值 NOCYCLE)； CACHE 选项的默认值，会在 memory 产生 20 笔数据。 执行以下语句，可看到所有 Sequence 的设定及存储内容。其中的 LAST_NAME 为其下一个将要产生的值。 select * from user_sequences; 要看某一个 Sequence 的当前值、下一个值，可用如下语句： select table1_seq.CURRVAL from dual; select table1_se1.NEXTVAL from dual;   需注意第二个语句 NEXTVAL 只要一被执行到，该个 Sequence 的内部编号，就会自动增加一个号码，而不仅只是 select 撷取而已。 若要搭配 Sequence，新增一笔记录到 table，可用如下语法： INSERT INTO table1 (id, name) VALUES (table1_seq.NEXTVAL, 'name1'); 在 Oracle 10g 以前的版本，或您用的是 OleDb 联机方式 (OracleClient 亦可)，当您想在 INSERT INTO 记录时，Primary Key 希望能写入 Sequence 的值，可用如下写法 (亦 可在新增完成后，立即传回该笔记录最新的 Sequence 值)： using System.Data.OleDb; OleDbConnection odConn = null; OleDbCommand odCmd = null; Int64 intDATA_ID_AfterInserted = 0; string strSql = "BEGIN SELECT table1_seq.NEXTVAL INTO :id FROM dual; INSERT INTO table1(id, name) VALUES(:id, :name); END;"; ...中间略... OleDbParameter p; p = odCmd.Parameters.Add(":id", OleDbType.Double, 7); p.Direction = ParameterDirection.Output; odCmd.Parameters.Add(":name", OleDbType.VarWChar, 30).Value = TextBox1.Text; odCmd.ExecuteNonQuery(); intDATA_ID_AfterInserted = Convert.ToInt64(p.Value); // 立即传回该笔记录最新的 Sequence 值 若您用的是 Oracle 10g 及以后的版本，且用的是 OracleClient Data Provider，则可用以下的「RETURNING INTO」更简洁写法。但须注意，OleDb 联机方式若用此种写法，在写 入时并不会造成 error 或引发 exception，但写入值会不正常。 string strSql = "INSERT INTO table1(id, name) VALUES(table1_seq.NEXTVAL, :name) RETURNING id INTO :id"; --------------------------------------------- 本帖第 (七) 点的 ASP.NET 2.0 + Oracle 11g 示例下载点 (批次新增 + 新增后马上取得最新 Sequence 值)： http://files.cnblogs.com/WizardWu/081128.zip --------------------------------------------- 若您执行本示例的操作系统中，并未安装 Oracle 11g server-side 软件 (数据库安装在别台主机)，则当您用 Visual Studio 执行本示例时，可能会出现类似以下的错误讯息： “OraOLEDB.Oracle.1”未在本地计算机注册 解决方式，是去 Oracle 官方网站，下载 Data Provider 和 Client-side 程序并安装，下载网址如下： http://www.oracle.com/technology/software/tech/windows/odpnet/index.html (较新) http://www.oracle.com/technology/software/tech/dotnet/utilsoft.html (较旧) 安装完后，即能以 Visual Studio 执行本示例。但若改以 IIS 执行时，仍会出现上述的错误信息，因为您还要再设定一些让 IIS / ASP.NET 的用户，有写入伺服端 Oracle 所在文件夹的权限。有关其设定，其参考本站下一篇帖子「Oracle 11g 学习笔记 (4)」。   建立序列 : CREATE SEQUENCE CustID INCREMENT BY 1 START WITH 1000 NOCACHE; 以序列值輸入資料 : INSERT INTO CUSTOMER (CustomerID, Name, AreaCode, PhoneNumber) VALUES (CustID.NextVal, ‘Mary Jones’, ‘350’, ‘555–1234); 取回剛建立的資料列 : SELECT * FROM CUSTOMER WHERE CustomerID = CustID.CurrVal       一些 ASP.NET + Oracle 11g 系统边写边学的随笔，包括引用 Oracle 官方的 Data Provider、更改 Oracle 存储的日期格式。 (八) 引用 Oracle 官方的 Data Provider 在上一篇帖子「Oracle 11g 学习笔记 (3)」的最后，有提供一个 ASP.NET 2.0 的示例给大家下载。但该帖最后有提到，若您执行示例的操作系统中，并未安装 Oracle 11g 的 server-side 程序 (例如 Oracle 安装在别台主机)，则当您用 Visual Studio 执行本示例时，可能会出现下列的错误讯息： “OraOLEDB.Oracle.1”未在本地计算机注册 OraOLEDB.Oracle' 提供者并未登录于本机计算机上 The OraOLEDB.Oracle provider is not registered on the local machine 解决方式，是去 Oracle 官方网站，下载 Data Provider 和 Oracle 的 client-side 程序并安装，下载网址如下： http://www.oracle.com/technology/software/tech/windows/odpnet/index.html (较新) http://www.oracle.com/technology/software/tech/dotnet/utilsoft.html (较旧) 安装完后，即能以 Visual Studio 执行本示例。但您若改用 IIS 执行示例时，仍可能会出现上述的错误信息，因为若您用的是 Windows 操作系统，必须在 NTFS 分区上，再设定一些让 IIS / ASP.NET 的用户，有写入 IIS 所在主机的 Oracle 主程序目录的权限。会出现这种错误，是因为 Oracle 和 IIS 安装在不同的主机上。 (1) 以版工而言，我用的是 Windows Server 2003 操作系统，我是将下列两个目录，开放文件夹的权限，让「ASP.NET 用户」、「IIS_WPG 群组」、「NETWORK SERVICE 群组」有写入和修改的权限 (是设定 IIS 或 Visual Studio 所在的主机，而非 Oracle server-side 程序所在的主机)： C:/app C:/Program Files/Oracle 再于 Windows 中执行「gpupdate /force」迫使设定马上生效 (若仍不行就重开机)。 (2) 网络上并有几篇文章，提到还要做一些设定： http://365.blog.ccidnet.com/blog.php?do=showone&itemid=687411&typ=blog http://topic.csdn.net/t/20050428/09/3972494.html http://topic.csdn.net/t/20040510/17/3050699.html http://www.blueshop.com.tw/board/show.asp?subcde=BRD20050513132850F5H&fumcde=FUM20041006161839LRJ&rplcnt=16 例如对 Oracle server-side 程序所在的主机，对 sqlnet.ora 这个档案，做以下的设定： SQLNET.AUTHENTICATION_SERVICES=(NTS) 改成 SQLNET.AUTHENTICATION_SERVICES=(NONE) 以版工而言，sqlnet.ora 是位在下列目录： C:/app/Administrator/product/11.1.0/db_1/NETWORK/ADMIN (3) 网络上还提到，若仍出现先前提到的错误，就再对 Oracle client-side 程序所在的主机 (即 IIS 所在的主机)，做以下的 Registry 注册动作： regsvr32 ../ORACLE_HOME/bin/OraOLEDB11.dll 但版工我并不用做到这个动作，IIS 即可用 ASP.NET 应用程序，联机到另一台主机上的 Oracle 11g。   另须注意，若您用的是 Oracle 提供的 OleDb Data Provider，当透过 Visual Studio 2005 的图形设定界面，设定数据库联机后，SQL 语句中的数据表名称前后，会被自动加上「中括号 [ ] 」，如下： SELECT * FROM [t04] 当您一执行时，这组「中括号」却会引发下列的错误： ORA-00903: 表格名称无效 解决之道，是手动把「中括号」拿掉即可。若您用的是 OracleClient Data Provider，则不会有此问题。但若透过 Visual Studio 2005 的图形界面，设定 OracleClient 数据库联机后，仍会在数据表的名称前后，自动加上「双引号 " "」，如下： SELECT * FROM "T04" ------------------------------------------------------------------------------------------ (九) 更改 Oracle 的日期格式 Oracle 在中文的操作系统中安装好后，在 Date 类型中，预设的日期格式为「03-3月-03」。此时透过 ASP.NET 应用程序，虽仍能正常「读、写」Oracle 的 Date 类型，但若要 透过 SQL Plus 写入数据库，就必须在「月份」的数字后面，再加上一个中文字「月」，才能正确写入数据库。 若您在 SQL Plus 中输入下列指令： select * from nls_session_parameters; 会看到 NLS_DATE_FORMAT 参数，其值为：DD-MON-RR NLS_LANGUAGE 参数，其值为：TRADITIONAL CHINESE (以繁体中文 Windows 操作系统为例) 有些情况，我们可能希望更改 Oracle 中存储的日期格式，若在 Windows 操作系统下，我们就必须去修改「注册表 (Registry)」。若您用的是其它操作系统，亦可参考下列这几 篇文章： http://space.itpub.net/658698/viewspace-265000 http://topic.csdn.net/t/20030320/16/1555747.html http://tech.techweb.com.cn/archiver/tid-228974.html 以 Windows Server 2003 操作系统，以及 Oracle 11g 为例，我们要先在 Oracle server-side 程序所在主机的 Windows 中，输入「regedit」指令进入 Registry，接着进入到 ： HKEY_LOCAL_MACHINE → SOFTWARE → ORACLE → KEY_OraDb11g_home1 (1) 修改既有的 NLS_LANGUAGE 参数，将存储月份的格式，从中文「月」改成英文： 进入 Registry 上述的目录后，将既有的 NLS_LANGUAGE 参数，从原本的「TRADITIONAL CHINESE_TAIWAN.ZHT16MSWIN950」，改成「American_America.ZHT16MSWIN950」即可；简体中文的操作系统亦同，把前半段的字符串，改成「American_America」即可。 (2) 新增一个 NLS_DATE_FORMAT 参数，将存储格式依我们的需求修改： 进入 Registry 上述的目录后，新建一个「字符串值」，命名为「NLS_DATE_FORMAT」，并设置其值为： YYYY-MM-DD:HH24:MI:SS 如此即成功地修改了 Oracle 的默认时间格式。此时若您再于 SQL Plus 输入指令： select * from nls_session_parameters; 会看到 NLS_DATE_FORMAT 参数，其值已经变为：YYYY-MM-DD:HH24:MI:SS 上述两种修改，不论修改与否，都仍可用 ASP.NET 等应用程序，正常以「2008/1/3」或「2008/01/03 12:30:30」这类的格式去写入 Oracle 数据库。 但应注意，若您在 Oracle server-side 内建的，或从 Oracle 网站独立下载的「Oracle SQL Developer」这套图形界面工具，若在里面输入「select * from nls_session_parameters;」这项指令，所看到的语系信息，并不是真正在 Oracle 11g server-side 的设定，而是「Oracle SQL Developer」工具其本身自己的语系设定。 在 Oracle 中，不管是透过 SQL Plus，还是 Oracle SQL Developer 这套工具，只要是对数据做 INSERT、UPDATE，都要再下一个 Commit; 或 Rollback; 的指令。之前版工我发现透过 Oracle SQL Developer 写入数据库的记录，透过 SQL Plus 或 ASP.NET 一直读不出来，原本以为是两者的日期格式、语系各自独立，且设定不同的原因，事实上并不是，而是要在写入数据库后，再下一次「Commit;」指令，才算真正完成写入的动作。 此外，在网络上流传的另一项指令： alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; 只能暂时性地修改语系设定。因此若要修改语系，最好还是修改 Oracle server-side 程序所安装主机的 Registry，才是永久性的解决方案。 要注意的是，包含有 ROWNUM 的 WHERE 条件式，一定要包含 1，例如： WHERE ROWNUM >0 WHERE ROWNUM >=1 WHERE ROWNUM <10 若不包含 1 的话，所下的查询会永远查无数据。