16-sql-prog-notes

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

Info iconThis preview shows pages 1–4. 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

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) 2 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 3 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;...
View Full Document

Page1 / 8

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

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

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