oracle常用的几个例子

--修改表，添加列
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;