[入门知识共享]我学Oracle时整理或收藏的一些东东

Question

[入门书籍]首先看一些oracle基础方面的书,比如oracle管理基础等,然后学习一些oracle中sql,pl/sql等的语法知识,oracle数据库的高级开发方面的书和资料.同时要多练习,多想,多看. 推荐书:《完全接触 Oracle》
Oracle PL_SQL高级程序设计
Oracle 数据库管理员技术指南
Oracle8i Designing and Tuning for Performance
Oracle9i SQL Reference
oracle初学指南
PL_SQL用户指南与参考(推荐)论坛
csdn
itpub
cnougoracle视频下载
ftp://202.96.64.144/http://www.oradb.net/
、哪里下载Oracle软件,Oracle的文档
  otn.oracle.com,需要注册一个帐号，不过是免费的
2、初学者应该看些什么Oracle方面的书？
  《ORACLE9i 初学者指南》
　《ORACLE9i DBA手册》
　《ORACLE9i DBA 基础教程》
　或到www.china-pub.com中，在搜索栏中敲入oracle,便可以见到各样的oracle书，且有介绍　
3、有什么好的Oracle软件工具？
  a、toad,在www.quest.com,注册码在论坛中就可以搜到
  b、pl/sql developer在www.51delphi.com可以down到

Answer 1

Oracle 的入门心得－－>强烈推荐oracle的体系太庞大了，对于初学者来说，难免会有些无从下手的感觉，什么都想学，结果什么都学不好，所以把学习经验共享一下，希望让刚刚入门的人对oracle有一个总体的认识，少走一些弯路。 
  
一、定位 
oracle分两大块，一块是开发，一块是管理。开发主要是写写存储过程、触发器什么的，还有就是用Oracle的Develop工具做form。有点类似于程序员，需要有较强的逻辑思维和创造能力，个人觉得会比较辛苦，是青春饭J；管理则需要对oracle数据库的原理有深刻的认识，有全局操纵的能力和紧密的思维，责任较大，因为一个小的失误就会down掉整个数据库，相对前者来说，后者更看重经验。 
  
因为数据库管理的责任重大，很少公司愿意请一个刚刚接触oracle的人去管理数据库。对于刚刚毕业的年轻人来说，可以先选择做开发，有一定经验后转型，去做数据库的管理。当然，这个还是要看人个的实际情况来定。二、学习方法  
我的方法很简单，就是：看书、思考、写笔记、做实验、再思考、再写笔记 
  
     看完理论的东西，自己静下心来想想，多问自己几个为什么，然后把所学和所想的知识点做个笔记；在想不通或有疑问的时候，就做做实验，想想怎么会这样，同样的，把实验的结果记下来。思考和做实验是为了深入的了解这个知识点。而做笔记的过程，也是理清自己思路的过程。 
  
     学习的过程是使一个问题由模糊到清晰，再由清晰到模糊的过程。而每次的改变都代表着你又学到了一个新的知识点。 
  
     学习的过程也是从点到线，从线到网，从网到面的过程。当点变成线的时候，你会有总豁然开朗的感觉。当网到面的时候，你就是高手了 
  
     很多网友，特别是初学的人，一碰到问题就拿到论坛上来问，在问前，你有没有查过书，自己有没有研究过，有没有搜索一下论坛？这就叫思维惰性。由别人来回答你的问题，会让你在短时间内不费劲地弄懂这个知识点，然而通过自己的努力去研究它，不但会更深入的了解这个知识点，更重要的是在研究的过程会提高你解决问题和分析问题的能力。总的来说，没有钻研的学习态度，不管学什么东西，都不会成功的。 
  
     当然，初学的人很多时候是因为遇到问题时，无从下手，也不知道去哪里找资料，才会到论坛上提问题的。但我认为，在提问的时候，是不是可以问别人是如何分析这个问题？从哪里可以找到相关的资料？而不是这个问题的答案是什么？授人以鱼不如授人以渔。   下面我讲下我处理问题的过程   首先要知道oracle的官方网站：www.oracle.com 这里有oracle的各种版本的数据库、应用工具和权威的官方文档。其次，还要知道http://metalink.oracle.com/这里是买了oracle服务或是oracle的合作伙伴才可以进去的，里面有很多权威的解决方案和补丁。然后就是一些著名网站：asktom.oracle.com www.orafaq.net, www.dbazine.com。这里有很多经验之谈。    遇到问题了。如果是概念上的问题，第一时间可以找tahiti.oracle.com，这里会给你最详细的解释。如果在运行的过程中出了什么错误。可以去metalink看看。如果是想知道事务的处理的经验之谈。可以去asktom。当然。这里只是相对而言。
三、oracle的体系 
oracle的体系很庞大，要学习它，首先要了解oracle的框架。在这里，简要的讲一下oracle的架构，让初学者对oracle有一个整体的认识。 
  
1、物理结构（由控制文件、数据文件、重做日志文件、参数文件、归档文件、密码文件组成） 
控制文件：包含维护和验证数据库完整性的必要信息、例如，控制文件用于识别数据文件和重做日志文件，一个数据库至少需要一个控制文件 
数据文件：存储数据的文件 
重做日志文件：含对数据库所做的更改记录，这样万一出现故障可以启用数据恢复。一个数据库至少需要两个重做日志文件 
参数文件：定义Oracle 例程的特性，例如它包含调整SGA 中一些内存结构大小的参数 
归档文件：是重做日志文件的脱机副本，这些副本可能对于从介质失败中进行恢复很必要。 
密码文件：认证哪些用户有权限启动和关闭Oracle例程 
  
2、逻辑结构（表空间、段、区、块） 
表空间：是数据库中的基本逻辑结构，一系列数据文件的集合。 
段：是对象在数据库中占用的空间 
区：是为数据一次性预留的一个较大的存储空间 
块：ORACLE最基本的存储单位，在建立数据库的时候指定 
  
3、内存分配（SGA和PGA） 
SGA：是用于存储数据库信息的内存区，该信息为数据库进程所共享。它包含Oracle 服务器的数据和控制信息, 它是在Oracle 服务器所驻留的计算机的实际内存中得以分配，如果实际内存不够再往虚拟内存中写。 
PGA：包含单个服务器进程或单个后台进程的数据和控制信息，与几个进程共享的SGA 正相反PGA 是只被一个进程使用的区域，PGA 在创建进程时分配在终止进程时回收 
  
4、后台进程（数据写进程、日志写进程、系统监控、进程监控、检查点进程、归档进程、服务进程、用户进程） 
数据写进程：负责将更改的数据从数据库缓冲区高速缓存写入数据文件 
日志写进程：将重做日志缓冲区中的更改写入在线重做日志文件 
系统监控：检查数据库的一致性如有必要还会在数据库打开时启动数据库的恢复 
进程监控：负责在一个Oracle 进程失败时清理资源 
检查点进程：负责在每当缓冲区高速缓存中的更改永久地记录在数据库中时,更新控制文件和数据文件中的数据库状态信息。 
归档进程：在每次日志切换时把已满的日志组进行备份或归档 
服务进程：用户进程服务。 
用户进程：在客户端，负责将用户的SQL 语句传递给服务进程，并从服务器段拿回查询数据。 
  
5、oracle例程：Oracle 例程由SGA 内存结构和用于管理数据库的后台进程组成。例程一次只能打开和使用一个数据库。 
  
6、SCN(System Change Number)：系统改变号，一个由系统内部维护的序列号。当系统需要更新的时候自动增加，他是系统中维持数据的一致性和顺序恢复的重要标志。四、深入学习  
管理：可以考OCP证书，对oracle先有一个系统的学习，然后看Oracle Concepts、oracle online document,对oracle的原理会有更深入的了解，同时可以开始进行一些专题的研究如：RMAN、RAS、STATSPACT、DATAGUARD、TUNING、BACKUP&RECOVER等等。 
  
开发：对于想做Oracle开发的，在了解完Oracle基本的体系结构之后，可以重点关注PL/SQL及Oracle的开发工具这一部分。 PL/SQL主要是包括怎么写SQL语句，怎么使用Oracle本身的函数，怎么写存储过程、存储函数、触发器等。 Oracle的开发工具主要就是Oracle自己的Developer Suite（Oracle Forms Developer and Reports Developer这些），学会如何熟练使用这些工具。介绍几本oracle入门的好书oracle官方文档：《concept》上面讲了oracle的体系和概念，很适合初学者看。OCP的教学用书，也就是STUDY GUIDE(SG)。
Oracle8i 备份恢复手册
Oracle8高级管理与优化
Oracle8i PLSQL程序设计
Oracle8数据库管理员手册
以上书本都是机械工业出版社出版。
  
介绍几个网站 
http://tahiti.oracle.com oracle的官方文档 
http://metalink.oracle.com/ oracle的技术支持网站。需要购买Oracle服务才能有一个帐号，才能登陆，有大量的Knowledge Base，大量问题解决经验。 
http://www.oracle.com oracle的官方网站，可以在这里down oracle的软件、官方文档和获得最新的消息 
http://www.dbazine.com/ Oracle的杂志 
http://asktom.oracle.com  
http://www.orafaq.net/  
http://www.ixora.com.au/ 
http://www.oracle-base.com 
http://www.dba-oracle.com/oracle_links.htm

Answer 2

[使用游标：]1:最基本的游标
declare 
  wldm erp.itm_m.itm_wldm%type;
  wlpm erp.itm_m.itm_wlpm%type;
  cursor cur is
    select itm_wldm,itm_wlpm from erp.itm_m  where itm_wldm = '10001';
begin
  open cur;
  fetch cur into wldm,wlpm;
  close cur;
  dbms_output.put_line(wldm||wlpm);end;2: 加参数的游标
declare 
  wldm erp.itm_m.itm_wldm%type;
  wlpm erp.itm_m.itm_wlpm%type;
  cursor cur(v_wldm varchar2) is
    select itm_wldm,itm_wlpm from erp.itm_m  where itm_wldm = v_wldm;
begin
  open cur('10001');
  fetch cur into wldm,wlpm;
  close cur;
  dbms_output.put_line(wldm||wlpm);
end;3:循环
set serveroutput on;
declare 
  wldm erp.itm_m.itm_wldm%type;
  wlpm erp.itm_m.itm_wlpm%type;
  cursor cur is
    select itm_wldm,itm_wlpm from erp.itm_m  ;
begin
 /* 以下为 Loop ... End Loop循环 */
  open cur;
  loop
    fetch cur into wldm,wlpm;
    exit when cur%notfound or cur%notfound is null;
    dbms_output.put_line(cur%rowcount ||' '||wldm||' '||wlpm);
  end loop;
  close cur;/* 以下为while 循环*/
  open cur;
  fetch cur into wldm,wlpm;
  while cur%found loop
    dbms_output.put_line(cur%rowcount ||' '||wldm||' '||wlpm);
    fetch cur into wldm,wlpm;
  end loop;
  close cur;  
/* 以下为For 循环*/  
  for c_cur in cur loop
     dbms_output.put_line(cur%rowcount ||' '||wldm||' '||wlpm);
  end loop;  
end;4: 使用bulk collect
declare
  type tp1 is table of erp.itm_m.itm_wlpm%type;
  type tp2 is table of erp.itm_m.itm_wlgg%type;
  v1 tp1;
  v2 tp2;
  cursor cur is
    select itm_wlpm,itm_wlgg from erp.itm_m;
begin
  open cur;
  fetch cur bulk collect into v1,v2;
  close cur;
  for i in 1..v1.count loop
    dbms_output.put_line(v1(i)||' ' ||v2(i));
  end loop;
end;5: for Update
set serveroutput on;
declare 
  wldm erp.itm_m.itm_wldm%type;
  wlpm erp.itm_m.itm_wlpm%type;
  cursor cur is
    select itm_wldm,itm_wlpm from erp.itm_m for update ;
begin
  for c_cur in cur loop
    update erp.itm_m set itm_wlpm = itm_wlpm || '1'
      where current of cur;
  end loop;
end;[if-else-endif]
set serveroutput on;
declare a int :=5;
 v1 erp.itm_m%rowtype;
begin
  select * into v1 from erp.itm_m where itm_wldm = '10001';
  if a<3 then
    dbms_output.put_line(v1.itm_wlpm);
  elsif a<5 then                                -- 是elsif 不是 elseif
    dbms_output.put_line(v1.itm_wlgg);
  else
    dbms_output.put_line(v1.itm_wldm);
  end if;
end;

Answer 3

[loop]declare i int:=0;
begin 
loop 
  i :=i+1;
  dbms_output.put_line(i);
  if i>=3 then
    exit;
  end if;
end loop;loop
  i:=i-1;
  dbms_output.put_line(i);
  exit when i<=0;
end loop;while i<=3 loop
  i:=i+1;
  dbms_output.put_line(i);
end loop;
dbms_output.put_line('i='||i);for i in 1..3 loop
--  此处不能赋值i:=i+1;
  dbms_output.put_line(i);
end loop;
dbms_output.put_line('i='||i);for i in reverse 1..3 loop
--  此处不能赋值i:=i+1;
  dbms_output.put_line(i);
end loop;
dbms_output.put_line('i='||i);
end;[goto]
declare i int:=0;
begin 
  dbms_output.put_line(i);
  loop
    i:=i+1;
    dbms_output.put_line(i);
    exit when i>=5;
    if i=3 then
      goto xxx;
    end if;
  end loop;
  return;
    <<xxx>>
  begin
    dbms_output.put_line('end  '||i);
  end xxx;  dbms_output.put_line('end  '||i);
end;

Answer 4

[decode]decode(column1,value1,output1,value2,output2,output3)
对column1列执行DECODE函数，如果column1有一个值为value1,那么将会用output1来代替当前值，如果column1有一个值为value2,那么将会用output2来代替当前值，如果column1中哪两个值都不是，那么就会用OUTPUT3来代替当前值。eg:
create table test(depID varchar2(1),Name varchar2(10));
insert into test values('A',      '一');
insert into test values('A' ,     '二');
insert into test values('A' ,     '三');
insert into test values('B'  ,    '四');
insert into test values('B'   ,   '五');
insert into test values('B' ,     '六');
insert into test values('C'    ,  '七');
insert into test values('C'     , '八');
insert into test values('C' ,     '九');select decode(depid,'A',name||'aaa','B','bbb'||name,'ccc') from test;
/*
DECODE(DEPID,
-------------
一aaa
二aaa
三aaa
bbb四
bbb五
bbb六
ccc已选择7行。
*/
[exception]
-- Exception
declare x numeric:=10;y numeric:=0;z numeric;
begin
  z:=x/y;
  dbms_output.put_line(z);
  exception
    when others then
    dbms_output.put_line('不能被0除');
end;
-- Raise 自定义Exception
declare my_exception exception;
 x numeric:=10;y numeric:=20;z numeric;
begin
  if  x<y then
    raise my_exception;
  end if;
  z:=x/y;
  dbms_output.put_line(z);
  exception
    when my_exception then
      dbms_output.put_line('被除数太小');
    when zero_divide then
      dbms_output.put_line('不能被0除');
end;[top]
-- 我們想把最先進入公司的5個人找出來
SELECT * FROM emp WHERE ROWNUM <= 5 ORDER BY hiredate;
或
SELECT * FROM (SELECT * FROM emp ORDER BY hiredate)
   WHERE ROWNUM <= 5;-- 我們想把最先進入公司的6~10個人找出來!!!!!!!!!!!!!!
SELECT * FROM (SELECT * FROM emp ORDER BY hiredate)
   WHERE ROWNUM <= 10 
MINUS
SELECT * FROM (SELECT * FROM emp ORDER BY hiredate)
   WHERE ROWNUM <= 5;
--测试：
create table test(depID varchar2(1),Name varchar2(10));
insert into test values('A',      '一');
insert into test values('A' ,     '二');
insert into test values('A' ,     '三');
insert into test values('B'  ,    '四');
insert into test values('B'   ,   '五');
insert into test values('B' ,     '六');
insert into test values('C'    ,  '七');
insert into test values('C'     , '八');
insert into test values('C' ,     '九');
--语句：
select * from test a
where rowid in (select rowid from test where depid = a.depid and rownum<3);
/*结果
D NAME
- ----------
A 一
A 二
B 四
B 五
C 七
C 八已选择6行。
*/
rownum是随着结果集生成的，一旦生成，就不会变化了；
同时,生成的结果是依次递加的，没有1就永远不会有2!  
rownum 是在 查询集合产生的过程中产生的伪列，并且如果where条件中存在 rownum 条件的话，则:  
1： 假如 判定条件是常量，则：  
只能 rownum = 1, <= 大于1 的自然数， = 大于1 的数是没有结果的， 大于一个数也是没有结果的  
即 当出现一个 rownum 不满足条件的时候则 查询结束

Answer 5

[transaction]set serveroutput on;
declare n int;
begin
  savepoint sp1;
  update erp.inv_m set inv_kcsl = inv_kcsl+10;
  n:=SQL%rowcount;
  dbms_output.put_line(n);
  
  savepoint sp2;
  update erp.inv_m set inv_kcsl = inv_kcsl+10;
  n:=SQL%rowcount;
  dbms_output.put_line(n);
  
  rollback to savepoint sp2;  
end;
[trigger]
简单的：
create or replace trigger t_itm_m
after insert on erp.itm_m
for each row
begin
  insert into erp.inv_m select :new.itm_wldm,100 from dual; 
end;create or replace trigger t_inv_m
before update on erp.inv_m
for each row
begin
  dbms_output.put_line(:old.inv_wldm||' 更改前：':old.inv_kcsl||' 更改后:'||:new.inv_kcsl);  
end;
系统触发器：
create table erp.log_table
(
  log_user varchar2(30),
  database_name varchar2(30),
  event_name varchar2(20),
  log_time date
);
1:登录
create or replace trigger user_logon
after logon on database
begin
  insert into erp.log_table (log_user,database_name,event_name,log_time)
    values(sys.login_user,sys.database_name,sys.sysevent,sysdate);
end;2：退出登录
create or replace trigger user_logoff
before logoff on database
begin
  insert into erp.log_table (log_user,database_name,event_name,log_time)
    values(sys.login_user,sys.database_name,sys.sysevent,sysdate);
end;3：启动数据库
create or replace trigger db_startup
after startup on database
begin
  insert into erp.log_table (log_user,database_name,event_name,log_time)
    values(sys.login_user,sys.database_name,sys.sysevent,sysdate);
end;
4:关闭数据库
create or replace trigger db_startup
before shutdown on database
begin
  insert into erp.log_table (log_user,database_name,event_name,log_time)
    values(sys.login_user,sys.database_name,sys.sysevent,sysdate);
end;5: 记录创建表时的各类信息
create table erp.object_created
(
  object_owner varchar2(30),
  object_name varchar2(30),
  object_type varchar2(20),
  create_user varchar2(30),
  create_time date
);create or replace trigger t_create_object
after create on erp.schema
begin
  insert into erp.object_created(
    object_owner,object_name,object_type,create_user,create_time)
  values(sys.dictionary_obj_owner,sys.dictionary_obj_name,sys.dictionary_obj_type,sys.login_user,sysdate);
end;
[function]
create or replace function f_getSum(wldm varchar2)
return numeric as
  v_num numeric(10);
begin
  select sum(inv_kcsl) into v_num
    from erp.inv_m where inv_wldm like wldm || '%';
  return v_num;
end;
--调用1：
set serveroutput on;
declare v_nm numeric(10);
begin
  v_nm := f_getsum('101');
  dbms_output.put_line(v_nm);
end;--调用2：
select f_getSum('101') from dual;[procedure]
create or replace procedure sp_inv_sl(v_sl in out numeric, v_flag in numeric :=0)
as
begin
  if v_flag = -1 then
    v_sl := -v_sl;
  else
    v_sl := v_sl;
  end if;
  update erp.inv_m set inv_kcsl = inv_kcsl + v_sl;
end;--调用:
declare sl numeric(10) ;
begin
sl :=10;
sp_inv_sl(sl,-1);
end;

Answer 6

[package]/*包头*/create or replace package my_pack
as
function f_test(n integer) return integer;
end my_pack;/*包主体*/
create or replace package body my_pack asfunction f_test(n integer) return integer is
begin
  return 1;
end f_test;end my_pack;
/*  注意： 包头和包主体需分开编译*/
eg:
create or replace package my_pack as
  type staff is table of varchar2(10);
  procedure proc1(members in staff);
end my_pack;create or replace package body my_pack as
  procedure proc1(members in staff) is
  begin
    if members.count > 10 then
      dbms_output.put_line('第一个为' || members(members.first));
    else
      dbms_output.put_line('最后一个为' || members(members.last));
    end if;
  end;
end my_pack;declare
  my_staff my_pack.staff;
  begin
    my_staff :=my_pack.staff('张三','李四','王五');
    my_pack.proc1(my_staff);
  end;

Answer 7

[嵌套表]
1:定义
create or replace type student as object
(
  vid number(4),
  vname varchar2(10),
  vsex varchar2(2),
  vaddress varchar2(40)
);create type studentlist as table of student;create table nest_table1
(
  vid number(4),
  vbj varchar2(14),
  students studentlist
)
nested table students store as students_tab;2: Insert
declare v_student studentlist :=studentlist(student(12,'张学友','男','深圳'));
begin
  insert into nest_table1 values(1002,'歌星',v_student);
  insert into nest_table1 values(1003,'球星',studentlist(student(13,'贝利','男','巴西')));
end;3: Update
update nest_table1 set students = studentlist(student(12,'张学友','男','香港'))
where vid = 1002;update the(select students from nest_table1 where vid = 1001)
set vaddress = '香港';4: Select
select * from nest_table1;select  the(select students from nest_table1 where vid = 1001)
from dual;

Answer 8

[索引表]
1:基本索引表 
set serveroutput on;
declare 
  type my_table is table of varchar(6) index by binary_integer;
  v_mytable my_table;
begin
  v_mytable(1) := 'a';
  v_mytable(2) := 'b';
  v_mytable(3) := 'c';
  dbms_output.put_line(v_mytable(1)||v_mytable(2)||v_mytable(3));
end;2：
declare
  type my_table is table of erp.itm_m%rowtype index by binary_integer;
  v_mytable my_table;
begin
  select itm_wldm,itm_wlpm,itm_wlgg into v_mytable(1)
    from erp.itm_m where itm_wldm = '10001';
  dbms_output.put_line(v_mytable(1).itm_wldm ||v_mytable(1).itm_wlpm||v_mytable(1).itm_wlgg);  
end;3:
declare
  type my_table is table of erp.itm_m%rowtype index by binary_integer;
  v_mytable my_table;
begin
  select itm_wldm,itm_wlpm,itm_wlgg into v_mytable(1)
    from erp.itm_m where itm_wldm = '10001';
  if v_mytable.exists(1) then
    dbms_output.put_line(v_mytable(1).itm_wldm ||v_mytable(1).itm_wlpm||v_mytable(1).itm_wlgg);  
  else
    dbms_output.put_line('不存在');
  end if;
end;4:
declare
  type my_table is table of erp.itm_m%rowtype index by binary_integer;
  v_mytable my_table;
begin
  select itm_wldm,itm_wlpm,itm_wlgg into v_mytable(2)
    from erp.itm_m where itm_wldm = '10001';
  if v_mytable.exists(1) then
    dbms_output.put_line(v_mytable(1).itm_wldm ||v_mytable(1).itm_wlpm||v_mytable(1).itm_wlgg);  
  else
    dbms_output.put_line('不存在');
  end if;
    
  dbms_output.put_line(v_mytable(1).itm_wldm ||v_mytable(1).itm_wlpm||v_mytable(1).itm_wlgg);  
  exception
    when no_data_found then
      dbms_output.put_line('找不到');
end;

Answer 9

[同义词]1:如下语句
select * from erp.tableName where rownum = 1;
因为表tableName是建在erp这个方案上的，每次都要写 erp.tableName才行，有没有什么设置可以用erp用户登录后就不用再写erp.tableName而直接写tableName解决办法：
1：
建同义词：
CREATE PUBLIC SYNONYM tableName FOR PLAN_TABLE;
授权：
GRANT ALL ON tableName TO PUBLIC;
2: 
用ERP用户，以NORMAL身份登录，而不是以SYSDBA身份登录。
同义词:
grant execute on hello_world to public;
create public synonym hello_world for erp.hello_world;

Answer 10

[动态SQL]1:
set serveroutput on;
declare 
  v1 erp.itm_m.itm_wldm%type; 
begin
  execute immediate 'select itm_wldm from erp.itm_m where rownum =1'
    into v1;
    dbms_output.put_line(v1);
end;2:
set serveroutput on;
declare 
  type t1 is table of erp.itm_m.itm_wldm%type;
  v1 t1;
begin
  execute immediate 'select itm_wldm from erp.itm_m where rownum <=3'
    bulk collect into v1;
  for i in v1.first .. v1.last loop
    dbms_output.put_line(v1(i));
  end loop;
end;
3:改进性能
create procedure test(par number) as
begin
execute immediate ''
end;

Answer 11

[行列转换]
http://community.csdn.net/Expert/topic/3222/3222974.xml?temp=.5841181col1      col2
-----------------
0001      Tom
0001      Jack
0001      Mike
0002      Kate
0002      Rose
0002      Fene如何用一条SQL语句得到如下结果：
col1     col2
-----------------
0001     Tom,Jack,Mike
0002     Kate,Rose,Fene创建自定义函数：
create  or  replace  function  f_ename(jobno  in  varchar2)  
return  varchar2  is  
   
str_return  varchar2(200);  
cursor  c_ename(v_jobno  varchar2)  is  select  ename  from  emp  where  job=v_jobno;  
begin  
for  str_temp  in  c_ename(jobno)  loop  
str_return:=str_return||str_temp.ename;  
end  loop;  
return  str_return;  
end;  
/  调用：
select job,f_ename(job) from (select distinct job from emp);  oracle 如何做行列动态的交叉表 
http://community.csdn.net/Expert/topic/3093/3093861.xml?temp=.5882379
/*
Create table test (name varchar2(10),km varchar2(10),cj number)
go
insert into test values('张三','语文',80);
insert into test values('张三','数学',86);
insert into test values('张三','英语',75);
insert into test values('李四','语文',78);
insert into test values('李四','数学',85);
insert into test values('李四','英语',78);
select * from test;select name,
 sum(decode(km,'语文',cj,0)) as 语文,
 sum(decode(km,'数学',cj,0)) as 数学, 
 sum(decode(km,'英语',cj,0)) as 英语
from test
group by name;
*/CREATE OR REPLACE PACKAGE Pack_Test
AS
TYPE QUERY_CURSOR IS REF CURSOR;
PROCEDURE My_cur_out(
result out QUERY_CURSOR);
END Pack_Test;
CREATE OR REPLACE PACKAGE BODY Pack_Test
AS
PROCEDURE My_cur_out(
result out QUERY_CURSOR
)
    AS
        BEGIN
        
DECLARE 
   v_result varchar2(4000);
   v_table varchar2(255):='TEST';--表名
   v_col1 varchar2(255):='NAME'; --表的第一个列
   v_col2 varchar2(255):='KM';   --表的第二个列
   v_col3 varchar2(255):='CJ';   --表的第三个列
   v_value varchar2(255);
   v_file utl_file.file_type;
   TYPE cur_type IS REF CURSOR;
   cur_row   cur_type;
BEGIN
  v_result := 'SELECT '||v_col1;
  OPEN cur_row FOR 'SELECT DISTINCT '||v_col2||' FROM '||v_table;
  LOOP
    FETCH cur_row INTO v_value;
    EXIT WHEN cur_row%NOTFOUND;  
    v_result := v_result||',sum(decode('||v_col2||','''||v_value||''','||v_col3||','''')) as '||v_value;   
  END LOOP;
  CLOSE cur_row;
  v_result := v_result||' from '||v_table||' group by '||v_col1;
  
  OPEN result FOR v_result;
END;
          
 
        END;
END Pack_Test;http://community.csdn.net/Expert/topic/3179/3179325.xml?temp=1.267642E-02
pl/sql怎么返回数据集?
CREATE OR REPLACE PACKAGE pkg_test
AS
   TYPE myrctype IS REF CURSOR;   PROCEDURE get (p_id NUMBER, p_rc OUT myrctype);
END pkg_test;
/CREATE OR REPLACE PACKAGE BODY pkg_test
AS
   PROCEDURE get (p_id NUMBER, p_rc OUT myrctype)
   IS
      sqlstr   VARCHAR2 (500);
   BEGIN
      IF p_id = 0 THEN
         OPEN p_rc FOR
            SELECT ID, NAME, sex, address, postcode, birthday
              FROM student;
      ELSE
         sqlstr :=
            'select id,name,sex,address,postcode,birthday
           from student where id=:w_id';
         OPEN p_rc FOR sqlstr USING p_id;
      END IF;
   END get;
END pkg_test;
/

Answer 12

[自增]
oracle中怎样将字段建成象SQL2000中的ID自动+1的字段
 两种方法
方法一：
　　用触发器建一个序列
   create sequence a_seq increment by 1 start with 100;
建一个触发器, 自动+1
create or replace trigger your_seq_tri
before insert on your_table1 for each row
declare
  next_id number;
begin
  select your_seq.nextval into next_id from dual;
  :new.id := next_id;
end;
方法二：
　　建一个序列
     create sequence a_seq increment by 1 start with 100;
   在语句中+1
　　insert into tbl(id,....)
     values (a_seq.nextval,....)

Answer 13

[object]create or replace type item as object
(
  wldm varchar2(15),    --明确的类型和长度，不能用%type
  wlpm varchar2(20),
  wlgg varchar2(40),
  member function get_wldm return varchar2,    -- 此处不能用 %type,不能指定varchar2的位数
  member function get_pmgg return varchar2,
  member procedure set_wldm(mwldm in varchar2)    -- 同上 (in可以不需要)
) not final;
create or replace type body erp.item as
  member function get_wldm return varchar2 is
  begin
    return wldm;
  end;
  
  member function get_pmgg return varchar2 is
  begin
    return wlpm || wlgg;
  end;
  member procedure set_wldm(mwldm varchar2) is
  begin
    wldm := mwldm;
  end;
    
end;
成员函数，如果self没被声明，它的参数缺省为IN，然而在成员过程，如果SELF没被声明，它的参数模式缺省为IN OUT，不能指定SELF的参数模式为OUT.ALTER TYPE ERP.ITEM ADD ATTRIBUTE(WLPM VARCHAR2(20));
ALTER TYPE ERP.ITEM ADD ATTRIBUTE(WLGG VARCHAR2(40));
ALTER TYPE ERP.ITEM DROP ATTRIBUTE WLPM;
ALTER TYPE ERP.ITEM DROP ATTRIBUTE WLGG;
ALTER TYPE ERP.ITEM ADD MEMBER FUNCTION get_pmgg return varchar2;drop type body erp.item;
drop type erp.item;调用示例：
declare itm erp.item := erp.item('1','a','b');
begin
  dbms_output.put_line(itm.wlpm || '  ' || itm.get_pmgg);
end;/*************************对象存入数据表字段***************************/
create table erp.test_obj
(
  id integer,
  itm erp.item
);
1:
insert into erp.test_obj values(1, erp.item('10001','歌星','刘德华') );
2:
declare itm erp.item := erp.item('10002','同事','刘文宏');
begin
  insert into erp.test_obj values(2,itm);
  insert into erp.test_obj values(3,erp.item('10003','同事','钟俊荣'));
end;
3:
update erp.test_obj D set D.itm.wlgg = '蔡然'
where id = 3;declare v_itm erp.item := erp.item('10003','同事','蔡sir');
begin
update erp.test_obj d set d.itm = v_itm
  where id = 3;
end;
/***********************对象表******************************/
create table erp.test_obj1 of erp.item;
insert into erp.test_obj1 values('10000','同事','刘文宏');
insert into erp.test_obj1 select * from erp.itm_m;

Answer 14

43.CONVERT(c,dset,sset)
将源字符串 sset从一个语言字符集转换到另一个目的dset字符集
SQL> select convert('strutz','we8hp','f7dec') "conversion" from dual;conver
------
strutz
44.HEXTORAW
将一个十六进制构成的字符串转换为二进制
45.RAWTOHEXT
将一个二进制构成的字符串转换为十六进制 46.ROWIDTOCHAR
将ROWID数据类型转换为字符类型 47.TO_CHAR(date,'format')
SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;TO_CHAR(SYSDATE,'YY
-------------------
2004/05/09 21:14:41 48.TO_DATE(string,'format')
将字符串转化为ORACLE中的一个日期
49.TO_MULTI_BYTE
将字符串中的单字节字符转化为多字节字符
SQL> select to_multi_byte('高') from dual;TO
--
高
50.TO_NUMBER
将给出的字符转换为数字
SQL> select to_number('1999') year from dual;YEAR
---------
1999
51.BFILENAME(dir,file)
指定一个外部二进制文件
SQL>insert into file_tb1 values(bfilename('lob_dir1','image1.gif'));
52.CONVERT('x','desc','source')
将x字段或变量的源source转换为desc
SQL> select sid,serial#,username,decode(command,
2 0,'none',
3 2,'insert',
4 3,
5 'select',
6 6,'update',
7 7,'delete',
8 8,'drop',
9 'other') cmd from v$session where type!='background';SID SERIAL# USERNAME CMD
--------- --------- ------------------------------ ------
1 1 none
2 1 none
3 1 none
4 1 none
5 1 none
6 1 none
7 1275 none
8 1275 none
9 20 GAO select
10 40 GAO none
53.DUMP(s,fmt,start,length)
DUMP函数以fmt指定的内部数字格式返回一个VARCHAR2类型的值
SQL> col global_name for a30
SQL> col dump_string for a50
SQL> set lin 200
SQL> select global_name,dump(global_name,1017,8,5) dump_string from global_name;GLOBAL_NAME DUMP_STRING
------------------------------ --------------------------------------------------
ORACLE.WORLD Typ=1 Len=12 CharacterSet=ZHS16GBK: W,O,R,L,D
54.EMPTY_BLOB()和EMPTY_CLOB()
这两个函数都是用来对大数据类型字段进行初始化操作的函数
55.GREATEST
返回一组表达式中的最大值,即比较字符的编码大小.
SQL> select greatest('AA','AB','AC') from dual;GR
--
AC
SQL> select greatest('啊','安','天') from dual;GR
--
天
56.LEAST
返回一组表达式中的最小值 
SQL> select least('啊','安','天') from dual;LE
--
啊
57.UID
返回标识当前用户的唯一整数
SQL> show user
USER 为"GAO"
SQL> select username,user_id from dba_users where user_id=uid;USERNAME USER_ID
------------------------------ ---------
GAO 25 58.USER
返回当前用户的名字
SQL> select user from dual;USER
------------------------------
GAO
59.USEREVN
返回当前用户环境的信息,opt可以是:
ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE
ISDBA 查看当前用户是否是DBA如果是则返回true
SQL> select userenv('isdba') from dual;USEREN
------
FALSE
SQL> select userenv('isdba') from dual;USEREN
------
TRUE
SESSION
返回会话标志
SQL> select userenv('sessionid') from dual;USERENV('SESSIONID')
--------------------
152
ENTRYID
返回会话人口标志
SQL> select userenv('entryid') from dual;USERENV('ENTRYID')
------------------
0
INSTANCE
返回当前INSTANCE的标志
SQL> select userenv('instance') from dual;USERENV('INSTANCE')
-------------------
1
LANGUAGE
返回当前环境变量
SQL> select userenv('language') from dual;USERENV('LANGUAGE')
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
LANG
返回当前环境的语言的缩写
SQL> select userenv('lang') from dual;USERENV('LANG')
----------------------------------------------------
ZHS
TERMINAL
返回用户的终端或机器的标志
SQL> select userenv('terminal') from dual;USERENV('TERMINA
----------------
GAO
VSIZE(X)
返回X的大小(字节)数
SQL> select vsize(user),user from dual;VSIZE(USER) USER
----------- ------------------------------
6 SYSTEM 60.AVG(DISTINCT|ALL)
all表示对所有的值求平均值,distinct只对不同的值求平均值
SQLWKS> create table table3(xm varchar(8),sal number(7,2));
语句已处理。
SQLWKS> insert into table3 values('gao',1111.11);
SQLWKS> insert into table3 values('gao',1111.11);
SQLWKS> insert into table3 values('zhu',5555.55);
SQLWKS> commit;SQL> select avg(distinct sal) from gao.table3;AVG(DISTINCTSAL)
----------------
3333.33SQL> select avg(all sal) from gao.table3;AVG(ALLSAL)
-----------
2592.59
61.MAX(DISTINCT|ALL)
求最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次
SQL> select max(distinct sal) from scott.emp;MAX(DISTINCTSAL)
----------------
5000
62.MIN(DISTINCT|ALL)
求最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次
SQL> select min(all sal) from gao.table3;MIN(ALLSAL)
-----------
1111.11
63.STDDEV(distinct|all)
求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差
SQL> select stddev(sal) from scott.emp;STDDEV(SAL)
-----------
1182.5032SQL> select stddev(distinct sal) from scott.emp;STDDEV(DISTINCTSAL)
-------------------
1229.951 64.VARIANCE(DISTINCT|ALL)
求协方差 SQL> select variance(sal) from scott.emp;VARIANCE(SAL)
-------------
1398313.9
65.GROUP BY
主要用来对一组数进行统计
SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno;DEPTNO COUNT(*) SUM(SAL)
--------- --------- ---------
10 3 8750
20 5 10875
30 6 9400 66.HAVING
对分组统计再加限制条件
SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno having count(*)>=5;DEPTNO COUNT(*) SUM(SAL)
--------- --------- ---------
20 5 10875
30 6 9400
SQL> select deptno,count(*),sum(sal) from scott.emp having count(*)>=5 group by deptno ;DEPTNO COUNT(*) SUM(SAL)
--------- --------- ---------
20 5 10875
30 6 9400
67.ORDER BY
用于对查询到的结果进行排序输出
SQL> select deptno,ename,sal from scott.emp order by deptno,sal desc;DEPTNO ENAME SAL
--------- ---------- ---------
10 KING 5000
10 CLARK 2450
10 MILLER 1300
20 SCOTT 3000
20 FORD 3000
20 JONES 2975
20 ADAMS 1100
20 SMITH 800
30 BLAKE 2850
30 ALLEN 1600
30 TURNER 1500
30 WARD 1250
30 MARTIN 1250
30 JAMES 950

Answer 15

FAQ中有一些好东东，初学的话一定要看看，我又要重新看起了。

Answer 16

马可，又见马可，呵呵在SQL版还不够你风光啊，又跑这里赚人气^_^

Answer 17

楼主什么时候转看ORACLE了:),呵呵.
多谢，支持.

Answer 18

zhaokeke2004(大力)： 此大力不会是彼大力吧？偶去年7,8月份时瞎摸了一阵，现在基本上全忘了。

Answer 19

txlicenhe(马可):
没有他那么强.呵呵.

Answer 20

其实我在Oracle也算是菜鸟一个。