study_note.txt.bak

介绍Oracle PL SQL编程

调用JAVA存储过程
dbms_java.grant_permission( user,
                                'SYS:java.io.FilePermission',
                                p_path,
                                'read'
                              );
-- Call the procedure

==============================
第3章 PL/SQL基础知识
==============================

PL/SQL块分为：
匿名块和命名块
查看命名块行号和内容：
select line || ' ' || text PROCEDURE
from user_source where name = 'COMPILE_ERROR';

查看命名块是否有效：
col object_name format A15
col status format A10
select object_name, status 
from user_objects where object_name = 'COMPILE_ERROR';

执行命名块：
1、exec compiler_error;
2、call compiler_error;
3、begin
     compiler_error;
     end;
     /

编译警告信息：
ALL                     包括所有可用的警告条件和警告信息
PERFORMANCE  仅返回与执行性能相关的警告
INFORMATION   对程序没有任何用的代码
SEVERE               严重问题，代码有逻辑问题
Specific Error      对某个错误信息而言，该警告可能是具体的

修改PLSQL_WARNINGS参数
显示参数值：show parameter PLSQL_WARNINGS
alter system set PLSQL_WARNINGS='ENABLE:PERFORMANCE', 'ENABLE:SEVERE';

编译PROCEDURE BOOK_INS
show errors
19/7     PLW-07202: 绑定类型可能会导致从列类型转换为其它类型
将该警告设为一个错误：
ALTER PROCEDURE BOOK_INS COMPILE PLSQL_WARNINGS='ERROR:07202';
运行这条命令后BOOK_INS变为INVALID，但是再次编译又变为VALID

DBMS_WARNING包：调节编译的日志级别
CALL DBMS_WARNING.SET_WARNING_SETTING_STRING('DISABLE:ALL', 'SESSION');
可以修改PLSQL_WARNING日志级别
CALL DBMS_WARNING.SET_WARNING_SETTING_STRING('ENABLE:ALL', 'SESSION');
SELECT DBMS_WARNING.GET_WARNING_SETTING_STRING() WARNING_LEVEL
FROM dual;
得到日志级别

嵌套块
declare
begin
  begin
  exception1......
  end;
  begin
  exception2.......
  end;
end;
即使嵌套块1发生了exception1也会指向嵌套块2，如果exception1和exception2都在一个嵌套块
中，则发生了exception1则其余代码都不能得到执行。

PL/SQL的语言规则与约定：
1、标识符
标识符是为PL/SQL对象（例如变量和游标）和数据库对象（包括存储过程、存储函数和包）提供命名
标识的。它让我们可以不通过Oracle的一些内部引用，只使用名称就可以引用所需对象。
标识符的命名约束包括：
长度30以内（包括30）
必须以字母打头
可以包括一下字符但不能为首字母：$, #, _, 以及全部数字
不能包含标点符号、空格或连接符号（即-）

用双引号将标识符包围起来以后，就可以使他们包含一些非标准的字符及空格
查看当前版本保留字符
select keyword from v_$reserved_words 
where length = 1 and keyword != 'A'
or keyword = '<<';
查看当前版本所用的全部保留字
select keyword from v_$reserved_words 
where (length > 1 or keyword = 'A')
and     keyword != '<<'
order by keyword;
or keyword = '<<';

字符串
q'[...]'
q'!...!'
q'<...>'
q'%...%'

布尔值：
TRUE, FALSE, NULL

日期/时间
DATE
TIMESTAMP
TIMESTAMP WITH TIMEZONE
TIMESTAMP WITH LOCAL TIMEZONE

PL/SQL的数据类型和Oracle数据库的数据类型不一样
PL/SQL可以分为以下几类：
标量类型，引用类型，复合类型，LOB类型

标量类型（字符/字符串类型，数字类型，布尔类型，日期/时间类型）
字符/字符串类型：
最多能存放32K以内的字符串
1、字符语义
v_string VARCHAR2(10);              能存放10个字节
v_string VARCHAR2(10 CHAR);   能存放10个字符，不管字符是有几个字节组成

2、字符/字符串类型
CHAR, LOGN, LONG RAW, NCHAR, NVARCHAR2, RAW, ROWID, UROWID, VARCHAR, VARCHAR2

3、数字类型
BINARY_DOUBLE, BINARY_FLOAT, BINARY_INTEGER, NUMBER, PLS_INTEGER
NUMBER的最大长度为38位，精度的取值范围：-84~127

日期类型
DATE：年月日时分秒，秒不带小数
TIMESTAMP可以指定秒的精度，最大为9纳秒，6为微秒（默认），3为毫秒，用法：TIMESTAMP(9)
TIMESTAMP WITH TIME ZONE
DECLARE
   v_datetime TIMESTAMP (3) WITH TIME ZONE := SYSTIMESTAMP;
BEGIN
   DBMS_OUTPUT.PUT_LINE(v_datetime);
END;
/
18-12月-07 11.41.45.687 下午 +08:00
秒后面的三位数标识毫秒，+08:00表示比UTC（Uniusal time coordinate，通用时间标准）早8小时

TIMESTAMP WITH LOCAL TIME ZONE
返回的和TIME ZONE类型相同，不过，返回的时间是访问数据库服务器的客户机的本地时间，
与我们客户机上设置的时区相匹配

Interval
PL/SQL有两种形式的INTERVAL：INTERVAL YEAR TO MONTH和INTERVAL DAY TO SECOND
v_college_deadline := TO_TIMESTAMP('06/06/2004', 'DD/MM/YYYY') + INTERVAL '12-3' YEAR TO MONTH;
指定日期加上12年3个月，结果：06-9月 -16 12.00.00.000000 上午
v_college_deadline := TO_TIMESTAMP('06/06/2004', 'DD/MM/YYYY') 
                         + INTERVAL '12-3' YEAR TO MONTH
                         + INTERVAL '19 9:0:0.0' DAY TO SECOND;
在前面的基础上再加上19天9小时，结果：25-9月 -16 09.00.00.000000 上午

符合类型：
记录、嵌套表、index-by表和varrays

引用类型：
Oracle包含两种PL/SQL类型，及REF COURSOR类型和REF类型。引用类型提供的内存结构，但与标量
类型和符合类型不同，它们在整个程序中可以指向不同的存储位置。
1、REF CURSOR
REF CURSOR类型的变量指的是游标变量。可以定义一个SYS_REFCURSOR类型的游标变量
SYS_REFCURSOR是一个弱类型的REF CURSOR类型，Oracle 9iR1 PL/SQL才开始支持这种弱类型的REF CURSOR
CREATE OR REPLACE PROCEDURE authors_sel(
  cv_results IN OUT SYS_REFCURSOR)
IS
BEGIN
  OPEN cv_results FOR
  SELECT id, first_name, last_name FROM authors;
END;
/
测试：
variable x REFCURSOR
EXEC authors_sel(:x)
PRINT x

2、REF
对象类型经常要使用REF。可以简单地将REF看作是一个指针，只是该指针指向的内容是对象表
或对象试图里的一个对象实例。

绑定变量
运行查询语句实际上要经历3个主要阶段：解析(PARSE)、执行(EXECUTE)和取值(FETCH)
alter session set SQL_TRACE = TRUE;
当代码块将某个值赋给查询使用的某个标量时，就认为这个变量是一个绑定变量

流程控制语句：
---------------------------------------
IF condition
THEN
  action
END IF;

---------------------------------------
IF condition
THEN
  action;
ELSE
  action;
END IF;

---------------------------------------
IF condition
THEN
  action
ELSIF condition
THEN
  action
[ELSE
  action]
END IF;

---------------------------------------
CASE expression
  WHEN test1 THEN action;
  WHEN test2 THEN action;
  .............
END CASE;

---------------------------------------
LOOP
  action
  EXIT WHEN condition
END LOOP;

---------------------------------------
FOR counter IN low_number .. high_number
LOOP
  action;
END LOOP;

---------------------------------------
WHILE condition
LOOP
  action;
END LOOP;

循环与标签
标签实际上就是一种标记符号，在PL/SQL中使用它来命名一段不能命名的代码，可以给循环打上一个
标签，通过变迁就可以直接应用它们了，就像他们有名称一样。
BEGIN
   <<l_For_Loop>>
   FOR v_count IN 1 .. 20
   LOOP
      DBMS_OUTPUT.PUT_LINE('Iteration: '||v_count);
   END LOOP l_For_Loop;
END;
结尾处标签名可有可无。

使用GOTO语句导航代码的执行顺序
BEGIN
   DBMS_OUTPUT.PUT_LINE('BEGINNING OF BLOCK');

   GOTO l_Last_Line;

   DBMS_OUTPUT.PUT_LINE('GOTO didn''t work!');
   RETURN;
   <<l_Last_Line>>
   DBMS_OUTPUT.PUT_LINE('Last Line');
END;

==============================
第4章 PL/SQL 与 SQL
==============================
4.1事务处理
ACID分别代表的含义：
Atomicity(原子性)  Consistency(一致性)  Isolation(隔离性)  Durability(持久性)

事务与锁定
sysdba查询锁信息
SELECT d.session_id sid, d.lock_type, d.mode_requested,
       d.mode_held, d.blocking_others
FROM dba_locks d, v$session v
WHERE v.username = 'PLSQL'
AND d.session_id = v.sid;

--锁定的对象
SELECT dbl.lock_type, dbl.mode_held, dbl.blocking_others, 
       dbo.object_name object_locked, dbo.object_type
FROM dba_locks dbl, v$session v, dba_objects dbo
WHERE v.username = 'PLSQL'
AND dbl.session_id = v.sid
AND dbo.object_id = dbl.lock_id1;

回滚：
savepoint save_name;
rollback to savepoint save_name;

自治事务
可以使用名为AUTONOMOUS_TRANSACTION的编译指令pragma创建一个自治事务。使用PRAGMA时，应该放在代码的申明部分。
可以在过程、函数、触发器和对象类型中创建自治事务代码
CREATE OR REPLACE PROCEDURE logging_ins_error (
   i_username IN VARCHAR2,
   i_datetime IN TIMESTAMP)
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN

   INSERT INTO logging (username, datetime)
   VALUES (i_username, i_datetime);
   -- NO COMMIT OR ROLLBACK
END;