在Php格式中,冒號被轉義了,應該在的有的old和new前加冒號,以示更加清晰.
--oracle悲觀封鎖應用示例(以下包是研究Oracle的11i后模擬的)：
--ttx_tmp.sql
代碼:
create or replace package ttx_temp_pkg is
g_ok varchar2(2) := 'OK';
g_changed varchar2(21) :='CHANGED BY OTHER USER';
g_no_row varchar2(14) := 'NO ROW UPDATED';
procedure lock_row
(
x_message out nocopy varchar2,
p_id in number,
p_owner in varchar2,
p_object_name in varchar2,
p_subobject_name in varchar2,
p_object_id in number,
p_data_object_id in number,
p_object_type in varchar2,
p_created in date,
p_last_ddl_time in date,
p_timestamp in varchar2,
p_status in varchar2,
p_temporary in varchar2,
p_generated in varchar2,
p_secondary in varchar2
);
procedure update_row
(
x_message out nocopy varchar2,
p_id in number,
p_owner in varchar2,
p_object_name in varchar2,
p_subobject_name in varchar2,
p_object_id in number,
p_data_object_id in number,
p_object_type in varchar2,
p_created in date,
p_last_ddl_time in date,
p_timestamp in varchar2,
p_status in varchar2,
p_temporary in varchar2,
p_generated in varchar2,
p_secondary in varchar2
);
procedure delete_row
(
p_id in number,
x_message out nocopy varchar2
);
end ttx_temp_pkg;
/
create or replace package body ttx_temp_pkg is
procedure lock_row
(
x_message out nocopy varchar2,
p_id in number,
p_owner in varchar2,
p_object_name in varchar2,
p_subobject_name in varchar2,
p_object_id in number,
p_data_object_id in number,
p_object_type in varchar2,
p_created in date,
p_last_ddl_time in date,
p_timestamp in varchar2,
p_status in varchar2,
p_temporary in varchar2,
p_generated in varchar2,
p_secondary in varchar2
) is
cursor cur_ttx_temp is
select
id,
owner,
object_name,
subobject_name,
object_id,
data_object_id,
object_type,
created,
last_ddl_time,
timestamp,
status,
temporary,
generated,
secondary
from ttx_temp
where id = p_id
for update nowait;
ctt cur_ttx_temp%rowtype;
begin
open cur_ttx_temp;
fetch cur_ttx_temp into ctt;
if ((ctt.owner=p_owner) or
(ctt.owner is null and p_owner is null))
and ((ctt.object_name=p_object_name) or
(ctt.object_name is null and p_object_name is null))
and ((ctt.subobject_name=p_subobject_name) or
(ctt.subobject_name is null and p_subobject_name is null))
and ((ctt.object_id=p_object_id) or
(ctt.object_id is null and p_object_id is null))
and ((ctt.data_object_id=p_data_object_id) or
(ctt.data_object_id is null and p_data_object_id is null))
and ((ctt.object_type=p_object_type) or
(ctt.object_type is null and p_object_type is null))
and ((ctt.created=p_created) or
(ctt.created is null and p_created is null))
and ((ctt.last_ddl_time=p_last_ddl_time) or
(ctt.last_ddl_time is null and p_last_ddl_time is null))
and ((ctt.timestamp=p_timestamp) or
(ctt.timestamp is null and p_timestamp is null))
and ((ctt.status=p_status) or
(ctt.status is null and p_status is null))
and ((ctt.temporary=p_temporary) or
(ctt.temporary is null and p_temporary is null))
and ((ctt.generated=p_generated) or
(ctt.generated is null and p_generated is null))
and ((ctt.secondary=p_secondary) or
(ctt.secondary is null and p_secondary is null)) then
x_message := g_ok;
else
x_message := g_changed;
end if;
close cur_ttx_temp;
exception
when others then
x_message := substrb(sqlcode||'/'||sqlerrm,1,200);
if cur_ttx_temp%isopen then
close cur_ttx_temp;
end if;
end;
procedure update_row
(
x_message out nocopy varchar2,
p_id in number,
p_owner in varchar2,
p_object_name in varchar2,
p_subobject_name in varchar2,
p_object_id in number,
p_data_object_id in number,
p_object_type in varchar2,
p_created in date,
p_last_ddl_time in date,
p_timestamp in varchar2,
p_status in varchar2,
p_temporary in varchar2,
p_generated in varchar2,
p_secondary in varchar2
) is
begin
update ttx_temp
set
owner = p_owner,
object_name = p_object_name,
subobject_name = p_subobject_name,
object_id = p_object_id,
data_object_id = p_data_object_id,
object_type = p_object_type,
created = p_created,
last_ddl_time = p_last_ddl_time,
timestamp = p_timestamp,
status = p_status,
temporary = p_temporary,
generated = p_generated,
secondary = p_secondary
where id=p_id;
if sql%rowcount > 0 then
x_message := g_ok;
else
x_message := g_no_row;
end if;
exception
when others then
x_message := substrb(sqlcode||'/'||sqlerrm,1,200);
end;
procedure delete_row
(
p_id in number,
x_message out nocopy varchar2
) is
begin
delete ttx_temp
where id=p_id;
if sql%rowcount > 0 then
x_message := g_ok;
else
raise no_data_found;
end if;
exception
when others then
x_message := substrb(sqlcode||'/'||sqlerrm,1,200);
end;
end ttx_temp_pkg;
/
--環境準備
ttx@TTX>create table ttx_temp as select *
2 from dba_objects where rownum < 100;
Table created.
ttx@TTX>alter table ttx_temp add id number;
Table altered.
ttx@TTX>create sequence ttx_temp_s;
Sequence created.
ttx@TTX>update ttx_temp set id = ttx_temp_s.nextval;
99 rows updated.
ttx@TTX>commit;
Commit complete.
ttx@TTX>alter table ttx_temp modify id not null;
Table altered.
ttx@TTX>alter table ttx_temp add constraint ttx_temp_pk primary key(id);
Table altered.
ttx@TTX>show errros;
SP2-0158: unknown SHOW option "errros"
ttx@TTX>desc ttx_temp
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
ID NOT NULL NUMBER
ttx@TTX>column object_name format a30
ttx@TTX>column owner format a15
ttx@TTX>select owner,object_name from ttx_temp where id < 10;
OWNER OBJECT_NAME
--------------- ------------------------------
SYS ICOL$
SYS I_USER1
SYS CON$
SYS UNDO$
SYS C_COBJ#
SYS I_OBJ#
SYS PROXY_ROLE_DATA$
SYS I_IND1
SYS I_CDEF2
9 rows selected.
ttx@TTX>desc ttx_temp_pkg
PROCEDURE DELETE_ROW
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_ID NUMBER IN
X_MESSAGE VARCHAR2 OUT
PROCEDURE LOCK_ROW
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
X_MESSAGE VARCHAR2 OUT
P_ID NUMBER IN
P_OWNER VARCHAR2 IN
P_OBJECT_NAME VARCHAR2 IN
P_SUBOBJECT_NAME VARCHAR2 IN
P_OBJECT_ID NUMBER IN
P_DATA_OBJECT_ID NUMBER IN
P_OBJECT_TYPE VARCHAR2 IN
P_CREATED DATE IN
P_LAST_DDL_TIME DATE IN
P_TIMESTAMP VARCHAR2 IN
P_STATUS VARCHAR2 IN
P_TEMPORARY VARCHAR2 IN
P_GENERATED VARCHAR2 IN
P_SECONDARY VARCHAR2 IN
PROCEDURE UPDATE_ROW
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
X_MESSAGE VARCHAR2 OUT
P_ID NUMBER IN
P_OWNER VARCHAR2 IN
P_OBJECT_NAME VARCHAR2 IN
P_SUBOBJECT_NAME VARCHAR2 IN
P_OBJECT_ID NUMBER IN
P_DATA_OBJECT_ID NUMBER IN
P_OBJECT_TYPE VARCHAR2 IN
P_CREATED DATE IN
P_LAST_DDL_TIME DATE IN
P_TIMESTAMP VARCHAR2 IN
P_STATUS VARCHAR2 IN
P_TEMPORARY VARCHAR2 IN
P_GENERATED VARCHAR2 IN
P_SECONDARY VARCHAR2 IN
ttx@TTX>
--前臺操作用戶通過前臺界面查出需要的數據(select * from ttx_temp)，
--然后對對ID=1的這行數據的SUBOBJECT_NAME進行修改后提交更新時，
--程序應該類似于下面的方式調用，就可以保證數據更新不被丟失
代碼:
begin
--old.xxx 在調用時用具體的初始值替代,在Oracle Form和Delphi中都支持Old和New的模式,
--在JAVA中我不太清楚,應該是有辦法的
ttx_temp_pkg.lock_row
(
x_message => v_message,
p_id => id,
p_owner => old.owner,
p_object_name => old.object_name,
p_subobject_name => old.subobject_name,
p_object_id => old.object_id,
p_data_object_id => old.data_object_id,
p_object_type => old.object_type,
p_created => old.created,
p_last_ddl_time => old.last_ddl_time,
p_timestamp => old.timestamp,
p_status => old.status,
p_temporary => old.temporary,
p_generated => old.generated,
p_secondary => old.secondary
);
if v_message = 'OK' then
ttx_temp_pkg.update_row
(
x_message => v_message,
p_id => id,--用具體的值代替
p_owner => new.owner,
p_object_name => new.object_name,
p_subobject_name => new.subobject_name,
p_object_id => new.object_id,
p_data_object_id => new.data_object_id,
p_object_type => new.object_type,
p_created => new.created,
p_last_ddl_time => new.last_ddl_time,
p_timestamp => new.timestamp,
p_status => new.status,
p_temporary => new.temporary,
p_generated => new.generated,
p_secondary => new.secondary
);
if v_message = 'OK' then
--數據更新成功
else
--數據更新失敗
end if;
else
--鎖定行出錯，具體信息為:v_message
end if;
end;
--Oracle樂觀封鎖示例：
--前臺操作用戶通過前臺界面查出需要的數據(select t.rowid,t.* from ttx_temp t)，
--然后對對ID=1的這行數據的SUBOBJECT_NAME進行修改提交更新，
--應該使用的程序代碼類似為(Delphi可以自已產生，其余的不太清楚)：
代碼:
update ttx_temp t
set t.subobject_name=new.subobject_name
where t.rowid=_rowid--用具體的值代替
and t.id=old.id
and nvl(t.owner,'"$!')=nvl(old.owner,'"$!')
and nvl(t.object_name,'"$!')=nvl(old.object_name,'"$!')
and nvl(t.object_id,'"$!')=nvl(old.object_id,'"$!')
and nvl(t.data_object_id,'"$!')=nvl(old.data_object_id,'"$!')
and nvl(t.object_type,'"$!')=nvl(old.object_type,'"$!')
and nvl(t.created,'"$!')=nvl(old.created,'"$!')
and nvl(t.last_ddl_time,'"$!')=nvl(old.last_ddl_time,'"$!')
and nvl(t.timestamp,'"$!')=nvl(old.timestamp,'"$!')
and nvl(t.status,'"$!')=nvl(old.status,'"$!')
and nvl(t.temporary,'"$!')=nvl(old.temporary,'"$!')
and nvl(t.generated,'"$!')=nvl(old.generated,'"$!')
and nvl(t.secondary,'"$!')=nvl(old.secondary,'"$!');
--樂觀封鎖的代碼量相對來說少很多，但增大了丟失更新的風險。在實際應用中
--到底是使用悲觀封鎖還是樂觀封鎖，由開發人員來定。不過Oracle 11i版的ERP
--如此龐大復雜的系統都使用悲觀封鎖，沒有理由說明樂觀封鎖優于悲觀封鎖。
posted on 2007-04-25 20:42
???MengChuChen 閱讀(554)
評論(0) 編輯 收藏 所屬分類:
ORACLE