study_note.txt

介绍Oracle PL SQL编程

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

从数据字典修改代码：
update sys.source$ s set s.source = 'retval := retval || street_address_in(i) || Chr(10);'
where s.obj# = 
(select o.obj# from sys.obj$ o 
where o.owner# = (select u.user# from sys.user$ u where name = 'PLSQL')
and o.name = 'MANY_TO_ONE' )
and line = 6;
alter function plsql.many_to_one compile;

得到指定PL/SQL对象的源代码：
create or replace procedure 
get_source(in_name in varchar2,
                   in_type in varchar2,                                                                     
                   out_cursor out sys_refcursor)
AS
begin
open out_cursor for select text from user_source
where name = in_name
and type = in_type
order by line;

exception
    when others then
        dbms_output.put_line('Error Code:' || SQLCODE || '    Error Message:' || SQLERRM);
end;

==============================
第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;
/
自治事务内部必须显示地COMMIT或ROLLBACK

查看数据字典中的代码：
select line, text from user_source where name = 'LOGGING_INS_ERROR';
重新编译
ALTER package/type object_name COMPILE body;

LIKE语句匹配，字符：下划线（"_"），字符串："%"

游标：
游标在OPEN是形成一个快照，数据到PGA
游标分为：显示游标，隐式游标，游标变量，游标子查询
游标的属性
%BULK_EXCEPTIONS, %BULK_ROWCOUNT, %FOUND, %ISOPEN, %NOTFOUND, %ROWCOUNT
1、显示游标
申明
CURSOR cursor_name[(parameter_list)] [RETURN return_type]
IS query
[FOR UPDATE [OF (column_list)] [NOWAIT];
打开
OPEN cursor_name[(parameter_list)]
记录获取
FETCH cursor_name INTO variable_name(s) | PL/SQL_record;
游标关闭
CLOSE cursor_name
使用LOOP遍历游标
LOOP
    FETCH....
    EXIT WHEN cursor_name%NOTFOUND;
END LOOP;

WHILE cursor_name%FOUND LOOP
    FETCH....
END LOOP;

FOR cur_variable IN cursor_name LOOP
    不用FETCH，直接使用cur_variable变量，该变量也不用申明
END LOOP;

2、隐式游标
隐式游标又Oracle自动打开和关闭，Oracle执行的每一个DML的SQL语句都会自动在PGA中占用一个上下文区域，并一次拥有一个游标。
隐式游标的名称为SQL，也可以使用游标的所有属性。

3、游标变量
游标变量的值在运行时而不是在编译时确定，并且在同一个代码块中，一个游标变量可以被多条SELECT语句打开。
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;