COP 2700 – Data Structures (SQL)

COP 2700 – Data Structures (SQL) Lecture 8 – June 15

Some Comments on Exam and Assignment3 • Exists returns only True and False. If the select in the Exists returns ANY rows, then it is true. • The variable list on set operations must match. • You can join a table with itself • AND is not the same as OR • A select of one variable can then be used in an IN

Announcements • Assignment 4 is due Thursday Night, June 17 by 11:59 PM. • Last Day to Drop is today

What is missing from SQL to make it a true Programming Language? • Variables • @Variable Names, SET • Conditional Statements • If, Else, Switch/Case • Iterative Statements • While • Traverse a “file” (which in this case is a Query Result Set). • Cursor, Open, Fetch, Close • Communicate with Outside World • Print

These items were added to a program language named Transact/SQL • SQL Server specific, although Oracle and MySQL have a similar set of programming commands. • These commands are then used to code: • Scripts – Programs stored as text files and then processed through either the GUI, or oSQL (DOS Shell Script Processor) • Functions – Small programs that accept parameter input and output a result. • Stored Procedures – Programs that can be called from other programs, Management Student or oSQL • Triggers – Small programs “fired” when certain actions are taken. Usually involved in ensuring data integrity.

Quick Look at oSQL • Go to a DOS prompt • Enter • oSQL -? • then • oSQL –S localhost\PBSC U=sa

Transact SQL Basics BEGIN DECLARE @Variable1 Type2; DECLARE @Variable2 Type2; Do Some Work SET IF/ELSE While Loop SELECT PRINT Exception Handling END; GO

BEGIN…END • Defines Blocks of code • Can be stand alone or can distinguish code to be executed for IF/ELSE or While Loops

Some Simple Examples Print the First_Name and Last_Name of Student Z123: Declare @sName Varchar(60); BEGIN SELECT @sname = (Last_Name + ', ' + First_name) FROM Student WHERE Student_ID = 'Z123'; Print @sName; END; GO Note: Each command or declare ends with a semi-colon. The block of commands ends with a GO Print is an internal function that writes stuff to an output area.

Another Example Print the instructor’s name for Instructor_ID “Bradley” and then print the Semesters, Courses and Sections that he taught: DECLARE @sName Varchar(60); DECLARE @Semester varchar(10), @Section Numeric, @Course_ID varchar(10); BEGIN SELECT @sName = (Last_Name + ', ' + First_name) FROM Instructor WHERE Instructor_ID = 'Bradley'; Print @Sname; declare Courses cursor for (SELECT Semester, Course_ID, Section FROM Schedule WHERE Instructor_ID = 'Bradley'); OPEN Courses; FETCH Courses into @Semester, @Course_ID, @Section; WHILE @@FETCH_STATUS = 0 BEGIN Print(@Semester + ' ' + @Course_ID + ' ' + cast(@Section as varchar(5))); FETCH Courses into @Semester, @Course_ID, @Section; END; CLOSE Courses; DEALLOCATE Courses; END;

Stuff Not Database Related Simple Counting Loop (SQLServer does NOT have a FOR loop!!) DECLARE @Ctr Integer; BEGIN SET @Ctr = 1 WHILE @Ctr < 10 BEGIN PRINT @Ctr; SET @CTR = @CTR + 1 END; END;

Selects in Procedures • Scalar Selects • Returns One Row and can then directly have the values of the columns assigned to variables • Cursors • Returns a result set that one can then transverse through • Direct Select Statements • Like Regular Old Selects. Write the output out to the console or to a grid. • Let’s add a select to our previous script.

Function Calls • Must Return a value • Basic Function Creation CREATE (ALTER) FUNCTION Example (@Number Numeric) RETURNS Numeric AS BEGIN DECLARE @DoubleNumber Numeric; SET @DoubleNumber = 2 * @Number; Return @DoubleNumber; END;

Get Age Function CREATE FUNCTION GETAGE(@Birthdate Date) RETURNS NUMERIC AS BEGIN Declare @Age integer, @Year integer, @Day integer, @TodayYear integer, @TodayDay integer; Declare @Month integer, @TodayMonth integer; Set @Year = Year(@BirthDate); Set @TodayYear = Year(GETDATE()); Set @AGE = @TodayYear - @Year; Set @Month = Month(@BirthDate); SET @TodayMonth = Month(GETDATE()); IF @Month > @TodayMonth Begin Set @AGE = @AGE - 1; Return @Age; END; IF @Month < @TodayMonth Begin Return @Age; END; Set @Day = Day(@birthdate); Set @TodayDay = Day(GETDATE()); IF @Day > @TodayDay Begin Set @AGE = @AGE - 1; END; Return @Age; END; GO select dbo.GETAGE(birth_date), * from student

Function with Database Call CREATE FUNCTION StudentName (@Student_ID as Varchar(10)) RETURNS Varchar(50) AS BEGIN DECLARE @Name Varchar(60) = 'Not Found'; SELECT @Name = (Last_Name + ', ' + First_Name) FROM Student WHERE Student_ID = @Student_ID; RETURN @Name; END;

Stored Procedure • Like a Function, but is not required to return anything. • There can be “OUTPUT” parameters, but normally it communicates back to the calling procedure with Error Codes and “Throws”. • Can be called directly from Transact SQL or Management Studio

Simple Stored Procedure CREATE PROCEDURE InsertInstructor @InstrId varchar,@LastName varchar, @FirstName varchar, @Office varchar, @City varchar, @Message varchar(100) output as DECLARE @n integer; BEGIN SELECT @n=COUNT(*) FROM Instructor WHERE Instructor_ID = @InstrId; IF (@n > 0) BEGIN SET @Message ='Instructor ' + @InstrId +' already in table, insert aborted'; RETURN; END; INSERT INTO Instructor (Instructor_ID, Last_Name, First_Name, Office, City) VALUES (@InstrID, @LastName, @FirstName, @Office, @City); SET @Message = 'Insert Complete for Instructor ' + @InstrId; END;

Calling a Stored Procedure • use registration • go • declare @Message varchar(100); • InsertInstructor 'Jones','Jones','Tom','EE903','Miami',@message output; • print @message; • go

For WednesdayTriggers • Small Transact SQL programs that are “Triggered” when the RDMS processes a change to a table • Used to maintain constraints, database consistency • Used to automatically create log type entries into other tables for security.

For Tonight • We Need to write a function that will return the GPA for a student. • Let’s design the function • What is the input? • What is the output? • What database involvement do we have? • What is the processing?

COP 2700 – Data Structures (SQL)