oracle常用SQL语句.docx

1、oracle常用SQL语句oracle常用SQL语句(转贴)- -动态SQL;declaresql varchar2(100);.sql := insert into | tablename | (id,name) vaules (:1, :2);execute immediate sql using :new.id, :new.name;=请问用PL/SQL如何判断两个日期之间相差的天数？ select to_date(2002-9-1,yyyy-mm-dd)-to_date(2002-8-19,yyyy-mm-dd) from dual=替代触发器提供了一种方法，以解决不能修改视图的问题。

2、替代触发器只能在视图上创建，它执行一个PLSQLSQL代码块，而不是一条DML语句。create or replace trigger trigger_name INSTEAD of xxx on view_name for each row DECLARE declarationsBEGINplsql codeEND;/ 下面是一个例子：create or replace trigger emp_name_changeINSTEAD OF UPDATE ON emp_public_dateBEGIN if(:new.emp_name :old.emp_name) or (:old.emp_n

3、ame is null and :new.emp_name is not null) then update employee set emp_name=:new.emp_name where emp_id=:new.emp_id;END IF;END;/ =快速掌握PL/SQL过程设计文/范生 我精心编写和测试了以下程序，如果能够在SQLPLUS中逐一键入并测试通过，则可以对过程和PLSQL的结构有较全面的掌握，以后我会再找更复杂的例子以为提高。一、PLSQL表INDEX可以用任意整数，可以是EMP(0).ENAME 或者是EMP(-2).ENAME 二、我刚才试着写了一个以CURSOR类型

4、为参数的过程，请大家在SQLPLUS里调试并体会其格式。create package pkgTest istype curTest is ref cursor;procedure sp_SelectData(s out curTest);end;/ CREATE or replace package BODY pkgTest ASprocedure sp_SelectData(s out curTest) ismyname varchar2(90);myno number;beginopen s for select ename,empno from emp;LOOPFETCH s into

5、myname,myno;dbms_output.put_line (myname);exit when s%notfound;end loop;close s;end;end;DECLAREmyname varchar2(22);type curTest is ref cursor;C_NAME curTest;beginpkgtest.sp_SelectData(c_name);如果在包体中只声明了一个游标，那么此处相当于取了一个结果集。end;/三、理解过程，由浅入深。create or replace procedure abcisbegindbms_output.put_line(He

6、llo);end abc;/beginabc;end;/修改后：create or replace procedure abcisvjob varchar(9);v_count number:=0;vtotal date:=sysdate +7;c_tax constant number(3,2):=8.25;v_valid boolean not null:=true;begindbms_output.put_line(Hello | to_char(vtotal);end abc;/这里加入了过程内部需要的变量，可以看作是匿名块多了一个过程名而已。beginabc;end;/再执行。修改后

7、，加一个IN参数：create or replace procedure abc(testin in varchar2 )isvjob varchar(9);v_count number:=0;vtotal date:=sysdate +7;c_tax constant number(3,2):=8.25;v_valid boolean not null:=true;begindbms_output.put_line(Hello | to_char(vtotal);dbms_output.put_line(testin);end abc;SQL execute abc(myhome)Hello

8、30-1月 -02myhomePL/SQL 过程已成功完成。再修改，加一个OUT参数：create or replace procedure abc(testin in varchar2,testout out number)isvjob varchar(9);v_count number:=7;vtotal date:=sysdate +7;c_tax constant number(3,2):=8.25;v_valid boolean not null:=true;begintestout:=v_count;dbms_output.put_line(Hello | to_char(vtot

9、al);dbms_output.put_line(testin);end abc;/这个将在过程中计算的结果通过一参数传递到过程外面的调用变量：declarevarid varchar(22):=your home;numbid number;beginabc(varid,numbid);dbms_output.put_line(numbid);end;/结果：Hello30-1月 -02your home7PL/SQL 过程已成功完成。如果是INOUT，既是输入同时可以输出。=回滚段理论与实践文/范生 既然称之为段（SEGMENT），这是一个占用物理空间的逻辑名称，一般我们认为段在TABLE

10、SPACE与EXTENT规模之间，表一段的一种，回滚段也是段的一种类型。下文中回滚段译为rollback segment。rollback segment是数据库中用来在修改的过程中保持旧值的单元，它存储了在完成编辑之前的原数据的位置和数据。一个TRANSACTION只能使用一个rollback segment，但并发的事物可以共用一个rollback segment。rollback segment的作用一般为：事物回滚、事物恢复、提供读一致性。rollback segment的类型：SYSTEMrollback segment：为SYSTEM表空间的对象来使用的。NONSYSTEMroll

11、back segment：分公共的和私有的两种。DEFERREDrollback segment：仅在表空间被TAKEOFFLINE的使用到。ORACLE在某一实例启动的时候，会指定几个回滚段ONLINE ，如果用户的事物没有指定一个回滚段，ORACLESERVER会指定一个所含事物最少的回滚段分配给此事物。但有时会出现回滚段太小或出现ORA-01555SNAPSHOT TOO OLD error.这是很常见的错误，在编程和操纵大数据时经常遇到，产生的原因有两种可能：1、回滚段中事物资源被重用。2、回滚段中的旧数据被另一事物改写。解决的方法可以扩大回滚段，但结果未必成功，可以试图为一个长事物指

12、定一个单独的回滚段：如在PLSQL的块中：SET TRANSACTION USE ROLLBACK SEGMENT rollback_segment这样可以使用一个独立创建的回滚段。创建回滚段的语句为：CREATE ROLLBACK SEGMENT rbs01TABLESPACE rbsSTORAGE (INITIAL 100KNEXT 100KMINEXTENTS 20MAXEXTENTS 100OPTIMAL 2000K );创建时注意：PCTINCREASE始终为0，INITIALNEXT，尽量不要使MAXEXTENTS 为 UNLIMITED，一个错误的程序可能导致空间无限扩展。ALT

13、ER ROLLBACK SEGMENT rbs01 ONLINE; 把一个回滚段ONLINE。也可以在INITSID.ORA中ROLLBACK_SEGMENTS=(rbs01, rbs02) 修改rollback segment的最大扩展：ALTER ROLLBACK SEGMENT rbs01STORAGE( MAXEXTENTS 200 );删除：DROP ROLLBACK SEGMENT rbs01;我们可以通过数据字典得知相关回滚段的信息：SQL SELECT segment_name, tablespace_name, owner, status FROM dba_rollback_

14、segs;如果大家需要优化数据库性能，就要根据自己数据库开发的方向来确定回滚段的多少和大小。如在OLTP中，需要很多小的回滚段，每个回滚段承担约4个事物。在DSS系统中，因为事物量少，只需要少量的回滚段，平均每个事物应用一个回滚段。综上，对于回滚段的掌握，这些可以应付大多数的应用，深入的内容则要多看ORACLE文档，多多实践。对DBA的要求：希望各位DBA对概念和规则性的知识点可以达到背诵的地步。=SQL-JOIN之完全用法 外联接。外联接可以是左向外联接、右向外联接或完整外部联接。 在 FROM 子句中指定外联接时，可以由下列几组关键字中的一组指定：LEFT JOIN 或 LEFT OUTE

15、R JOIN。 左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。 RIGHT JOIN 或 RIGHT OUTER JOIN。 右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。 FULL JOIN 或 FULL OUTER JOIN。 完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。仅当至少有一

16、个同属于两表的行符合联接条件时，内联接才返回行。内联接消除与另一个表中的任何行不匹配的行。而外联接会返回 FROM 子句中提到的至少一个表或视图的所有行，只要这些行符合任何 WHERE 或 HAVING 搜索条件。将检索通过左向外联接引用的左表的所有行，以及通过右向外联接引用的右表的所有行。完整外部联接中两个表的所有行都将返回。Microsoft? SQL Server? 2000 对在 FROM 子句中指定的外联接使用以下 SQL-92 关键字： LEFT OUTER JOIN 或 LEFT JOINRIGHT OUTER JOIN 或 RIGHT JOINFULL OUTER JOIN 或

17、 FULL JOIN SQL Server 支持 SQL-92 外联接语法，以及在 WHERE 子句中使用 *= 和 =* 运算符指定外联接的旧式语法。由于 SQL-92 语法不容易产生歧义，而旧式 Transact-SQL 外联接有时会产生歧义，因此建议使用 SQL-92 语法。使用左向外联接假设在 city 列上联接 authors 表和 publishers 表。结果只显示在出版商所在城市居住的作者（本例中为 Abraham Bennet 和 Cheryl Carson）。若要在结果中包括所有的作者，而不管出版商是否住在同一个城市，请使用 SQL-92 左向外联接。下面是 Transac

18、t-SQL 左向外联接的查询和结果：USE pubsSELECT a.au_fname, a.au_lname, p.pub_nameFROM authors a LEFT OUTER JOIN publishers pON a.city = p.cityORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC下面是结果集：au_fname au_lname pub_name - - - Reginald Blotchet-Halls NULLMichel DeFrance NULL Innes del Castillo NULLAnn D

19、ull NULL Marjorie Green NULL Morningstar Greene NULL Burt Gringlesby NULL Sheryl Hunter NULL Livia Karsen NULL Charlene Locksley NULL Stearns MacFeather NULL Heather McBadden NULL Michael OLeary NULL Sylvia Panteley NULL Albert Ringer NULL Anne Ringer NULL Meander Smith NULL Dean Straight NULL Dirk

20、Stringer NULL Johnson White NULL Akiko Yokomoto NULL Abraham Bennet Algodata Infosystems Cheryl Carson Algodata Infosystems(23 row(s) affected)不管是否与 publishers 表中的 city 列匹配，LEFT OUTER JOIN 均会在结果中包含 authors 表的所有行。注意：结果中所列的大多数作者都没有相匹配的数据,因此，这些行的 pub_name 列包含空值。使用右向外联接假设在 city 列上联接 authors 表和 publisher

21、s 表。结果只显示在出版商所在城市居住的作者（本例中为 Abraham Bennet 和 Cheryl Carson）。SQL-92 右向外联接运算符 RIGHT OUTER JOIN 指明：不管第一个表中是否有匹配的数据，结果将包含第二个表中的所有行。若要在结果中包括所有的出版商，而不管城市中是否还有出版商居住，请使用 SQL-92 右向外联接。下面是 Transact-SQL 右向外联接的查询和结果：USE pubsSELECT a.au_fname, a.au_lname, p.pub_nameFROM authors AS a RIGHT OUTER JOIN publishers A

22、S pON a.city = p.cityORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC下面是结果集：au_fname au_lname pub_name - - - Abraham Bennet Algodata InfosystemsCheryl Carson Algodata InfosystemsNULL NULL Binnet & Hardley NULL NULL Five Lakes Publishing NULL NULL GGG&G NULL NULL Lucerne Publishing NULL NULL N

23、ew Moon Books NULL NULL Ramona Publishers NULL NULL Scootney Books (9 row(s) affected) 使用谓词（如将联接与常量比较）可以进一步限制外联接。下例包含相同的右向外联接，但消除销售量低于 50 本的书籍的书名：USE pubsSELECT s.stor_id, s.qty, t.titleFROM sales s RIGHT OUTER JOIN titles t =只返回前3条纪录SQL select rownum,month,sell from sale where rownum select rownum,

24、month,sell from sale where rownum10 2 minus 3 select rownum,month,sell from sale where rownum5; ROWNUM MONTH SELL- - - 5 200005 1400 6 200006 1500 7 200007 1600 8 200101 1100 9 200202 1200=-自动编号触发器create or replace trigger trigger1 before insert on aabb1 for each rowbeginselect max(orderid)+1 into :

25、new.orderid from dual;end;=-没有数据找到时的异常处理declarei number;s1 varchar(20);BEGIN select * into i,s1 from aabb1;EXCEPTION WHEN NO_DATA_FOUND THEN Dbms_Output.Put_Line(No Data Found.);END;=异常处理: 一、PLSQL异常处理 异常是由ORACLE错误或显式的抛出一个错误产生的。 如何处理： 用一个处理程序来捕获它； 将它传递给CALLING ENVIRONMENT 二、异常的类型： ORACLE SERVER 预定义错误 非ORACLE SERVER 预定义错误，但也是ORACLE SERVER 的标准错误 用户自定义异常 三、捕捉异常的要点： WHEN OTHERS clause 要放在所有捕捉条件的后面。 错误处理最多有一个 WHEN OTHERS clause. 异常段是以 EXCEPTION.关键字开头的。 我们可以定义不同的EXCEPTION的句柄，来捕捉异常。 处理异常时，只有一条语句能够被处理。 EXCEPTION WHEN exception1 OR exception2 . . . THEN