REM
3gl_4gl.sql
REM
Chapter 1, Oracle8i
Advanced PL/SQL Programming
REM
by Scott Urman
REM This block shows both 3GL and 4GL statements.
DECLARE
/* Declare variables which will be used in SQL statements */
v_NewMajor VARCHAR2(10) := 'History';
v_FirstName VARCHAR2(10) := 'Scott';
v_LastName VARCHAR2(10) := 'Urman';
BEGIN
/* Update the students table. */
UPDATE students
SET major = v_NewMajor
WHERE first_name = v_FirstName
AND last_name = v_LastName;
/* Check to see if the record was found. If not, then we need
to insert this record. */
IF SQL%NOTFOUND THEN
INSERT INTO students (ID, first_name, last_name, major)
VALUES (student_sequence.NEXTVAL, v_FirstName, v_LastName,
v_NewMajor);
END IF;
END;
/
ROLLBACK;
REM AddNewStudent.sql
REM Chapter 4, Oracle8i
Advanced PL/SQL Programming
REM by Scott Urman
REM This script demonstrates a stored procedure and how to call it.
CREATE OR REPLACE PROCEDURE AddNewStudent (
p_FirstName students.first_name%TYPE,
p_LastName students.last_name%TYPE,
p_Major students.major%TYPE) AS
BEGIN
-- Insert a new row in the students table. Use
-- student_sequence to generate the new student ID, and
-- 0 for current_credits.
INSERT INTO students (ID, first_name, last_name,
major, current_credits)
VALUES (student_sequence.nextval, p_FirstName, p_LastName,
p_Major, 0);
END AddNewStudent;
/
BEGIN
AddNewStudent('Zelda', 'Zudnik', 'Computer Science');
END;
/
ROLLBACK;
REM varray.sql
REM Chapter 14, Oracle8i
Advanced PL/SQL Programming
REM by Scott Urman
REM This block illustrates some varray declarations.
DECLARE
-- Some valid varray types.
-- This is a list of numbers, each of which is constrained to
-- be not null.
TYPE NumberList IS VARRAY(10) OF NUMBER(3) NOT NULL;
-- A list of PL/SQL records.
TYPE StudentList IS VARRAY(100) OF students%ROWTYPE;
-- A list of Class objects.
TYPE ClassList IS VARRAY(20) OF Class;
BEGIN
NULL;
END;
/
Exit to : Oracle Hints and Tips