oracle pl/sql 程序设计历史笔记整理

oracle pl/sql 程序设计历史笔记整理
20131016 周三

oracle pl/sql 程序设计第2章创建并运行pl/sql代码
- sqlplus yjkhecc/yjkhecc@10.85.23.92:1521/orcl
- 在java中调用存储过程：
create or replace procedure t_p(l_in in out number) is

begin

l_in := 5;

end;

@Test

    public void test() throws SQLException

    {

        DataSource ds = DataSourceGen.getDataSourceFromXML();

        Connection conn = ds.getConnection();

        int inValue = 0;

        CallableStatement cs = conn.prepareCall("{call t_p(?)}");//注意有大括号

        cs.setInt(1, inValue);//设置传入的值下标从1开始

        cs.registerOutParameter(1, Types.INTEGER);//注册传出的值

        cs.executeUpdate();//执行

        int outValue = cs.getInt(1);//获取输出

        System.out.println(inValue);//输出0

        System.out.println(outValue);//输出5

        conn.close();

    }
- 在java中调用fucntion
create or replace function t_f(l_in in out number) return number is

l_c number;

begin

l_in := 5;

l_c := 2;

return l_c;

end;

@Test

    public void test() throws SQLException

    {

        DataSource ds = DataSourceGen.getDataSourceFromXML();

        Connection conn = ds.getConnection();

        int inValue = 0;

        CallableStatement cs = conn.prepareCall("{? = call t_f(?)}");

        cs.setInt(2, inValue);//设置传入的值

        cs.registerOutParameter(1, Types.INTEGER);//注册传出的值-通过return返回值

        cs.registerOutParameter(2, Types.INTEGER);//注册传出的值-通过参数返回值

        cs.executeUpdate();

        int outValue1 = cs.getInt(1);

        int outValue2 = cs.getInt(2);

        System.out.println(inValue);//输出0

        System.out.println(outValue1);//输出2

        System.out.println(outValue2);//输出5

        conn.close();

    }

也可以通过select t_f(1) from dual;调用，但是具有out参数的函数不可以通过sql调用。

oracle pl/sql 程序设计第3章语言基础
- 在plsql中调用存储过程：
  begin t_p(); end;
  调用函数:
  declare a number;begin a := t_f();end;
- 变量，异常，模块的作用范围都是在声明他们的块内。
  create or replace package scope_demo is
g_globle number;

procedure set_globle(number_in in number);

end scope_demo;

create or replace package body scope_demo is

procedure set_globle(number_in in number) is

l_count pls_integer;

l_name varchar2(10) := '北京';

begin

<<localscope>>--标签为匿名块命名

declare

l_use char(1) := '1';

begin

select count(1)

intoset_globle.l_count

from pub_organ o

where o.organ_name likeset_globle.l_name || '%'

and o.in_use =localscope.l_use;

dbms_output.put_line(set_globle.l_count);

end localscope;

scope_demo.g_globle := set_globle.number_in;

end set_globle;

end scope_demo;

begin

scope_demo.set_globle(2);

dbms_output.put_line(scope_demo.g_globle);

end;
- 嵌套程序，完全在生命单元内声明的局部程序。
  declare
procedure local_precedure is

begin

dbms_output.put_line('dd');

end local_precedure;

begin

for i in 1..10

loop

local_precedure();

end loop;

end;
- 在sql中，空字符串和null是一样的，不管是对varchar还是char。但是在plsql中，空字符串在varchar中是和null一样的，但是在char中不是。如：
  declare
l_c char(1);

begin

l_c := '';

if (l_c is null) then

dbms_output.put_line(1);

elsif (l_c = ' ') then

dbms_output.put_line(2);--输出2 因为自动填满了而在sql中不会自动填满

end if;

end;

oracle pl/sql 程序设计第4章条件和顺序控制
- plsql对and的多个条件之间采用短路控制，后面的条件可能不会被执行。所以不应该将逻辑放在and中处理。
- 规律：if elsif when后面肯定要跟上then。
- case语句以end结束将作为一个表达式存在，以end case结束将作为一条语句存在。
- case语句中的每个when条件如果满足，那么之后的when将不会再被执行。相当于加了个break。
- if中使用in和like
  declare
l_n number := '10';

begin

if(l_n in ('10','20'))

then dbms_output.put_line('in');

end if;

if(l_n like '10%')

then dbms_output.put_line('like');

end if;

end;

20131018 周五

oracle pl/sql 程序设计第5章用循环进行迭代处理
- 简单循环
  declare
l_c pls_integer;

begin

l_c := 0;

loop

--exit when l_c > 10;

if (l_c > 10) then

exit;

end if;

dbms_output.put_line(l_c);

l_c := l_c + 1;

end loop;

end;
- while循环
declare

l_c pls_integer;

begin

l_c := 0;

while l_c <= 10 loop —while的条件可以加括号也可以不加

dbms_output.put_line(l_c);

l_c := l_c + 1;

end loop;

end;
- for循环
只有数值型for循环和游标型(可以是游标，也可以直接是sql查询)for循环，这两种循环都要有in字句。遍历的变量不用声明，系统会自动生成。

begin

for l_c in 0 .. 10 loop –不能加括号

dbms_output.put_line(l_c);

end loop;

end;

begin

for l_organ in (select * from pub_organ o where o.organ_name like '北京%') loop –查询必须用括号括起来

dbms_output.put_line(l_organ.organ_name);

end loop;

end ;
- 嵌套循环以及从内层直接跳出
  declare
i pls_integer := 0;

j pls_integer := 0;

begin

<<outerloop>>

loop

dbms_output.put_line(i);

exit when i > 10;

j := 0;

<<innerloop>>

loop

dbms_output.put_line(' ' || j);

exit when j > 10;

exit outerloop when(j = 5 and i = 5);

j := j + 1;

end loop innerloop;

i := i + 1;

end loop outerloop;

end;
- oracle11g开始才支持continue。
oracle pl/sql 程序设计第6章异常处理
- 异常出现后，如果没有被捕捉，将会自动抛出到上层。
- 遇到未处理的异常（一直到顶层都没有被捕捉），系统会自动加上rollback。
  使用下面的处理将会忽略异常不会滚也不会报错继续执行下面的语句
  exception
when others then

null;

3、

create or replace procedure t_pp as

l_n number;

begin

update pub_organ o set o.organ_name = 'dddd444ddd' where o.organ_id = 'O50649821';

l_n := 'ddd';--此处出错本层未捕捉自动抛出异常到上层

end;

begin

t_pp();--此处会捕捉到下层抛出的异常

exception

when others then

rollback;--回滚

commit;

end;
1. 什么时候抛出异常什么时候捕捉？
  如果存储过程是为了其他程序来调用的，并非直接面向前台按钮触发的操作。那么可以将异常抛出到上层来处理。如果是直接面向前台按钮触发的操作，那么需要将异常限制在本层处理掉，不要抛出到应用层去。可以返回成功失败信息，失败原因等错误信息。
2. 未绑定异常号的异常：
  这种异常根据变量来定位异常，定义的两个异常变量将是两个异常。
  declare
l_ex1 exception;

l_ex2 exception;

begin

begin

raise l_ex1;

end;

exception

when l_ex2

then dbms_output.put_line('l_ex1');

end;
1. 绑定异常号的异常，将根据异常号来捕捉异常。
  
  declare
l_ex1 exception;

l_ex2 exception;

pragma exception_init(l_ex1,-20111);

pragma exception_init(l_ex2,-20111);

begin

begin

raise l_ex1;

end;

exception

when l_ex2

then dbms_output.put_line('l_ex1');--此处将成功打印

end;
1. no_data_found
  no_data_found的异常号是-1403，但是作为一个特殊情况，将一个自定义的异常关联到-1403不能捕捉到no_data_found，必须关联到100.
  
  declare
l_organ_id varchar2(32);

l_organ_name varchar2(500);

begin

l_organ_id := '222';

select o.organ_name into l_organ_name from pub_organ o where o.organ_id = l_organ_id;

end;

declare

l_organ_id varchar2(32);

l_organ_name varchar2(500);

err_no_organ_1403 exception;

pragma exception_init(err_no_organ_1403, -1403);

begin

l_organ_id := '222';

select o.organ_name

into l_organ_name

from pub_organ o

where o.organ_id = l_organ_id;

exception

when err_no_organ_1403 then

dbms_output.put_line('err_no_organ_1403');

end;

declare

l_organ_id varchar2(32);

l_organ_name varchar2(500);

err_no_organ_100 exception;

pragma exception_init(err_no_organ_100, 100);

begin

l_organ_id := '222';

select o.organ_name

into l_organ_name

from pub_organ o

where o.organ_id = l_organ_id;

exception

when err_no_organ_100 then

dbms_output.put_line('err_no_organ_100');--成功打印

end;
1. 系统预留的可以使用异常号（保证不会跟系统已有的异常的异常号冲突）：
  -20NNN。
2. 在exception语句中可以使用SQLCODE 和SQLERRM来获取错误代码和错误描述。
3. raise_application_error
  raise_application_error可以抛出自定义的-20NNN的异常号，并附带自定义的字符串用来存储错误信息。
  
  raise_application_error是一个系统预定义的存储过程，它接收一个错误代码和错误描述，然后封装一个又此代码和描述组成的exception对象，然后抛出。它的好处是可以封装错误信息，而自定义的exception对象无法将错误信息和它绑定。
  
  declare
l_ex1 exception;

pragma exception_init(l_ex1, -20111);

begin

begin

raise_application_error(-20111, 'ddd');

end;

exception

when l_ex1 then

dbms_output.put_line('l_ex1'); --此处将成功打印"l_ex1"

dbms_output.put_line(sqlerrm); --此处将成功打印"ORA-20111: ddd"

end;
1. 存储过程或者函数内部的异常被抛出到外部，那么所有的out参数的修改将被回滚。如：
  
  create or replace procedure t_pp(p_num in out number) as
l_ex1 exception;

pragma exception_init(l_ex1, -20111);

begin

p_num := 10;

raise_application_error(-20111, 'ddd');

end;

declare

l_num number := 0;

begin

t_pp(l_num);

exception

when others then

null;

dbms_output.put_line(l_num);--输出0

end;
1. dbms_utility.format_call_stack()函数获取错误错误码和错误信息，dbms_utility.format_error_backtrace ()获取错误的堆栈信息。
create or replace procedure t_pp as

l_n number;

begin

update pub_organ o set o.organ_name = 'dddd444ddd' where o.organ_id = 'O50649821';

update pub_organ o set o.organ_name = '' where o.organ_id = 'O50649831';

end;

begin

t_pp();--此处会捕捉到下层抛出的异常

exception

when others then

dbms_output.put_line(dbms_utility.format_error_stack);

dbms_output.put_line('..................................');

dbms_output.put_line(dbms_utility.format_error_backtrace);

end;

最终打印为：

ORA-01407: 无法更新 ("YJKHECC"."PUB_ORGAN"."ORGAN_NAME") 为 NULL

..................................

ORA-06512: 在 "YJKHECC.T_PP", line 5

ORA-06512: 在 line 2
1. 在exception的when中可以使用or匹配多个异常。
2. 在包中定义exception
  create or replace package p_errors is
my_exception exception;

pragma exception_init(my_exception, -20111);

end;

begin

raise_application_error(-20111, 'ddd');

exception

when p_errors.my_exception then

dbms_output.put_line('-20111');

end;

20131106 周三

oracle pl/sql 程序设计第7章使用数据
- varchar2的度量单位是字节，建表时，最大字节数是4000，在plsql中最大可达3W多。字符集： oracle的默认字符是AL32UTF8，一个中文占据3个字节。而中文编码 ZHS16GBK一个中文占据2个字节。
  查看一个中文占据多少个字节：
  select lengthb('中') from dual;
- oracle在sql中的数值型只有number。在plsql中可以有pls_integer等。
  number:浮点小数
  number(2):两位整数
  number(5,2):共5位，包含2位小数。
- 日期型有date和timestamp。date可以精确到秒。timestamp可以更精确。
- 没有布尔类型。
- BLOB CLOB
- 声明语法：是否常量是否为空默认值
  l_c constant varchar2(10) not null := '2';--注意顺序
- oracle支持多种类型之间的隐式转换。常用的是varchar和number之间的转换。date和varchar之间的默认转换需要系统配置日期格式的参数NLS_DATE_FORMAT，所以不要使用。
- subtype
  
  局部子类型：
  declare
subtype id_type is varchar(32);

emp_id id_type;

begin

select o.organ_id

into emp_id

from pub_organ o

where o.organ_name = '江西省德兴市供电有限责任公司';

dbms_output.put_line(emp_id);

exception

when no_data_found then

dbms_output.put_line('no_data_found');

when too_many_rows then

dbms_output.put_line('too_many_rows');

when others then

dbms_output.put_line('others exception happen');

end;

包子类型：

create or replace package scope_demo is

g_globle number := 0;

procedure set_globle(number_in in number);

subtype id_type is varchar(32);

end scope_demo;

declare

emp_id scope_demo.id_type;

begin

select o.organ_id

into emp_id

from pub_organ o

where o.organ_name = '江西省德兴市供电有限责任公司';

dbms_output.put_line(emp_id);

exception

when no_data_found then

dbms_output.put_line('no_data_found');

when too_many_rows then

dbms_output.put_line('too_many_rows');

when others then

dbms_output.put_line('others exception happen');

end;
- 显示转换
  显示转换经常用到的是to_char to_number to_date等函数。cast语法是一个sql标准的转换语法，不过不是很实用，因为没有转换格式的配置。不过cast可以把一个集合转换成另外一个类型的集合。(集合的内容必须是一样的)。
- cast
  cast转换的源类型和目的类型必须是全局的数据类型(使用create创建的)，不能是局部定义(放在declare块中或者包中)的。如下面代码将不能执行：
  
  declare
type t_names is table of varchar(200);

type t_org_names is table of varchar(200);

l_names t_names;

l_org_names t_org_names;

begin

l_names := t_names('1', '2');

for l_name in (select column_value orgName

from table(cast(l_names as t_org_names))) loop

dbms_output.put_line(l_name.orgName);

end loop;

end;

但是，如果使用下面代码，将会成功。
create type t_names is table of varchar(200);

create type t_org_names is table of varchar(200);

cast不能转换record和object。

20131107 周四

oracle pl/sql 程序设计第8章字符串
- 定义字符串（char和varchar2）的时候，可以通过byte和char来限定长度的类型是字节来时字符。
  表中：
  create table TT
(

name VARCHAR2(32 CHAR)

)

plsql中：

declare

l_name VARCHAR2(2 CHAR);

begin

l_name := '中文';

end;

如果没有设置byte和char，默认值是根据系统参数NLS_LENGTH_SEMANTICS来配置的。
- 因为plsql中字符串的长度大于sql中字符串的长度，所以如果讲一个plsql的varchar2存到表中，可能会造成截断。
- instr
  从前往后查找：instr('12345654321','1')
  从后往前查找：instr('12345654321','1',-1)
  得到的结果都是从1开始的
- trim ltirm rtirm
oracle pl/sql 程序设计第9章数字
- number是十进制的，也是唯一可以在建表时使用的。小数点前位数超出会报错，小数点后位数超出会自动四舍五入。number是高精度的十进制，所以处理金额应该使用number。
- 整型：
  pls_integer：这个是为了效率而设计的。底层的运算都是采用机器语言构建的，而number是c语言。如果pls_integer要经常和number做转换，还是一开始就采用number好。
  binary_integer：在10g之后将等同于pls_integer。尽量不使用它。
  simple_integer：在原生编译模式下能够取得极佳的性能。比上面两个都要快很多。但是不支持null值，没有溢出检查。
- 浮点数：
  binary_float：二进制单精度
  binary_double：二进制双精度
  simple_float和simple_double和simple_integer一样，速度快，但是不支持null，不检查溢出。这两个也是二进制的。在11g才有。
  注意：二进制浮点数涉及到丢失的问题。
  float可以到10的38次方，double可以到10的308次方。
  
  begin
     dbms_output.put_line(0.95f);--binary_float 9.49999988E-001

     dbms_output.put_line(0.95d);--binary_double 9.4999999999999996E-001

     dbms_output.put_line(0.95);--number .95

end;

20131108 周五

oracle pl/sql 程序设计第10章日期
1. 常用的日期格式 YYYY-MM-DD HH:MI:SS
2. extract (year|month|day|hour|minute|second from sysdate) 返回number型数值
oracle pl/sql 程序设计第11章记录类型
- %rowtype（对表或者游标）得到的是一个记录类型的变量。
- --定义记录
create or replace package p_records is

type organ is record(

organ_id varchar2(30),

organ_name varchar2(80)

);

type obj is record (

obj_id varchar2(30),

obj_name varchar2(80)

);

end p_records;

declare

l_organ p_records.organ;

l_organ1 p_records.organ;

l_obj p_records.obj;

begin

--对记录赋值不能new 也没有构造函数只能逐个属性进行赋值

l_organ.organ_id := '01';

l_organ.organ_name := '国家电网';

l_organ1.organ_id := '01';

l_organ1.organ_name := '国家电网';

l_obj.obj_id := '01';

l_obj.obj_name := '01';

dbms_output.put_line(l_obj.obj_name);

--失败赋值类型不一致

--l_obj := l_organ;

--成功

l_organ := l_organ1;

/*

--失败不能对整个record比较

if (l_organ = l_organ1) then

dbms_output.put_line('equal');

end if;

*/

end;

--对记录进行赋值

declare

type ee is record(

id1 VARCHAR2(2000),

dataid VARCHAR2(255),

fieldname VARCHAR2(40),

content LONG RAW);

l_e ee;

l_d editdata%rowtype;

begin

--into不要求字段类型一摸一样可以转换即可

--定义的字段长度不一样也可以执行如果执行时实际长度不够会报错

select * into l_e from editdata t;

l_d := l_e;

l_d.id := 'newid';

if (l_d.id = l_e.id1) then

dbms_output.put_line('赋值操作是赋值引用');--不打印

else

dbms_output.put_line('赋值操作是赋值值');--打印

end if;

--插入整个记录按照字段声明顺序不是根据名称

insert into editdata values l_d;

--可以对记录赋null 结果就会把所有字段置null

l_d := null;

dbms_output.put_line(l_d.fieldname); --输出空字符串

--不可以对记录判断为空只能对记录的字段逐个判断下面注释会报错

--if(l_d is null)

-- then dbms_output.put_line('null');

--end if;

end;
- 对记录赋值
create table file_test (

file_id varchar2(32),

file_name varchar2(100),

file_path varchar2(200),

file_large number

);

insert into FILE_TEST

(FILE_ID, FILE_NAME, FILE_PATH, FILE_LARGE)

values

('1', '1.txt', 'e:/1.txt', 121);

declare

type my_file is record(

file_id varchar2(32),

file_name varchar2(100),

file_path varchar2(200),

file_large number);

type my_image is record(

image_id varchar2(32),

image_name varchar2(100),

image_path varchar2(200),

image_large number);

l_file my_file;

l_image my_image;

l_file_rowtype file_test%rowtype;

begin

--select into的操作只要字段类型可以匹配即可

--即使记录字段长度与表中的字段长度不一致也可以执行如果实际插入时的长度不够会报错

select * into l_file from file_test t;

dbms_output.put_line(l_file.file_name);

select * into l_image from file_test t;

dbms_output.put_line(l_image.image_name);

select * into l_file_rowtype from file_test t;

--记录赋值要求记录的类型是一样的（不是记录的字段的类型）

--例外：rowtype类型的"伪记录" 可以像into一样的赋值

--l_file := l_image;--报错

l_file := l_file_rowtype;

l_image := l_file_rowtype;

for l_filetemp in (select * from file_test) loop

l_file := l_filetemp;

l_image := l_filetemp;

end loop;

dbms_output.put_line(l_file.file_name);

dbms_output.put_line(l_image.image_name);

end;
1. 使用记录执行DML操作
--不严格要求记录必须是%rowtype 只要record字段与表字段匹配即可

declare

type t_unit is record(

organid VARCHAR2(32),

unit_type VARCHAR2(2),

senddept1 VARCHAR2(32),

ifsend1 CHAR(1));

l_unit t_unit;

l_unit1 t_unit;

begin

l_unit.organid := '111';

l_unit.unit_type := '1';

insert into org_unit t values l_unit;

--使用row关键字update

update org_unit t

set row = l_unit

where t.organid = '11O000000000000000000000000000';

delete from org_unit t

where t.organid = '29O000000000000000000000000000'

returning t.organid, t.unit_type, t.senddept, t.ifsend into l_unit1; --可以returning到记录

dbms_output.put_line(l_unit1.senddept1);

end;

20131211 周三

oracle pl/sql 程序设计第12章集合
- 关联数组嵌套表 VARRAY
- 特征：同质的（数据类型是相同的），一维的（暂时不支持多维，不过可以集合套集合）。
- 术语：无界：无最大长度限制。有界：有最大长度限制。稀疏：中间有空隙。紧凑：中间无空隙。
- 集合的常见作用：可以作为参数（in out皆可），返回值。可以作为record或者object的属性。可以作为表的字段类型。
- 关联数组示例：
  declare
--限制：关联数组只能在declare中声明不能全局create

--限制：关联数组的index by只能是pls_integer或者varchar2

type t_name is table of varchar2(100) index by pls_integer;

l_t_name t_name;

begin

--不需要构造方法来声明也不需要extend 直接通过下标插入数据

l_t_name(0) := 'a';

l_t_name(5) := 'b';

l_t_name(9) := 'c';

l_t_name(1000) := 'd';

--对关联数组进行遍历因为是稀疏的所以不能使用下标++的方式遍历

declare

l_index pls_integer;

begin

--在oracle中对于方法或者存储过程的调用可以不加括号表示不传入参数

l_index := l_t_name.first;

while (l_index is not null) loop

dbms_output.put_line(l_t_name(l_index));

l_index := l_t_name.next(l_index);

end loop;

end;

end;
- 嵌套表示例：
  create or replace type t_names is table of varchar(100);
declare

--必须通过构造函数初始化才能使用（初始化不用new关键字）

family t_names := t_names();

children t_names := t_names();

parents t_names := t_names();

begin

--先extend 在根据下标插入数据 oracle下标从1开始

family.extend(5);

--extend之后默认为null

--不论是嵌套表还是关联数组 VARRAY，对于非法下标的访问都会报no_data_found的错误 extend之后就不是非法的了

if (family(3) is null) then

dbms_output.put_line('extend之后默认为null');

end if;

family(1) := 'zjf';

family(2) := 'zcx';

family(3) := 'zdw';

family(4) := 'zsy';

family(5) := 'mcw';

--extend默认参数是1

parents.extend;

parents(1) := 'zsy';

parents.extend;

parents(2) := 'mcw';

--只有嵌套表可以使用集合方法

children := family multiset except parents;

--此时还是紧凑的可以这样遍历

for l_index in children.first .. children.last loop

dbms_output.put_line(children(l_index));

end loop;

end;
- VARRAY示例：
  --varray是有界的紧凑的这个其他两个正好相反除此之外它的用法和嵌套表基本一样
create or replace type t_names_v is varray(3) of varchar2(100);

declare

--初始化

l_names t_names_v := t_names_v();

begin

l_names.extend(2);

dbms_output.put_line(l_names.count); --输出2

--为了保持紧凑 delete只能全部删除（不传参数）

l_names.delete;

dbms_output.put_line(l_names.count); --输出0

l_names.extend(2);

--使用trim从尾部删除也能保持紧凑

l_names.trim;

dbms_output.put_line(l_names.count); --输出1

end;
- Collection methods make collections easier to use, and make your applications easier to maintain. These methods include COUNT, DELETE, EXISTS, EXTEND, FIRST, LAST, LIMIT, NEXT, PRIOR, and TRIM.
- EXISTS方法
  DECLARE
TYPE NumList IS TABLE OF INTEGER;

n NumList := NumList(1, 3, 5, 7);

BEGIN

n.DELETE(2); -- Delete the second element

IF n.EXISTS(1) THEN

DBMS_OUTPUT.PUT_LINE('OK, element #1 exists.');

END IF;

IF n.EXISTS(2) = FALSE THEN

DBMS_OUTPUT.PUT_LINE('OK, element #2 has been deleted.');

END IF;

IF n.EXISTS(99) = FALSE THEN

DBMS_OUTPUT.PUT_LINE('OK, element #99 does not exist at all.');

END IF;

END;
- COUNT方法
- LIMIT方法：
  For nested tables and associative arrays, which have no declared size, LIMIT returns NULL. For varrays, LIMIT returns the maximum number of elements that a varray can contain. You specify this limit in the type definition, and can change it later with the TRIM and EXTEND methods.
- FIRST and LAST方法
- PRIOR and NEXT方法
- EXTEND Method
  This procedure has three forms:
  - EXTEND appends one null element to a collection.
  - EXTEND(n) appends n null elements to a collection.
  - EXTEND(n,i) appends n copies of the ith element to a collection.
- TRIM Method
  This procedure has two forms:
  - TRIM removes one element from the end of a collection.
  - TRIM(n) removes n elements from the end of a collection.
If you want to remove all elements, use DELETE without parameters.
- Deleting Collection Elements (DELETE Method)
This procedure has various forms:
- DELETE with no parameters removes all elements from a collection, setting COUNT to 0.
- DELETE(n) removes the nth element from an associative array with a numeric key or a nested table. If the associative array has a string key, the element corresponding to the key value is deleted. If n is null, DELETE(n) does nothing.
- DELETE(m,n) removes all elements in the range m..n from an associative array or nested table. If m is larger than n or if m or n is null, DELETE(m,n) does nothing.
- 由于实现机制的原因，trim方法会以为delete的方法删除掉的内容还未删除，所以两个不要混用。
- 集合赋值
declare

family t_names := t_names();

family1 t_names := t_names();

begin

family.extend(5);

family(1) := 'zjf';

family(2) := 'zcx';

family(3) := 'zdw';

family(4) := 'zsy';

family(5) := 'mcw';

--集合赋值类似于复制不是引用级别的操作

family1 := family;

end;

DECLARE

TYPE last_name_typ IS VARRAY(3) OF VARCHAR2(64);

TYPE surname_typ IS VARRAY(3) OF VARCHAR2(64);

-- These first two variables have the same datatype.

group1 last_name_typ := last_name_typ('Jones', 'Wong', 'Marceau');

group2 last_name_typ := last_name_typ('Klein', 'Patsos', 'Singh');

-- This third variable has a similar declaration, but is not the same type.

group3 surname_typ := surname_typ('Trevisi', 'Macleod', 'Marquez');

BEGIN

-- Allowed because they have the same datatype

group1 := group2;

-- Not allowed because they have different datatypes

-- group3 := group2; -- raises an error

END;
- 集合的行赋值
  集合的行赋值取决于行的类型，如下面的列子的行是record，那么规则是基于record的。
declare

type t_organ is table of pub_organ%rowtype;

l_organs t_organ := t_organ();

begin

l_organs.extend;

--方式1 into

select *

into l_organs(1)

from pub_organ o

where o.organ_id = 'O50649765';

dbms_output.put_line(l_organs(1).organ_name);

--方式2 record直接赋值

for l_organ in (select * from pub_organ o where o.organ_id = 'O50649765') loop

l_organs(1) := l_organ;

end loop;

dbms_output.put_line(l_organs(1).organ_name);

--至今为止对于集合 record 集合的一行等的赋值都是值赋值不是引用赋值

declare

l_organ pub_organ%rowtype;

begin

l_organ.organ_name := 'organ1';

l_organs(1) := l_organ;

l_organ.organ_name := 'organ2';

dbms_output.put_line(l_organ.organ_name); --organ2

dbms_output.put_line(l_organs(1).organ_name); --organ1

end;

end;
- CAST 将集合转换为另外一个集合，MULTISET将查询转换为集合。TABLE将集合转换为查询。TABLE只能在sql中使用。
  CREATE TYPE address_book_t AS TABLE OF cust_address_typ;
CREATE TABLE cust_address (

custno NUMBER,

street_address VARCHAR2(40),

postal_code VARCHAR2(10),

city VARCHAR2(30),

state_province VARCHAR2(10),

country_id CHAR(2));

CREATE TABLE cust_short (custno NUMBER, name VARCHAR2(31));

CREATE TABLE states (state_id NUMBER, addresses address_array_t);

SELECT s.custno, s.name,

CAST(MULTISET(SELECT ca.street_address,

ca.postal_code,

ca.city,

ca.state_province,

ca.country_id

FROM cust_address ca

WHERE s.custno = ca.custno)

AS address_book_t)

FROM cust_short s;

21、对集合的null操作

DECLARE

type t_nested is table of varchar2(30);

type t_associative is table of varchar2(30) index by pls_integer;

type t_varray is varray(10) of varchar2(30);

l_nested1 t_nested;

l_nested2 t_nested;

l_associative t_associative;

l_varray t_varray;

BEGIN

if (l_nested1 is null) then

dbms_output.put_line('nested table is automatic null!'); --成功打印

end if;

if (l_associative is null) then

dbms_output.put_line('associative array is automatic null!'); --不打印因为关联数组不需要通过构造函数初始化默认不是null

end if;

if (l_varray is null) then

dbms_output.put_line(' varray is automatic null!'); --成功打印

end if;

l_nested1 := t_nested();

if (l_nested1 is null) then

dbms_output.put_line('nested table is automatic null!'); --不打印

end if;

l_nested1 := null;

if (l_nested1 is null) then

dbms_output.put_line('nested table is automatic null!'); --打印

end if;

END;
- 对集合的=比较只对嵌套表有效
DECLARE

TYPE dnames_tab IS TABLE OF VARCHAR2(30);

dept_names1 dnames_tab := dnames_tab('Shipping','Sales','Finance','Payroll');

dept_names2 dnames_tab := dnames_tab('Sales','Finance','Shipping','Payroll');

dept_names3 dnames_tab := dnames_tab('Sales','Finance','Payroll');

BEGIN

-- We can use = or !=, but not < or >.

-- Notice that these 2 are equal even though the members are in different order.

IF dept_names1 = dept_names2 THEN

DBMS_OUTPUT.PUT_LINE('dept_names1 and dept_names2 have the same members.');

END IF;

IF dept_names2 != dept_names3 THEN

DBMS_OUTPUT.PUT_LINE('dept_names2 and dept_names3 have different members.');

END IF;

END;
- You can use operators such as SET, MULTISET UNION, MULTISET INTERSECT, and MULTISET EXCEPT to transform nested tables as part of an assignment statement.
- You can also apply set operators (CARDINALITY, MEMBER OF, IS A SET, IS EMPTY) to check certain conditions within a nested table or between two nested tables
DECLARE

TYPE nested_typ IS TABLE OF NUMBER;

nt1 nested_typ := nested_typ(1,2,3);

nt2 nested_typ := nested_typ(3,2,1);

nt3 nested_typ := nested_typ(2,3,1,3);

nt4 nested_typ := nested_typ(1,2,4);

answer BOOLEAN;

howmany NUMBER;

PROCEDURE testify(truth BOOLEAN DEFAULT NULL, quantity NUMBER DEFAULT NULL) IS

BEGIN

IF truth IS NOT NULL THEN

DBMS_OUTPUT.PUT_LINE(CASE truth WHEN TRUE THEN 'True' WHEN FALSE THEN 'False' END);

END IF;

IF quantity IS NOT NULL THEN

DBMS_OUTPUT.PUT_LINE(quantity);

END IF;

END;

BEGIN

answer := nt1 IN (nt2,nt3,nt4); -- true, nt1 matches nt2

testify(truth => answer);

answer := nt1 SUBMULTISET OF nt3; -- true, all elements match

testify(truth => answer);

answer := nt1 NOT SUBMULTISET OF nt4; -- also true

testify(truth => answer);

howmany := CARDINALITY(nt3); -- number of elements in nt3

testify(quantity => howmany);

howmany := CARDINALITY(SET(nt3)); -- number of distinct elements

testify(quantity => howmany);

answer := 4 MEMBER OF nt1; -- false, no element matches

testify(truth => answer);

answer := nt3 IS A SET; -- false, nt3 has duplicates

testify(truth => answer);

answer := nt3 IS NOT A SET; -- true, nt3 has duplicates

testify(truth => answer);

answer := nt1 IS EMPTY; -- false, nt1 has some members

testify(truth => answer);

END;
- 临时表
  
  --临时表
  create global temporary table T_TEMP
(

name VARCHAR2(200)

)

on commit delete rows; --数据只在实务内部有效 不加这句代码是在session内部有效

Oracle allocates segments for a temporary table when the first INSERT into that table is issued. (This can be an internal insert operation issued by CREATE TABLE AS SELECT.) The first INSERT into a temporary table allocates the segments for the table and its indexes, creates the root page for the indexes, and allocates any LOB segments.

Segments for a temporary table are allocated in a temporary tablespace of the user who created the temporary table.

Oracle drops segments for a transaction-specific temporary table at the end of the transaction and drops segments for a session-specific temporary table at the end of the session. If other transactions or sessions share the use of that temporary table, the segments containing their data remain in the table.

解释：oracle为每个事物分配单独的块来存储数据在事物结束的时候直接把块清除也就是说删除的速度很快插入时候也不会跟其他事物冲突两个事物的同一个临时表的操作相当于两张单独的表
- 不能直接into到集合：
declare

type t_names is table of varchar2(200);

l_names t_names := t_names();

begin

--下面代码报错

--select o.organ_name into l_names from pub_organ o;

end;

20131212 周四

oracle pl/sql 程序设计第13章其他数据类型
- Boolean ：plsql支持，sql不支持。
20131213 周五

oracle pl/sql 程序设计第13章DML和事务管理
- 隐式游标，可以在DML语句之后获取相关信息。
sql%found

sql%notfound

sql%rowcount
- 从DML中获取返回信息
  declare
l_organid pub_organ.organ_id%type;

l_organname pub_organ.organ_name%type;

begin

update pub_organ o

set o.organ_name = 'new organ'

where o.organ_id = 'O50649765'

returning o.organ_id, o.organ_name into l_organid, l_organname; --返回修改后的信息

dbms_output.put_line(l_organid);

dbms_output.put_line(l_organname); --new organ

delete from pub_stru s where s.organ_id = 'O50649765';

delete from pub_organ o

where o.organ_id = 'O50649765'

returning o.organ_id, o.organ_name into l_organid, l_organname; --返回删除前的信息

dbms_output.put_line(l_organid);

dbms_output.put_line(l_organname); --new organ

l_organid := 'newID';

l_organname := 'newName';

--没有的情况下将不执行into操作也不报错这和select into 不一样

update pub_organ o

set o.organ_name = 'new organ'

where o.organ_id = 'ＮＥＶＥＲ'

returning o.organ_id, o.organ_name into l_organid, l_organname;

dbms_output.put_line(l_organid); --newID

dbms_output.put_line(l_organname); --newName

dbms_output.put_line(sql%rowcount); --0

end;
- 事物中单条语句报错与整个事务的影响：
java示例：

@Test

    public void test() throws SQLException

    {

        DataSource ds = DataSourceGen.getDataSourceFromXML();

        Connection conn = ds.getConnection();

        try {

            //禁用自动提交

            conn.setAutoCommit(false);

            //这句代码不报错

            PreparedStatement ps = conn.prepareStatement("update pub_organ o set o.organ_name = '221112' where o.organ_id = 'O50649765'");

            ps.executeUpdate();

            //这句代码报错报错后数据库会自动回滚当前出错的代码但是上一条的代码不会自动回滚等待commit或者rollback代码来处理

            ps = conn.prepareStatement("update pub_organ o set o.in_use = '11' where o.organ_id = 'O50649765'");

            ps.executeUpdate();



            //这句代码执行不到

            ps = conn.prepareStatement("update pub_organ o set o.organ_name = 'new name' where o.organ_id = 'O50649765'");

            ps.executeUpdate();



        } catch (Exception e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

        finally{

            //close会触发commit 所以这里第一条语句执行成功第二条执行失败

            conn.close();

        }



    }

plsql示例：

如果下面代码是一个存储过程：

begin

update pub_organ o

set o.organ_name = '221112'

where o.organ_id = 'O50649765';

--下面代码报错

update pub_organ o set o.in_use = '11' where o.organ_id = 'O50649765';

update pub_organ o

set o.organ_name = 'new name'

where o.organ_id = 'O50649765';

end;

在java中调用这个存储过程。在执行错误代码的时候报错，而存储过程内部没有捕捉到异常，异常会跑出到java层，java层可以commit或者rollback，如果没有，那么将在conn的close的时候自动commit。

所以，如果是存储过程，应该将异常在存储过程内部处理掉，不要抛出到应用层，应该返回错误信息给应用层。

如：

declare

l_message varchar2(100);

begin

update pub_organ o

set o.organ_name = '221112'

where o.organ_id = 'O50649765';

update pub_organ o set o.in_use = '11' where o.organ_id = 'O50649765';

update pub_organ o

set o.organ_name = 'new name'

where o.organ_id = 'O50649765';

exception

when others then

rollback;

l_message := 'error';

end;
- 自治事务：如果对一个过程设置了自治事务，那么调用它的外层和它本身使用两个事务，互不影响。
oracle pl/sql 程序设计第15章数据提取
- 游标的基本概念：
游标并不是指向数据库表的指针，而是指向虚拟表的，这张虚拟表只在查询期间存在，为了满足读一致性。游标的结果集是游标打开那个时间点的有效数据。不是fetch时的实时数据。

declare

type cur_organ is ref cursor return pub_organ%rowtype;

l_cur cur_organ;

type organ is record(

organ_id VARCHAR2(30),

organ_code VARCHAR2(30),

organ_name VARCHAR2(80),

short_name VARCHAR2(40),

organ_type VARCHAR2(10),

workplace_id VARCHAR2(30),

begin_date CHAR(8),

end_date CHAR(8),

scn INTEGER,

in_use CHAR(1));

l_organ organ;

--也可以使用下面代码至今为止所有的into操作不强制要求类型是%rowtype

--l_organ pub_organ%rowtype;

begin

open l_cur for

select * from pub_organ o where o.organ_id = 'O50649765';

update pub_organ o

set o.organ_name = 'newname'

where o.organ_id = 'O50649765';

fetch l_cur

into l_organ;

--输出"江西省全南县供电有限责任公司" 不是newname 符合读一致性

dbms_output.put_line(l_organ.organ_name);

close l_cur;

end;
- SQL查询的结果集是存放在SGA（系统全局区域）中的。数据库针对这个结果集维护一个指针，实现游标的功能。
- 游标变量是一个真正的变量，这个记录，集合是不一样的。
  
  declare
type cur_organ is ref cursor return pub_organ%rowtype;

l_cur1 cur_organ;

l_cur2 cur_organ;

l_organ pub_organ%rowtype;

begin

--O50649765 江西省全南县供电有限责任公司

open l_cur1 for

select * from pub_organ o where o.organ_id = 'O50649765';

-- 此时两个游标变量指向同一片内存区域

l_cur2 := l_cur1;

--O50649773 江西省定南县供电有限责任公司

open l_cur2 for

select * from pub_organ o where o.organ_id = 'O50649773';

--更改l_cur2的查询 l_cur1的结果也被改变

fetch l_cur1

into l_organ;

--输出"江西省定南县供电有限责任公司"

dbms_output.put_line(l_organ.organ_name);

--只需关闭一个

close l_cur1;

end;
- 游标属性
sql%found

sql%notfound

sql%rowcount

sql%isopen

sql%bulk_rowcount

sql%bulk_exceptions

如果是隐式游标，那么应该在查询代码之后即可执行游标的相关属性，因为中间执行了其他查询后，sql%的信息就是新查询的了。
- select into是典型的隐式游标。它的缺点是会抛出异常no_data_found和too_many_rows。每次使用select into都要考虑到这两个异常的处理情况，所以可以考虑养成显示声明游标的习惯。
- 一个显示游标的例子：
create or replace package p_cursors is

cursor cur_organ is

select * from pub_organ o where rownum < 100;

end p_cursors;

declare

--声明的类型应该是游标%rowtype

l_organ p_cursors.cur_organ%rowtype;

begin

open p_cursors.cur_organ;

loop

fetch p_cursors.cur_organ

into l_organ;

--游标在达到最后一行后让可以fetch

--只是fetch的还是最后一行的数据

--exit应该紧接着fetch 然后才是业务处理

exit when p_cursors.cur_organ%notfound;

dbms_output.put_line(l_organ.organ_name);

end loop;

--关闭

close p_cursors.cur_organ;

exception

when others then

--关闭

close p_cursors.cur_organ;

end;

思考：包中的游标是session级别的吗，支持并发吗？
- 游标的关闭
  oracle会尽量的在游标作用域过期的时候自动关闭游标（但是不包括游标变量），游标变量必须被显示关闭。但是oracle在嵌套的匿名块中不能自动关闭游标。而且包中的游标也不会自动关闭，所以要养成显示关闭游标。
- 游标变量一个最大的用途是可以在不同的plsql程序间传递查询的结果集。这种查询传递的是引用，引用指向的查询结果集放在SGA，但是如果你使用集合来传递，那么集合是放在PGA的，需要复制集合数据来实现值传递
- sys_refcursor是一个弱类型的游标变量，系统自带声明。
- 游标变量存在PGA，所指向的游标对象存在SGA。声明游标变量的时候不会创建游标对象，只有在open for操作赋予游标变量查询语句的时候才会创建游标对象。在已经open for的基础上再次open for，上一个游标对象如果没有关闭，仍然会处于打开状态，虽然已经找不到了。
- 约束：
  游标变量不能在包中定义。
  不能使用比值运算。如两个游标变量是否相等。
  不能给一个游标变量赋null值。
20131216 周一

oracle pl/sql 程序设计第14章动态SQL和动态PL/SQL
- 代码示例：
  declare
l_organ pub_organ%rowtype;

begin

--执行查询

execute immediate 'select * from pub_organ o where o.organ_id = :organ_id'

into l_organ

using 'O50649765';

dbms_output.put_line(l_organ.organ_name);

--执行DML

execute immediate 'create table pub_organ_' || to_char(sysdate, 'DD');

--执行PLSQL

execute immediate 'begin proc_name_' || to_char(sysdate, 'DD') || ' end;';

end;
- execute immediate后的sql可以是除了多行查询之外的任何sql语句。
- 多行查询使用open for。
  declare
l_organ pub_organ%rowtype;

l_cur sys_refcursor;

begin

open l_cur for 'select /* zjf_flag100 */ * from pub_organ o where o.organ_name like :oname'

using '国家%';

loop

fetch l_cur

into l_organ;

exit when l_cur%notfound;

dbms_output.put_line(l_organ.organ_name);

end loop;

close l_cur;

end;
- 可以绑定变量的内容不包含表明列名where关键字等，因为去除绑定内容后，oracle要保证这个sql可以正确解析。不可以绑定的部分应该使用字符串拼接。绑定是为了使用sql解析的缓存，防止重复解析。
- 动态sql的out参数：在使用returnling字句的时候，需要out参数。如：
  declare
l_organ_name pub_organ.organ_name%type;

begin

execute immediate 'update pub_organ o set o.organ_name = :newname1 where o.organ_id = :organ_id returning o.organ_name into :newname'

using 'newname', 'O50649765', out l_organ_name;

dbms_output.put_line(l_organ_name);

end;
- 重复的特殊情况：如果是sql语句，保证重复的参数也要用不同的占位符名字，如上面那个例子newname1和newname1，如果是plsql那么重复的参数应该使用相同的占位符名字。
- null的特殊情况：不能直接using null。但是可以使用using to_number(null)或者赋值为null的变量。
- 包的变量的有效性局限在会话级别上：
create or replace package p_globle is

g_num number := 0;

end;

create or replace function f_package_test return number is

begin

return p_globle.g_num;

end;

create or replace procedure p_package_test is

begin

p_globle.g_num := 1;

end;

public static void main(String[] args) {



        DataSource ds = DataSourceGen.getDataSourceFromXML();

        Connection conn = null;

        try {

            conn = ds.getConnection();

            CallableStatement cs = conn.prepareCall("{call p_package_test()}");

            cs.execute();





            cs = conn.prepareCall("{? = call f_package_test()}");

            cs.registerOutParameter(1, Types.NUMERIC);

            cs.execute();

            System.out.println(cs.getDouble(1));//还是那个connection 输出1

            conn.close();





            conn = ds.getConnection();//重新建立一个连接

            cs = conn.prepareCall("{? = call f_package_test()}");

            cs.registerOutParameter(1, Types.NUMERIC);

            cs.execute();

            System.out.println(cs.getDouble(1));//输出0

        } catch (SQLException e) {

            e.printStackTrace();

        } finally {

            if (conn != null) {

                try {

                    conn.close();

                } catch (SQLException e) {

                    e.printStackTrace();

                }

            }

        }
相关阅读:
003.Heartbeat MySQL双主复制
 001.常见监控简介
 微服务探索与实践—服务注册与发现
 设计模式之建造者模式
 .NET Core 3.0之深入源码理解Startup的注册及运行
 【译】.NET Core 3.0 Preview 3中关于ASP.NET Core的更新内容
 C#并发编程之异步编程(三)
设计模式之中介者模式
 设计模式之单例模式
 设计模式之装饰器模式
原文地址：https://www.cnblogs.com/xiaolang8762400/p/7078687.html

oracle pl/sql 程序设计 历史笔记整理

20131016 周三

oracle pl/sql 程序设计 第2章 创建并运行pl/sql代码

oracle pl/sql 程序设计 第3章 语言基础

oracle pl/sql 程序设计 第4章 条件和顺序控制

20131018 周五

oracle pl/sql 程序设计 第5章 用循环进行迭代处理

oracle pl/sql 程序设计 第6章 异常处理

20131106 周三

oracle pl/sql 程序设计 第7章 使用数据

20131107 周四

oracle pl/sql 程序设计 第8章字符串

oracle pl/sql 程序设计 第9章数字

20131108 周五

oracle pl/sql 程序设计 第10章日期

oracle pl/sql 程序设计 第11章记录类型

20131211 周三

oracle pl/sql 程序设计 第12章集合

20131212 周四

oracle pl/sql 程序设计 第13章其他数据类型

20131213 周五

oracle pl/sql 程序设计 第13章DML和事务管理

oracle pl/sql 程序设计 第15章数据提取

20131216 周一

oracle pl/sql 程序设计 第14章动态SQL和动态PL/SQL

oracle pl/sql 程序设计历史笔记整理

oracle pl/sql 程序设计第2章创建并运行pl/sql代码

oracle pl/sql 程序设计第3章语言基础

oracle pl/sql 程序设计第4章条件和顺序控制

oracle pl/sql 程序设计第5章用循环进行迭代处理

oracle pl/sql 程序设计第6章异常处理

oracle pl/sql 程序设计第7章使用数据

oracle pl/sql 程序设计第8章字符串

oracle pl/sql 程序设计第9章数字

oracle pl/sql 程序设计第10章日期

oracle pl/sql 程序设计第11章记录类型

oracle pl/sql 程序设计第12章集合

oracle pl/sql 程序设计第13章其他数据类型

oracle pl/sql 程序设计第13章DML和事务管理

oracle pl/sql 程序设计第15章数据提取

oracle pl/sql 程序设计第14章动态SQL和动态PL/SQL