dblink 定时器 触发器 存储过程中 CLOB字段错误的解决方案
create PUBLIC database link bjlink
CONNECT TO username IDENTIFIED BY password
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xx.xx)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = xxx)
)
)'
update student
set student.name = student1.name,
student.id = student1.id
(select student1.name,student1.id
from student,student1
where student.int_id = student1.int_id);
update student set (name,id )=
(select name ,id from (select student.rowid rd,student1.name,student1.id from student1,student where student1.int_id =student.int_id) tmp
where student.rowid=tmp.rd);
commit;
SQL:
update inventory set prod_id=
(
select prod_id from inventory_temp where inventory_temp.lot_no=inventory.lot_no
)
where exists
(
select lot_no from inventory_temp where inventory_temp.lot_no=inventory.lot_no
)
体会:
用一个表去更新另一个表
上边写法可以简写为:
update a
set a.name=(select b.name from a.id = b.id)
where exsits (select 1 from a.id = b.id);
如果更新的是多个字段:
UPDATE employees a
SET department_id =
(SELECT department_id
FROM departments
WHERE location_id = '2100'),
(salary, commission_pct) =
(SELECT 1.1*AVG(salary), 1.5*AVG(commission_pct)
FROM employees b
WHERE a.department_id = b.department_id)
WHERE department_id IN
(SELECT department_id
FROM departments
WHERE location_id = 2900
OR location_id = 2700);
-------------------------------------------
另外一个: http://blog.csdn.net/Bobwu/archive/2009/01/13/3768636.aspx
1.
declare
cursor t1 is select * from tablename;
begin
for rec in t1 loop
update tablename t set t.detail=rec.jieshao where t.objectid=rec.objid;
end loop;
end;
2.
update student set (name,id )=
(select name ,id from (select student.rowid rd,student1.name,student1.id from student1,student where student1.int_id =student.int_id) tmp
where student.rowid=tmp.rd);
commit;
3.
update test_a a set (a.name,a.age)=
(select b.name,b.age from test_b b where a.id = b.id) where exists
(select * from test_b c where c.id=a.id)
4.
UPDATE t_A SET Djrq=
(
SELECT djrq FROM t_B WHERE t_A.ID = T_B.ID
WHERE ROWNUM = 1
)
WHERE t_A.ID IN
(
SELECT ID FROM t_B WHERE jwh='XX村'
)
5.
update tbl1 a
set (a.col1, a.col2) = (select b.col1, b.col2
from tbl2 b
where a.key = b.key)
where a.key in(select key from tbl2)
保存备用
create or replace procedure sync_out_test_proc IS
--
CURSOR cursor_test IS
SELECT * FROM TEMP@REPL_23_DBLINK tabl WHERE tabl.CHANGETYPE = 'C';
--
v_currrow TEMP@REPL_23_DBLINK%ROWTYPE;
BEGIN
--*************************************************--
-- Author : hmily_telli
-- Created : 2010-3-2 10:40:11
-- Purpose : 测试将外网临时库中指定的数据同步到中心数据库
--*************************************************--
--增加。标志位为'C'的数据
INSERT INTO TEMP
(ID, NAME,T_CLOB)
SELECT TEST.ID, TEST.NAME,T_CLOB
FROM TEMP@REPL_23_DBLINK TEST;
--修改。标志位为'U'的数据
update student set (name,id )=
(select name ,id from (select student.rowid rd,student1.name,student1.id
from student1,student where student1.int_id =student.int_id) tmp
where student.rowid=tmp.rd);
--删除。标志位为'D'的数据
游标!!1
游标名字test Test表名%rowtype(定义test的没一行的类型)
定义游标 cursor test is select *from test;
For test in(select * from test )
open cursor_test;
loop
fetch cursor_test into v_currrow;
exit when cursor_test%notfound;
insert INTO temp
(ID, NAME, t_clob, creattime, modifytime, changetype, EXCHANGE)
VALUES
(v_currrow.ID,v_currrow.NAME,v_currrow.t_clob,v_currrow.creattime,v_currrow.modifytime,v_currrow.changetype,v_currrow.EXCHANGE);
end loop;
close cursor_test;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee Number Not Found!');
end sync_out_test_proc
一个完整的存储过程
create or replace procedure SYNC_OUT_YWPZ_PROC IS
--*************************************************--
-- Author : zj
-- Created : 2010-3-3 10:40:11
-- Purpose : 测试将外网临时库中指定的数据同步到中心数据库
--*************************************************--
--定义row变量
v_currrow t_ins_ywpz%ROWTYPE;
--异常代号及内容
v_errorcode number;
v_errortext varchar2(400);
--临时表字符
--g_temp_table varchar2(1000);
BEGIN
/****************************/
/*含有CLOB等特殊字段解决方案
/****************************/
--创建事务级临时表
-- g_temp_table:='create global temporary table g_clob_temp as select * from t_ins_ywpz ON COMMIT DELETE ROWS';
-- EXECUTE IMMEDIATE g_temp_tableone;
--利用database link把远程数据先插入到临时表中
--(注意:INSERT后先不要commit,否则commit后临时表中数据就会丢失)
insert into g_clob_ywpz
select *
from t_ins_ywpz@REPL_163_DBLINK tabl
WHERE tabl.CHANGETYPE = 'C'
AND tabl.EXCHANGE = '-1';
-- COMMIT;
--新增。标志位为'C'的数据
--定义新增游标
for v_currrow in (select * from g_clob_ywpz) loop
begin
insert into t_ins_ywpz
(id,
ywbh,
type,
dataxml,
createtime,
modifytime,
changetype,
exchange)
values
(v_currrow.id,
v_currrow.ywbh,
v_currrow.type,
v_currrow.dataxml,
v_currrow.createtime,
v_currrow.modifytime,
v_currrow.changetype,
v_currrow.exchange);
--插入操作成功后,修改标识符1
UPDATE t_ins_ywpz SET EXCHANGE = '1' WHERE ID = v_currrow.ID;
exception
when OTHERS THEN
--执行异常插入操作
v_errorcode := SQLCODE;
v_errortext := SUBSTR(SQLERRM, 1, 400);
--插入异常记录
insert INTO t_sync_excep_log
(t_name,
t_excep_content,
t_ctime,
t_sync_dir,
t_excep_code,
t_unique_flag,
t_oper_type)
VALUES
('t_ins_ywpz',
v_errortext,
sysdate,
'website@163->website@163',
v_errorcode,
v_currrow.ID,
v_currrow.changetype);
--插入操作失败,修改标识符0
UPDATE t_ins_ywpz SET EXCHANGE = '0' WHERE ID = v_currrow.ID;
end;
end loop;
dbms_output.put_line('-->>cursor_c done!');
--利用database link把远程数据先插入到临时表中
--(注意:INSERT后先不要commit,否则commit后临时表中数据就会丢失)
--insert into g_clob_temp select * from TEMP@REPL_163_DBLINK tabl
-- WHERE (tabl.CHANGETYPE = 'U' OR tabl.CHANGETYPE = 'D')
-- AND tabl.EXCHANGE = '-1';
-- COMMIT;
--修改、删除。标志位为'U'或'D'的数据
--删除操作执行逻辑删除
--定义修改、删除游标
for v_currrow in (select *
from t_ins_ywpz tabl
WHERE tabl.CHANGETYPE = 'C'
AND tabl.EXCHANGE = '-1') loop
begin
--执行修改、删除操作
update t_ins_ywpz@REPL_163_DBLINK
set id = v_currrow.id,
ywbh = v_currrow.ywbh,
type = v_currrow.type,
dataxml = v_currrow.dataxml,
createtime = v_currrow.createtime,
modifytime = v_currrow.modifytime,
changetype = v_currrow.changetype,
exchange = v_currrow.exchange
where id = v_currrow.id;
--更新操作成功后,修改标识符1
UPDATE t_ins_ywpz SET EXCHANGE = '1' WHERE ID = v_currrow.ID;
exception
when OTHERS THEN
--执行异常插入操作
v_errorcode := SQLCODE;
v_errortext := SUBSTR(SQLERRM, 1, 400);
--插入异常记录
insert INTO t_sync_excep_log
(t_name,
t_excep_content,
t_ctime,
t_sync_dir,
t_excep_code,
t_unique_flag,
t_oper_type)
VALUES
('t_ins_ywpz',
v_errortext,
sysdate,
'website@163->website@163',
v_errorcode,
v_currrow.ID,
v_currrow.changetype);
--更新操作失败,修改标识符0
UPDATE t_ins_ywpz SET EXCHANGE = '0' WHERE ID = v_currrow.ID;
end;
end loop;
dbms_output.put_line('-->>cursor_ud done!');
end SYNC_OUT_YWPZ_PROC;
创建测试表
SQL> create table a(a date);
表已创建。
创建一个自定义过程
SQL> create or replace procedure test as
2 begin
3 insert into a values(sysdate);
4 end;
5 /
过程已创建。
创建JOB
variable v_jobnum number;
exec dbms_job.submit(:v_jobnum,'your_procedure;',trunc(sysdate)+9/24,'trunc(sysdate)+1');
commit;
这是每天9点执行!
variable v_jobnum number;
exec dbms_job.submit(:v_jobnum,'your_procedure;', sysdate,'sysdate+1/1440');
commit;
此是每分钟执行一次!
variable v_jobnum number;
exec dbms_job.submit(:v_jobnum,' pck_swap.p_clear_swapdata;',trunc(sysdate)+1/24,'trunc(sysdate)+1');
commit;
删除JOB
SQL> begin
2 dbms_job.remove(:job1);
3 end;
4 /
PL/SQL 过程已成功完成。
ID NVARCHAR2(20) not null,
NAME NVARCHAR2(100),
T_CLOB CLOB,
CREATTIME DATE default sysdate,
MODIFYTIME DATE default sysdate,
CHANGETYPE VARCHAR2(2 CHAR) default 'C',
EXCHANGE VARCHAR2(2 CHAR) default '-1'
物级零时表的创建方法
--创建事务级临时表
--g_temp_table:='create global temporary table g_clob_temp as select * from TEMP ON COMMIT DELETE ROWS'; TEMP 是 基表
EXECUTE IMMEDIATE g_temp_table;
Create global temporary table
(
字段1 varchar2()
…….
……
) ON COMMIT DELETE(trazication ) ROWS
create global temporary table 临时表名 on commit preserve|delete rows
用preserve时就是SESSION级的临时表,用delete就是TRANSACTION级的临时表
汪雄才 14:23:29
创建SESSION级的临时表
解决clob字段
- create global temporary table photo_temp as select * from photo
create global temporary table photo_temp as select * from photo ;
2.用database link导入远程数据到临时表
Sql代码
- insert into photo_temp select * from photo@photo_link;--不要commit;否则临时表中数据消失 insert into photo_temp select * from photo@photo_link;--不要commit;否则临时表中数据消失
3.把临时表数据插入到永久表中:
Sql代码 insert into photo select * from photo_temp; commit;
更新 表ietm 的OK 列使其 所有值为哈哈!
如果想把这列都赋值为‘哈哈’那就用: update item set ok='哈哈' 如果想在这列后追加一个‘哈哈’那就用: update item set ok=ok+'哈哈'
游标 使用和定义
CURSOR business_c_cur
IS
SELECT *
FROM t_webhall_business_info@REPL_241_DBLINK tabl
WHERE tabl.changetype = 'C'
AND tabl.exchange = '-1';
OPEN business_c_cur;
LOOP
FETCH business_c_cur
INTO v_currrow;
EXIT WHEN business_c_cur%NOTFOUND;
BEGIN
Oracle 授权语句
--select * from dba_users; 查询数据库中的所有用户
--alter user TEST_SELECT account lock; 锁住用户
--alter user TEST_SELECT account unlock; 给用户解锁
--create user xujin identified by xujin; 建立用户
--grant create tablespace to xujin; 授权
--grant select on tabel1 to xujin; 授权查询
--grant update on table1 to xujin;
--grant execute on procedure1 to xujin 授权存储过程
--grant update on table1 to xujin with grant option; 授权更新权限转移给xujin用户,许进用户可以继续授权;
--收回权限
--revoke select on table1 from xujin1; 收回查询select表的权限;
--revoke all on table1 from xujin;
/*grant connect to xujin;
revoke connect from xujin
grant select on xezf.cfg_alarm to xujin;
revoke select on xezf.cfg_alarm from xujin;*/
--select table_name,privilege from dba_tab_privs where grantee='xujin' 查询一个用户拥有的对象权限
--select * from dba_sys_privs where grantee='xujin' 查询一个用户拥有的系统权限
--select * from session_privs --当钱会话有效的系统权限
--角色
--create role xujin1;--建立xujin1角色
--grant insert on xezf.cfg_alarm to xujin1; 将插入表的信息
--revoke insert on xezf.cfg_alarm from xujin1; 收回xujin1角色的权限
--grant xujin1 to xujin ; 将角色的权限授权给xujin;
-- create role xujin2;
--grant xujin1 to xujin2; 将角色xujin1授权给xujin2;
--alter user xujin default xujin1,xujin2; 修改用户默认角色
-- DROP ROLE xujin1;删除角色1;
--select * from role_sys_privs where role=xujin1;
--查看许进1角色下有什么系统权限;
--select granted_role,admin_option from role_role_privs where role='xujin2';
--查看xujin1角色下面有什么角色权限
--select * from role_sys_privs where role='xujin2';
--select table_name,privilege from role_tab_privs where role='xujin1';
--select * from dba_role_privs where grantee='xujin' --查看用户下面有多少个角色;
- 1楼cjr152336611431小时前
- 你对dblink的研究还是挺深入的啊