TAILIEUCHUNG - SQL PROGRAMMING STYLE- P6

SQL PROGRAMMING STYLE- P6:Im mot trying to teach you to program in SQL in this book. You might want to read that again. If that is what you wanted, there are better books. This ought to be the second book you buy, not the first. I assume that you already write SQL at some level and want to get better at it. If you want to learn SQL programming tricks, get a copy of my other book, SQL for Smarties (3rd edition, 2005). | 192 CHAPTER 10 THINKING IN SQL CREATE TABLE Users user_id CHAR 8 NOT NULL PRIMARY KEY password VARCHAR 10 NOT NULL max_reserves INTEGER NOT NULL CHECK max_reserves 0 CREATE TABLE Reservations user_id CHAR 8 NOT NULL REFERENCES Users user_id ON UPDATE CASCADE ON DELETE CASCADE item_id INTEGER NOT NULL REFERENCES Items item_id The original narrative specification was Each user can reserve a maximum of n items. Whenever a user reserves something the max_reserves field sic of the user is retrieved and checked. Then a record sic is inserted into the Reservations table and the max_reserves field sic of the user is updated accordingly. I would like to ask if there is a better way to implement this system because there is a chance that the user reserves more than the maximum number if he or she is logged in from two computers. The first proposal was for a stored procedure that looked like this in SQL PSM CREATE PROCEDURE InsertReservations IN max_reserves INTEGER IN my_user_id CHAR 8 IN my_item_id INTEGER LANGUAGE SQL BEGIN DECLARE my_count INTEGER SET my_count SELECT COUNT FROM Reservations WHERE user_id my_user_id IF my_count max_reserves THEN RETURN You have Reached you MAX number of items ELSE INSERT INTO Reservations user_id item_id VALUES my_user_id my_item_id END IF END Thinking in Processes Not Declarations 193 Passing the maximum number of items as a parameter makes no sense because you have to look it up this will let you pass any value you desire. Having a local variable for the count is redundant SQL is orthogonal and the scalar subquery can be used wherever the scalar variable is used. Rows are not records and columns are not fields. SQL is a declarative language not a procedural one. So a sequence of procedural steps like Retrieve check insert update does not make sense. Instead you say that you make a reservation such that the user is not over his or her limit. Think of logic not process. CREATE PROCEDURE MakeReservation IN my_user_id CHAR 8 IN .

• Cơ sở dữ liệu
• cơ sở dữ liệu MySQL
• database SQL
• thiết kế CSDL quan hệ
• Kiến trúc hệ quản trị CSDL
• đặc tính cơ sở dữ liệu
• bảo mật cơ sở dữ liệu
• Cơ sở dữ liệu
• Hệ quản trị cơ sở dữ liệu MySQL
• Quản trị cơ sở dữ liệu
• Làm việc với MySQL
• Giới thiệu về MySQL
• Bài giảng Phần mềm nguồn mở
• Phần mềm nguồn mở
• Hệ quản trị cơ sở dữ liệu
• Lịch sử phát triển MySQL
• Kiến trúc MySQL
• MySQL Command Line
• MySQL Query Browser
• giáo trình cơ sở dữ liệu
• lý thuyết cơ sở dữ liệu
• bái giảng cơ sở dữ liệu
• tài liệu cơ sở dữ liệu
• Kết nối với MySQL
• Truy xuất cơ sở dữ liệu
• Quản lý dữ liệu
• Kết nối cơ sở dữ liệu
• Cập nhật dữ liệu
• Kích hoạt dữ liệu
• cơ sở dữ liệu sql server
• MySQL cơ bản