7、对象类型继承
 
对象类型实现允许我们创建一个基类型，或叫父类型，这种类型的属性或方法可以被另一个对象类型继承。然后可以创建一个子类型，或叫孩子类型，直接使用继承过来的属性或方法，或者用自己的属性和方法重写父类型的属性或方法。
 
INSTANTIABLE关键字表示我们可以从该类型中实例化或者创建对象实例。
FINAL关键字是指该对象类型下面不再有子类型，没有其他类型会继承该对象类型。
 
下面这个例子说明了继承关系，inventory_obj被book_obj继承。
 
CREATE OR REPLACE TYPE inventory_obj AS OBJECT (
item_id NUMBER (10),
num_in_stock NUMBER (10),
reorder_status VARCHAR2 (20 CHAR),
price NUMBER(10,2),
CONSTRUCTOR FUNCTION inventory_obj (
item_id IN NUMBER,
num_in_stock IN NUMBER,
price IN NUMBER
)
RETURN SELF AS RESULT,
MEMBER PROCEDURE print_inventory,
MEMBER PROCEDURE print_status,
MEMBER PROCEDURE print_price
)
INSTANTIABLE NOT FINAL;
/

CREATE OR REPLACE TYPE BOD ......

7、对象类型继承
 
对象类型实现允许我们创建一个基类型，或叫父类型，这种类型的属性或方法可以被另一个对象类型继承。然后可以创建一个子类型，或叫孩子类型，直接使用继承过来的属性或方法，或者用自己的属性和方法重写父类型的属性或方法。
 
INSTANTIABLE关键字表示我们可以从该类型中实例化或者创建对象实例。
FINAL关键字是指该对象类型下面不再有子类型，没有其他类型会继承该对象类型。
 
下面这个例子说明了继承关系，inventory_obj被book_obj继承。
 
CREATE OR REPLACE TYPE inventory_obj AS OBJECT (
item_id NUMBER (10),
num_in_stock NUMBER (10),
reorder_status VARCHAR2 (20 CHAR),
price NUMBER(10,2),
CONSTRUCTOR FUNCTION inventory_obj (
item_id IN NUMBER,
num_in_stock IN NUMBER,
price IN NUMBER
)
RETURN SELF AS RESULT,
MEMBER PROCEDURE print_inventory,
MEMBER PROCEDURE print_status,
MEMBER PROCEDURE print_price
)
INSTANTIABLE NOT FINAL;
/

CREATE OR REPLACE TYPE BOD ......

--1加内存表
EXEC sp_tableoption '表名','pintable', 'true' 
--2卸载内存表
EXEC sp_tableoption '表名','pintable', 'false' 
--2查询是否有内存表驻留
SELECT * from INFORMATION_SCHEMA.Tables 
WHERE TABLE_TYPE = 'BASE TABLE' 
          AND OBJECTPROPERTY(object_id(TABLE_NAME), 'TableIsPinned') > 0 ......

oracle 方法                                                                 postgreSQL方法
select seqName.nextval from dual                            select nextval('seqname')
select seqName.currval from dual                             select currval('seqname')
long 字段                     &nb ......

oracle 方法                                                                 postgreSQL方法
select seqName.nextval from dual                            select nextval('seqname')
select seqName.currval from dual                             select currval('seqname')
long 字段                     &nb ......

Oracle SQL(partI)
Data manipulation language(DML): select, insert, update, delete, merge.
Data definition language(DDL): create, alter, drop, rename, truncate, comment
Data control language(DCL): grant, revoke
Transaction control: commit, rollback, savepoint
Arithmetic Expressions:
+, -, *, /
1) Arithmetic expressions containing a null value evaluate to null 
Column Alias
1) requires double quotation marks if it contains spaces or special characters, or if it is case-sensitive
e.g.: select last_name as name, commission_pct comm from employees;
e.g.: select last_name  "Name", salary*12 "Annual Salary" from employees 
Alternative Quote(q) Operator
e.g.: select depart_name||' Department'||q'['s Manager is ]'||manager_id as "Department and Manager"
results:
Department and Manager
Administrator Department's Manager is Me
...
Using DESCRIBE to display the table structure
e.g.: describe tb1;
Rules of precedence for operators in an expression
1   Arith ......

Subquery: (single-row subqueries and multi-rows subqueries).
select select_list
from table
where expr operator (select select_list from table);
single-row subqueries operator: =, >, >=, <, <=, <>
e.g.:
1. select department_id, min(salary) from employees group by department_id having min(salary)>(select mn(salary) from employees where department_id = 50)
* min salary in department_id=50
* list department_id, min(salary) group by department_id and min(salary)> (min salary in departmentid=50).
2. select department_id, min(salary) from employees group by department_id having min(salary)>(select min(salary) from employees group by department_id);
* this sentense is wrong. has the subquery operates multi results.
Multi-row subqueires: any, all, in
IN:     equals to any member in the list
any:   1) must be preceded by =, !=, >, <, <=, >=. 2) compares a value to each value in a list or returned by a query. 3) evaluates to ......

constraint Example:
1. grammer:
 create table [schema.]table
      (column datatype [DEFAULT expr]
     [column_constraint], ...
     [table_constraint] [,......]);
2. example of a column_level constraint:
create table employees(employee_id number(6) constraint emp_emp_id_pk primiary key, first_name varchar2(20), ...);
3. example of a table-level constraint:
create table employees(employee_id number(6), first_name varchar2(20), ... job_id varchar2(20) not null, constraint emp_emp_id_pk primary key(employee_id));
4. UNIQUE example
create table employees(employee_id number(6), last_name varchar2(20) not null,..., constraint emp_email_uk UNIQUE(email));
5. FOREIGN KEY constraint
create table employees(employee_id number(6), last_name varchar2(20),... constraint emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments(department_id), constraint emp_email_uk UNIQUE(email));
** foreign key constaint:
* ......