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                => 这样就有可能引起这错误,

阅读更多
个人分类: oracle
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

不良信息举报

oracle常用SQL语句

最多只允许输入30个字

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭