16-sql-prog - 1 SQL: Programming CPS 116 Introduction to...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: 1 SQL: Programming CPS 116 Introduction to Database Systems 2 Announcements (Thu. Nov. 3) ¡ Homework #3 due next Tuesday ¡ Project milestone #2 due next Thursday 3 Motivation ¡ Pros and cons of SQL ¢ Very high-level, possible to optimize ¢ Not intended for general-purpose computation ¡ Solutions ¢ Augment SQL with constructs from general-purpose programming languages • E.g.: SQL/PSM ¢ Use SQL together with general-purpose programming languages • E.g.: JDBC, embedded SQL ¢ Extend general-purpose programming languages with SQL- like constructs • E.g.: LINQ (Language Integrated Query for .NET), HQL (Hibernate Query Language) 4 Impedance mismatch and a solution ¡ SQL operates on a set of records at a time ¡ Typical low-level general-purpose programming languages operates on one record at a time ) Solution: cursor ¢ Open (a result table): position the cursor before the first row ¢ Get next: move the cursor to the next row and return that row; raise a flag if there is no such row ¢ Close: clean up and release DBMS resources ) Found in virtually every database language/API • With slightly different syntaxes ) Some support more positioning and movement options, modification at the current position, etc. 5 Augmenting SQL: SQL/PSM ¡ PSM = Persistent Stored Modules ¡ CREATE PROCEDURE proc_name ( parameter_declarations ) local_declarations procedure_body ; ¡ CREATE FUNCTION func_name ( parameter_declarations ) RETURNS return_type local_declarations procedure_body ; ¡ CALL proc_name ( parameters ); ¡ Inside procedure body: SET variable = CALL func_name ( parameters ); 6 SQL/PSM example CREATE FUNCTION SetMaxGPA(IN newMaxGPA FLOAT) RETURNS INT-- Enforce newMaxGPA ; return number of rows modified. BEGIN DECLARE rowsUpdated INT DEFAULT 0; DECLARE thisGPA FLOAT;-- A cursor to range over all students: DECLARE studentCursor CURSOR FOR SELECT GPA FROM Student FOR UPDATE;-- Set a flag whenever there is a “not found” exception: DECLARE noMoreRows INT DEFAULT 0; DECLARE CONTINUE HANDLER FOR NOT FOUND SET noMoreRows = 1; … (see next slide) … RETURN rowsUpdated; END 2 7 SQL/PSM example continued-- Fetch the first result row: OPEN studentCursor; FETCH FROM studentCursor INTO thisGPA;-- Loop over all result rows: WHILE noMoreRows <> 1 DO IF thisGPA > newMaxGPA THEN-- Enforce newMaxGPA : UPDATE Student SET Student.GPA = newMaxGPA WHERE CURRENT OF studentCursor;-- Update count: SET rowsUpdated = rowsUpdated + 1; END IF;-- Fetch the next result row: FETCH FROM studentCursor INTO thisGPA; END WHILE; CLOSE studentCursor; 8 Other SQL/PSM features ¡ Assignment using scalar query results ¢ SELECT INTO ¡ Other loop constructs...
View Full Document

This document was uploaded on 01/17/2012.

Page1 / 4

16-sql-prog - 1 SQL: Programming CPS 116 Introduction to...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online