oracle常用sql语句

动态sql;

declare
sql 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

================================================================================================

替代触发器提供了一种方法，以解决不能修改视图的问题。替代触发器只能在视图上创建，它执行一个plsql　sql代码块，而不是一条dml语句。
create  [or replace] trigger trigger_name instead of xxx on view_name for each row
declare
declarations
begin
plsql code
end;
/
下面是一个例子：
create or replace trigger emp_name_change
instead of update on  emp_public_date
begin
if(:new.emp_name<> :old.emp_name)
or (:old.emp_name 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类型为参数的过程，请大家在sqlplus里调试并体会其格式。

create package  pkgtest is
type curtest is ref cursor;
procedure sp_selectdata(s out  curtest);
end;
/ 
create or replace package body pkgtest  as
procedure sp_selectdata(s out curtest) is
myname varchar2(90);
myno  number;
begin
open s for select ename,empno from emp;
loop
fetch s  into myname,myno;
dbms_output.put_line (myname);
exit when  s%notfound;
end loop;
close s;
end;
end;

declare
myname  varchar2(22);
type curtest is ref cursor;
c_name  curtest;
begin
pkgtest.sp_selectdata(c_name);　－－如果在包体中只声明了一个游标，那么此处相当于取了一个结果集。
end;
/

三、理解过程，由浅入深。

create  or replace procedure abc
is
begin
dbms_output.put_line(‘hello‘);
end  abc;
/
begin
abc;
end;
/
修改后：
create or replace procedure  abc
is
vjob varchar(9);
v_count number:=0;
vtotal date:=sysdate  +7;
c_tax constant number(3,2):=8.25;
v_valid boolean not  null:=true;
begin
dbms_output.put_line(‘hello‘ || to_char(vtotal));
end  abc;
/
－－这里加入了过程内部需要的变量，可以看作是匿名块多了一个过程名而已。
begin
abc;
end;
/　－－再执行。
修改后，加一个in参数：
create  or replace procedure abc(testin in varchar2 )
is
vjob  varchar(9);
v_count number:=0;
vtotal date:=sysdate +7;
c_tax constant  number(3,2):=8.25;
v_valid boolean not  null:=true;
begin
dbms_output.put_line(‘hello‘ ||  to_char(vtotal));
dbms_output.put_line(testin);
end abc;
sql>  execute abc(‘myhome‘)
hello30-1月 -02
myhome

pl/sql  过程已成功完成。
再修改，加一个out参数：
create or replace procedure abc(testin in  varchar2,testout out number)
is
vjob varchar(9);
v_count  number:=7;
vtotal date:=sysdate +7;
c_tax constant  number(3,2):=8.25;
v_valid boolean not  null:=true;
begin
testout:=v_count;
dbms_output.put_line(‘hello‘ ||  to_char(vtotal));
dbms_output.put_line(testin);
end  abc;
/
这个将在过程中计算的结果通过一参数传递到过程外面的调用变量：
declare
varid  varchar(22):=‘your home‘;
numbid  number;
begin
abc(varid,numbid);
dbms_output.put_line(numbid);
end;
/
结果：
hello30-1月  -02
your home
7

pl/sql  过程已成功完成。
如果是inout，既是输入同时可以输出。

================================================================================================
回滚段理论与实践

文/范生                         

既然称之为段（segment），这是一个占用物理空间的逻辑名称，一般我们认为段在tablespace与extent规模之间，表一段的一种，回滚段也是段的一种类型。
下文中回滚段译为rollback  segment。
rollback  segment是数据库中用来在修改的过程中保持旧值的单元，它存储了在完成编辑之前的原数据的位置和数据。一个transaction只能使用一个rollback  segment，但并发的事物可以共用一个rollback segment。
rollback  segment的作用一般为：事物回滚、事物恢复、提供读一致性。
rollback segment的类型：
system　rollback  segment：为system表空间的对象来使用的。
non－system　rollback  segment：分公共的和私有的两种。
deferred　rollback  segment：仅在表空间被take　offline的使用到。
oracle在某一实例启动的时候，会指定几个回滚段online ，如果用户的事物没有指定一个回滚段，oracle　server会指定一个所含事物最少的回滚段分配给此事物。但有时会出现回滚段太小或出现ora- 01555　snapshot too old error.这是很常见的错误，在编程和操纵大数据时经常遇到，产生的原因有两种可能：1、回滚段中事物资源被重用。
2、回滚段中的旧数据被另一事物改写。
解决的方法可以扩大回滚段，但结果未必成功，可以试图为一个长事物指定一个单独的回滚段：
如在plsql的块中：
set  transaction use rollback segment  rollback_segment
这样可以使用一个独立创建的回滚段。
创建回滚段的语句为：
create rollback  segment rbs01
tablespace rbs
storage (
initial  100k
next 100k
minextents 20
maxextents  100
optimal 2000k  );
创建时注意：pctincrease始终为0，initial＝next，尽量不要使maxextents 为  unlimited，一个错误的程序可能导致空间无限扩展。
alter rollback segment rbs01 online;  把一个回滚段online。
也可以在initsid.ora中rollback_segments=(rbs01, rbs02)  
修改rollback segment的最大扩展：
alter rollback segment rbs01
storage(  maxextents 200 );
删除：　drop rollback segment  rbs01;
我们可以通过数据字典得知相关回滚段的信息：
sql> select segment_name,  tablespace_name, owner, status from  dba_rollback_segs;
如果大家需要优化数据库性能，就要根据自己数据库开发的方向来确定回滚段的多少和大小。如在oltp中，需要很多小的回滚段，每个回滚段承担约4个事物。在dss系统中，因为事物量少，只需要少量的回滚段，平均每个事物应用一个回滚段。
综上，对于回滚段的掌握，这些可以应付大多数的应用，深入的内容则要多看oracle文档，多多实践。
对dba的要求：希望各位dba对概念和规则性的知识点可以达到背诵的地步。

================================================================================================
sql--join之完全用法  



外联接。外联接可以是左向外联接、右向外联接或完整外部联接。 在 from  子句中指定外联接时，可以由下列几组关键字中的一组指定：
left join 或 left outer join。 左向外联接的结果集包括 left outer 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。 right join 或 right outer join。 
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。 full join 或 full  outer join。  
完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。
仅当至少有一个同属于两表的行符合联接条件时，内联接才返回行。内联接消除与另一个表中的任何行不匹配的行。而外联接会返回 from 子句中提到的至少一个表或视图的所有行，只要这些行符合任何 where 或 having 搜索条件。将检索通过左向外联接引用的左表的所有行，以及通过右向外联接引用的右表的所有行。完整外部联接中两个表的所有行都将返回。
microsoft?  sql server? 2000 对在 from 子句中指定的外联接使用以下 sql-92 关键字： left outer join 或 left  join
right outer join 或 right join
full outer join 或 full join 
sql server 支持 sql-92 外联接语法，以及在 where 子句中使用 *= 和 =* 运算符指定外联接的旧式语法。由于 sql-92 语法不容易产生歧义，而旧式 transact-sql 外联接有时会产生歧义，因此建议使用 sql-92 语法。
使用左向外联接
假设在 city  列上联接 authors 表和 publishers 表。结果只显示在出版商所在城市居住的作者（本例中为 abraham bennet 和 cheryl  carson）。
若要在结果中包括所有的作者，而不管出版商是否住在同一个城市，请使用 sql-92 左向外联接。下面是 transact-sql  左向外联接的查询和结果：
use pubs
select a.au_fname, a.au_lname, p.pub_name
from  authors a left outer join publishers p
on a.city = p.city
order by  p.pub_name asc, a.au_lname asc, a.au_fname asc
下面是结果集：
au_fname au_lname  pub_name 
-------------------- ------------------------------  ----------------- 
reginald blotchet-halls null
michel defrance null innes  del castillo null
ann dull 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 o‘leary null sylvia panteley null albert ringer null anne ringer null meander smith null dean straight null dirk 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 表和 publishers 表。结果只显示在出版商所在城市居住的作者（本例中为 abraham bennet 和 cheryl carson）。sql-92 右向外联接运算符 right outer join 指明：不管第一个表中是否有匹配的数据，结果将包含第二个表中的所有行。
若要在结果中包括所有的出版商，而不管城市中是否还有出版商居住，请使用 sql-92  右向外联接。下面是 transact-sql 右向外联接的查询和结果：
use pubs
select a.au_fname,  a.au_lname, p.pub_name
from authors as a right outer join publishers as  p
on a.city = p.city
order by p.pub_name asc, a.au_lname asc, a.au_fname  asc
下面是结果集：
au_fname au_lname pub_name 
--------------------  ------------------------ -------------------- 
abraham bennet algodata  infosystems
cheryl carson algodata infosystems
null null binnet & hardley null null five lakes publishing null null ggg&g null null lucerne publishing null null new moon books null null ramona publishers null null scootney books (9 row(s) affected) 使用谓词（如将联接与常量比较）可以进一步限制外联接。下例包含相同的右向外联接，但消除销售量低于 50 本的书籍的书名：
use  pubs
select s.stor_id, s.qty, t.title
from sales s right outer join titles  t  


================================================================================================
只返回前3条纪录
sql>  select rownum,month,sell from sale where rownum<4;

rownum  month        sell
--------- ------ ---------
1 200001        1000
2 200002       1100
3 200003        1200


如何用rownum实现大于、小于逻辑？（返回rownum在4—10之间的数据）（minus操作，速度会受影响）
sql>  select rownum,month,sell from sale where rownum<10
2   minus
3    select rownum,month,sell from sale where rownum<5;

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  row
begin
select max(orderid)+1 into :new.orderid from  dual;
end;
================================================================================================
--没有数据找到时的异常处理

declare
i  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

statement1;

四、常用错误或异常：

no_data_found  ora-01403

too_many_rows ora-01422

invalid_cursor ora-01001

zero_divide ora-01476

dup_val_on_index ora-00001

五、实例

procedure  elim_inventory

(v_product_id in s_product.id%type) is

v_id s_product.id%type;

begin

select id

into  v_id

from s_product

where id = v_product_id;

delete from s_inventory

where product_id = v_product_id;

commit;

exception

when no_data_found then

rollback;

text_io.put_line(to_char(v_product_id)||

’ is  invalid.’);

when too_many_rows then

rollback;

text_io.put_line(’data corruption in  s_product.’);

when others then

rollback;

text_io.put_line(’other error occurred.’);

end  elim_inventory;

以下摘自oracle plsql guide 
declare
err_num  number;
err_msg varchar2(100);
begin
...
exception
...
when  others then
err_num := sqlcode;
err_msg := substr(sqlerrm, 1,  100);
insert into errors values (err_num, err_msg);
end;

在scott环境中使用要稍加改动

========================================================================================      
form中，exception(例外)都有哪些，如何书写？  


软件环境： 
1、服务器端：windows nt4.0+oracle 8.0.4
oracle安装路径为：c:orant
2、客户端：windows 98、developer/2000客户端安装（版本2.1） 
实现方法：  
begin
《pl/sql块》;
exception
when no_data_found then  --没有找到数据
《响应命令》;
when too_many_rows then  --返回多行，隐式光标每次只能检索一行数据
《响应命令》;
when invalid_number then  --字符向数字转换失败
《响应命令》;
when zero_divide then   --被零除
《响应命令》;
when dup_val_on_index then --向唯一索引中插入重复数据
《响应命令》;
when invalid_cursor then --非法游标操作
《响应命令》;
when value_error then    --数字的,数据转换,截字符串或强制性的错误
《响应命令》;
when others then   --发生其它任何错误
null;    --选择一：什么也不做，就当错误没发生
raise form_trigger_failure;  --选择二：挂起当前程序
end;

========================================================================================      


decode()函数使用技巧  


含义解释：  
decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)

该函数的含义如下：
if 条件=值1  then
return(翻译值1)
elsif 条件=值2  then
return(翻译值2)
......
elsif 条件=值n  then
return(翻译值n)

else
return(缺省值)
end  if
========================================================================================      
杀用户进程
select  * from v$session where lower(terminal) = ‘wangliang‘;

alter system kill  session ‘sid, serial#‘;


应该是
alter system kill session  ‘sid,serial#‘
或者用toad，pl/sql developer等工具。
另外，
select vp.spid
from  v$session vs,v$process vp
where vs.paddr = vp.addr --记不太清了
and vs.machine  = ‘your machine name‘
and 其他条件

spid 就是unix下的进程号，kill  掉即可。
========================================================================================      
这样可以只复制表结构，不复制数据。

create  table dup_table as
(select * from org_table
where  1=0);

=========================================================================================
表复制:

用copy  from 
用法: copy from <db> to <db> <opt> <table>  {(<cols> )} using <sel>
<db> : 数据库字符串, 例如: scott/tiger@d:  chicago-mktg
<opt> : 下列关键字之一: append, create, insert 或  replace
<table>: 目标表名称
<cols> : 用逗号分隔的目标列别名列表
<sel> :  任何有效的 sql select 语句
缺少的 from 或 to 子句使用当前的 sql*plus  连接
=========================================================================================
查看数据库字符集：

可以在sqlplus中输入select  userenv(‘language‘) from  dual;

=========================================================================================
update  table1 set (c,b)=(select d,e from table2 where table1.c=table2.f and  rownum=1)
=========================================================================================
与oracle中的decode函数相似的sql函数是什么  


select decode(a1,‘0‘, ‘easy‘, ‘1‘, ‘normal‘, ‘2‘, ‘hard‘) level from  a;

轉成sql server為:
select
(case a1 when ‘0‘ then  ‘easy‘
when ‘1‘ then ‘normal‘
when ‘2‘ then ‘hard‘  end) level
from  a;

=========================================================================================
select  * from v$open_cursor
select * from  v$parameter
v$sqltext

=========================================================================================

查看服务器支持的字符集

select  value from v$nls_valid_values where parameter=‘characterset‘ order by  value;

=========================================================================================
dmp文件第二第三字节是字符集，比如001f
select  nls_charset_name(to_number(‘001f‘)) from  dual;
we8iso8859p1

要改成zhs16gbk
select  to_char(nls_charset_id(‘zhs16gbk‘)) from  dual;
0354
改吧
不过不是任意两个字符集之间都能改的，要注意。不过好在我们常用的
us7ascii，we8iso8859p1，zhs16cgb231280，zhs16gbk都可以。
=========================================================================================
[oracle初始口令]
internal/oracle
sys/change_on_install
system/manager
scott/tiger
=========================================================================================
ora-06550/06553
1.  (可能)有可以是因为空值引起的。sum()函数会返回null，而count()则不会。一般用nvl()可以解决
2. 程序结构不是很好，例如:  
open cursor for 
select * from a
union
select * from b                 => 这样就有可能引起这错误，
解决：

open cursor  for 
select x.* from 
(select * from a
union
select * from b) x                 => 这样就有可能引起这错误，

转自：http://hi.baidu.com/it_luming/blog/item/7a2591d1ca127f86a0ec9c7b.html