--建立数据链接
create public database link
BIZ_DBLINK connect to nstcsa identified by "11111111" using 'jinjiang';

--建菜单数据
insert into wfpage (pageno,pagename,appno,rspmode,url,caption)
select ltrim(rtrim(a.appno||a.actionid)) as  pageno ,a.actionName,a.appno as appno ,0,'/'||a.appno||'/cs',a.actionName from nsaction a
where  not exists(select * from wfpage d where
 1=1
 and d.appno=a.appno
 and to_number(substr(d.pageno,length(a.appno)+1,4)) = a.actionid);
 

--在delphi中用下面的命令启动了一个oracle的imp命令
winexec('imp system/oracle file=(e:aaa.dmp) fromuser=newbj touser=newbj ignore=y',SW_Normal);

--检查死锁

SELECT C.SID, C.SERIAL#,A.OWNER,A.OBJECT_NAME, B.XIDUSN, B.XIDSLOT,B.XIDSQN,B.SESSION_ID,B.ORACLE_USERNAME,B.OS_USER_NAME,
B.PROCESS,B.LOCKED_MODE,C.MACHINE,C.STATUS, C.SERVER, C.PROGRAM FROM ALL_OBJECTS A,
V$LOCKED_OBJECT B,SYS.GV_$SESSION C WHERE ( A.OBJECT_ID = B.OBJECT_ID ) AND (B.PROCESS = C.PROCESS )
ORDER BY 1,2

alter system kill session 'SID,SERIAL#'

--创建表 、加触发器生成id值

-------------------------------------------------begin
create table NSMAKESHEETCONFIG
(
  ID     INTEGER not null,
  JKM    VARCHAR2(24),
  JNO    VARCHAR2(32),
  JCASH  VARCHAR2(18),
  DKM    VARCHAR2(24),
  DNO    VARCHAR2(32),
  DCASH  VARCHAR2(18),
  MEMOS  VARCHAR2(60),
  REMAIN NUMBER(15,2)
);

-- Create/Recreate primary, unique and foreign key constraints
alter table NSMAKESHEETCONFIG
  add constraint ID_PK primary key (ID)
  using index;

-- Create sequence
create sequence NSMAKESHEETCONFIG_SEQ
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;

CREATE OR REPLACE TRIGGER NsMakeSheetConfig_TR
BEFORE INSERT ON NsMakeSheetConfig
FOR EACH ROW
DECLARE TEMP_NO INT;
 BEGIN
  SELECT NSMAKESHEETCONFIG_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
  SELECT NSMAKESHEETCONFIG_SEQ.CURRVAL INTO GLOBALPKG.IDENTITY FROM DUAL;
END;
---------------------------------------------------end

--异常

  RAISE_APPLICATION_ERROR(-20001, RetMsg);
 

     FOR REC IN (SELECT CAST(PARAVALUE AS INT) TEMP_ALIAS_0
                  FROM CNTPARA
                 WHERE PARAID = 'S001'
                   AND BRNO = NS_ST_TransVoucherDtl.BRNO) LOOP
      NS_ST_TransVoucherDtl.VGMODE := REC.TEMP_ALIAS_0;
    END LOOP;

--动态游标

 TYPE REF_CodeCur IS REF CURSOR;
 CodeCur REF_CodeCur;

 OPEN CodeCur FOR SELECT 1;
 WHILE  1=1
 LOOP
  --逐个修改--
 BEGIN
  FETCH  CodeCur INTO Km, KmPath;
  IF CodeCur%notFOUND THEN
   EXIT;
  END IF;
  ......
 END;

 END LOOP;
 CLOSE CodeCur;

--打印==================================================
  DBMS_OUTPUT.PUT_LINE('声明游标');

--清临时表

EXECUTE IMMEDIATE ' DROP TABLE teBook_ADV_101 ' ;
EXECUTE IMMEDIATE 'TRUNCATE TABLE T_ADV_59';

--未找到数据

BEGIN
  SELECT 1
      INTO ASSIGNMENTVARIABLE0
      FROM DUAL
     WHERE not EXISTS
     (SELECT * FROM ayear WHERE yr = Ns_Acnt_SaveVoucher.yr);
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      ASSIGNMENTVARIABLE0 := 0;
END;