oracle存储过程-学习笔记1

1.存储过程定义
create or replace procedure usp_learing
(
 p_para1  varchar2:='参数一'
 p_para2  nvarchar2 default '参数二'
 p_para3 out varchar2,
 p_para4 in out  varcha2
)
is
begin
declare
 v_para5  varchar2(20);
begin
 v_para5:='输入输出：'||p_para4;
 p_para3:='输出：'||p_para1||pa_para2;
 p_para4:=v_para5;
end;
end usp_learing;

 

2.存储过程维护
删除存储过程
drop procedure procedure_name;

编译存储过程
alter procedure procedure_name compile;

与存储过程相关的几个查询
--查看无效的存储过程
select object_name
from user_objectse
where status='invalid'
and object_type='procedure'

--查看存储过程代码
select text
from user_source
where name=procedure_name

3.调用存储过程
在sql*plus中调用存储过程时，需要用call或execute命令，而在pl/sql块中可以直接引用。

无需参数的存储过程，可直接引用存储过程名；
exec usp_outtime

带有输入参数的存储过程，需要为输入参数提供数据值；
带有输出参数的存储过程，需要使用变量接收输出结果；
带有输入输出参数的存储过程，调用时需要使用具有输入值的变量。

当为参数传递变量或者数据时，可以采用位置传递、名称传递和组合传递三种方法。

declare
 v_para1  varchar2(10);
 v_para2  nvarchar2(10);
 v_para3  varchar2(30);
 v_para4  varchar2(30);
begin
 --call the procedure
 v_para1:='123'；
 v_para2:='456';
 v_para4:='789';

 --位置传递
 usp_learing(v_para1,v_para2,v_para4);
 
 --值传递
 usp_learing(p_para1=>v_para1,p_para2=>v_para2,p_para3=>v_para3,p_para4=>v_para4);

 --组合传递
 usp_learing(v_para2,v_para2,p_para3=>v_para3,p_para4=>v_para4);
 dbms_output.put_line(v_para3);
 dbms_output.put_line(v_para4);
end 

4.存储过程中常用的复合数据类型、CTE
记录record，单行多列
表table，多行多列
嵌套表table，多行多列
变长数组varry，多行单列
common table expression(CTE)

自定义记录变量
type type_name is record
(
 filed_declaration,…
);
identifier type_name;

使用%rowtype属性定义记录变量
identifier [table_name|view_name]%rowtype;
--identifier记录变量的名称
 
pl/sql记录(record)--单行多列
declare
 type type_dz_record is record
 (
  v_xh rx_dz_nc.xh%type,--序号
  v_dz rx_dz_nc.dz%type,--地址串
  v_xsbj char(1) --虚实标记
 )；
 dz_record type_dz_record;
 begin
 select xh,dz,xsbj
 into dz_record
 from rx_dz_nc
 where xh=&xh;
 dbms_output.put_line(dz_record.v_xh);
 dbms_output.put_line(dz_record.v_dz);
 end;

declare
 dz_record rx_dz_nc%rowtype;
 begin
 select xh,dz,xsbj
 into dz_record
 from rx_dz_nc
 where xh=&xh;
 dbms_output.put_line(dz_record.XH);
 dbms_output.put_line(dz_record.DZ);
 end;

pl/sql表(table)--多行多列
type type_name is table of element_type [not null] index by key_type;
identifier type_name;

--type_name用于指定表类型的名称；element_type用于指定表的数据类型；not null表示不允许引用空元素；key_type用于指定表下标的数据类型(binary_integer、pls_integer

或varchar2);identifier用于定义变量的名称。

declare
 type dz_table_type is table of rx_dz_nc%rowtype index by binary_integer;
 dz_table dz_table_type;
 bigin
 select xh,dz,xsbj bulk collect into dz_table
 from rx_dz_nc;
 dbms_output.put_line('地址：'||dz_table(1).dz);
 end
 
从oracle9i开始，允许使用varchar2定义表的下标。当使用varchar2定义下标时，会按照下标值的升序方式确定元素顺序。

declare type dz_table_type is table of nvarchar2(30)
 index by varchar2(20);
dz_table dz_table_type;
begin
 dz_table('张三'):=1;
 dz_table('李四'):=2;
 dz_table('王五'):=3;
 dz_table('赵六'):=4;
 dbms_output.put_line('第一个元素'||dz_table.first);
 dbms_output.put_line('王五的前一个元素'||dz_table.prior('王五'));
 dbms_output.put_line('李四的后一个元素'||dz_table.next('李四'));
 dbms_output.put_line('最后一个元素'||dz_table.last);
end

pl/sql嵌套表(table)
pl/sql嵌套表用于处理pl/sql集合的数据类型，表的下标以1开始，并且元素个数无限制，可以作为表列的数据类型使用。

type type_name is table of element_type;
identifier type_name;
--type
--element_type用于指定嵌套表的数据类型

使用嵌套时，需要使用其构造方法初始化嵌套表变量。
decalre
type dz_table_type is table of rx_dz_nc%rowtype;
dz_table dz_table_type;
begin
 select xh,dz,xsbj bulk collect into dz_table
 from rx_dz_nc;
 dbms_output.put_line('地址：'||dz_table(1).dz);
end;

变长数组(varray)
varry用于处理pl/sql集合的数据类型，表的小标以1开始，并且元素个数有限制，可以作为表列的数据类型使用。
type type_name is varray(size_limit) of element_type [not null];
identifier type_name;

使用varray时，需要使用其构造方法初始化varray元素。

declare type dz_array_type is varray(20) of rx_dz_nc.dz%type;
 dz_array dz_array_type:=dz_array_type('123','12321');
begin
 select dz into dz_array(1)
 from rx_dz_nc
 where xh=&xh;
 dbms_output.put_line('地址1：'||dz_array(1));
 dbms_output.put_line('地址2：'||dz_array(2));
end

declare type dz_array_type is varray(20) of rx_dz_nc.dz%type;
 dz_array dz_array_type;
begin
 select dz bulk collect into dz_array
 from rx_dz_nc
 where rownum<=20;
 dbms_output.put_line('地址1：'||dz_array(1));
 dbms_output.out_line('地址2：'||dz_array(2)); 
end

common table expression(CTE)
common table expression兼具视图(view)和派生数据表(derived table)的能力，可以称为临时的视图，或是在同一批子查询语法中可重复使用的派生数据表。

with <CTE 名称>
as
(
 <sql查询语句>
)
select <列名称列表> from <CTE 名称>；

例：
with tempdz
as
(
 select xh,dz
 from rx_dz_nc
)
select xh,dz from tempdz;

 

5.存储过程中的异常处理
异常（exception）是一种pl/sql标识符，包括预定义异常、非预定义异常和自定义异常；

raise_application_error用于自定义错误消息，并且消息号必须在-20000—20999之间。

create or replace procedure usp_exception
 (
 p_pcid  integer,--批次id
 p_fm number,--分母
 p_fz number,--分子
 p_result out number --结果
 )

 is
 v_raise exception;--异常处理
 type type_table_pcmx is table of t_bl_pcmx%rowtype;
 table_pcmx type_table_pcmx;

  begin
 if p_fz=o then
 raise v_raise;
 end if;

 p_result:=p_fm/p_fz

 select id,pcid,xmid,zjxmid,bz,czsj,cjsj bulk collect into table_pcmx
 from t_bl_pcmx
 where pcid=p_pcid;

 Exception
 when v_raise then raise_application_error(-20010,'error:分子为零！');
 when no_data_found then e_application_error(-20011,'error:批次明细不存在！');
 when othersd then raise_application_error(-20012,'error:数据错误！')；
 end;

6.存储过程的事务处理
事务用于确保数据的一致性，由一组相关的DML语句组成，改组DML语句所执行的操作要么全部确认，要么全部取消。

commit提交
savapoint保存点--在当前事务中，标记事务的保存点。
rollback
rollback to savepoint

当执行ddl、dcl语句，或者退出sql*plus时，会自动提交事务；
事务期间应避免与使用者互动；
查询数据期间，尽量不要启动事务；
尽可能让事务持续的越短越好；
在事务中尽可能存取最少的数据量。

create or replace procedure usp_shlwu
 is
 begin

 insert into table_test;
 value{1,'200904220','2009042201'};
 commit;
 savepoint savepoint1;

 insert into table_test
 value{2,'2009042201','2009042202'};
 dbms_transaction.savepoint('savepoint2');

 update table_test
 set vcode='2009042202'
 where ild=2;
 commit;

 exception
 when dup_val_on_index then
  rllback to savepoint savepoint1;
 --dbms_transaction.rollback_savepoint(savepoint1);
  raise_application_error(-20010,'error':违反唯一索引约束！')
 when others then
  rollback;
 --dbms_transaction.rollback;
 end usp_shlwu;

 

7.存储过程的优化
sql语句的优化
索引的优化
游标的优化

select语句的执行顺序
(8)select (9)[distinct]
(11)传回结果列表[into 新数据表名称]
(1)from 数据表
(3)[inner|left|right] join 数据表
(2)on <数据表join的条件>
(4)[where <过滤条件>]
(5)[group by <群组语法>]
(6)[with {cube|rollup}]
(7)[having <过滤条件>]
(10)order by <排序条件>[asc|desc]

1）子查询select中，尽量避免使用*，而选择用where得出有用的行，降低传送过多数据造成符合。
2）尽量避免反复访问同一张或几张表，尤其是数据量大的表，可以先根据条件提取数据到临时表中，然后再做链接。
3）尽量避免使用游标，游标的效率较差，如果游标操作的数据超过1万行，经应该改写，如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作；
4）where字句写法，必须考虑语句顺序，应该根据索引顺序、范围大小来确定条件字句的前后顺序，尽可能让字句顺序与索引顺序相一致，范围从大到小；
5）不要在where子句的“=”左边进行函数、算数运算或其它表达式运算，否则系统将可能无法正确使用索引。
6）注意存储过程中参数和数据类型的关系，并注意表之间连接的数据类型，避免不同数据类型之间的连接；
7）尽可能的使用索引字段作为查询条件，尤其是聚簇索引。