Đang chuẩn bị nút TẢI XUỐNG, xin hãy chờ
Tải xuống
Database Systems: The Complete Book- P5: Database Systems and Database Design and Application courses offered at the junior, senior and graduate levels in Computer Science departments. Written by well-known computer scientists, this introduction to database systems offers a comprehensive approach, focusing on database design, database use, and implementation of database applications and database management systems | 376 CHAPTER 8. SYSTEM ASPECTS OF SQL 1 CREATE FUNCTION GetYear t VARCHAR 255 RETURNS INTEGER 2 DECLARE Not_Found CONDITION FOR SQLSTATE 02000 3 DECLARE Too.Many CONDITION FOR SQLSTATE 21000 BEGIN 4 DECLARE EXIT HANDLER FOR Not_Found Too_Many 5 RETURN NULL 6 RETURN SELECT year FROM Movie WHERE title t END Figure 8.14 Handling exceptions in which a single-row select returns other than one tuple Line 6 is the statement that does the work of the function GetYear. It is a SELECT statement that is expected to return exactly one integer since that is what the function GetYear returns. If there is exactly one movie with title t the input parameter of the function then this value will be returned. However if an exception is raised at line 6 either because there is no movie with title t or several movies with that title then the handler is invoked and NULL instead becomes the return-value. Also since the handler is an EXIT handler control next passes to the point after the END. Since that point is the end of the function. GetYear returns at that time with the return-value NULL. 8.2.8 Using PSM Functions and Procedures As we mentioned in Section 8.2.2 we can call a PSM function or procedure from a program with embedded SQL from PSM code itself or from ordinary SQL commands issued to the generic interface. The use of these procedures and functions is the same as in most programming languages with procedures invoked by CALL and functions appearing as part of an expression. We shall give one example of how a function can be called from the generic interface. Ik g Example 8.16 Suppose that our schema includes a module with the function GetYear of Fig. 8.14. Imagine that we are sitting at the generic interface and we want to enter the fact that Denzel Washington was a star of Remember the Titans. However we forget the year in which that movie was made. As long as there was only one movie of that name and it is in the Movie relation we don t have to look it up in a preliminary .