TAILIEUCHUNG - SQL PROGRAMMING STYLE- P7

SQL PROGRAMMING STYLE- P7: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). | Bad Programming in SQL and Procedural Languages 187 but it at least follows the specs that were given without making too many guesses as to what should have been done. But can we do this without a loop and get a pure nonprocedural SQL solution Yes there are several ways Because the purpose of finding this weekday number is to insert a row in the table why not do that in one procedure instead of finding the number in a function and then doing the insertion in another procedural step. Think at the level of a whole process and not in sequential steps. This first answer is ugly looking and difficult to generalize but it is fast if the optimizer factors out the tabular subquery in the WHEN clauses and computes it once. It also uses no local variables. CREATE PROCEDURE InsertNewWeekDay IN new_week_nbr INTEGER LANGUAGE SQL INSERT INTO WeeklyReport week_nbr day_nbr VALUES new_week_nbr CASE WHEN 1 NOT IN SELECT day_nbr FROM WeeklyReport WHERE week_nbr new_week_nbr THEN 1 WHEN 2 NOT IN SELECT day_nbr FROM WeeklyReport WHERE week_nbr new_week_nbr THEN 2 WHEN 3 NOT IN SELECT day_nbr FROM WeeklyReport WHERE week_nbr new_week_nbr THEN 3 WHEN 4 NOT IN SELECT day_nbr FROM WeeklyReport WHERE week_nbr new_week_nbr THEN 4 WHEN 5 NOT IN SELECT day_nbr FROM WeeklyReport WHERE week_nbr new_week_nbr THEN 5 WHEN 6 NOT IN SELECT day_nbr FROM WeeklyReport WHERE week_nbr new_week_nbr THEN 6 WHEN 7 NOT IN 188 CHAPTER 10 THINKING IN SQL SELECT day_nbr FROM WeeklyReport WHERE week_nbr new_week_nbr THEN 7 ELSE NULL END null will violate primary key The thought process was to get the entire set of weekday numbers present in the week and then compare them to each value in an ordered list. The CASE expression is just a way to hide that list. Although it is a step forward it is not yet really a set-oriented solution. Here is another version that uses a table constructor. This is more compact and easy to generalize. Here we are actually using a set-oriented solution We are subtracting the set of

• 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