oracle存储过程超详细使用基础手册.doc

Oracle存放过程总结
1、创建存放过程
create or replace procedure test(var_name_1 in type,var_name_2 out type) as
--申明变量(变量名 变量类型)
begin
--存放过程实施体
end test;
打印出输入时间信息
E.g:
create or replace procedure test(workDate in Date) is
begin
dbms_output.putline(The input date is:||to_date(workDate, yyyy-mm-dd));
end test;
2、变量赋值
变量名 := 值;
E.g：
create or replace procedure test(workDate in Date) is
x number(4,2);
 begin
 x := 1;
end test;
3、判定语句:
if 比较式 then begin end; end if;
E.g
create or replace procedure test(x in number) is
begin
        if x >0 then
         begin
        x := 0 - x;
        end;
    end if;
    if x = 0 then
       begin
        x: = 1;
    end;
    end if;
end test;
4、For 循环
For ... in ... LOOP
--实施语句
end LOOP;
(1)循环遍历游标
create or replace procedure test() as
Cursor cursor is select name from student;
name varchar(20);
begin
for name in cursor LOOP
begin
 dbms_output.putline(name); 
end;
end LOOP;
end test;
(2)循环遍历数组
 create or replace procedure test(varArray in myPackage.TestArray) as
--(输入参数varArray 是自定义数组类型，定义方法见标题6)
i number;
begin
i := 1;  --存放过程数组是起始位置是从1开始，和java、C、C++等语言不一样。因为在Oracle中本是没有数组概念，数组其实就是一张
--表(Table),每个数组元素就是表中一个统计，所以遍历数组时就相当于从表中第一条统计开始遍历
for i in 1..varArray.count LOOP     
dbms_output.putline(The No. || i ||record in varArray is: ||varArray(i));   
 end LOOP;
end test;
5、While 循环
while 条件语句 LOOP
begin
end;
end LOOP;
E.g
create or replace procedure test(i in number) as
begin
while i < 10 LOOP
begin    
 i:= i + 1;
end;
end LOOP;
 end test;
6、数组
首先明确一个概念：Oracle中本是没有数组概念，数组其实就是一张表(Table),每个数组元素就是表中一个统计。
使用数组时，用户能够使用Oracle已经定义好数组类型，或可依据自己需要定义数组类型。
(1)使用Oracle自带数组类型
x array; --使用时需要需要进行初始化
e.g:
create or replace procedure test(y out array) is
 x array;  
 begin
x := new array();
y := x;
end test;
(2)自定义数组类型
(自定义数据类型时，提议经过创建Package方法实现，方便于管理)
E.g (自定义使用参见标题4.2) create or replace package myPackage is
  -- Public type declarations   type info is record(     name varchar(20),     y number);
  type TestArray is table of info index by binary_integer;   --此处申明了一个TestArray类型数据，其实其为一张存放Info数据类型Table而已，及TestArray 就是一张表，有两个字段，一个是
name，一个是y。需要注意是此处使用了Index by binary_integer 编制该Table索引项，也能够不写，直接写成：type TestArray is
table of info，假如不写话使用数组时就需要进行初始化：varArray myPackage.TestArray; varArray := new myPackage.TestArray();
end TestArray;
7.游标使用
Oracle中Cursor是很有用，用于遍历临时表中查询结果。其相关方法和属性也很多，现仅就常见使用方法做一二介绍：
(1)Cursor型游标(不能用于参数传输)
create or replace procedure test() is  
cusor_1 Cursor is select std_name from student where  ...;  --Cursor使用方法1   cursor_2 Cursor;
begin
select class_name into cursor_2 from class where ...;  --Cursor使用方法2
可使用For x in cursor LOOP .... end LOOP; 来实现对Cursor遍历
end test;
(2)SYS_REFCURSOR型游标，该游标是Oracle以预先定义游标，可作出参数进行传输
create or replace procedure test(rsCursor out SYS_REFCURSOR) is
cursor SYS_REFCURSOR; name varhcar(20);
begin
OPEN cursor FOR select name from student where ... --SYS_REFCURSOR只能经过OPEN方法来打开和赋值
LOOP
 fetch cursor into name   --SYS_REFCURSOR只能经过fetch into来打开和遍历 exit when cursor%NOTFOUND;              --SYS_REFCURSOR中可使用三个状态属性：                                         ---%NOTFOUND(未找到统计信息) %FOUND(找到统计信息)                                         ---%ROWCOUNT(然后目前游标所指向行位置)
 dbms_output.putline(name);
end LOOP;
rsCursor := cursor;
end test;
下面写一个简单例子来对以上所说存放过程使用方法做一个应用：
现假设存在两张表，一张是学生成绩表(studnet)，字段为：stdId,math,article,language,music,sport,total,average,step                   一张是学生课外成绩表(out_school),字段为:stdId,parctice,comment
经过存放过程自动计算出每位学生总成绩和平均成绩，同时，假如学生在课外课程中取得评价为A，就在总成绩上加20分。
create or replace procedure autocomputer(step in number) is
rsCursor SYS_REFCURSOR;
commentArray myPackage.myArray;
math number;
article number;
language number;
music number;
sport number;
total number;
average number;
stdId varchar(30);
record myPackage.stdInfo;
i number;
begin
i := 1;
get_comment(commentArray); --调用名为get_comment()存放过程获取学生课外评分信息
OPEN rsCursor for select stdId,math,article,language,music,sport from student t where t.step = step;
LOOP
fetch rsCursor into stdId,math,article,language,music,sport; exit when rsCursor%NOTFOUND;
total := math + article + language + music + sport;
for i in mentArray.count LOOP 
 record := commentArray(i);    
if stdId = record.stdId then  
 begin     
 if ment = &apos;A&apos; then     
  begin         
 total := total + 20;   
   go to next; --使用go to跳出for循环       
  end;    
end if;  
end;  
end if;
end LOOP;
<<continue>>  average := total / 5;
 update student t set t.total=total and t.average = average where t.stdId = stdId;
end LOOP;
end;
end autocomputer;
--取得学生评论信息存放过程
create or replace procedure get_comment(commentArray out myPackage.myArray) is
rs SYS_REFCURSOR；
record myPackage.stdInfo;
stdId varchar(30);
comment varchar(1);
i number;
begin
open rs for select stdId,comment from out_school
i := 1;
LOOP
 fetch rs into stdId,comment; exit when rs%NOTFOUND;
record.stdId := stdId;
 ment := comment;
recommentArray(i) := record;
i:=i + 1;
end LOOP;
end get_comment;
--定义数组类型myArray
create or replace package myPackage is begin
type stdInfo is record(stdId varchar(30),comment varchar(1));
type myArray is table of stdInfo index by binary_integer;
end myPackage;
项目中有包含存放过程对字符串处理，所以就将在网上查找到资料汇总，做一个信息拼接式总结。
以下信息均来自互联网，贴出来一则自己保留以待以后使用，一则供大家分享。
字符函数——返回字符值
这些函数全全部接收是字符族类型参数(CHR除外)而且返回字符值.
除了尤其说明之外,这些函数大部分返回VARCHAR2类型数值.
字符函数返回类型所受限制和基础数据库类型所受限制是相同。
字符型变量存放最大值：
 VARCHAR2数值被限制为字符(ORACLE 8中为4000字符)
 CHAR数值被限制为255字符(在ORACLE8中是)
 long类型为2GB
 Clob类型为4GB
1、CHR
语法:  chr(x)
功效：返回在数据库字符集中和X拥有等价数值字符。CHR和ASCII是一对反函数。经过CHR转换后字符再经过ASCII转换又得到了原来字
符。
使用位置：过程性语句和SQL语句。
2、CONCAT
语法： CONCAT（string1,string2）
功效：返回string1，而且在后面连接string2。
使用位置：过程性语句和SQL语句。
3、INITCAP
语法：INITCAP（string）
功效：返回字符串每个单词第一个字母大写而单词中其它字母小写string。单词是用.空格或给字母数字字符进行分隔。不是字母
字符不变动。
使用位置：过程性语句和SQL语句。
4、LTRIM
语法：LTRIM（string1,string2）
功效：返回删除从左边算起出现在string2中字符string1。String2被缺省设置为单个空格。数据库将扫描string1，从最左边开始。当
碰到不在string2中第一个字符，结果就被返回了。LTRIM行为方法和RTRIM很相同。
使用位置：过程性语句和SQL语句。
5、NLS_INITCAP
语法：NLS_INITCAP（string[,nlsparams]）
功效：返回字符串每个单词第一个字母大写而单词中其它字母小写string，nlsparams
指定了不一样于该会话缺省值不一样排序序列。假如不指定参数，则功效和INITCAP相同。Nlsparams能够使用形式是：
‘NLS_SORT=sort’
这里sort制订了一个语言排序序列。
使用位置：过程性语句和SQL语句。
6、NLS_LOWER
语法：NLS_LOWER（string[,nlsparams]）
功效：返回字符串中全部字母全部是小写形式string。不是字母字符不变。
      Nlsparams参数形式和用途和NLS_INITCAP中nlsparams参数是相同。假如nlsparams没有被包含，那么NLS_LOWER所作处理和
LOWER相同。
使用位置；过程性语句和SQL语句。
7、NLS_UPPER
语法：nls_upper（string[,nlsparams]）
功效：返回字符串中全部字母全部是大写形式string。不是字母字符不变。nlsparams参数形式和用途和NLS_INITCAP中相同。假如
没有设定参数，则NLS_UPPER功效和UPPER相同。
使用位置：过程性语句和SQL语句。
8、REPLACE
语法：REPLACE（string，search_str[,replace_str]）
功效：把string中全部子字符串search_str用可选replace_str替换，假如没有指定replace_str，全部string中子字符串
search_str全部将被删除。REPLACE是TRANSLATE所提供功效一个子集。
使用位置：过程性语句和SQL语句。
9、RPAD
语法：RPAD（string1,x[,string2]）
功效：返回在X字符长度位置上插入一个string2中字符string1。假如string2长度要比X字符少，就根据需要进行复制。假如string2
多于X字符，则仅string1前面X各字符被使用。假如没有指定string2，那么使用空格进行填充。X是使用显示长度能够比字符串实际长度
要长。RPAD行为方法和LPAD很相同，除了它是在右边而不是在左边进行填充。
使用位置：过程性语句和SQL语句。
10、RTRIM
语法:  RTRIM（string1,[,string2]）
功效:  返回删除从右边算起出现在string1中出现字符string2. string2被缺省设置为单个空格.数据库将扫描string1,从右边开始.当遇
到不在string2中第一个字符,结果就被返回了RTRIM行为方法和LTRIM很相同.
使用位置：过程性语句和SQL语句。
11、SOUNDEX
语法:  SOUNDEX（string）
功效:  返回string声音表示形式.这对于比较两个拼写不一样不过发音类似单词而言很有帮助.
使用位置：过程性语句和SQL语句。
12、SUBSTR
语法:  SUBSTR（string,a[,b]）
功效:  返回从字母为值a开始b个字符长string一个子字符串.假如a是0,那么它就被认为从第一个字符开始.假如是正数,返回字符是从左
边向右边进行计算.假如b是负数,那么返回字符是从string末尾开始从右向左进行计算.假如b不存在,那么它将缺省设置为整个字符
串.假如b小于1,那么将返回NULL.假如a或b使用了浮点数,那么该数值将在处理进行以前首先被却为一个整数.
使用位置：过程性语句和SQL语句。
13、TRANSLATE
   语法:  TRANSLATE(string,from_str,to_str)
   功效:  返回将所出现from_str中每个字符替换为to_str中对应字符以后string. TRANSLATE是REPLACE所提供功效一个超集.
假如from_str比to_str长,那么在from_str中而不在to_str中而外字符将从string中被删除,因为它们没有对应替换字符. to_str不能为空
.Oracle把空字符串认为是NULL,而且假如TRANSLATE中任何参数为NULL,那么结果也是NULL.
使用位置：过程性语句和SQL语句。
14、UPPER
语法: UPPER（string）
功效: 返回大写string.不是字母字符不变.假如string是CHAR数据类型,那么结果也是CHAR类型.假如string是VARCHAR2类型,那么
结果也是VARCHAR2类型.
使用位置: 过程性语句和SQL语句。
字符函数——返回数字
这些函数接收字符参数回数字结果.参数能够是CHAR或是VARCHAR2类型.尽管实际下很多结果全部是整数值,不过返回结果全部是简单NUMBER
类型,没有定义任何精度或刻度范围.
16、ASCII
语法:  ASCII（string）
功效: 数据库字符集返回string第一个字节十进制表示.请注意该函数仍然称作为ASCII.尽管很多字符集不是7位ASCII.CHR和ASCII是互为
相反函数.CHR得到给定字符编码响应字符. ASCII得到给定字符字符编码.
使用位置: 过程性语句和SQL语句。
17、INSTR
语法:  INSTR（string1, string2[a,b]）
功效:  得到在string1中包含string2位置. string1时从左边开始检验,开始位置为a,假如a是一个负数,那么string1是从右边开始进行
扫描.第b次出现位置将被返回. a和b全部缺省设置为1,这将会返回在string1中第一次出现string2位置.假如string2在a和b要求下没有
找到,那么返回0.位置计算是相对于string1开始位置,不管a和b取值是多少.
使用位置: 过程性语句和SQL语句。
18、INSTRB
语法:  INSTRB（string1, string2[a,[b]]）
功效:  和INSTR相同,只是操作对参数字符使用位置是字节.
使用位置: 过程性语句和SQL语句。
19、LENGTH
语法:  LENGTH（string）
功效:  返回string字节单位长度.CHAR数值是填充空格类型,假如string由数据类型CHAR,它结尾空格全部被计算到字符串长度中间.
假如string是NULL,返回结果是NULL,而不是0.
使用位置: 过程性语句和SQL语句。
20、LENGTHB
语法:  LENGTHB（string）
功效:  返回以字节为单位string长度.对于单字节字符集LENGTHB和LENGTH是一样.
使用位置: 过程性语句和SQL语句。
21、NLSSORT
语法: NLSSORT（string[,nlsparams]）
功效: 得到用于排序string字符串字节.全部数值全部被转换为字节字符串,这么在不一样数据库之间就保持了一致性. Nlsparams作用和
NLS_INITCAP中相同.假如忽略参数,会话使用缺省排序.
使用位置: 过程性语句和SQL语句。

oracle 存放过程基础语法
1.基础结构
CREATE OR REPLACE PROCEDURE 存放过程名字
(
    参数1 IN NUMBER,
    参数2 IN NUMBER
) IS
变量1 INTEGER :=0;
变量2 DATE;
BEGIN
END 存放过程名字

2.SELECT INTO STATEMENT
  将select查询结果存入到变量中，能够同时将多个列存放多个变量中，必需有一条
  统计，不然抛出异常(假如没有统计抛出NO_DATA_FOUND)
  例子：
  BEGIN
  SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
      xxxx;
  END;
  ...

3.IF 判定
  IF V_TEST=1 THEN
    BEGIN
       do something
    END;
  END IF;

4.while 循环
  WHILE V_TEST=1 LOOP
  BEGIN
 XXXX
  END;
  END LOOP;

5.变量赋值
  V_TEST := 123;

6.用for in 使用cursor
  ...
  IS
  CURSOR cur IS SELECT * FROM xxx;
  BEGIN
 FOR cur_result in cur LOOP
  BEGIN
   V_SUM :=cur_result.列名1+cur_result.列名2
  END;
 END LOOP;
  END;

7.带参数cursor
  CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;
  OPEN C_USER(变量值);
  LOOP
 FETCH C_USER INTO V_NAME;
 EXIT FETCH C_USER%NOTFOUND;
    do something
  END LOOP;
  CLOSE C_USER;

8.用pl/sql developer debug
  连接数据库后建立一个Test WINDOW
  在窗口输入调用SP代码,F9开始debug,CTRL+N单步调试
相关oracle存放过程若干问题备忘
1.在oracle中，数据表别名不能加as，如：
select a.appname from appinfo a;-- 正确
select a.appname from appinfo as a;-- 错误
 可能，是怕和oracle中存放过程中关键字as冲突问题吧
2.在存放过程中，select某一字段时，后面必需紧跟into，假如select整个统计，利用游标话就另当别论了。
  select af.keynode into kn from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;-- 有into，正确编译
  select af.keynode from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;-- 没有into，编译报错，提醒：Compilation 
  Error: PLS-00428: an INTO clause is expected in this SELECT statement

3.在利用select...into...语法时，必需先确保数据库中有该条统计，不然会报出"no data found"异常。
   能够在该语法之前，先利用select count(*) from 查看数据库中是否存在该统计，假如存在，再利用select...into...
4.在存放过程中，别名不能和字段名称相同，不然即使编译能够经过，但在运行阶段会报错
 select keynode into kn from APPFOUNDATION where appid=aid and foundationid=fid;-- 正确运行
select af.keynode into kn from APPFOUNDATION af where af.appid=appid and af.foundationid=foundationid;-- 运行阶段报错，提醒
ORA-01422:exact fetch returns more than requested number of rows
5.在存放过程中，相关出现null问题
假设有一个表A，定义以下：
create table A(
id varchar2(50) primary key not null,
vcount number(8) not null,
bid varchar2(50) not null -- 外键 
);
假如在存放过程中，使用以下语句：
select sum(vcount) into fcount from A where bid='xxxxxx';
假如A表中不存在bid="xxxxxx"统计，则fcount=null(即使fcount定义时设置了默认值，如：fcount number(8):=0仍然无效，fcount还是会变成null)，这么以后使用fcount时就可能有问题，所以在这里最好先判定一下：
if fcount is null then
    fcount:=0;
end if;
这么就一切ok了。
6.Hibernate调用oracle存放过程
        this.pnumberManager.getHibernateTemplate().execute(
                new HibernateCallback() ...{
                    public Object doInHibernate(Session session)
                            throws HibernateException, SQLException ...{
                        CallableStatement cs = session
                                .connection()
                                .prepareCall("{call modifyapppnumber_remain(?)}");
                        cs.setString(1, foundationid);
                        cs.execute();
                        return null;
                    }
                });

oracle 存放过程语法总结及练习
---------------------------------------------
--1.存放过程之if
clear;
create or replace procedure mydel(
in_a in integer)
as
begin
if in_a<100 then
dbms_output.put_line('小于100.');
elsif in_a<200 then
dbms_output.put_line('大于100小于200.');
else
dbms_output.put_line('大于200.');
end if;
end;
/

set serveroutput on;
begin
mydel(1102);
end;
/
---------------------------------------------

--2.存放过程之case1
clear;
create or replace procedure mydel(
in_a in integer)
as
begin
case in_a
when 1 then
dbms_output.put_line('小于100.');
when 2 then
dbms_output.put_line('大于100小于200.');
else
dbms_output.put_line('大于200.');
end case;
end;
/

set serveroutput on;
begin
mydel(2);
end;
/
------------------------------------------------

--1.存放过程之loop1
clear;
create or replace procedure mydel(
in_a in integer)
as
a integer;
begin
a:=0;
loop
dbms_output.put_line(a);
a:=a+1;
exit when
a>301;
end loop;
end;
/

set serveroutput on;
begin
mydel(2);
end;
/
--------------------------------------------------
--1.存放过程之loop2
clear;
create or replace procedure mydel(
in_a in integer)
as
a integer;
begin
a:=0;
while a<300 loop
dbms_output.put_line(a);
a:=a+1;
end loop;
end;
/

set serveroutput on;
begin
mydel(2);
end;
--------------------------------------------------
--1.存放过程之loop3
clear;
create or replace procedure mydel(
in_a in integer)
as
a integer;
begin
for a in 0..300
loop
dbms_output.put_line(a);
end loop;
end;
/

set serveroutput on;
begin
mydel(2);
end;
/
clear;
select ename,cc:=(case
when comm=null then sal*12;
else (sal+comm)*12;
end case from emp order by salpersal;

----------------------------------------------------
clear;
create or replace procedure getstudentcomments(
i_studentid in int,o_comments out varchar)
as
exams_sat int;
avg_mark int;
tmp_comments varchar(100);
begin
select count(examid) into exams_sat from studentexam
where studentid=i_studentid;
if exams_sat=0 then
tmp_comments:='n/a-this student did not attend the exam!';
else
select avg(mark) into avg_mark from studentexam
where studentid=i_studentid;
case
when avg_mark<50 then tmp_comments:='very bad';
when avg_mark<60 then tmp_comments:='bad';
when avg_mark<70 then tmp_comments:='good';
end case;
end if;
o_comments:=tmp_comments;
end;
/

set serveroutput on;
declare
pp ments%type;
begin
getstudentcomments(8,pp);
dbms_output.put_line(pp);
end;
/
--------------------------------------------------------

delete from emp where empno<6000;
clear;
create or replace procedure insertdata(
in_num in integer)
as