1.存储过程定义create or replace procedure usp_learing( p_para1 varchar2:='参数一' p_para2 nvarchar2 default '参数二' p_para3 out varchar2, p_para4 in out varcha2)isbegindeclare 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_namefrom user_objectsewhere status='invalid'and object_type='procedure'
--查看存储过程代码select textfrom user_sourcewhere 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 tempdzas( 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保存点--在当前事务中，标记事务的保存点。rollbackrollback 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）尽可能的使用索引字段作为查询条件，尤其是聚簇索引。
