首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > oracle >

经典SQL语句收集（ORACLE）,该怎么处理

2012-02-21 
经典SQL语句收集（ORACLE）1、经典的查询语句2、经典的字定义函数3、经典的与业务相关的存储过程等等抛砖引玉：

经典SQL语句收集（ORACLE）
1、经典的查询语句
2、经典的字定义函数
3、经典的与业务相关的存储过程
等等



抛砖引玉：备注本人彩票迷
（有点缺陷）
create or replace function f_ssqrandom(priornum in integer,endnum in integer)
 return varchar2 
 is
 v_prior_rand integer :=0;
 v_end_rand integer;
 v_string string(32000):='红色球';
begin
for v in 1..5 loop
for v_count in 1..3 loop
  v_prior_rand:=floor(dbms_random.value(1,priornum));
  
  v_end_rand:=v_prior_rand;
  <<random>>
  v_prior_rand:=floor(dbms_random.value(1,priornum));
  if v_prior_rand!=v_end_rand then
  v_string:=v_string||','||v_prior_rand||','||v_end_rand;
  else
  goto random;
  end if;
  dbms_output.put_line(v_string);
  end loop;
  v_string:=v_string||' 蓝色球, '||floor(dbms_random.value(1,endnum))||' 红色球';
  end loop;
  return v_string;
 
end f_ssqrandom;

  希望不要恶意回帖 MARK 顶的大哥大姐 放心理就行 小弟心领了

[解决办法]
查询新建用户
select username
from dba_users
 where username not in
('TEXT', 'RMAN_USER', 'TEST', 'SCOTT', 'TSMSYS', 'MDDATA', 'DIP',
'DBSNMP', 'SYSMAN', 'MDSYS', 'ORDSYS', 'EXFSYS', 'DMSYS', 'WMSYS',
'CTXSYS', 'ANONYMOUS', 'XDB', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA',
'OLAPSYS', 'MGMT_VIEW', 'SYS', 'SYSTEM', 'OUTLN');


查询那些用户，操纵了那些表造成了锁机 
SELECT s.username, 
decode(l.type,'TM','TABLE LOCK', 
'TX','ROW LOCK', 
NULL) LOCK_LEVEL, 
o.owner,o.object_name,o.object_type, 
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser 
FROM v$session s,v$lock l,all_objects o 
WHERE l.sid = s.sid 
AND l.id1 = o.object_id(+) 
AND s.username is NOT Null 
其中 TM 为表锁定 TX 为行锁定


看锁阻塞的方法是 
SELECT (select username FROM v$session WHERE sid=a.sid) blocker, 
a.sid, 
'is blocking', 
(select username FROM v$session WHERE sid=b.sid) blockee, 
b.sid 
FROM v$lock a, v$lock b 
WHERE a.block = 1 
AND b.request > 0 
AND a.id1 = b.id1 
AND a.id2 = b.id2
[解决办法]
比如：获取系统信息：
select
SYS_CONTEXT('USERENV','TERMINAL') terminal,
SYS_CONTEXT('USERENV','LANGUAGE') language,
SYS_CONTEXT('USERENV','SESSIONID') sessionid,
SYS_CONTEXT('USERENV','INSTANCE') instance,
SYS_CONTEXT('USERENV','ENTRYID') entryid,
SYS_CONTEXT('USERENV','ISDBA') isdba,
SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory,
SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency,
SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar,
SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') nls_date_format,
SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language,
SYS_CONTEXT('USERENV','NLS_SORT') nls_sort,
SYS_CONTEXT('USERENV','CURRENT_USER') current_user,
SYS_CONTEXT('USERENV','CURRENT_USERID') current_userid,
SYS_CONTEXT('USERENV','SESSION_USER') session_user,
SYS_CONTEXT('USERENV','SESSION_USERID') session_userid,
SYS_CONTEXT('USERENV','PROXY_USER') proxy_user,
SYS_CONTEXT('USERENV','PROXY_USERID') proxy_userid,
SYS_CONTEXT('USERENV','DB_DOMAIN') db_domain,
SYS_CONTEXT('USERENV','DB_NAME') db_name,
SYS_CONTEXT('USERENV','HOST') host,
SYS_CONTEXT('USERENV','OS_USER') os_user,
SYS_CONTEXT('USERENV','EXTERNAL_NAME') external_name,


SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address,
SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') network_protocol,
SYS_CONTEXT('USERENV','BG_JOB_ID') bg_job_id,
SYS_CONTEXT('USERENV','FG_JOB_ID') fg_job_id,
SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE')
authentication_type,
SYS_CONTEXT('USERENV','AUTHENTICATION_DATA')
authentication_data
from dual
[解决办法]

SQL code
Oracle计算时间差表达式 --获取两时间的相差豪秒数select ceil((To_date('2008-05-02 00:00:00' , 'yyyy-mm-dd hh24-mi-ss') - To_date('2008-04-30 23:59:59' , 'yyyy-mm-dd hh24-mi-ss')) * 24 * 60 * 60 * 1000) 相差豪秒数 FROM DUAL;/*相差豪秒数----------  864010001 row selected*/--获取两时间的相差秒数select ceil((To_date('2008-05-02 00:00:00' , 'yyyy-mm-dd hh24-mi-ss') - To_date('2008-04-30 23:59:59' , 'yyyy-mm-dd hh24-mi-ss')) * 24 * 60 * 60) 相差秒数 FROM DUAL;/*相差秒数----------     864011 row selected*/--获取两时间的相差分钟数select ceil(((To_date('2008-05-02 00:00:00' , 'yyyy-mm-dd hh24-mi-ss') - To_date('2008-04-30 23:59:59' , 'yyyy-mm-dd hh24-mi-ss'))) * 24 * 60)  相差分钟数 FROM DUAL;/*相差分钟数----------      14411 row selected*/--获取两时间的相差小时数select ceil((To_date('2008-05-02 00:00:00' , 'yyyy-mm-dd hh24-mi-ss') - To_date('2008-04-30 23:59:59' , 'yyyy-mm-dd hh24-mi-ss')) * 24)  相差小时数 FROM DUAL;/*相差小时数----------        251 row selected*/--获取两时间的相差天数select ceil((To_date('2008-05-02 00:00:00' , 'yyyy-mm-dd hh24-mi-ss') - To_date('2008-04-30 23:59:59' , 'yyyy-mm-dd hh24-mi-ss')))  相差天数 FROM DUAL;/*相差天数----------         21 row selected*/--获取两时间月份差select (EXTRACT(year FROM to_date('2009-05-01','yyyy-mm-dd')) - EXTRACT(year FROM to_date('2008-04-30','yyyy-mm-dd'))) * 12 +        EXTRACT(month FROM to_date('2008-05-01','yyyy-mm-dd')) - EXTRACT(month FROM to_date('2008-04-30','yyyy-mm-dd')) monthsfrom dual;/*MONTHS----------        131 row selected*/--获取两时间年份差select EXTRACT(year FROM to_date('2009-05-01','yyyy-mm-dd')) - EXTRACT(year FROM to_date('2008-04-30','yyyy-mm-dd')) years from dual;/*YEARS----------         11 row selected*/
[解决办法]
大家用过吗?下面这个
SQL code
--Insert multiple rows into different tables with a single statement: INSERT ALL   WHEN type=1 THEN INTO tab1 VALUES (myseq.NEXTVAL, val)   WHEN type=2 THEN INTO tab2 VALUES (myseq.NEXTVAL, val)   WHEN type IN (3,4,5) THEN INTO tab3 VALUES (myseq.NEXTVAL, val)   ELSE INTO tab4 VALUES (myseq.NEXTVAL, val)SELECT type, val FROM source_tab;
[解决办法]
--动态sql创建ORACLE存储过程.txt
create or replace procedure p3(v_tname varchar2) as
 i       number;
 m       number;
 /*v_tname varchar2(10) := 't1';*/
 e_createerror exception;
begin
  execute immediate 'select count(*) from all_tables where table_name =''' ||
          upper(v_tname) || '''' into i;
 if i > 0 then
   dbms_output.put_line('table is also exists');
   execute immediate 'drop table ' || v_tname;
 end if;
 begin
   execute immediate 'create table ' || v_tname || ' (id1 number)';
 exception
   when others then
     raise e_createerror;
 end;
 execute immediate 'insert into ' || v_tname || '  values (3)';
 execute immediate 'insert into ' || v_tname || '  values (3)';
 execute immediate 'insert into ' || v_tname || '  values (3)';
 execute immediate 'insert into ' || v_tname || '  values (3)';
 commit;
 execute immediate ' select count(*) from all_tables where table_name =''' ||upper(v_tname) || ''''
   into m;

 if m > 0 then
   --execute immediate ' select count(*) from '''||upper(v_tname) || '''' into m;
   dbms_output.put_line('good1!');dbms_output.put_line('count of v_tname:'||m); 
 
 end if;
exception
 when e_createerror then
   dbms_output.put_line('表创建语句出错请检查');
end;
/
[解决办法]
-- 定义游标  
declare  
 cursor aa is  
     select names,num from test;  
begin  
 for bb in aa  
 loop  
       if bb.names = "ORACLE" then  
      
       end if  
 end loop;    
end  
论坛里的例子:  
create or replace procedure test is  
cursor v_cur_6(ids varchar2) is  
     select * from T where instr(','||ids||',',','||to_char(A)||',')=0;  
v_cur_line_6 v_cur_6%ROWTYPE;  
begin  
   open v_cur_6('1,2');  
   loop  

     fetch v_cur_6 into v_cur_line_6;  
     exit when v_cur_6%notfound;  
     dbms_output.put_line('---------');  
   end loop;  
   close v_cur_6;  
end;
[解决办法]
我也看到一篇文章，分享
http://www.cnblogs.com/liuweitoo/archive/2007/04/02/697293.html
[解决办法]
以工作分组，按名称排序，取出汇总每个工作下的人员名称，并以“,”符号作为分隔符
SQL code
select job, ltrim(sys_connect_by_path(ename, ','), ',') scbp  from (select job,               ename,               row_number() over(partition by job order by ename) rn,               count(*) over(partition by job) cnt          from scott.emp) t where rn = cnt start with rn = 1connect by prior job = job       and prior rn = rn - 1 order by job
[解决办法]
SQL code
-- 定义游标 declare   cursor aa is       select names,num from test; begin   for bb in aa   loop         if bb.names = "ORACLE" then                 end if   end loop;     end 论坛里的例子: create or replace procedure test is cursor v_cur_6(ids varchar2) is       select * from T where instr(','||ids||',',','||to_char(A)||',')=0; v_cur_line_6 v_cur_6%ROWTYPE; begin     open v_cur_6('1,2');     loop       fetch v_cur_6 into v_cur_line_6;       exit when v_cur_6%notfound;       dbms_output.put_line('---------');     end loop;     close v_cur_6; end;
[解决办法]
如何查找、删除表中重复的记录

方法原理： 
1、Oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的，  
　 rowid确定了每条记录是在ORACLE中的哪一个数据文件、块、行上。  

2、在重复的记录中，可能所有列的内容都相同，但rowid不会相同，所以只要确定出重复记录中  
　 那些具有最大rowid的就可以了，其余全部删除。

实现方法： 
SQL> create table a (  
 2  bm char(4), --编码  
 3  mc varchar2(20) --名称  
 4  )  
 5  /  

表已建立.  

SQL> insert into a values('1111','1111');  
SQL> insert into a values('1112','1111');  
SQL> insert into a values('1113','1111');  
SQL> insert into a values('1114','1111');  

SQL> insert into a select * from a;  

插入4个记录.  

SQL> commit;  

完全提交.  

SQL> select rowid,bm,mc from a;  

ROWID              BM   MC  
------------------ ---- -------  
000000D5.0000.0002 1111 1111  
000000D5.0001.0002 1112 1111  
000000D5.0002.0002 1113 1111  
000000D5.0003.0002 1114 1111  
000000D5.0004.0002 1111 1111  
000000D5.0005.0002 1112 1111  
000000D5.0006.0002 1113 1111  
000000D5.0007.0002 1114 1111  

查询到8记录.  


查出重复记录  
SQL> select rowid,bm,mc from a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc);  

ROWID              BM   MC   
 
------------------ ---- --------------------  
000000D5.0000.0002 1111 1111  
000000D5.0001.0002 1112 1111  
000000D5.0002.0002 1113 1111  
000000D5.0003.0002 1114 1111  

删除重复记录  
SQL> delete from a a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc);  

删除4个记录.  

SQL> select rowid,bm,mc from a;  

ROWID              BM   MC  
------------------ ---- --------------------  
000000D5.0004.0002 1111 1111  
000000D5.0005.0002 1112 1111  
000000D5.0006.0002 1113 1111  
000000D5.0007.0002 1114 1111  

[解决办法]
手动创建oracle9i数据库具体步骤

在linux操作系统上,以oracle用户名登录
1、停止linux上运行的数据库实例
2、更改linux上ORACLE_SID环境参数
    ORACLE_SID=gaogao
    export ORACLE_SID
3、手动添加文件目录
   mkdir /opt/oracle/admin/gaogao
   mkdir /opt/oracle/admin/gaogao/bdump
   mkdir /opt/oracle/admin/gaogao/cdump
   mkdir /opt/oracle/admin/gaogao/udump
   mkdir /opt/oracle/admin/gaogao/pfile （这个目录为个人爱好，不是必须）
mkdir /opt/oracle/oradata/gaogao
4、创建参数文件init(sid).ora
Create pfile from spfile;
这样就可以在dbs目录下自动创建一个init(sid).ora文件.然后根据这个文件,略作修改,可以创建initgaogao.ora参数文件,然后放到/opt/oracle/admin/gaogao/pfile目录一个,在放到/opt/oracle/product/9.2.0/dbs一个就ok了.
5.创建密码文件orapw(sid)
[oracle@test oracle]$ orapwd file=/opt/oracle/product/9.2.0/dbs/orapwgaogao password=bscy entries=5
这样就创建了一个针对gaogao实例的密码文件.默认放到/opt/oracle/product/9.2.0/dbs目录下.
6、以nomount状态启动oracle实例
sqlplus /nolog
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup nomount  
ORACLE instance started.
SQL>start 创建数据库的sql语句文件的路径
以下是创建数据库的标准SQL语句:
CREATE DATABASE gao
LOGFILE
GROUP 1 '/opt/oracle/oradata/gaogao/redo01.log'  SIZE 100M,
GROUP 2 '/opt/oracle/oradata/gaogao/redo02.log'  SIZE 100M,
GROUP 3 '/opt/oracle/oradata/gaogao/redo03.log'  SIZE 100M
MAXINSTANCES 8  
MAXLOGHISTORY 100  
MAXLOGMEMBERS 3  
MAXLOGFILES 16
MAXDATAFILES 254
NOARCHIVELOG
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/opt/oracle/oradata/gaogao/system_01_gaogao.dbf' SIZE 100M
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
UNDO TABLESPACE UNDOTBS1
DATAFILE '/opt/oracle/oradata/gaogao/undogaogao01.dbf' SIZE 35M
DEFAULT TEMPORARY TABLESPACE TEMP
TEMPFILE '/opt/oracle/oradata/gaogao/tempgaogao01.dbf' SIZE 20M;

等待大约2分钟后提示Database created. 建库成功.

7：建立数据字典的文件是：  

$ORACLE_HOME/rdbms/admin/catalog.sql  

$ORACLE_HOME/rdbms/admin/catproc.sql  

$ORACLE_HOME/rdbms/admin/catexp.sql  


二、心得体会
1、spfile文件不是建库必须，可以待数据库建成后再生成
2、pwd文件不是建库必须，可以待数据库建成后再生成
3、参数文件和建库脚本的相关内容必须匹配，特别是SID，datafile文件的位置和UNDO表空间的名字
4、建库一旦发生错误，所有的错误信息都会记录在/opt/oracle/admin/myocp/bdump目录的alert_myocp.log文件中，准确的定位错误才能修正错误
5、dbca是个好东西，但对它形成依赖后，在实际工作中就得启动X窗口并且必须在服务器上操作，有很大的局限性，所以学习OCP的同仁必须掌握手动建库。
读书人精选
热点排行