10-Lecture 10数据库课件.pptVIP

Raise the prices of books by 1.1 times until the average price is greater than 500; print a message if the maximal price is greater than 800 CONTINUE Statement WHILE (SELECT AVG(Price) FROM Book) = 500 BEGIN UPDATE Book SET Price = Price * 1.1; IF (SELECT MAX(Price) FROM Book) = 800 CONTINUE; PRINT ‘Max price 800 !’; END It quits the stored procedure, and return to where it is called integer_expression An integer returned to the calling script Usually it indicates the execution status of the procedure (4) RETURN RETURN [integer_expression]; (4) RETURN CREATE PROCEDURE check_professor @name varchar(20) AS IF(SELECT Position FROM Teacher WHERE Name = @name) = ‘Professor’ RETURN 1 ELSE RETURN 0 GO DECLARE @status int; EXECUTE @status = check_professor ‘William’; A SELECT statement returns a set of rows in its result at once Sometimes we want to process the result row by row Cursor is a tool that allows us to access one row at a time in the result of a SELECT statement 10.4 Cursor Syntax Declaring a Cursor DECLARE cursor_name CURSOR FOR select_statement DECLARE student_cursor CURSOR FOR SELECT * FROM Student; Open a cursor Close a cursor Deallocate a cursor Cursor Operations OPEN cursor_name; CLOSE cursor_name; DEALLOCATE cursor_name; Syntax: fetch a row and mark it as the current row Read Data from a Cursor FETCH [NEXT|FIRST|LAST|ABSOLUTE n] FROM cursor_name [INTO variable_list]; FETCH NEXT FROM student_cursor; Syntax Update Data Using a Cursor UPDATE table_name SET column_name = value WHERE CURRENT OF cursor_name; UPDATE Student SET Name = ‘Alice’ WHERE CURRENT OF student_cursor; Syntax Delete Data Using a Cursor DELETE FROM table_name WHERE CURRENT OF cursor_name; DELETE FROM Student WHERE CURRENT OF student_cursor; Loop with Cursors DECLARE cur CURSOR FOR SELECT Title, Price FROM Book; OPEN cur; DECLARE @title varchar(50), @price int; FETCH NEXT FROM cur INTO @title, @price; WHILE(@@FETCH_STATUS -1) BEGIN PRINT @title + ‘: