Oracle总结

PL/SQL块结构
DECLARE
/*定义部分*/
BEGIN
/*执行部分*/
EXCEPTION
/*例外处理部分*/
END; /*块结束标记，此处有分号*/
/   -- /*这是命令行中的结束符*/
四大块类型   匿名块、命名块、子程序（过程、函数、包）、触发器
四大变量类型 标量(Scalar)、复合(Composite)、参照(Reference)、LOB(Large Object)
复合（Composite）:PL/SQL记录、PL/SQL表、嵌套表、VARRAY
参照：游标变量(REF CURSOR)、 对象类型变量(REF obj_type)
          LOB:内部LOB(CLOB,BLOB,NCLOB)、外部LOB(BFILE)

DECLARE
v_num NUMBER(6,2); 变量定死了长度，将来无法动态改变，只能手动修改
v_num tbl.num%TYPE; 动态确定新的类型和长度
挺像Delphi:
1 变量赋值 := 
2记录 RECORD
在命令行中写SQL语句时注意：单条结束要用; 模块结束要用/ 才能显示你的查询
不重复显示查询：SELECT DISTINCT * FROM A_TBL
处理NULL：NVL(expr1,expr2) 如果expr1是null则返回expr2;如果不是则返回expr1两者类型要匹配
删除表数据并释放空间：TRUNCATE TABLE
Oracle不允许其他用户读取脏数据（未提交事务），确保数据库数据的读一致性
加锁，确保同一时间对文件的操作只能有一人
Savepoint 用于取消部分事务，事务结束后自动删除所有保存点。
只读事务：SET TRANSACTION READ ONLY;    或者：exec dbms_transaction.read_only
分组函数
MAX、MIN、AVG、SUM、COUNT、VARIANCE(方差)、STDDEV(标准偏差)
GROUP BY用于对查询结果进行分组统计――比如每组最大值
HAVING用于限制分组显示结果――比如最大值小于2800
ROLLUP在原统计结果基础上生成横向小统计
CUBE在原统计结果基础上生成纵向小统计
Oracle的流程控制语句
IF语句
IF      THEN
ELSIF  THEN
ELSE
END IF;
 
CASE语句(9i)
CASE
   WHEN   THEN
ELSE
END CASE;
基本循环
LOOP
   EXIT[WHEN  ];
END LOOP;
WHILE循环
WHILE   LOOP
END LOOP;
FOR       循环
FOR _v in[REVERSE] lo..hi LOOP
END LOOP;

************************************************

游标

************************************************
使用显式游标
定义游标DECLARE
CURSOR cursor_name IS select_statement;
打开游标OPEN
OPEN cursor_name
提取游标FETCH
FETCH cursor_name INTO variable1,variable2,...;
关闭游标CLOSE
CLOSE cursor_name;
显式游标属性
%ISOPEN判断是否已经打开 打开TRUE
%FOUND是否从结果集中提取到了数据 提取到TRUE
%NOTFOUND是否从结果集中提取到了数据 没有提取到TRUE
%ROWCOUNT返回到当前行为止已经提取到的实际行数
参数游标
以实现使用不同参数值多次打开游标时，可以生成不同的结果集
CURSOR cursor_name(parameter_name datatype) IS select_statement;
使用游标更新/删除数据
CURSOR cursor_name(parameter_name datatype) IS select_statement FOR UPDATE [OF column_reference] [NOWAIT];
FOR UPDATE用于在结果集数据上加行共享锁，以防止其他用户再次行执行DML操作
OF确定那些表要加锁
NOWAIT指定执行时不等待锁，如果其他会话已经在被作用行上加锁，则当前会话显示错误提示
UPDATE table_name SET column=.. WHERE CURRENT OF cursor_name;
DELETE table_name WHERE CURRENT OF cursor_name;
游标循环
Oracle会隐含地打开游标，提取游标并关闭游标
FOR record_name IN cursor_name LOOP
    statement1;
    statement2;
    ...
END LOOP;
如果循环时不需要使用任何游标属性，则可直接使用子查询
FOR record_name IN (select_statement) LOOP
    statement1;
    statement2;
    ...
END LOOP;
使用游标变量
定义REF CURSOR类型和游标变量
TYPE ref_type_name IS REF CURSOR [RETURN return_type];
cursor_variable ref_type_name;
打开游标
OPEN cursor_variable FOR select_statement;
提取游标数据
FETCH cursor_variable INTO variable1,variable2,...;
关闭游标CLOSE
CLOSE cursor_variable
开发子程序
开发过程
建立过程
CREATE [OR REPLACE] PROCEDURE procedure_name(argument1 [mode1] datatype1,...)
IS[AS]
PL/SQL Block;
IS或AS用于开始一个PL/SQL块 mode可以是IN,OUT,IN OUT，默认是IN
参数传递方式
位置传递
调用子程序时，按照参数定义的顺序依次为参数指定相应变量或者数值
名称传递
调用子程序是指定参数名，并使用关联符号=>为其提供相应的数值或变量
组合传递
同时使用上述两者
删除过程
DROP PROCEDURE procedure_name;
开发函数
建立函数
CREATE [OR REPLACE] FUNCTION function_name(argument1 [mode1] datatype1,...)
RETURN datatype
IS[AS]
PL/SQL Block;
RETURN指定函数返回值的数据类型，函数体内要至少包含一条RETURN语句
开发包
包用于逻辑组合PL/SQL类型（TABLE类型和RECORD类型），PL/SQL型（游标和游标变量）和PL/SQL子程序（过程和函数）。它由包规范（Packege SPecification）和包体（Package Body）两部分组成。
建立包
建立包规范
包规范用于定义包的公用组件（常量、变量、游标、过程、函数）
CREATE [OR REPALCE] PACKAGE package_name
IS | AS
    public type and item declarations
    subprogram specifications
END package_name;
建立包体
用于实现包规范锁定义的过程和函数
CREATE [OR REPALCE] PACKAGE BODY package_name
IS | AS
    private type and item declarations
    subprogram bodies
END package_name;
调用包组件--包名.组件名
纯度级别(purity level)
定义
PRGMA RESTRICT_REFERENCES (function_name,WNDS,[WNPS][,RNDS][,RNPS]);
WNDS--用于限制函数不能修改数据库数据（禁止执行DML）
WNPS--用于限制函数不能修改包变量（不能给包变量赋值）
RNDS--用于限制函数不能读取数据库数据（禁止执行SELECT操作）
RNPS--用于限制函数不能读取包变量（不能将包变量赋值给其他变量）
动态SQL的使用

Oracle实际上比SQL Server好一些，但绝对没有传说中那么强。这是这些天一识Oracle庐山真面目的感受。看来好多事情多如此，听的要比见到的完美。接着第一句说，Oracle的动态SQL就很棒，我专门学习了一下。记录如下。

先说说动态SQL是什么，看这句熟悉的：
select * from a_table where a_variable=a_declarevalue;
再写句动态的：
select * from a_table where a_variable=:a_dynamicvalue;
两句的区别很明显，后者多一个占位符，这个以冒号开始的变量可以灵活地执行不同条件的where语句。
这是动态SQL语句的优势，接下来的功能就是它的独门功夫了--执行DDL,DCL语句。

动态SQL的执行
1 EXECUTE IMMEDIATE语句
EXECUTE IMMEDIATE dynamic_string
[INTO {define_variable[,define_variable]...| record}]
[USING [IN | OUT | IN OUT] bind_argument[,[IN | OUT / IN OUT] bind_argumnet]...]
[{RETURNING | RETURN} INTO bing_argument[,bind_argument]...];

下面是它的使用

处理DDL操作(CREATE,ALTER,DROP)
CREATE OR REPLACE PROCEDURE drop_table(table_name VARCHAR2)
IS
  Sql_statemet VARCHAR2(100);
BEGIN
  Sql_statement:='DROP TABLE' || table_name;
  EXECUTE IMMEDIATE sql_statement;
END;
/
建立过程drop_table后，调用如下：
SQL> exec drop_table(‘worker')
处理DCL操作(GRANT REVOKE)
SQL> conn system/manager
CREATE OR REPLACE PROCEDURE grant_sys_priv(priv VARCHAR2,username VARCHAR2)
IS
  Sql_stat VARCHAR2(100);
BEGIN
  Sql_stat:='GRANT " || priv|| ' TO '|| username;
EXECUTE IMMEDIATE sql_stat;
END;
/
调用
SQL> exec grant_sys_priv(‘CREATE SESSION','SCOTT')

处理DML操作(INSERT UPDATE DELETE)
如果DML语句带有占位符，那么在E I语句中则要带USING子句
如果DML语句带有RETURNING子句，那么E I语句中要带有RETURNINGINTO子句

例子，处理单行查询：
DECLARE
  sql_stat VARCHAR2(100);
  emp_record tbl%ROWTYPE;
BEGIN
  sql-stat:='SELECT * FROM tbl WHERE tblno=:no';
  EXECUTE IMMEDIATE sql_stat INTO emp_record USING &1;
  dbms_output.put_line(emp_record.ename||emp_record.sal);
END;
/

2 使用OPEN-FOR，FETCH  和 CLOSE 语句处理多行查询

动态处理SELECT语句步骤：定义游标->打开游标->循环提取数据->关闭游标
定义：
TYPE cursortype IS REF CURSOR;
cursor_variable cursortype;
打开：
OPEN cursor_variable FOR dynamic_string
[USING bind_argument[,bing_argument]...];
提取：
FETCH cursor_variable INTO {var1[,var2]...| recor_var};
关闭：
CLOSE cursor_variable;

显示特定部门雇员姓名和工资
DECLARE
  TYPE empcurtype IS REF CURSOR;
  emp_cs empcurtype;
  emp_record emptable%ROWTYPE;
  sql_stat VARCHAR2(100);
BEGIN
  sql_stat:='select * from emptable where deptno=:dno';
  OPEN emp_cs FOR sql_stat USING &dno;
  LOOP
    FETCH emp_cs INTO emp_record;
    EXIT WHEN emp_cs%NOTFOUND;
    dbms_output.put_line(emp_record.ename||emp_record.sal);
  END LOOP;
  CLOSE emp_cs; 
END;
/
3 使用批量动态SQL(9i)
BULK子句可以加快批量数据的处理速度。有三种语句支持BULK子句的方法。
1 使用EXECUTE IMMEDIATE，语法为：
EXECUTE IMMEDIATE dynamic_string
[BULK COLLECT INTO define_variable[,define_variable ...]]
[USING [IN | OUT | IN OUT] bind_argument[,[IN | OUT / IN OUT] bind_argumnet]...]
[{RETURNING | RETURN}
BULK COLLECT INTO return_variable[,return_variable...]]; 
用于DML处理多行子句，例子：为某部门所有员工增加%比的工资
DECLARE ......
BEGIN
  sql_stat:='UPDATE emptbl SET sal=sal*(1+:percent/100)' ||
                'WHERE deptno=:dno' ||
                'RETURNING ename,sal INTO :name,:salary';

  EXECUTE IMMEDIATE sql_stat USING &percent,&dno
    RETURNING BULK COLLECT INTO ename_table,sal_table;

  FOR i IN 1.ename_table.COUNT LOOP
    dbms_output.put_line( ename_table(i) ||sal_table(i) );
  END LOOP;

END；
/

2 FETCH语句，语法为
FETCH dynamic_cursor
BULK COLLECT INTO define_variable[,dyfine_variable ...];

3 FORALL语句。适用于DML，不适用于动态的SELECT语句。FORALL语句要与E I 结合使用。语法为
FORALL index IN lower bound..upper bound
EXECUTE IMMEDIATE dynamic_string
USING bind_argument | bind_argumnet(index)
[,bind_argument | bind_argumnet(index)]...
[{RETURNING | RETURN} BULK COLLECT
INTO bind_argument[,bind_argument...]];
对象类型
看此章节之前，让我联想起JDO。如果Oracle能把关系型数据库的操作以对象为单位实现，那该有多好。不知这一章将带给我什么。
对象组件包括属性和方法
属性是Attribute的翻译。通常在高级语言里，属性通常是Property的翻译，而Attribute被译为特征。在Oracle里，Attribute和java里的Property是对应的。即拥有描述对象所具有的属性。
 方法（Method）用于实现对象所执行的操作。
1构造方法在9i后，允许重载了.
CONSTRUCTOR FUNCTION fangfaming(somevariable...)
RETURN SELF AS RESULT
这里的fangfaming必须是定义的类名，不然还叫什么"构造子"了

2MEMBER方法就是高级语言里的实例方法,
MEMBER PROCEDURE fangfa1 ()
MEMBER FUNCTION fangfa2() RETURN simple_type
使用：
DECLARE
a_shili a_lei;
BEGIN
a_shili.fangfa1()


3STATIC方法就是类方法，全局方法。
A_lei.staticfangfa();

4MAP方法用于将对象实例映射为标量数值,然后根据该标量类型数据可以排序对象实例。用于比较多个对象实例。

5ORDER方法用于比较两个对象实例的大小

在一个对象类型中，只能定义一个MAP方法或者一个ORDER方法（它们不能同时使用）。
对象类型
包括对象类型规范(Object Type Specification)和对象类型体(Object Type Body)。就是定义和实现部分。对于熟悉delphi的人，也许会认为这样的方式才显得很得体。

建立对象类型规范的语法：
CREATE OR REPLACE TYPE type_name AS OBJECT(
       Attribute1 datatype,[,attribute2 datatype,...],
       [MEMBER|STATIC method1 spec,
MEMBER|STATIC method2 spec,...]);

建立对象类型体的语法：
CREATE OR REPLACE TYPE BODY type_name AS
       MEMBER|STATIC method1 body;
       MEMBER|STATIC method2 body;
...]);

建立对象类型时，至少定义一个属性，可以不定义方法（这是不需要建立对象类型体）。
对象表--就象高级语言里的对象/实例，是对类的实现，分为行对象和列对象
行对象是直接给予对象类型所建立的表
CREATE TABLE table_name OF type_name


列对象是包含多个列的对象表
CREATE TBALE table_name{
       Mynum number(6),
       Mytype type_name,
       Mychar varchar2(10)
);
REF数据类型
通过REF应用行对象，何以是不同的表共享相同的对象，从而降低了内存的占用。

下面是Jdo的相关信息。作者sun2bin

Oracle与JDO
作为JDO专家组的重要成员，同时作为最大的关系数据库厂商的Oracle地位显然非同一般。在JDO规范中，Oracle也可说是立下汗马功劳，很多API的形成，Oracle都提供了很重要的参考意见，最终的投票Oracle也是毫不犹豫。
可是，世间的事总是变化莫测的，就在JDO1.0快出台之时，Oracle收购了TopLink，这一点使Oracle的身份变得特殊而复杂。TopLink是一个商业产品，是以商业利益为目标的，而Oracle也是追求利益最大化的典型商家，这一点与JDO的开放精神背道而驰。因此，我们看到后期Oracle对JDO的不积极态度，甚至在前一阵的JavaOne大会上有人从Oracle的角度非正式地攻击JDO。--《 JDO之前世今生》
对象包装技术，百家争鸣？群魔乱舞？
于是，从规范化开发的原则出发，我们开始写自己的JavaBean来包装数据对象，使数据对象化，避免太多的地方涉及JDBC操作。但一些问题也随之而来：灵活性不够，接口死板，性能低下，这使我一阵苦恼。于是，"君子性非异也，善假于物也"，咱也上网去找点"技术支持"！很快，竟然被我发现了"Castor JDO"，一个专用于数据包装的撞阕榧峁┝薕DMG标准的OQL作为查询语言，方便且容易理解，比SQL好多了。这让我享受了一段时间的"面向对象的数据库开发"的好处，一句话，"效果不错，还实惠！"。
然而，好景不长，Castor一些内在的BUG影响了稳定性，而这个免费产品的更新又太慢，一直未能解决。只好放弃。"执手相看泪眼，竟无语凝噎"！怎么办？要知道，由俭入奢易，由奢入俭难，吃过肉的人，怎能忍受只能吃菜的生活！象《甲方乙方》里面那个一心想吃素的大款还是不多见的。对我们来说，再使用JDBC原始调用似乎难以下咽，再用JavaBean包装又有点返古，于是我又开始了网上的搜寻历程。余秋雨先生有《文化苦旅》，咱这也算是《编程苦旅》了，呵呵，苦笑。
从网上的资料来看，我的这些经历也是很多Java开发同仁的共同经历，无论是国内还是国外，不过从实际情况来看，国外的研究更深入更广泛一些，至少从网上所能找到的资料来说是这样。美国从八十年代起就开始研究面向对象的数据库ODBMS，目前已有一些成形的产品，比如Versant公司的Versant数据库，FastObjects公司的FastObjects t7数据库，以及其它一些相对市场份额小一些的诸如ObjectStore等公司的产品，当然，也不乏一些免费的产品，如Orient等等。总的来说，ODBMS尽管拥有面向对象的优点，但由于历史原因，在与关系数据库RDBMS的竞争中始终处于下风，基于RDBMS的应用还是占绝大多数，因此，出现了Object-Relational映射的一些工具，前面提到的Castor就是近年来出现的一个工具，实际上更早的时候，已经有一些成熟、稳定的商业化产品出现，比如前一阵被Oracle收购的TopLink，被BEA收购的WebGain等等，比较有名气的CocoBase等等。
象TopLink这样的产品我也了解了一下，功能确实强大，性能、稳定性都有优势，然而，其同样强大的价格和古怪的API令我却步。我很担心被锁定在某个产品上面，无法脱身，众所周知，Java给我们的就是一种自由的感觉，自由，永远是那么地吸引人。
出路在哪里？JDO浮现在我眼前。


JDO自1999年起就由一些经常写数据库对象映射层的富有经验的开发人员提出大纲，他们在长期的面向对象开发中进行了大量的数据库方面的处理和对象化包装，终于，多种多样的包装方式引起很多兼容性方面的问题。于是，一些主要的开发团队就联合起来，以SUN为领头羊，制定了JDO规范。它的目标不是取代JDBC或EJB，而是在JDBC的基础上进行包装，同时又可以做EJB的底层（CMP），简化J2EE服务器提供商的工作。JDO主要面向中小型规模的项目，不过随着产品提供商（Vendors）给出越来越多的功能（Feature），比如分布式的同步控制等等，JDO的作用也越来越大。JDO规范在Sun的富有经验的Craig Russel的带领下，经过三年的讨论，在2002年四月形成了第一版。--《如何用JDO开发数据库应用》