一些ORACLE脚本

基本都是网上摘录来的，可能有自己的整理，谢谢作者
----------------------------------------------------------------SEPARATE-LINE----------------------------------------------------------------

强制删除Oracle当前用户

SELECT 'alter system kill session '''||sid||','||serial#||''';' FROM v$session WHERE username='USER';

这样得到脚本，然后保存成文件执行，然后再drop user ....

引用

模仿Java中String类的endsWith方法

CREATE OR REPLACE FUNCTION F_ENDSWITH(SRC    IN VARCHAR2,
                                      SUFFIX IN VARCHAR2 DEFAULT '00')
  RETURN BOOLEAN IS
  RESULT         BOOLEAN;
  N_SUFFIX_COUNT NUMBER;
  N_SRC_COUNT    NUMBER;
  V_STR_TMP      VARCHAR2(200);
BEGIN
  N_SUFFIX_COUNT := LENGTH(SUFFIX);
  N_SRC_COUNT    := LENGTH(SRC);
  V_STR_TMP      := SUBSTR(SRC,
                           N_SRC_COUNT - N_SUFFIX_COUNT + 1,
                           N_SUFFIX_COUNT);

  IF (V_STR_TMP = SUFFIX) THEN
    RESULT := TRUE;
  ELSE
    RESULT := FALSE;
  END IF;
  RETURN(RESULT);
END F_ENDSWITH;

引用

模仿Java中String类的startsWith方法

CREATE OR REPLACE FUNCTION F_STARTSWITH(SRC    IN VARCHAR2,
                                        PREFIX IN VARCHAR2) RETURN BOOLEAN IS
  RESULT         BOOLEAN;
  N_PREFIX_COUNT NUMBER;
  V_STR_TMP      VARCHAR2(200);
BEGIN
  N_PREFIX_COUNT := LENGTH(PREFIX);
  V_STR_TMP      := SUBSTR(SRC, 0, N_PREFIX_COUNT);

  IF (V_STR_TMP = PREFIX) THEN
    RESULT := TRUE;
  ELSE
    RESULT := FALSE;
  END IF;
  RETURN(RESULT);
END F_STARTSWITH;

引用

校验传入的字符串是否为合法的日期表示，或者能否转化为日期，还有待完善

CREATE OR REPLACE FUNCTION F_ISDATE(YYYYMMDD IN VARCHAR2,
                                    FORMAT   IN VARCHAR2 DEFAULT 'yyyymmdd')
  RETURN BOOLEAN IS
  FLAG     BOOLEAN DEFAULT FALSE;
  TMP_DATE VARCHAR2(10);
  YYYY     INT;
  MM       INT;
  DD       INT;
BEGIN
  BEGIN
    TMP_DATE := TRIM(YYYYMMDD);
    YYYY     := TO_NUMBER(SUBSTR(TMP_DATE, 1, 4));
    MM       := TO_NUMBER(SUBSTR(TMP_DATE, 5, 2));
    DD       := TO_NUMBER(SUBSTR(TMP_DATE, 7, 2));
  
    IF (MM >= 1 AND MM <= 12 AND DD >= 1 AND DD <= 31) THEN
      IF (MM = 4 OR MM = 6 OR MM = 9 OR MM = 11) THEN
        IF (DD <= 30) THEN
          FLAG := TRUE;
        END IF;
      ELSIF (MM = 2) THEN
        IF (MOD(YYYY, 100) <> 0 AND MOD(YYYY, 4) = 0 OR MOD(YYYY, 400) = 0) THEN
          IF (DD <= 29) THEN
            FLAG := TRUE;
          END IF;
        ELSIF (DD <= 28) THEN
          FLAG := TRUE;
        END IF;
      ELSE
        FLAG := TRUE;
      END IF;
    END IF;
  
  EXCEPTION
    WHEN OTHERS THEN
      FLAG := FALSE;
  END;
  RETURN(FLAG);
END F_ISDATE;

引用

校验传入的字符串是否为合法的身份证号码，包括15位和18位，还有待完善

'T'表示是符合校验规则身份证，'F'表示错误的号码，'NR'表示是不符合18位校验规则的

CREATE OR REPLACE FUNCTION F_ISIDCARD(AAC002       IN VARCHAR2, -- 传入的号码
                                      VALIDATOR_ON IN NUMBER DEFAULT 0) -- 是否开启18位校验器
 RETURN VARCHAR2 IS
  RESULT VARCHAR2(2);

  TYPE W IS VARRAY(18) OF INTEGER;
  TYPE A IS VARRAY(11) OF VARCHAR2(1);

  NUM_LEN INT;
  DOB     VARCHAR2(8);
  F17     VARCHAR2(17);
  I18     VARCHAR2(18);

  RW W;
  RA A;

  NUM_SUM INTEGER DEFAULT 0;

BEGIN

  NUM_LEN := LENGTH(AAC002);
  IF (NUM_LEN = 15 OR NUM_LEN = 18) THEN
  
    RW := W(7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2, 1);
    RA := A('1', '0', 'X', '9', '8', '7', '6', '5', '4', '3', '2');
  
    IF NUM_LEN = 15 THEN
      F17 := SUBSTR(AAC002, 1, 6) || '19' || SUBSTR(AAC002, 7, 9);
    ELSE
      F17 := SUBSTR(AAC002, 1, 17);
    END IF;
  
    DOB := SUBSTR(F17, 7, 8);
  
    IF F_ISDATE(DOB) AND VALIDATOR_ON = 0 THEN
      -- 如果出生日期正确，而且不进行18位校验
      RESULT := 'T';
    ELSIF F_ISDATE(DOB) AND VALIDATOR_ON = 1 THEN
      -- 如果出生日期正确，而且进行18位校验
      FOR I IN 1 .. 17 LOOP
        NUM_SUM := NUM_SUM + TO_NUMBER(SUBSTR(F17, I, 1)) * RW(I);
      END LOOP;
      I18 := F17 || RA(MOD(NUM_SUM, 11) + 1);
      IF NUM_LEN = 18 AND UPPER(AAC002) = I18 THEN
        RESULT := 'T';
      ELSE
        RESULT := 'NR';
      END IF;
    ELSE
      RESULT := 'F';
    END IF;
  ELSE
    RESULT := 'F';
  END IF;
  RETURN(RESULT);
END F_ISIDCARD;

引用

查看oracle中锁住的对象，并杀之

SELECT B.SID,
       B.MACHINE,
       C.OBJECT_NAME,
       A.ORACLE_USERNAME,
       A.LOCKED_MODE,
       B.OSUSER,
       'alter system kill session ''' || B.SID || ',' || B.SERIAL# || ''';'
  FROM V$LOCKED_OBJECT A, V$SESSION B, ALL_OBJECTS C
 WHERE A.SESSION_ID = B.SID
   AND A.OBJECT_ID = C.OBJECT_ID;