多谢捧场,请问sql中有能储存查询结果的对象之类的吗, select product_id,count(product_id) as productCount from order_product where order_id = temporderId group by product_Id;这条语句返回多条记录,我想针对每条记录一一处理,该怎么写呢
想对记录进行一一处理可以用游标循环 for cur in (select ...)loop ..... end loop;表结构,关系,及相关业务描述得不够清楚,不理解
那可以用For循環處理麼?FOR I IN ( select product_id,count(product_id) as productCount from order_product where order_id = temporderId group by product_Id) LOOP --对每条记录一一处理 END LOOP;
CREATE OR REPLACE FUNCTION UpdateNode(p_code in varchar2) return varchar2 is v_result varchar2(10); v_count number; v_superid varchar2(20):='000001101'; begin --取父节点编号 select superid into v_superid from test_func where test_func.code=v_superid; --DBMS_OUTPUT.PUT_LINE(v_superid); --查询父节点的子节点数 select count(b.superid) into v_count from test_func b where superId =v_superid; DBMS_OUTPUT.PUT_LINE(v_count); --如果当前只有一条记录的话,删除节点同时变为非子节点 if v_result=1 then update test_func set isref =1 WHERE code=v_superid; end if; --删除你要删除的节点 delete from test_func where code=p_code; COMMIT; v_result := 1; exception when NO_DATA_FOUND then v_result := -1; when others then DBMS_OUTPUT.PUT_LINE('在FN_GET_TAX_GB过程中出错!'||v_result); return v_result; end UpdateNode; 给你个例子,调试工具不会用的话,就用DBMS_OUT_PUT()
多谢楼上各位了,我又整了下执行后 toad死了create or replace procedure BACK_PRODUCT_ORDER( timediff in product_order.id%type )as temporderId product_order.id%type; cursor orderIds is select ID from product_order where state = 1 and 1<(select floor(to_number(sysdate-order_time)) as spanDays from dual) and rownum = 1; begin open orderIds; if orderIds%notfound then return; end if; loop fetch orderIds into temporderId; update product_order set state=4 where id=temporderId; update TICKET_INFO set state = 1 where order_id = temporderId; for i in(select product_id,count(product_id) as productCount from order_product where order_id = temporderId group by product_Id) loop update product set quantity = quantity + i.productCount where id=i.product_id; end loop; end loop; close orderIds; end;
create or replace procedure BACK_PRODUCT_ORDER( timediff in product_order.id%type )as temporderId product_order.id%type; cursor orderIds is select ID from product_order where state = 1 and 1<floor(to_number(sysdate-order_time)) and rownum = 1; begin open orderIds; FETCH ... INTO .... if orderIds%notfound then return; end if; loop fetch orderIds into temporderId; update product_order set state=4 where id=temporderId; update TICKET_INFO set state = 1 where order_id = temporderId; for i in(select product_id,count(product_id) as productCount from order_product where order_id = temporderId group by product_Id) loop update product set quantity = quantity + i.productCount where id=i.product_id; end loop; end loop; close orderIds; end;
多谢指正现在要job定时执行这个过程,时间的设置不明白,我想每天凌晨12点执行一次。该怎么设置呢DECLARE X NUMBER; BEGIN SYS.DBMS_JOB.SUBMIT ( job => X ,what => 'Back_Order_Product;' ,next_date => to_date('05-03-2010 01:00:00','dd/mm/yyyy hh24:mi:ss') ,interval => 'trunc(sysdate)+1+1/24' ,no_parse => TRUE ); SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x)); END;
2010/3/5凌晨12点: '05-03-2010 00:00:00' interval要改。。DECLARE X NUMBER; BEGIN SYS.DBMS_JOB.SUBMIT ( job => X ,what => 'Back_Order_Product;' ,next_date => to_date('05-03-2010 00:00:00','dd/mm/yyyy hh24:mi:ss') ,interval => 'trunc(sysdate)+1' ,no_parse => TRUE ); SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x)); END;
select product_id,count(product_id) as productCount from order_product where order_id = temporderId group by product_Id;这条语句返回多条记录,我想针对每条记录一一处理,该怎么写呢
for cur in (select ...)loop
.....
end loop;表结构,关系,及相关业务描述得不够清楚,不理解
select product_id,count(product_id) as productCount from order_product where order_id = temporderId group by product_Id)
LOOP
--对每条记录一一处理
END LOOP;
订单表ORDER:ORDER_ID (1)
定的产品表ORDER_PRODUCT:ORDER_ID,PRODUCT_ID (2)
产品表PRODUCT:product_id(3)
票:TICKETINFO:PRODUCT_ID,ORDER_ID(4)
1 对 多个 3 ,3 对多个 4
且 表 (2) 中 一个ORDER_ID 会对应多个 PRODUCT_ID,且order_id 与 product_id 组合不唯一。于是要按product_id 分组统计 每个ORDER中 同一产品的数量
2。将在一定时间内未处理的product写入临时表内。
3。用循环读临时表,释放产品数量及unlock product
4. clear temp table.
1:查询的时候要用变量接收,再去判断变量的值,然后继续其他流程
2:游标更新语句,加锁和不加锁区分,
建议你一步一步来,用PL/SQL DEV调试,分布实现,对于初学的话,是比较稳妥的
return varchar2 is
v_result varchar2(10);
v_count number;
v_superid varchar2(20):='000001101';
begin
--取父节点编号
select superid
into v_superid
from test_func
where test_func.code=v_superid;
--DBMS_OUTPUT.PUT_LINE(v_superid);
--查询父节点的子节点数
select count(b.superid)
into v_count
from test_func b
where superId =v_superid;
DBMS_OUTPUT.PUT_LINE(v_count);
--如果当前只有一条记录的话,删除节点同时变为非子节点
if v_result=1 then
update test_func set isref =1 WHERE code=v_superid;
end if;
--删除你要删除的节点
delete from test_func where code=p_code;
COMMIT;
v_result := 1;
exception
when NO_DATA_FOUND then
v_result := -1;
when others then
DBMS_OUTPUT.PUT_LINE('在FN_GET_TAX_GB过程中出错!'||v_result);
return v_result;
end UpdateNode;
给你个例子,调试工具不会用的话,就用DBMS_OUT_PUT()
timediff in product_order.id%type
)as
temporderId product_order.id%type;
cursor orderIds is
select ID
from product_order where state = 1 and 1<(select floor(to_number(sysdate-order_time)) as spanDays from dual) and rownum = 1;
begin
open orderIds;
if orderIds%notfound then
return;
end if;
loop
fetch orderIds into temporderId;
update product_order set state=4 where id=temporderId;
update TICKET_INFO set state = 1 where order_id = temporderId;
for i in(select product_id,count(product_id) as productCount from order_product where order_id = temporderId group by product_Id)
loop
update product set quantity = quantity + i.productCount where id=i.product_id;
end loop;
end loop;
close orderIds;
end;
timediff in product_order.id%type
)as
temporderId product_order.id%type;
cursor orderIds is
select ID
from product_order
where state = 1 and 1<floor(to_number(sysdate-order_time)) and rownum = 1;
begin
open orderIds;
FETCH ... INTO ....
if orderIds%notfound then
return;
end if;
loop
fetch orderIds into temporderId;
update product_order set state=4 where id=temporderId;
update TICKET_INFO set state = 1 where order_id = temporderId;
for i in(select product_id,count(product_id) as productCount from order_product where order_id = temporderId group by product_Id)
loop
update product set quantity = quantity + i.productCount where id=i.product_id;
end loop;
end loop;
close orderIds;
end;
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'Back_Order_Product;'
,next_date => to_date('05-03-2010 01:00:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'trunc(sysdate)+1+1/24'
,no_parse => TRUE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
END;
interval要改。。DECLARE X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'Back_Order_Product;'
,next_date => to_date('05-03-2010 00:00:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'trunc(sysdate)+1'
,no_parse => TRUE ); SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x)); END;
怎么改啊。。帮帮我吧..最好把改好的帖出来。。那个next_date是设置什么呀,怎么又个 2010、05,03啊
改了呀。。
DECLARE
X NUMBER;BEGIN
SYS.DBMS_JOB.SUBMIT
( job=> X
,what=>'Back_Order_Product;'
,next_date=> to_date('05-03-2010 00:00:00','dd/mm/yyyy hh24:mi:ss')
,interval=>'trunc(sysdate)+1'
,no_parse=> TRUE ); SYS.DBMS_OUTPUT.PUT_LINE('Job Number is:'|| to_char(x));END;
Next_date是下次執行Job的時間,按'dd/mm/yyyy'是指2010年3月5日就是明天啊,今晚到12點就是2010年3月5日零點了。。 -_-!!
可是设置个next_date 干嘛啊。只要设置几点就可以了吧。
如果这个就是指执行时间,那明天执行完 就不执行了??
貌似是个很弱智的问题,嘿嘿,告诉我咯,猫猫...
每次执行的时候,通过interval里的参数确定下一次执行的时间
其实那个存储过程 同事给写好了,我就只弄个job测试测试了,哎...得好好学习oracle了...