Database Systems: The Complete Book- P5

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
Nội dung trích xuất từ tài liệu:
Database Systems: The Complete Book- P5 3 76 CHAPTER 8. SYSTEM ASPECTS OF SQL 8.2. PROCEDURES STORED IN THE SCHEAIA 377 1) CREATE FUNCTION GetYear (t VARCHAR(255) ) RETURNS INTEGER Since GetYear returns NULL if there is not a unique movie by the name of Remember the Titans, it is possible that this insertion will have NULL in the 2) DECLARE Not-Found CONDITION FOR SQLSTATE 02000; middle component. 0 3) DECLARE Too-Many CONDITION FOR SQLSTATE 21000; 8.2.9 Exercises for Section 8.2 BEGIN Exercise 8.2.1 : Using our running movie database: 4) DECLARE EXIT HANDLER FOR Not-Found, Too-Many 5) RETURN NULL ; Movie(title, year, length, incolor, studiolame, producerC#) 6) RETURN (SELECT year FROM Movie WHERE title = t); StarsIn(movieTitle, movieyear, starName) END ; MovieStar(name, address, gender, birthdate) MovieExec(name, address, cert#, networth) Figure 8.14: Handling exceptions in which a single-row select returns other than Studio(name, address, presC#) one tuple write PSM procedures or functions t o perform the following tasks: Line (6) is the statement that does the work of the function GetYear. It is * a) Given the name of a movie studio, produce the net worth of its president. a SELECT statement that is expected t o return exactly one integer, since that is * b) Given a name and address, return 1 if the person is a movie star but not what the function GetYear returns. If there is exactly one movie with title t (the an executive, 2 if the person is a n executive but not a star, 3 if both, and input parameter of the function), then this value will be returned. However. if 4 if neither. a n 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 *! c) Given a studio name. assign t o output parameters the titles of the two becomes the return-value. Also, since the handler is an EXIT handler, control longest movies by that studio. Assign NULL to one or both parameters if next passes to the point after the END. Since that point is the end of the funrtion. there is no such movie (e.g., if there is only one m o ~ i e a studio, there by GetYear returns a t that time, with the return-value NULL. 0 is no Lsecond-longesti) . ! d) Given a star name, find the earliest (lowest year) movie of more than 120 8.2.8 Using PSM Functions and Procedures minutes length in u-hich they appeared. If there is no such movie, return As we mentioned in Section 8.2.2, we can call a PSM function or procedtire the year 0. from a program with embedded SQL, from PSLI code itself, or from ordinary e) Given an address. find the name of the unique star with that address if SQL commands issued to the generic interface. The use of these procedures there is exactly one, and return NULL if there is none or more than one. and functions is the same as in most programming languages, with procedures invoked by CALL, and functions appearing as past of an expression. K e shall f) Given the name of a star, delete them from Moviestar and delete all their give one example of how a function can be called from the generic interface. movies from StarsIn and Movie. ...