RA MẮT THỬ NGHIỆM PHIÊN BẢN MỚI CỦA DIỄN ĐÀN

Mình có một chút vấn đề về StoreProc và Transaction muốn hỏi mọi người. Giả sử mình có một Table như sau: Categories(CategoryID, CategoryName, ParentCategoryID), có một Relationship từ bảng Categories đến chính nó, CategoryID ---<- ParentCategoryID . Mỗi Category có thể có các Category con hoặc không có. Khi mình delete một Category đi thì mình muốn: nếu Category đó có các Category con thì trường ParentCategoryID của các Category con đó cho về null. Vậy mình viết một StoreProc sau, mọi người sửa giúp mình nhé . Mình viết bằng ngôn ngữ giả SQL mô tả công việc thôi, nhờ mọi người chuyển giúp thành ngôn ngữ SQL thật :

PROCEDURE DeleteCategory
@CategoryID char(10)
AS
if exists( select * from Categories where CategoryID =@CategoryID )
  begin
   begin Transaction   // bắt đầu một giao  dịch
    SubCategories = select * from Categories where ParentID = @CategoryID
   for each Category in SubCategories{ // cái này mình chỉ mô tả thôi, không biết viết chính xác như thế nào
     exec UpdateCategoryParent Category.CategoryID, null// lời gọi này có đúng ko?
    }
    delete from Categories where CategoryID =@CategoryID
    if error goto err
   end Transaction  // kết thúc giao dịch
   err: roll back Transaction  // nếu có lỗi thì đảo ngược giao dịch
  end
RETURN

Còn đây là StoreProc UpdateCategoryParent :

PROCEDURE UpdateCategoryParent
@CategoryID char(10),
@ParentID char(10)
AS
if exists( select * from Categories where CategoryID=@CategoryID)
  begin
   if exists( select * from Categories where CategoryID=@ParentID)
    begin
     update Categories set
      ParentID = @ParentID
     where CategoryID=@CategoryID
    end
  end
RETURN

Mình nói lại là mình chỉ viết bằng ngôn ngữ giả SQL để mô tả thôi chứ không phải SQL thật . Nhờ mọi người giúp chuyển thành SQL thật .  Cám ơn nhiều !

Bảng Categories có quan hệ đến chính nó, Category A có Category con là B, B lại có Category con là C, ..D..E....cứ tiếp tục như thế . Như vậy ta không thể biết được chính xác độ sâu của chuỗi Category cha con. Tớ muốn truy  vấn như thế này, đầu vào là một CategoryID, đầu ra là tất cả các Category nằm trong chuỗi Category cha con đó thì phải truy vấn như thế nào . Mọi người chỉ giúp với. Thanks a lot!

Với yêu cầu của bạn thì dùng Stored Procedure là "dao mổ trâu giết gà" rồi. Bạn chỉ cần dùng tính chất của Foreign key là đủ.

VD tạo 1 table có relationship với chính nó
CREATE TABLE category (
  categoryID     int     not null default '0',
  parentID      int     not null default '0'
    REFERENCES category(categoryID)
      ON DELETE SET DEFAULT
      ON UPDATE CASCADE,
  PRIMARY KEY (categoryID)
)

ON DELETE/UPDATE là event đựoc fired khi có thao tác DELTE/UPDATE xảy ra.

SET DEFAULT sẽ gán giá trị của field đó vể mặc định (là 0)
CASCADE: update hoặc del luôn các row nằm trong ràng buộc của relationship
SET NULL sẽ gán giá trị cảu field đó là NULL

thangdv:

Mình có một chút vấn đề về StoreProc và Transaction muốn hỏi mọi người......

PROCEDURE DeleteCategory
@CategoryID char(10)
AS
..........................

Chào bạn,

Cách làm của bạn có vẻ phức tạp quá. Nếu như thế bạn sẽ phải dùng đến Cursor. Mình thử đưa một cách đơn giản hơn để bạn xem xem thế nào nhé.

Cách xử lí của mình: Đầu tiên mình sẽ set hết parentID của các bản ghi về rống(hay gì đó) nếu như nó có giá trị là CategoryID cần xoá.  Sau đó xoá bản ghi CategoryID

Tất nhiên trong cả 2 cách mình vẫn cần đến transaction.

Khi đó thủ tục xoá sẽ là:
PROCEDURE DeleteCategory
@CategoryID char(10)
AS
Begin Tran DeleteCate_Trans
-- set parent to empty
UPADATE Category
SET ParentID=''
WHERE ParentID=@CategoryID

--Check error
If @@Error <>0 goto Error_Lbl

-- Delete
Delete FROM Category
WHERE CategoryID=@CategoryID

--Check error
If @@Error <>0 goto Error_Lbl

-- Have no error, commit trans, exit sub
Commit Tran DeleteCate_Trans
Return

-- error processing
Error_Lbl:
Rollback Tran DeleteCate_Trans


Mình thấy việc update|delete đâu cần thiết check exist làm gì, nếu muốn bắt lỗi, bạn có thể dùng @@ROWCOUNT sau mỗi câu lệnh để kiểm tra số bản ghi đưọc thực thi.

Còn việc làm như bạn Guest bảo thì có lẽ không ổn, chí ít là đối với SQL server 2K vì mình không thấy có cú pháp đó trong SQL server 2K.

Have fun.
HHD.

hau_hung_dao:

Còn việc làm như bạn Guest bảo thì có lẽ không ổn, chí ít là đối với SQL server 2K vì mình không thấy có cú pháp đó trong SQL server 2K.

MSSQL Server 7 thì tôi không biết (mà có lẽ là không có thật vì trên internet cũng có nhiều người hỏi câu này), nhưng MSSQL Server 2k thì đã hỗ trợ ON DELETE và ON UPDATE. Xem thêm: http://databasejournal....ssql/article.php/2219901
và trên MSDN cú pháo lệnh CREATE TABLE: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp
hoặc trong Book online của MSSQL Server (hiện cái book online tôi không có ở đây).

Tuy nhiên, hiện tại MSSQL Server chỉ hỗ trợ CASCADE và NO ACTION chứ không có SET NULL hay SET DEFAULT, chắc là bạn guest kia nhớ nhầm sang Oracle hoặc MySQL.

Guest:

...............
Tuy nhiên, hiện tại MSSQL Server chỉ hỗ trợ CASCADE và NO ACTION chứ không có SET NULL hay SET DEFAULT, chắc là bạn guest kia nhớ nhầm sang Oracle hoặc MySQL.

Hi,
Bác Guest này nói chính xác (cú pháp) của SQL Server, hihih, còn bác Guest kia nhớ nhầm sang MySQL rồi thì phải vì em thấy Oracle  cũng chỉ có đến SET NULL chứ chưa có thêm SET DEFAULT như MySQL.

Have fun!
HHD.

thangdv:

Giả sử mình có một Table như sau: Categories(CategoryID, CategoryName, ParentCategoryID), có một Relationship từ bảng Categories đến chính nó.
........
Category A có Category con là B, B lại có Category con là C, ..D..E....cứ tiếp tục như thế . Như vậy ta không thể biết được chính xác độ sâu của chuỗi Category cha con. Tớ muốn truy  vấn như thế này, đầu vào là một CategoryID, đầu ra là tất cả các Category nằm trong chuỗi Category cha con đó thì phải truy vấn như thế nào . Mọi người chỉ giúp với. Thanks a lot!

Hi,

Còn cái query này, ý của bạn là lấy tất cả con, cháu, chắt, chút, chít, ... của nó ra đúng không, thế thì chắc là phải dùng đệ quy rồi, ;-). Bạn có thể dùng trong code của chương trình (recordset), hoặc có thể dùng ngay T-SQL trong SQL Server. Chắc cách gọi đệ quy thế nào trong bài toán này thì bạn hẳn đã biết rồi đúng không!

Theo ý kiến chủ quan của mình, để làm được trong T-SQL thì sẽ phải dùng đến cursor và chỉ làm được trên SQL Server 2K(8.0) vì mình dùng kiểu biến là Table, ;-), và ... mình phải viết dưới dạng hàm, ;-).

Đây là hàm của mình để bạn tham khảo, có thể cấu trúc bảng sẽ khác với của bạn:
CREATE FUNCTION getChild(
@ParentID int
)
RETURNS @OutTable Table(CategoryID int,   [Name] varchar(50), ParentID int )
AS
BEGIN
-- Khai bao mot bien kieu Table voi cau truc nhu trong CSDL
Declare @tmpTable Table(CategoryID int,   [Name] varchar(50), ParentID int )

-- Lay gia tri do vao table
INSERT Into @tmpTable
SELECT CategoryID,   [Name], ParentID
FROM Category
WHERE ParentID=@ParentID

-- neu chua het ban ghi, ;-) hay van còn con
IF @@RowCount>0
Begin
     Declare @tmpID int
     -- Khai bao mot bien con tro de  duyet, lap de quy
     Declare myCur CURSOR   FOR
     SELECT CategoryID FROM Category
     WHERE ParentID=@ParentID
    -- Mo con tro   
    Open myCur
    -- chuyen den ban ghi dau tien
    Fetch next from myCur
    Into @tmpID

    While @@Fetch_Status=0 -- lap khi van con du lieu
    Begin
           -- goi de quy va lay gia tri chen vao bang ket qua            
           INSERT Into @tmpTable
           SELECT CategoryID,   [Name], ParentID
           From dbo.getChild(@tmpID)
           -- Move next -- chuyen den ban ghi ke tiep 
           Fetch Next From myCur
           Into @tmpID
    End
    -- Dong va giai phong bien con tro
    Close myCur   
    Deallocate myCur
End
-- Do giu lieu ra bang ket qua
INSERT Into @OutTable
SELECT CategoryID,   [Name], ParentID
From @tmpTable

Return

END

Sau dùng câu lệnh select đơn giản như sau:
Select *
From dbo.getchild(category_value)
Order by CategoryID

Tất nhiên, để đơn giản bạn có thể chỉ cần lấy ra CategoryID thôi, khi đó câu SQL truy vấn sẽ có thể như sau:

Select *
From CategoryTable
WHERE CategoryID IN ( Select CategoryID
                                    From dbo.getchild(category_value))
Order by CategoryID

Have fun!
HHD.

Thanks bác H_H_D và mọi người, mode thế mới là mode chứ.
Cho mình hỏi thêm một chút nhá, có được dùng nhiều hơn một con trỏ CURSOR trong cùng một storeProc hay function không vậy. Vì mình thấy điều kiện kiểm tra con trỏ là:
fetch next from ... into @....
while @@FETCH_STATUS = 0
       begin
            ...............
            fetch next from ... into @....
       end
tất cả các con trỏ trong cùng  một giao dịch đều dùng biến global @@FETCH_STATUS để kiểm tra, như vậy nếu hai con trỏ lồng nhau thì có được không, ví dụ:
declare thefirstCur CURSOR FOR select ..............
fetch next from thefirstCur into @....
while @@FETCH_STATUS = 0
       begin
            declare thesecondCur CURSOR FOR select ..............
            fetch next from thesecondCur into @....
            while @@FETCH_STATUS = 0
                  begin      
                      ...............
                      fetch next from thesecondCur into @....
                  end
                 close thefirstCur
                 deallocate thefirstCur
            .......................
            fetch next from thefirstCur into @....
       end
close thefirstCur
deallocate thefirstCur

Đại loại là lồng nhau như vậy có làm sao không ạ?

thangdv:

Cho mình hỏi thêm một chút nhá,.....
Tất cả các con trỏ trong cùng một giao dịch đều dùng biến global @@FETCH_STATUS để kiểm tra, như vậy nếu hai con trỏ lồng nhau thì có được không, ví dụ:
declare thefirstCur CURSOR FOR select ..............
fetch next from thefirstCur into @....
while @@FETCH_STATUS = 0
       begin
            declare thesecondCur CURSOR FOR select ..............
            fetch next from thesecondCur into @....
            while @@FETCH_STATUS = 0
                  begin      
                      ...............
                      fetch next from thesecondCur into @....
                  end
                 close thefirstCur
                 deallocate thefirstCur
            .......................
            fetch next from thefirstCur into @....
       end
close thefirstCur
deallocate thefirstCur

Đại loại là lồng nhau như vậy có làm sao không ạ?

Chào bạn,

Đúng là cái biến @@FETCH_STATUS là được dùng chung cho tất cả các cursor trong một connection, nó được set lại khi bạn gọi lệnh FETCH và dĩ nhiên sẽ nhận giá trị của cursor vừa được fetch đó, vì thế bạn phải hết sức chú ý khi sử dụng nó.

Nếu bạn dùng nó trong trường hợp lồng nhau như bạn kể ở trên thì mình có thể nói là không có vấn đề gì. Sở dĩ làm được đó là vì bạn duyệt hết cursor con rồi gọi lại Fetch tới cursor cha, khi đó @@FETCH_STATUS được set trở lại và nó sẽ mang giá trị của cursor cha. Mình có thể mô tả như thế này nhé:

Fetch next from Cursor_Cha
-- -> @@Fetch_status được reset, nó nhận giá trị của Cursor_Cha

While @@Fetch_status=0 -- tôi luôn mang giá trị của Cursor_Cha?, ;-)
Begin 
      ...............................................
      Fetch next from Cursor_Con
      -- -> @@Fetch_status được reset, nó nhận giá trị của Cursor_Con

      While @@Fetch_status=0 -- tôi luôn mang giá trị của Cursor_Con?, ;-)
      Begin
            ...........................
            Fetch next from Cursor_Con
             -- -> @@Fetch_status được reset, nó nhận giá trị của Cursor_Con
      End

      Fetch next from Cursor_Cha
      -- -> @@Fetch_status được reset, nó nhận giá trị của Cursor_Cha
      -- Nhận giá trị của Cursor_Cha hả?, hehehe vòng lặp sài tốt
End

Như vậy là vẫn được phải không, tuy nhiên nếu bạn mà dùng không cẩn thận thì sẽ bị lỗi, chủ yếu có thể là dùng song song các cursor. Ví dụ như đoạn code sau:
..........
Open Cursor1
Open Cursor2
.................

Fetch Next From Cursor1
-- -> @@Fetch_status được reset
If @@Fetch_status=0 --tôi mang giá trị của Cursor1 đó, quá rõ ràng;-)
Begin
-- Tôi đang ở bản ghi đầu tiên của bảng
-- do Cursor1 trỏ tới. ừ!
.....................
End

..............................
Fetch Next From Cursor2
-- -> @@Fetch_status được reset
If @@Fetch_status=0
--bây giờ tôi thì mang giá trị của Cursor2 rồi. Dĩ nhiên!
Begin
-- Và tôi cũng đang ở bản ghi đầu tiên
-- của bảng do Cursor2 trỏ tới. Ừ!
.....................
End

Fetch Next From Cursor1
-- -> @@Fetch_status được reset
If @@Fetch_status=0 --tôi mang giá trị của Cursor1 đó. Biết rồi!
Begin
-- Bây giờ tôi đang ở bản ghi thứ 2 của bảng do
-- Cursor1 trỏ tới. Rồi! Nói nhiều quá, ;-)
.....................
End

...................................

Như vậy, khi dùng với con trỏ trong SQL Server, bạn cần chú ý là bạn đã gọi fetch trên con trỏ nào thì @@Fetch_status mang giá trị của con trỏ đó và nó luôn mang giá trị của contror được fetch sau cùng, ;-).

Hi vọng có thể giúp được bạn đôi chút,
HHD.