DB2 SF13 学习日志

一个菜鸟在学习DB2的过程中，总会遇到各种各样的问题，当看完DB2 SQL Wirkshop for Experienced Users 这本教材，并在实践过程中仍然有一些问题让我值得注意，所以我列出了一些今天实践中觉得注意的地方。

1，create trigger:
        create trigger re_order \
        after update of stock_qty on article \
        referencing new as n old as o \
        for each row \
        mode db2sql \
        when (n.stock_qty < 50 and \
              o.stock_qty>=50) \
          insert into sup_orders \
                 values(n.art_no, n.stock_qty)
        drop trigger re_order
    mark: If FOR EACH ROW is used, the triggered action will take place once for each row updated.
         If FOR EACH STATEMENT is used, the triggered action will be performed only once, no matter how many rows are updated.

2，referential integrity:
      alter table order \
            add foreign key (order_ref) \
                references order \
                on delete no action
   mark:If the foreign key is in the same table as the corresponding primary key, the delete rule for the referential constraint must be CASCADE or NO ACTION. It cannot be SET NULL or RESTRICT. This may have an impact on the programs for the application.
        In the example above, order 22444 refers to order 22333 and order 35555 to order  22444. The delete rule chosen is NO ACTION which, for example, prevents the deletion of order 22333 because order 22444 depends on it. Since order 35555 is not dependent on any other order, it can be deleted.
        If the delete rule was CASCADE, the deletion of order 22333 would cause the deletion of order 22444 which, in turn, would cause the deletion of order 35555.

3，group by grouping sets:

                        select job, day(birthdate), \
                              decimal(avg(salary),8,2) as avg_salary \
                             from employee \
                             where year(birthdate)>1938 \
                              group by \
                              grouping sets(day(birthdate),job)
    mark:GROUP BY GROUPING SETS((JOB),(DAY(BIRTHDATE)) is equivalent to
         - GROUP BY JOB
         - GROUP BY DAY(BIRTHDATE)

4，UDT: 

        select route_num from speed_limits where canada_sl > kph (80)
         mark:WHERE CANADA_SL > 80'. The number 80 is an integer, but CANADA_SL is not an integer, it is a KPH. Therefore, the 80 has to be cast to a KPH value via the 'KPH(80)' expression.

5，Not all views are read-only. Views that are not read-only may have some restrictions on which columns of the view may be updated. For example, a view that contains derived data like a person's age cannot be used to update the derived data.
• In addition:
- You must have the appropriate authorization to insert, update, or delete rows using a view.
- A view that you can use to update data is subject to the referential constraints and table check constraints for the table upon which the view is ultimately based.
- When you use a view to insert a row in a table, the view definition must specify all the columns in the base table that are not nullable and do not have a default value. The row being inserted must contain a value for each of those columns.
- When you use a view to update, you can only update columns that are in the view. The columns of the view form. a projection of the base table. You cannot update derived columns, even if they are in the view.

- When you delete with a view, you are deleting one or more entire base table rows
(the selection of rows from the base table), and not just the part of the table that is visible through the view. Make sure that the data which is not visible through the view is data that you want to delete before you delete through a view.

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22570045/viewspace-615672/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22570045/viewspace-615672/