--修改表,添加列
alter table F_confirm_car
add (ddsj_num INTEGER );
--锁表查询SQL
SELECT object_name, machine, s.sid, s.serial#
FROM gv$locked_object l, dba_objects o, gv$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid;
--alter system kill session 'sid, serial#';
ALTER system kill session '23, 1647';
p_update_club_card_procedure
-------------存储过程列子------
CREATE OR REPLACE PROCEDURE P_update_club_card_procedure IS
CURSOR cur IS select point as points ,username as v_created , cardid as cardid, id as ids ,vaddtype as v_add_type ,crttime as v_date from CLUB_TT_IMP_POINT_DETAILS t where t.flage='0';
BEGIN
FOR cur_result in cur LOOP
BEGIN
--1,更新积分手工导入临时表
update CLUB_TT_IMP_POINT_DETAILS set flage='1' , dealtime=sysdate where id=cur_result.ids;
--2 调用存储过程执行更新
p_club_add_score(cur_result.cardid,to_char(cur_result.v_date,'yyyyMMdd'),cur_result.v_add_type,cur_result.points,cur_result.v_created);
END;
END LOOP;
COMMIT;
END;
--触发器例子----
CREATE OR REPLACE TRIGGER SAV_BILL_DUTY
AFTER INSERT OR UPDATE ON TAS_BILL_DUTY
FOR EACH ROW
BEGIN
INSERT INTO HIS_TAS_BILL_DUTY
(VDUTY_COZ_ID, VBILL_ID, VAFFIRMANT, VAFM_DATE, VISSAFETY, VISAPP, VISCONFIRM, VCORROBORATOR, VCONFIRM_DATE, VNOTES, VCREATED, DCRT_DATE, VUPDATED, DUP_DATE)
VALUES
(:NEW.VDUTY_COZ_ID, :NEW.VBILL_ID, :NEW.VAFFIRMANT, :NEW.VAFM_DATE, :NEW.VISSAFETY, :NEW.VISAPP, :NEW.VISCONFIRM, :NEW.VCORROBORATOR, :NEW.VCONFIRM_DATE, :NEW.VNOTES, :NEW.VCREATED, :NEW.DCRT_DATE, :NEW.VUPDATED, :NEW.DUP_DATE
);
END;
oracle常用的几个例子
最新推荐文章于 2021-04-03 00:53:11 发布