Download
1 / 14
140 likes | 283 Views
PL/SQL. Creating an Application Program. PL/SQL. Procedural Language extension to SQL (PL/SQL) Procedural constructs such as variables, and types Selective and iterative constructs to SQL (ie. IF, LOOP). Query the Database. Display the ID, Name and Major Description for student 111
E N D
PL/SQL Creating an Application Program
PL/SQL • Procedural Language extension to SQL (PL/SQL) • Procedural constructs such as variables, and types • Selective and iterative constructs to SQL (ie. IF, LOOP)
Query the Database • Display the ID, Name and Major Description for student 111 SELECT ID, Name, Description FROM Student, Major WHERE Student.Major = Major.Code AND ID = 111; • This query will only display the information for student 111. • This lesson demonstrate how to prompt the user to enter an id and display the information for that student.
Receiving Input & Storing Results in Variables • Receive the ID from the user and store the values returned in temporary variables: SELECT ID, Name, Description INTO Temp_ID, Temp_Name, Temp_Description FROM Student, Major WHERE Student.Major = Major.Code AND ID = &Temp_ID; The ampersand before the variable name indicates to accept input
Display Results • Turn on setting to display output on screen SET SERVEROUTPUT ON • Print output using PUT_LINE function DBMS_OUTPUT.PUT_LINE(‘Hello’); • Display Temporary Variables DBMS_OUTPUT.PUT_LINE(Temp_ID); DBMS_OUTPUT.PUT_LINE(Temp_Name); • Concatenate with || DBMS_OUTPUT.PUT_LINE(Temp_ID||’—’||Temp_Name);
Creating Variables • To declare temporary variables – specify Name and Data Type Price Number(8,2); • Use the Data Type of an Existing Column with %TYPE Temp_ID Student.ID%TYPE; • In the book they use I, for input, to designate temporary variables. I_ID Student.ID%TYPE;
PL/SQL Program Structure • Turn SERVEROUTPUT ON • DECLARE variables • Enclose statements with in a BEGIN – END; block • Store program in SQL file (filename.sql) • Make the last line of the file be / (slash) which indicates to run the previous command • Run script file • SQL> @filename
Putting It All Together SET SERVEROUTPUT ON DECLARE I_ID Student.ID%TYPE; I_Name Student.Name%TYPE; I_Description Major.Description%TYPE; BEGIN SELECT ID, Name, Description I INTO I_ID, I_Name, I_Description FROM Student, Major WHERE Student.Major = Major.Code AND ID = &I_ID; DBMS_OUTPUT.PUT_LINE(I_ID||' '||I_Name|| ' '||I_Description); END; / studentinfo.sql
Displaying A Collection Of Records • Display the ID and Name of students named ‘Cody’ SELECT ID, Name FROM Student WHERE Name = ‘Cody’;
Displaying A Collection Of Records • Compare Name to user input SELECT ID, Name FROM Student WHERE Name = ‘&Temp_Name’;
Displaying a Collection of Records • A CURSOR is a pointer to a row in a collection of rows retrieved by a SQL command • Enables you to process the set of retrieved rows as if they were recorded in a sequential file • LOOP until there are no records left to process • FETCH each record • Display the fetched record
Create a Cursor CURSOR STUDGROUP IS SELECT ID, Name FROM Student WHERE Name = '&Temp_Name';
Loop to Process Each Record LOOP FETCH STUDGROUP INTO Temp_ID, Temp_Name; EXIT WHEN STUDGROUP%NOTFOUND; DBMS_OUTPUT.PUT_LINE(I_ID||' ‘|| I_Name); END LOOP;
Open Cursor For Processing • Enclose LOOP within commands to OPEN and CLOSE Cursor BEGIN OPEN STUDGROUP; LOOP FETCH STUDGROUP INTO I_ID, I_Name; EXITWHEN STUDGROUP%NOTFOUND; DBMS_OUTPUT.PUT_LINE(I_ID || ' ' || I_Name); ENDLOOP; CLOSE STUDGROUP; END;
