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应用行对象，何以是不同的表共享相同的对象，从而降低了内存的占用。