16-sql-prog - Announcements(Thu Nov 3 SQL Programming CPS...

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

View Full Document Right Arrow Icon
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
Image of page 1

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

View Full Document Right Arrow Icon
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 SELECTINTO Other loop constructs
Image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern