Oracle数据库常用操作 2011.3.15

1.创建DB Link

 

create database link link_name

connect to username identified by password

using sqlnet_string;

 

 

2.事务(设置SAVEPOINT)

begin

insert into t_tablename values(...)

savepoint A;

 

insert into t_tablename values(...)

savepoint B;

 

insert into t_tablename values(...)

savepoint C;

 

ROLLBACK TO B;

COMMIT;

 

第三条INSERT语句不会被执行。

 

3.遍历游标

CREATE OR REPLACE PROCEDURE P_TEMP_INSERTLOGDATA

 AS

  CURSOR c_UserActiveLog IS
  SELECT * FROM T_VVV_ACTIVE_USEROPERATELOG;
 
  v_UserActiveLog c_UserActiveLog%ROWTYPE;

BEGIN

    FOR v_UserActiveLog IN c_UserActiveLog LOOP
    INSERT INTO T_VVV_ACTIVE_USEROPTTEMP
      (SEQUENCEID, OP_TIME, LOG_LEVEL, MODULE_NAME, USERNO, ACTIVITY, DESCRIPTION, OP_RESULT, PARTCOL_DATE)
    VALUES
      (v_UserActiveLog.SEQUENCEID,
       v_UserActiveLog.OP_TIME,
       v_UserActiveLog.LOG_LEVEL,
       v_UserActiveLog.MODULE_NAME,
       v_UserActiveLog.USERNO,
       v_UserActiveLog.ACTIVITY,
       v_UserActiveLog.DESCRIPTION,
       v_UserActiveLog.OP_RESULT,
       SUBSTR(v_UserActiveLog.USERNO, -3, 2));
 
  END LOOP;
  COMMIT;

END P_TEMP_INSERTLOGDATA;