SQL Programming

Short Description
SQL Programming. Example schema:. CREATE TABLE Student (SID INTEGER PRIMARY KEY, … Use SQL together with a general-purpose programming language: …

Website: infolab.stanford.edu | Filesize: 96kb

Content
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…