You should be familiar with using SQL*Plus to execute SQL statements (if not, then refer to another book in the Prentice Hall Interactive Oracle Series on this topic, Morrison/Rishchert's Oracle SQL Interactive Workbook, 2nd ed., 2004). There are a few key differences between executing SQL statements in SQL*Plus and executing PL/SQL statements in SQL*Plus. You will be introduced to these differences so that you can work with the exercises in this book.
You can end an SQL Command in SQL*Plus in one of three ways:
The semicolon (;) tells SQL*Plus that you want to run the command that you have just entered. You type the semicolon at the end of the SELECT statement and then press return. SQL*Plus will process what is in the SQL Buffer (described next).
SQL> SELECT sysdate 2 FROM dual 3 ; SYSDATE --------- 28-JUL-02 SQL>
The SQL Buffer
SQL*Plus will store the SQL command or PL/SQL block that you have most recently entered in an area of memory known as the SQL Buffer. The SQL Buffer will remain unchanged until you enter a new command or exit your SQL*Plus session. You can easily edit the contents of the SQL Buffer by typing the EDIT command at the SQL prompt. The default text editor will open with the contents of the SQL Buffer. You can edit and save the file and then exit the editor. This will cause the contents of the SQL Buffer to change to your last saved version.
SQL*Plus commands such as SET SERVEROUTPUT ON are not captured into the SQL Buffer, nor does SQL*Plus store the semicolon or the forward slash you type to execute a command in the SQL buffer.
When you create stored procedures, functions, or packages, you begin with the CREATE command. When you begin a PL/SQL block, you start by entering the word DECLARE or BEGIN. Typing either BEGIN, DECLARE, or CREATE will put the SQL*Plus session into PL/SQL mode.
Running PL/SQL Blocks in SQL*Plus
Once you are in PL/SQL mode, you will not be able to end the block in the same manner that you ended a SQL block. The semicolon (;) can be used multiple times in a single PL/SQL block; thus when you end a line with a semicolon you will not terminate the block. You can terminate the PL/SQL block in the SQL Buffer by entering a period (.). This will end the block and leave the block in the SQL Buffer, but it will not execute it. At this point you have a choice of typing the EDIT command to edit the block or executing it with a forward slash (/) or a SQL*Plus command RUN.
You may enter and execute a PL/SQL subprogram as follows:
SQL> BEGIN 2 DBMS_OUTPUT.PUT_LINE ('This is a PL/SQL Block'); 3 END; 4 . SQL> / This is a PL/SQL Block PL/SQL procedure successfully completed.
If want to run a script file at a later date, you must remember to terminate it with a period (.) and/or forward slash (/) before saving it on your computer. If you simply want to put the code into the SQL Buffer and then execute it, you can end the script with a forward slash (/).
You should terminate PL/SQL blocks stored in the script file with the period if you want to put the code in the SQL Buffer. You should end the script with forward slash (/) if you want the PL/SQL code in the file to execute.
The failure to end your PL/SQL block with a period (.) and/or a forward slash (/) will prevent your block from executing.
About the Sample Schema
The STUDENT schema contains tables and other objects meant to keep information about a registration and enrollment system for a fictitious university. There are ten tables in the system that store data about students, courses, instructors, and so on. In addition to storing contact information (addresses and telephone numbers) for students and instructors, and descriptive information about courses (costs and prerequisites), the schema also keeps track of the sections for particular courses, and the sections in which students have enrolled.
The SECTION is one of the most important tables in the schema because it stores data about the individual sections that have been created for each course. Each section record also stores information about where and when the section will meet and which instructor will teach the section. The section table is related to the COURSE and INSTRUCTOR tables.
The ENROLLMENT table is equally important because it keeps track of which students have enrolled in which sections. Each enrollment record also stores information about the student's grade and enrollment date. The enrollment table is related to the STUDENT and SECTION tables.
The schema also has a number of other tables that manage grading for each student in each section.