oracle常用sql语句

最新推荐文章于 2020-10-07 19:21:53 发布

iteye_18051

最新推荐文章于 2020-10-07 19:21:53 发布

阅读量123

点赞数

文章标签：数据库操作系统人工智能

动态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‘)fromdual

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

替代触发器提供了一种方法，以解决不能修改视图的问题。替代触发器只能在视图上创建，它执行一个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 onemp_public_date
begin
if(:new.emp_name<> :old.emp_name)
or (:old.emp_name is null and :new.emp_name is not null)
then
updateemployee set emp_name=:new.emp_name
where emp_id=:new.emp_id;
endif;
end;
/

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

快速掌握pl/sql过程设计

文/范生

我精心编写和测试了以下程序，如果能够在sqlplus中逐一键入并测试通过，则可以对过程和plsql的结构有较全面的掌握，
以后我会再找更复杂的例子以为提高。
一、plsql表　index可以用任意整数，可以是emp(0).ename或者是emp(-2).ename
二、我刚才试着写了一个以cursor类型为参数的过程，请大家在sqlplus里调试并体会其格式。

create packagepkgtest is
type curtest is ref cursor;
procedure sp_selectdata(s outcurtest);
end;
/
create or replace package body pkgtestas
procedure sp_selectdata(s out curtest) is
myname varchar2(90);
mynonumber;
begin
open s for select ename,empno from emp;
loop
fetch sinto myname,myno;
dbms_output.put_line (myname);
exit whens%notfound;
end loop;
close s;
end;
end;

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

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

createor replace procedure abc
is
begin
dbms_output.put_line(‘hello‘);
endabc;
/
begin
abc;
end;
/
修改后：
create or replace procedureabc
is
vjob varchar(9);
v_count number:=0;
vtotal date:=sysdate+7;
c_tax constant number(3,2):=8.25;
v_valid boolean notnull:=true;
begin
dbms_output.put_line(‘hello‘ || to_char(vtotal));
endabc;
/
－－这里加入了过程内部需要的变量，可以看作是匿名块多了一个过程名而已。
begin
abc;
end;
/　－－再执行。
修改后，加一个in参数：
createor replace procedure abc(testin in varchar2 )
is
vjobvarchar(9);
v_count number:=0;
vtotal date:=sysdate +7;
c_tax constantnumber(3,2):=8.25;
v_valid boolean notnull:=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 invarchar2,testout out number)
is
vjob varchar(9);
v_countnumber:=7;
vtotal date:=sysdate +7;
c_tax constantnumber(3,2):=8.25;
v_valid boolean notnull:=true;
begin
testout:=v_count;
dbms_output.put_line(‘hello‘ ||to_char(vtotal));
dbms_output.put_line(testin);
endabc;
/
这个将在过程中计算的结果通过一参数传递到过程外面的调用变量：
declare
varidvarchar(22):=‘your home‘;
numbidnumber;
begin
abc(varid,numbid);
dbms_output.put_line(numbid);
end;
/
结果：
hello30-1月-02
your home
7

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

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

文/范生

既然称之为段（segment），这是一个占用物理空间的逻辑名称，一般我们认为段在tablespace与extent规模之间，表一段的一种，回滚段也是段的一种类型。
下文中回滚段译为rollbacksegment。
rollbacksegment是数据库中用来在修改的过程中保持旧值的单元，它存储了在完成编辑之前的原数据的位置和数据。一个transaction只能使用一个rollbacksegment，但并发的事物可以共用一个rollback segment。
rollbacksegment的作用一般为：事物回滚、事物恢复、提供读一致性。
rollback segment的类型：
system　rollbacksegment：为system表空间的对象来使用的。
non－system　rollbacksegment：分公共的和私有的两种。
deferred　rollbacksegment：仅在表空间被take　offline的使用到。
oracle在某一实例启动的时候，会指定几个回滚段online ，如果用户的事物没有指定一个回滚段，oracle　server会指定一个所含事物最少的回滚段分配给此事物。但有时会出现回滚段太小或出现ora- 01555　snapshot too old error.这是很常见的错误，在编程和操纵大数据时经常遇到，产生的原因有两种可能：1、回滚段中事物资源被重用。
2、回滚段中的旧数据被另一事物改写。
解决的方法可以扩大回滚段，但结果未必成功，可以试图为一个长事物指定一个单独的回滚段：
如在plsql的块中：
settransaction use rollback segmentrollback_segment
这样可以使用一个独立创建的回滚段。
创建回滚段的语句为：
create rollbacksegment rbs01
tablespace rbs
storage (
initial100k
next 100k
minextents 20
maxextents100
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 segmentrbs01;
我们可以通过数据字典得知相关回滚段的信息：
sql> select segment_name,tablespace_name, owner, status fromdba_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 或 fullouter join。
完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。
仅当至少有一个同属于两表的行符合联接条件时，内联接才返回行。内联接消除与另一个表中的任何行不匹配的行。而外联接会返回 from 子句中提到的至少一个表或视图的所有行，只要这些行符合任何 where 或 having 搜索条件。将检索通过左向外联接引用的左表的所有行，以及通过右向外联接引用的右表的所有行。完整外部联接中两个表的所有行都将返回。
microsoft?sql server? 2000 对在 from 子句中指定的外联接使用以下 sql-92 关键字： left outer join 或 leftjoin
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 和 cherylcarson）。
若要在结果中包括所有的作者，而不管出版商是否住在同一个城市，请使用 sql-92 左向外联接。下面是 transact-sql左向外联接的查询和结果：
use pubs
select a.au_fname, a.au_lname, p.pub_name
fromauthors a left outer join publishers p
on a.city = p.city
order byp.pub_name asc, a.au_lname asc, a.au_fname asc
下面是结果集：
au_fname au_lnamepub_name
-------------------- -----------------------------------------------
reginald blotchet-halls null
michel defrance null innesdel 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 asp
on a.city = p.city
order by p.pub_name asc, a.au_lname asc, a.au_fnameasc
下面是结果集：
au_fname au_lname pub_name
-------------------------------------------- --------------------
abraham bennet algodatainfosystems
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 本的书籍的书名：
usepubs
select s.stor_id, s.qty, t.title
from sales s right outer join titlest

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

rownummonth sell
--------- ------ ---------
1 2000011000
2 200002 1100
3 2000031200

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

rownummonth sell
--------- ------ ---------
5 2000051400
6 200006 1500
7 200007 1600
8 200101 1100
9 2002021200

================================================================================================
--自动编号触发器
createor replace trigger trigger1 before insert on aabb1 for eachrow
begin
select max(orderid)+1 into :new.orderid fromdual;
end;
================================================================================================
--没有数据找到时的异常处理

declare
inumber;
s1 varchar(20);
begin
select * into i,s1 fromaabb1;
exception
when no_data_found then
dbms_output.put_line(‘no datafound.‘);
end;

================================================================================================
异常处理::::::

一、plsql异常处理

异常是由oracle错误或显式的抛出一个错误产生的。

如何处理：

用一个处理程序来捕获它；

将它传递给callingenvironment

二、异常的类型：

oracle server预定义错误

非oracle server 预定义错误，但也是oracle server的标准错误

用户自定义异常

三、捕捉异常的要点：

whenothers clause 要放在所有捕捉条件的后面。

错误处理最多有一个 when othersclause.

异常段是以 exception.关键字开头的。

我们可以定义不同的exception的句柄，来捕捉异常。

处理异常时，只有一条语句能够被处理。

exception

when exception1 [or exception2 . . .] then

statement1;

四、常用错误或异常：

no_data_foundora-01403

too_many_rows ora-01422

invalid_cursor ora-01001

zero_divide ora-01476

dup_val_on_index ora-00001

五、实例

procedureelim_inventory

(v_product_id in s_product.id%type) is

v_id s_product.id%type;

begin

select id

intov_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)||

’ isinvalid.’);

when too_many_rows then

rollback;

text_io.put_line(’data corruption ins_product.’);

when others then

rollback;

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

endelim_inventory;

以下摘自oracle plsql guide
declare
err_numnumber;
err_msg varchar2(100);
begin
...
exception
...
whenothers 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 条件=值1then
return(翻译值1)
elsif 条件=值2then
return(翻译值2)
......
elsif 条件=值nthen
return(翻译值n)

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

alter system killsession ‘sid, serial#‘;

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

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

createtable dup_table as
(select * from org_table
where1=0);

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

用copyfrom
用法: 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中输入selectuserenv(‘language‘) fromdual;

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

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

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

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

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

查看服务器支持的字符集

selectvalue from v$nls_valid_values where parameter=‘characterset‘ order byvalue;

=========================================================================================
dmp文件第二第三字节是字符集，比如001f
selectnls_charset_name(to_number(‘001f‘)) fromdual;
we8iso8859p1

要改成zhs16gbk
selectto_char(nls_charset_id(‘zhs16gbk‘)) fromdual;
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 cursorfor
select x.* from
(select * from a
union
select * from b) x => 这样就有可能引起这错误，