wORACLE PL/SQL vO~Ox


Chapter1
p.2
DELETE FROM students
  WHERE major = 'Nutrition';


  
LOOP over each student record
  IF this record has major = 'Nutrition' THEN
    DELETE this record;
  END IF;
END LOOP;


p.3
DECLARE
  /* SQLŎgpϐ̐錾 */
  v_NewMajor VARCHAR2(10) := 'History'; 
  v_FirstName VARCHAR2(10) := 'Scott'; 
  v_LastName VARCHAR2(10) := 'Urman'; 
BEGIN
  /* \students̍XV */
  UPDATE students
    SET major = v_NewMajor
    WHERE first_name = v_FirstName
    AND last_name = v_LastName; 
  /* R[hA`FbNDȂꍇɂ́A
     YR[h}KvD */
  IF SQL%NOTFOUND THEN
    INSERT INTO students (ID, first_name, last_name, major) 
      VALUES (10020, v_FirstName, v_LastName, v_NewMajor); 
  END IF; 
END;


p.6
DECLARE
  v_ErrorCode NUMBER;          -- G[R[h
  v_ErrorMsg  VARCHAR2(200);   -- G[̃bZ[WeLXg
  v_CurrentUser VARCHAR2(8);   -- ݂̃f[^x[X[U
  v_Information VARCHAR2(100); -- G[Ɋւ
BEGIN
 .... 
EXCEPTION
  WHEN OTHER THEN
  v_ErrorCode := SQLCODE; 
  v_ErrorMsg := SQLERRM; 
  v_CurrentUser := USER; 
  -- gݍ݊֐gpāAOϐɒl
  v_Information := 'Error encountered on ' ||
    TO_CHAR(SYSDATE) || ' by database user ' || v_CurrentUser; 
  -- log_tableɃObZ[W}
  INSERT INTO log_table (code, message, info) 
    VALUES (v_ErrorCode, v_ErrorMsg, v_information); 
END; 


DECLARE
  v_StudentName  VARCHAR2(20);
  v_CurrentDate  DATE;
  v_NumberCredits NUMBER(3);


p.7
DECLARE
  v_LoopCounter BINARY_INTEGER;
  v_CurrentlyRegistered BOOLEAN;


DECLARE
  TYPE t_StudentRecord IS RECORD (
    FirstName  VARCHAR2(10),
    LastName   VARCHAR2(10),
    CurrentCredits NUMBER(3)
  );
  v_Student t_StudentRecord;


DECLARE
  v_LoopCounter BINARY_INTEGER := 1; 
BEGIN
  LOOP
    INSERT INTO temp_table (num_col) 
      VALUES (v_LoopCounter); 
    v_LoopCounter := v_LoopCounter + 1; 
    EXIT WHEN v_LoopCounter > 50; 
  END LOOP; 
END;


BEGIN
  FOR v_LoopCounter IN 1..50 LOOP
    INSERT INTO temp_table (num_col) 
      VALUES (v_LoopCounter); 
  END LOOP; 
END;


p.8
DECLARE
  v_FirstName VARCHAR2(20); 
  v_LastName  VARCHAR2(20); 
  -- J[\錾DA̍sԂ߂SQL`
  CURSOR c_Students IS
    SELECT first_name, last_name
      FROM students; 
BEGIN
  -- J[\̊Jn
  OPEN c_Students; 
  LOOP
    -- Pso
    FETCH c_Students INTO v_FirstName, v_LastName; 
    -- ׂĂ̍soIA[vI
    EXIT WHEN c_Students%NOTFOUND; 
    /* ŁAf[^ */
  END LOOP; 
  -- ̏I
  CLOSE c_Students; 
END; 


p.10
ڑ:
Personal Oracle7 Release 7.3.2.3.1 - Production Release
With the distributed and replication options
PL/SQL Release 2.3.2.3.0 - Production


ڑ:
Oracle7 Server Release 7.2.2.3.0 - Production Release
With the distributed, replication and parallel query options
PL/SQL Release 2.2.2.3.0 - Production


p.11
CREATE SEQUENCE student_sequence
  START WITH 10000
  INCREMENT BY 1;


p.11-12
CREATE TABLE students (
  id               NUMBER(5) PRIMARY KEY,
  first_name       VARCHAR2(20),
  last_name        VARCHAR2(20),
  major            VARCHAR2(30),
  current_credits  NUMBER(3)
  );

INSERT INTO students
  (id, first_name, last_name, major, current_credits)
  VALUES (10000, 'Scott', 'Smith', 'Computer Science', 0);

INSERT INTO students
  (id, first_name, last_name, major, current_credits)
  VALUES (10001, 'Margaret', 'Mason', 'History', 0); 

INSERT INTO students
  (id, first_name, last_name, major, current_credits) 
  VALUES (10002, 'Joanne', 'Junebug', 'Computer Science', 0); 

INSERT INTO students
  (id, first_name, last_name, major, current_credits) 
  VALUES (10003, 'Manish', 'Murgratroid', 'Economics', 0); 

INSERT INTO students
  (id, first_name, last_name, major, current_credits) 
  VALUES(10004, 'Patrick', 'Poll', 'History', 0); 

INSERT INTO students
  (id, first_name, last_name, major, current_credits)
  VALUES (10005, 'Timothy', 'Taller', 'History', 0);

INSERT INTO students
  (id, first_name, last_name, major, current_credits)
  VALUES (10006, 'Barbara', 'Blues', 'Economics', 0);

INSERT INTO students
  (id, first_name, last_name, major, current_credits)
  VALUES (10007, 'David', 'Dinsmore', 'Music', 0);

INSERT INTO students
  (id, first_name, last_name, major, current_credits)
  VALUES (10008, 'Ester', 'Elegant', 'Nutrition', 0);

INSERT INTO students
  (id, first_name, last_name, major, current_credits)
  VALUES (10009, 'Rose', 'Riznit', 'Music', 0);

INSERT INTO students
  (id, first_name, last_name, major, current_credits)
  VALUES (10010, 'Rita', 'Razmataz', 'Nutrition', 0);


p.12
CREATE TABLE major_stats (
  major          VARCHAR2(30),
  total_credits  NUMBER,
  total_students NUMBER);


p.12-13
CREATE TABLE rooms (
  room_id          NUMBER(5) PRIMARY KEY,
  building         VARCHAR2(15),
  room_number      NUMBER(4),
  number_seats     NUMBER(4),
  description      VARCHAR2(50)
  );

INSERT INTO rooms
  (room_id, building, room_number, number_seats, description)
  VALUES (99999, 'Building 7', 310, 1000, 'Large Lecture Hall');

INSERT INTO rooms
  (room_id, building, room_number, number_seats, description)
  VALUES (99998, 'Building 6', 101, 500, 'Small Lecture Hall');

INSERT INTO rooms
  (room_id, building, room_number, number_seats, description)
  VALUES (99997, 'Building 6', 150, 50, 'Discussion Room A');

INSERT INTO rooms
  (room_id, building, room_number, number_seats, description)
  VALUES (99996, 'Building 6', 160, 50, 'Discussion Room B');

INSERT INTO rooms
  (room_id, building, room_number, number_seats, description)
  VALUES (99995, 'Building 6', 170, 50, 'Discussion Room C');

INSERT INTO rooms
  (room_id, building, room_number, number_seats, description)
  VALUES (99994, 'Music Building', 100, 10, 'Music Practice Room');

INSERT INTO rooms
  (room_id, building, room_number, number_seats, description)
  VALUES (99993, 'Music Building', 200, 1000, 'Concert Room');

INSERT INTO rooms
  (room_id, building, room_number, number_seats, description)
  VALUES (99992, 'Building 7', 300, 75, 'Discussion Room D');

INSERT INTO rooms
  (room_id, building, room_number, number_seats, description)
  VALUES (99991, 'Building 7', 310, 50, 'Discussion Room E');


p.13-14
CREATE TABLE classes(
  department       CHAR(3),
  course           NUMBER(3),
  description      VARCHAR2(2000),
  max_students     NUMBER(3),
  current_students NUMBER(3),
  num_credits      NUMBER(1),
  room_id          NUMBER(5),
  CONSTRAINT classes_department_course
    PRIMARY KEY (department, course),
  CONSTRAINT classes_room_id
    FOREIGN KEY (room_id) REFERENCES rooms (room_id)
  );

INSERT INTO classes
  (department, course, description, max_students,
   current_students, num_credits, room_id)
  VALUES ('HIS', 101, 'History 101', 30, 0, 4, 99999);

INSERT INTO classes
  (department, course, description, max_students,
   current_students, num_credits, room_id)
  VALUES ('HIS', 301, 'History 301', 30, 0, 4, 99995);

INSERT INTO classes
  (department, course, description, max_students,
   current_students, num_credits, room_id)
  VALUES ('CS', 101, 'Computer Science 101', 50, 0, 4, 99998);

INSERT INTO classes
  (department, course, description, max_students,
   current_students, num_credits, room_id)
  VALUES ('ECN', 203, 'Economics 203', 15, 0, 3, 99997);

INSERT INTO classes
  (department, course, description, max_students,
   current_students, num_credits, room_id)
  VALUES ('CS', 102, 'Computer Science 102', 35, 0, 4, 99996);

INSERT INTO classes
  (department, course, description, max_students,
   current_students, num_credits, room_id)
  VALUES ('MUS', 410, 'Music 410', 5, 0, 3, 99994);

INSERT INTO classes
  (department, course, description, max_students,
   current_students, num_credits, room_id)
  VALUES ('ECN', 101, 'Economics 101', 50, 0, 4, 99992);

INSERT INTO classes
  (department, course, description, max_students,
   current_students, num_credits, room_id)
  VALUES ('NUT', 307, 'Nutrition 307', 20, 0, 4, 99991);


p.15-16
CREATE TABLE registered_students (
  student_id NUMBER(5) NOT NULL,
  department CHAR(3)   NOT NULL,
  course     NUMBER(3) NOT NULL,
  grade      CHAR(1),
  CONSTRAINT rs_grade
    CHECK (grade IN ('A', 'B', 'C', 'D', 'E')),
  CONSTRAINT rs_student_id
    FOREIGN KEY (student_id) REFERENCES students (id),
  CONSTRAINT rs_department_course
    FOREIGN KEY (department, course)
    REFERENCES classes (department, course)
  );

INSERT INTO registered_students
  (student_id, department, course, grade)
  VALUES (10000, 'CS', 102, 'A');

INSERT INTO registered_students
  (student_id, department, course, grade)
  VALUES (10002, 'CS', 102, 'B');

INSERT INTO registered_students
  (student_id, department, course, grade)
  VALUES (10003, 'CS', 102, 'C');

INSERT INTO registered_students
  (student_id, department, course, grade)
  VALUES (10000, 'HIS', 101, 'A');

INSERT INTO registered_students
  (student_id, department, course, grade)
  VALUES (10001, 'HIS', 101, 'B');

INSERT INTO registered_students
  (student_id, department, course, grade)
  VALUES (10002, 'HIS', 101, 'B');

INSERT INTO registered_students
  (student_id, department, course, grade)
  VALUES (10003, 'HIS', 101, 'A');

INSERT INTO registered_students
  (student_id, department, course, grade)
  VALUES (10004, 'HIS', 101, 'C');

INSERT INTO registered_students
  (student_id, department, course, grade)
  VALUES (10005, 'HIS', 101, 'C');

INSERT INTO registered_students
  (student_id, department, course, grade)
  VALUES (10006, 'HIS', 101, 'E');

INSERT INTO registered_students
  (student_id, department, course, grade)
  VALUES (10007, 'HIS', 101, 'B');

INSERT INTO registered_students
  (student_id, department, course, grade)
  VALUES (10008, 'HIS', 101, 'A');

INSERT INTO registered_students
  (student_id, department, course, grade)
  VALUES (10009, 'HIS', 101, 'D');

INSERT INTO registered_students
  (student_id, department, course, grade)
  VALUES (10010, 'HIS', 101, 'A');

INSERT INTO registered_students
  (student_id, department, course, grade)
  VALUES (10008, 'NUT', 307, 'A');

INSERT INTO registered_students
  (student_id, department, course, grade)
  VALUES (10010, 'NUT', 307, 'A');

INSERT INTO registered_students
  (student_id, department, course, grade)
  VALUES (10009, 'MUS', 410, 'B');

INSERT INTO registered_students
  (student_id, department, course, grade)
  VALUES (10006, 'MUS', 410, 'E');


p.17
CREATE TABLE RS_audit (
  change_type    CHAR(1)     NOT NULL,
  changed_by     VARCHAR2(8) NOT NULL,
  timestamp      DATE        NOT NULL,
  old_student_id NUMBER(5),
  old_department CHAR(3),
  old_course     NUMBER(3),
  old_grade      CHAR(1),
  new_student_id NUMBER(5),
  new_department CHAR(3),
  new_course     NUMBER(3),
  new_grade      CHAR(1)
  );


p.17
CREATE TABLE log_table (
  code             NUMBER,
  message          VARCHAR2(200),
  info             VARCHAR2(100)
  );


CREATE TABLE temp_table (
  num_col    NUMBER,
  char_col   VARCHAR2(60)
  );


p.18
CREATE TABLE debug_table (
  linecount  NUMBER,
  debug_str  VARCHAR2(100)
  );


Chapter2
p.20
DECLARE
  /* ubNŎgpϐ̐錾 */
  v_Department       CHAR(3)  := 'ECN'; 
  v_Course           NUMBER(3) := 203; 
  v_Description      VARCHAR2(20) := 'Economics 203'; 
  v_MaxStudents      NUMBER := 15; 
  v_CurrentStudents  NUMBER := 0; 
  v_NumCredits       NUMBER := 3; 
  v_RoomID           NUMBER := 99997; 
BEGIN
  /* A̕ϐ̒lgpāA\classesɍsPsǉ */
  INSERT INTO classes (department, course, description, max_students, 
                       current_students, num_credits, room_id) 
    VALUES (v_Department, v_Course, v_Description, v_MaxStudents, 
            v_CurrentStudents, v_NumCredits, v_RoomID); 
END;


p.21
DECLARE
  /* ubNŎgpϐ̐錾 */
  v_Department       CHAR(3)  := 'ECN'; 
  v_Course           NUMBER(3) := 203; 
  v_Description      VARCHAR2(20) := 'Economics 203'; 
  v_MaxStudents      NUMBER := 15; 
  v_CurrentStudents  NUMBER := 0; 
  v_NumCredits       NUMBER := 3; 
  v_RoomID           NUMBER := 99997; 
BEGIN
  /* A̕ϐ̒lgpāA\classesɍsPsǉ */
  INSERT INTO classes (department, course, description, max_students, 
                       current_students, num_credits, room_id) 
    VALUES (v_Department, v_Course, v_Description, v_MaxStudents, 
            v_CurrentStudents, v_NumCredits, v_RoomID); 
END l_AddNewRow;


CREATE PROCEDURE AddNewRow AS
  /* ubNŎgpϐ̐錾 */
  v_Department       CHAR(3)  := 'ECN'; 
  v_Course           NUMBER(3) := 203; 
  v_Description      VARCHAR2(20) := 'Economics 203'; 
  v_MaxStudents      NUMBER := 15; 
  v_CurrentStudents  NUMBER := 0; 
  v_NumCredits       NUMBER := 3; 
  v_RoomID           NUMBER := 99997; 
BEGIN
  /* A̕ϐ̒lgpāA\classesɍsPsǉ */
  INSERT INTO classes (department, course, description, max_students, 
                       current_students, num_credits, room_id) 
    VALUES (v_Department, v_Course, v_Description, v_MaxStudents, 
            v_CurrentStudents, v_NumCredits, v_RoomID); 
END AddNewRow;


p.22
CREATE OR REPLACE TRIGGER CheckRoomID
  BEFORE INSERT OR UPDATE OF room_id
  ON classes
  FOR EACH ROW
DECLARE
  /* ԍi[邽߂̈ꎞϐ */
  v_RoomID  NUMBER(5); 
BEGIN
  /* \roomsɖ₢킹邱ƂɂāAԍL`FbN */
  SELECT room_id
    into v_RoomID
    FROM rooms
    where room_id = :new.room_id; 
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    /* ԍȂƁA֗D֗ƁAԍ
       ł邱ƂG[o͂D܂AINSERTُID */
    RAISE_APPLICATION_ERROR(-20000, :new.room_id || ' is not a ' ||
        ' valid room'); 
END CheckRoomID;


p.22-23
DECLARE
  v_StudentID NUMBER(5) := 10000;  -- ϐ10,000ŏ
  v_FirstName VARCHAR2(20);        -- ϐl̕񒷂́Aő20
BEGIN
  /* s\ZNV̊Jn */
  -- ID10,000̊w̖OoD
  SELECT first_name
    INTO v_FirstName
    FROM students
    WHERE = V_StudentID; 
EXCEPTION
  /* OZNV̊Jn */
  WHEN NO_DATA_FOUND THEN
  -- G[̏
    INSERT INTO log_table (info) 
      VALUES ('Student 10,000 dose not exist!'); 
END;


p.23
DECLARE
   /* 錾ZNV */
BEGIN
   /* s\ZNV */
EXCEPTION
   /* OZNV */
END; 


p.24
BEGIN
   /* s\ZNV */
END;


DECLARE
   /* 錾ZNV */
BEGIN
   /* s\ZNV */
END;


p.25
x
v_StudentID
TempVar
v1
v2_
social_security_#


x+y                               -- +́CȕD
_temp_                            -- 擪́CAt@xbgłȂ΂ȂȂD
                                     A_[XRAł͂ȂD
First Name                        -- 󔒂́CD
This_is_a_really_long_identifier  -- 30𒴂ĂD
1_variable                        -- 擪ł͂ȂD


Room_Description
room_description
ROOM_DESCRIPTION
rOOm_DEscriPTIOn


p.26
DECLARE
  begin number;


DECLARE
  v_BeginDate  DATE;


"A number"
"Linda's variable"
"x/y"
"X/Y"


p.27
DECLARE
  v_Exception   VARCHAR2(10);
BEGIN
  SELECT "EXCEPTION"
    INTO v_Exception
    FROM exception_table;
END;


p.29
'12345'
'Four score and seven years ago...'
'100%'
'"'


p.30
'Mike''s string


''''


''


123
-7
+12
0


-17.1
23.0
3.


p.31
1.345E7
9.87E-3
-7.12e+12


1.345E7 = 1.345~i107j
        = 1.345~10,000,000
        = 13,450,000
9.87E-3 = 9.87~i10-3j
        = 9.87~.001
        = 0.00987
-7.12e+12 = -7.12~i1012j
          = -7.12~1,000,000,000,000
          = -7,120,000,000,000


p.32
DECLARE
  v_Department  CHAR(3);
  v_Course      NUMBER;
BEGIN
  INSERT INTO classes (department, course)
    VALUES (v_Department, v_Course);
END;


DECLARE
  v_Department  CHAR(3);  -- 3̊wR[h
                          -- i[邽߂̕ϐD
  v_Course      NUMBER;   -- wȔԍi[邽߂̕ϐD
BEGIN
  -- v_Departmentv_CourseŎw肵wȂ
  -- f[^x[X̕\classesɑ}D
  INSERT INTO classes (department, course)
    VALUES (v_Department, v_Course);
END;


DECLARE
  v_Department  CHAR(3); /* 3̊w
                            i[邽߂̕ϐ */
  v_Course      NUMBER;  /* wȔԍi[邽߂̕ϐ */
BEGIN
  /* v_Departmentv_CourseŎw肵wȂ
     f[^x[X̕\classesɑ} */
  INSERT INTO classes (department, course)
    VALUES (v_Department, v_Course);
END;


p.33
BEGIN
  /* ́CRg̓łD/* ̂悤 */ ʂ̃Rgr
   Jn̂́CłD */
  NULL;
END;


DECLARE
  v_Description     VARCHAR2(50);
  v_NumberSeats     NUMBER := 45;
  v_Counter         BINARY_INTEGER := 0;


p.34
DECLARE
  v_TempVar  NUMBER NOT NULL;


DECLARE
  v_TempVar  NUMBER NOT NULL := 0;


DECLARE
  c_MinimumStudentID  CONSTANT NUMBER(5) := 10000;


DECLARE
  v_NumberSeats  NUMBER DEFAULT 45;
  v_Counter      BINARY_INTEGER DEFAULT 0;
  v_FirstName    VARCHAR2(20) DEFAULT 'Scott';


DECLARE
  v_FirstName, v_LastName  VARCHAR2(20);


DECLARE
  v_FirstName VARCHAR2(20);
  v_LastName  VARCHAR2(20);


p.40
BBBBBBBB.RRRR.FFFF


0000001E.00FF.0001


p.41
DECLARE
  v_ContinueFlag  BOOLEAN := 0;


p.42
DECLARE
  v_FirstName    VARCHAR2(20);


DECLARE
  v_FirstName  VARCHAR2(25);


DECLARE
  v_FirstName  students.first_name%TYPE;


DECLARE
  v_RoomID     classes.room_id%TYPE;  -- NUMBER(5)ԂD
  v_RoomID2    v_RoomID%TYPE;         -- NUMBER(5)ԂD
  v_TempVar    NUMBER(7,3) NOT NULL := 12.3;
  v_AnotherVar v_TempVar%TYPE;        -- NUMBER(7,3)ԂD


p.43
DECLARE
  SUBTYPE T_LoopCounter IS NUMBER;  -- VTu^Cv̒`D
  v_LoopCounter   T_LoopCounter;    -- YTu^Cv
                                    -- ϐ錾D
  SUBTYPE T_NameType IS students.first_name%TYPE;


DECLARE
  SUBTYPE T_LoopCounter IS NUMBER(4);  -- ȐD


DECLARE
  v_DummyVar  NUMBER(4);  -- _~[̕ϐD̕ϐ́Cۂɂ͎gpȂD
  SUBTYPE T_LoopCounter is v_DummyVar%TYPE;  -- NUMBER(4)ԂD
  v_Counter  T_LoopCounter;


p.44
DECLARE
  SUBTYPE  T_Numeric IS NUMBER; -- ̂ȂTu^Cv̒`D
  v_Counter is T_Numeric(5);    -- Cϐɂ݂͐D 


p.44-45
DECLARE
  v_CurrentCredits  VARCHAR2(5);
BEGIN
  SELECT current_credits
    INTO v_CurrentCredits
    FROM students
    WHERE id = 10002;
END;


p.45-46
DECLARE
  v_CurrentCredits  VARCHAR2(5);
BEGIN
  SELECT TO_CHAR(current_credits)
    INTO v_CurrentCredits
    FROM students
    WHERE id = 10002;
END;


p.47
I_Outer.v_SSN


p.48-49
DECLARE
  v_String1  VARCHAR2(10);
  v_String2  VARCHAR2(15);
  v_Numeric  NUMBER;
BEGIN
  v_String1 := 'Hello';
  v_String2 := v_String1;
  v_Numeric := -12.4;
END;


p.50
3 + 5 * 7


(3 + 5) * 7


'Hello ' || 'World' || '!'


'Hello World!'


DECLARE
  v_TempVar  VARCHAR2(10) := 'PL';
  v_Result   VARCHAR2(20);
BEGIN
  v_Result := v_TempVar || '/SQL';
END;


p.51
X > Y
NULL
(4 > 5) OR (-1 != Z)


TRUE AND NULL


p.52
'Scott' LIKE 'Sc%t'


'Scott' LIKE 'Sc_tt'


'Scott' LIKE '%'


100 BETWEEN 110 AND 120


100 BETWEEN 90 AND 110


'Scott' IN ('Mike', 'Pamela', 'Fred')


p.53-54
DECLARE
  v_NumberSeats rooms.number_seats%TYPE;
  v_Comment VARCHAR2(35);
BEGIN
  /* 9999ƂIDŎw肵̍ȐoD
     ʂ́Av_NumberSeatsɊi[D */
  SELECT number_seats
    INTO v_NumberSeats
    FROM rooms
    WHERE room_id = 99999;
  IF v_NumberSeats < 50 THEN
    v_Comment := 'Fairly small';
  ELSIF v_NumberSeats < 100 THEN
    v_Comment := 'A little bigger';
  ELSE
    v_Comment := 'Lots of room';
  END IF;
END;


p.54
v_NumberSeats < 50


v_Comment := 'Fairly small';


v_NumberSeats < 100


v_Comment := 'A little bigger';


v_Comment := 'Lots of room';


p.54-55
DECLARE
  v_NumberSeats rooms.number_seats%TYPE;
  v_Comment VARCHAR2(35);
BEGIN
  /* 9999ƂIDŎw肵̍ȐoD
     ʂ́Av_NumberSeatsɊi[D */
  SELECT number_seats
    INTO v_NumberSeats
    FROM rooms
    WHERE room_id = 99999;
  IF v_NumberSeats < 50 THEN
    v_Comment := 'Fairly small';
    INSERT INTO temp_table (char_col)
      VALUES ('Nice and cozy');
  ELSIF v_NumberSeats < 100 THEN
    v_Comment := 'A little bigger';
    INSERT INTO temp_table (char_col)
      VALUES ('Some breathing room');
  ELSE
    v_Comment := 'Lots of room';
  END IF;
END;


p.55-56
/* ubN1 */
DECLARE 
  v_Number1 NUMBER;
  v_Number2 NUMBER;
  v_Result  VARCHAR2(5); 
BEGIN 
  ... 
  IF v_Number1 < v_Number2 THEN
    v_Result := 'Yes';
  ELSE
    v_Result := 'No'; 
  END IF; 
END;


/* ubN2 */
 DECLARE
v_Number1 NUMBER;
v_Number2 NUMBER;
v_Result VARCHAR2(5);
BEGIN
  ...
  IF v_Number1 >= v_Number2 THEN
    v_Result := 'No';
  ELSE 
    v_Result := 'Yes';
  END IF; 
END;


p.56-57
/* ubN1 */
DECLARE 
  v_Number1 NUMBER; 
  v_Number2 NUMBER; 
  v_Result  VARCHAR2(5); 
BEGIN 
  ... 
  IF v_Number1 IS NULL OR 
       v_Number2 IS NULL THEN 
    v_Result := 'Unknown';
  ELSIF v_Number1 < v_Number2 THEN
    v_Result := 'Yes'; 
  ELSE
    v_Result := 'No'; 
  END IF;
END;


/* ubN2 */
DECLARE
  v_Number1 NUMBER;
  v_Number2 NUMBER;
  v_Result VARCHAR2(5);
BEGIN
 ...
 IF v_Number1 IS NULL OR
      v_Number2 IS NULL THEN
    v_Result := 'Unknown';
  ELSIF v_Number1 >= v_Number2 THEN
    v_Result := 'No';
  ELSE
    v_Result := 'Yes';
  END IF;
 END;
 
 
p.58
DECLARE
  v_Counter BINARY_INTEGER := 1;
BEGIN
  LOOP
    -- [vJE^̌ݒls
    -- temp_tableɑ}
    INSERT INTO temp_table
      VALUES (v_Counter, 'Loop index');
    v_Counter := v_Counter + 1;
    -- Ii[vJE^ >50 j
    -- [v𔲂
    IF v_Counter > 50 THEN
      EXIT;
    END IF;
  END LOOP;
END;


DECLARE
  v_Counter BINARY_INTEGER := 1;
BEGIN
  LOOP
    -- [vJE^̌ݒls
    -- temp_tableɑ}
    INSERT INTO temp_table
      VALUES (v_Counter, 'Loop index');
    v_Counter := v_Counter + 1;
    -- Ii[vJE^ >50 j
    -- [v𔲂
    EXIT WHEN v_Counter > 50;
  END LOOP;
END;


p.59
DECLARE
  v_Counter BINARY_INTEGER := 1;
BEGIN
  -- [v̊esOɁA[vJE^50ȉł邩
  -- eXg
  WHILE v_Counter <= 50 LOOP
    INSERT INTO temp_table
      VALUES (v_Counter, 'Loop index');
    v_Counter := v_Counter + 1;
  END LOOP;
END;


DECLARE
  v_Counter BINARY_INTEGER;
BEGIN
  -- v_Counter̓fBtHglNULLɂď邽
  -- ̏NULLɕ]
  WHILE v_Counter <= 50 LOOP
    INSERT INTO temp_table
      VALUES (v_Counter, 'Loop index');
    v_Counter := v_Counter + 1;
  END LOOP;
END;


p.60
BEGIN
  FOR v_Counter IN 1..50 LOOP
    INSERT INTO temp_table
      VALUES (v_Counter, 'Loop Index');
  END LOOP;
END;


p.60-61
DECLARE
  v_Counter  NUMBER := 7;
BEGIN
  -- temp_tableɒl7}
  INSERT INTO temp_table (num_col)
    VALUES (v_Counter);
  -- ̃[vł́Av_CounterBINARY_INTEGERƂĐ錾
  -- ɂANUMBER^ƂĐ錾v_Counter͉B
  FOR v_Counter IN 20..30 LOOP
    -- v_Counter̒ĺA[v2030ɕω
    INSERT INTO temp_table (num_col)
      VALUES (v_Counter);
  END LOOP;
  -- temp_tableɒl7P}
  INSERT INTO temp_table (num_col)
    VALUES (v_Counter);
END;


p.61
BEGIN
  FOR v_Counter in REVERSE 50..10 LOOP
    -- v_Counter50n܂C[v𔽕邽т
    -- 1ZD
    NULL;
  END LOOP;
END;


DECLARE
  v_LowValue  NUMBER := 10;
  v_HighValue NUMBER := 40;
BEGIN
  FOR v_Counter IN REVERSE v_LowValue .. v_HighValue LOOP
    INSERT INTO temp_table
      VALUES (v_Counter, 'Dynamically specified loop ranges');
  END LOOP;
END;


p.62
DECLARE
  v_Counter  BINARY_INTEGER := 1;
BEGIN
  LOOP
    INSERT INTO temp_table
      VALUES (v_Counter, 'Loop count');
    v_Counter := v_Counter + 1;
    IF v_Counter > 50 THEN
      GOTO l_EndOfLoop;
    END IF;
  END LOOP;

  <<l_EndOfLoop>>
  INSERT INTO temp_table (char_col)
    VALUES ('Done!');
END;


p.62-63
BEGIN
  GOTO L_InnerBlock;  -- D̃ubNɂ̓u`łȂD
  BEGIN
    ...
    <<L_InnerBlock>>
    ...
  END;

  GOTO L_InsideIf;  -- DIF̒ɂ̓u`łȂD
  IF x > 3 THEN
    ...
    <<L_InsideIf>>
    INSERT INTO ...
  END IF;
END;


p.63
BEGIN
  IF x > 3 THEN
    ...
    GOTO L_NextCondition;
  ELSE
    <<L_NextCondition>>
    ...
  END IF;
END;


DECLARE
  v_Room  rooms%ROWTYPE;
BEGIN
  -- \roomss1soD
  SELECT *
    INTO v_Room
    FROM rooms
    WHERE rowid = 1;
  <<L_Insert>>
  INSERT INTO temp_table (char_col)
    VALUES ('Found a row!');
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    GOTO L_Insert;  -- D݂̃ubNɃu`Ŗ߂邱Ƃ͂łȂD
END;


p.64
BEGIN
  <<L_Outer>>
  FOR v_OuterIndex IN 1..50 LOOP
    ...
    <<L_Inner>>
    FOR v_InnerIndex IN 2..10 LOOP
      ...
      IF v_OuterIndex > 40 THEN
        EXIT L_Outer; -- ̃[vID
      END IF;
    END LOOP L_Inner;
END LOOP L_Outer;


p.64-65
DECLARE
  v_TempVar  NUMBER := 7;
BEGIN
  IF v_TempVar < 5 THEN
    INSERT INTO temp_table (char_col)
      VALUES ('Too small');
  ELSIF v_TempVar < 10 THEN
    INSERT INTO temp_table (char_col)
      VALUES ('Just right');
  ELSE
    NULL; -- sȂD
  END IF;
END;


p.65
DECLARE
  v_StudentID  NUMBER(5);
  v_FirstName  VARCHAR2(20);
  v_LastName   VARCHAR2(20);


p.66
DECLARE
  /* wɋʂȏi[邽߂̃R[h^̒` */
  TYPE t_StudentRecord IS RECORD (
    StudentID  NUMBER(5),
    FirstName  VARCHAR2(20),
    LastName   VARCHAR2(20));

  /* Y^̕ϐ錾 */
  v_StudentInfo  t_StudentRecord


DECLARE
  TYPE t_SampleRecord IS RECORD (
    Count          NUMBER(4),
    Name           VARCHAR2(10) := 'Scott',
    EffectiveDate  DATE,
    Description    VARCHAR2(45) NOT NULL := 'Unknown');
  v_Sample1  t_SampleRecord;
  v_Sample2  t_SampleRecord;


p.67
BEGIN
  /* SYSDATÉC݂̓tƎԂ
     gݍ݊֐łD*/
  v_Sample1.EffectiveDate := SYSDATE;
  v_Sample2.Description := 'Pesto Pizza';
END;


v_Sample1 := v_Sample2;


p.67
DECLARE
  TYPE t_Rec1Type IS RECORD (
    Field1 NUMBER,
    Field2 VARCHAR2(5));
  TYPE t_Rec2Type IS RECORD (
    Field1 NUMBER,
    Field2 VARCHAR2(5));
  v_Rec1 t_Rec1Type;
  v_Rec2 t_Rec2Type;
BEGIN
  /* v_Rec1v_Rec2́CtB[h̖Oь^łĂ
     R[h^قȂĂD
     ̂߁C͖̑łD */
  v_Rec1 := v_Rec2;

  /* CtB[ȟ^ł邽
     ̑͗LłD */
  v_Rec1.Field1 := v_Rec2.Field1;
  v_Rec2.Field2 := v_Rec2.Field2;
END;


p.68
DECLARE
  -- \studentŝ̃tB[hɈv郌R[h`
  -- tB[hɑ΂%TYPEgpĂ_ɒ
  TYPE t_StudentRecord IS RECORD (
    FirstName  students.first_name%TYPE,
    LastName   students.last_name%TYPE,
    Major      students.major%TYPE);

  -- f[^󂯎邽߂̕ϐ錾
  v_Student  t_StudentRecord;
BEGIN
  -- ID10,000̊wɊւo
  -- v_Student̃tB[hɈv⍇킹ǂ̂悤
  -- Ԃɒ
  SELECT first_name, last_name, major
    INTO v_Student
    FROM students
    WHERE ID = 10000;
END;


DECLARE
  v_RoomRecord  rooms%ROWTYPE;


p.68-69
(room_id      NUMBER(5),
 building     VARCHAR2(15),
 room_number  NUMBER(4),
 number_seats NUMBER(4),
 description  VARCHAR2(50))


p.69
DECLARE
  /* \̌^`Ď^ϐɂ́C
     10ȉ̊ei[łD */
  TYPE t_CharacterTable IS TABLE OF VARCHAR2(10)
    INDEX BY BINARY_INTEGER;

  /* Ľ^ϐ錾Dɂ
     ̈悪ۂɊ蓖ĂD */
  v_Characters t_CharacterTable;


p.69-70
DECLARE
  TYPE t_NameTable IS TABLE OF students.first_name%TYPE
    INDEX BY BINARY_INTEGER;
  TYPE t_DateTable IS TABLE OF DATE
    INDEX BY BINARY_INTEGER;
  v_Names t_NameTable;
  v_Dates t_DateTable;


p.70
v_Names(0) := 'Harold';
v_Names(-7) := 'Susan';
v_Names(3) := 'Steve'


p.71
ORA-01403: f[^܂B 


DECLARE
  TYPE t_StudentTable IS TABLE OF students%ROWTYPE
    INDEX BY BINARY_INTEGER;
  /* v_Students̊evf́CR[hłD */
  v_Students t_StudentTable;
BEGIN
  /* id = 10,001̃R[hoC
 v_Students(10001)Ɋi[D */
  SELECT *
    INTO v_Students(10001)
    FROM students
    WHERE id = 10001;
END;


p.72
v_Students(10001).first_name := 'Larry';


p.73
DECLARE
  TYPE t_NumberTable IS TABLE OF NUMBER
    INDEX BY BINARY_INTEGER;
  v_Numbers t_NumberTable;
  v_Total NUMBER;
BEGIN
  -- \50s}D
  FOR v_Counter IN 1..50 LOOP
    v_Numbers(v_Counter) := v_Counter;
  END LOOP;

  v_Total := v_Numbers.COUNT;
END;


DECLARE
  TYPE t_ValueTable IS TABLE OF VARCHAR2(10)
    INDEX BY BINARY_INTEGER;
  v_Values t_ValueTable;
BEGIN
  -- \ɍs}
  v_Values(1) := 'One';
  v_Values(3) := 'Three';
  v_Values(-2) := 'Minus Two';
  v_Values(0) := 'Zero';
  v_Values(100) := 'Hundred';
  
  v_Values.DELETE(100);  -- 'Hundred'폜
  v_Values.DELETE(1,3);  -- 'One''Three'폜
  v_Values.DELETE;       -- c̒lׂč폜
END;


p.74
DECLARE
  t_FirstNameTable IS TABLE OF students.first_name%TYPE
    INDEX BY BINARY_INTEGER;
  FirstNames  t_FirstNameTable;
BEGIN
  -- \ɍs}
  FirstNames(1) := 'Scott';
  FirstNames(3) := 'Joanne';

  -- s݂Ă邩A`FbN
  IF FirstNames.EXISTS(1) THEN
    INSERT INTO temp_table (char_col) VALUES
      ('Row 1 exists!');
  ELSE
    INSERT INTO temp_table (char_col) VALUES
      ('Row 1 doesn't exist!');
  END IF;
  IF FirstNames.EXISTS(2) THEN
    INSERT INTO temp_table (char_col) VALUES
      ('Row 2 exists!');
  ELSE
    INSERT INTO temp_table (char_col) VALUES
      ('Row 2 doesn''t exist!');
  END IF;
END;


p.75
DECLARE
  t_LastNameTable IS TABLE OF students.last_name%TYPE
    INDEX BY BINARY_INTEGER;
  v_LastNames  t_LastNameTable;
  v_Index  BINARY_INTEGER;
BEGIN
  -- \ɍs}
  v_LastNames(43) := 'Mason';
  v_LastNames(50) := 'Junebug';
  v_LastNames(47) := 'Taller';

  -- v_Index43
  v_Index := v_LastNames.FIRST;

  -- v_Index50
  v_Index := v_LastNames.LAST;
END;


DECLARE
  TYPE t_MajorTable IS TABLE OF students.major
    INDEX BY BINARY_INTEGER;
  v_Majors t_MajorTable;
  v_Index  BINARY_INTEGER;
BEGIN
  -- \ɍs}
  v_Majors(-7) := 'Computer Science';
  v_Majors(4) := 'History';
  v_Majors(5) := 'Economics';

  -- \ׂ̂Ă̍s[vŒׁA̍s
  -- temp_tableɑ}
  v_Index := v_Majors.FIRST;
  LOOP
    INSERT INTO temp_table (num_col, char_col)
      VALUES (v_Index, v_Majors(v_Index));
    EXIT WHEN v_Index = v_Majors.LAST;
    v_Index := v_Majors.NEXT(v_Index);
  END LOOP;
END;


p.76
DECLARE
  TYPE t_NameTable IS TABLE OF students.first_name%TYPE
    INDEX BY BINARY_INTEGER;
  v_Names t_NameTable;
BEGIN
  /* v_NameŝׂĂ폜D */
  v_Names := NULL;
END;


p.77
declare
x number;
y number;
begin if x < 10 then y := 7; else y := 3; end if; end;


DECLARE
  v_Test   NUMBER;  -- eXgΏۂϐ
  v_Result NUMBER;  -- eXgʂ̊i[ƂȂϐ
BEGIN
  -- v_TesteXgCv_Test < 10̏ꍇv_Result7D
  IF v_Test < 10 THEN
    v_Result := 7;
  ELSE
    v_Result := 3;
  END IF;
END;


p.78
DECLARE
  v_Temp NUMBER := 0;  -- v_Temp0D


x number;


p.79
v_StudentID  NUMBER(5);


v_VariableName     vOϐ
e_ExceptionName    [U[`̗O
t_TypeName         [U[`̌^
p_ParameterName    vV[W܂͊֐ɓnp[^


p.80
IF x < y THEN IF z IS NULL THEN x := 3; ELSE x := 2; END IF; ELSE x := 4; END IF;


IF x < y THEN
  IF z IS NULL THEN
    x := 3;
  ELSE 
    x := 2;
  END IF;
ELSE
  x := 4;
END IF;


SELECT id, first_name, last_name
  INTO v_StudentID, v_FirstName, v_LastName
  FROM STUDENTS
  WHERE id = 10002;



Chapter3
p.85
BEGIN
  CREATE TABLE temp_table (
    num_value   NUMBER,
    char_value  CHAR(10));
END;


p.86
INSERT INTO classes (department, course, description, max_students, 
                     current_students, num_credits, room_id)
  VALUES ('CS', 101, 'Computer Science 101', 50, 50, 4, 99998);


p.87
DECLARE
  v_CourseDescription  classes.description%TYPE;
BEGIN
  /* v_CourseDescriptionւ̑ */
  v_CourseDescription := 'Computer Science 101';
  INSERT INTO classes (department, course, description, max_students,
                       current_students, num_credits, room_id) 
    VALUES ('CS', 101, v_CourseDescription, 50, 50, 4, 99998);
END;


p.89
ORA-01422: v̍so܂B 


DECLARE
  v_StudentRecord  students%ROWTYPE;
  v_Department     classes.department%TYPE;
  v_Course         classes.course%TYPE;
BEGIN
  -- \students烌R[hPoAv_StudentRecord
  -- Ɋi[DWHERÉA\̂Psv_ɂĒӁD
  -- ܂A⍇킹́A\StudentŝׂẴtB[hԂi*
  -- IĂ邽߁j_ɂĂӁD̂߁AoƂȂ郌R[h́A
  -- students%ROWTYPEƒ`ĂD
  SELECT *
    INTO v_StudentRecord
    FROM students
    WHERE id = 10000;

  -- \classestB[hQoAꂩv_Department
  -- v_CourseɊi[D̏ꍇAWHERE傪\̂Psɂ
  -- v_ɒӁD
  SELECT department, course
    INTO v_Department, v_Course
    FROM classes
    WHERE room_id = 99997;
END;


p.90
DECLARE
  v_StudentID  students.id%TYPE;
BEGIN
  -- V炵wIDԍo
  SELECT student_sequence.NEXTVAL
    INTO v_StudentID
    FROM dual;

  -- \studentsɍsPsǉ
  INSERT INTO students (id, first_name, last_name)
    VALUES (v_StudentID, 'Timothy', 'Taller');

  -- QsڂǉDAINSERT̒ŏԍ
  -- ڎw肷
  INSERT INTO students (id, first_name, last_name)
    VALUES (student_sequence.NEXTVAL, 'Patrick', 'Poll');
END;


p.91
INSERT INTO rooms
  SELECT * FROM classes;


INSERT INTO classes
  SELECT * FROM classes;


DECLARE
  v_Major           students.major%TYPE;
  v_CreditIncrease  NUMBER := 3;
BEGIN
  -- UPDATEł́AjUĂ邷ׂĂ̊wcurrent_credits
  -- tB[hɂRZ
  v_Major := 'History';
  UPDATE students
    SET current_credits = current_credits + v_CreditIncrease
    WHERE major = V_Major;
END;


p.92
DECLARE
  v_StudentCutoff  NUMBER;
BEGIN
  v_StudentCutoff := 10;
  -- w̓o^s\ȃNXAׂč폜
  DELETE FROM classes
    WHERE current_students < v_StudentCutoff;

  -- ocwŮw̓APʂ܂KĂȂwׂč폜
  DELETE FROM students
    WHERE current_credits = 0
    AND   major = 'Economics';
END;


p.93
DECLARE
  v_Department  CHAR(3);
BEGIN
  v_Department := 'CS';
  -- Rs[^TCGXiCSj̃NXׂč폜D
  DELETE FROM classes
    WHERE department = v_Department;
END;


DECLARE
 Department  CHAR(3);
BEGIN
  Department := 'CS';
  -- Rs[^TCGXiCSj̃NXׂč폜D  
  DELETE FROM classes
    WHERE department = Department;
END;


p.94
<<l_DeleteBlock>>
DECLARE
  Department  CHAR(3);
BEGIN
  Department := 'CS';
  -- Rs[^TCGXiCSj̃NXׂč폜D
  DELETE FROM classes
    WHERE department = l_DeleteBlock.Department;
END;


'abc' = 'abc'
'abc   ' = 'abc'  -- ŏ̖̕ɋ󔒂_ɒӁD
'ab' < 'abc'
'abcd' > 'abcc'


p.95
'abc' = 'abc'
'ab' < 'abc'
'abcd' > 'abcc'


'abc   ' = 'abc'  -- ŏ̖̕ɋ󔒂_ɒӁD


DECLARE
 v_Department  VARCHAR2(3);
BEGIN
  v_Department := 'CS';
  -- Rs[^TCGXiCSj̃NXׂč폜D
  DELETE FROM classes
    WHERE department = v_Department;
END;


p.96
UPDATE students
  SET major = 'Music'
  WHERE id = 10005;


ORA-00942: \܂̓r[݂܂B


PLS-00201: ʎq: <O>͐錾Ă܂B


p.97
UPDATE example.students
  SET major = 'Music'
  WHERE id = 10005;


CREATE DATABASE LINK example_backup
  CONNECT TO example IDENTIFIED BY example
  USING 'backup_database';


UPDATE students@example_backup
  SET major = 'Music'
  WHERE id = 10005;


p.98
CREATE SYNONYM backup_students
  FOR students@example_backup;


UPDATE backup_students
  SET major = 'Music'
  WHERE id = 10005;


p.99
SELECT UPPER(first_name)
  FROM students;


DECLARE
  v_FirstName  students.first_name%TYPE;
BEGIN
  v_FirstName := UPPER('Charlie');
END;


p.100
SELECT CHR(37) a, CHR(100) b, CHR(101) c
  FROM dual;
A B C
- - -
% d e 


p.101
SELECT CONCAT('Alphabet ', 'Soup') "Dinner"
  FROM dual;
Dinner
-------------
Alphabet Soup 


SELECT INITCAP('4 scoRE and 7 YEARS ago...') "Speech"
  FROM dual;


Speech
--------------------------
4 Score And 7 Years Ago...


p.102
SELECT LOWER('4 scoRE and 7 YEARS ago...') "Speech"
  FROM dual;
Speech
--------------------------
4 score and 7 years ago...


SELECT LPAD('Short String', 15) "First"
  FROM dual;
First
---------------
   Short String

SELECT LPAD('Short String', 20, 'XY') "Second"
  FROM dual;
Second
--------------------
XYXYXYXYShort String

SELECT LPAD('Short String', 13, 'XY') "Third"
  FROM dual;
Third
-------------
XShort String


p.103
SELECT LTRIM('   End of the string') "First"
  FROM dual;
First
-----------------
End of the string

SELECT LTRIM('xxxEnd of the string', 'x') "Second"
  FROM dual;
Second
-----------------
End of the string

SELECT LTRIM('xyxyxyEnd of the string', 'xy') "Third"
  FROM dual;
Third
-----------------
End of the string

SELECT LTRIM('xyxyxxxyEnd of the string', 'xy') "Fourth"
  FROM dual;
Fourth
-----------------
End of the string


p.104
SELECT NLS_INITCAP('ijgloo', 'NLS_SORT = Xdutch') "Result"
  FROM dual;
Result
------
IJgloo 


p.105
SELECT NLS_LOWER('CITA''DEL', 'NLS_SORT = Xgerman') "Result"
  FROM dual;
Result
---------
citEel 


SELECT NLS_UPPER('groe', 'NLS_SORT = Xgerman') "Result"
  FROM dual;
Result
------
GROSS 


p.106
SELECT REPLACE ('This and That', 'Th', 'B') "First"
  FROM dual;
First
-----------
Bis and Bat

SELECT REPLACE ('This and That', 'Th') "Second"
  FROM dual;
Second
---------
is and at

SELECT REPLACE ('This and That', NULL) "Third"
  FROM dual;
Third
-------------
This and That


p.106-107
SELECT RPAD('Nifty', 10, '!') "First"
  FROM dual;
First
----------
Nifty!!!!!

SELECT RPAD('Nifty', 10, 'AB') "Second"
  FROM dual;
Second
----------
NiftyABABA 


p.107
SELECT RTRIM('This is a stringxxxxx', 'x') "First"
  FROM dual;
First
----------------
This is a string

SELECT RTRIM('This is also a stringxxXXxx', 'x') "Second"
  FROM dual;
Second
-------------------------
This is also a stringxxXX

SELECT RTRIM('This is a string as well', 'well') "Third"
  FROM dual;
Third
--------------------
This is a string as


p.108-109
SELECT first_name, SOUNDEX(first_name)
  FROM students;
FIRST_NAME           SOUN
-------------------- ----
Scott                S300
Margaret             M626
Joanne               J500
Manish               M520
Patrick              P362
Timothy              T530

SELECT first_name
  FROM students
  WHERE SOUNDEX(first_name) = SOUNDEX('skit');
FIRST_NAME
--------------------
Scott


p.109
SELECT SUBSTR('abc123def', 4, 4) "First"
  FROM dual;
First
----
123d

SELECT SUBSTR('abc123def', -4, 4) "Second"
  FROM dual;
Second
----
3def 


p.110
SELECT SUBSTR("abc123def", 2, 6) "Example"
  FROM DUAL;
Example
-------
bc1


p.110
SELECT TRANSLATE('abcdefghij', 'abcdef', '123456')
  FROM dual;
TRANSLATE(
----------
123456ghij

SELECT TRANSLATE('abcdefghij', 'abcdefghij', '123456')
  FROM dual;
TRANSL
------
123456


p.111
SELECT UPPER('THE quick bROwn Fox jumped over THE LAZY
             dOg...') "Result"
  FROM dual;
Result
-----------------------------------------------
THE QUICK BROWN FOX JUMPED OVER THE LAZY DOG...


p.112
SELECT ASCII(' ')
  FROM dual;
ASCII('')
---------
       32

SELECT ASCII('a')
  FROM dual;
ASCII('A')
----------
        97


p.112-113
SELECT INSTR('Scott''s spot', 'ot', 1, 2) "First"
  FROM dual;
    First
---------
       11

SELECT INSTR('Scott''s spot', 'ot', -1, 2) "Second"
  FROM dual;
   Second
---------
        3

SELECT INSTR('Scott''s spot', 'ot', 5) "Third"
  FROM dual;
    Third
---------
       11

SELECT INSTR('Scott''s spot', 'ot', 12) "Fourth"
  FROM dual;
   Fourth
---------
        0


p.113
SELECT INSTRB('Scott''s spot', 'ot', 1, 2) "INSTRB"
  FROM dual;
   INSTRB
---------
       21


p.114
SELECT LENGTH('Mary had a little lamb') "Length"
  FROM dual;
   Length
---------
       22


SELECT LENGTHB('Mary had a little lamb') "Length"
  FROM dual;
   Length
---------
       44


p.115
SELECT NLSSORT('Scott') "NLS"
  FROM dual;
NLS
--------------------------------------------
53636F747400


p.116
SELECT ABS(-7), ABS(7)
  FROM dual;
  ABS(-7)    ABS(7)
--------- ---------
        7         7


SELECT CEIL(18.1), CEIL(-18.1)
  FROM dual;
CEIL(18.1) CEIL(-18.1)
---------- -----------
        19         -18


SELECT COS(0), COS(90 * 3.14159265359/180)
  FROM dual;
   COS(0) COS(90*3.14159265359/180)
--------- -------------------------
        1                        -1


p.117
SELECT COSH(0), COSH(90 * 3.14159265359/180)
  FROM dual;
  COSH(0) COSH(90*3.14159265359/180)
--------- --------------------------
        1                  2.5091785


SELECT EXP(1), EXP(2.7)
  FROM dual;
   EXP(1)  EXP(2.7)
--------- ---------
2.7182818 14.879732


p.118
SELECT FLOOR(-23.5), FLOOR(23.5)
  FROM dual;
FLOOR(-23.5) FLOOR(23.5)
------------ -----------
         -24          23


SELECT LN(100)
  FROM dual;
  LN(100)
---------
4.6051702


p.119
SELECT LOG(2, 32), LOG(5, 25)
  FROM dual;
LOG(2,32) LOG(5,25)
--------- ---------
        5         2


SELECT MOD(23, 5), MOD(4, 1.3)
  FROM dual;
MOD(23,5) MOD(4,1.3)
--------- ----------
        3         .1


p.120
SELECT POWER(4, 3), POWER(1.1, 2.6), POWER(25, -2), POWER
   FROM dual;
POWER(4,3) POWER(1.1,2.6) POWER(25,-2) POWER(-2,3)
---------- -------------- ------------ -----------
        64       1.281212        .0016          -8


SELECT ROUND(1.56), ROUND(1.56, 1), ROUND(12.34, -2)
  FROM dual;
ROUND(1.56) ROUND(1.56,1) ROUND(12.34,-2)
----------- ------------- ---------------
          2           1.6               0


p.121
SELECT SIGN(-47.3), SIGN(0), SIGN(47.3)
  FROM dual;
SIGN(-47.3)   SIGN(0) SIGN(47.3)
----------- --------- ----------
         -1         0          1


SELECT SIN(0), SIN(60 * 3.14159265359/180)
  FROM dual;
   SIN(0) SIN(60*3.14159265359/180)
--------- -------------------------
        0                  .8660254


p.121-122
SELECT SINH(0), SINH(60 * 3.14159265359/180)
  FROM dual;
  SINH(0) SINH(60*3.14159265359/180)
--------- --------------------------
        0                  1.2493671


p.122
SELECT SQRT(64), SQRT(97.654)
  FROM dual;
 SQRT(64) SQRT(97.654)
--------- ------------
        8    9.8820038


SELECT TAN(0), TAN(-60 * 3.14159265359/180)
  FROM dual;
   TAN(0) TAN(-60*3.14159265359/180)
--------- --------------------------
        0                  -1.732051


p.123
SELECT TANH(0), TANH(-60 * 3.14159265359/180)
  FROM dual;
  TANH(0) TANH(-60*3.14159265359/180)
--------- ---------------------------
        0                   -.7807144


SELECT TRUNC(-123.456), TRUNC(-123.456, 1), TRUNC(-123.456, -1)
  FROM dual;
TRUNC(-123.456) TRUNC(-123.456,1) TRUNC(-123.456,-1)
--------------- ----------------- ------------------
           -123            -123.4               -120


p.124
SELECT ADD_MONTHS('02-FEB-91', 1), ADD_MONTHS('19-JAN-87', 1),
       ADD_MONTHS('30-JAN-87', 13)
  FROM dual;
ADD_MONTH ADD_MONTH ADD_MONTH
--------- --------- ---------
02-MAR-91 19-FEB-87 29-FEB-88


p.125
SELECT LAST_DAY('12-APR-71') "Current",
       LAST_DAY('12-APR-71') - TO_DATE('12-APR-71') "Days Left"
  FROM dual;
Current   Days Left
--------- ---------
30-APR-71        18


SELECT MONTHS_BETWEEN('12-APR-71', '12-MAR-97') "First",
       MONTHS_BETWEEN('12-APR-71', '22-MAR-60') "Second"
  FROM dual;
First     Second
--------- ---------
     -311 132.67742


p.126
SELECT TO_CHAR(NEW_TIME(TO_DATE('12-APR-71 12:00:00',
                                'DD-MON-YY HH24:MI:SS'),
                        'PST', 'EST'),
               'DD-MON-YY HH24:MI:SS') "Pacific -> Eastern"
  FROM dual;
Pacific -> Eastern
------------------
12-APR-71 15:00:00


p.127
SELECT NEXT_DAY('12-APR-71', 'thursday') "Result"
   FROM dual;
Result
--------
15-APR-71


SELECT ROUND(TO_DATE('12-APR-71'), 'MM') "Nearest Month"
  FROM dual;
Nearest Month
-------------
01-APR-71


p.128
SELECT TO_CHAR(SYSDATE, 'Month DD, YYYY HH24:MI:SS') "Now"
  FROM dual;
Now
----------------------------------------------------------------
April  16, 1996 00:14:18


p.129
SELECT TRUNC(TO_DATE('12-APR-71 13:21:00', 'DD-MON-YY HH24'),
             'Year') "First Day"
  FROM dual;
First Day
---------
01-JAN-71


SELECT SYSDATE, SYSDATE + 1 "Tomorrow"
  FROM dual;
SYSDATE   Tomorrow
--------- ---------
13-NOV-95 14-NOV-95

SELECT TO_DATE('12-APR-71 12:00:00', 'DD-MON-YY HH24:MI:SS') -
       TO_DATE('15-MAR-71 15:00 -MON-YY 
               HH24:MI:SS') "Difference"
  FROM dual
Difference
----------
    27.875


p.131
SELECT description
  FROM classes
  WHERE rowid = CHARTOROWID('0000002D.0002.0002');
DESCRIPTION
-------------
Economics 203


p.131-132
SELECT CONVERT('Gro*', 'WE8HP', 'WE8DEC') "Conversion"
  FROM dual;
Conversion
----------
Gro


INSERT INTO raw_table (raw_column)
  VALUES (HEXTORAW('017D3F'));


SELECT raw_column
  FROM raw_table;
RAW_COLUMN
----------
017D3F 


p.133
SELECT ROWIDTOCHAR(rowid)
  FROM classes;

ROWIDTOCHAR(ROWID)
------------------
0000002D.0000.0002
0000002D.0002.0002
0000002D.0003.0002


p.134
SELECT TO_CHAR(SYSDATE, 'DD-MON-YY HH24:MI:SS') "Right Now"
  FROM dual;
Right Now
------------------
15-NOV-95 01:17:14


p.136
SELECT TO_CHAR(123456, '99G99G99') "Result"
  FROM dual;
Result
---------
 12,34,56

SELECT TO_CHAR(123456, 'L99G99D99',
               'NLS_NUMERIC_CHARACTERS = '',.''
                NLS_CURRENCY = ''Money'' ') "Result 2"
  FROM dual;
Result 2
-------------
Money12,34.56



p.138
DECLARE
  v_CurrentDate  DATE;
BEGIN
  v_CurrentDate := TO_DATE('January 7, 1973', 'Month DD, YYYY');
END;


p.139
SELECT TO_MULTI_BYTE('Hello') "Multi"
  FROM dual;
Multi
----------
Hello 


DECLARE
  v_Num  NUMBER;
BEGIN
  v_Num := TO_NUMBER('$12345.67', '$99999.99');
END;


p.140
SELECT TO_SINGLE_BYTE('Greetings') "Single"
  FROM dual;
Single
----------
Greetings 


p.141
SELECT AVG(number_seats)
  FROM rooms;
   AVG(NUMBER_SEATS)
--------------------
                 330


SELECT COUNT(*)
  FROM students;
  COUNT(*)
----------
         6

SELECT COUNT(DISTINCT major) "Majors"
  FROM students;
    Majors
----------
         3


p.142
SELECT MAX(LENGTH(first_name))
  FROM students;
MAX(LENGTH(FIRST_NAME))
-----------------------
                      8


p.143
SELECT MIN(id)
  FROM students;
  MIN(ID)
---------
    10000


SELECT STDDEV(number_seats)
  FROM rooms;
STDDEV(NUMBER_SEATS)
--------------------
           422.19664


p.144
SELECT department dept, SUM(num_credits)
  FROM classes
  GROUP by department;
DEPT SUM(NUM_CREDITS)
---- ----------------
CS                  4
ECN                 3
HIS                 4


p.145
SELECT VARIANCE(number_seats)
  FROM rooms;
VARIANCE(NUMBER_SEATS)
----------------------
                178250


p.145-146
SELECT DECODE('abc', 'a', 1,
                     'b', 2,
                     'abc', 3,
                     'd', 4,
                     -1) "Decode 1"
  FROM dual;
 Decode 1
---------
        3

SELECT DECODE(NULL, 'a', 1,
                    NULL, 2) "Decode 2"
  FROM dual;
Decode 2
---------
        2


p.147
SELECT first_name, DUMP(first_name) "Dump"
   FROM students
FIRST_NAME           Dump
-------------------- ---------------------------------------------
Scott                Typ=1 Len=5: 83,99,111,116,116
Margaret             Typ=1 Len=8: 77,97,114,103,97,114,101,116
Joanne               Typ=1 Len=6: 74,111,97,110,110,101
Manish               Typ=1 Len=6: 77,97,110,105,115,104
Patrick              Typ=1 Len=7: 80,97,116,114,105,99,107
Timothy              Typ=1 Len=7: 84,105,109,111,116,104,121

SELECT first_name, DUMP(first_name, 17) "Dump"
  FROM students
FIRST_NAME           Dump
-------------------- --------------------------------------
Scott                Typ=1 Len=5: S,c,o,t,t
Margaret             Typ=1 Len=8: M,a,r,g,a,r,e,t
Joanne               Typ=1 Len=6: J,o,a,n,n,e
Manish               Typ=1 Len=6: M,a,n,i,s,h
Patrick              Typ=1 Len=7: P,a,t,r,i,c,k
Timothy              Typ=1 Len=7: T,i,m,o,t,h,y

SELECT first_name, DUMP(first_name, 17, 2, 4) "Dump"
  FROM students;
FIRST_NAME           Dump
-------------------- --------------------------------------
Scott                Typ=1 Len=5: c,o,t,t
Margaret             Typ=1 Len=8: a,r,g,a
Joanne               Typ=1 Len=6: o,a,n,n
Manish               Typ=1 Len=6: a,n,i,s
Patrick              Typ=1 Len=7: a,t,r,I


p.148
SELECT GREATEST(10, '7', -1)
  FROM dual;
GREATEST(10,'7',-1)
-------------------
                 10


p.149
SELECT LEAST('abcd', 'ABCD', 'a', 'xyz') "Least"
  FROM dual;
Least
-----
ABCD 


p.150
SELECT NVL('non null value', 7) "First",
       NVL(NULL, 'null value') "Second"
  FROM dual;
First          Second
-------------- ----------
non null value null value 


SQL> connect scott/tiger
Connected.
SQL> SELECT UID
  2    FROM dual;
      UID
---------
        8

SQL> connect system/manager
Connected.
SQL> SELECT UID
  2    FROM dual;
      UID
---------
        5


p.151
SQL> connect scott/tiger
Connected.
SQL> SELECT USER
  2    FROM dual;
USER
------------------------------
SCOTT

SQL> connect sys/change_on_install
Connected.
SQL> SELECT USER
  2    FROM dual;
USER
------------------------------
SYS


p.152
SELECT USERENV('TERMINAL'), USERENV('LANGUAGE')
  FROM dual;
USERENV( USERENV('LANGUAGE')
-------- --------------------------------------------
Windows  AMERICAN_AMERICA.WE8ISO8859P1


p.153
list[-]
SELECT last_name, VSIZE(last_name) "Size"
  FROM students;
LAST_NAME                 Size
-------------------- ---------
Smith                        5
Mason                        5
Junebug                      7
Murgratroid                 11
Poll                         4
Taller                       6


p.154
CREATE SEQUENCE student_sequence
  START WITH 10000;

-- ̕ł́CIDlƂ10,000gpD
INSERT INTO students (id, first_name, last_name)
  VALUES (student_sequence.NEXTVAL, 'Scott', 'Smith');
-- ̕ł́CIDlƂ10,001gpD
INSERT INTO students (id, first_name, last_name)
  VALUES (student_sequence.NEXTVAL, 'Margaret', 'Mason');

SELECT student_sequence.NEXTVAL "Value"
  FROM dual;  -- ԍC܂1₷D
Value
----------
10002

SELECT student_sequence.CURRVAL "Value"
  FROM dual;  -- ݂̒lԂD
Value
----------
10002


p.154-155
SELECT ROWID
  FROM rooms;

ROWID
------------------
00000045.0000.0002
00000045.0001.0002
00000045.0002.0002
00000045.0003.0002
00000045.0004.0002


p.155
SELECT *
  FROM students
  WHERE ROWNUM < 3;


p.157
GRANT SELECT ON classes TO userA;


GRANT UPDATE, DELETE ON students TO userA;


GRANT CREATE TABLE, ALTER ANY PROCEDURE to userA;


p.158
REVOKE SELECT ON classes FROM userA;


REVOKE UPDATE, DELETE, INSERT ON students FROM userA;


REVOKE ALTER TABLE, EXECUTE ANY PROCEDURE FROM userA;


CREATE ROLE table_query;
GRANT SELECT ON students TO table_query;
GRANT SELECT ON classes TO table_query;
GRANT SELECT ON rooms TO table_query;


p.159
GRANT table_query TO userA;
GRANT table_query TO userB;


p.160
UPDATE accounts
  SET balance = balance - transaction_amount
  WHERE account_no = from_acct;
UPDATE accounts
  SET balance = balance + transaction_amount
  WHERE account_no = to_acct;


p.163
BEGIN
  INSERT INTO temp_table (char_col) VALUES ('Insert One');
  SAVEPOINT A;
  INSERT INTO temp_table (char_col) VALUES ('Insert Two');
  SAVEPOINT B;
  INSERT INTO temp_table (char_col) VALUES ('Insert Three');
  SAVEPOINT C;
  /* X */
  COMMIT;
END;


ROLLBACK TO B;


ROLLBACK TO A;


p.163-164
INSERT INTO classes
   (department, course, description, max_students, 
    current_students, num_credits, room_id) 
  VALUES ('CS', 101, 'Computer Science 101', 50, 10, 4, 99998);
BEGIN
  UPDATE rooms
    SET room_id = room_id + 1;
  ROLLBACK WORK;
END;


p.164
DECLARE
  v_NumIterations   NUMBER;
BEGIN
  -- 1500܂ł̃[vɂC1`500̐ltemp_tableɑ}D
  -- R~bǵC50sƂɎsD
  FOR v_LoopCounter IN 1..500 LOOP
    INSERT INTO temp_table (num_col) VALUES (v_LoopCounter);
    v_NumIterations := v_NumIterations + 1;
    IF v_NumIterations = 50 THEN
      COMMIT;
      v_NumIterations := 0;
    END IF;
  END LOOP;
END;



Chapter4
p.166
DECLARE
  /* ⍇킹̌ʂi[邽߂̏o͕ϐ */
  v_StudentID    students.id%TYPE;
  v_FirstName    students.first_name%TYPE;
  v_LastName     students.last_name%TYPE;

  /* ⍇킹̒ŎgpoChϐy */
  v_Major        students.major%TYPE := 'Computer Science';

  /* J[\̐錾 */
  CURSOR c_Students IS
    SELECT id, first_name, last_name
      FROM students
      WHERE major = v_Major;
BEGIN
  /* ANeBuZbg̒̍s𒲂ׁÃf[^ɑ΂鑼̏̏s */
  OPEN c_Students;
  LOOP
    /* ANeBuZbg̒̊esoāAPL/SQL̕ϐɊi[ */
    FETCH c_Students INTO v_StudentID, v_FirstName, v_LastName;

    /* osȂȂA[vI */
    EXIT WHEN c_Students%NOTFOUND;
  END LOOP;

  /* ⍇킹Ŏgp\[X */
  CLOSE c_Students;
END; 


p.168
DECLARE
  v_Department   classes.department%TYPE;
  v_Course       classes.course%TYPE;
  CURSOR c_Classes IS
    SELECT * from classes
      WHERE department = v_Department
      AND course = v_Course;


DECLARE
  CURSOR c_Classes IS
    SELECT * from classes
      WHERE department = v_Department
      AND course = v_Course;
  v_Department   classes.department%TYPE;
  v_Course       classes.course%TYPE;


p.169
DECLARE
  v_RoomID      classes.room_id%TYPE;
  v_Building    rooms.building%TYPE;
  v_Department  classes.department%TYPE;
  v_Course      classes.course%TYPE;
  CURSOR c_Buildings IS
    SELECT building
      from rooms, classes
      where rooms.room_id = classes.room_id
      and department = v_Department
      and course = v_Course;
BEGIN
  -- J[\OPENOɁAoChϐɒl
  v_Department := 'HIS';
  v_Course := 101;

  -- J[\I[v
  OPEN c_Buildings;

  -- oChϐɁAlēxDAJ[\͊ɃI[vς
  -- Ȃ̂ŁȂɂe͂Ȃ
  v_Department := 'XXX';
  v_Course := -1;
END; 


p.170
FETCH c_Buildings INTO v_Building;


p.170-171
DECLARE
  v_Department  classes.department%TYPE;
  v_Course      classes.course%TYPE;
  CURSOR c_AllClasses IS
    SELECT *
      FROM classes;
  v_ClassesRecord  c_AllClasses%ROWTYPE;
BEGIN
  OPEN c_AllClasses;

  -- FETCH́A⍇킹̑IXgɍvPL/SQL̃R[h
  -- 擪sԂĂ邽ߗLłB
  FETCH c_AllClasses INTO v_ClassesRecord;

  -- FETCH͖łB́A₢킹̑IXgł
  -- \classes̗̒7ƂԂĂɂ炸A
  -- 2̕ϐɂi[ĂȂ߂łD
  FETCH c_AllClasses INTO v_Department, v_Course;
END; 


p.171
ORA-1001: Invalid Cursor 


ORA-1002: Fetch out of Sequence 


p.175
BEGIN
  UPDATE rooms
    SET number_seats = 100
    WHERE room_id = 99980;
  -- OUPDATEǂ̍sɂvȂꍇ́A
  -- \roomsɐVs1s}D
  IF SQL%NOTFOUND THEN
    INSERT INTO rooms (room_id, number_seats)
      VALUES (99980, 100);
  END IF;
END; 


BEGIN
  UPDATE rooms
    SET number_seats = 100
    WHERE room_id = 99980;
  -- OUPDATEǂ̍sɂvȂꍇ́A
  -- \roomsɐVs1s}D
  IF SQL%ROWCOUNT = 0 THEN
    INSERT INTO rooms (room_id, number_seats)
      VALUES (99980, 100);
  END IF;
END; 


ORA-1403: f[^܂B


p.175-176
DECLARE
  -- i[邽߂̃R[h
  v_RoomData   rooms%ROWTYPE;
BEGIN
  -- -1ƂID̏oD
  SELECT *
    INTO v_RoomData
    FROM rooms
    WHERE room_id = -1;

  -- Onhɐ䂪Ɉڂ邽߁A̕s邱Ƃ͂Ȃ
  IF SQL%NOTFOUND THEN
    INSERT INTO temp_table (char_col)
      VALUES ('Not found!');
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    INSERT INTO temp_table (char_col)
      VALUES ('Not found, exception handler');
END;


p.176-177
DECLARE
  -- jŮwɊւi[邽߂̕ϐ錾
  v_StudentID   students.id%TYPE;
  v_FirstName   students.first_name%TYPE;
  v_LastName    students.last_name%TYPE;

  -- jŮwɊւo߂̃J[\
  CURSOR c_HistoryStudents IS
    SELECT id, first_name, last_name
      FROM students
      WHERE major = 'History';
BEGIN
  -- J[\I[vAANeBuZbg
  OPEN c_HistoryStudents;
  LOOP
    -- ̊w̏o
    FETCH c_HistoryStudents INTO v_StudentID, v_FirstName, v_LastName;

    -- osȂȂA[vI
    EXIT WHEN c_HistoryStudents%NOTFOUND;

    -- osD̏ꍇ́A\registered_students
    -- }邱ƂɂāAj301̊ewo^D
    -- ܂Atemp_tableւ̖OƐ̋L^s
    INSERT INTO registered_students (student_id, department, course)
      VALUES (v_StudentID, 'HIS', 301);

    INSERT INTO temp_table (num_col, char_col)
      VALUES (v_StudentID, v_FirstName || ' ' || v_LastName);

  END LOOP;

  -- J[\ŎgpĂ\[X
  CLOSE c_HistoryStudents;

  -- sʂR~bg
  COMMIT;
END;


p.178
DECLARE
  -- jŮwɊւi[邽߂̕ϐ錾
  v_StudentID   students.id%TYPE;
  v_FirstName   students.first_name%TYPE;
  v_LastName    students.last_name%TYPE;

  -- jŮwɊւo߂̃J[\
  CURSOR c_HistoryStudents IS
    SELECT id, first_name, last_name
      FROM students
      WHERE major = 'History';
BEGIN
  -- J[\I[vAANeBuZbg
  OPEN c_HistoryStudents;
  LOOP
    -- ̊w̏o
    FETCH c_HistoryStudents INTO v_StudentID, v_FirstName, v_LastName;

    -- osD̏ꍇ́A\registered_students
    -- }邱ƂɂāAj101̊ewo^D
    -- ܂Atemp_tableւ̖OƐ̋L^s
    INSERT INTO registered_students (student_id, department, course)
      VALUES (v_StudentID, 'HIS', 101);

    INSERT INTO temp_table (num_col, char_col)
      VALUES (v_StudentID, v_FirstName || ' ' || v_LastName);


    -- osȂȂA[vI
    EXIT WHEN c_HistoryStudents%NOTFOUND;

  END LOOP;

  -- J[\ŎgpĂ\[X
  CLOSE c_HistoryStudents;

  -- sʂR~bg
  COMMIT;
END;


p.179
DECLARE
  -- jŮwɊւo߂̃J[\
  CURSOR c_HistoryStudents IS
    SELECT id, first_name, last_name
      FROM students
      WHERE major = 'History';

  -- oi[邽߂̃R[h錾
  v_StudentData  c_HistoryStudents%ROWTYPE;
BEGIN
  -- J[\I[vAANeBuZbg
  OPEN c_HistoryStudents;

  -- 擪soAWHILE[v̏s
  FETCH c_HistoryStudents INTO v_StudentData;

  -- [v́AosȂȂ܂ő
  WHILE c_HistoryStudents%FOUND LOOP
    -- osD̏ꍇɂ́A\registered_students
    -- }邱ƂɂāAj101̊ewo^D
    -- ܂Atemp_tableւ̖OƐ̋L^s
    INSERT INTO registered_students (student_id, department, course)
      VALUES (v_StudentData.ID, 'HIS', 101);

    INSERT INTO temp_table (num_col, char_col)
      VALUES (v_StudentData.ID,
              v_StudentData.first_name || ' ' ||
              v_StudentData.last_name);

    -- ̍soD%FOUND̏Ԃ́Ã[vɐiޑO
    -- `FbND
    FETCH c_HistoryStudents INTO v_StudentData;
  END LOOP;

  -- J[\ŎgpĂ\[X
  CLOSE c_HistoryStudents;

  -- sʂR~bg
  COMMIT;
END; 


p.180-181
DECLARE
  -- jŮwɊւo߂̃J[\
  CURSOR c_HistoryStudents IS
    SELECT id, first_name, last_name
      FROM students
      WHERE major = 'History';
BEGIN
  -- [v̊JnDc_HistoryStudents̈ÖٓIOPENA
  -- Ŏs
  FOR v_StudentData IN c_HistoryStudents LOOP
    -- ÖٓIFETCHAŎs

    -- osD̏ꍇ́A\registered_students
    -- }邱ƂɂāAj101̊ewo^D
    -- ܂Atemp_tableւ̖OƐ̋L^s
    INSERT INTO registered_students (student_id, department, course)
      VALUES (v_StudentData.ID, 'HIS', 101);

    INSERT INTO temp_table (num_col, char_col)
      VALUES (v_StudentData.ID,
              v_StudentData.first_name || ' ' ||
              v_StudentData.last_name);

    -- ̃[vɐiޑOɁAc_HistoryStudentsɊւÖٓIȃ`FbN
    -- Ŏs
  END LOOP;
  -- [vI߁Ac_HistoryStudentsɊւÖٓICLOSE
  -- Ŏs

  -- sʂR~bg
  COMMIT;
END;


p.182
DECLARE
  -- ̃J[\ł́CUPDATEɗXg`2LqĂD
  CURSOR c_AllStudents IS
    SELECT *
      FROM students
      FOR UPDATE OF first_name, last_name;

  -- ̃J[\ł́C1LqĂȂD
  CURSOR c_LargeClasses IS
    SELECT department, course
      FROM classes
      WHERE max_students > 50
      FOR UPDATE;


p.183
ORA-54: resource busy and acquire with NOWAIT specified


p.183-184
DECLARE
  -- ew̑PʐɒǉׂPʐ
  v_NumCredits  classes.num_credits%TYPE;

  -- HIS 101ɓo^ĂwÃJ[\őI
  CURSOR c_RegisteredStudents IS
    SELECT *
      FROM students
      WHERE id IN (SELECT student_id
                     FROM registered_students
                     WHERE department= 'HIS'
                     AND course = 101)
      FOR UPDATE OF current_credits;

BEGIN
  -- J[\̎o[v̏s
  FOR v_StudentInfo IN c_RegisteredStudents LOOP
  -- HIS 101̒Pʐ𒲂ׂ
  SELECT num_credits
    INTO v_NumCredits
    FROM classes
    WHERE department = 'HIS'
    AND course = 101;

  -- J[\o΂̍sXV
  UPDATE students
    SET current_credits = current_credits + v_NumCredits
    WHERE CURRENT OF c_RegisteredStudents;
  END LOOP;

  -- sʂR~bg
  COMMIT;
END;


p.184
ORA-01002: tFb`łB


p.184-185
DECLARE
  -- ׂĂ̊wo߂̃J[\DYs̃bNs
  CURSOR c_AllStudents IS
    SELECT *
      FROM students
      FOR UPDATE;

  -- of[^i[邽߂̕ϐ
  v_StudentInfo  c_AllStudents%ROWTYPE;
BEGIN
  -- J[\I[vDɂāAbN
  OPEN c_AllStudents;

  -- 擪R[ho
  FETCH c_AllStudents INTO v_StudentInfo;

  -- COMMIT𔭍sDɂ胍bNAJ[\ƂȂ
  COMMIT WORK;

  -- FETCHsƁAORA-1002̃G[ʒm
  FETCH c_AllStudents INTO v_StudentInfo;
END;


p.185-186
  -- ew̑PʐɒǉׂPʐ
  v_NumCredits  classes.num_credits%TYPE;

  -- HIS 101ɓo^ĂwÃJ[\őI
  CURSOR c_RegisteredStudents IS
    SELECT *
      FROM students
      WHERE id IN (SELECT student_id
                     FROM registered_students
                     WHERE department= 'HIS'
                     AND course = 101);

BEGIN
  -- J[\̎o[v̏s
  FOR v_StudentInfo IN c_RegisteredStudents LOOP
  -- HIS 101̒Pʐ𒲂ׂ
  SELECT num_credits
    INTO v_NumCredits
    FROM classes
    WHERE department = 'HIS'
    AND course = 101;

  -- J[\o΂̍sXV
  UPDATE students
    SET current_credits = current_credits + v_NumCredits
    WHERE id = v_Studentinfo.id;

  -- J[\FOR UPDATEwŐ錾ĂȂ߁A
  -- [v̒ŃR~bgsł
  COMMIT;
  END LOOP;
END;


p.187-188
DECLARE
  -- %ROWTYPEgp`
  TYPE t_StudentsRef IS REF CURSOR
    RETURN students%ROWTYPE;

  -- VR[h^`C
  TYPE t_NameRecord IS RECORD (
    first_name  students.first_name%TYPE,
    last_name   students.last_name%TYPE);

  -- ̌^ϐ`C
  v_NameRecord  t_NameRecord;

  -- قǂ̃R[h^gpăJ[\ϐ`D
  TYPE t_NamesRef IS REF CURSOR
    RETURN t_NameRecord;

  -- `ς݂̃R[h%TYPEŎw肷΁Cʂ̌^`łD
  TYPE t_NamesRef2 IS REF CURSOR
    RETURN v_NameRecord%TYPE;

  -- Ľ^gpāCJ[\ϐ錾D
  v_StudentCV t_StudentsRef;
  v_NameCV    t_NamesRef;


p.188
DECLARE
  -- 񖳂̎Qƌ^`D
  TYPE t_FlexibleRef IS REF CURSOR;

  -- āČ^ϐ`D
  v_CursorVar t_FlexibleRef;


p.189
EXEC SQL BEGIN DECLARE SECTION;
  SQL_CURSOR v_CursorVar;
EXEC SQL END DECLARE SECTION;

EXEC SQL ALLOCATE :v_CursorVar;


p.190
PLS-00382: ̃f[^^łB


DECLARE
  TYPE t_ClassesRef IS REF CURSOR RETURN classes%ROWTYPE;
  v_ClassesCV t_ClassesRef;


OPEN v_ClassesCV FOR
  SELECT * FROM CLASSES;


OPEN v_ClassesCV FOR
  SELECT department, course FROM CLASSES


p.191
/* CSQL̃wb_[t@CCN[h */
#include <stdio.h>
EXEC SQL INCLUDE SQLCA;


p.191-193
/* CSQL̃wb_[t@CCN[h */
#include <stdio.h>
EXEC SQL INCLUDE SQLCA;

/* SQL̐錾ZNV zXgϐׂ͂āAŐ錾邱 */
EXEC SQL BEGIN DECLARE SECTION;
  /* [UƃpX[hi[邽߂̕ */
  char *v_Username = "example/example";

  /* SQL̃J[\ϐ */
  SQL_CURSOR v_CursorVar;

  /* \̑I𐧌䂷邽߂̐^ϐ */
  int v_Table;

  /* \roomsŎgpo͕ϐ */
  int v_RoomID;
  VARCHAR v_Description[2001];

  /* \classesŎgpo͕ϐ */
  VARCHAR v_Department[4];
  int v_Course;
EXEC SQL END DECLARE SECTION;


/* G[[`DG[o͂AI */
void handle_error() {
  printf("SQL Error occurred!\n");
  printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
}
  
int main() {
  /* [U[̓͒li[邽ߕ */
  char v_Choice[20];

  /* G[̏DSQLG[
     handle_error()[`R[ */
  EXEC SQL WHENEVER SQLERROR DO handle_error();

  /* f[^x[Xɐڑ */
  EXEC SQL CONNECT :v_Username;
  printf("Connected to Oracle.\n");

  /* J[\ϐɗ̈蓖Ă */
  EXEC SQL ALLOCATE :v_CursorVar;

  /* l̓͂𑣂bZ[Wo͂A[U[̑Ie
     v_ChoiceɊi[ */
  printf("Choose from (C)lasses or (R)omms.  Enter c or r: ");
  gets(v_Choice);

  /* \肷 */
  if (v_Choice[0] == 'c')
    v_Table = 1;
  else
    v_Table = 2;

  /* ߍPL/SQLubNgpāAJ[\ϐI[v */
  EXEC SQL EXECUTE
    BEGIN
      IF :v_Table = 1 THEN
        /* \classesp̕ϐI[v */
        OPEN :v_CursorVar FOR
          SELECT department, course
            FROM classes;
      ELSE
        /* \roomsp̕ϐI[v */
        OPEN :v_CursorVar FOR
          SELECT room_id, description
            FROM rooms;
      END IF;
    END;
  END-EXEC;

  /* oIA[v𔲂 */
  EXEC SQL WHENEVER NOT FOUND DO BREAK;

  /* o[vJn */
  for (;;) {
    if (v_Table == 1) {
      /* NXo */
      EXEC SQL FETCH :v_CursorVar
        INTO :v_Department, :v_Course;

      /* NXʂɕ\Dv_Department́AVARCHAR^ł邽߁A
         ۂ̒ɂ́AlentB[hAf[^ɂ.arrtB[hgp */
      printf("%.*s %d\n", v_Department.len, v_Department.arr,
                          v_Course);
    }
    else {
      /* o */
      EXEC SQL FETCH :v_CursorVar
        INTO :v_RoomID, v_Description;

      /* ʂɕ\Dv_DescriptiońAVARCHAR^ł邽߁A
         ۂ̒ɂ́AlentB[hAf[^ɂ.arrtB[hgp */
      printf("%d %.*s\n", v_RoomID, v_Description.len,
                          v_Description.arr);
    }
  }

  /* J[\N[Y */
  EXEC SQL CLOSE :v_CursorVar;

  /* f[^x[XƂ̐ڑ */
  EXEC SQL COMMIT WORK RELEASE;
}


p.194-195
CREATE OR REPLACE PROCEDURE ShowCursorVariable
  /* T[o[ł̃J[\ϐ̎gp@ɂĐ
     p_Table'classes'̏ꍇ́A\classes̏񂪁Atemp_table
     }Dp_Table'rooms'̏ꍇ́A\rooms̏񂪁A
     temp_tableɑ} */
  (p_Table IN VARCHAR2) AS

  /* J[\ϐ̌^` */
  TYPE t_ClassesRooms IS REF CURSOR;

  /* J[\ϐ̂` */
  v_CursorVar t_ClassesRooms;

  /* o͓ei[邽߂̕ϐ */
  v_Department  classes.department%TYPE;
  v_Course      classes.course%TYPE;
  v_RoomID      rooms.room_id%TYPE;
  v_Description rooms.description%TYPE;
BEGIN
  -- ̓p[^ɏ]āAJ[\ϐI[v
  IF p_Table = 'classes' THEN
    OPEN v_CursorVar FOR
      SELECT department, course
        FROM classes;
  ELSIF p_table = 'rooms' THEN
    OPEN v_CursorVar FOR
      SELECT room_id, description
        FROM rooms;
  ELSE
    /* ͒lԈĂꍇ́AG[ʒm */
    RAISE_APPLICATION_ERROR(-20000,
      'Input must be ''classes'' or ''rooms''');
  END IF;

  /* o[vDFETCȞEXIT WHENLqĂ_ɒ
     PL/SQL 2.3ł́AJ[\ϐƂɁAJ[\̑
     gpł */
  LOOP
    IF p_Table = 'classes' THEN
      FETCH v_CursorVar INTO
        v_Department, v_Course;
      EXIT WHEN v_CursorVar%NOTFOUND;

      INSERT INTO temp_table (num_col, char_col)
        VALUES (v_Course, v_Department);
    ELSE
      FETCH v_CursorVar INTO
        v_RoomID, v_Description;
      EXIT WHEN v_CursorVAR%NOTFOUND;

      INSERT INTO temp_table (num_col, char_col)
        VALUES (v_RoomID, SUBSTR(v_Description, 1, 60));
    END IF;
  END LOOP;

  /* J[\N[Y */
  CLOSE v_CursorVar;

  COMMIT;
END ShowCursorVariable;



Chapter5
p.198
CREATE OR REPLACE PROCEDURE AddNewStudent (
  p_FirstName  students.first_name%TYPE,
  p_LastName   students.last_name%TYPE,
  p_Major      students.major%TYPE) AS
BEGIN
  -- \studentsɁAVsPs}DVwID
  -- 邽߂student_sequencegpA
  -- current_credits0ƂD
  INSERT INTO students (ID, first_name, last_name,
                        current_credits,  major)
    VALUES (student_sequence.nextval, p_FirstName, p_LastName,
            0, p_Major);

  COMMIT;
END AddNewStudent;


BEGIN
  AddNewStudent('David', 'Dinsmore', 'Music');
END;


p.199
ORA-00955: łɎgpĂIuWFNgłB


p.200
DECLARE
  -- VwϐɊi[
  v_NewFirstName  students.first_name%TYPE := 'Margaret';
  v_NewLastName   students.last_name%TYPE := 'Mason';
  v_NewMajor      students.major%TYPE := 'History';
BEGIN
  -- Margaret MasonAf[^x[Xɒǉ
  AddNewStudent(v_NewFirstName, v_NewLastName, v_NewMajor);
END;


p.200-201
CREATE OR REPLACE PROCEDURE ModeTest (
  p_InParameter    IN NUMBER,
  p_OutParameter   OUT NUMBER,
  p_InOutParameter IN OUT NUMBER) IS

  v_LocalVariable  NUMBER;
BEGIN
  /* p_InParameterv_LocalVariableɑDIN[h̃p[^
     ɑ΂āA݂ł͂ȂAǂݍ݂sĂ邽߁Ȁ
     Lȏł */
  v_LocalVariable := p_InParameter;  -- L

  /* p_InParameter7DIN[hp[^ɏ݂
     sĂ邽߂ɁȀ͖ȏł */
  p_InParameter := 7;  -- 

  /* p_OutParameter7DOUT[hp[^ɑ΂
     ǂݍ݂ł͂ȂA݂sĂ邽߁Ȁ͗L
     ł */
  p_OutParameter := 7;  -- L

  /* p_OutParameterv_LocalVariableɑDOUT[h
     p[^ɑ΂ēǂݍ݂sĂ邽߁Ȁ͖
     ł */
  v_LocalVariable := p_outParameter;  -- 

  /* p_InOutParameterv_LocalVariableɑDIN OUT[h
     p[^ɑ΂ēǂݍ݂sĂ邽߁Ȁ͗L
     ł */
  v_LocalVariable := p_InOutParameter;  -- L

  /* p_InOutParameter7DIN OUT[h̃p[^
     ɑ΂ď݂s邽߁Ȁ͗L
     ł */
  p_InOutParameter := 7;  -- L
END ModeTest;


p.202
PLS-363:	:P_INPARAMETERƂĖłB
PLS-365:	P_OUTPARAMETEROUTp[^ŁAǂނƂł܂B


DECLARE
  v_Variable1 NUMBER;
  v_Variable2 NUMBER;
BEGIN
  ModeTest(12, v_Variable1, v_Variable2);
END;


DECLARE
  v_Variable1 NUMBER;
BEGIN
  ModeTest(12, v_Variable1, 11);
END;


p.203
s:4ŃG[܂D
ORA-06550: s: 4A: 28:
PLS-00363: :11͑ƂĖłD
ORA-06550: s: 4A: 3:
PL/SQL: Statement ignored


p.204
CREATE OR REPLACE PROCEDURE ParameterLength (
  p_Parameter1 IN OUT VARCHAR2(10),
  p_Parameter2 IN OUT NUMBER(3,2)) AS
BEGIN
  p_Parameter1 := 'abcdefghijklm';
  p_Parameter2 := 12.3;
END ParameterLength;


CREATE OR REPLACE PROCEDURE ParameterLength (
  p_Parameter1 IN OUT VARCHAR2,
  p_Parameter2 IN OUT NUMBER) AS
BEGIN
  p_Parameter1 := 'abcdefghijklmno';
  p_Parameter2 := 12.3;
END ParameterLength;


DECLARE
  v_Variable1 VARCHAR2(40);
  v_Variable2 NUMBER(3,4);
BEGIN
  ParameterLength(v_Variable1, v_Variable2);
END;


p.205
DECLARE
  v_Variable1 VARCHAR2(10);
  v_Variable2 NUMBER(3,4);
BEGIN
  ParameterLength(v_Variable1, v_Variable2);
END;


ORA-06502: PL/SQL: l܂͐lG[܂B


CREATE OR REPLACE PROCEDURE ParameterLength (
  p_Parameter1 IN OUT VARCHAR2,
  p_Parameter2 IN OUT students.current_credits%TYPE) AS
BEGIN
  p_Parameter2 := 12345;
END ParameterLength;


p.206
DECLARE
  v_Variable1 VARCHAR2(1);
  v_Variable2 NUMBER; -- Declare v_Variable2 with no constraints
BEGIN
  -- w肵Ȃv_Variable2錾D12345i[ł̈悪
  -- p[^ɂĂCp[^ɑ΂鐧񂪎gp邽߁C
  -- ̃vV[WR[łORA-6502ԂD
  ParameterLength(v_Variable1, v_Variable2);
END;


CREATE OR REPLACE PROCEDURE CallMe (
  p_ParameterA VARCHAR2,
  p_ParameterB NUMBER,
  p_ParameterC BOOLEAN,
  p_ParameterD DATE) AS
BEGIN
  ...
END CallMe;


DECLARE
  v_Variable1 VARCHAR2(10);
  v_Variable2 NUMBER(7,6);
  v_Variable3 BOOLEAN;
  v_Variable4 DATE;
BEGIN
  CallMe(v_Variable1, v_Variable2, v_Variable3, v_Variable4);
END;


p.206-207
DECLARE
  v_Variable1 VARCHAR2(10);
  v_Variable2 NUMBER(7,6);
  v_Variable3 BOOLEAN;
  v_Variable4 DATE;
BEGIN
  CallMe(p_ParameterA => v_Variable1, 
         p_ParameterB => v_Variable2,
         p_ParameterC => v_Variable3,
         p_ParameterD => v_Variable4);
END;


p.207
DECLARE
  v_Variable1 VARCHAR2(10);
  v_Variable2 NUMBER(7,6);
  v_Variable3 BOOLEAN;
  v_Variable4 DATE;
BEGIN
  CallMe(p_ParameterB => v_Variable2, 
         p_ParameterC => v_Variable3,
         p_ParameterD => v_Variable4,
         p_ParameterA => v_Variable1);
END;


DECLARE
  v_Variable1 VARCHAR2(10);
  v_Variable2 NUMBER(7,6);
  v_Variable3 BOOLEAN;
  v_Variable4 DATE;
BEGIN
  -- 擪2̃p[^͈ʒu\L@Ŏw肵Cc2̃p[^
  -- O\L@Ŏw肵ĂD
  CallMe(v_Variable1, v_Variable2, 
         p_ParameterC => v_Variable3,
         p_ParameterD => v_Variable4);
END;


p.209
CREATE OR REPLACE PROCEDURE AddNewStudent (
  p_FirstName  students.first_name%TYPE,
  p_LastName   students.last_name%TYPE,
  p_Major      students.major%TYPE DEFAULT 'Economics') AS
BEGIN
  -- \studentsɁAVsPs}
  -- student_sequencegpĐVwID𐶐A
  -- current_credits0ݒ肷
  INSERT INTO students VALUES (student_sequence.nextval, 
    p_FirstName, p_LastName, p_Major, 0);

  COMMIT;
END AddNewStudent;


BEGIN
  AddNewStudent('Barbara', 'Blues');
END;


BEGIN
  AddNewStudent(p_FirstName => 'Barbara',
                p_LastName => 'Blues');
END;


p.210
CREATE OR REPLACE PROCEDURE DefaultTest (
  p_ParameterA NUMBER DEFAULT 10,
  p_ParameterB VARCHAR2 DEFAULT 'abcdef',
  p_ParameterC DATE DEFAULT SYSDATE) AS
BEGIN
  ...
END DefaultTest;


BEGIN
  DefaultTest(p_ParameterA => 7, p_ParameterC => '30-DEC-95');
END;


BEGIN
  /* p_ParameterBp_ParameterC̗ɁC
     ftHglꍇ */
  DefaultTest(7);
END;


p.211
CREATE OR REPLACE FUNCTION AlmostFull (
  p_Department classes.department%TYPE,
  p_Course     classes.course%TYPE)
  RETURN BOOLEAN IS

  v_CurrentStudents NUMBER;
  v_MaxStudents     NUMBER;
  v_ReturnValue     BOOLEAN;
  v_FullPercent     CONSTANT NUMBER := 90;
BEGIN
  -- vꂽwȂ݂̌̊wƒiőlj擾
  SELECT current_students, max_students
    INTO v_CurrentStudents, v_MaxStudents
    FROM classes
    WHERE department = p_Department
    AND course = p_Course;

  -- NX̒ɑ΂w̗v_FullPercent̃p[Ze[W
  -- 𒴂ĂꍇTRUEԂAłȂꍇFALSEԂ
  IF (v_CurrentStudents / v_MaxStudents * 100) > v_FullPercent THEN
    v_ReturnValue := TRUE;
  ELSE
    v_ReturnValue := FALSE;
  END IF;

  RETURN v_ReturnValue;
END AlmostFull;


p.211-212
DECLARE
  CURSOR c_Classes IS
    SELECT department, course
      FROM classes;
BEGIN
  FOR v_ClassRecord IN c_Classes LOOP
    -- Ȑɗ]T܂ȂNXׂ
    -- temp_tableɋL^
    IF AlmostFull(v_ClassRecord.department, v_ClassRecord.course) THEN
      INSERT INTO temp_table (char_col) VALUES
        (v_ClassRecord.department || ' ' || v_ClassRecord.course ||
         ' is almost full!');
    END IF;
  END LOOP;
END;


p.213-214
CREATE OR REPLACE FUNCTION ClassInfo (
  /* NXt̏ꍇɂ'Full'C
     NX80%𒴂Ĉt̏ꍇɂ'Some Room'C
     NX60%𒴂Ĉt̏ꍇɂ'More Room'C
     NX݋60%ȉ̏ꍇɂ'Lots of Room'C
     o^wȂꍇɂ'Empty'Ԃ */
  p_Department classes.department%TYPE,
  p_Course     classes.course%TYPE)
  RETURN VARCHAR2 IS

  v_CurrentStudents NUMBER;
  v_MaxStudents     NUMBER;
  v_PercentFull     NUMBER;
BEGIN
  -- vꂽwȂ݂̌̊wƒiő吔j擾
  SELECT current_students, max_students
    INTO v_CurrentStudents, v_MaxStudents
    FROM classes
    WHERE department = p_Department
    AND course = p_Course;

  -- ݂̍݋\p[Ze[WvZ
  v_PercentFull := v_CurrentStudents / v_MaxStudents * 100;

  IF v_PercentFull = 100 THEN
    RETURN 'Full';
  ELSIF v_PercentFull > 80 THEN
    RETURN 'Some Room';
  ELSIF v_PercentFull > 60 THEN
    RETURN 'More Room';
  ELSIF v_PercentFull > 0 THEN
    RETURN 'Lots of Room';
  ELSE
    RETURN 'Empty';
  END IF;
END ClassInfo; 


p.215
CREATE OR REPLACE PROCEDURE RaiseError (
  /* OOUT[h̕ϐ̓ɂĐ
     p_RaiseTRUȄꍇ́AG[ʒm
     p_RaiseFALSȄꍇ́A{vV[W͐ɏI */
  p_Raise IN BOOLEAN := TRUE,
  p_ParameterA OUT NUMBER) AS
BEGIN
  p_ParameterA := 7;

  IF p_Raise THEN
    /* p_ParameterA7A̖OƁA
       p_ParameterAɊYp[^7Ԃ邱ƂȂA
       䂪ɖ߂ */
    RAISE DUP_VAL_ON_INDEX;
  ELSE
    /* G[Ȃꍇ́APɃ^[D̏ꍇ́AY
       p[^7Ԃ */
    RETURN;
  END IF;
END RaiseError;


DECLARE
  v_TempVar NUMBER := 1;
BEGIN
  INSERT INTO temp_table (num_col, char_col)
    VALUES (v_TempVar, 'Initial value');
  RaiseError(FALSE, v_TempVar);

  INSERT INTO temp_table (num_col, char_col)
    VALUES (v_TempVar, 'Value after successful call');

  v_TempVar := 2;
  INSERT INTO temp_table (num_col, char_col)
    VALUES (v_TempVar, 'Value before 2nd call');
  RaiseError(TRUE, v_TempVar);
EXCEPTION
  WHEN OTHERS THEN
    INSERT INTO temp_table (num_col, char_col)
      VALUES (v_TempVar, 'Value after unsuccessful call');
END;


p.216
SQL> SELECT * FROM temp_table;

  NUM_COL CHAR_COL
--------- --------------------------------
        1 Initial value
        7 Value after successful call
        2 Value before 2nd call
        2 Value after unsuccessful call


p.216
DROP PROCEDURE AddNewStudent;


p.217
CREATE OR REPLACE PROCEDURE Simple AS
  v_Counter NUMBER;
BEGIN
  v_Counter := 7;
END Simple;


p.218
CREATE OR REPLACE PROCEDURE Simple AS
  v_Counter NUMBER;
BEGIN
  v_Counter := 7
END Simple;


p.220
PLS-905: IuWFNg:<O>͖łD


DECLARE
  CURSOR c_AllStudents IS
    SELECT first_name, last_name
      FROM students;

  v_FormattedName VARCHAR2(50);

  /* ̊֐ł́AOƐ󔒂PŘAgĕԂ */
  FUNCTION FormatName(p_FirstName IN VARCHAR2,
                      p_LastName IN VARCHAR2)
    RETURN VARCHAR2 IS
  BEGIN
    RETURN p_FirstName || ' ' || p_LastName;
  END FormatName;

-- CubN̊Jn
BEGIN
  FOR v_StudentRecord IN c_AllStudents LOOP
    v_FormattedName := 
      FormatName(v_StudentRecord.first_name, 
                 v_StudentRecord.last_name);  
    INSERT INTO temp_table (char_col)
      VALUES (v_FormattedName);
  END LOOP;

  COMMIT;
END;


p.221
DECLARE
  /* FormatName܂錾D̂悤ȂƂsƁCRpCG[
     ɂȂDȂȂC[JȃTuvÓĈׂ̂Đ錾
     Ő錾Ȃ΂ȂȂ߂łD /*
  FUNCTION FormatName(p_FirstName IN VARCHAR2,
                      p_LastName IN VARCHAR2)
    RETURN VARCHAR2 IS
  BEGIN
    RETURN p_FirstName || ' ' || p_LastName;
  END FormatName;

  CURSOR c_AllStudents IS
    SELECT first_name, last_name
      FROM students;

  v_FormattedName VARCHAR2(50);
-- CubN̊JnD
BEGIN
  NULL;
END;


p.221-222
DECLARE
  v_TempVal BINARY_INTEGER := 5;

  -- [JvV[WADvV[WBR[Ă_ɒӁD
  PROCEDURE A(p_Counter IN OUT BINARY_INTEGER) IS
  BEGIN
    IF p_Counter > 0 THEN
      B(p_Counter);
      p_Counter := p_Counter - 1;
    END IF;
  END A;

  -- [JvV[WBDvV[WAR[Ă_ɒӁD
  PROCEDURE B(p_Counter IN OUT BINARY_INTEGER) IS
  BEGIN
    p_Counter := p_Counter - 1;
    A(p_Counter);
  END B;
BEGIN
  B(v_TempVal);
END;


p.222-223
DECLARE
  v_TempVal BINARY_INTEGER := 5;

  -- vV[WB̑O錾
  PROCEDURE B(p_Counter IN OUT BINARY_INTEGER);

  PROCEDURE A(p_Counter IN OUT BINARY_INTEGER) IS
  BEGIN
    IF p_Counter > 0 THEN
      B(p_Counter);
      p_Counter := p_Counter - 1;
    END IF;
  END A;

  PROCEDURE B(p_Counter IN OUT BINARY_INTEGER) IS
  BEGIN
    p_Counter := p_Counter - 1;
    A(p_Counter);
  END B;
BEGIN
  B(v_TempVal);
END;


p.224
CREATE OR REPLACE PROCEDURE RecordFullClasses AS
  CURSOR c_Classes IS
    SELECT department, course
      FROM classes;
BEGIN
  FOR v_ClassRecord IN c_Classes LOOP
    -- Ȑɗ]T܂ȂNXׂtemp_tableɋL^
    IF AlmostFull(v_ClassRecord.department, v_ClassRecord.course) THEN
      INSERT INTO temp_table (char_col) VALUES
        (v_ClassRecord.department || ' ' || v_ClassRecord.course ||
         ' is almost full!');
    END IF;
  END LOOP;
END RecordFullClasses;


p.225
ALTER TABLE classes ADD (
  student_rating  NUMBER(2)); -- Փx\li1`10jD


p.227
P2(...)@DBLINK 


p.229-230
CREATE OR REPLACE PACKAGE ClassPackage AS
  -- w肳ꂽNXɐVw1lǉ
  PROCEDURE AddStudent(p_StudentID  IN students.id%TYPE,
                       p_Department IN classes.department%TYPE, 
                       p_Course     IN classes.course%TYPE);

  -- w肳ꂽNXAw肳ꂽw폜
  PROCEDURE RemoveStudent(p_StudentID  IN students.id%TYPE,
                          p_Department IN classes.department%TYPE, 
                          p_Course     IN classes.course%TYPE);

  -- RemoveStudentʒmO
  e_StudentNotRegistered EXCEPTION;

  -- ẘi[Ɏgp\̌^
  TYPE t_StudentIDTable IS TABLE OF students.id%TYPE
    INDEX BY BINARY_INTEGER;

  -- w肳ꂽNXɌݏĂwi[ꂽPL/SQL\Ԃ
  PROCEDURE ClassList(p_Department  IN  classes.department%TYPE,
                      p_Course      IN  classes.course%TYPE,
                      p_IDs         OUT t_StudentIDTable,
                      p_NumStudents IN OUT BINARY_INTEGER);
END ClassPackage;


p.231-232
CREATE OR REPLACE PACKAGE BODY ClassPackage AS
  -- w肳ꂽNXɐVw1lǉ
  PROCEDURE AddStudent(p_StudentID  IN students.id%TYPE,
                       p_Department IN classes.department%TYPE, 
                       p_Course     IN classes.course%TYPE) IS
  BEGIN
    INSERT INTO registered_students (student_id, department, course)
      VALUES (p_StudentID, p_Department, p_Course);
    COMMIT;
  END AddStudent;


  -- w肳ꂽNXAw肳ꂽw폜
  PROCEDURE RemoveStudent(p_StudentID  IN students.id%TYPE,
                          p_Department IN classes.department%TYPE, 
                          p_Course     IN classes.course%TYPE) IS
  BEGIN
    DELETE FROM registered_students
      WHERE student_id = p_StudentID
      AND department = p_Department
      AND course = p_Course;

    -- DELETEǂA`FbND
    -- vsȂꍇ́AG[ʒm
    IF SQL%NOTFOUND THEN
      RAISE e_StudentNotRegistered;
    END IF;

    COMMIT;
  END RemoveStudent;


  -- w肳ꂽNXɌݏĂwi[ꂽPL/SQL\
  -- Ԃ
  PROCEDURE ClassList(p_Department  IN  classes.department%TYPE,
                      p_Course      IN  classes.course%TYPE,
                      p_IDs         OUT t_StudentIDTable,
                      p_NumStudents IN OUT BINARY_INTEGER) IS

    v_StudentID  registered_students.student_id%TYPE;

    -- o^ς݂̊wo߂̃[JȃJ[\
    CURSOR c_RegisteredStudents IS
      SELECT student_id
        FROM registered_students
        WHERE department = p_Department
        AND course = p_Course;
  BEGIN
    /* p_NumStudentśA\̍ƂĎgpD
       ܂A0n߁Ao[vs邽т+1ɂD
       [v̏Iɂ́AošɂȂ邽߁A
       p_IDsɕԂꂽšɓȂ */
    p_NumStudents := 0;

    OPEN c_RegisteredStudents;
    LOOP
      FETCH c_RegisteredStudents INTO v_StudentID;
      EXIT WHEN c_RegisteredStudents%NOTFOUND;

      p_NumStudents := p_NumStudents + 1;
      p_IDs(p_NumStudents) := v_StudentID;
    END LOOP;
  END ClassList;
END ClassPackage;


p.233
CREATE OR REPLACE PACKAGE PackageA AS
  FUNCTION FunctionA(p_Parameter1 IN NUMBER,
                     p_Parameter2 IN DATE)
    RETURN VARCHAR2;
END PackageA;

CREATE OR REPLACE PACKAGE BODY PackageA AS
  FUNCTION FunctionA(p_Parameter1 IN CHAR)
    RETURN VARCHAR2;
END PackageA;


p.233
PLS-00328: 錾:FUNCTIONAɃTuvO{̂`Ȃ΂Ȃ܂B


BEGIN
  ClassPackage.RemoveStudent(10006, 'HIS', 101);
END;


p.234
DECLARE
  v_HistoryStudents ClassPackage.t_StudentIDTable;
  v_NumStudents     BINARY_INTEGER := 20;
BEGIN
  -- j101̐擪20̊wAPL/SQL\Ɋi[
  ClassPackage.ClassList('HIS', 101, v_HistoryStudents,
                         v_NumStudents);

  -- ̊wAtemp_tableɑ}
  FOR v_LoopCounter IN 1..v_NumStudents LOOP
    INSERT INTO temp_table (num_col, char_col)
      VALUES (v_HistoryStudents(v_LoopCounter), 
              'In History 101');
  END LOOP;
END;


p.234-235
CREATE OR REPLACE PACKAGE ClassPackage AS
  -- w肳ꂽNXɁAVw1lǉ
  PROCEDURE AddStudent(p_StudentID  IN students.id%TYPE,
                       p_Department IN classes.department%TYPE, 
                       p_Course     IN classes.course%TYPE);

  -- IDԍł͂ȂAw肷邱Ƃɂ
  -- Vw1lǉ
  PROCEDURE AddStudent(p_FirstName IN students.first_name%TYPE,
                       p_LastName  IN students.last_name%TYPE,
                       p_Department IN classes.department%TYPE, 
                       p_Course     IN classes.course%TYPE);
  
END ClassPackage;

CREATE OR REPLACE PACKAGE BODY ClassPackage AS
  -- w肳ꂽNXɁAVw1lǉ
  PROCEDURE AddStudent(p_StudentID  IN students.id%TYPE,
                       p_Department IN classes.department%TYPE, 
                       p_Course     IN classes.course%TYPE) IS
  BEGIN
    INSERT INTO registered_students (student_id, department, course)
      VALUES (p_StudentID, p_Department, p_Course);
    COMMIT;
  END AddStudent;

  -- IDł͂ȂOw肷邱ƂɂāAVw1lǉ
  PROCEDURE AddStudent(p_FirstName IN students.first_name%TYPE,
                       p_LastName  IN students.last_name%TYPE,
                       p_Department IN classes.department%TYPE, 
                       p_Course     IN classes.course%TYPE) IS
    v_StudentID students.ID%TYPE;
  BEGIN
    /* ܂A\studentsID擾Kv */
    SELECT ID
      INTO v_StudentID
      FROM students
      WHERE first_name = p_FirstName
      AND last_name = p_LastName;

    -- ID擾łAIDɂĊwǉł
    INSERT INTO registered_students (student_id, department, course)
      VALUES (v_StudentID, p_Department, p_Course);
    COMMIT;
  END AddStudent;
  
END ClassPackage;


p.235
BEGIN
  ClassPackage.AddStudent(10000, 'MUS', 410);
END;


p.236
BEGIN
  ClassPackage.AddStudent('Barbara', 'Blues', 'MUS', 410);
END;


p.236-238
CREATE OR REPLACE PACKAGE Random AS
/* ̔DCrand()֐ƓASYgp */

  -- V[h̕ύXɎgpAV[hł΁A
  -- V[PXŐ
  PROCEDURE ChangeSeed(p_NewSeed IN NUMBER);

  -- 132767͈̔͂ɂ鐮̗Ԃ
  FUNCTION Rand RETURN NUMBER;
  PRAGMA RESTRICT_REFERENCES(rand, WNDS );

  -- RandƓlł邪AvV[W^̃C^[tF[XĂ
  PROCEDURE GetRand(p_RandomNumber OUT NUMBER);

  -- 1p_MaxVal͈̔͂ɂ鐮̗Ԃ
  FUNCTION RandMax(p_MaxVal IN NUMBER) RETURN NUMBER;
  PRAGMA RESTRICT_REFERENCES(RandMax, WNDS);

  -- RandMaxƓlł邪AvV[W^̃C^[tF[XĂ
  PROCEDURE GetRandMax(p_RandomNumber OUT NUMBER,
                       p_MaxVal IN NUMBER);
END Random;
/

CREATE OR REPLACE PACKAGE BODY Random AS

  /* ̐ľvZɎgp */
  v_Multiplier  CONSTANT NUMBER := 22695477;
  v_Increment   CONSTANT NUMBER := 1;

  /* A̗𔭐邽߂̃V[h */
  v_Seed        number := 1;

  PROCEDURE ChangeSeed(p_NewSeed IN NUMBER) IS
  BEGIN
    v_Seed := p_NewSeed;
  END ChangeSeed;

  FUNCTION Rand RETURN NUMBER IS
  BEGIN
    v_Seed := MOD(v_Multiplier * v_Seed + v_Increment,
                  (2 ** 32));
    RETURN BITAND(v_Seed/(2 ** 16), 32767);
  END Rand;

  PROCEDURE GetRand(p_RandomNumber OUT NUMBER) IS
  BEGIN
    -- PRandR[AlԂ
    p_RandomNumber := Rand;
  END GetRand;

  FUNCTION RandMax(p_MaxVal IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN MOD(Rand, p_MaxVal) + 1;
  END RandMax;

  PROCEDURE GetRandMax(p_RandomNumber OUT NUMBER,
                       p_MaxVal IN NUMBER) IS
  BEGIN
    -- PRandMaxR[AlԂ
    p_RandomNumber := RandMax(p_MaxVal);
  END GetRandMax;

BEGIN
  /* pbP[W̏DV[h̒l݂̎ibPʁjŏ */
  ChangeSeed(TO_NUMBER(TO_CHAR(SYSDATE, 'SSSSS')));
END Random;


p.239
CREATE TABLE major_stats (
  major          VARCHAR2(30),
  total_credits  NUMBER,
  total_students NUMBER);



p.240
CREATE OR REPLACE TRIGGER UpdateMajorStats
  /* \major_stats\up-to-dateɕύX邽тɍXVA
     ŐV̏ԂɈێ */
  AFTER INSERT OR DELETE OR UPDATE ON students
DECLARE
  CURSOR c_Statistics IS
    SELECT major, COUNT(*) total_students,
           SUM(current_credits) total_credits
      FROM students
      GROUP BY major;
BEGIN
  /* eU[vŒׁAUɑΉĂmajor_stats̒
     vXVDYs݂Ȃꍇ͍쐬 */
  FOR v_StatsRecord in c_Statistics LOOP
    UPDATE major_stats
      SET total_credits = v_StatsRecord.total_credits,
          total_students = v_StatsRecord.total_students
      WHERE major = v_StatsRecord.major;
    /* Ys݂Ă邩A`FbN */
    IF SQL%NOTFOUND THEN
      INSERT INTO major_stats (major, total_credits, total_students)
        VALUES (v_StatsRecord.major, v_StatsRecord.total_credits,
                v_StatsRecord.total_students);
    END IF;
  END LOOP;
END UpdateMajorStats;


p.243
SQL> SELECT trigger_type, table_name, triggering_event
  2    FROM user_triggers
  3    WHERE trigger_name = 'UPDATEMAJORSTATS';

TRIGGER_TYPE     TABLE_NAME     TRIGGERING_EVENT
---------------- -------------- --------------------------
AFTER STATEMENT  STUDENTS       INSERT OR UPDATE OR DELETE


p.244
SQL> ALTER TRIGGER UpdateMajorStats DISABLE
Trigger altered.

SQL> ALTER TRIGGER UpdateMajorStats ENABLE;
Trigger altered.


SQL> ALTER TABLE students
  2    ENABLE ALL TRIGGERS;
Table altered.

SQL> ALTER TABLE students
  2    DISABLE ALL TRIGGERS;
Table altered.


p.245
UPDATE classes
  SET num_credits = 4
  WHERE department IN ('HIS', 'CS');


p.247-248
CREATE OR REPLACE TRIGGER GenerateStudentID
  BEFORE INSERT OR UPDATE ON students
  FOR EACH ROW
BEGIN
  /* student_sequence琶̒lAstudents
     IDtB[hɊi[DID́Astudents̗ł邽߁A
     :new.ID́ALȎQƂł */
  SELECT student_sequence.nextval
    INTO :new.ID
    FROM dual;
END GenerateStudentID;


p.248
I INSERT INTO students (first_name, last_name)
  VALUES ('Lolita', 'Lazarus');


INSERT INTO students (ID, first_name, last_name)
  VALUES (-7, 'Lolita', 'Lazarus');


p.249
CREATE OR REPLACE TRIGGER CheckCredits
  BEFORE INSERT OR UPDATE OF current_credits ON students
  FOR EACH ROW
  WHEN (new.current_credits > 20)
BEGIN
  /* ɂ́CgK[̖{̂LqD */
END;


CREATE OR REPLACE TRIGGER CheckCredits
  BEFORE INSERT OR UPDATE OF current_credits ON students
  FOR EACH ROW
BEGIN
  IF :new.current_credits > 20 THEN
    /* ɂ́CgK[̖{̂LqD */
  END IF;
END;


p.250
CREATE TABLE RS_audit (
  old_student_id NUMBER(5),
  old_department CHAR(3),
  old_course     NUMBER(3),
  old_grade      CHAR(1),
  new_student_id NUMBER(5),
  new_department CHAR(3),
  new_course     NUMBER(3),
  new_grade      CHAR(1),
  changed_by     VARCHAR2(8),
  timestamp      DATE
  );


p.250-251
CREATE OR REPLACE TRIGGER LogRSChanges
  BEFORE INSERT OR DELETE OR UPDATE ON registered_students
  FOR EACH ROW
DECLARE
  v_ChangeType CHAR(1);
BEGIN
  /* Use 'I' for an INSERT, 'D' for DELETE, and 'U' for UPDATE. */
  IF INSERTING THEN
    v_ChangeType := 'I';
  ELSIF UPDATING THEN
    v_ChangeType := 'U';
  ELSE
    v_ChangeType := 'D';
  END IF;

  /* registered_studentsɎsꂽύXeׂāARS_auditɋL^
     ^CX^v𐶐邽߂SYSDATEA
     ݂̃[U[̃[U[IDԂ߂USERgp */
  INSERT INTO RS_audit
    (change_type, changed_by, timestamp,
     old_student_id, old_department, old_course, old_grade, 
     new_student_id, new_department, new_course, new_grade)
  VALUES
    (v_ChangeType, USER, SYSDATE,
     :old.student_id, :old.department, :old.course, :old.grade,
     :new.student_id, :new.department, :new.course, :new.grade);
END LogRSChanges;*/


p.251
CREATE TABLE registered_students (
  student_id NUMBER(5) NOT NULL,
  department CHAR(3)   NOT NULL,
  course     NUMBER(3) NOT NULL,
  grade      CHAR(1),
  CONSTRAINT rs_grade
    CHECK (grade IN ('A', 'B', 'C', 'D', 'E')),
  CONSTRAINT rs_student_id
    FOREIGN KEY (student_id) REFERENCES students (id),
  CONSTRAINT rs_department_course
    FOREIGN KEY (department, course) 
    REFERENCES classes (department, course)
  );


p.252-253
CREATE OR REPLACE TRIGGER CascadeRSInserts
  /* registered_students, students, classesƂ\𓯊  */
  BEFORE INSERT ON registered_students
  FOR EACH ROW
DECLARE
  v_Credits classes.num_credits%TYPE;
BEGIN
  -- YNX̒Pʐ𒲂ׂ
  SELECT num_credits
    INTO v_Credits
    FROM classes
    WHERE department = :new.department
    AND course = :new.course;

  -- Yw݂̌̒PʐύX
  UPDATE students
    SET current_credits = current_credits + v_Credits
    WHERE ID = :new.student_id;

  -- YNX̊w1𑫂
  UPDATE classes
    SET current_students = current_students + 1
    WHERE department = :new.department
    AND course = :new.course;
END CascadeRSInserts;


p.253-254
CREATE OR REPLACE TRIGGER LimitMajors
  /* eŮw5ɐ
     ̐l𒴂ARAISE_APPLICATION_ERRORɂ
     G[ʒm */
  BEFORE INSERT OR UPDATE OF major ON students
  FOR EACH ROW
DECLARE
  v_MaxStudents CONSTANT NUMBER := 5;
  v_CurrentStudents NUMBER;
BEGIN
  -- YǓ݂̊w𒲂ׂ
  SELECT COUNT(*)
    INTO v_CurrentStudents
    FROM students
    WHERE major = :new.major;

  -- ǉł]TȂꍇ́AG[ʒm
  IF v_CurrentStudents + 1 > v_MaxStudents THEN
    RAISE_APPLICATION_ERROR(-20000, 
      'Too many students in major ' || :new.major);
  END IF;
END LimitMajors;


p.254
SQL> UPDATE students
  2    SET major = 'History'
  3    WHERE ID = 10003;
UPDATE students
 *s:1ŃG[܂B
ORA-04091: \EXAMPLE.STUDENTS͕ύXȂ̂ŃgK[͂̕\Ǎ/C邱Ƃł܂B
ORA-06512: "EXAMPLE.LIMITMAJORS", s: 6
ORA-04088: gK[: EXAMPLE.LIMITMAJORS̎sɃG[܂B


p.254-256
CREATE OR REPLACE PACKAGE StudentData AS
  TYPE t_Majors IS TABLE OF students.major%TYPE
    INDEX BY BINARY_INTEGER;
  TYPE t_IDs IS TABLE OF students.ID%TYPE
    INDEX BY BINARY_INTEGER;

  v_StudentMajors t_Majors;
  v_StudentIDs    t_IDs;
  v_NumEntries    BINARY_INTEGER := 0;
END StudentData;
/

CREATE OR REPLACE TRIGGER RLimitMajors
  BEFORE INSERT OR UPDATE OF major ON students
  FOR EACH ROW
BEGIN
  /* StudentDataɁAVf[^L^DORA-4091ƂG[
     o͂̂邽߂ɁAstudentsɂ͕ύXȂ */
  StudentData.v_NumEntries := StudentData.v_NumEntries + 1;
  StudentData.v_StudentMajors(StudentData.v_NumEntries) := 
    :new.major;
  StudentData.v_StudentIDs(StudentData.v_NumEntries) := :new.id;
END RLimitMajors;

CREATE OR REPLACE TRIGGER SLimitMajors
  AFTER INSERT OR UPDATE OF major ON students
DECLARE
  v_MaxStudents     CONSTANT NUMBER := 5;
  v_CurrentStudents NUMBER;
  v_StudentID       students.ID%TYPE;
  v_Major           students.major%TYPE;
BEGIN
  /* }܂͍XVꂽew[vŒ
     ܂͈͓̔ɂ邩؂ */
  FOR v_LoopIndex IN 1..StudentData.v_NumEntries LOOP
    v_StudentID := StudentData.v_StudentIDs(v_LoopIndex);
    v_Major := StudentData.v_StudentMajors(v_LoopIndex);

    -- YǓ݂̊w𒲂ׂ
    SELECT COUNT(*)
      INTO v_CurrentStudents
      FROM students
      WHERE major = v_Major;

    -- ǉł]TȂꍇ́AG[ʒm
    IF v_CurrentStudents > v_MaxStudents THEN
      RAISE_APPLICATION_ERROR(-20000, 
        'Too many students for major ' || v_Major ||
        ' because of student ' || v_StudentID);
    END IF;
  END LOOP;

  -- ̎sŐVf[^gp悤AJE^Zbg
  StudentData.v_NumEntries := 0;
END SLimitMajors;


p.256
SQL> UPDATE students
  2    SET major = 'History'
  3    WHERE ID = 10003;
1 row updated.

SQL> UPDATE students
  2    SET major = 'History'
  3    WHERE ID = 10002;
1 row updated.

SQL> UPDATE students
  2    SET major = 'History'
  3    WHERE ID = 10009;
UPDATE students
 *
s:1ŃG[܂B
ORA-20000: Too many students for major History because of student 10009
ORA-06512: "EXAMPLE.SLIMITMAJORS", s: 19
ORA-04088: gK[: EXAMPLE.SLIMITMAJORS̎sɃG[܂B


p.257-258
CREATE OR REPLACE PROCEDURE RecordFullClasses AS
  CURSOR c_Classes IS
    SELECT department, course
      FROM classes;
BEGIN
  FOR v_ClassRecord IN c_Classes LOOP
    -- Ȑɗ]T܂ȂNXC
    -- ׂtemp_tableɋL^D
    IF AlmostFull(v_ClassRecord.department, v_ClassRecord.course) THEN
      INSERT INTO temp_table (char_col) VALUES
        (v_ClassRecord.department || ' ' || v_ClassRecord.course ||
         ' is almost full!');
    END IF;
  END LOOP;
END RecordFullClasses;


p.258
GRANT EXECUTE on RecordFullClasses TO UserB;


BEGIN
  UserA.RecordFullClasses;
END;


p.260
CREATE OR REPLACE PROCEDURE RecordFullClasses AS
  CURSOR c_Classes IS
    SELECT department, course
      FROM UserA.classes;
BEGIN
  FOR v_ClassRecord IN c_Classes LOOP
    -- Ȑɗ]T܂ȂNXC
    -- ׂtemp_tableɋL^D
    IF UserA.AlmostFull(v_ClassRecord.department, 
                        v_ClassRecord.course) THEN
      INSERT INTO temp_table (char_col) VALUES
        (v_ClassRecord.department || ' ' || v_ClassRecord.course ||
         ' is almost full!');
    END IF;
  END LOOP;
END RecordFullClasses;


p.261
GRANT SELECT ON classes TO UserB;
GRANT EXECUTE ON AlmostFull TO UserB;


CREATE ROLE UserA_Role;
GRANT SELECT ON classes TO UserA_Role;
GRANT EXECUTE ON AlmostFull TO UserA_Role;
GRANT UserA_Role to UserB;


PLS-201:	ʎq: CLASSES͐錾Ă܂B
PLS-201:	ʎq: ALMOSTFULL͐錾Ă܂B


p.264
CREATE OR REPLACE FUNCTION FullName (
  p_StudentID  students.ID%TYPE)
  RETURN VARCHAR2 IS

  v_Result  VARCHAR2(100);
BEGIN
  SELECT first_name || ' ' || last_name
    INTO v_Result
    FROM students
    WHERE ID = p_StudentID;

  RETURN v_Result;
END FullName;


p.264-265
SQL> SELECT ID, FullName(ID) "Full Name"
  2    FROM students;

       ID Full Name
--------- -------------------------------
    10000 Scott Smith
    10001 Margaret Mason
    10002 Joanne Junebug
    10003 Manish Murgratroid
    10004 Patrick Poll
    10005 Timothy Taller
    10006 Barbara Blues
    10007 David Dinsmore
    10008 Ester Elegant
    10009 Rose Riznit
    10010 Rita Razmataz

11 rows selected.
SQL> INSERT INTO temp_table (char_col)
  2    VALUES (FullName(10010));

1 row created.


p.265-266
CREATE OR REPLACE PACKAGE StudentOps AS
  FUNCTION FullName(p_StudentID IN students.ID%TYPE)
    RETURN VARCHAR2;
  PRAGMA RESTRICT_REFERENCES(FullName, WNDS, WNPS, RNPS);

  /* jŮwԂ */
  FUNCTION NumHistoryMajors
    RETURN NUMBER;
  PRAGMA RESTRICT_REFERENCES(NumHistoryMajors, WNDS, WNPS, RNPS);
END StudentOps;
/

CREATE OR REPLACE PACKAGE BODY StudentOps AS

  -- jŮwi[邽߂̃pbP[Wϐ
  v_NumHist NUMBER;

  FUNCTION FullName(p_StudentID IN students.ID%TYPE)
    RETURN VARCHAR2 IS
    v_Result  VARCHAR2(100);
  BEGIN
    SELECT first_name || ' ' || last_name
      INTO v_Result
      FROM students
      WHERE ID = p_StudentID;

    RETURN v_Result;
  END FullName;

  FUNCTION NumHistoryMajors RETURN NUMBER IS
    v_Result NUMBER;
  BEGIN
    IF v_NumHist IS NULL THEN
      /* ړÎ̂擾 */
      SELECT COUNT(*)
        INTO v_Result
        FROM students
        WHERE major = 'History';
      /* 擾̂AŎgpł悤ɕۑ */
      v_NumHist := v_Result;
    ELSE
      v_Result := v_NumHist;
    END IF;

    RETURN v_Result;
  END NumHistoryMajors;
END StudentOps;


p.267
CREATE OR REPLACE PACKAGE StudentOps AS
  PRAGMA RESTRICT_REFERENCES (StudentOps, WNDS, WNPS, RNPS);
  ...
END StudentOps;


CREATE OR REPLACE PACKAGE TestPackage AS
  FUNCTION F(p_ParameterOne IN NUMBER) RETURN VARCHAR2;
  FUNCTION F RETURN DATE;
  PRAGMA RESTRICT_REFERENCES(F, WNDS, RNDS);
END TestPackage;


SELECT FullName(p_StudentID => 10000) FROM dual;



Chapter6
p.270
ORA-1: unique constraint violated 


ORA-06502: PL/SQL: numeric or value error 


int x, y, z;
f(x); /* ֐R[DƂxnD */
if <G[ꍇ>
  handle_error(...);
y = 1 / z;
if <G[ꍇ>
  handle_error(...);
z = x + y;
if <G[ꍇ>
  handle_error(...);


p.271
DECLARE
  x NUMBER;
  y NUMBER;
  z NUMBER;
BEGIN
  f(x);
  y := 1 / z;
  z := x + y;
EXCEPTION
  WHEN OTHERS THEN
    /* ׂẴG[邽߂̃nh */
    handle_error(...);
END;


p.272
DECLARE
  e_TooManyStudents EXCEPTION;


p.273
DECLARE
  TYPE t_NumberTableType IS TABLE OF NUMBER
    INDEX BY BINARY_INTEGER;
  v_NumberTable t_NumberTableType;
  v_TempVar NUMBER;
BEGIN  
  v_TempVar := v_NumberTable(1);
END;


p.274
INSERT INTO students (id, first_name, last_name)
  VALUES ('X', 'Scott', 'Smith');


DECLARE
  v_TempVar VARCHAR2(3);
BEGIN
  v_TempVar := 'ABCD';
END;

DECLARE
  v_TempVar NUMBER(2);
BEGIN
  SELECT id
    INTO v_TempVar
    FROM students
    WHERE last_name = 'Smith';
END;


p.275
DECLARE
  e_TooManyStudents EXCEPTION;  -- G[̏O
  v_CurrentStudents NUMBER(3);  -- HIS-101Ɍݓo^ς݂̊w
  v_MaxStudents NUMBER(3);      -- HIS-101ɓo^łw̍ő吔
BEGIN
  /* ݓo^ς݂̊wƁAo^łw̍ő吔𒲂ׂ */
  SELECT current_students, max_students
    INTO v_CurrentStudents, v_MaxStudents
    FROM classes
    WHERE department = 'HIS' AND course = 101;
  /* YNX̊w`FbN */
  IF v_CurrentStudents > v_MaxStudents THEN
    /* o^Ώۂ̊wꍇ --Oʒm */
    RAISE e_TooManyStudents;
  END IF;
END;


BEGIN
  INSERT INTO students (id, first_name, last_name)
    VALUES (10001, 'John', 'Smith');
  INSERT INTO students (id, first_name, last_name)
    VALUES (10001, 'Susan', 'Ryan');
END;


p.276
ORA-0001: Ӑ: <O>ɔĂ܂B


p.277
DECLARE
  e_TooManyStudents EXCEPTION;  -- G[̏O
  v_CurrentStudents NUMBER(3);  -- HIS-101Ɍݓo^ς݂̊w
  v_MaxStudents NUMBER(3);      -- HIS-101ɓo^łw̍ő吔
BEGIN
  /* ݓo^ς݂̊wƁAo^łw̍ő吔𒲂ׂ */
  SELECT current_students, max_students
    INTO v_CurrentStudents, v_MaxStudents
    FROM classes
    WHERE department = 'HIS' AND course = 101;
  /* YNX̊w`FbN */
  IF v_CurrentStudents > v_MaxStudents THEN
    /* o^Ώۂ̊wꍇ -- Oʒm */
    RAISE e_TooManyStudents;
  END IF;
EXCEPTION
  WHEN e_TooManyStudents THEN
    /* HIS-101ɓo^ׂwꍇɎsnh
       󋵂邽߂̃ObZ[W} */
    INSERT INTO log_table (info) VALUES ('History 101 has ' || 
     v_CurrentStudents ||
      'students: max allowed is ' || v_MaxStudents);
END;


EXCEPTION
  WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
    INSERT INTO log_table (info) VALUES ('A select error occurred.');
END;


p.278
DECLARE
  e_TooManyStudents EXCEPTION;  -- G[̏O
  v_CurrentStudents NUMBER(3);  -- HIS-101Ɍݓo^ς݂̊w
  v_MaxStudents NUMBER(3);      -- HIS-101ɓo^łw̍ő吔
BEGIN
  /* ݓo^ς݂̊wƁAo^łw̍ő吔𒲂ׂ */
  SELECT current_students, max_students
    INTO v_CurrentStudents, v_MaxStudents
    FROM classes
    WHERE department = 'HIS' AND course = 101;
  /* YNX̊w`FbN */
  IF v_CurrentStudents > v_MaxStudents THEN
    /* o^Ώۂ̊wꍇ -- Oʒm */
    RAISE e_TooManyStudents;
  END IF;
EXCEPTION
  WHEN e_TooManyStudents THEN
    /* HIS-101ɓo^ׂwꍇɎsnh
       󋵂邽߂̃ObZ[W} */
    INSERT INTO log_table (info) VALUES ('History 101 has ' ||
     v_CurrentStudents ||
      'students: max allowed is ' || v_MaxStudents);
  WHEN OTHERS THEN
    /* ׂ̑̂ẴG[ɑ΂Ďsnh */
    INSERT INTO log_table (info) VALUES ('Another error occurred');
END;


p.279
DECLARE
  e_TooManyStudents EXCEPTION;  -- G[̏O
  v_CurrentStudents NUMBER(3);  -- HIS-101Ɍݓo^ς݂̊w
  v_MaxStudents NUMBER(3);      -- HIS-101ɓo^łw̍ő吔
  v_ErrorCode NUMBER;           -- G[bZ[W̃R[hi[p̕ϐ
  v_ErrorText VARCHAR2(200);    -- G[bZ[W̃eLXgi[p̕ϐ

BEGIN
  /* ݓo^ς݂̊wƁAo^łw̍ő吔𒲂ׂ */
  SELECT current_students, max_students
    INTO v_CurrentStudents, v_MaxStudents
    FROM classes
    WHERE department = 'HIS' AND course = 101;
  /* YNX̊w`FbN */
  IF v_CurrentStudents > v_MaxStudents THEN
    /* o^Ώۂ̊wꍇ -- Oʒm */
    RAISE e_TooManyStudents;
  END IF;
EXCEPTION
  WHEN e_TooManyStudents THEN
    /* HIS-101ɓo^ׂwꍇɎsnh
       󋵂邽߂̃ObZ[W} */
    INSERT INTO log_table (info) VALUES ('History 101 has ' ||
     v_CurrentStudents ||
      'students: max allowed is ' || v_MaxStudents);
  WHEN OTHERS THEN
    /* ׂ̑̂ẴG[ɑ΂Ďsnh */
    v_ErrorCode := SQLCODE;
    v_ErrorText := SUBSTR(SQLERRM, 1, 200);  -- Note the use of SUBSTR
    here.
    INSERT INTO log_table (code, message, info) VALUES
      (v_ErrorCode, v_ErrorText, 'Oracle error occurred');
END;


v_ErrorText := SQLERRM;


p.280
ORA-0000: normal, successful completion


User-Defined Exception 


ORA-01403: f[^܂B


ORA-0000: normal, successful completion


p.280-281
DECLARE
  v_ErrorText   log_table.message%TYPE;  -- G[bZ[W̃eLXgi[pϐ
BEGIN
  /* SQLERRM(0) */
  v_ErrorText := SUBSTR(SQLERRM(0), 1, 200);
  INSERT INTO log_table (code, message, info)
    VALUES (0, v_ErrorText, 'SQLERRM(0)');

  /* SQLERRM(100) */
  v_ErrorText := SUBSTR(SQLERRM(100), 1, 200);
  INSERT INTO log_table (code, message, info)
    VALUES (100, v_ErrorText, 'SQLERRM(100)');

  /* SQLERRM(10) */
  v_ErrorText := SUBSTR(SQLERRM(10), 1, 200);
  INSERT INTO log_table (code, message, info)
    VALUES (10, v_ErrorText, 'SQLERRM(10)');

  /* SQLERRM Ȃ */
  v_ErrorText := SUBSTR(SQLERRM, 1, 200);
  INSERT INTO log_table (code, message, info)
    VALUES (NULL, v_ErrorText, 'SQLERRM with no argument');

  /* SQLERRM(-1) */
  v_ErrorText := SUBSTR(SQLERRM(-1), 1, 200);
  INSERT INTO log_table (code, message, info)
    VALUES (-1, v_ErrorText, 'SQLERRM(-1)');

  /* SQLERRM(-54) */
  v_ErrorText := SUBSTR(SQLERRM(-54), 1, 200);
  INSERT INTO log_table (code, message, info)
    VALUES (-54, v_ErrorText, 'SQLERRM(-54)');

END;


p.282
DECLARE
  e_MissingNull EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_MissingNull, -1400);
BEGIN
  INSERT INTO students (id) VALUES (NULL);
EXCEPTION
  WHEN e_MissingNull then
    INSERT INTO log_table (info) VALUES ('ORA-1400 occurred');
END;


p.283
CREATE OR REPLACE PROCEDURE Register (
  /* p_Departmentp_CourseƂp[^Ŏw肳ꂽNX
     p[^p_StudentIDŎw肳ꂽwo^DAwNX
     ۂɓo^ClassPackage.AddStudentR[OɁAYNX
     ݂ƁÃNX̊wɗ]T邩ǂA{vV[W
     `FbN */
  p_StudentID IN students.id%TYPE,
  p_Department IN classes.department%TYPE,
  p_Course IN classes.course%TYPE) AS

  v_CurrentStudents NUMBER;  -- YNX݂̌̊w
  v_MaxStudents NUMBER;      -- YNXɓo^łw̍ő吔

BEGIN
  /* ݂̓o^ς݂̊wƁAo^łw̍ő吔𒲂ׂ */
  SELECT current_students, max_students
    INTO v_CurrentStudents, v_MaxStudents
    FROM classes
    WHERE course = p_Course
    AND department = p_Department;

  /* Ywǉł]T邩A`FbN */
  IF v_CurrentStudents + 1 > v_MaxStudents THEN
    RAISE_APPLICATION_ERROR(-20000, 'Can''t add more students to ' ||
      p_Department || ' ' || p_Course);
  END IF;

  /* YwAYNXɒǉ */
  ClassPackage.AddStudent(p_StudentID, p_Department, p_Course);

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    /* {vV[WɓnꂽNX̏́A݂ĂȂDR[
       vOɂ̂Ƃ邽߂ɁAG[ʒm */
    RAISE_APPLICATION_ERROR(-20001, p_Department || ' ' || p_Course ||
      ' doesn''t exist!');
END Register;


p.284
ORA-20000: Can't add more students to HIS 102


ORA-1403: f[^܂B 


p.286
DECLARE
  -- ÕubN̊JnD
  ...
BEGIN
  ...
  DECLARE
    -- ̃ubN1̊JnD̃ubŃCÕubNɈ͂܂ĂD
  ...
  BEGIN
    ...
  END;
  ...
  BEGIN
    -- ̃ubN2̊JnD̃ubNCÕubNɈ͂܂ĂD
    -- ̃ubNɂ́C錾Ȃ_ɒӁD
    ...
  END;
  ...
  -- ÕubN̏ID
END;


p.287
BEGIN
  -- ÕubN̊JnD
  -- vV[WR[D̃vV[ẂCÓíjubNɂ
  -- ͂܂邱ƂɂȂD
  F(...);
END;


p.289
v_Number NUMBER(3) := 'ABC';


p.293
DECLARE
  ...
BEGIN
  ...
  DECLARE
    e_UserDefinedException EXCEPTION;
  BEGIN
    RAISE e_UserDefinedException;
  END;
EXCEPTION
  /* ́Ce_UserDefinedException̗L͈͊OłD̂߁C
   e_UserDefinedExceptiońCnhOTHERSłłȂD */
  WHEN OTHERS THEN
    /* G[̏ */
END;


CREATE OR REPLACE PACKAGE Globals
  /* ̃pbP[Wł́CO[oȐ錾LqDŐ錾
   IuWFNǵCCgp΁C̔Cӂ̃ubN
   vV[WQƂłD̃pbP[Wɂ́C
   pbP[W̖{̂Ȃ_ɒӂ邱ƁD */

  /* [U[`̗O */
  e_UserDefinedException EXCEPTION;
END Globals;


p.293-294
DECLARE
  ...
BEGIN
  ...
  BEGIN
    /* e_UserDefinedExceptionpbP[WŏC
   Ȃ΂ȂȂ_ɒӁD */
    RAISE Globals.e_UserDefinedException;
  END;
EXCEPTION
  /* e_UserDefinedExceptiońCłQƂł邽߁C
   Iɏ邱ƂłD */
  WHEN Globals.e_UserDefinedException THEN
    /* G[̏ */
END;


p.294
DECLARE
  v_ErrorNumber NUMBER;       -- G[ԍi[邽߂̕ϐD
  v_ErrorText VARCHAR2(200);  -- G[bZ[W̃eLXgi[邽߂̕ϐD
BEGIN
  /* PL/SQLł̒ʏ̏ */
  ...
EXCEPTION
  WHEN OTHERS THEN
    /* ɏIł悤COׂăMOD */
    v_ErrorNumber := SQLCODE;
    v_ErrorText := SUBSTR(SQLERRM, 1, 200);
    INSERT INTO log_table (code, message, info) VALUES
      (v_ErrorNumber, v_ErrorText, 'Oracle error occurred');
END;


p.294-295
BEGIN
  SELECT ...
  SELECT ...
  SELECT ...
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    -- ǂSELECTOʒmꂽH
END;


p.295
DECLARE
  v_SelectCounter NUMBER := 1;  -- SELECT̔ԍi[邽߂̕ϐD
BEGIN
  SELECT ...
  v_SelectCounter := 2;
  SELECT ...
  v_SelectCounter := 3;
  SELECT ...
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    INSERT INTO log_table (info) VALUES ('No data found in select 
                                         ' || v_SelectCounter);
END;


p.295-296
BEGIN
  BEGIN
    SELECT ...
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      INSERT INTO log_table (info) VALUES ('No data found 
                                            in select 1');
  END;
  BEGIN
    SELECT ...
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      INSERT INTO log_table (info) VALUES ('No data found 
                                            in select 2');
  END;
  BEGIN
    SELECT ...
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      INSERT INTO log_table (info) VALUES ('No data found 
                                            in select 3');
  END;
END;



Chapter7
p.301
SQL> VARIABLE v_Num NUMBER
SQL> variable v_Char char(3)
SQL> vaRIAbLe v_Varchar VarCHAR2(5)


p.305
SQL> EXECUTE Register(10006, 'CS', 102)


p.306
BEGIN Register(10006, 'CS', 102); END;


VARIABLE v_Count NUMBER

BEGIN
  Register(&student_id, 'CS', 102);
  SELECT COUNT(*)
    INTO :v_Count
    FROM registered_students
    WHERE department = 'CS'
    AND course = 102;
END;
/

PRINT v_Count


SQL> @register


p.307
x: vV[W쐬܂ARpCG[܂B


EXEC SQL BEGIN DECLARE SECTION;


p.309
EXEC SQL END DECLARE SECTION;


p.309-310
EXEC SQL BEGIN DECLARE SECTION;
  /* C̕ϐ̐錾 */
  VARCHAR v_Department[4]; /* ^^VARCHAŔAPro*Cł
                              gpłȂ߁A.arrlen
                              Ƃ2̃tB[h
                              R[h^ɕϊ */
  int v_Course;            /* v_Course͐ */
  int v_StudentID;         /* v_StudentID */
EXEC SQL END DECLARE SECTION;

  /* zXgϐDł̓zXgϐɒl𒼐ڑĂ邾
     ł邪AzXgϐ̒l̓t@Cǂݍ񂾂A[U[ɓ͂
     肷邱ƂłDVARCHAR^ϐ̏ꍇ́AY镶.arr
     tB[hɃRs[A̒̕(̏ꍇ́A3).lentB[h
     ɑ */
  strcpy(v_Department.arr, "ECN");
  v_Department.len = 3;
  v_Course = 101;
  v_StudentID = 10006;

  /* ߍPL/SQLubN̊JnDEXEC SQL EXECUTEEND-EXEC;
     ƂL[[hɒӁD̃vbŃAvRpĈ߂
     ̃L[[hɂċ؂Ă */
  EXEC SQL EXECUTE
    BEGIN
       Register(:v_Department, :v_Course, :v_StudentID);
    END;
  END-EXEC; 


p.310
PLS-00201: ʎq: <O>͐錾Ă܂B


EXEC SQL EXECUTE 


END-EXEC;


p.311
EXEC SQL BEGIN DECLARE SECTION;
  char v_Grade;    /* v_Grade1 */
  short i_Grade;  /* Wϐshort(2oCg̐)Ƃ
                     錾Ă_ɒ */
EXEC SQL END DECLARE SECTION;

EXEC SQL EXECUTE
  BEGIN
    SELECT grade
      INTO :v_Grade INDICATOR :i_Grade
      FROM registered_students
      WHERE student_id = 10006
      AND department = 'ECN'
      AND course = 101;
  END;
END-EXEC;

  if (i_Grade != 0)
    printf("No grade recorded for this student\n");
  else
    printf("The grade recorded is %c\n", v_Grade); 


EXEC SQL EXECUTE
  BEGIN
    SELECT grade
      INTO :v_Grade:i_Grade
      FROM registered_students
      WHERE student_id = 10006
      AND department = 'ECN'
      AND course = 101;
  END;
END-EXEC;


p.312-313
EXEC SQL INCLUDE SQLCA; /* ̕ł́A\SQLCACN[hĂ 
                           ̍\̂̒̈ÃtB[h́AG[
                           Ɏgp */
EXEC SQL EXECUTE
  BEGIN
    RecordFullClasses;
  END;
END-EXEC;
/* sqlca.sqlcodeɂ́AɏIƃ[AُI
   YG[R[hi[DG[ꍇA
   sqlca.sqlerrm.sqlerrmcɂ̓G[bZ[W̃eLXgA
   sqlca.sqlerrm.sqlerrmlɂ͂̃bZ[Wi[ */
if (sqlca.sqlcode != 0) {
  printf("Error during execution of RecordFullClasses.\n");
  printf("%.70s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
  }
else
  printf("Execution successful.\n"); 


p.313
SQLCHECK=SEMANTICS USERID=example/example 


p.314
char *plsql_block = 
  "BEGIN \
     Register(:v_StudentID, :v_Department, :v_Course); \
   END;";
int return_val;
Cda_Def cda;

return_val = oparse(&cda, plsql_block, -1, 1, 2);


p.315-318
/* Wwb_[t@COCĨwb_[CN[h */
#include <stdio.h>
#include <oratypes.h>
#include <ocidfn.h>
#include <ociapr.h>

/* ̕ŎgpLDA, HDA CDA錾 */
Lda_Def lda;
ub1 HDA[512];
Cda_Def cda;

/* ͂Ɏgpϐ錾 */
char v_Department[4] = "ECN";
int v_Course = 101;
int v_StudentID = 10006;

/* Register̃R[̃ubN܂܂Ă镶
   ׂĈP̕ɂ邽߂ɁALW^[
   obNXbVŒuĂ_ɒӁD
   vbN̍\vfƂĕKvł邽߁A
   ̃Z~RɓĂ */
char *plsqlBlock =
  "BEGIN \
       Register(:v_StudentID, :v_Department, :v_Course); \
   END;";

/* f[^x[Xɐڑ邽߂̃[U[ƃpX[h */
char *username = "example";
char *password = "example";  


/* G[񍐗p̊֐DoerhmsgpăG[Ŝ擾A
   ʂɏo͂ */
void print_error(Lda_Def *lda, Cda_Def *cda) {
  int v_ReturnChars;
  char v_Buffer[1000];

  v_ReturnChars = oerhms(lda, cda->rc, (text *) v_Buffer,
                         (sword) sizeof(v_Buffer));
  printf("Oracle error occurred!\n");
  printf("%s\n", v_Buffer);
}


main() {
  /* f[^x[Xւ̐ڑ */
  if (orlon(&lda, HDA, (text *) username, -1,
            (text *) password, -1, 0)) {
    print_error(&lda, &lda);
    exit(-1);
  }
  printf("Connected to Oracle\n");

  /* ɎgpJ[\I[v */
  if (oopen(&cda, &lda, (text *) 0, -1, -1,
            (text *) 0, -1)) {
    print_error(&lda, &cda);
    exit(-1);
  }

  /* PL/SQLubN̉ */
  if (oparse(&cda, (text *) plsqlBlock,
             (sb4) -1, 1, (ub4) 2)) {
    print_error(&lda, &cda);
    exit(-1);
  }

  /* ^Cv5STRINGgpāAw(department)oCh */
  if (obndrv(&cda, (text *) ":v_Department", -1,
             (ub1 *) v_Department, sizeof(v_Department),
             5, -1, (sb2 *) 0, 0, -1, -1)) {
    print_error(&lda, &cda);
    exit(-1);
  }

  /* ^Cv3INTEGERgpāAw(course)oCh */
  if (obndrv(&cda, (text *) ":v_Course", -1,
            (ub1 *) &v_Course, sizeof(v_Course),
            3, -1, (sb2 *) 0, 0, -1, -1)) {
    print_error(&lda, &cda);
    exit(-1);
  }

  /* ^Cv3INTEGERgpāAwIDoCh */
  if (obndrv(&cda, (text *) ":v_StudentID", -1,
             (ub1 *) &v_StudentID, sizeof(v_StudentID),
             3, -1, (sb2 *) 0, 0, -1, -1)) {
    print_error(&lda, &cda);
    exit(-1);
  }

  /* ̎s */
  if (oexec(&cda)) {
    print_error(&lda, &cda);
    exit(-1);
  }

  /* sʂR~bg */
  if (ocom(&lda)) {
    print_error(&lda, &cda);
    exit(-1);
  }

  /* J[\N[Y */
  if (oclose(&cda)) {
    print_error(&lda, &cda);
    exit(-1);
  }

  /* f[^x[X̃OIt */
  if (ologof(&lda)) {
    print_error(&lda, &cda);
    exit(-1);
  }
} 


p.325
WRAP INAME=register.sql
WRAP INAME=register
WRAP INAME=register.sql ONAME=register.plb 


p.326
CREATE [OR REPLACE] PROCEDURE
CREATE [OR REPLACE] PACKAGE
CREATE [OR REPLACE] PACKAGE BODY
CREATE [OR REPLACE] FUNCTION 


CREATE OR REPLACE PROCEDURE Register(...) AS
  ...
BEGIN
  ...
END Register;


CREATE OR REPLACE PROCEDURE Register WRAPPED
  012ba779f...


p.326-327
CREATE OR REPLACE PROCEDURE CountStudents
  (p_Major IN students.major%TYPE,
   p_TotalNumber OUT NUMBER) AS
BEGIN
  SELECT COUNT(*)
    INTO p_TotalNumber
    FROM student
    WHERE major = p_Major;
END CountStudents;
/


p.327
PLS-00201:    ʎq: student͐錾Ă܂B



Chapter8
p.332-333
CREATE OR REPLACE FUNCTION AverageGrade (
/* w肳ꂽNX̕ϐт𒲂ׂDт́A\registered_students
   ̒AE1Ŋi[ĂD{֐Aϐт1
   ԂDo^ĂwNXɂȂꍇ́AG[ʒm
    */
  p_Department IN VARCHAR2,
  p_Course IN NUMBER) RETURN VARCHAR2 AS

  v_AverageGrade VARCHAR2(1);
  v_NumericGrade NUMBER;
  v_NumberStudents NUMBER;

  CURSOR c_Grades IS
    SELECT grade
      FROM registered_students
      WHERE department = p_Department
      AND course = p_Course;
BEGIN
  /* YNX̊w܂ׂKvDwȂꍇɂ́A
     G[ʒmKv */
  SELECT COUNT(*)
    INTO v_NumberStudents
    FROM registered_students
    WHERE department = p_Department
      AND course = p_Course;

  IF v_NumberStudents = 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'No students registered for ' ||
      p_Department || ' ' || p_Course);
  END IF;

  /* т͕Ŋi[Ă邽߁AAVG֐𒼐ڎgp邱Ƃ͂łȂD
     ̂߁ADECODE֐gpĕ\̐т𐔒lɕϊĂA
     ςvZ */
  SELECT AVG(DECODE(grade, 'A', 5,
                           'B', 4,
                           'C', 3,
                           'D', 2,
                           'E', 1))
    INTO v_NumericGrade
    FROM registered_students
    WHERE department = p_Department
    AND course = p_Course;

  /* v_NumericGradeɂ́Aϐт1`5̐lƂĊi[ĂD
     x́A𕶎ɕϊKvDDECODE֐́Ał
     gpłDϊʂ́Ał͂ȂSELECTɂv_AverageGrade
     i[Ă_ɒӁDȂȂADECODE֐́ASQL̒łgp
     łȂ߂ł */
  SELECT DECODE(ROUND(v_NumericGrade), 5, 'A',
                                       4, 'B',
                                       3, 'C',
                                       2, 'D',
                                       1, 'E')
    INTO v_AverageGrade
    FROM dual;

  RETURN v_AverageGrade;
END AverageGrade;


p.333-334
SQL> select * from registered_students;

STUDENT_ID DEP    COURSE G
---------- --- --------- -
     10000 CS        102 A
     10002 CS        102 B
     10003 CS        102 C
     10000 HIS       101 A
     10001 HIS       101 B
     10002 HIS       101 B
     10003 HIS       101 A
     10004 HIS       101 C
     10005 HIS       101 C
     10006 HIS       101 E
     10007 HIS       101 B
     10008 HIS       101 A
     10009 HIS       101 D
     10010 HIS       101 A
     10008 NUT       307 A
     10010 NUT       307 A
     10009 MUS       410 B
     10006 MUS       410 E

18 rows selected.


p.334-335
SQL> VARIABLE v_AveGrade VARCHAR2(1)
SQL> exec :v_AveGrade := AverageGrade('HIS', 101)

PL/SQL procedure successfully completed.

SQL> print v_AveGrade

V_AVEGRADE
-------------------------------
B

SQL> exec :v_AveGrade := AverageGrade('NUT', 307)

PL/SQL procedure successfully completed.

SQL> print v_AveGrade

V_AVEGRADE
-------------------------------
A

SQL> exec :v_AveGrade := AverageGrade('MUS', 410)

PL/SQL procedure successfully completed.

SQL> print v_AveGrade

V_AVEGRADE
-------------------------------
C

SQL> exec :v_AveGrade := AverageGrade('CS', 102)
begin :v_AveGrade := AverageGrade('CS', 102); end;

 *
s:1ŃG[܂B
ORA-20001: No students registered for CS 102
ORA-06512: "EXAMPLE.AVERAGEGRADE", s: 25


p.335-336
CREATE OR REPLACE PACKAGE Debug AS
  /* Po[WDebugpbP[WD̃pbP[ẂA
     \debug_tableɑ}s邱Ƃɂē삷D
     o͌ʂɂ́ASQL*PlusŎ̕ɂAdebug_tableɑ΂
     Is邱ƁD
 SELECT debug_str FROM debug_table ORDER BY linecount; */

  /* Debug̃CvV[WAp_DescriptiońA
     p_ValueƘAAdebug_tableɑ} */
  PROCEDURE Debug(p_Description IN VARCHAR2, p_Value IN VARCHAR2);
 
  /* Debug̓ZbgDReset́A{pbP[W߂
     CX^VF[gꂽƂɂɃR[D܂AV
     ZbV̂߂debug_table̓e폜ꍇA
     ResetR[邱ƁD */
  PROCEDURE Reset;
 END Debug;
/

CREATE OR REPLACE PACKAGE BODY Debug AS
  /* v_LineCount́Adebug_table̒̍sԂɕׂ邽߂Ɏgp */
  v_LineCount NUMBER;
 
  PROCEDURE Debug(p_Description IN VARCHAR2, p_Value IN VARCHAR2) IS
  BEGIN
    INSERT INTO debug_table (linecount, debug_str)
      VALUES (v_LineCount, p_Description || ': ' || p_Value);
    COMMIT;
    v_LineCount := v_LineCount + 1;
  END Debug;
 
  PROCEDURE Reset IS
  BEGIN
    v_LineCount := 1;
    DELETE FROM debug_table;
  END Reset;
 
BEGIN /* pbP[W̏R[h */
  Reset;
END Debug;


p.336-337
CREATE OR REPLACE FUNCTION AverageGrade (
  p_Department IN VARCHAR2,
  p_Course IN NUMBER) RETURN VARCHAR2 AS

  v_AverageGrade VARCHAR2(1);
  v_NumericGrade NUMBER;
  v_NumberStudents NUMBER;

  CURSOR c_Grades IS
    SELECT grade
      FROM registered_students
      WHERE department = p_Department
      AND course = p_Course;
BEGIN
  Debug.Reset;
  Debug.Debug('p_Department', p_Department);
  Debug.Debug('p_Course', p_Course);

  /* YNX̊w܂ׂKvDwȂꍇɂ́A
     G[ʒmKv */
  SELECT COUNT(*)
    INTO v_NumberStudents
    FROM registered_students
    WHERE department = p_Department
    AND course = p_Course;

  Debug.Debug('After select, v_NumberStudents', v_NumberStudents);
  IF v_NumberStudents = 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'No students registered for ' ||
      p_Department || ' ' || p_Course);
  END IF;

  SELECT AVG(DECODE(grade, 'A', 5,
                           'B', 4,
                           'C', 3,
                           'D', 2,
                           'E', 1))
    INTO v_NumericGrade
    FROM registered_students
    WHERE department = p_Department
      AND course = p_Course;

  SELECT DECODE(ROUND(v_NumericGrade), 5, 'A',
                                       4, 'B',
                                       3, 'C',
                                       2, 'D',
                                       1, 'E')
    INTO v_AverageGrade
    FROM dual;

  RETURN v_AverageGrade;
END AverageGrade;


p.337-338
SQL> EXEC :v_AveGrade := AverageGrade('CS', 102)
begin :v_AveGrade := AverageGrade('CS', 102); end;

 *
s:1ŃG[܂B
ORA-20001: No students registered for CS 102
ORA-06512: "EXAMPLE.AVERAGEGRADE", s: 25
ORA-06512: s: 1


SQL> SELECT debug_str FROM debug_table ORDER BY linecount;

DEBUG_STR
----------------------------------------------------------
p_Department: CS
p_Course: 102
After select, v_NumberStudents: 0


p.338
SELECT COUNT(*)
    INTO v_NumberStudents
    FROM registered_students
    WHERE department = p_Department
    AND course = p_Course;


CREATE OR REPLACE FUNCTION AverageGrade
  ...
BEGIN
  Debug.Reset;
  Debug.Debug('p_Department', '''' || p_Department || '''');
  Debug.Debug('p_Course', '''' || p_Course || '''');

  /* YNX̊w܂ׂKvDwȂꍇ́C
     G[ʒmKvD */
  SELECT COUNT(*)
    INTO v_NumberStudents
    FROM registered_students
    WHERE department = p_Department
      AND course = p_Course;

  Debug.Debug('After select, v_NumberStudents', v_NumberStudents);
  ...


p.339
SQL> exec :v_AveGrade := AverageGrade('CS', 102)
begin :v_AveGrade := AverageGrade('CS', 102); end;

 *
s:1ŃG[܂B
ORA-20001: No students registered for CS 102
ORA-06512: "EXAMPLE.AVERAGEGRADE", s: 25
ORA-06512: s: 1


SQL> SELECT debug_str FROM debug_table ORDER BY linecount;

DEBUG_STR
----------------------------------------------------------
p_Department: 'CS'
p_Course: '102'
After select, v_NumberStudents: 0


p.340
CREATE OR REPLACE FUNCTION AverageGrade (
  p_Department IN CHAR,
  p_Course IN NUMBER) RETURN VARCHAR2 AS
  ...
BEGIN
  ...
END AverageGrade;


SQL> exec :v_AveGrade := AverageGrade('CS', 102)

PL/SQL procedure successfully completed.

SQL> print v_AveGrade

V_AVEGRADE
-------------------------------
B 


p.340-341
CREATE OR REPLACE FUNCTION AverageGrade (
  p_Department IN registered_students.department%TYPE,
  p_Course IN registered_students.course%TYPE) RETURN CHAR AS
 
  v_AverageGrade CHAR(1);
  v_NumericGrade NUMBER;
  v_NumberStudents NUMBER;
...
BEGIN
  ...
END AverageGrade;


p.344-345
DECLARE
  /* PUT_LINE GET_LINE̎gp */
  v_Data      DBMS_OUTPUT.CHARARR;
  v_NumLines  NUMBER;
BEGIN
  -- obt@܂Lɂ
  DBMS_OUTPUT.ENABLE(1000000);

  -- GET_LINESŉo悤ɁApbt@ɉf[^
  -- i[Ă
  DBMS_OUTPUT.PUT_LINE('Line One');
  DBMS_OUTPUT.PUT_LINE('Line Two');
  DBMS_OUTPUT.PUT_LINE('Line Three');

  -- os̍őlݒ肷
  v_NumLines := 3;

  /* obt@̓eoDv_DatáADBMS_OUTPUT.GET_LINES
     ̐錾ɍv悤ɁDDBMS_OUTPUT.CHARARRƂ^Ő錾
     _ɒӂ邱 */
  DBMS_OUTPUT.GET_LINES(v_Data, v_NumLines);

  /* Ԃꂽobt@̓eA[vŏAtemp_table
     } */
  FOR v_Counter IN 1..v_NumLines LOOP
    INSERT INTO temp_table (char_col)
      VALUES (v_Data(v_Counter));
  END LOOP;
END;


p.347
ORA-20000: ORU-10027: buffer overflow,
           limit of <buf_limit> bytes.


ORA-20000: ORU-10028: line length overflow,
           limit of 255 bytes per line.


p.347-348
CREATE OR REPLACE FUNCTION CountCredits (
  /* p_StudentIDŎw肳ꂽw݂̌̎擾ςݒPʐԂ */
  p_StudentID IN students.ID%TYPE)
  RETURN NUMBER AS

  v_TotalCredits NUMBER;  -- Pʐ
  v_CourseCredits NUMBER; -- P̉Ȗڂ̒Pʐ
  CURSOR c_RegisteredCourses IS
    SELECT department, course
      FROM registered_students
      WHERE student_id = p_StudentID;
BEGIN
  FOR v_CourseRec IN c_RegisteredCourses LOOP
    -- YȖڂ̒Pʐ𒲂ׂ
    SELECT num_credits
      INTO v_CourseCredits
      FROM classes
      WHERE department = v_CourseRec.department
      AND course = v_CourseRec.course;

    -- ܂ł̓vɑ
    v_TotalCredits := v_TotalCredits + v_CourseCredits;
  END LOOP;

  RETURN v_TotalCredits;
END CountCredits;


p.348
SQL> SELECT ID, CountCredits(ID) "Total Credits"
  2    FROM students;

       ID Total Credits
--------- -------------
    10000
    10001
    10002
    10003
    10004
    10005
    10006
    10007
    10008
    10009
    10010

11 rows selected.


p.349
CREATE OR REPLACE PACKAGE Debug AS
  PROCEDURE Debug(p_Description IN VARCHAR2,
                  p_Value IN VARCHAR2);

  PROCEDURE Reset;
END Debug;
/

CREATE OR REPLACE PACKAGE BODY Debug AS
  PROCEDURE Debug(p_Description IN VARCHAR2,
                  p_Value IN VARCHAR2) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(p_Description || ': ' || p_Value);
  END Debug;

  PROCEDURE Reset IS
  BEGIN
       /* obt@A񖳌ɂĂAőTCYw
          ėLɂDDISABLEsƓobt@폜邽߁A
          ResetR[邽тɐVobt@擾ł */
    DBMS_OUTPUT.DISABLE;
    DBMS_OUTPUT.ENABLE(1000000);
  END Reset;
BEGIN /* pbP[W̏R[h */
  Reset;
END Debug;


p.349-350
CREATE OR REPLACE FUNCTION CountCredits (
  /* p_StudentIDŎw肳ꂽw݂̌̎擾ςݒPʐԂ */
  p_StudentID IN students.ID%TYPE)
  RETURN NUMBER AS

  v_TotalCredits NUMBER;  -- Pʐ
  v_CourseCredits NUMBER; -- P̉Ȗڂ̒Pʐ
  CURSOR c_RegisteredCourses IS
    SELECT department, course
      FROM registered_students
      WHERE student_id = p_StudentID;
BEGIN
  Debug.Reset;
  FOR v_CourseRec IN c_RegisteredCourses LOOP
    -- YȖڂ̒Pʐ𒲂ׂ
    SELECT num_credits
      INTO v_CourseCredits
      FROM classes
      WHERE department = v_CourseRec.department
      AND course = v_CourseRec.course;

    Debug.Debug('Inside loop, v_CourseCredits', v_CourseCredits);
    -- ܂ł̑vɑ
    v_TotalCredits := v_TotalCredits + v_CourseCredits;
  END LOOP;

  Debug.Debug('After loop, returning', v_TotalCredits);
  RETURN v_TotalCredits;
END CountCredits;


p.350
SQL> VARIABLE v_Total NUMBER
SQL> SET SERVEROUTPUT ON
SQL> exec :v_Total := CountCredits(10006);
Inside loop, v_CourseCredits: 4
Inside loop, v_CourseCredits: 3
After loop, returning:

PL/SQL procedure successfully completed.

SQL> print v_Total

  V_TOTAL
---------


SQL>


p.351-352
CREATE OR REPLACE FUNCTION CountCredits (
  /* p_StudentIDŎw肳ꂽw݂̌̎擾ςݒPʐԂ */
  p_StudentID IN students.ID%TYPE)
  RETURN NUMBER AS

  v_TotalCredits NUMBER;  -- Pʐ
  v_CourseCredits NUMBER; -- P̉Ȗڂ̒Pʐ
  CURSOR c_RegisteredCourses IS
    SELECT department, course
      FROM registered_students
      WHERE student_id = p_StudentID;
BEGIN
  Debug.Reset;
  Debug.Debug('Before loop, v_TotalCredits', v_TotalCredits);
  FOR v_CourseRec IN c_RegisteredCourses LOOP
    -- YȖڂ̒Pʐ𒲂ׂ
    SELECT num_credits
      INTO v_CourseCredits
      FROM classes
      WHERE department = v_CourseRec.department
      AND course = v_CourseRec.course;

    Debug.Debug('Inside loop, v_CourseCredits', v_CourseCredits);
    -- ܂ł̑vɑ
    v_TotalCredits := v_TotalCredits + v_CourseCredits;
    Debug.Debug('Inside loop, v_TotalCredits', v_TotalCredits);
  END LOOP;

  Debug.Debug('After loop, returning', v_TotalCredits);
  RETURN v_TotalCredits;
END CountCredits;


p.352
SQL> exec :v_Total := CountCredits(10006);
Before loop, v_TotalCredits:
Inside loop, v_CourseCredits: 4
Inside loop, v_TotalCredits:
Inside loop, v_CourseCredits: 3
Inside loop, v_TotalCredits:
After loop, returning:

PL/SPL procedure successfully completed.


p.352-353
CREATE OR REPLACE FUNCTION CountCredits (
  /* p_StudentIDŎw肳ꂽw݂̌̎擾ςݒPʐԂ */
  p_StudentID IN students.ID%TYPE)
  RETURN NUMBER AS

  v_TotalCredits NUMBER := 0;  -- Pʐ
  v_CourseCredits NUMBER;      -- P̉Ȗڂ̒Pʐ
  CURSOR c_RegisteredCourses IS
    SELECT department, course
      FROM registered_students
      WHERE student_id = p_StudentID;
BEGIN
  FOR v_CourseRec IN c_RegisteredCourses LOOP
    -- YȖڂ̒Pʐ𒲂ׂ
    SELECT num_credits
      INTO v_CourseCredits
      FROM classes
      WHERE department = v_CourseRec.department
      AND course = v_CourseRec.course;

    -- ܂ł̑vɑ
    v_TotalCredits := v_TotalCredits + v_CourseCredits;
  END LOOP;

  RETURN v_TotalCredits;
END CountCredits;


p.353
SQL> exec :v_Total := CountCredits(10006);

PL/SQL procedure successfully completed.

SQL> print v_Total

  V_TOTAL
---------
        7

SQL> SELECT ID, CountCredits(ID) "Total Credits"
  2    FROM students;

       ID Total Credits
--------- -------------
    10000             8
    10001             4
    10002             8
    10003             8
    10004             4
    10005             4
    10006             7
    10007             4
    10008             8
    10009             7
    10010             8


p.355
CREATE OR REPLACE PROCEDURE CreditLoop AS
  /* temp_tableɁAwIDԍƌ݂̒Pʐ} */
  v_StudentID students.ID%TYPE;
  v_Credits   students.current_credits%TYPE;
  CURSOR c_Students IS
    SELECT ID
      FROM students;
BEGIN
  OPEN c_Students;
  LOOP
    FETCH c_Students INTO v_StudentID;
    v_Credits := CountCredits(v_StudentID);
    INSERT INTO temp_table (num_col, char_col)
      VALUES (v_StudentID, 'Credits = ' || TO_CHAR(v_Credits));
    EXIT WHEN c_Students%NOTFOUND;
  END LOOP;
  CLOSE c_Students;
END CreditLoop;


SQL> exec CreditLoop;
PL/SQL procedure successfully completed.
SQL> SELECT * FROM temp_table
  2    ORDER BY num_col;

  NUM_COL CHAR_COL
--------- --------------------
    10000 Credits = 8
    10001 Credits = 4
    10002 Credits = 8
    10003 Credits = 8
    10004 Credits = 4
    10005 Credits = 4
    10006 Credits = 7
    10007 Credits = 4
    10008 Credits = 8
    10009 Credits = 7
    10010 Credits = 8
    10010 Credits = 8

12 rows selected.


p.356
CreditLoop;


p.360
SQL> exec CreditLoop
PL/SQL procedure successfully completed.

SQL> SELECT * FROM temp_table
  2    ORDER BY num_col;

  NUM_COL CHAR_COL
--------- -------------------------------
    10000 Credits = 8
    10001 Credits = 4
    10002 Credits = 8
    10003 Credits = 8
    10004 Credits = 4
    10005 Credits = 4
    10006 Credits = 7
    10007 Credits = 4
    10008 Credits = 8
    10009 Credits = 7
    10010 Credits = 8

11 rows selected.


p.362-363
CREATE OR REPLACE PROCEDURE PrintTranscript(
  /* w肳ꂽw̐яؖo͂Dяؖ́A
     wݓo^ĂÃNXƂ̊eNX
     т\D܂Aя̖ؖɂ́A
     ̊wGPAo͂D */
  p_StudentID IN students.ID%TYPE) AS

  v_StudentGPA  NUMBER;  -- YwGPAt.
  CURSOR CurrentClasses IS
    SELECT *
      FROM registered_students
      WHERE student_id = p_StudentID;
BEGIN
  -- UȂǁAYwɊւwb_[o͂

  FOR v_ClassesRecord IN CurrentClasses LOOP
    -- eNXɊւo͂
    NULL;
  END LOOP;

  -- GPAvZ
  CalculateGPA(p_StudentID, v_StudentGPA);

  -- GPAo͂
END PrintTranscript;


p.363
CREATE OR REPLACE PROCEDURE CalculateGPA(
  /* p_StudentIDŎw肳ꂽwGPAp_GPAɕԂ */
  p_StudentID IN students.ID%TYPE,
  p_GPA OUT NUMBER) AS
BEGIN
  NULL;
END CalculateGPA;


Chapter9
p.366-367
CREATE OR REPLACE TRIGGER LogRSInserts
  BEFORE INSERT ON registered_students
  FOR EACH ROW
DECLARE
  v_Status     INTEGER;
BEGIN

  /* ̎ނ܂obt@ɃpbLO */
  DBMS_PIPE.PACK_MESSAGE('I');

  /* ݂̃[U[ƃ^CX^vpbLO */
  DBMS_PIPE.PACK_MESSAGE(user);
  DBMS_PIPE.PACK_MESSAGE(sysdate);

  /* VlpbLO */
  DBMS_PIPE.PACK_MESSAGE(:new.student_ID);
  DBMS_PIPE.PACK_MESSAGE(:new.department);
  DBMS_PIPE.PACK_MESSAGE(:new.course);
  DBMS_PIPE.PACK_MESSAGE(:new.grade);

  /* nCv'RSInserts'āAbZ[W𑗐M */
  v_Status := DBMS_PIPE.SEND_MESSAGE('RSInserts');

  /* MɎsȂꍇ́AύX
     sȂ悤ɁAG[ʒm */
  IF v_Status != 0 THEN
    RAISE_APPLICATION_ERROR(-20010, 'LogRSInserts trigger ' ||
      'couldn''t send the message, status = ' || v_Status);
  END IF;
  
END LogRSInserts;


p.367-370
/* ̃vÓARSInsertsƂpCv烁bZ[WMA
   t@CɃMO */

/* CSQL̃wb_[t@C */
#include <stdio.h>
EXEC SQL INCLUDE sqlca;

EXEC SQL BEGIN DECLARE SECTION;
  /* f[^x[Xɐڑ邽߂̃[U[ƃpX[h */
  char *v_Connect = "example/example";

  /* DBMS_PIPER[ۂɎgp󋵕ϐ */
  int v_Status;
  VARCHAR v_Code[5];

  /* pCvoRőMϐ - ꂪMȎΏۂƂȂ */
  VARCHAR v_Userid[9];
  VARCHAR v_Changedate[10];
  int v_StudentID;
  VARCHAR v_Department[4];
  int v_Course;
  VARCHAR v_Grade[2];
  short v_Grade_ind;
EXEC SQL END DECLARE SECTION;

/* Ot@Cɑ΂t@C|C^ */
FILE *outfile;

void sqlerror();

int main() {

  /* G[̏ */
  EXEC SQL WHENEVER SQLERROR DO sqlerror();

  /* f[^x[Xւ̐ڑ */
  EXEC SQL CONNECT :v_Connect;

  /* Ot@C̃I[v */
  outfile = fopen("rs.log", "w");

  /* C[vD'STOP'bZ[WMꍇA
     G[ꍇłȂƁÃ[v甲Ȃ */
  for (;;) {
    /* pCv'RSInserts'ăbZ[WM܂ŃX[v
       ^CAEgĺAw肵ĂȂ߁AftHglgp */
    EXEC SQL EXECUTE
      BEGIN
        :v_Status := DBMS_PIPE.RECEIVE_MESSAGE('RSInserts');
      END;
    END-EXEC;

    if (v_Status == 0) {
      /* bZ[W̐MD@肷邽߂ɁA
         擪̃f[^vf擾Kv */
      v_Code.len = 5;
      EXEC SQL EXECUTE
        BEGIN
          DBMS_PIPE.UNPACK_MESSAGE(:v_Code);
        END;
      END-EXEC;
      v_Code.arr[v_Code.len] = '\0';

      if (!strcmp(v_Code.arr, "STOP")) {
        /* 'Stop'bZ[W̎MD[v甲 */
        break;
      }

      /* bZ[W̎c̏([U[ID, tAѐVl)
         o */
      v_Userid.len = 9;
      v_Changedate.len = 10;    
      v_Department.len = 4;
      v_Grade.len = 2;
      EXEC SQL EXECUTE
        DECLARE
          v_ChangeDate DATE;
        BEGIN
          DBMS_PIPE.UNPACK_MESSAGE(:v_Userid);
          DBMS_PIPE.UNPACK_MESSAGE(v_ChangeDate);
          :v_Changedate := TO_CHAR(v_ChangeDate, 'DD-MON-YY');
          DBMS_PIPE.UNPACK_MESSAGE(:v_StudentID);
          DBMS_PIPE.UNPACK_MESSAGE(:v_Department);
          DBMS_PIPE.UNPACK_MESSAGE(:v_Course);
          DBMS_PIPE.UNPACK_MESSAGE(:v_Grade:v_Grade_ind);
        END;
      END-EXEC;

      /* ̖NULLt */
      v_Userid.arr[v_Userid.len] = '\0';
      v_Changedate.arr[v_Changedate.len] = '\0';
      v_Department.arr[v_Department.len] = '\0';

      if (v_Grade_ind == -1)
        v_Grade.arr[0] = '\0';
      else
        v_Grade.arr[v_Grade.len] = '\0';

      /* f[^Ot@Cɏo */
      fprintf(outfile, "User: %s Timestamp: %s",
        v_Userid.arr, v_Changedate.arr);
      fprintf(outfile, " ID: %d Course: %s %d Grade: %s\n",
        v_StudentID, v_Department.arr, v_Course, v_Grade.arr);

    }
    else if (v_Status == 1) {
      /* RECEIVE_MESSAGER[̃^CAEgD[vōēx҂ */
      continue;
    }
    else {
      /* RECEIVE_MESSAGEG[ňُID
         YG[o͂AI */
      printf("RECEIVE_MESSAGE Error!  Status = %d\n", v_Status);
      EXEC SQL ROLLBACK WORK RELEASE;
      exit(1);
    }

  }  /* C[v̏I */
    
  /* t@C̃N[Y */
  fclose(outfile);

  /* f[^x[XƂ̐ڑ */
  EXEC SQL COMMIT WORK RELEASE;
}


/* G[֐DG[ʂɏo͂AI */
void sqlerror() {

  printf("SQL Error!\n");
  printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);

  EXEC SQL WHENEVER SQLERROR CONTINUE;

  EXEC SQL ROLLBACK RELEASE;
}


p.377
ORA-23322: Insufficient privilege to access pipe 


p.380-383
/* {vÓADBMS_PIPEłDebugpbP[W̃obNGh
   sD{vÓAfobNΏۂPL/SQLZbV͕ʂ
   EChEŎs邱 */

/* CSQL̃wb_[t@C */
#include <stdio.h>
EXEC SQL INCLUDE sqlca;

EXEC SQL BEGIN DECLARE SECTION;
  /* f[^x[Xɐڑ邽߂̃[U[ƃpX[h */
  char *v_Connect = "example/example";

  /* DBMS_PIPER[ۂɎgpԕϐ */
  int v_Status;
  VARCHAR v_Code[6];

  /* nCvoRőMϐ */
  VARCHAR v_ReturnPipeName[31];
  VARCHAR v_Description[100];
  VARCHAR v_Value[100];
EXEC SQL END DECLARE SECTION;

/* G[֐. */
void sqlerror();

int main() {

  /* G[̏ */
  EXEC SQL WHENEVER SQLERROR DO sqlerror();

  /* f[^x[Xւ̐ڑ */
  EXEC SQL CONNECT :v_Connect;

  printf("Debug ready for input.\n");

  /* C[vD'STOP'bZ[WMꍇA
     G[ꍇłȂƁÃ[v甲Ȃ */
  for (;;) {
    /* nCv'Debug'ăbZ[WM܂ŃX[v
       ^CAEgĺAw肵ĂȂ߁AfBtHglgp */
    EXEC SQL EXECUTE
      BEGIN
        :v_Status := DBMS_PIPE.RECEIVE_MESSAGE('DebugPipe');
      END;
    END-EXEC;

    if (v_Status == 0) {
      /* bZ[W̐MD@肷邽߂ɁA
         擪̃f[^vf擾Kv */
      v_Code.len = 6;
      EXEC SQL EXECUTE
        BEGIN
          DBMS_PIPE.UNPACK_MESSAGE(:v_Code);
        END;
      END-EXEC;
      v_Code.arr[v_Code.len] = '\0';

      if (!strcmp(v_Code.arr, "STOP")) {
        /* 'STOP'bZ[W̎MD[v甲 */
        break;
      } /* STOP̏I */

      else if (!strcmp(v_Code.arr, "TEST")) {
        /* TESTbZ[W̎MDnCv
           nhVFCNԑ */
        EXEC SQL EXECUTE
          BEGIN
            DBMS_PIPE.PACK_MESSAGE('Handshake');
            :v_Status := DBMS_PIPE.SEND_MESSAGE('DebugPipe');
          END;
        END-EXEC;

        if (v_Status != 0) {
          /* G[bZ[Wo͂ */
          printf("Error %d while responding to TEST message\n",
                 v_Status);
        }
      } /* TEST̏I */

      else if (!strcmp(v_Code.arr, "DEBUG")) {
        /* DEBUGbZ[W̎MD^[p̃pCvAA
           яo͒l̃pbLO */
        v_ReturnPipeName.len = 30;
        v_Description.len = 100;
        v_Value.len = 100;
        EXEC SQL EXECUTE
          BEGIN
            DBMS_PIPE.UNPACK_MESSAGE(:v_ReturnPipeName);
            DBMS_PIPE.UNPACK_MESSAGE(:v_Description);
            DBMS_PIPE.UNPACK_MESSAGE(:v_Value);
          END;
        END-EXEC;

        /* o͕ϐ̖NULLt */
        v_Description.arr[v_Description.len] = '\0';
        v_Value.arr[v_Value.len] = '\0';

        /* fobNʃGR[ɕ\ */
        printf("%s: %s\n", v_Description.arr, v_Value.arr);

        /* nhVFCNbZ[Wԑ */
        EXEC SQL EXECUTE
          BEGIN
            DBMS_PIPE.PACK_MESSAGE('Processed');
            :v_Status := DBMS_PIPE.SEND_MESSAGE(:v_ReturnPipeName);
          END;
        END-EXEC;

        if (v_Status != 0) {
          /* G[bZ[Wo͂ */
          printf("Error %d while sending handshake message\n",
                 v_Status);
        }
      } /* DEBUG̏I */
    } /* End of successful retreive of a message */

    else if (v_Status == 1) {
      /* RECEIVE_MESSAGER[̃^CAEgD[vōēx҂ */
      continue;
    }

    else {
      /* RECEIVE_MESSAGEG[ňُID
         YG[o͂āAI */
      printf("Main loop RECEIVE_MESSAGE Error. Status = %d\n",
             v_Status);
      EXEC SQL ROLLBACK WORK RELEASE;
      exit(1);
    }

  }  /* C[v̏I */
    
  /* f[^x[XƂ̐ڑ */
  EXEC SQL COMMIT WORK RELEASE;
}


/* G[֐DG[ʂɏo͂AI */
void sqlerror() {

  printf("SQL Error!\n");
  printf("%.*s\n", sqlca.sqlerrm.sqlerrml,
                   sqlca.sqlerrm.sqlerrmc);

  EXEC SQL WHENEVER SQLERROR CONTINUE;

  EXEC SQL ROLLBACK RELEASE;
}


p.383-386
CREATE OR REPLACE PACKAGE Debug AS
  -- nhVFCNbZ[W̍ő҂ԁibj
  v_TimeOut NUMBER := 10;

  -- Debug̃CvV[W
  PROCEDURE Debug(p_Description IN VARCHAR2, p_Value IN
VARCHAR2);

  -- Debug̊̃ZbgAbv
  PROCEDURE Reset;

  -- f[I
  PROCEDURE Exit;
END Debug;
/

CREATE OR REPLACE PACKAGE BODY Debug as

  v_CurrentPipeName VARCHAR2(30);

  PROCEDURE Debug(p_Description IN VARCHAR2, p_Value IN
VARCHAR2) IS
    v_ReturnCode NUMBER;
    v_Handshake  VARCHAR2(10);
  BEGIN
    /* pCv̖Ȍꍇɂ́ApCv̖O肷 */
    IF v_CurrentPipeName IS NULL THEN
      v_CurrentPipeName := DBMS_PIPE.UNIQUE_SESSION_NAME;
    END IF;

    /* ̏Ƃ 'DEBUG' bZ[W𑗐M
         - nhVFCNp̃pCv
         - 
         - l
    */
    DBMS_PIPE.PACK_MESSAGE('DEBUG');
    DBMS_PIPE.PACK_MESSAGE(v_CurrentPipeName);
    DBMS_PIPE.PACK_MESSAGE(p_Description);
    DBMS_PIPE.PACK_MESSAGE(p_Value);
    v_ReturnCode := DBMS_PIPE.SEND_MESSAGE('DebugPipe');

    IF v_ReturnCode != 0 THEN
      RAISE_APPLICATION_ERROR(-20210,
        'Debug.Debug: SEND_MESSAGE failed with ' || v_ReturnCode);
    END IF;

    /* ^[p̃pCvŃnhVFCNbZ[W҂ */
    v_ReturnCode := DBMS_PIPE.RECEIVE_MESSAGE(v_CurrentPipeName);

    IF v_ReturnCode = 1 THEN
       -- ^CAEg
      RAISE_APPLICATION_ERROR(-20211,
        'Debug.Debug: No handshake message received');
    ELSIF v_ReturnCode != 0 THEN
      -- ̑̃G[
      RAISE_APPLICATION_ERROR(-20212,
        'Debug.Debug: RECEIVE_MESSAGE failed with ' ||
        v_ReturnCode);
    ELSE
      -- nhVFCNbZ[W̃`FbN
      DBMS_PIPE.UNPACK_MESSAGE(v_Handshake);
      IF v_Handshake = 'Processed' THEN
        -- o͂ꂽꍇ
        NULL;
      ELSE
        -- nhVFCNȂ̏ꍇ
        RAISE_APPLICATION_ERROR(-20213,
          'Debug.Debug: Incorrect handshake message received');
      END IF;
    END IF;
  END Debug;

  PROCEDURE Reset IS
    /* pCvăeXgbZ[W𑗐MAf[삵Ă邩
       mFD삵ĂȂꍇ́AG[ʒm */
    v_ReturnCode NUMBER;
  BEGIN
    DBMS_PIPE.PACK_MESSAGE('TEST');
    v_ReturnCode := DBMS_PIPE.SEND_MESSAGE('DebugPipe');

    IF v_ReturnCode != 0 THEN
      RAISE_APPLICATION_ERROR(-20200,
        'Debug.Reset: SEND_MESSAGE failed with ' || v_ReturnCode);
    END IF;

    /* f[́ApCvĉD̃R[
       ^CAEgɂȂꍇ́Af[̏łĂȂ
       ߁AG[ʒm */
    v_ReturnCode := DBMS_PIPE.RECEIVE_MESSAGE('DebugPipe', v_TimeOut);
    IF v_ReturnCode = 1 THEN
      -- ^CAEg
      RAISE_APPLICATION_ERROR(-20201,
        'Debug.Reset: Daemon not ready');
    ELSIF v_ReturnCode != 0 THEN
      -- ̑̃G[
      RAISE_APPLICATION_ERROR(-20202,
        'Debug.Reset: RECEIVE_MESSAGE failed with ' ||
        v_ReturnCode);
    ELSE
      -- f[́A
      NULL;
    END IF;
  END Reset;

  PROCEDURE Exit IS
    v_ReturnCode NUMBER;
  BEGIN
    -- 'STOP'bZ[W𑗐M
    DBMS_PIPE.PACK_MESSAGE('STOP');
    v_ReturnCode := DBMS_PIPE.SEND_MESSAGE('DebugPipe');

    IF v_ReturnCode != 0 THEN
      RAISE_APPLICATION_ERROR(-20230,
        'Debug.Exit: SEND_MESSAGE failed with ' || v_ReturnCode);
    END IF;
  END Exit;

END Debug;



Chapter10
p.398-399
CREATE OR REPLACE PROCEDURE RecreateTempTable (
  /* temp_tablex폜ŁAēx쐬D\̐́A 
     p_Descriptionœn̂ƂA\̖ǑCREATE TABLE̓e
     Lq邱ƁDƂ΁Â悤ȃR[͗Lł

     RecreateTempTable('(num_col NUMBER, char_col VARCHAR2(50))');
  */
  p_Description IN VARCHAR2) IS

  v_Cursor        NUMBER;
  v_CreateString  VARCHAR2(100);
  v_DropString    VARCHAR2(100);
  v_NumRows       INTEGER; 
BEGIN
  /* ɎgpJ[\I[v */
  v_Cursor := DBMS_SQL.OPEN_CURSOR;

  /* ŏɕ\폜 */
  v_DropString := 'DROP TABLE temp_table';

  /* 'DROP TABLE'R}h͂ĎsDY\܂
     ĂȂꍇ́AORA-942ƂG[o */
  BEGIN
    -- DBMS_SQL.V7́ApbP[Wwb_[Œ`Ă萔ł
    DBMS_SQL.PARSE(v_Cursor, v_DropString, DBMS_SQL.V7);
    v_NumRows := DBMS_SQL.EXECUTE(v_Cursor);
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
        RAISE;
      END IF;
  END;
  /* ŁAtemp_tableēx쐬D܂ACREATE TABLE̕쐬
     ĂẢ͂ƎssKv */
  v_CreateString := 'CREATE TABLE temp_table ' || p_Description;
  DBMS_SQL.PARSE(v_Cursor, v_CreateString, DBMS_SQL.V7);
  v_NumRows := DBMS_SQL.EXECUTE(v_Cursor);

  /* ÎŁAJ[\N[Y */
  DBMS_SQL.CLOSE_CURSOR(v_Cursor);
EXCEPTION
  WHEN OTHERS THEN
    /* ܂J[\N[YĂAG[ēxʒmA
       Oɓ`d */
    DBMS_SQL.CLOSE_CURSOR(v_Cursor);
    RAISE;
END RecreateTempTable;


p.404
INSERT INTO temp_table (num_col, char_col)
  VALUES (:number_value, :char_value);


DBMS_SQL.BIND_VARIABLE(v_CursorID, ':number_value', -7);
DBMS_SQL.BIND_VARIABLE(v_CursorID, ':char_value', 'Hello');


p.407-408
CREATE OR REPLACE PROCEDURE DeleteMajor(
  /* w肳ꂽŮwADBMS_SQLgpāA\students炷ׂč폜
     폜šp_RowsDeletedɕԂ */
  p_Major       IN students.major%TYPE,
  p_RowsDeleted OUT INTEGER) AS

  v_CursorID   INTEGER;
  v_DeleteStmt VARCHAR2(100);
BEGIN
  -- ɎgpJ[\I[v
  v_CursorID := DBMS_SQL.OPEN_CURSOR;

  -- SQL肷
  v_DeleteStmt := 'DELETE FROM students WHERE major = :m';

  -- ͂
  DBMS_SQL.PARSE(v_CursorID, v_DeleteStmt, DBMS_SQL.V7);

  -- p_Majoru[Xz_ɃoCh
  DBMS_SQL.BIND_VARIABLE(v_CursorID, ':m', p_Major);

  -- s
  p_RowsDeleted := DBMS_SQL.EXECUTE(v_CursorID);

  -- J[\N[Y
  DBMS_SQL.CLOSE_CURSOR(v_CursorID);
EXCEPTION
  WHEN OTHERS THEN
    -- J[\N[YAG[ēxʒm
    DBMS_SQL.CLOSE_CURSOR(v_CursorID);
    RAISE;
END DeleteMajor;


p.408
SQL> VARIABLE v_NumDeleted NUMBER
SQL> exec DeleteMajor('History', :v_NumDeleted)

PL/SQL procedure successfully completed.

SQL> print v_NumDeleted

V_NUMDELETED
------------
           5


p.409
SELECT * FROM students

SELECT COUNT(*) "Number of Students", department || course
  FROM registered_students
  WHERE department IN (:d1, :d2)
  GROUP BY department || course

SELECT FullName(ID), ID
  FROM students
  WHERE ID = :student_id



p.410
SELECT first_name, last_name, num_credits
  FROM students 


DECLARE
  v_FirstName  VARCHAR2(30);
  v_LastName   VARCHAR2(40);
  v_NumCredits NUMBER;
  v_CursorID   INTEGER;
BEGIN
  ...
  DBMS_SQL.DEFINE_COLUMN(v_CursorID, 1, v_FirstName);
  DBMS_SQL.DEFINE_COLUMN(v_CursorID, 2, v_LastName);
  DBMS_SQL.DEFINE_COLUMN(v_CursorID, 3, v_NumCredits);
  ...
END;


p.416-418
CREATE OR REPLACE PROCEDURE DynamicQuery (
  /* DBMS_SQLgpĕ\studentsɖ₢킹sAʂtemp_table
     i[Dw肳ꂽUi2܂Łj̊wɊւāAOAA
     U} */
  p_Major1 IN students.major%TYPE DEFAULT NULL,
  p_Major2 IN students.major%TYPE DEFAULT NULL) AS

  v_CursorID   INTEGER;
  v_SelectStmt VARCHAR2(500);
  v_FirstName  students.first_name%TYPE;
  v_LastName   students.last_name%TYPE;
  v_Major      students.major%TYPE;
  v_Dummy      INTEGER;

BEGIN
  -- ɎgpJ[\I[v
  v_CursorID := DBMS_SQL.OPEN_CURSOR;

  -- ₢킹̕쐬
  v_SelectStmt := 'SELECT first_name, last_name, major
                     FROM students
                     WHERE major IN (:m1, :m2)
                     ORDER BY major, last_name';

  -- ₢킹͂
  DBMS_SQL.PARSE(v_CursorID, v_SelectStmt, DBMS_SQL.V7);

  -- ͕ϐoCh
  DBMS_SQL.BIND_VARIABLE(v_CursorID, ':m1', p_Major1);
  DBMS_SQL.BIND_VARIABLE(v_CursorID, ':m2', p_Major2);

  -- o͕ϐ`
  DBMS_SQL.DEFINE_COLUMN(v_CursorID, 1, v_FirstName, 20);
  DBMS_SQL.DEFINE_COLUMN(v_CursorID, 2, v_LastName, 20);
  DBMS_SQL.DEFINE_COLUMN(v_CursorID, 3, v_Major, 30);

  -- sD߂l͉łĂ܂ȂA
  -- ߂lp̕ϐ錾Kv
  v_Dummy := DBMS_SQL.EXECUTE(v_CursorID);

  -- ĹAo[v
  LOOP
    -- A̍sobt@ɎoA
    -- [v̏I`FbN
    IF DBMS_SQL.FETCH_ROWS(v_CursorID) = 0 THEN
      EXIT;
    END IF;

    -- A̍sobt@PL/SQLϐɎo
    DBMS_SQL.COLUMN_VALUE(v_CursorID, 1, v_FirstName);
    DBMS_SQL.COLUMN_VALUE(v_CursorID, 2, v_LastName);
    DBMS_SQL.COLUMN_VALUE(v_CursorID, 3, v_Major);

    -- of[^Atemp_tableɑ}
    INSERT INTO temp_table (char_col)
      VALUES (v_FirstName || ' ' || v_LastName || ' is a ' ||
              v_Major || ' major.');
  END LOOP;

  -- J[\N[Y
  DBMS_SQL.CLOSE_CURSOR(v_CursorID);

  -- sʂR~bg
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    -- J[\N[YAG[ēxʒm
    DBMS_SQL.CLOSE_CURSOR(v_CursorID);
    RAISE;
END DynamicQuery;


p.418
SQL> exec DynamicQuery('History', 'Music')
PL/SQL procedure successfully completed.

SQL> SELECT char_col FROM temp_table;

CHAR_COL
------------------------------------------
Margaret Mason is a History major.
Patrick Poll is a History major.
Timothy Taller is a History major.
David Dinsmore is a Music major.
Rose Riznit is a Music major.


p.419
BEGIN :placeholder := 7; END;

DECLARE
  v_Numeric   NUMBER := :p1;
  v_Character VARCHAR2(50) := :p2;
BEGIN
  INSERT INTO temp_table VALUES (v_Numeric, v_Character);
END;

BEGIN
  SELECT first_name, last_name
    INTO :first_name, :last_name
    FROM students
    WHERE ID = :ID;
END;


p.421-423
CREATE OR REPLACE PROCEDURE DynamicPLSQL (
  /* PL/SQLubN𓮓IɎsDPL/SQLubNł́A
     studentsɑ΂đIsA͗p̃v[Xz_Ƃ
     p_StudentIDgp */
  p_StudentID IN students.ID%TYPE) IS

  v_CursorID  INTEGER;
  v_BlockStr  VARCHAR2(500);
  v_FirstName students.first_name%TYPE;
  v_LastName  students.last_name%TYPE;
  v_Dummy     INTEGER;

BEGIN
  -- ŎgpJ[\I[v
  v_CursorID := DBMS_SQL.OPEN_CURSOR;

  -- PL/SQLubN܂ޕ쐬D
  -- ̒̕ŁA:first_name:last_nameƂ
  -- v[Xz_͏o͕ϐA:ID͓͕ϐł
  v_BlockStr := 
    'BEGIN
       SELECT first_name, last_name
         INTO :first_name, :last_name
         FROM students
         WHERE ID = :ID;
     END;';

  -- ͂
  DBMS_SQL.PARSE(v_CursorID, v_BlockStr, DBMS_SQL.V7);

  -- v[Xz_ϐɃoChD̏́A͕ϐ
  -- o͕ϐ̗Ɏs_ɒӂ邱ƁD:first_name
  -- :last_nameł́Aő咷n
  DBMS_SQL.BIND_VARIABLE(v_CursorID, ':first_name', v_FirstName, 30);
  DBMS_SQL.BIND_VARIABLE(v_CursorID, ':last_name', v_LastName, 30);
  DBMS_SQL.BIND_VARIABLE(v_CursorID, ':ID', p_StudentID);

  -- sD߂l͉łĂ܂Ȃ
  -- ߂lp̕ϐ錾Kv
  v_Dummy := DBMS_SQL.EXECUTE(v_CursorID);

  -- o͕ϐ̒lo
  DBMS_SQL.VARIABLE_VALUE(v_CursorID, ':first_name', v_FirstName);
  DBMS_SQL.VARIABLE_VALUE(v_CursorID, ':last_name', v_LastName);

  -- oltemp_tableɑ}
  INSERT INTO temp_table (num_col, char_col)
    VALUES (p_StudentID, v_FirstName || ' ' || v_LastName);

  -- J[\N[Y
  DBMS_SQL.CLOSE_CURSOR(v_CursorID);

  -- sʂR~bg
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    -- J[\N[YAG[ēxʒm
    DBMS_SQL.CLOSE_CURSOR(v_CursorID);
    RAISE;
END DynamicPLSQL;


p.423
SQL> exec DynamicPLSQL(10010)
PL/SQL procedure successfully completed.

SQL> exec DynamicPLSQL(10003)
PL/SQL procedure successfully completed.

SQL> SELECT * FROM temp_table;

  NUM_COL CHAR_COL
--------- ------------------
    10010 Rita Razmataz
    10003 Manish Murgratroid


p.427-428
DECLARE
  v_CursorID INTEGER;
  ...
BEGIN
  ...
EXCEPTION
  WHEN OTHERS THEN
    IF DBMS_SQL.IS_OPEN(v_CursorID) THEN
      DBMS_SQL.CLOSE_CURSOR(v_CursorID);
    END IF;
    RAISE;
END;


p.429
DROP TABLE temp_table;
CREATE TABLE temp_table (num_col NUMBER, char_col VARCHAR2(50));


ORA-01031: sĂ܂B



Chapter11
p.436
CREATE SEQUENCE temp_seq
  START WITH 1
  INCREMENT BY 1;

CREATE OR REPLACE PROCEDURE TempInsert AS
BEGIN
    INSERT INTO temp_table (num_col)
      VALUES (temp_seq.nextval);
    COMMIT;
END TempInsert;


SQL> VARIABLE v_JobNum NUMBER
SQL> BEGIN
  2    DBMS_JOB.SUBMIT(:v_JobNum, 'TempInsert;', sysdate,
  3                    'sysdate + (1/(24*60*60))');
  4  END;
  5  /

PL/SQL procedure successfully completed.
SQL> print v_JobNum

 V_JOBNUM
---------
        2


p.438
CREATE OR REPLACE PROCEDURE TempInsert
  (p_NextDate IN OUT DATE) AS
  v_CurrVal NUMBER;
BEGIN
  INSERT INTO temp_table (num_col)
    VALUES (temp_seq.nextval);
  SELECT temp_seq.currval
    INTO v_CurrVal
    FROM dual;
  IF v_CurrVal > 15 THEN
    p_NextDate := NULL;
  END IF;
  COMMIT;
END TempInsert;


BEGIN
  DBMS_JOB.SUBMIT(:v_JobNum, 'TempInsert(next_date);', sysdate,
                  'sysdate + (1/(24*60*60))');
END;


'Register(10006, ''MUS'', 410);'


p.442
utl_file_dir = /tmp
utl_file_dir = /home/oracle/output_files


p.443
utl_file_dir = *


p.450
DECLARE
  v_OutputFile UTL_FILE.FILE_TYPE;
  v_Name VARCHAR2(10) := 'Scott';
BEGIN
  v_OutputFile := UTL_FILE.FOPEN(...);
  UTL_FILE.PUTF(v_OutputFile,
    'Hi there!\nMy name is %s, and I am a %s major.\n',
    v_Name, 'Computer Science');
  FCLOSE(v_OutputFile);
END;


Hi There!
My name is Scott, and I am a Computer Science major.


p.452-453
CREATE OR REPLACE PACKAGE Debug AS

  /* fobNp̃t@C̖OƃfBgNi[邽߂̃O[oϐ */
  v_DebugDir VARCHAR2(50);
  v_DebugFile VARCHAR2(20);

  PROCEDURE Debug(p_Description IN VARCHAR2,
                  p_Value IN VARCHAR2);

  PROCEDURE Reset(p_NewFile IN VARCHAR2 := v_DebugFile,
                  p_NewDir IN VARCHAR2 := v_DebugDir) ;

  /* fobNp̃t@CN[Y */
  PROCEDURE Close;
END Debug;
/

CREATE OR REPLACE PACKAGE BODY Debug AS

  v_DebugHandle UTL_FILE.FILE_TYPE;

  PROCEDURE Debug(p_Description IN VARCHAR2,
                  p_Value IN VARCHAR2) IS
  BEGIN
    /* o͂At@CtbV */
    UTL_FILE.PUTF(v_DebugHandle, '%s: %s\n', p_Description, p_Value);
    UTL_FILE.FFLUSH(v_DebugHandle);
  EXCEPTION
    WHEN UTL_FILE.INVALID_OPERATION THEN
      RAISE_APPLICATION_ERROR(-20102,
                              'Debug: Invalid Operation');
    WHEN UTL_FILE.INVALID_FILEHANDLE THEN
      RAISE_APPLICATION_ERROR(-20103,
                              'Debug: Invalid File Handle');
    WHEN UTL_FILE.WRITE_ERROR THEN
      RAISE_APPLICATION_ERROR(-20104,
                              'Debug: Write Error');
  END Debug;

  PROCEDURE Reset(p_NewFile IN VARCHAR2 := v_DebugFile,
                  p_NewDir IN VARCHAR2 := v_DebugDir) IS
  BEGIN

    /* t@CN[YĂ邩A܂`FbN */
    IF UTL_FILE.IS_OPEN(v_DebugHandle) THEN
      UTL_FILE.FCLOSE(v_DebugHandle);
    END IF;

    /* ݗp̃t@CI[v */
    v_DebugHandle := UTL_FILE.FOPEN(p_NewDir, p_NewFile, 'w');

    /* Anꂽ΂̒lpbP[Wϐɐݒ肷 */
    v_DebugFile := p_NewFile;
    v_DebugDir := p_NewDir;
  EXCEPTION
    WHEN UTL_FILE.INVALID_PATH THEN
      RAISE_APPLICATION_ERROR(-20100, 'Reset: Invalid Path');
    WHEN UTL_FILE.INVALID_MODE THEN
      RAISE_APPLICATION_ERROR(-20101, 'Reset: Invalid Mode');
    WHEN UTL_FILE.INVALID_OPERATION THEN
      RAISE_APPLICATION_ERROR(-20101, 'Reset: Invalid Operation');
  END Reset;

  PROCEDURE Close IS
  BEGIN
    UTL_FILE.FCLOSE(v_DebugHandle);
  END Close;

BEGIN
  v_DebugDir := '/tmp';
  v_DebugFile := 'debug.out';
  Reset;
END Debug; 


p.454-456
CREATE OR REPLACE PROCEDURE LoadStudents (
  /* J}؂t@Cǂݍ݁A\studentsɃ[h
     Yt@Cɂ́Â悤ȍsi[Ă̂Ƃ

     wID́Astudent_sequence琶
     ܂A}s̑́Ap_TotalInsertedԂ */
  p_FileDir  IN VARCHAR2,
  p_FileName IN VARCHAR2,
  p_TotalInserted IN OUT NUMBER) AS

  v_FileHandle UTL_FILE.FILE_TYPE;
  v_NewLine  VARCHAR2(100);  -- Input line
  v_FirstName students.first_name%TYPE;
  v_LastName students.last_name%TYPE;
  v_Major students.major%TYPE;
  /* ͍sȓł̃J}̈ʒu */
  v_FirstComma NUMBER;
  v_SecondComma NUMBER;

BEGIN
  -- w肳ꂽt@CAǂݍݗpɃI[v
  v_FileHandle := UTL_FILE.FOPEN(p_FileDir, p_FileName, 'r');

  -- w̏o͐
  p_TotalInserted := 0;

  -- Yt@CŜA[vłPsǂݍ
  -- ǂݍ݂ƁAGET_LINENO_DATA_FOUNDʒm
  -- ̂߁A̓[v̏IƂĎg
  LOOP
    BEGIN
      UTL_FILE.GET_LINE(v_FileHandle, v_NewLine);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        EXIT;
    END;

    -- ̓R[h̊etB[h́AJ}ŋ؂Ă
    -- ͈ȓ̂Q̃J}̈ʒu𒲂ׁÄʒugp
    -- v_NewLineYtB[h擾KvD
    -- J}̈ʒúAINSTRgpĒׂ
    v_FirstComma := INSTR(v_NewLine, ',', 1, 1);
    v_SecondComma := INSTR(v_NewLine, ',', 1, 2);

    -- ܂ł΁ASUBSTRɂĈÃtB[h𒊏oł
    v_FirstName := SUBSTR(v_NewLine, 1, v_FirstComma - 1);
    v_LastName := SUBSTR(v_NewLine, v_FirstComma + 1,
                         v_SecondComma - v_FirstComma - 1);
    v_Major := SUBSTR(v_NewLine, v_SecondComma + 1);

    -- studentsɐVR[hP}
    INSERT INTO students (ID, first_name, last_name, major)
      VALUES (student_sequence.nextval, v_FirstName,
              v_LastName, v_Major);

    p_TotalInserted := p_TotalInserted + 1;
  END LOOP;

  -- t@CN[Y
  UTL_FILE.FCLOSE(v_FileHandle);

  COMMIT;
EXCEPTION
  -- UTL_FILËA̗OӖʂɏAYt@CN[Y
  -- Ă邩`FbN
  WHEN UTL_FILE.INVALID_OPERATION THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE_APPLICATION_ERROR(-20051,
                            'LoadStudents: Invalid Operation');
  WHEN UTL_FILE.INVALID_FILEHANDLE THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE_APPLICATION_ERROR(-20052,
                            'LoadStudents: Invalid File Handle');
  WHEN UTL_FILE.READ_ERROR THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE_APPLICATION_ERROR(-20053,
                            'LoadStudents: Read Error');
  WHEN OTHERS THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE;
END LoadStudents; 


p.456
Scott,Smith,Computer Science
Margaret,Mason,History
Joanne,Junebug,Computer Science
Manish,Murgratroid,Economics
Patrick,Poll,History
Timothy,Taller,History
Barbara,Blues,Economics
David,Dinsmore,Music
Ester,Elegant,Nutrition
Rose,Riznit,Music
Rita,Razmataz,Nutrition


p.455-457
CREATE OR REPLACE PROCEDURE CalculateGPA (
  /* p_StudentIDŎw肳ꂽwGPAp_GPAɕԂ */
  p_StudentID IN students.ID%TYPE,
  p_GPA OUT NUMBER) AS

  CURSOR c_ClassDetails IS
    SELECT classes.num_credits, rs.grade
      FROM classes, registered_students rs
      WHERE classes.department = rs.department
      AND classes.course = rs.course
      AND rs.student_id = p_StudentID;

  v_NumericGrade NUMBER;
  v_TotalCredits NUMBER := 0;
  v_TotalGrade NUMBER := 0;

BEGIN
  FOR v_ClassRecord in c_ClassDetails LOOP
    -- тɊY鐔l𒲂ׂ
    SELECT DECODE(v_ClassRecord.grade, 'A', 4,
                                       'B', 3,
                                       'C', 2,
                                       'D', 1,
                                       'E', 0)
      INTO v_NumericGrade
      FROM dual;

    v_TotalCredits := v_TotalCredits + v_ClassRecord.num_credits;
    v_TotalGrade := v_TotalGrade +
                    (v_ClassRecord.num_credits * v_NumericGrade);
  END LOOP;

  p_GPA := v_TotalGrade / v_TotalCredits;
END CalculateGPA; 


p.457-459
CREATE OR REPLACE PROCEDURE PrintTranscript (
  /* w肳ꂽw̐яؖo͂Dяؖ́A
     wݓo^ĂÃNXƂ̊eNX
     т\D܂Aя̖ؖɂ́A
     ̊wGPAo͂ */
  p_StudentID IN students.ID%TYPE,
  p_FileDir IN VARCHAR2,
  p_FileName IN VARCHAR2) AS

  v_StudentGPA NUMBER;
  v_StudentRecord  students%ROWTYPE;
  v_FileHandle UTL_FILE.FILE_TYPE;
  v_NumCredits NUMBER;

  CURSOR c_CurrentClasses IS
    SELECT *
      FROM registered_students
      WHERE student_id = p_StudentID;

BEGIN
  -- o̓t@Cǉ(append)[hŃI[v
  v_FileHandle := UTL_FILE.FOPEN(p_FileDir, p_FileName, 'w');

  SELECT *
    INTO v_StudentRecord
    FROM students
    WHERE ID = p_StudentID;

  -- wb_[o͂Dwb_[́A݂̓tƎA
  -- Yw̏񂩂\

  UTL_FILE.PUTF(v_FileHandle, 'Student ID: %s\n',
    v_StudentRecord.ID);
  UTL_FILE.PUTF(v_FileHandle, 'Student Name: %s %s\n',
    v_StudentRecord.first_name, v_StudentRecord.last_name);
  UTL_FILE.PUTF(v_FileHandle, 'Major: %s\n',
    v_StudentRecord.major);
  UTL_FILE.PUTF(v_FileHandle, 'Transcript Printed on: %s\n\n\n',
    TO_CHAR(SYSDATE, 'Mon DD,YYYY HH24:MI:SS'));

  UTL_FILE.PUT_LINE(v_FileHandle, 'Class   Credits Grade');
  UTL_FILE.PUT_LINE(v_FileHandle, '------- ------- -----');
  FOR v_ClassesRecord in c_CurrentClasses LOOP
    -- YNX̒Pʐ𒲂ׂ
    SELECT num_credits
      INTO v_NumCredits
      FROM classes
      WHERE course = v_ClassesRecord.course
      AND department = v_ClassesRecord.department;

    -- YNX̏o͂
    UTL_FILE.PUTF(v_FileHandle, '%s %s %s\n',
      RPAD(v_ClassesRecord.department || ' '  ||
           v_ClassesRecord.course, 7),
      LPAD(v_NumCredits, 7),
      LPAD(v_ClassesRecord.grade, 5));
  END LOOP;

  -- GPA߂
  CalculateGPA(p_StudentID, v_StudentGPA);

  -- GPAo͂
  UTL_FILE.PUTF(v_FileHandle, '\n\nCurrent GPA: %s\n',
    TO_CHAR(v_StudentGPA, '9.99'));

  -- Yt@CN[Y
  UTL_FILE.FCLOSE(v_FileHandle);

EXCEPTION
  -- UTL_FILËA̗OӖʂ̏AYt@CN[Y
  -- Ă邩`FbN
  WHEN UTL_FILE.INVALID_OPERATION THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE_APPLICATION_ERROR(-20061,
                            'PrintTranscript: Invalid Operation');
  WHEN UTL_FILE.INVALID_FILEHANDLE THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE_APPLICATION_ERROR(-20062,
                            'PrintTranscript: Invalid File Handle');
  WHEN UTL_FILE.WRITE_ERROR THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE_APPLICATION_ERROR(-20063,
                            'PrintTranscript: Write Error');
  WHEN OTHERS THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE;
END PrintTranscript; 


p.459
SQL> select * from registered_students;
STUDENT_ID DEP     COURSE G
---------- --- ---------- -
     10002 CS         102 B
     10002 HIS        101 B
     10002 ECN        203 A
     10002 CS         101 A
     10009 HIS        101 D
     10009 MUS        410 B
     10009 HIS        301 C
     10009 MUS        410 B

8 rows selected.


p.459-460
Student ID: 10002
Student Name: Joanne Junebug
Major: Computer Science
Transcript Printed on: Jan 27,1996 17:37:43


Class   Credits Grade
------- ------- -----
CS  102       4     B
HIS 101       4     B
ECN 203       3     A
CS  101       4     A


Current GPA:  3.47




Student ID: 10009
Student Name: Rose Riznit
Major: Music
Transcript Printed on: Jan 27,1996 17:38:56


Class   Credits Grade
------- ------- -----
HIS 101       4     D
MUS 410       3     B
HIS 301       4     C
MUS 410       3     B


Current GPA:  2.14



Chapter12
p.467
ORA-4031: unable to allocate X bytes of shared memory


ALTER SYSTEM FLUSH SHARED POOL;


p.469
SQL> SELECT name, type, code_size
  2    FROM dba_object_size
  3    WHERE name IN ('DBMS_PIPE', 'STANDARD', 'DBMS_OUTPUT')
  4    ORDER BY name, type;

NAME                           TYPE         CODE_SIZE
------------------------------ ------------ ---------
DBMS_OUTPUT                    PACKAGE            388
DBMS_OUTPUT                    PACKAGE BODY      6217
DBMS_OUTPUT                    SYNONYM              0
DBMS_PIPE                      PACKAGE            699
DBMS_PIPE                      PACKAGE BODY      6427
DBMS_PIPE                      SYNONYM              0
STANDARD                       PACKAGE          10494
STANDARD                       PACKAGE BODY     22400


SQL> SELECT sid
  2    FROM v$process p, v$session s
  3    WHERE p.addr = s.paddr
  4    AND s.username = 'SYSTEM';

      SID
---------
        6


p.470
SQL> SELECT value
  2    FROM v$sesstat s, v$statname n
  3    WHERE s.statistic# = n.statistic#
  4    AND n.name = 'session uga memory max'
  5    AND SID = 6;

    VALUE
---------
    94704


p.472
CREATE TABLE plan_table (
  statement_id    VARCHAR2(30),
  timestamp       DATE,
  remarks         VARCHAR2(80),
  operation       VARCHAR2(30),
  options         VARCHAR2(30),
  object_node     VARCHAR2(30),
  object_owner    VARCHAR2(30),
  object_name     VARCHAR2(30),
  object_instance NUMBER,
  object_type     VARCHAR2(30),
  search_columns  NUMBER,
  id              NUMBER,
  parent_id       NUMBER,
  position        NUMBER,
  other           LONG);


p.473
EXPLAIN PLAN
  SET STATEMENT_ID = 'Query 1' FOR
    SELECT rs.course, rs.department, students.ID
      FROM registered_students rs, students
      WHERE rs.student_id = students.id
      AND students.last_name = 'Razmataz';


SELECT LPAD(' ', 2 * (LEVEL - 1)) || operation ||
       ' ' || options || ' ' || object_name || ' ' ||
       DECODE(id, 0, 'Cost = ' || position) "Execution Plan"
  FROM plan_table
  START WITH id = 0
    AND statement_id = 'Query 1'
  CONNECT BY PRIOR id = parent_id
    AND statement_id = 'Query 1';


Execution Plan
------------------------------------------
SELECT STATEMENT   Cost =
  NESTED LOOPS
    TABLE ACCESS FULL REGISTERED_STUDENTS
    TABLE ACCESS BY ROWID STUDENTS
      INDEX UNIQUE SCAN SYS_C00859


p.474-475
SQL> ALTER SESSION SET SQL_TRACE = TRUE;
Session Altered.
SQL> SELECT rs.course, rs.department, students.ID
       FROM registered_students rs, students
       WHERE rs.student_id = students.id
       AND students.last_name = 'Razmataz';
    COURSE DEP         ID
---------- --- ----------
       101 HIS      10010
       307 NUT      10010

SQL> ALTER SESSION SET SQL_TRACE = FALSE;
Session altered.


p.476-477
SELECT rs.course, rs.department, students.ID
      FROM registered_students rs, students
      WHERE rs.student_id = students.id
      AND students.last_name = 'Razmataz'

call      count  cpu   elapsed  disk  query  current  rows
--------  -----  ----  -------  ----  -----  -------  ----
Parse         1  0.00  0.00     0     0      0        0
Execute       1  0.00  0.00     0     0      0        0
Fetch         1  0.00  0.00     0     55     3        2

--------  -----  ----  -------  ----  -----  ------   ----
total         3  0.00  0.00     0     55     3        2

Misses in library cache during parse: 1
Optimizer hint: CHOOSE
Parsing user id: 9  (EXAMPLE)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   OPTIMIZER HINT: CHOOSE
      2    NESTED LOOPS
     18      TABLE ACCESS (FULL) OF 'REGISTERED_STUDENTS'
     18      TABLE ACCESS (BY ROWID) OF 'STUDENTS'
     18        INDEX (UNIQUE SCAN) OF 'SYS_C00859' (UNIQUE)

**************************************************************



AppendixA
p.483
DECLARE
  "BEGIN" NUMBER;
BEGIN
  "BEGIN" := 7;
END;



AppendixB
p.495
DBMS_SESSION.SET_ROLE('Administrator IDENTIFIED BY admin');


DBMS_SESSION.SET_NLS('nls_date_format', '''DD-MON-YY
                      HH24:MI:SS''');


p.502
DECLARE
  v_Start NUMBER;
  v_End NUMBER;
BEGIN
  v_Start := DBMS_UTILITY.GET_TIME;
  /* ŁCsD*/
  v_End := DBMS_UTILITY.GET_TIME;
  /* Y鏈̎sɂ́C(v_Start - v_End) * 100bƂɂȂD */
END;



AppendixC
p.508
DECLARE
  e_NonExistentTable  EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_NonExistentTable, -942);
  ...


p.514
DECLARE
  v_ClassInfo classes%ROWTYPE;


p.517
DECLARE
  v_FirstName      students.first_name%TYPE;
  v_CurrentCredits students.current_credits%TYPE;


p.519
  -- ́CPs̃RgD
/* ́Cs̃RgłC
   sɂ܂ėLłD */


p.521
DECLARE
  TYPE t_DateTable IS TABLE OF DATE
    INDEX BY BINARY_INTEGER;
  v_Dates t_DateTable;


p.523
DECLARE
  TYPE t_StudentType IS RECORD (
    FirstName students.first_name%TYPE;
    LastName  students.last_name%TYPE;
    ID        students.ID%TYPE);
  v_StudentInfo t_StudentType;



AppendixD
p.527
SQL> SELECT object_type, status
  2    FROM user_objects
  3    WHERE object_name = UPPER('ClassPackage');

OBJECT_TYPE   STATUS
------------- -------
PACKAGE       VALID
PACKAGE BODY  VALID


p.529
SELECT line, position, text
  FROM user_errors
  WHERE name = object_name
  ORDER BY sequence;


p.531
SELECT text
  FROM user_source
  WHERE NAME = object_name
  ORDER BY LINE;

































 














