oracle常用SQL语句.docx
《oracle常用SQL语句.docx》由会员分享,可在线阅读,更多相关《oracle常用SQL语句.docx(16页珍藏版)》请在冰豆网上搜索。
![oracle常用SQL语句.docx](https://file1.bdocx.com/fileroot1/2023-1/29/b3172eb5-3073-4959-b4a7-28ece1fdb650/b3172eb5-3073-4959-b4a7-28ece1fdb6501.gif)
oracle常用SQL语句
oracle常用SQL语句(转贴)--
动态SQL;
declare
sqlvarchar2(100);
...
sql:
='insertinto'||tablename||'(id,name)vaules(:
1,:
2)';
executeimmediatesqlusing:
new.id,:
new.name;
================================================================================================
请问用PL/SQL如何判断两个日期之间相差的天数?
selectto_date('2002-9-1','yyyy-mm-dd')-to_date('2002-8-19','yyyy-mm-dd')fromdual
================================================================================================
替代触发器提供了一种方法,以解决不能修改视图的问题。
替代触发器只能在视图上创建,它执行一个PLSQL SQL代码块,而不是一条DML语句。
create[orreplace]triggertrigger_nameINSTEADofxxxonview_nameforeachrow
DECLARE
declarations
BEGIN
plsqlcode
END;
/
下面是一个例子:
createorreplacetriggeremp_name_change
INSTEADOFUPDATEONemp_public_date
BEGIN
if(:
new.emp_name<>:
old.emp_name)
or(:
old.emp_nameisnulland:
new.emp_nameisnotnull)
then
updateemployeesetemp_name=:
new.emp_name
whereemp_id=:
new.emp_id;
ENDIF;
END;
/
================================================================================================
快速掌握PL/SQL过程设计
文/范生
我精心编写和测试了以下程序,如果能够在SQLPLUS中逐一键入并测试通过,则可以对过程和PLSQL的结构有较全面的掌握,
以后我会再找更复杂的例子以为提高。
一、PLSQL表 INDEX可以用任意整数,可以是EMP(0).ENAME或者是EMP(-2).ENAME
二、我刚才试着写了一个以CURSOR类型为参数的过程,请大家在SQLPLUS里调试并体会其格式。
createpackagepkgTestis
typecurTestisrefcursor;
proceduresp_SelectData(soutcurTest);
end;
/
CREATEorreplacepackageBODYpkgTestAS
proceduresp_SelectData(soutcurTest)is
mynamevarchar2(90);
mynonumber;
begin
opensforselectename,empnofromemp;
LOOP
FETCHsintomyname,myno;
dbms_output.put_line(myname);
exitwhens%notfound;
endloop;
closes;
end;
end;
DECLARE
mynamevarchar2(22);
typecurTestisrefcursor;
C_NAMEcurTest;
begin
pkgtest.sp_SelectData(c_name); --如果在包体中只声明了一个游标,那么此处相当于取了一个结果集。
end;
/
三、理解过程,由浅入深。
createorreplaceprocedureabc
is
begin
dbms_output.put_line('Hello');
endabc;
/
begin
abc;
end;
/
修改后:
createorreplaceprocedureabc
is
vjobvarchar(9);
v_countnumber:
=0;
vtotaldate:
=sysdate+7;
c_taxconstantnumber(3,2):
=8.25;
v_validbooleannotnull:
=true;
begin
dbms_output.put_line('Hello'||to_char(vtotal));
endabc;
/
--这里加入了过程内部需要的变量,可以看作是匿名块多了一个过程名而已。
begin
abc;
end;
/ --再执行。
修改后,加一个IN参数:
createorreplaceprocedureabc(testininvarchar2)
is
vjobvarchar(9);
v_countnumber:
=0;
vtotaldate:
=sysdate+7;
c_taxconstantnumber(3,2):
=8.25;
v_validbooleannotnull:
=true;
begin
dbms_output.put_line('Hello'||to_char(vtotal));
dbms_output.put_line(testin);
endabc;
SQL>executeabc('myhome')
Hello30-1月-02
myhome
PL/SQL过程已成功完成。
再修改,加一个OUT参数:
createorreplaceprocedureabc(testininvarchar2,testoutoutnumber)
is
vjobvarchar(9);
v_countnumber:
=7;
vtotaldate:
=sysdate+7;
c_taxconstantnumber(3,2):
=8.25;
v_validbooleannotnull:
=true;
begin
testout:
=v_count;
dbms_output.put_line('Hello'||to_char(vtotal));
dbms_output.put_line(testin);
endabc;
/
这个将在过程中计算的结果通过一参数传递到过程外面的调用变量:
declare
varidvarchar(22):
='yourhome';
numbidnumber;
begin
abc(varid,numbid);
dbms_output.put_line(numbid);
end;
/
结果:
Hello30-1月-02
yourhome
7
PL/SQL过程已成功完成。
如果是INOUT,既是输入同时可以输出。
================================================================================================
回滚段理论与实践
文/范生
既然称之为段(SEGMENT),这是一个占用物理空间的逻辑名称,一般我们认为段在TABLESPACE与EXTENT规模之间,表一段的一种,回滚段也是段的一种类型。
下文中回滚段译为rollbacksegment。
rollbacksegment是数据库中用来在修改的过程中保持旧值的单元,它存储了在完成编辑之前的原数据的位置和数据。
一个TRANSACTION只能使用一个rollbacksegment,但并发的事物可以共用一个rollbacksegment。
rollbacksegment的作用一般为:
事物回滚、事物恢复、提供读一致性。
rollbacksegment的类型:
SYSTEM rollbacksegment:
为SYSTEM表空间的对象来使用的。
NON-SYSTEM rollbacksegment:
分公共的和私有的两种。
DEFERRED rollbacksegment:
仅在表空间被TAKE OFFLINE的使用到。
ORACLE在某一实例启动的时候,会指定几个回滚段ONLINE,如果用户的事物没有指定一个回滚段,ORACLE SERVER会指定一个所含事物最少的回滚段分配给此事物。
但有时会出现回滚段太小或出现ORA-01555 SNAPSHOTTOOOLDerror.这是很常见的错误,在编程和操纵大数据时经常遇到,产生的原因有两种可能:
1、回滚段中事物资源被重用。
2、回滚段中的旧数据被另一事物改写。
解决的方法可以扩大回滚段,但结果未必成功,可以试图为一个长事物指定一个单独的回滚段:
如在PLSQL的块中:
SETTRANSACTIONUSEROLLBACKSEGMENTrollback_segment
这样可以使用一个独立创建的回滚段。
创建回滚段的语句为:
CREATEROLLBACKSEGMENTrbs01
TABLESPACErbs
STORAGE(
INITIAL100K
NEXT100K
MINEXTENTS20
MAXEXTENTS100
OPTIMAL2000K);
创建时注意:
PCTINCREASE始终为0,INITIAL=NEXT,尽量不要使MAXEXTENTS为UNLIMITED,一个错误的程序可能导致空间无限扩展。
ALTERROLLBACKSEGMENTrbs01ONLINE;把一个回滚段ONLINE。
也可以在INITSID.ORA中ROLLBACK_SEGMENTS=(rbs01,rbs02)
修改rollbacksegment的最大扩展:
ALTERROLLBACKSEGMENTrbs01
STORAGE(MAXEXTENTS200);
删除:
DROPROLLBACKSEGMENTrbs01;
我们可以通过数据字典得知相关回滚段的信息:
SQL>SELECTsegment_name,tablespace_name,owner,statusFROMdba_rollback_segs;
如果大家需要优化数据库性能,就要根据自己数据库开发的方向来确定回滚段的多少和大小。
如在OLTP中,需要很多小的回滚段,每个回滚段承担约4个事物。
在DSS系统中,因为事物量少,只需要少量的回滚段,平均每个事物应用一个回滚段。
综上,对于回滚段的掌握,这些可以应付大多数的应用,深入的内容则要多看ORACLE文档,多多实践。
对DBA的要求:
希望各位DBA对概念和规则性的知识点可以达到背诵的地步。
================================================================================================
SQL--JOIN之完全用法
外联接。
外联接可以是左向外联接、右向外联接或完整外部联接。
在FROM子句中指定外联接时,可以由下列几组关键字中的一组指定:
LEFTJOIN或LEFTOUTERJOIN。
左向外联接的结果集包括LEFTOUTER子句中指定的左表的所有行,而不仅仅是联接列所匹配的行。
如果左表的某行在右表中没有匹配行,则在相关联的结果集行中右表的所有选择列表列均为空值。
RIGHTJOIN或RIGHTOUTERJOIN。
右向外联接是左向外联接的反向联接。
将返回右表的所有行。
如果右表的某行在左表中没有匹配行,则将为左表返回空值。
FULLJOIN或FULLOUTERJOIN。
完整外部联接返回左表和右表中的所有行。
当某行在另一个表中没有匹配行时,则另一个表的选择列表列包含空值。
如果表之间有匹配行,则整个结果集行包含基表的数据值。
仅当至少有一个同属于两表的行符合联接条件时,内联接才返回行。
内联接消除与另一个表中的任何行不匹配的行。
而外联接会返回FROM子句中提到的至少一个表或视图的所有行,只要这些行符合任何WHERE或HAVING搜索条件。
将检索通过左向外联接引用的左表的所有行,以及通过右向外联接引用的右表的所有行。
完整外部联接中两个表的所有行都将返回。
Microsoft?
SQLServer?
2000对在FROM子句中指定的外联接使用以下SQL-92关键字:
LEFTOUTERJOIN或LEFTJOIN
RIGHTOUTERJOIN或RIGHTJOIN
FULLOUTERJOIN或FULLJOIN
SQLServer支持SQL-92外联接语法,以及在WHERE子句中使用*=和=*运算符指定外联接的旧式语法。
由于SQL-92语法不容易产生歧义,而旧式Transact-SQL外联接有时会产生歧义,因此建议使用SQL-92语法。
使用左向外联接
假设在city列上联接authors表和publishers表。
结果只显示在出版商所在城市居住的作者(本例中为AbrahamBennet和CherylCarson)。
若要在结果中包括所有的作者,而不管出版商是否住在同一个城市,请使用SQL-92左向外联接。
下面是Transact-SQL左向外联接的查询和结果:
USEpubs
SELECTa.au_fname,a.au_lname,p.pub_name
FROMauthorsaLEFTOUTERJOINpublishersp
ONa.city=p.city
ORDERBYp.pub_nameASC,a.au_lnameASC,a.au_fnameASC
下面是结果集:
au_fnameau_lnamepub_name
-------------------------------------------------------------------
ReginaldBlotchet-HallsNULL
MichelDeFranceNULLInnesdelCastilloNULL
AnnDullNULLMarjorieGreenNULLMorningstarGreeneNULLBurtGringlesbyNULLSherylHunterNULLLiviaKarsenNULLCharleneLocksleyNULLStearnsMacFeatherNULLHeatherMcBaddenNULLMichaelO'LearyNULLSylviaPanteleyNULLAlbertRingerNULLAnneRingerNULLMeanderSmithNULLDeanStraightNULLDirkStringerNULLJohnsonWhiteNULLAkikoYokomotoNULLAbrahamBennetAlgodataInfosystemsCherylCarsonAlgodataInfosystems
(23row(s)affected)
不管是否与publishers表中的city列匹配,LEFTOUTERJOIN均会在结果中包含authors表的所有行。
注意:
结果中所列的大多数作者都没有相匹配的数据,因此,这些行的pub_name列包含空值。
使用右向外联接
假设在city列上联接authors表和publishers表。
结果只显示在出版商所在城市居住的作者(本例中为AbrahamBennet和CherylCarson)。
SQL-92右向外联接运算符RIGHTOUTERJOIN指明:
不管第一个表中是否有匹配的数据,结果将包含第二个表中的所有行。
若要在结果中包括所有的出版商,而不管城市中是否还有出版商居住,请使用SQL-92右向外联接。
下面是Transact-SQL右向外联接的查询和结果:
USEpubs
SELECTa.au_fname,a.au_lname,p.pub_name
FROMauthorsASaRIGHTOUTERJOINpublishersASp
ONa.city=p.city
ORDERBYp.pub_nameASC,a.au_lnameASC,a.au_fnameASC
下面是结果集:
au_fnameau_lnamepub_name
----------------------------------------------------------------
AbrahamBennetAlgodataInfosystems
CherylCarsonAlgodataInfosystems
NULLNULLBinnet&HardleyNULLNULLFiveLakesPublishingNULLNULLGGG&GNULLNULLLucernePublishingNULLNULLNewMoonBooksNULLNULLRamonaPublishersNULLNULLScootneyBooks(9row(s)affected)使用谓词(如将联接与常量比较)可以进一步限制外联接。
下例包含相同的右向外联接,但消除销售量低于50本的书籍的书名:
USEpubs
SELECTs.stor_id,s.qty,t.title
FROMsalessRIGHTOUTERJOINtitlest
================================================================================================
只返回前3条纪录
SQL>selectrownum,month,sellfromsalewhererownum<4;
ROWNUMMONTH SELL
------------------------
1200001 1000
2200002 1100
3200003 1200
如何用rownum实现大于、小于逻辑?
(返回rownum在4—10之间的数据)(minus操作,速度会受影响)
SQL>selectrownum,month,sellfromsalewhererownum<10
2 minus
3 selectrownum,month,sellfromsalewhererownum<5;
ROWNUMMONTH SELL
------------------------
5200005 1400
6200006 1500
7200007 1600
8200101 1100
9200202 1200
================================================================================================
--自动编号触发器
createorreplacetriggertrigger1beforeinsertonaabb1foreachrow
begin
selectmax(orderid)+1into:
new.orderidfromdual;
end;
================================================================================================
--没有数据找到时的异常处理
declare
inumber;
s1varchar(20);
BEGIN
select*intoi,s1fromaabb1;
EXCEPTION
WHENNO_DATA_FOUNDTHEN
Dbms_Output.Put_Line('NoDataFound.');
END;
================================================================================================
异常处理:
:
:
:
:
:
一、PLSQL异常处理
异常是由ORACLE错误或显式的抛出一个错误产生的。
如何处理:
用一个处理程序来捕获它;
将它传递给CALLINGENVIRONMENT
二、异常的类型:
ORACLESERVER预定义错误
非ORACLESERVER预定义错误,但也是ORACLESERVER的标准错误
用户自定义异常
三、捕捉异常的要点:
WHENOTHERSclause要放在所有捕捉条件的后面。
错误处理最多有一个WHENOTHERSclause.
异常段是以EXCEPTION.关键字开头的。
我们可以定义不同的EXCEPTION的句柄,来捕捉异常。
处理异常时,只有一条语句能够被处理。
EXCEPTION
WHENexception1[ORexception2...]THEN