2010年9月30日 星期四

我與小光

影像174

張貼者： 於 沒有留言:

2010年9月28日 星期二

SQL SERVER Trigger +cursor 及參考資料

SQL SERVER Trigger裡使用CURSOR查詢資料

參考URL: http://www.dotblogs.com.tw/jain/archive/2010/04/21/14728.aspx

如何判斷TRIGGER目前是INSERT、UPDATE、DELETE的狀態:

http://www.dotblogs.com.tw/dc690216/archive/2009/09/10/10553.aspx

在master table及dtl table的條件下，又要加上放行機制(在下範例*_LOG為放行table)，如果在MASTER_LOG需要觸發到MASTER TABLE及DTL的所有相關TABLE一併更新及新增，可用下列的範例去達成。

1．建立TABLE:

   1: USE [DB]



   2: GO



   3:  



   4: /****** Object:  Table [dbo].[TB_CBRP_EXPT]    Script Date: 09/29/2010 17:24:46 ******/



   5: SET ANSI_NULLS ON



   6: GO



   7:  



   8: SET QUOTED_IDENTIFIER ON



   9: GO



  10:  



  11: --建立主檔



  12:  



  13: CREATE TABLE [dbo].[TB_CBRP_EXPT](



  14:     [CASE_ID] [nvarchar](12) NOT NULL,



  15:     [CASE_TYPE] [nvarchar](1) NULL,



  16:     [CASE_GROUP] [nvarchar](1) NULL,



  17:     [CARD_NO] [nvarchar](19) NULL,



  18:     [BRANCH_ID] [nvarchar](5) NULL,



  19:     [CONFIRM_ID] [nvarchar](12) NULL,



  20:     [CONFIRM_DATETIME] [datetime] NULL,



  21:     [CREATE_ID] [nvarchar](12) NULL,



  22:     [CREATE_DATETIME] [datetime] NULL,



  23:     [UPDATE_ID] [nvarchar](12) NULL,



  24:     [UPDATE_DATETIME] [datetime] NULL,



  25:  CONSTRAINT [PK_CBRP_EXPT] PRIMARY KEY CLUSTERED ([CASE_ID] ASC)



  26: ) ON [PRIMARY]



  27:  



  28: --建立主檔放行表



  29:  



  30: /****** Object:  Table [dbo].[TB_CBRP_EXPT_LOG]    Script Date: 09/29/2010 17:26:22 ******/



  31: CREATE TABLE [dbo].[TB_CBRP_EXPT_LOG](



  32:     [CASE_ID] [nvarchar](12) NOT NULL,



  33:     [CASE_TYPE] [nvarchar](1) NULL,



  34:     [CASE_GROUP] [nvarchar](1) NULL,



  35:     [CARD_NO] [nvarchar](19) NULL,



  36:     [BRANCH_ID] [nvarchar](5) NULL,



  37:     [LOG_ID] [int] NOT NULL,



  38:     [IS_CONFIRM] [bit] NOT NULL,



  39:     [CONFIRM_TYPE] [nvarchar](1) NULL,



  40:     [CONFIRM_ID] [nvarchar](12) NULL,



  41:     [CONFIRM_DATETIME] [datetime] NULL,



  42:     [CREATE_ID] [nvarchar](12) NULL,



  43:     [CREATE_DATETIME] [datetime] NULL,



  44:     [UPDATE_ID] [nvarchar](12) NULL,



  45:     [UPDATE_DATETIME] [datetime] NULL,



  46:  CONSTRAINT [PK_CBRP_EXPT_LOG] PRIMARY KEY CLUSTERED ([CASE_ID] ASC,[LOG_ID] ASC)



  47: ) ON [PRIMARY]



  48:  



  49:  



  50: --建立明細主檔



  51:  



  52: /****** Object:  Table [dbo].[TB_CBRP_EXPT_DTL]    Script Date: 09/29/2010 17:27:30 ******/



  53: CREATE TABLE [dbo].[TB_CBRP_EXPT_DTL](



  54:     [CASE_ID] [nvarchar](12) NOT NULL,



  55:     [CASE_SEQ] [numeric](3, 0) NOT NULL,



  56:     [MERCH_ID] [nvarchar](15) NULL,



  57:     [CASE_STATUS] [nvarchar](2) NULL,



  58:     [SETL_FLAG] [nvarchar](1) NULL,



  59:     [CONFIRM_ID] [nvarchar](12) NULL,



  60:     [CONFIRM_DATETIME] [datetime] NULL,



  61:     [CREATE_ID] [nvarchar](12) NULL,



  62:     [CREATE_DATETIME] [datetime] NULL,



  63:     [UPDATE_ID] [nvarchar](12) NULL,



  64:     [UPDATE_DATETIME] [datetime] NULL,



  65:  CONSTRAINT [PK_CBRP_EXPT_DTL] PRIMARY KEY CLUSTERED ([CASE_ID] ASC,[CASE_SEQ] ASC)



  66: ) ON [PRIMARY]



  67:  



  68: --建立明細放行表



  69:  



  70: /****** Object:  Table [dbo].[TB_CBRP_EXPT_DTL_LOG]    Script Date: 09/29/2010 17:28:01 ******/



  71: CREATE TABLE [dbo].[TB_CBRP_EXPT_DTL_LOG](



  72:     [CASE_ID] [nvarchar](12) NOT NULL,



  73:     [CASE_SEQ] [numeric](3, 0) NOT NULL,



  74:     [MERCH_ID] [nvarchar](15) NULL,



  75:     [CASE_STATUS] [nvarchar](2) NULL,



  76:     [SETL_FLAG] [nvarchar](1) NULL,



  77:     [LOG_ID] [int] NOT NULL,



  78:     [IS_CONFIRM] [bit] NULL,



  79:     [CONFIRM_TYPE] [nvarchar](1) NULL,



  80:     [CONFIRM_ID] [nvarchar](12) NULL,



  81:     [CONFIRM_DATETIME] [datetime] NULL,



  82:     [CREATE_ID] [nvarchar](12) NULL,



  83:     [CREATE_DATETIME] [datetime] NULL,



  84:     [UPDATE_ID] [nvarchar](12) NULL,



  85:     [UPDATE_DATETIME] [datetime] NULL,



  86:  CONSTRAINT [PK_CBRP_EXPT_DTL_LOG] PRIMARY KEY CLUSTERED ([CASE_ID] ASC,[CASE_SEQ] ASC,[LOG_ID] ASC)



  87: ) ON [PRIMARY]



  88:  



  89: GO





 2．以下是我所建立的trigger


所執行的目的是


1．MASTER_LOG放行表如果異動了IS_CONFIRM欄位為1(允許)時，要同時觸發新增一筆資料至MASTER主檔，及將DTL_LOG明細放行表裡該筆交易也UPDATE IS_CONFIRM欄位視為放行，並將DTL_LOG放行內容，INSERT記錄至DTL_TABLE。


等於可以把放行的行為一次到位。但因為我作的範例綁定了欄位名稱，這樣作有好處是效能較快一點，但不夠彈性！




   1: ALTER TRIGGER [dbo].[TRI_UPDATE]    --Trigger名稱  



   2:     ON  [dbo].TB_CBRP_EXPT_LOG             --附掛在那張Table  



   3:     FOR UPDATE               --觸發時機(INSERT/DELETE/UPDATE)  



   4:  AS  



   5: IF (select is_confirm from inserted )=1



   6:  BEGIN 



   7:      Declare     @key        nVarChar(30)    --宣告要使用的暫時變數  



   8:      Declare     @key1        nVarChar(30)    --宣告要使用的暫時變數  



   9:      Declare     @dtl_key        nVarChar(30)    --宣告要使用的暫時變數  



  10:      Declare     @dtl_key1        nVarChar(30)    --宣告要使用的暫時變數  



  11:                



  12:                BEGIN --IF Exists --SQL處理



  13:                 Select @key=CASE_ID,@key1=log_id From Inserted ;                



  14:                   IF Exists (Select case_id,log_id From TB_CBRP_EXPT_LOG where case_id=@key and log_id=@key1)  



  15:                     BEGIN--IF Exists --SQL處理  



  16:                         insert into TB_CBRP_EXPT (case_id,case_type,case_group,card_no,branch_id,confirm_id,confirm_datetime,create_id,create_datetime,update_id,update_datetime) (select case_id,case_type,case_group,card_no,branch_id,confirm_id,confirm_datetime,create_id,create_datetime,update_id,update_datetime from tb_cbrp_expt_log where case_id=@key and log_id=@key1);



  17:                         print 'insert into cbrp_expt table ';



  18:                         update tb_cbrp_expt_dtl_log set is_confirm='1' where case_id=@key;



  19:                     END--IF Exists



  20:                   ELSE 



  21:                     begin



  22:                         print 'nothing';



  23:                     end  



  24:                   



  25:                END     --IF Exists --SQL處理           



  26:         SELECT @dtl_key=CASE_ID FROM TB_CBRP_EXPT_DTL_LOG WHERE CASE_ID=@key and is_confirm=1;        



  27:  



  28:         BEGIN



  29:             



  30:             Declare TRI_CBRP_DTL_CURSOR  CURSOR For 



  31:             SELECT case_id,case_seq ,merch_id,case_status,setl_flag,confirm_id,confirm_datetime,create_id,create_datetime,update_id,update_datetime  FROM TB_CBRP_EXPT_DTL_LOG WHERE CASE_ID=@dtl_key ;



  32:             --宣告CURSOR名字及資料  



  33:             Open TRI_CBRP_DTL_CURSOR   



  34:             declare @case_id nvarchar(12),@case_seq numeric(3),@merch_id nvarchar(15),@case_status nvarchar(2);



  35:             declare @setl_flag nvarchar(1),@confirm_id nvarchar(12),@confirm_datetime datetime,@create_id nvarchar(12);



  36:             declare @create_datetime datetime,@update_id nvarchar(12),@update_datetime datetime ;



  37:             



  38:             fetch next from TRI_CBRP_DTL_CURSOR Into @case_id,@case_seq ,@merch_id,@case_status,@setl_flag,@confirm_id,@confirm_datetime,@create_id,@create_datetime,@update_id,@update_datetime 



  39:             



  40:             While (@@FETCH_STATUS<>-1)    --迴圈  



  41:                BEGIN--While  



  42:                     insert into TB_CBRP_EXPT_DTL VALUES(@case_id,@case_seq ,@merch_id,@case_status,@setl_flag,@confirm_id,@confirm_datetime,@create_id,@create_datetime,@update_id,@update_datetime) ;



  43:                     Fetch Next From TRI_CBRP_DTL_CURSOR  Into @case_id,@case_seq ,@merch_id,@case_status,@setl_flag,@confirm_id,@confirm_datetime,@create_id,@create_datetime,@update_id,@update_datetime 



  44:                End--While          



  45:             Deallocate  TRI_CBRP_DTL_CURSOR  --釋放CURSOR  



  46:          END 



  47:          



  48:  END






未來在作SQL SERVER trigger，可以參考用。

張貼者： 於 沒有留言:

2010年6月7日 星期一

遠端桌面連線數已滿的解決方式

當遠端桌面連線只有2個連線數的限制後，兩個連線都被佔滿之後，在使用mstsc登入時，會出現已超過連線限制的訊息:『終端機伺服器已經超過允許的最大連線數目』，

image

這時我使用mstsc /console也無法登入，後來上網查詢了一下其它網友分享的解決方式如下:

1．在遠端桌面登入輸入伺服器名稱的地方再加上 /admin ，

原本是/console-->但這個方式在xp sp3後，就無法再使用了。

image

電腦的地方，請輸入 『xxx.xxx.xxx.xxx /admin』，按連線，接著輸入登入帳號，即可順利登入嘍！！

連線後，盡快登入”終端機服務管理員，將逾時連線的user登出，以免之後再繼續佔滿。

以前常常被這種連線數困擾，有此方式，就不需要請客戶端經常幫我踢掉那些佔用的連線了！^^

如果常常有使用者佔著連線不作事，可以在群組原則裡設定三個限制項目，

利用 gpedit.msc

image

至於每個項目會影響到使用者的使用方式及結果，就要靠自己評估了！

 

ps: 看到有一篇文章也在解這個問題，

http://ns2.ublink.org/phpbb/viewtopic.php?p=756

但這個方式連線後，還是會出現連線數滿的狀況。建議用上述第一種方式。

張貼者： 於 沒有留言:

2010年5月7日 星期五

Oracle不區分字母大小寫的文章

在Oracle 10G R2 版本new feature中，有提到oracle在字母區分大小寫的部分，有作了一些enhancements，以看官方文件中的此主題:『Linguistic Sorting and String Searching』

URL:http://download-uk.oracle.com/docs/cd/B14117_01/server.101/b10749/ch5lingsort.htm#i1009059

主要是ap可能在進行這類型的需求時，需要先改變session層級設定:

ALTER SESSION SET NLS_COMP=ANSI;
ALTER SESSION SET NLS_SORT=GENERIC_BASELETTER;

張貼者： 於 沒有留言:

2010年5月5日 星期三

SQLNET.ora 遇domain naming時的參數設定

Oracle DB Server若有domain 設定時，
用netca 新建命名規則，sqlnet.ora會自動幫您新建一個參數
NAMES.DEFAULT_DOMAIN = <domain name>

若mark掉，在connect時會出現tns error:

image

必需要帶sqlplus user/password@SID.domain才能連線。

設定此參數後，即可利用Sqlplus user/password@SID 連線成功。

張貼者： 於 沒有留言:

2010年4月30日 星期五

LOG_ARCHIVE_FORMAT參數如何設定

Format options available on 9i:

%s – log sequence number
%S – log sequence number, zero filled
%t – thread number, needed when running RAC as each node creates it』s own archivelog
%T thread number, zero filled, needed when running RAC as each node creates it』s own archivelog

Format options available on 10g

%s – log sequence number
%S – log sequence number, zero filled
%t – thread number, needed when running RAC as each node creates it』s own archivelog
%T – thread number, zero filled, needed when running RAC as each node creates it』s own archivelog
%a – activation ID
%d – database ID
%r – resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database.

In 10g, %s, %t, %r are required to be present in the parameter, if it doesn』t the database fail to start with the error ORA-19905: log_archive_format must contain %s, %t and %r. Using this format makes it the archive log filename unique for that instance.
張貼者： 於 沒有留言:

2010年3月25日 星期四

在Oracle 10g之後安裝Oracle9i ，DBCA會啟動失敗

最近重新在裝了Oracle10G Client的機器上為了安裝oracle 9i Database Server,安裝完後，卻出現dbca啟動失敗。

後來重灌了2次oracle 9i (還以為是安裝元件出了錯)，錯誤好像跟JRE連結有關，原本以為是jre版本相衝突，到環境變數修改path，將jre舊的版本提前，結果還是錯誤。

錯誤畫面

2010-03-25_180529

最後Google了一些關鍵字，終於找到一個相同的案例，說是jre環境變數的原因，但不是動jre的版本順序，而是要直接將oracle 10g的home path先移除，等到啟動了dbca後，再恢復環境變數。

解法:

環境變數修改>>

原本的:
C:\oracle\ora92\bin;C:\Program Files\Oracle\jre\1.1.8\bin;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;c:\Oracle\product\10.1.0\Client_1\BIN;C:\Program Files\Oracle\jre\1.3.1\bin;

換成:

C:\oracle\ora92\bin;C:\Program Files\Oracle\jre\1.3.1\bin;C:\Program Files\Oracle\jre\1.1.8\bin;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem

啟動dbca>>

成功！！正在安裝資料庫呢！

image

趕緊記下來，免得以後又忘了類似的問題~~參考的url:http://www.100test.com/html/142/s_142717_14.htm

等到dbca運行正常後，請記得把oracle 10g的path再加回去環境變數中哦！

張貼者： 於 沒有留言:

2010年2月9日 星期二

SQL Server 與 Oracle 函數差別

當開發人員要開發由ORACLE -> SQL SERVER資料庫平台的服務時，
最先遇到的問題是"SQL語法"究竟能不能通用呢？

很多設計師在設計系統時，若常常是BASE ON ORACLE，那麼其程式撰寫的SQL會非常依賴ORACLE，相對的在移轉性上會比較困難。
雖然個人認為ORACLE在函數的運用上有非常多元，速度上可能也比較佔優勢。
但近來SQL SERVER 2008的T-SQL，有許多以前SQL 2000沒有的函數，可以說已經跟ORACLE不相上下了~~。

在此說明這兩者資料庫的函數不同點吧！相同的我就不加以說明，會再繼續補充:

函數與語法 Oracle SQL Server 2008
字串連結 || +
補位 LPAD/RPAD REPLICATE(補位字元,<最大長度n>-len(欄位)) +cast(欄位 as nvarchar)
型態轉換 TO_CHAR CONVERT,CAST
型態轉換 TO_NUMBER CONVERT,CAST
型態轉換 TO_DATE CONVERT(TYPE,COL,Sql server定義格式編號)
NULL值轉換 NVL ISNULL   (   check_expression   ,   replacement_value   ) 
四捨五入(數) CEIL CEILING
取整數 TRUNC CAST( COL AS INT)
取子字串 SUBSTR SUBSTRING
取長度 LENGTH LEN
取系統日 SYSDATE GETDATE()
時間相減差 datediff() Date1-Date2
序號 sequence (.nextval,.currval) 類似的作法(IDENTIFY,@@IDENTITY,IDENT_CURRENT)
或自行撰寫
張貼者： 於 沒有留言:
訂閱： 文章 (Atom)