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