常用PLSQL子程式，plsql

常用PLSQL子程式，plsql

01.清空資源回收筒
begin
  EXECUTE IMMEDIATE 'purge recyclebin';
end;
/
02.截斷表
declare
begin
  execute immediate 'truncate table person_bak2';
end;
/
03.修改表列可以為空白
--修改時間：2014年9月1日
--修改內容：修改表BT_SALE_CONTRACT欄位TRANS_TIME允許為空白
--修改原因：銷售合約匯入時報TRANS_TIME為NULL不能插入
DECLARE
  VN_NULLABLE VARCHAR2(1);
BEGIN
  SELECT nullable
    INTO VN_NULLABLE
    FROM user_tab_columns
   WHERE table_name = 'BT_SALE_CONTRACT'
     AND column_name = 'TRANS_TIME';
  IF VN_NULLABLE = 'N' THEN
    EXECUTE IMMEDIATE ' ALTER TABLE BT_SALE_CONTRACT MODIFY TRANS_TIME DATE NULL ';
  END IF;
  COMMIT;
END;
/
04.修改列列長度(字元型，只允許變長，不允許變短)
--修改時間：2014年9月3日
--修改內容：修改表BT_BUY_DETAIL欄位PRODUCT_NAME的長度
--        修改表BT_BUY_FPRECORD 欄位PRODUCT_NAME的長度
--修改原因：匯入時報其欄位的長度不夠

--修改產品名稱欄位地,採購明細匯入報其欄位的長度不夠
alter table BT_BUY_DETAIL modify(PRODUCT_NAME  varchar2(500));
--修改產品名稱欄位,發票表匯入報其欄位的長度不夠
alter table BT_BUY_FPRECORD  modify(PRODUCT_NAME  varchar2(500));
commit;
05.建立表
--修改人：易小群
--修改內容：新增預投產品/返修產品庫，在招標採購時可供選擇
DECLARE
  VC_STR           VARCHAR2(5000);
  VN_COUNT         NUMBER;
BEGIN
  --查看現有系統是否有BT_PRODUCT_MODEL表
  SELECT COUNT(*)
    INTO VN_COUNT
    FROM USER_TABLES
   WHERE TABLE_NAME = 'BT_PRODUCT_MODEL';
  --如果沒有則新增表，如果有就不處理
  IF VN_COUNT < 1 THEN
    VC_STR := '  create table BT_PRODUCT_MODEL
(
  product_model_id NUMBER not null,
  product_code     VARCHAR2(30),
  product_name     VARCHAR2(30),
  product_type     VARCHAR2(30),
  product_desc     VARCHAR2(100),
  constraint PK_PRODUCT_MODEL_ID primary key (PRODUCT_MODEL_ID)
)';
    EXECUTE IMMEDIATE VC_STR;
  END IF;
END;
/
06.表列增加欄位
--修改時間:2014-9-16
--修改內容:銷售合約表增加合約標記
DECLARE
  VN_COUNT     NUMBER;
  VC_STR        VARCHAR2(1000);
BEGIN
  --查看該表中該欄位是否存在
  SELECT COUNT(*)
    INTO VN_COUNT
    FROM USER_TAB_COLUMNS
   WHERE TABLE_NAME = 'BT_SALE_CONTRACT' AND COLUMN_NAME = 'CONTRACTFLAG';
  IF VN_COUNT < 1 THEN
    VC_STR := ' ALTER TABLE BT_SALE_CONTRACT ADD CONTRACTFLAG VARCHAR2(30)';
    EXECUTE IMMEDIATE VC_STR;
  END IF;
END;
/
07.開啟資源回收筒
begin
  execute immediate 'alter session set recyclebin=on';
end;
/
08.建立序列
DECLARE
VC_STR VARCHAR2(5000);
VN_COUNT NUMBER;
BEGIN
  SELECT COUNT(*)
    INTO VN_COUNT
    FROM ALL_SEQUENCES
   WHERE SEQUENCE_NAME = 'AUDIT_SEQ';
  IF VN_COUNT < 1 THEN
    VC_STR := 'CREATE SEQUENCE audit_seq
    START WITH 1000
    INCREMENT BY 1
    NOMAXVALUE
    NOCYCLE NOCACHE;';
    EXECUTE IMMEDIATE VC_STR;
  END IF;
END;
/
09.解鎖使用者
SQL> SELECT OBJECT_NAME,S.SID, S.SERIAL#
  2    FROM GV$LOCKED_OBJECT L, DBA_OBJECTS O, GV$SESSION S
  3   WHERE L.OBJECT_ID　 = O.OBJECT_ID
  4     AND L.SESSION_ID = S.SID;

OBJECT_NAME
-----------------------------------------------------------------------

       SID    SERIAL#
---------- ----------
BT_USER
       132         37

SQL> alter system kill session '132,37';

系統已更改。

SQL> SELECT OBJECT_NAME,S.SID, S.SERIAL#
  2    FROM GV$LOCKED_OBJECT L, DBA_OBJECTS O, GV$SESSION S
  3   WHERE L.OBJECT_ID　 = O.OBJECT_ID
  4     AND L.SESSION_ID = S.SID;

未選定行

SQL>
SQL注意
各位在寫SQL指令碼的時候注意下面的情況：
1、SQL指令碼中單引號中嵌套單引號問題，如：需要在一個欄位中插入字串"ab'1'cd",不能寫成'ab'1'cd'，需要寫成'ab''1''cd'
2、SQL遇到字串中有取地址符&問題，需要修改成'||'&'||'                                例如：'abc&def'   需要修改成'abc'||'&'||'def'
或使用函數CHR(38)
SQL> select chr(38) from dual;

C
-
&

 

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。