ITPub博客

首页 > 数据库 > Oracle > 2011 常用 的SQL

2011 常用 的SQL

Oracle 作者:tonyni666 时间:2011-07-16 11:58:34 0 删除 编辑

--建立数据链接
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;

<!-- 正文结束 -->

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23185096/viewspace-1117028/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2010-01-14