SQL Programming.pdf

CS145 Lecture Notes #10 SQL Programming Example schema: CREATE TABLE Student (SID INTEGER PRIMARY KEY, name CHAR(30), age INTEGER, GPA FLOAT); CREATE TABLE Take (SID INTEGER, CID CHAR(10), PRIMARY KEY(SID, CID)); CREATE TABLE Course (CID CHAR(10) PRIMARY KEY, title VARCHAR(100) UNIQUE); Motivation Pros and cons of SQL: Very high-level, possible to optimize Not tuned to support general-purpose computation Oracle as a calculator? SELECT 142857*3 FROM DUAL; Strictly less expressive than general-purpose languages SQL2 has no recursion and cannot even compute factorial! Solutions: Augment SQL: Oracle’s PL/SQL Use SQL together with a general-purpose programming language: embedded SQL, dynamic SQL Oracle PL/SQL Basics Rough form of a PL/SQL program: DECLARE BEGIN END; . RUN; DECLARE section is optional . and RUN end the program and execute it Jun Yang 1 CS145 Spring 1999 Example: go through students 142–857 and set all GPA’s under 4.0 to 4.0 DECLARE thisSID Student.SID%TYPE; thisGPA Student.GPA%TYPE; BEGIN thisSID := 142; LOOP EXIT WHEN (thisSID 857); SELECT GPA INTO thisGPA FROM Student WHERE SID = thisSID; IF (thisGPA 4.0) THEN UPDATE Student SET GPA = 4.0 WHERE SID = thisSID; END IF; thisSID := thisSID + 1; END LOOP; END; . RUN; Basic features: Local variable: Use %TYPE to match its type to a column in the schema Use := for assignment; = for comparison Branch: IF (...) THEN ... ELSE ... END IF; Loop: LOOP ... EXIT WHEN (...); ... END LOOP; The usual data modification statements: INSERT, DELETE, UPDATE Single-row SELECT: SELECT ... INTO ... FROM ...; Oracle raises an exception if SELECT returns no rows or more than one row Cursors Inside a PL/SQL program, the result of a SELECT must go somewhere: If SELECT returns one row, it can go INTO a variable What if SELECT returns multiple rows? Cursor: a variable that runs through the result of a SELECT, row by row Declare by: CURSOR IS ; Use inside a cursor loop: Fetch one result row at a time: FETCH INTO ; Break the loop when