wORACLE8 A[LeN`x


Chapter3
p.40
SELECT * FROM sales.orders;

ID         CUST_ID    ORDER_DATE SHIP_DATE PAID_DATE STATUS
---------- ---------- ---------- --------- --------- ------
         1          1 16-OCT-97  16-OCT-97 16-OCT-97 F
         2         10 16-OCT-97  16-OCT-97 16-OCT-97 B
         3         23 16-OCT-97  16-OCT-97 16-OCT-97 F
...


p.41
SELECT id, order_date
  FROM sales.orders
  WHERE cust_id = 1;

ID         ORDER_DATE
---------- ----------
         1 16-OCT-97
        68 19-OCT-97
       130 23-OCT-97
...


SELECT * FROM sales.orders
  WHERE order_date = '16-10-97' AND status = 'F';

ID         CUST_ID    ORDER_DATE SHIP_DATE PAID_DATE STATUS
---------- ---------- ---------- --------- --------- ------
         1          1 16-OCT-97  16-OCT-97 16-OCT-97 F
         3         23 16-OCT-97  16-OCT-97 16-OCT-97 F
...

SELECT * FROM sales.orders
  WHERE cust_id = (
    SELECT id FROM sales.customers
      WHERE last_name = 'Ellison' OR last_name = 'White' );
ID         CUST_ID    ORDER_DATE SHIP_DATE PAID_DATE STATUS
---------- ---------- ---------- --------- --------- ------
         1          1 16-OCT-97  16-OCT-97 16-OCT-97 F
         2         10 16-OCT-97  16-OCT-97 16-OCT-97 B
...



p.42
SELECT i.id lineid, p.description, i.quantity, p.unitprice 
  FROM sales.items i, sales.parts p
  WHERE i.order_id = 1732
    AND i.part_id = p.id;

LINEID     DESCRIPTION          QUANTITY   UNIT_PRICE
---------- -------------------- ---------- ----------
         1 Pentium 166 CPU               2      150.9
         2 Network Computer              1        500
...



SELECT last_name, first_name FROM sales.customers
  ORDER BY last_name, first_name ASC;

LAST_NAME          FIRST_NAME
------------------ -------------------
Ellison            Lawrence
Gates              William
White              Phillip
...



p.43
INSERT INTO sales.parts (id, unit_price, description)
  VALUES (45, 1000.00, 'Pentium 166 CPU');


INSERT INTO archive.customers
  (SELECT * FROM sales.customers);


UPDATE sales.parts
  SET unit_price = 250.00
  WHERE id = 492;


DELETE FROM sales.customers
  WHERE last_name = 'Gates' AND first_name = 'William';


p.44
CREATE TABLE sales.parts
( id INTEGER PRIMARY KEY,
  unit_price NUMBER(10,2),
  description VARCHAR2(150));


ALTER TABLE sales.parts
  MODIFY (unit_price DEFAULT 0.00, description NOT NULL);


CREATE VIEW cust_usa AS
  SELECT last_name, first_name, phone
    FROM sales.customers
    WHERE country = 'USA'
  WITH CHECK OPTION;


p.45
CREATE INDEX sales.part_descriptions ON sales.parts(description);

ALTER INDEX sales.part_descriptions REBUILD;

DROP INDEX sales.part_descriptions;


CREATE SEQUENCE sales.parts_id
  START WITH 1
  INCREMENT BY 1
  NOMAXVALUE
  NOCYCLE;


p.47
SELECT --+INDEX(customers state)
 id, last_name, first_name
 FROM customers
 WHERE state = 'CA';


p.48
INSERT INTO sales.orders
  (id, cust_id, order_date, ship_date, paid_date, status)
  VALUES (sales.order_id.NEXTVAL, 391, SYSDATE, NULL, NULL, 'F');
INSERT INTO sales.items
  (order_id, id, part_id, quantity)
  VALUES (sales.order_id.CURRVAL, 1, 32, 2);
INSERT INTO sales.items
  (order_id, id, part_id, quantity)
  VALUES (sales.order_id.CURRVAL, 2, 11, 1);


COMMIT WORK;


ROLLBACK WORK;


p.49
SET TRANSACTION READ WRITE;


SET TRANSACTION READ ONLY;


p.50
EXECUTE dbms_transaction.begin_discrete_transaction;
INSERT ... ;
INSERT ... ;
INSERT ... ;
COMMIT WORK ;


p.51
INSERT INTO sales.customers ... ;
INSERT INTO sales.parts ... ;
INSERT INTO sales.orders ... ;
INSERT INTO sales.items ... ;
INSERT INTO sales.items ... ;
COMMIT WORK ;


INSERT INTO sales.orders ... ;
COMMIT WORK ;
INSERT INTO sales.items ... ;
COMMIT WORK ;
INSERT INTO sales.items ... ;
COMMIT WORK ;



Chapter3
p.54
PROCEDURE update_part_unitprice (part_id IN INTEGER, new_price IN NUMBER)
IS
 invalid_part EXCEPTION;
BEGIN
-- UPDATEŃf[^x[X̃R[hXV
 UPDATE sales.parts
  SET unit_price = new_price
  WHERE id = part_id;
-- G[`FbN
 IF SQL%NOTFOUND THEN
  RAISE invalid_part;
 END IF;
EXCEPTION
-- G[[`
 WHEN invalid_part THEN
  raise_application_error(-20000, 'Invalid Part ID');
END update_part_unitprice;


p.56
--1s琬Rg̏ꍇ͐擪ɃnCt2tB
/*s琬Rg̏ꍇ́A擪"/*"A"*/"tB
s̃Rgɂ́Asł͂łB*/


DECLARE
 emp_id INTEGER;
 standard_commission CONSTANT INTEGER := 500;
...


p.57
DECLARE
 counter INTEGER := 0;  -- example of initial value
 emp_commission INTEGER DEFAULT 0;  -- example of default value


p.58
DECLARE
 varchar2_50 VARCHAR2(50);
 SUBTYPE description IS Varchar2_50;
 current_description Description DEFAULT 'Unknown';
...


p.59
DECLARE
 TYPE part_record IS RECORD (
  id INTEGER,
  unit_price NUMBER(10,2),
  description VARCHAR2(200)
 );
 current_part Part_Record;
...


DECLARE
 TYPE part_record IS RECORD (
  id INTEGER,
  unit_price NUMBER(10,2),
  description VARCHAR2(200)
 );
 TYPE parts_table IS TABLE OF Part_Record;
 current_parts_table Parts_Table;
...


p.60
DECLARE
 TYPE part_record IS RECORD (
  id INTEGER,
  unit_price NUMBER(10,2),
  description VARCHAR2(150)
 );
 TYPE parts_table IS VARRAY(3) OF Part_Record;
 current_parts_table Parts_Table;
...


p.61
DECLARE
 TYPE part_record IS RECORD (
  id sales.parts.id%TYPE,
  unit_price sales.parts.unit_price%TYPE,
  description sales.parts.description%TYPE
 );
 current_part Part_Record;


DECLARE
 TYPE parts_table IS TABLE OF sales.parts%ROWTYPE;
 current_parts_table Parts_Table;
...


p.62
DECLARE
 CURSOR parts_cursor IS
  SELECT * FROM sales.parts;

 CURSOR customers_cursor (state_id CHAR) IS
  SELECT id, last_name, first_name, phone
   FROM sales.customers
   WHERE state = state_id;
...


DECLARE
-- ̃XgOJ[\^Cvw肵 ...
 TYPE parts_type IS REF CURSOR RETURN sales.parts%ROWTYPE;
-- ... ΉJ[\ϐw
 parts_cursor1 Parts_Type;
 parts_cursor2 Parts_Type;
 parts_cursor3 Parts_Type;

-- ʓIȃEB[NJ[\^Cvw
 TYPE cursor_type IS REF CURSOR;
...


p.63-64
-- XJ[ϐ̊ė
DECLARE
 emp_id INTEGER;
 another_integer_variable INTEGER := 0;
 part_description VARCHAR2(200);
BEGIN
 emp_id := 1;
 emp_id := another_integer_variable;
 part_description := 'Network Computer';
...

-- R[hϐ̊ė
DECLARE
 TYPE part_record IS RECORD (
  id INTEGER,
  unit_price NUMBER(10,2),
  description VARCHAR2(200)
 );
 current_part Part_Record;
 another_Part_Record_variable Part_Record;
BEGIN
 current_part.id := 1;
 current_part.description := 'Network Computer';
 current_part := another_Part_Record_variable;
...

-- lXg\܂varrayϐ̊ė
DECLARE
 TYPE part_record IS RECORD (
  id INTEGER,
  unit_price NUMBER(10,2),
  description VARCHAR2(200)
 );
 TYPE parts_table IS TABLE OF Part_Record;
 current_parts_table Parts_Table;
BEGIN
 current_parts_table(1).id := 1;
 current_parts_table(1).description := 'Network Computer';
 current_parts_table(1) := current_parts_table(2);
...


p.65
DECLARE
 TYPE parts_table IS TABLE OF sales.parts%ROWTYPE;
 current_parts_table Parts_Table := Parts_Table (
  (1, 150.90,'Pentium 166 CPU'),
  NULL,
  (3,500.00,'Network Computer')
 );
...


p.66
record_count := current_parts_table.COUNT;
current_parts_record := current_parts_table.FIRST;
current_parts_table.DELETE(3);
current_parts_table.DELETE(4,6);
current_parts_table.DELETE;


p.66-67
-- {IIF
IF condition THEN
 statement1;
 statement2;
 ...
END IF;

-- IF-ELSE
IF condition THEN
 statement1;
 statement2;
 ...
ELSE
 statement3;
 ...
END IF;

-- ɕGIF-ELSIF-ELSE
IF condition THEN
 statement1;
 statement2;
 ...
ELSIF  -- NOT "ELSEIF"
 statement3;
 statement4;
 ...
ELSIF
 statement5;
 ...
ELSE
 statement6;
 statement7;
 ...
END IF;


p.67-68
-- EXIT-WHENgpĂ{Iȃ[v
LOOP
 statement1;
 statement2;
 ...
 EXIT WHEN condition;
END LOOP;

-- WHILE[v
WHILE condition LOOP
 statement1;
 statement2;
 ...
END LOOP;

-- [vуxlXgĂFOR[v
<< outer_loop >>  -- loop label or name
FOR x IN y..z LOOP
 outer_statement1;
 << inner_loop >>
 LOOP
  inner_statement1;
  inner_statement2;
  EXIT outer_loop WHEN condition1;
  EXIT inner_loop WHEN condition2;
 END LOOP inner_loop;
 outer_statement3;
 ...
END LOOP outer_loop;
...


p.68
BEGIN
 ...
 IF ... THEN
  GOTO section_1;
 END IF;
 ...
 << section_1 >>
 DELETE FROM sales.parts WHERE ... ;
 ... 


p.69
DECLARE
 current_part sales.parts%ROWTYPE;
BEGIN
 SELECT * INTO current_part
  FROM sales.parts
  WHERE id = 6;
...


DECLARE
 CURSOR parts_cursor IS
  SELECT * FROM sales.parts;
 current_part sales.parts%ROWTYPE;
BEGIN
 OPEN parts_cursor;
 LOOP
  FETCH parts_cursor INTO current_part;
  ... other statements ...
 END LOOP;
 CLOSE parts_cursor;
...


p.70
DECLARE
 CURSOR parts_cursor IS
  SELECT * FROM sales.parts;
BEGIN
 FOR current_part IN parts_cursor LOOP
  ... other statements ...
 END LOOP;
...


DECLARE
 CURSOR customers_cursor (state_id CHAR) IS
  SELECT * FROM sales.customers
   WHERE state = state_id;
BEGIN
 FOR current_customer IN customers_cursor('CA') LOOP
  ... other statements ...
 END LOOP;
...


WHILE parts_cursor%FOUND LOOP
 FETCH parts_cursor INTO current_part;
 ... other statements ...
END LOOP;


p.71
BEGIN
 FOR current_customer IN customers_cursor('CA') LOOP
  IF ... THEN
   DELETE FROM sales.customers
    WHERE CURRENT OF customers_cursor;
  END IF;
 END LOOP;
...


DECLARE
 TYPE cursor_type IS REF CURSOR;
 customers_cursorv Cursor_Type;
BEGIN
 OPEN customers_cursorv FOR
  SELECT id, last_name, first_name, phone FROM sales.customers;
 WHILE customers_cursorv%FOUND LOOP
  IF ... THEN
  ... other statements ...
  END IF;
  ... other statements ...
 END LOOP;
 CLOSE customers_cursorv; 
...


p.72
CREATE OR REPLACE PROCEDURE utilities.drop_table (
 schema_name IN OUT VARCHAR2,
 table_name IN OUT VARCHAR2
)
IS
 cursor_id INTEGER;
 return_value INTEGER;
 command_string VARCHAR2(250);
BEGIN
 command_string := 'DROP TABLE '|| schema_name||'.'||table_name;
 cursor_id := dbms_sql.open_cursor;
 dbms_sql.parse(cursor_id, command_string, dbms_sql.v7);
 return_value := dbms_sql.execute(cursor_id);
 dbms_sql.close_cursor(cursor_id);
END drop_table;


p.73-74
DECLARE
 invalid_part EXCEPTION;
 insufficient_privileges EXCEPTION;
 PRAGMA EXCEPTION_INIT (insufficient_privileges, -1031);
 err_num INTEGER;
 err_msg VARCHAR2(2000);
 part_num INTEGER;
BEGIN
 SELECT ... INTO ... FROM ... ;
 UPDATE sales.parts
  SET unit_price = 20.00
  WHERE id = 6;
 IF SQL%NOTFOUND THEN  -- CHECK FOR USER-DEFINED EXCEPTION
  RAISE invalid_part;
 END IF;
EXCEPTION
 WHEN no_data_found THEN
  raise_application_error(-20001, 'No rows found');
 WHEN too_many_rows THEN
  raise_application_error(-20002, 'Too many rows found');
 WHEN invalid_part THEN
  raise_application_error(-20003, 'Invalid Part ID');
 WHEN insufficient_privileges THEN
  raise_application_error(-20004, 'Insufficient privileges to update table');
 WHEN OTHERS THEN
  err_num := SQLCODE;
  err_msg := SUBSTR(SQLERRM, 1, 100);
  raise_application_error(-20000, err_num ||' '||err_msg);

...


p.76
CREATE OR REPLACE FUNCTION sales.get_customer_id (
 last IN VARCHAR2,
 first IN VARCHAR2
 )
RETURN INTEGER IS
 cust_id INTEGER;
BEGIN
 SELECT id INTO cust_id
  FROM sales.customers
  WHERE last_name = last
  AND first_name = first;
 RETURN cust_id;
EXCEPTION
 WHEN OTHERS THEN
  RETURN NULL;
 END get_customer_id;


p.77
DECLARE
 cur_cust_id INTEGER;
 cur_cust_last VARCHAR2(100);
 cur_cust_first VARCHAR2(100);
BEGIN
 ...
 cur_cust_id := sales.get_customer_id (cur_cust_last, cur_cust_first);
 ...


DELETE FROM sales.orders
 WHERE cust_id = sales.get_customer_id ('Ellison','Lawrence');


p.78
CREATE OR REPLACE PACKAGE sales.part_mgmt IS
---------------------------------------------------------
-- O[oȃ^Cvƕϐ
 TYPE parts_type IS REF CURSOR RETURN sales.parts%ROWTYPE;
 current_part sales.parts%ROWTYPE;
---------------------------------------------------------
-- vV[Wƃt@NV
-- insert_part́APARTS\ɐVi}܂B
-- update_part_unitpricéAỉiXV܂B
-- update_part_descriptiońAi̐XV܂B
-- delete_part́Ai폜܂B
-- get_part_id́AiIDԍԂ܂B
---------------------------------------------------------
 PROCEDURE insert_part ( part_record sales.parts%ROWTYPE);
 PROCEDURE update_part_unitprice (part_id IN INTEGER, new_price IN NUMBER);
 PROCEDURE update_part_description (part_id IN INTEGER, new_desc IN NUMBER);
 PROCEDURE delete_part (part_id IN INTEGER);
 FUNCTION get_part_id (part_desc IN VARCHAR2) RETURN INTEGER;
END part_mgmt;

CREATE OR REPLACE PACKAGE BODY sales.part_mgmt IS
---------------------------------------------------------
 PROCEDURE insert_part (part_record sales.parts%ROWTYPE) IS
  dup_primary_key EXCEPTION;
  PRAGMA EXCEPTION_INIT (dup_primary_key, -1);
 BEGIN
  INSERT INTO sales.parts
   VALUES (part_record.id, part_record.unit_price, part_record.description);
 EXCEPTION
  WHEN dup_primary_key THEN
   raise_application_error (-20001, 'Duplicate part ID');
  WHEN OTHERS THEN
   raise_application_error (-20000, 'Undefined exception');
 END insert_part;
---------------------------------------------------------
... other package procedure and function definitions ...
END part_mgmt;


p.79
DECLARE
BEGIN
-- ́̕AO[opbP[Wϐ܂B
 SELECT * INTO sales.part_mgmt.current_part
  FROM sales.parts
  WHERE id = 3;
-- ́̕AINSERT_PARTpbP[WvV[Wďo܂B
 sales.part_mgmt.insert_part(3, 500.00, 'Network Computer');
 ... 


p.80
CREATE OR REPLACE TRIGGER sales.parts_log
AFTER INSERT OR UPDATE OR DELETE ON sales.parts
DECLARE
 stmt_type CHAR(1);
BEGIN
 IF INSERTING
  THEN stmt_type := 'I';
 ELSIF UPDATING
  THEN stmt_type := 'U';
 ELSE
  stmt_type := 'D';
 END IF;
 INSERT INTO sales.part_change_log
  VALUES (stmt_type, USER);
END parts_log;


p.81
CREATE OR REPLACE TRIGGER sales.parts_log
BEFORE INSERT OR UPDATE OR DELETE ON sales.parts
FOR EACH ROW
DECLARE
 stmt_type CHAR(1);
BEGIN
 IF INSERTING THEN
  stmt_type := 'I';
 ELSIF UPDATING THEN
  stmt_type := 'U';
 ELSE
  stmt_type := 'D';
 END IF;
 INSERT INTO sales.part_change_log
  VALUES (
   :new.id, :old.id,
   :new.unit_price, :old.unit_price,
   :new.description, :old.description,
   stmt_type,
   USER
  );
END parts_log;


p.83
CREATE LIBRARY external.odbc as 'c:\windows\system\odbc.dll';

CREATE OR REPLACE FUNCTION external.sql_exec_direct (
-- ODBCgĔCӂ̕s
  sql_handle BINARY_INTEGER,
  sql_statement VARCHAR2(2000),
  sql_length INTEGER )
RETURN VARCHAR2 AS EXTERNAL
   LIBRARY external.odbc
   NAME SQLExecDirect
   LANGUAGE C;


DECLARE
 return_code VARCHAR2(2000);
 stmt VARCHAR2(2000) := 'DELETE FROM access.customers';
BEGIN
 return_code := external.sql_exec_direct(1, stmt, LENGTH(stmt));
 ...



Chapter5
p.87
CREATE OR REPLACE TYPE sales.part_type AS OBJECT (
 id INTEGER,
 description VARCHAR2(50),
 on_hand INTEGER,
 reorder_point INTEGER,
 MEMBER FUNCTION part_id (descr IN VARCHAR2) RETURN INTEGER,
 MEMBER FUNCTION parts_on_hand (part_id IN INTEGER) RETURN INTEGER,
 MEMBER PROCEDURE order_part (part_id IN INTEGER, quantity IN INTEGER),
 MEMBER PROCEDURE return_part (part_id IN INTEGER, quantity IN INTEGER)
);


p.92
CREATE OR REPLACE TYPE pub.address_type AS OBJECT (
 street1 VARCHAR2(50),
 street2 VARCHAR2(50),
 city VARCHAR2(50),
 state VARCHAR2(25),
 zipcode VARCHAR2(10),
 country VARCHAR2(50));


p.93
CREATE TABLE sales.customers (
 id INTEGER PRIMARY KEY,
 last_name VARCHAR2(50),
 first_name VARCHAR2(50),
 company_name VARCHAR2(50),
 address pub.Address_Type,
 ...


CREATE OR REPLACE PROCEDURE sales.new_customer (
 custid IN INTEGER,
 last IN VARCHAR2,
 first IN VARCHAR2,
 address IN pub.Address_Type)
BEGIN
...


SELECT
  id, last_name, first_name,
  address.street1, address.street2, address.city,
  address.state, address.zipcode, address.country
 FROM sales.customers;


ID LAST_NAME               FIRST_NAME            ADDRESS.STREET1     ...
-- ----------------------- --------------------- ----------------    ...
 1  Ellison                 Lawrence             500 Oracle Parkway  ...


p.94
UPDATE sales.customers
 SET address.zipcode = '94065'
 WHERE id = 1;


INSERT INTO sales.customers VALUES (
 1,'Ellison','Lawrence','Oracle Corporation',
 pub.Address_Type(
  '500 Oracle Parkway', 'Box 659510',
  'Redwood Shores','CA','95045','USA'));


p.94-95
CREATE OR REPLACE TYPE sales.item_type AS OBJECT (
 item_id INTEGER,
 quantity INTEGER );

CREATE OR REPLACE TYPE sales.item_list AS TABLE OF sales.Item_Type;

CREATE TABLE sales.orders (
 id INTEGER PRIMARY KEY,
 order_date DATE,
 ship_date DATE,
 line_items sales.Item_List )
 NESTED TABLE line_items STORE AS items;


p.95
INSERT INTO sales.orders VALUES (
 1, SYSDATE, NULL,
 sales.Item_List(
 sales.Item_Type(1, 22),
 sales.Item_Type(2,  100) ) );


-- 1̍sڂɐVs}
INSERT INTO THE(SELECT line_items FROM sales.orders WHERE id = 1)
 VALUES (3, 200);


p.96
-- 1̍sڂIDƐʂI
SELECT item_id, quantity
 FROM THE (SELECT line_items FROM sales.orders WHERE id = 1)
 ORDER BY item_id;

ITEM_ID    QUANTITY  
---------- ----------
         1         22
         2        100
         3        200


-- 1ID3̍sڂ폜
DELETE THE(SELECT line_items FROM sales.orders WHERE id = 1) o
 WHERE o.item_id = 3;


p.96-97
-- CUSTOMERSIuWFNg\쐬镶
CREATE OR REPLACE TYPE sales.customer_type AS OBJECT (
 id INTEGER,
 last_name VARCHAR2(50),
 first_name VARCHAR2(50),
 company_name VARCHAR2(50),
 address pub.Address_Type);

CREATE TABLE sales.customers OF sales.Customer_Type
 (id PRIMARY KEY);

-- PARTSIuWFNg\쐬镶
CREATE OR REPLACE TYPE sales.part_type AS OBJECT (
 id INTEGER,
 description VARCHAR2(50),
 unit_price NUMBER(10,2),
 on_hand INTEGER,
 reorder_point INTEGER);

CREATE TABLE sales.parts OF sales.Part_Type
 (id PRIMARY KEY);

-- ORDERSIuWFNg\
-- lXgITEMS\Ƌɍ쐬镶
CREATE OR REPLACE TYPE sales.item_type AS OBJECT (
 item_id INTEGER,
 part REF sales.Part_Type,
 quantity INTEGER);

CREATE OR REPLACE TYPE sales.item_list AS TABLE OF sales.Item_Type;

CREATE OR REPLACE TYPE sales.order_type AS OBJECT (
 id INTEGER,
 customer REF sales.Customer_Type,
 order_date DATE,
 ship_date DATE,
 line_items sales.Item_List );

CREATE TABLE sales.orders OF sales.Order_Type
 (id PRIMARY KEY)
 NESTED TABLE line_items STORE AS items;


p.98
INSERT INTO sales.parts
 VALUES (sales.Part_Type(1,'Pentium 200 CPU', 250.00, 1000, 300));

INSERT INTO sales.customers
 VALUES (sales.Customer_Type(1,'Ellison','Lawrence','Oracle Corporation',
  pub.Address_Type('500 Oracle Parkway', 'Box 659510',
  'Redwood Shores','CA','95045','USA')));


INSERT INTO sales.orders
 SELECT 1, REF(c), SYSDATE, NULL, sales.Item_List()
  FROM sales.customers c
  WHERE id = 1;


INSERT INTO THE(SELECT o.line_items FROM orders o WHERE o.id = 1)
 SELECT 1, REF(p), 20 FROM parts p 
  WHERE id = 2;

INSERT INTO THE(SELECT o.line_items FROM orders o WHERE o.id = 1)
 SELECT 2, REF(p), 10 FROM parts p 
  WHERE id = 11;

...


p.98-99
DECLARE
 -- OIDQƂϐ錾
 custoid REF sales.Customer_Type;
 partoid REF sales.Part_Type;
BEGIN
 -- Larry EllisonOIDCUSTOIDɊĂ
 SELECT REF(c) INTO custoid FROM sales.customers c
  WHERE c.last_name = 'Ellison'
  AND c.first_name = 'Lawrence';

-- Pentium 200OIDPARTOIDɊĂ
 SELECT REF(p) INTO partoid FROM sales.parts p
  WHERE p.description = 'Pentium 200 CPU';

-- CUSTOIDɐV}
-- ɂPARTOIDs1s܂߂
-- VOIDORDOIDɊĂ
 INSERT INTO sales.orders
  VALUES (sales.Order_Type (1, custoid, SYSDATE, NULL,
   sales.Item_List(sales.Item_Type(1, partoid, 50) ) ) );
EXCEPTION
 WHEN NO_DATA_FOUND THEN
  raise_application_error(-20000,'No data found');
END;


p.99
SELECT o.id, o.customer.company_name
 FROM sales.orders o;

ID         CUSTOMER.COMPANY_NAME
---------- -------------------------
         1 Oracle Corporation


SELECT o.id, c.company_name
 FROM sales.orders o, sales.customers c
 WHERE o.cust_id = c.id;



p.100
SELECT o.item_id, o.part.description, o.quantity
 FROM THE(SELECT line_items FROM sales.orders WHERE id = 1) o;

ITEM_ID    PART.DESCRIPTION      QUANTITY
---------- --------------------- ----------
         1 Pentium 200 CPU               50


SELECT * FROM user_dependencies
 ORDER BY name, type;

NAME      TYPE   REFERENCED_OWNER REFERENCED_NAME  REFERENCED_TYPE
--------  ------ ---------------- ---------------- ---------------
CUSTOMERS TABLE  PUB              ADDRESS_TYPE     TYPE
CUSTOMERS TABLE  SALES            CUSTOMER_TYPE    TYPE
...


p.102
CREATE OR REPLACE TYPE sales.order_type AS OBJECT (
 id INTEGER,
 customer REF sales.Customer_Type,
 order_date DATE,
 ship_date DATE,
 line_items sales.Item_List,
 MEMBER FUNCTION order_total RETURN NUMBER,
 PRAGMA RESTRICT_REFERENCES(order_total, WNDS, WNPS) );

CREATE TABLE sales.orders OF sales.Order_Type
 (id PRIMARY KEY)
 NESTED TABLE line_items STORE AS items;


CREATE OR REPLACE TYPE BODY sales.order_type (
MEMBER FUNCTION order_total RETURN NUMBER IS
 return_value NUMBER;
BEGIN
 SELECT SUM(l.quantity * l.part.unit_price) INTO return_value
  FROM THE(SELECT o.line_items FROM sales.orders o WHERE o.id = SELF.id) l;
 RETURN return_value;
END order_total;
);


p.103
SELECT o.order_total() FROM sales.orders o WHERE id = 1;

O.ORDER_TOTAL
-------------
         5000


SELECT SUM(i.quantity * p.unit_price)
 FROM sales.items i, sales.parts p
 WHERE i.order_id = 1
 AND i.part_id = p.id;


SELECT c.company_name, c.address.zipcode FROM sales.customers c
 ORDER BY c.address;

ORA-22950: cannot ORDER objects without MAP or ORDER method


p.104
CREATE OR REPLACE TYPE pub.address_type AS OBJECT (
 street1 VARCHAR2(50),
 street2 VARCHAR2(50),
 city VARCHAR2(50),
 state VARCHAR2(25),
 zipcode VARCHAR2(10),
 country VARCHAR2(50),
 MAP MEMBER FUNCTION address_map RETURN VARCHAR2
);

CREATE OR REPLACE TYPE BODY address_type (
MAP MEMBER FUNCTION address_map RETURN VARCHAR2 IS
BEGIN
 RETURN zipcode || city || street1;
END address_map;
);


SELECT c.company_name, c.address.zipcode FROM sales.customers c
 ORDER BY c.address;

COMPANY_NAME              ADDRESS.ZIPCODE
------------------------- -----------------
Oracle Corporation        95045
Microsoft                 98052
...


p.105
CREATE OR REPLACE TYPE pub.address_type AS OBJECT (
 street1 VARCHAR2(50),
 street2 VARCHAR2(50),
 city VARCHAR2(50),
 state VARCHAR2(25),
 zipcode VARCHAR2(10),
 country VARCHAR2(50),
 ORDER MEMBER FUNCTION address_map (other address_type)
  RETURN INTEGER
);
CREATE OR REPLACE TYPE BODY pub.address_type (
ORDER MEMBER FUNCTION address_map (other address_type)
  RETURN INTEGER IS
 self_address VARCHAR2(150) := self.zipcode||self.city||self.street1;
 other_address VARCHAR2(150) := other.zipcode||other.city||other.street1;
BEGIN
 IF self_address < other_address THEN
  RETURN -1;
 ELSIF self_address > other_address THEN
  RETURN 1;
 ELSE
  RETURN 0;
 END IF;
END address_map; 
);


p.106
SELECT c.company_name, c.address.zipcode FROM sales.customers c
 ORDER BY c.address;

COMPANY_NAME              ADDRESS.ZIPCODE
------------------------- -----------------
Oracle Corporation        95045
Microsoft                 98052
...


CREATE OR REPLACE VIEW sales.cust OF sales.customer_type AS
 SELECT * FROM sales.customers;


p.107
CREATE OR REPLACE TYPE sales.customer_order_type AS OBJECT (
 order_count INTEGER,
 company VARCHAR2(50) );


CREATE OR REPLACE VIEW sales.customer_orders OF sales.customer_order_type
 WITH OBJECT OID (company) AS
 SELECT COUNT(o.id), o.customer.company_name
  FROM sales.orders o
  GROUP BY o.customer.company_name;


SELECT * FROM customer_orders;

ORDER_COUNT  COMPANY 
------------ --------------------------------------------------
          31 Oracle Corporation
...


p.108
CREATE TABLE sales.customers (
 id INTEGER PRIMARY KEY,
 last_name VARCHAR2(50),
 first_name VARCHAR2(50),
 company_name VARCHAR2(50),
 street1 VARCHAR2(50),
 street2 VARCHAR2(50),
 city VARCHAR2(50),
 state VARCHAR2(25),
 zipcode VARCHAR2(10),
 country VARCHAR2(50));


p.109
INSERT INTO sales.customers
 VALUES (1,'Ellison','Lawrence','Oracle Corporation',
  '500 Oracle Parkway','Box 659511','Redwood Shores',             
  'CA','95045','USA');

UPDATE sales.customers
 SET street2 = 'Box 659510'
 WHERE id = 1;


INSERT INTO sales.customers
 VALUES (sales.Customer_Type(1,'Ellison','Lawrence','Oracle Corporation',
  pub.Address_Type('500 Oracle Parkway', 'Box 659511',
  'Redwood Shores','CA','95045','USA')));

SELECT c.address.street1, c.address.street2, c.address.city
 FROM sales.customers c;

UPDATE sales.customers c
 SET c.address.street2 = 'Box 659510'
 WHERE c.id = 1;


CREATE OR REPLACE VIEW cust (id, last, first, company, address) AS
 SELECT id, last_name, first_name, company_name,
  pub.address_type(street1, street2, city, state, zipcode, country)
 FROM customers;


SELECT c.id, c.address.zipcode
 FROM sales.cust c;

ID         ADDRESS.ZIPCODE
---------- ----------------
         1 95045
...


p.110
CREATE OR REPLACE TYPE sales.customer_type AS OBJECT (
 id INTEGER,
 last_name VARCHAR2(50),
 first_name VARCHAR2(50),
 company_name VARCHAR2(50),
 address pub.Address_Type);

CREATE OR REPLACE VIEW sales.cust OF sales.customer_type
 WITH OBJECT OID (id) AS
 SELECT id, last_name, first_name, company_name,
  pub.address_type(street1, street2, city, state, zipcode, country)
 FROM sales.customers;


CREATE OR REPLACE VIEW sales.part OF sales.Part_Type
 WITH OBJECT OID(id) AS
 SELECT * FROM sales.parts;

CREATE OR REPLACE VIEW sales.ord OF sales.Order_Type
 WITH OBJECT OID (id) AS
 SELECT id, MAKE_REF(sales.cust, cust_id), order_date, ship_date,
  CAST(MULTISET(SELECT id, MAKE_REF(sales.part, id), quantity
   FROM sales.items l
   WHERE l.order_id = o.id)
   AS sales.Item_List)
  FROM sales.orders o;


p.111
SELECT o.id, o.customer.company_name
 FROM sales.orders o;

ID         CUSTOMER.COMPANY_NAME
---------- -------------------------
         1 Oracle Corporation


p.111-112
CREATE OR REPLACE TRIGGER ord_insert_trigger
INSTEAD OF INSERT ON sales.ord
DECLARE
 item_var sales.Item_List;
 i INTEGER;
 cust_var sales.Customer_Type;
 part_var sales.Part_Type;
 part_var_ref REF sales.Part_Type;
BEGIN
 item_var := :new.line_items;
 SELECT DEREF(:new.customer) INTO cust_var FROM dual;
 INSERT INTO sales.orders
  VALUES (:new.id, cust_var.id, :new.order_date, :new.ship_date);
 FOR I IN 1 .. item_var.count LOOP
  part_var_ref := item_var(i).part;
  SELECT DEREF(part_var_ref) INTO part_var FROM dual;
  INSERT INTO sales.items
   VALUES (:new.id, part_var.id, item_var(i).item_id, item_var(i).quantity);
 END LOOP;
END;


p.112
INSERT INTO sales.ord
 SELECT 1, REF(c), SYSDATE, NULL, sales.Item_List()
  FROM sales.cust c
  WHERE id = 1;



Chapter6
p.125
CREATE ROLE order_entry;
GRANT SELECT, INSERT, UPDATE, DELETE ON sales.customers
 TO order_entry;
GRANT SELECT, INSERT, UPDATE, DELETE ON sales.orders
 TO order_entry;
GRANT SELECT, INSERT, UPDATE, DELETE ON sales.items
 TO order_entry;
GRANT SELECT, UPDATE ON sales.parts
 TO order_entry;
GRANT order_entry
 TO ssmith, tbrown, ptate;


p.129
-- ̊č́A[U[SROGERSJGIBBS
-- SELECT ANY TABLEVXegpčs
-- ׂĂ̌ʁiꍇ̂݁jč܂B
-- OracléAč𖞂ꍇ́A1x̃ZbVɂ
-- 1xčR[h𐶐܂B
AUDIT SELECT ANY TABLE
 BY srogers, jgibbs
 BY SESSION
 WHENEVER NOT SUCCESSFUL;
-- ̊č́ASALES.CUSTOMERS\^[QbgƂDELETE
-- ׂĂ̌ʁi܂͕sjč܂B 
-- OracléAč𖞂ꍇ
-- AčR[h𐶐܂B
AUDIT SELECT ON sales.customers
 BY ACCESS;


NOAUDIT SELECT ON sales.customers;



Chapter7
p.138
ALTER TABLESPACE rbseg
 COALESCE;


p.150
CREATE PUBLIC ROLLBACK SEGMENT rbseg_10
 TABLESPACE rbseg;


CREATE TABLE sales.customers
 ( ... column specifications ... )
 STORAGE (
  INITIAL 500K
  NEXT 500K
  MINEXTENTS 1
  MAXEXTENTS 10
  PCTINCREASE 50  );


p.151
CREATE PUBLIC ROLLBACK SEGMENT rbseg_11
 TABLESPACE rbseg
 STORAGE (
  INITIAL 100K
  NEXT 100K
  MINEXTENTS 3
  MAXEXTENTS 100
  OPTIMAL 310K );


p.152
CREATE INDEX sales.customer_name
 ON sales.customers (lastname, firstname)
 STORAGE (
 ... other storage options ...
  FREELIST GROUPS 2
  FREELISTS 2  );


p.153
CREATE TABLE sales.orders
 ( ... column specifications ... )
 PCTFREE 20
 PCTUSED 60 ;


p.155
ALTER TABLESPACE sales_data_1
 DEFAULT STORAGE (
  INITIAL 100K
  NEXT 100K
  MINEXTENTS 3
  MAXEXTENTS 100
  PCTINCREASE 0 );


p.158
CREATE TABLE sales.usa_customers
( id NUMBER(5) PRIMARY KEY,
  lastname VARCHAR2(50) NOT NULL,
  firstname VARCHAR2(50) NOT NULL,
  address VARCHAR2(100),
  city VARCHAR2(50),
  state CHAR(2),
  zipcode VARCHAR2(15),
  phone VARCHAR2(15),
  fax VARCHAR2(15),
  email VARCHAR2(100) )
  PARTITION BY RANGE ( state )
   ( PARTITION p1  VALUES LESS THAN ('H')      -- AL,AK,AZ,AR,CA,CO,CT,DC,DE,FL,GA
       TABLESPACE data01,
     PARTITION p2  VALUES LESS THAN ('MI')     -- HI,IA,ID,IL,IN,KS,KY,LA,MA,MD,ME
       TABLESPACE data02,
     PARTITION p3  VALUES LESS THAN ('NM')     -- MI,MN,MS,MO,MT,NC,ND,NE,NH,NJ
       TABLESPACE data03,
     PARTITION p4  VALUES LESS THAN ('S')      -- NM,NV,NY,OH,OK,OR,PA,PR,RI
       TABLESPACE data04,
     PARTITION p5  VALUES LESS THAN (MAXVALUE) -- SC,SD,TN,TX,UT,VA,VT,WA,WI,WV,WY
       TABLESPACE data05 );


p.159
CREATE INDEX usa_customers_state
  ON usa_customers ( state )
  PARTITION BY RANGE ( state )
   ( PARTITION p1  VALUES LESS THAN ('H')      -- AL,AK,AZ,AR,CA,CO,CT,DC,DE,FL,GA
       TABLESPACE data01,
     PARTITION p2  VALUES LESS THAN ('MI')     -- HI,IA,ID,IL,IN,KS,KY,LA,MA,MD,ME
       TABLESPACE data02,
     PARTITION p3  VALUES LESS THAN ('NM')     -- MI,MN,MS,MO,MT,NC,ND,NE,NH,NJ
       TABLESPACE data03,
     PARTITION p4  VALUES LESS THAN ('S')      -- NM,NV,NY,OH,OK,OR,PA,PR,RI
       TABLESPACE data04,
     PARTITION p5  VALUES LESS THAN (MAXVALUE) -- SC,SD,TN,TX,UT,VA,VT,WA,WI,WV,WY
       TABLESPACE data05 );


p.162
SELECT * FROM usa_customers PARTITION (p1);


CREATE VIEW v1 AS SELECT * FROM t1 PARTITION (p1)
  SELECT * FROM v1;



Chapter9
p.198
-- rxserializableŃgUNVJn
SET TRANSACTION
 ISOLATION LEVEL SERIALIZABLE;
... other transaction statements ...
COMMIT;
-- rxread committedŃgUNVJn
SET TRANSACTION
 ISOLATION LEVEL READ COMMITTED;
... other transaction statements ...
COMMIT;


p.200
UPDATE sales.customers
 SET ...
 WHERE last_name = 'Ellison' AND first_name = 'Lawrence';


p.201
SELECT * FROM sales.customers
 WHERE zipcode = 95000
 FOR UPDATE
 NOWAIT;


LOCK TABLE customers
 IN EXCLUSIVE MODE
 NOWAIT;


p.208
SET TRANSACTION READ ONLY;



Chapter10
p.217
TARGET "system/manager@sales" RCVCAT "rman/rman@hq";

REGISTER DATABASE;

CREATE JOB bkup_full_01 {
 ALLOCATE CHANNEL t1 NAME "tape1";
 ALLOCATE CHANNEL t2 NAME "tape2";
 BACKUP FULL;
}

EXECUTE JOB bkup_full_01;


p.228
CREATE JOB bkup_full_01 {
   ALLOCATE CHANNEL t1 NAME "tape1";
   ALLOCATE CHANNEL t2 NAME "tape2";
 BACKUP FULL;
}



Chapter11
p.248
SELECT * FROM sales.parts@east.compworld;


p.249
CREATE DATABASE LINK east.compworld ... ;


p.250
UPDATE sales.parts@east.compworld
 SET unit_price = 100.50
 WHERE id = 1;


CREATE DATABASE LINK east.compworld
 CONNECT sales IDENTIFIED BY person;


p.253
SELECT o.id, c.company_name
 FROM sales.orders@west.compworld o, sales.customers@west.compworld c
 WHERE o.cust_id = c.id;


SELECT o.id, c.company_name
 FROM sales.orders o, sales.customers@west.compworld c
 WHERE o.cust_id = c.id;


UPDATE sales.parts@east.compworld
 SET unit_price = 100.50
 WHERE id = 1;


BEGIN
UPDATE sales.parts@east.compworld
 SET ... ;
UPDATE sales.items
 SET ... ;
END;


SELECT o.order_total() 
 FROM sales.orders@east.compworld o WHERE id = 1;


p.254-255
UPDATE sales.parts@east.compworld
 SET ... 
 WHERE ... ;
UPDATE sales.parts@east.compworld
 SET ... 
 WHERE ... ;
UPDATE sales.parts@east.compworld
 SET ... 
 WHERE ... ;
COMMIT;


p.255
UPDATE sales.parts@east.compworld
 SET ... 
 WHERE ... ;
UPDATE sales.parts@east.compworld
 SET ... 
 WHERE ... ;
UPDATE sales.parts@east.compworld
 SET ... 
 WHERE ... ;
COMMIT;


p.256
CREATE PUBLIC SYNONYM parts
  FOR sales.parts@east.compworld;


SELECT * FROM parts;


CREATE VIEW sales.parts AS
 SELECT * FROM sales.parts@east.compworld;


p.260
CREATE SNAPSHOT sales.parts AS
 SELECT * FROM sales.parts@central.compworld;


p.261
CREATE SNAPSHOT sales.order_items AS
 SELECT i.id lineid, p.description, i.quantity, p.unitprice, i.total
  FROM sales.items@central.compworld i, sales.parts@central.compworld p
  WHERE i.part_id = p.id;








































