oracle常用SQL语句.docx

oracle常用SQL语句.docx

《oracle常用SQL语句.docx》由会员分享，可在线阅读，更多相关《oracle常用SQL语句.docx（16页珍藏版）》请在冰豆网上搜索。

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