业务是这样：
触发过来的记录有以下一些判断约束：
新增：无约束，新增一条数据变动。
修改：
1） 如果变动表中存在同一记录的新增变动记录，但该新增变动记录处理状态为未处理，如果过滤状态为未过滤，则更新该新增变动记录的接受时间为当前时间；如果过滤状态为已过滤，则不做任何处理。
2） 如果变动表中存在同一记录的修改变动记录，但该修改变动记录处理状态为未处理，则更新该修改变动记录（更新接收时间为当前时间），并设置该修改变动记录的过滤状态为未过滤。
3） 其他情况，添加一条修改的变动记录。
删除：
1） 如果变动表中存在同一记录的新增变动记录，但该新增变动记录处理状态为未处理，则删除该新增变动记录。
2） 如果变动表中存在同一记录的修改变动记录，但该修改变动记录处理状态为未处理，则删除该修改变动记录，并添加一条删除的变动记录（或者更新该修改变动记录为删除变动记录，接受时间为当前时间，过滤状态为未过滤）。
3） 其他情况，添加一条删除的变动记录。
Read the rest of this entry »
Archive for category oracle
测试带有业务逻辑的trigger
十二 9
绿色oracle客户端
十二 4
以后不再需要安装oracle客户端了，需要两个dll文件，oci.dll和oraociei11.dll，只要扔到system32文件夹下就可以了。当连接数据库的时候，在Web.Config中的配置改为以下配置就ＯＫ了。
<add name="DataAccessQuickStart" providerName="System.Data.OracleClient" connectionString="Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.100)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)));uid=system;pwd=123"/>
那用到的两个dll是从哪里来的呢？答案是从oracle官方网站上下载，下载地址为：instantclient-basic-win32-11.1.0.7.0.zip解压并找到上面讲的两个dll就ＯＫ了。在我写这个blog的时候，发现了其他的朋友也有写好的，地址分别是：
〔原创〕Windows下面怎样配置 Oracle10g即时客户端（绿色安装ORACLE客户端）
免安装的Oracle客户端 – Oracle Instant Client介绍与配置
可以参考上面两个朋友的文章。
在上一篇中介绍了如何使用odp.net代替ado.net，不过demo程序是C/S的，今天写一个B/S的，主要是获取翻页存储过程的数据和行数。
首先，在Web.Config中配置数据库连接字串，具体企业库怎么配置连接字串这里就不介绍了。代码如下：
<add name="OracleSource" connectionString="Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.88)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)));User Id=sys;Password=123;" providerName="Oracle.DataAccess.Client" />
然后，在.cs文件中编写如下代码，代码要注意的地方是存储过程的参数名要与代码中的参数名一致：
/// <summary>
/// 获取当前页数据
/// </summary>
/// <returns></returns>
public DataSet GetCurrentDataSet()
{
//调用EnterpriseLibrary获取Database对象
Database db = DatabaseFactory.CreateDatabase("OracleSource");
//获取OracleCommand
OracleCommand com = db.GetStoredProcCommand("WP_ALL_PAGINATION") as OracleCommand;
//表名称
com.Parameters.Add("TABLENAME_IN", OracleDbType.Varchar2, 50);
com.Parameters["TABLENAME_IN"].Direction = ParameterDirection.Input;
com.Parameters["TABLENAME_IN"].Value = tableName;
//每页显示记录数
com.Parameters.Add("PAGESIZE_IN", OracleDbType.Int32);
com.Parameters["PAGESIZE_IN"].Direction = ParameterDirection.Input;
com.Parameters["PAGESIZE_IN"].Value = PageSize * PageNo;
//当前页索引值
com.Parameters.Add("PAGEINDEX_IN", OracleDbType.Int32);
com.Parameters["PAGEINDEX_IN"].Direction = ParameterDirection.Input;
com.Parameters["PAGEINDEX_IN"].Value = (PageNo - 1) * PageSize;
//排序字段
com.Parameters.Add("ORDERSYNTAX_IN", OracleDbType.Varchar2, 3000);
com.Parameters["ORDERSYNTAX_IN"].Direction = ParameterDirection.Input;
com.Parameters["ORDERSYNTAX_IN"].Value = orderBY;
//Where子句下条件
com.Parameters.Add("CONDITION_IN", OracleDbType.Varchar2, 3000);
com.Parameters["CONDITION_IN"].Direction = ParameterDirection.Input;
com.Parameters["CONDITION_IN"].Value = whereCondition;
//显示的字段
com.Parameters.Add("FIELDSNAME_IN", OracleDbType.Varchar2,300);
com.Parameters["FIELDSNAME_IN"].Direction = ParameterDirection.Input;
com.Parameters["FIELDSNAME_IN"].Value = showFields;
//主键
com.Parameters.Add("ONLYFIELD_IN", OracleDbType.Varchar2, 100);
com.Parameters["ONLYFIELD_IN"].Direction = ParameterDirection.Input;
com.Parameters["ONLYFIELD_IN"].Value = guid;
//返回总记录数
com.Parameters.Add("COUNT_OUT", OracleDbType.Int32);
com.Parameters["COUNT_OUT"].Direction = ParameterDirection.InputOutput;
com.Parameters["COUNT_OUT"].Value = 0;
//返回记录集
com.Parameters.Add("cur_out", OracleDbType.RefCursor);
com.Parameters["cur_out"].Direction = ParameterDirection.Output;
DataSet ds = db.ExecuteDataSet(com);
//符合条件的记录总条数
Count = int.Parse(com.Parameters["COUNT_OUT"].Value.ToString());
return ds;
}
由于微软在.net framework4中会将System.Data.OracleClient.dll deprecated，而且就访问效率和速度而言，System.Data.OracleClient.dll与Oracle.DataAccess.dll相比，微软的确实没有oracle提供的类库有优势，所以我放弃了使用多年的System.Data.OracleClient.dll，取而代之的是odp.net。然而odp.net的优点不止这些，还包括：
1、不在安装客户端也能访问服务器上的oracle（假设Application Server与DB Server 分开）
2、不需要配置TnsNames.Ora文件
当然，我选择odp.net的最主要的原因还是性能。这篇文章列举了两者之间的对比。Technical Comparison: ODP.NET Versus Microsoft OracleClient
ORA-01157: 无法标识/锁定数据文件 7
錯誤提示為：
SQL> startup ORACLE 例程已经启动。 Total System Global Area 293601280 bytes Fixed Size 1290208 bytes Variable Size 209715232 bytes Database Buffers 75497472 bytes Redo Buffers 7098368 bytes 数据库装载完毕。 ORA-01157: 无法标识/锁定数据文件 7 - 请参阅 DBWR 跟踪文件 ORA-01110: 数据文件 7: 'D:\TCM52.DBF'
原因：我在數據庫服務停止的時候，將數據文件D:\TCM52.DBF刪除了。
解決方法：
SQL> startup ORACLE 例程已经启动。 Total System Global Area 293601280 bytes Fixed Size 1290208 bytes Variable Size 213909536 bytes Database Buffers 71303168 bytes Redo Buffers 7098368 bytes 数据库装载完毕。 ORA-01157: 无法标识/锁定数据文件 7 - 请参阅 DBWR 跟踪文件 ORA-01110: 数据文件 7: 'D:\TCM52.DBF' SQL> alter database datafile 'd:\TCM52.dbf' offline drop; 数据库已更改。 SQL> alter database open; 数据库已更改。 SQL> drop tablespace TCM52 including contents; 表空间已删除。 SQL> create undo tablespace TCM52 datafile 'E:\oracle\product\10.2.0\oradata\TCM52.dbf' size 2048M extent management local; 表空间已创建。 SQL> alter system set undo_tablespace=TCM52; 系统已更改。 SQL>
有這樣一個需求：用戶A在表空間ts1下，用戶B在表空間ts2下，要求在用戶B下創建一個trigger，這個trigger的主要功能是修改用戶A中的表。
trigger如下：
create or replace trigger tri_test
after insert or update or delete on t_test
for each row
begin
if inserting then
insert into A.t_user(id,title,modifydate)
values(:new.ID,:new.TITLE,1,sysdate);
elsif updating then
update A.t_user t set t.title =:new.CTITLE,t.modifydate=sysdate where t.id=:new.ID;
elsif deleting then
update A.t_user t set t.deleted=1,t.modifydate=sysdate where t.id=:new.ID;
end if;
end ;
但編譯的時候提示：
PL/SQL: ORA-01031: insufficient privileges 權限不足的意思 PL/SQL: SQL Statement ignored
既然提示權限不足，那麼我想一定是用戶B中的trigger對用戶A中的表是無權操作的，終於在google的幫助下，找到了解答，見解決問題的blog
解決方法如下：
grant update on A.t_user to B grant insert on A.t_user to B
這樣就解決了
公司的一个项目要求服务器使用windows2008操作系统和oracle数据库，于是安装完成Windows2008后，就把去年下载的oracle10g复制到服务器上安装，没想到出错，弹出一个提示，上面一堆问号。后来一查才得知，Windows2008需要安装特定的oracle版本。于是到oracle官网上下载了win32_11gR1_database.zip，可以运行在Windows2008上
下面是从oracle官上网复制来的。注：下载oracle数据库要先登录oracle网站的。
Oracle Database 11g Release 1
Standard Edition, Standard Edition One, and Enterprise Edition
(11.1.0.7.0)
Microsoft Windows Server 2008 (32-bit) (1.9 GB) | See All (Including Client, Clusterware)
Microsoft Windows Server 2008 x64 (1.9 GB) | See All (Including Client, Clusterware)
(11.1.0.6.0)
Microsoft Windows (32-bit) (1.7 GB) | See All (Including Client, Examples, Gateways, Clusterware)
Microsoft Windows (x64) (1.7 GB) | See All (Including Client, Examples, Clusterware)
Linux x86 (1.7 GB) | See All (Including Client, Examples, Gateways, Clusterware)
Linux x86-64 (1.8 GB) | See All (Including Client, Examples, Gateways, Clusterware)
Solaris (SPARC) (64-bit) (1.9 GB) | See All (Including Client, Examples, Gateways, Clusterware)
AIX (PPC64) Disk 1, Disk 2 (2.3 GB) | See All (Including Client, Examples, Gateways, Clusterware)
HP-UX Itanium Disk 1, Disk 2 (2.3 GB) | See All (Including Client, Examples, Gateways, Clusterware)
HP-UX PA-RISC (64-bit) Disk 1, Disk 2 (2.3 GB) | See All (Including Client, Examples, Gateways, Clusterware)
安装过程依旧是那样简单，但在配好IIS站点，准备连接数据库的时候出错了，以下是错误提示：
System.Data.OracleClient 需要 Oracle 客户端软件 8.1.7 或更高版本。
以往这种情况的处理就是把ASPNET用户加上oracle/bin文件夹上，并开启读取与运行权限就OK了，但windows2008中，没有ASPNET这个用户。那加谁好呢？试验了几个用户后，IIS_IUSRS？，都无果。最后，找到了“Authenticated Users”这个用户组，加上之后，重启了IIS，还是没有效果。是不是重启一下就OK了呢？抱着试试看的想法，重启了服务器，再一试，果然OK了。
因为应用中有通过页面操作写文件的代码，执行的时候就报错，说无法写，这想再试想起了“Authenticated Users”这个用户组，把这个用户组加上应用的文件夹安全权限里面，就可以进行文件的写操作了。
oracle10g创建数据表的索引
十 10
以下sql语句中，以fx作为oracle的用户进行操作的，请复制代码的同学进行修改。
一、设置词法分析器
BEGIN
ctx_ddl.create_preference ('fx_lexer', 'chinese_vgram_lexer');
END;
/
二、针对数据表的特定字段建索引
CREATE INDEX I_DM_FILE ON DM_FILE (FILEBODY) indextype is ctxsys.context
parameters('lexer fx_lexer ');
三、创建存储过程同步更新与优化索引
prompt
prompt Creating procedure OPTIMIZE_I_DM_FILE
prompt =====================================
prompt
CREATE OR REPLACE PROCEDURE FX.optimize_i_dm_file
as
begin
execute immediate 'begin ctx_ddl.optimize_index(''i_dm_file'',''FULL'');end;';
END optimize_i_dm_file;
/
prompt
prompt Creating procedure SYNC_I_DM_FILE
prompt =================================
prompt
CREATE OR REPLACE PROCEDURE FX.sync_i_dm_file
as
begin
begin
execute immediate 'begin ctx_ddl.sync_index(''i_dm_file'',''2M'');end;';
end;
END sync_i_dm_file;
/
四、新建作业，定时执行上面创建的存储过程
begin
sys.dbms_job.submit(job => :job,
what => 'SYNC_I_DM_FILE;',
next_date => to_date('22-04-2009 17:17:39', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'SYSDATE + (1/24/4)');
commit;
end;
/
begin
sys.dbms_job.submit(job => :job,
what => 'optimize_i_dm_file;',
next_date => to_date('22-04-2009 17:20:13', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'SYSDATE+1');
commit;
end;
/
五、赋予作业运行的权限
grant execute any procedure to fx;
Oracle数据库导入导出方式
十 10
注意事项：
1. Oracle9i的数据能导入倒Oracle10g，而Oracle10g的数据不能导入Oracle9i
2. 数据库导出的时候要导二个地方：1.导出用户对象2.导出数据
3. 必须要安装过完整版的Oracle数据库才能进行导入和导出操作
4. 如果在导入的用户表中有数据，而且没有设置主键，请先将数据清空或把表删除
导出用户对象和数据
1、没有安装过的先需要安装，安装过的打开PL/SQL工具 ，选择要导出的数据库，输入用户名和密码，登录类型为普通（Normal）
２、登录系统后选择“我的对象”（英文My object）
３、选择工具（英文Tool）下面的导出用户对象（英文Export User Object）
4、在弹出的新窗口中选择要导出的用户对象，包括：表、视图、存储过程等，设置好导出文件的保存路径，导出的文件格式为.sql，都是对象的sql脚本。
５、选择工具（英文Tool）下面的导出表（英文Export table）
６、在弹出的新窗口中选择要导出的表，并设置好导出文件保存路径，导出的数据库格式默认为dmp
导入用户对象和数据
１、可以直接通过点击操作页面中的注销按钮登录到要导入的数据库中
２、登录到另一个数据库后，点击“新建”中的命令窗口（英文command window）
３、弹出新窗口，将生成的脚本执行（导出的用户对象），用文件编辑器如editplus或者nodepad++打开.sql文件，全选脚本直接托进弹出的窗口也可以打开脚本把里面的内容进行复制粘贴
４、脚本执行好之后，进行表导入，选择工具——导入表（英文guide rope table），弹出导入窗口，选择导出的文件后执行导入操作。
--查询指定用户名下的表的记录数 select NUM_ROWS,TABLE_NAME from dba_all_tables where owner='用户名' order by NUM_ROWS desc
--删除重复的记录 delete from onetable where guid in(select guid from onetable group by guid Having count (guid)>1) and rowid not in(select min( rowid ) from onetable group by guid Having count (guid)>1)
CREATE OR REPLACE PROCEDURE GETRECORDFROMPAGE(
tblName in varchar2, -- 表名
fldName in varchar2:='*', --显示列
PageSize in number := 20, -- 页尺寸
PageIndex in number := 1, -- 页码
strOrderSyntax in varchar2, ---排序的具体语法
strWhere in varchar2 := '', -- 查询条件 (注意: 不要加 where)
Onlyzd in varchar2 :='*',--唯一字段
Total in out number,
cur_OUT OUT sys_refcursor
)
as
tempSql varchar2(8000):='';
countSql varchar2(2000):='';
strSql varchar2(8000):='';
condition varchar2(1000):=strWhere;
fieldsName varchar2(1000):='';
begin
if condition='' or condition is null then
condition := '1=1';
end if;
fieldsName := trim(fldName);
if fieldsName != '*' then -- 不为 '*'
tempSql := 'select '|| fldName ||' from '||tblName||' where '||condition||' '||strOrderSyntax;
else
tempSql := 'select a.* from '||tblName||' a where '||condition||' '||strOrderSyntax;
end if;
--记录集
strSql := 'select t.*,rownum as num from ('||tempSql||') t ';
strSql := 'select * from ('|| strSql ||') tt where tt.num<='||CAST((PageSize*PageIndex) AS char)||' and tt.num>'||CAST((PageSize*(PageIndex-1)) AS char);
--为了排序，重新组织语句
--strSql := 'select * from ('|| strSql||') tt '||strOrderSyntax;
--记录总数
countSql := 'select DISTINCT '||Onlyzd||' from '||tblName||' where '||condition;
countSql := 'select count(*) as Total from ('|| countSql ||') t';
execute immediate countSql into Total;
OPEN cur_OUT FOR strSql;
END GETRECORDFROMPAGE;
--创建表空间 create tablespace HzjwUiasDB logging datafile 'd:\DBRunfile\HzjwUiasDB.dbf' size 1024M autoextend on next 512M maxsize 20480M extent management local;