mywebcode

浏览: 998401 次

最近访客更多访客>>

u012363178

qq_33632159

cshui

dd_9921

博主相关

博客

微博

相册

留言

关于我

文章分类

全部博客 (2181)

社区版块

存档分类

2014-04 ( 138)
2014-03 ( 64)
2014-02 ( 54)
更多存档...

Oracle存储过程总结

Oracle存储过程总结

1、创建存储过程

createorreplaceproceduretest(var_name_1intype,var_name_2outtype)as

--声明变量(变量名变量类型)

begin

--存储过程的执行体

endtest;

打印出输入的时间信息

E.g:

createorreplaceproceduretest(workDateinDate)is

begin

dbms_output.putline(Theinputdateis:||to_date(workDate,yyyy-mm-dd));

endtest;

2、变量赋值

变量名:=值;

E.g：

createorreplaceproceduretest(workDateinDate)is

xnumber(4,2);

begin

x:=1;

endtest;

3、判断语句:

if比较式thenbeginend;endif;

E.g

createorreplaceproceduretest(xinnumber)is

begin

ifx>0then

begin

x:=0-x;

end;

endif;

ifx=0then

begin

x:=1;

end;

endif;

endtest;

4、For循环

For...in...LOOP

--执行语句

endLOOP;

(1)循环遍历游标

createorreplaceproceduretest()as

Cursor1cursorisselectnamefromstudent;

namevarchar(20);

begin

fornameincursorLOOP

begin

dbms_output.putline(name);

end;

endLOOP;

endtest;

(2)循环遍历数组

createorreplaceproceduretest(varArrayinmyPackage.TestArray)as

--(输入参数varArray是自定义的数组类型，定义方式见标题6)

inumber;

begin

i:=1;--存储过程数组是起始位置是从1开始的，与java、C、C++等语言不同。因为在Oracle中本是没有数组的概念的，数组其实就是一张

--表(Table),每个数组元素就是表中的一个记录，所以遍历数组时就相当于从表中的第一条记录开始遍历

foriin1..varArray.countLOOP

dbms_output.putline(TheNo.||i||recordinvarArrayis:||varArray(i));

endLOOP;

endtest;

5、While循环

while条件语句LOOP

begin

end;

endLOOP;

E.g

createorreplaceproceduretest(iinnumber)as

begin

whilei<10LOOP

begin

i:=i+1;

end;

endLOOP;

endtest;

6、数组

首先明确一个概念：Oracle中本是没有数组的概念的，数组其实就是一张表(Table),每个数组元素就是表中的一个记录。

使用数组时，用户可以使用Oracle已经定义好的数组类型，或可根据自己的需要定义数组类型。

(1)使用Oracle自带的数组类型

xarray;--使用时需要需要进行初始化

e.g:

createorreplaceproceduretest(youtarray)is

xarray;

begin

x:=newarray();

y:=x;

endtest;

(2)自定义的数组类型(自定义数据类型时，建议通过创建Package的方式实现，以便于管理)

E.g(自定义使用参见标题4.2)createorreplacepackagemyPackageis

--Publictypedeclarationstypeinfoisrecord(namevarchar(20),ynumber);

typeTestArrayistableofinfoindexbybinary_integer;--此处声明了一个TestArray的类型数据，其实其为一张存储Info数据类型的Table而已，及TestArray就是一张表，有两个字段，一个是

name，一个是y。需要注意的是此处使用了Indexbybinary_integer编制该Table的索引项，也可以不写，直接写成：typeTestArrayis

tableofinfo，如果不写的话使用数组时就需要进行初始化：varArraymyPackage.TestArray;varArray:=newmyPackage.TestArray();

endTestArray;

7.游标的使用Oracle中Cursor是非常有用的，用于遍历临时表中的查询结果。其相关方法和属性也很多，现仅就常用的用法做一二介绍：

(1)Cursor型游标(不能用于参数传递)

createorreplaceproceduretest()is

cusor_1Cursorisselectstd_namefromstudentwhere...;--Cursor的使用方式1cursor_2Cursor;

begin

selectclass_nameintocursor_2fromclasswhere...;--Cursor的使用方式2

可使用ForxincursorLOOP....endLOOP;来实现对Cursor的遍历

endtest;

(2)SYS_REFCURSOR型游标，该游标是Oracle以预先定义的游标，可作出参数进行传递

createorreplaceproceduretest(rsCursoroutSYS_REFCURSOR)is

cursorSYS_REFCURSOR;namevarhcar(20);

begin

OPENcursorFORselectnamefromstudentwhere...--SYS_REFCURSOR只能通过OPEN方法来打开和赋值

LOOP

fetchcursorintoname--SYS_REFCURSOR只能通过fetchinto来打开和遍历exitwhencursor%NOTFOUND;--SYS_REFCURSOR中可使用三个状态属性：---%NOTFOUND(未找到记录信息)%FOUND(找到记录信息)---%ROWCOUNT(然后当前游标所指向的行位置)

dbms_output.putline(name);

endLOOP;

rsCursor:=cursor;

endtest;

下面写一个简单的例子来对以上所说的存储过程的用法做一个应用：

现假设存在两张表，一张是学生成绩表(studnet)，字段为：stdId,math,article,language,music,sport,total,average,step一张是学生课外成绩表(out_school),字段为:stdId,parctice,comment

通过存储过程自动计算出每位学生的总成绩和平均成绩，同时，如果学生在课外课程中获得的评价为A，就在总成绩上加20分。

createorreplaceprocedureautocomputer(stepinnumber)is

rsCursorSYS_REFCURSOR;

commentArraymyPackage.myArray;

mathnumber;

articlenumber;

languagenumber;

musicnumber;

sportnumber;

totalnumber;

averagenumber;

stdIdvarchar(30);

recordmyPackage.stdInfo;

inumber;

begin

i:=1;

get_comment(commentArray);--调用名为get_comment()的存储过程获取学生课外评分信息

OPENrsCursorforselectstdId,math,article,language,music,sportfromstudenttwheret.step=step;

LOOP

fetchrsCursorintostdId,math,article,language,music,sport;exitwhenrsCursor%NOTFOUND;

total:=math+article+language+music+sport;

foriin1..commentArray.countLOOP

record:=commentArray(i);

ifstdId=record.stdIdthen

begin

ifrecord.comment='A'then

begin

total:=total+20;

gotonext;--使用goto跳出for循环

end;

endif;

end;

endif;

endLOOP;

<<continue>>average:=total/5;

updatestudenttsett.total=totalandt.average=averagewheret.stdId=stdId;

endLOOP;

end;

endautocomputer;

--取得学生评论信息的存储过程

createorreplaceprocedureget_comment(commentArrayoutmyPackage.myArray)is

rsSYS_REFCURSOR；

recordmyPackage.stdInfo;

stdIdvarchar(30);

commentvarchar(1);

inumber;

begin

openrsforselectstdId,commentfromout_school

i:=1;

LOOP

fetchrsintostdId,comment;exitwhenrs%NOTFOUND;

record.stdId:=stdId;

record.comment:=comment;

recommentArray(i):=record;

i:=i+1;

endLOOP;

endget_comment;

--定义数组类型myArray

createorreplacepackagemyPackageisbegin

typestdInfoisrecord(stdIdvarchar(30),commentvarchar(1));

typemyArrayistableofstdInfoindexbybinary_integer;

endmyPackage;

项目中有涉及存储过程对字符串的处理，所以就将在网上查找到的资料汇总，做一个信息拼接式的总结。

以下信息均来自互联网，贴出来一则自己保存以待以后使用，一则供大家分享。

字符函数——返回字符值

这些函数全都接收的是字符族类型的参数(CHR除外)并且返回字符值.

除了特别说明的之外,这些函数大部分返回VARCHAR2类型的数值.

字符函数的返回类型所受的限制和基本数据库类型所受的限制是相同的。

字符型变量存储的最大值：

VARCHAR2数值被限制为2000字符(ORACLE8中为4000字符)

CHAR数值被限制为255字符(在ORACLE8中是2000)

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.基本结构
CREATEORREPLACEPROCEDURE存储过程名字
(
参数1INNUMBER,
参数2INNUMBER
)IS
变量1INTEGER:=0;
变量2DATE;
BEGIN

END存储过程名字

2.SELECTINTOSTATEMENT
将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条
记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)
例子：
BEGIN
SELECTcol1,col2into变量1,变量2FROMtypestructwherexxx;
EXCEPTION
WHENNO_DATA_FOUNDTHEN
xxxx;
END;
...

3.IF判断
IFV_TEST=1THEN
BEGIN
dosomething
END;
ENDIF;

4.while循环
WHILEV_TEST=1LOOP
BEGIN
XXXX
END;
ENDLOOP;

5.变量赋值
V_TEST:=123;

6.用forin使用cursor
...
IS
CURSORcurISSELECT*FROMxxx;
BEGIN
FORcur_resultincurLOOP
BEGIN
V_SUM:=cur_result.列名1+cur_result.列名2
END;
ENDLOOP;
END;

7.带参数的cursor
CURSORC_USER(C_IDNUMBER)ISSELECTNAMEFROMUSERWHERETYPEID=C_ID;
OPENC_USER(变量值);
LOOP
FETCHC_USERINTOV_NAME;
EXITFETCHC_USER%NOTFOUND;
dosomething
ENDLOOP;
CLOSEC_USER;

8.用pl/sqldeveloperdebug
连接数据库后建立一个TestWINDOW
在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试

关于oracle存储过程的若干问题备忘

1.在oracle中，数据表别名不能加as，如：

selecta.appnamefromappinfoa;--正确
selecta.appnamefromappinfoasa;--错误

也许，是怕和oracle中的存储过程中的关键字as冲突的问题吧

2.在存储过程中，select某一字段时，后面必须紧跟into，如果select整个记录，利用游标的话就另当别论了。

selectaf.keynodeintoknfromAPPFOUNDATIONafwhereaf.appid=aidandaf.foundationid=fid;--有into，正确编译
selectaf.keynodefromAPPFOUNDATIONafwhereaf.appid=aidandaf.foundationid=fid;--没有into，编译报错，提示：Compilation
Error:PLS-00428:anINTOclauseisexpectedinthisSELECTstatement

3.在利用select...into...语法时，必须先确保数据库中有该条记录，否则会报出"nodatafound"异常。

可以在该语法之前，先利用selectcount(*)from查看数据库中是否存在该记录，如果存在，再利用select...into...

4.在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会报错

selectkeynodeintoknfromAPPFOUNDATIONwhereappid=aidandfoundationid=fid;--正确运行
selectaf.keynodeintoknfromAPPFOUNDATIONafwhereaf.appid=appidandaf.foundationid=foundationid;--运行阶段报错，提示
ORA-01422:exactfetchreturnsmorethanrequestednumberofrows

5.在存储过程中，关于出现null的问题

假设有一个表A，定义如下：

createtableA(
idvarchar2(50)primarykeynotnull,
vcountnumber(8)notnull,
bidvarchar2(50)notnull--外键
);

如果在存储过程中，使用如下语句：

selectsum(vcount)intofcountfromAwherebid='xxxxxx';

如果A表中不存在bid="xxxxxx"的记录，则fcount=null(即使fcount定义时设置了默认值，如：fcountnumber(8):=0依然无效，fcount还是会变成null)，这样以后使用fcount时就可能有问题，所以在这里最好先判断一下：

iffcountisnullthen
fcount:=0;
endif;

这样就一切ok了。

6.Hibernate调用oracle存储过程

this.pnumberManager.getHibernateTemplate().execute(
newHibernateCallback()...{
publicObjectdoInHibernate(Sessionsession)
throwsHibernateException,SQLException...{
CallableStatementcs=session
.connection()
.prepareCall("{callmodifyapppnumber_remain(?)}");
cs.setString(1,foundationid);
cs.execute();
returnnull;
}
});

oracle存储过程语法总结及练习

---------------------------------------------
--1.存储过程之if
clear;
createorreplaceproceduremydel(
in_aininteger)
as
begin
ifin_a<100then
dbms_output.put_line('小于100.');
elsifin_a<200then
dbms_output.put_line('大于100小于200.');
else
dbms_output.put_line('大于200.');
endif;
end;
/

setserveroutputon;
begin
mydel(1102);
end;
/
---------------------------------------------

--2.存储过程之case1
clear;
createorreplaceproceduremydel(
in_aininteger)
as
begin
casein_a
when1then
dbms_output.put_line('小于100.');
when2then
dbms_output.put_line('大于100小于200.');
else
dbms_output.put_line('大于200.');
endcase;
end;
/

setserveroutputon;
begin
mydel(2);
end;
/
------------------------------------------------

--1.存储过程之loop1
clear;
createorreplaceproceduremydel(
in_aininteger)
as
ainteger;
begin
a:=0;
loop
dbms_output.put_line(a);
a:=a+1;
exitwhen
a>301;
endloop;
end;
/

setserveroutputon;
begin
mydel(2);
end;
/
--------------------------------------------------
--1.存储过程之loop2
clear;
createorreplaceproceduremydel(
in_aininteger)
as
ainteger;
begin
a:=0;
whilea<300loop
dbms_output.put_line(a);
a:=a+1;
endloop;
end;
/

setserveroutputon;
begin
mydel(2);
end;
--------------------------------------------------
--1.存储过程之loop3
clear;
createorreplaceproceduremydel(
in_aininteger)
as
ainteger;
begin
forain0..300
loop
dbms_output.put_line(a);
endloop;
end;
/

setserveroutputon;
begin
mydel(2);
end;
/
clear;
selectename,cc:=(case
whencomm=nullthensal*12;
else(sal+comm)*12;
endcasefromemporderbysalpersal;

----------------------------------------------------
clear;
createorreplaceproceduregetstudentcomments(
i_studentidinint,o_commentsoutvarchar)
as
exams_satint;
avg_markint;
tmp_commentsvarchar(100);
begin
selectcount(examid)intoexams_satfromstudentexam
wherestudentid=i_studentid;
ifexams_sat=0then
tmp_comments:='n/a-thisstudentdidnotattendtheexam!';
else
selectavg(mark)intoavg_markfromstudentexam
wherestudentid=i_studentid;
case
whenavg_mark<50thentmp_comments:='verybad';
whenavg_mark<60thentmp_comments:='bad';
whenavg_mark<70thentmp_comments:='good';
endcase;
endif;
o_comments:=tmp_comments;
end;
/

setserveroutputon;
declare
ppstudentexam.comments%type;
begin
getstudentcomments(8,pp);
dbms_output.put_line(pp);
end;
/
--------------------------------------------------------

deletefromempwhereempno<6000;
clear;
createorreplaceprocedureinsertdata(
in_numininteger)
as
myNumintdefault0;
emp_noemp.empno%type:=1000;
begin
whilemyNum<in_numloop
insertintoempvalues(emp_no,'hui'||myNum,'coder',7555,current_date,8000,6258,30);
emp_no:=emp_no+1;
myNum:=myNum+1;
endloop;
end;
/

setserveroutputon;
begin
insertdata(10);
end;
/
select*fromemp;

------------------------------------------------------------------

clear;
selectstudentname,averageMark,case
whenaverageMark<60then'不及格'
whenaverageMark<70then'考得好'
whenaverageMark<80then'考得很好'
endcase
from(select(
selectb.namefromstudentbwhereb.studentid=a.studentid)asstudentname,
round(avg(mark),2)asaverageMarkfromstudentexamagroupbya.studentid);

ORACLE查询练习

emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号)

dept部门表(deptno部门编号/dname部门名称/loc地点)

工资＝薪金＋佣金

1．列出至少有一个员工的所有部门。

2．列出薪金比“SMITH”多的所有员工。

3．列出所有员工的姓名及其直接上级的姓名。

4．列出受雇日期早于其直接上级的所有员工。

5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

6．列出所有“CLERK”（办事员）的姓名及其部门名称。

7．列出最低薪金大于1500的各种工作。

8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。

9．列出薪金高于公司平均薪金的所有员工。

10．列出与“SCOTT”从事相同工作的所有员工。

11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。

12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。

13．列出在每个部门工作的员工数量、平均工资和平均服务期限。

14．列出所有员工的姓名、部门名称和工资。

15．列出所有部门的详细信息和部门人数。

16．列出各种工作的最低工资。

17．列出各个部门的MANAGER（经理）的最低薪金。

18．列出所有员工的年工资,按年薪从低到高排序。

--------1----------
selectdnamefromdeptwheredeptnoin(
selectdeptnofromemp);

--------2----------
select*fromempwheresal>(
selectsalfromempwhereename='SMITH');

--------3----------
selecta.ename,(
selectenamefromempbwhereb.empno=a.mgr)asbossnamefromempa;

--------4----------
selecta.enamefromempawherea.hiredate<(
selecthiredatefromempbwhereb.empno=a.mgr);

--------5----------
selecta.dname,b.empno,b.ename,b.job,b.mgr,b.hiredate,b.sal,b.comm,b.deptno
fromdeptaleftjoinempbona.deptno=b.deptno;

--------6----------
selecta.ename,b.dnamefromempajoindeptb
ona.deptno=b.deptnoanda.job='CLERK';

--------7----------
selectdistinctjobasHighSalJobfromempgroupbyjobhavingmin(sal)>1500;

--------8----------
selectenamefromempwheredeptno=(
selectdeptnofromdeptwheredname='SALES');

--------9----------
selectenamefromempwheresal>(
selectavg(sal)fromemp);

--------10---------
selectenamefromempwherejob=(
selectjobfromempwhereename='SCOTT');

--------11---------
selecta.ename,a.salfromempawherea.salin(
selectb.salfromempbwhereb.deptno=30)anda.deptno<>30;

--------12---------
selectename,salfromempwheresal>(
selectmax(sal)fromempwheredeptno=30);

--------13---------
select
(selectb.dnamefromdeptbwherea.deptno=b.deptno)asdeptname,
count(deptno)asdeptcount,
avg(sal)asdeptavgsal
fromempagroupbydeptno;

--------14---------
select
a.ename,
(selectb.dnamefromdeptbwhereb.deptno=a.deptno)asdeptname,
sal
fromempa;

--------15---------
select
a.deptno,
a.dname,
a.loc,
(selectcount(deptno)fromempbwhereb.deptno=a.deptnogroupbyb.deptno)asdeptcount
fromdepta;

--------16---------
selectjob,avg(sal)fromempgroupbyjob;

--------17---------
selectdeptno,min(sal)fromempwherejob='MANAGER'groupbydeptno;

--------18---------
selectename,(sal+nvl(comm,0))*12assalpersalfromemporderbysalpersal;

ORACLE子句查询,分组等

A.同表子查询作为条件
a.给出人口多于Russia(俄国)的国家名称SELECTnameFROMbbc
WHEREpopulation>
(SELECTpopulationFROMbbc
WHEREname='Russia')

b.给出'India'(印度),'Iran'(伊朗)所在地区的所有国家的所有信息SELECT*FROMbbc
WHEREregionIN
(SELECTregionFROMbbc
WHEREnameIN('India','Iran'))

c.给出人均GDP超过'UnitedKingdom'(英国)的欧洲国家.SELECTnameFROMbbc
WHEREregion='Europe'ANDgdp/population>
(SELECTgdp/populationFROMbbc
WHEREname='UnitedKingdom')

d.这个查询实际上等同于以下这个:

selecte1.enamefromempe1,(selectempnofromempwhereename='KING')e2wheree1.mgr=e2.empno;
你可以用EXISTS写同样的查询，你只要把外部查询一栏移到一个像下面这样的子查询环境中就可以了：
selectenamefromempe
whereexists(select0fromempwheree.mgr=empnoandename='KING');
当你在一个WHERE子句中写EXISTS时，又等于向最优化传达了这样一条信息，即你想让外部查询先运行，使用每一个值来从内部查询（假定：EXISTS＝由外而内）中得到一个值。
B.异表子查询作为条件
a.select*fromstudentExamwherestudentid=(selectstudentidfromstudentwherename='吴丽丽');

b.select*fromstudentexamwherestudentidin(
selectstudentidfromstudent)orderbystudentid;

c.select*fromstudentwherestudentidin(selectstudentidfromstudentexamwheremark>80);

3.selectstudentexam.mark,studentexam.studentidasseid,student.studentid,student.namefromstudentexam,studentwherestudent.studentid=studentexam.studentid;

过滤分组:
顺序为先分组,再过滤,最后进行统计(实际值).
selectstudentid,count(*)ashighpassesfromstudentexamwheremark>70groupbystudentid;
假使我们不想通过数据表中的实际值,而是通过聚合函数的结果来过过滤查询的结果.
selectstudentid,avg(mark)asaveragemarkfromstudentexamwhereavg(mark)<50oravg(mark)>70groupbystudentid;(此句错误,where句子是不能用聚合函数作条件的)此时要用having.
selectstudentid,avg(mark)fromstudentexamgroupbystudentidhavingavg(mark)>70oravg(mark)<50;

selectstudentid,avg(mark)fromstudentexamwherestudentidin(1,7,9,5)groupbystudentidhavingavg(mark)>70;(先分组,再过滤,再having聚合,最后再统计).

selectstudentid,avg(mark)asaveragemarkfromstudentexamwhereexamidin(5,8,11)groupbystudentidhavingavg(mark)<50oravg(mark)>70;

返回限定行数查询:
selectnamefromstudentwhererownum<=10;
oracle中使用rownum关键字指定,但该关键字必须在where子句中与一个比较运算符一起指定,而不能与orderby一起配合便用,因为rownum维护的是原始行号.如果需要用groupby\orderby就用子句查询作表使用的方法:

selectstudentid,averagemarkfrom(selectstudentid,avg(mark)asaveragemarkfromstudentexamgroupbystudentidorderbyaveragemarkdesc)whererownum<=10;

oracle存储过程语法:Oracle存储过程入门学习基本语法

1.基本结构
　　createORREPLACEPROCEDURE存储过程名字
　　(
　　参数1INNUMBER,
　　参数2INNUMBER
　　)IS
　　变量1INTEGER:=0;
　　变量2DATE;
　　BEGIN
　　END存储过程名字
　　2.selectINTOSTATEMENT
　　将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)
　　例子:
　BEGIN
　　selectcol1,col2into变量1,变量2FROMtypestructwherexxx;
　　EXCEPTION
　　WHENNO_DATA_FOUNDTHEN
　　　xxxx;
　　END;
　　...
　　3.IF判断
　　IFV_TEST=1THEN
　　BEGIN
　　　　dosomething
　　END;
　　ENDIF;
　　4.while循环
　　WHILEV_TEST=1LOOP
　　BEGIN
　XXXX
　　END;
　ENDLOOP;
　　5.变量赋值
　　V_TEST:=123;
　　6.用forin使用cursor
　...
　IS
　　CURSORcurISselect*FROMxxx;
　BEGIN
　FORcur_resultincurLOOP
　BEGIN
　　V_SUM:=cur_result.列名1+cur_result.列名2
　END;
　ENDLOOP;
　　END;
　　7.带参数的cursor
　CURSORC_USER(C_IDNUMBER)ISselectNAMEFROMUSERwhereTYPEID=C_ID;
　OPENC_USER(变量值);
　　LOOP
　　FETCHC_USERINTOV_NAME;
　　EXITFETCHC_USER%NOTFOUND;
　　　dosomething
　ENDLOOP;
　　CLOSEC_USER;
　　8.用pl/sqldeveloperdebug
　　连接数据库后建立一个TestWINDOW
　　在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试

oracle语法:Oracle触发器语法及实例基础知识（一）

一Oracle触发器语法

　　触发器是特定事件出现的时候，自动执行的代码块类似于存储过程，触发器和存储过程的区别在于:存储过程是由用户或应用程序显式调用的,而触发器是不能被直接调用的

　　功能:

　　1、允许/限制对表的修改

　　2、自动生成派生列，比如自增字段

　　3、强制数据一致性

　　4、提供审计和日志记录

　　5、防止无效的事务处理

　　6、启用复杂的业务逻辑

　　触发器触发时间有两种:after和before

　　1、触发器的语法:

　　CREATE[ORREPLACE]TIGGER触发器名触发时间触发事件

　　ON表名

　　[FOREACHROW]

　　BEGIN

　　pl/sql语句

　　END

　　其中:

　　触发器名:触发器对象的名称

　　由于触发器是数据库自动执行的，因此该名称只是一个名称，没有实质的用途

　　触发时间:指明触发器何时执行，该值可取:

　　before---表示在数据库动作的前触发器执行;

　　after---表示在数据库动作的后出发器执行

　　触发事件:指明哪些数据库动作会触发此触发器:

　　insert:数据库插入会触发此触发器;

　　update:数据库修改会触发此触发器;

　　delete:数据库删除会触发此触发器

　　表名:数据库触发器所在的表

　　foreachrow:对表的每一行触发器执行一次如果没有这一选项，则只对整个表执行一次

　　2、举例:

　　下面的触发器在更新表auths的前触发，目的是不允许在周末修改表:

createtriggerauth_securebeforeinsertorupdateordelete//对整表更新前触发
　　onauths
　　begin
　　if(to_char(sysdate,'DY')='SUN'
　　RAISE_APPLICATION_ERROR(-20600,'不能在周末修改表auths');
　　endif;
　　end

　　例子:

　CREATEORREPLACETRIGGERCRM.T_SUB_USERINFO_AUR_NAMEAFTERUPDATEOFSTAFF_NAME
　　ONCRM.T_SUB_USERINFO
　　REFERENCINGOLDASOLDNEWASNEW
　　FOREACHROW
　　declare
　　begin
　　if:NEW.STAFF_NAME!=:OLD.STAFF_NAMEthen
　　begin

　　客户投诉　
　updateT_COMPLAINT_MANAGEsetSERVE_NAME=:NEW.STAFF_NAMEwhereSERVE_SEED=:OLD.SEED;

　　客户关怀　　
updateT_CUSTOMER_CAREsetEXECUTOR_NAME=:NEW.STAFF_NAME
　　whereEXECUTOR_SEED=:OLD.SEED;

　　客户服务　
updateT_CUSTOMER_SERVICEsetEXECUTOR_NAME=:NEW.STAFF_NAME
　　whereEXECUTOR_SEED=:OLD.SEED;
　　end;
　　endif;
　　endT_sub_userinfo_aur_name;
　　/

　　2Oracle触发器详解

　　开始:　　

createtriggerbiufer_employees_department_id
　　beforeinsertorupdateofdepartment_idonemployees
　　referencingoldasold_valuenewasnew_value
　　foreachrow
　　when(new_value.department_id<>80)
　　begin
　　:new_value.commission_pct:=0;
　　end;
　　/

　　1、触发器的组成部分:

　　1、触发器名称

　　2、触发语句

　　3、触发器限制

　　4、触发操作

　　1.1、触发器名称

createtriggerbiufer_employees_department_id

　　命名习惯:　　

biufer(beforeinsertupdateforeachrow)

　　employees表名

　　department_id列名

　　1.2、触发语句

　　比如:

　　表或视图上的DML语句

　　DDL语句

　　数据库关闭或启动,startupshutdown等等　

beforeinsertorupdate
　　ofdepartment_id
　　onemployees
　　referencingoldasold_value
　　newasnew_value
　　foreachrow

　　介绍说明:

　　1、无论是否规定了department_id，对employees表进行insert的时候

　　2、对employees表的department_id列进行update的时候

　　1.3、触发器限制　　

when(new_value.department_id<>80)

　　限制不是必须的此例表示如果列department_id不等于80的时候，触发器就会执行

　　其中的new_value是代表更新的后的值

　　1.4、触发操作

　　是触发器的主体　

begin
　　:new_value.commission_pct:=0;
　　end;

　　主体很简单，就是将更新后的commission_pct列置为0

　　触发:　　

insertintoemployees(employee_id,last_name,first_name,hire_date,job_id,email,
　　department_id,salary,commission_pct)
　　values(12345,’Chen’,’Donny’,sysdate,12,‘donny@hotmail.com’,60,10000,.25);
　　selectcommission_pctfromemployeeswhereemployee_id=12345;

2、触发器的类型有:

　　触发器类型:

　　1、语句触发器

　　2、行触发器

　　3、INSTEADOF触发

　　4、系统条件触发器

　　5、用户事件触发器

　　2.1、语句级触发器.(语句级触发器对每个DML语句执行一次)

　　是在表上或者某些情况下的视图上执行的特定语句或者语句组上的触发器能够和INSERT、UPDATE、DELETE或者组合上进行关联但是无论使用什么样的组合，各个语句触发器都只会针对指定语句激活一次比如，无论update多少行，也只会调用一次update语句触发器

　　例子:　　

createorreplacetriggertri_test
　　afterinsertorupdateordelete_disibledevent=>

　　测试,插入几条记录　　
insertintotestvalues(0,'ff');
　　insertintotestvalues(0,'ff');
　　insertintotestvalues(0,'tt');

例子2:

　　创建一个触发器,无论用户插入新记录,还是修改emp表的job列,都将用户指定的job列的值转换成大写.
　　createorreplacetriggertrig_job
　　beforeinsertorupdateofjob
　　onemp
　　foreachrow
　　begin
　　ifinsertingthen
　　:new.job:=upper(:new.job);
　　else
　　:new.job:=upper(:new.job);
　　endif;
　　end;

　　2.3、insteadof触发器.

　　(此触发器是在视图上而不是在表上定义的触发器,它是用来替换所使用实际语句的触发器.)

　　语法如下:

　createorreplacetriggertrig_test
　　insteadofinsertorupdate_disibledevent=>

　　2.5、数据库级触发器.

　　可以创建在数据库事件上的触发器,包括关闭,启动,服务器错误,登录等.这些事件都是例子范围的,不和特定的表或视图关联.

　　例子:　　

createorreplacetriggertrig_name
　　afterstartup_disibledevent=>

　　4、测试

　updateemployees_copysetsalary=salary*1.1;
　　select*fromemployess_log;

　　5、确定是哪个语句起作用?

　　即是INSERT/UPDATE/DELETE中的哪一个触发了触发器?

　　可以在触发器中使用INSERTING/UPDATING/DELETING条件谓词，作判断:　　

begin
　　ifinsertingthen
　　-----
　　elsifupdatingthen
　　-----
　　elsifdeletingthen
　　------
　　endif;
　　end;
　　ifupdating(‘COL1’)orupdating(‘COL2’)then
　　------
　　endif;

　　2.8、[试验]

　　1、修改日志表

　altertableemployees_log
　　add(actionvarchar2(20));

　　2、修改触发器，以便记录语句类型　　

then
　　l_action:=’Delete’;
　　else
　　raise_application_error(-20001,’Youshouldneverevergetthiserror.’);
　　Insertintoemployees_log(Who,action,when)
　　Values(user,l_action,sysdate);
　　End;Createorreplacetriggerbiud_employee_copy
　　Beforeinsertorupdateordelete
　　Onemployees_copy
　　Declare
　　L_actionemployees_log.action%type;
　　Begin
　　ifinsertingthen
　　l_action:=’Insert’;
　　elsifupdatingthen
　　l_action:=’Update’;
　　elsifdeleting
　　/

　　　3、测试
　　insertintoemployees_copy(employee_id,last_name,email,hire_date,job_id)
　　values(12345,’Chen’,’Donny@hotmail’,sysdate,12);
　　select*fromemployees_log

oracle基本语法备忘

2008年11月13日星期四09:58

1.基本结构
CREATEORREPLACEPROCEDURE存储过程名字
(
参数1INNUMBER,
参数2INNUMBER
)IS
变量1INTEGER:=0;
变量2DATE;
BEGIN

关于oracle存储过程的若干问题备忘

1.在oracle中，数据表别名不能加as，如：

selecta.appnamefromappinfoa;--正确
selecta.appnamefromappinfoasa;--错误

也许，是怕和oracle中的存储过程中的关键字as冲突的问题吧

2.在存储过程中，select某一字段时，后面必须紧跟into，如果select整个记录，利用游标的话就另当别论了。

3.在利用select...into...语法时，必须先确保数据库中有该条记录，否则会报出"nodatafound"异常。

可以在该语法之前，先利用selectcount(*)from查看数据库中是否存在该记录，如果存在，再利用select...into...

4.在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会报错

5.在存储过程中，关于出现null的问题

假设有一个表A，定义如下：

createtableA(
idvarchar2(50)primarykeynotnull,
vcountnumber(8)notnull,
bidvarchar2(50)notnull--外键
);

如果在存储过程中，使用如下语句：

selectsum(vcount)intofcountfromAwherebid='xxxxxx';

iffcountisnullthen
fcount:=0;
endif;

这样就一切ok了。

1、创建存储过程

createorreplaceproceduretest(var_name_1intype,var_name_2outtype)as

--声明变量(变量名变量类型)

begin

--存储过程的执行体

endtest;

打印出输入的时间信息

E.g:

createorreplaceproceduretest(workDateinDate)is

begin

dbms_output.putline('Theinputdateis:'||to_date(workDate,'yyyy-mm-dd'));

endtest;

2、变量赋值

变量名:=值;

E.g：

createorreplaceproceduretest(workDateinDate)is

xnumber(4,2);

begin

x:=1;

endtest;

3、判断语句:

if比较式thenbeginend;endif;

E.g

createorreplaceproceduretest(xinnumber)is

begin

ifx>0then

begin

x:=0-x;

end;

endif;

ifx=0then

begin

x:=1;

end;

endif;

endtest;

4、For循环

For...in...LOOP

--执行语句

endLOOP;

(1)循环遍历游标

createorreplaceproceduretest()as

Cursorcursorisselectnamefromstudent;namevarchar(20);

begin

fornameincursorLOOP

begin

dbms_output.putline(name);

end;

endLOOP;

endtest;

(2)循环遍历数组

createorreplaceproceduretest(varArrayinmyPackage.TestArray)as

--(输入参数varArray是自定义的数组类型，定义方式见标题6)

inumber;

begin

i:=1;--存储过程数组是起始位置是从1开始的，与java、C、C++等语言不同。因为在Oracle中本是没有数组的概念的，数组其实就是一张

--表(Table),每个数组元素就是表中的一个记录，所以遍历数组时就相当于从表中的第一条记录开始遍历

foriin1..varArray.countLOOP

dbms_output.putline('TheNo.'||i||'recordinvarArrayis:'||varArray(i));

endLOOP;

endtest;

5、While循环

while条件语句LOOP

begin

end;

endLOOP;

E.g

createorreplaceproceduretest(iinnumber)as

begin

whilei<10LOOP

begin

i:=i+1;

end;

endLOOP;

endtest;

6、数组

首先明确一个概念：Oracle中本是没有数组的概念的，数组其实就是一张表(Table),每个数组元素就是表中的一个记录。

使用数组时，用户可以使用Oracle已经定义好的数组类型，或可根据自己的需要定义数组类型。

(1)使用Oracle自带的数组类型

xarray;--使用时需要需要进行初始化

e.g:

createorreplaceproceduretest(youtarray)is

xarray;

begin

x:=newarray();

y:=x;

endtest;

(2)自定义的数组类型(自定义数据类型时，建议通过创建Package的方式实现，以便于管理)

E.g(自定义使用参见标题4.2)createorreplacepackagemyPackageis

--Publictypedeclarationstypeinfoisrecord(namevarchar(20),ynumber);

name，一个是y。需要注意的是此处使用了Indexbybinary_integer编制该Table的索引项，也可以不写，直接写成：typeTestArrayis

tableofinfo，如果不写的话使用数组时就需要进行初始化：varArraymyPackage.TestArray;varArray:=newmyPackage.TestArray();

endTestArray;

7.游标的使用Oracle中Cursor是非常有用的，用于遍历临时表中的查询结果。其相关方法和属性也很多，现仅就常用的用法做一二介绍：

(1)Cursor型游标(不能用于参数传递)

createorreplaceproceduretest()is

cusor_1Cursorisselectstd_namefromstudentwhere...;--Cursor的使用方式1cursor_2Cursor;

begin

selectclass_nameintocursor_2fromclasswhere...;--Cursor的使用方式2

可使用ForxincursorLOOP....endLOOP;来实现对Cursor的遍历

endtest;

(2)SYS_REFCURSOR型游标，该游标是Oracle以预先定义的游标，可作出参数进行传递

createorreplaceproceduretest(rsCursoroutSYS_REFCURSOR)is

cursorSYS_REFCURSOR;namevarhcar(20);

begin

OPENcursorFORselectnamefromstudentwhere...--SYS_REFCURSOR只能通过OPEN方法来打开和赋值

LOOP

dbms_output.putline(name);

endLOOP;

rsCursor:=cursor;

endtest;

下面写一个简单的例子来对以上所说的存储过程的用法做一个应用：

通过存储过程自动计算出每位学生的总成绩和平均成绩，同时，如果学生在课外课程中获得的评价为A，就在总成绩上加20分。

createorreplaceprocedureautocomputer(stepinnumber)is

rsCursorSYS_REFCURSOR;

commentArraymyPackage.myArray;

mathnumber;

articlenumber;

languagenumber;

musicnumber;

sportnumber;

totalnumber;

averagenumber;

stdIdvarchar(30);

recordmyPackage.stdInfo;

inumber;

begin

i:=1;

get_comment(commentArray);--调用名为get_comment()的存储过程获取学生课外评分信息

OPENrsCursorforselectstdId,math,article,language,music,sportfromstudenttwheret.step=step;

LOOP

fetchrsCursorintostdId,math,article,language,music,sport;exitwhenrsCursor%NOTFOUND;

total:=math+article+language+music+sport;

foriin1..commentArray.countLOOP

record:=commentArray(i);

ifstdId=record.stdIdthen

begin

ifrecord.comment='A'then

begin

total:=total+20;

gotonext;--使用goto跳出for循环

end;

endif;

end;

endif;

endLOOP;

<<continue>>average:=total/5;

updatestudenttsett.total=totalandt.average=averagewheret.stdId=stdId;

endLOOP;

end;

endautocomputer;

--取得学生评论信息的存储过程

createorreplaceprocedureget_comment(commentArrayoutmyPackage.myArray)is

rsSYS_REFCURSOR；

recordmyPackage.stdInfo;

stdIdvarchar(30);

commentvarchar(1);

inumber;

begin

openrsforselectstdId,commentfromout_school

i:=1;

LOOP

fetchrsintostdId,comment;exitwhenrs%NOTFOUND;

record.stdId:=stdId;

record.comment:=comment;

recommentArray(i):=record;

i:=i+1;

endLOOP;

endget_comment;

--定义数组类型myArray

createorreplacepackagemyPackageisbegin

typestdInfoisrecord(stdIdvarchar(30),commentvarchar(1));

typemyArrayistableofstdInfoindexbybinary_integer;

endmyPackage;

分享到：

IT人士常喝点菊花茶泡枸杞 | ArcGIS Server 客户端开发中如何使用自定义 ...

2012-08-07 14:10
浏览 869
评论(0)
查看更多

发表评论

您还没有登录,请您登录后再发表评论

最近访客更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论

Oracle存储过程总结

Oracle存储过程总结

oracle存储过程的基本语法

关于oracle存储过程的若干问题备忘

oracle存储过程语法总结及练习

ORACLE查询练习

ORACLE子句查询,分组等

oracle存储过程语法:Oracle存储过程入门学习基本语法

oracle语法:Oracle触发器语法及实例基础知识（一）

oracle基本语法备忘

评论

发表评论

相关推荐

最近访客 更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论

Oracle存储过程总结

Oracle存储过程总结

oracle存储过程的基本语法

关于oracle存储过程的若干问题备忘

oracle存储过程语法总结及练习

ORACLE查询练习

ORACLE子句查询,分组等

oracle存储过程语法:Oracle存储过程入门学习基本语法

oracle语法:Oracle触发器语法及实例基础知识（一）

oracle基本语法备忘

评论

发表评论

相关推荐

最近访客更多访客>>