存储过程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 = '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 myNum int default 0;

展开阅读全文