Java开发网 Java开发网
注册 | 登录 | 帮助 | 搜索 | 排行榜 | 发帖统计  

您没有登录

» Java开发网 » Database/JDBC/SQL/JDO/Hibernate  

按打印兼容模式打印这个话题 打印话题    把这个话题寄给朋友 寄给朋友    该主题的所有更新都将Email到你的邮箱 订阅主题
flat modethreaded modego to previous topicgo to next topicgo to back
作者 ORACLE使用若干技术 [精华]
九佰



版主


发贴: 983
积分: 126
于 2003-07-09 16:48 user profilesend a private message to usersearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
怎么样让我的用户名和密码不泄漏?
=====================
在unix下,我用sqlplus sys/sys登陆,别的用户很容易就能看到我的密码:怎么办?

$ ps -ef|grep sqlplus
oracle 3787 3781 1 22:05:34 pts/3 0:00 sqlplus sys/sys
oracle 3789 3772 0 22:05:44 pts/2 0:00 grep sqlplus
采用sqlplus /nolog

sql>connect sys/sys,这样别的用户就看不到你的密码啦。

怎样生成建表的完整的DDL语句?
====================
用exp ,再Imp,show=y可以看到。

或者使用某些Oracle 的小工具,比如quest的toad和sql*navigator.

truncate table和delete table有些什么区别?
==========================
truncate: DDL ,no rollback possibility and no rollback segment usage, quick ,release space used by the table except the original one.

delete: dml, can rollback, use rollback space, not release space, slow, delete large table may cause ora-1555 error.

 

如何删除重复的记录:
=============
第一个办法: 1。生成建表的完整DDL语句,并且生成tab_bak的表名。

2。insert into tab_bak select distinct * from tab_name;

3。drop table tab_name, rename tab_bak to tab_name;

第二个办法:

DELETE FROM table_name A WHERE ROWID >
( SELECT min(rowid) FROM table_name B

WHERE A.key_values = B.key_values);


第三个办法:



Delete from my_table where rowid not in
( SQL> select max(rowid) from my_table

group by my_column_name );


第四个办法:



delete from my_table t1
where exists (select 'x' from my_table t2

where t2.key_value1 = t1.key_value1

and t2.key_value2 = t1.key_value2

and t2.rowid > t1.rowid);


如何快速为已有的表加上一个主键?
=====================
加上一个非空的列,比如seqno,然后:

update table_name set seqno=rownum;

或者:

CREATE SEQUENCE testseq START WITH 1 INCREMENT BY 1;
update table_name set seqno=testseq.nextval;

SQL排序问题:我怎么才能选择出按照某个列排序后前N行来?
=====================================
在SQL*Server 里面,可以用这样的语句:select top 10 col1,col2 from table_name;

从Oracle8i开始,支持这样的语法(在子查询里面使用order by语句)

select * from (select col1,col2 from table_name order by col1,col2)

where rownum<11;

这样就能够起到同样的效果。

在Oracle8或者以下,可以这样:

SELECT col1,col2 FROM
(SELECT /*+ INDEX_DESC (table_name index_name) */ col1,col2 FROM table_name)
WHERE rownum < 6;

使用提示可以让Oracle在子查询返回结果之前先对他进行排序,一般可以使用hintINDEX_DESC(TABLE_NAME,INDEX_NAME)来起到这个作用。

我们可以分别查看两个SQL的执行计划:

scott@testdb> select * from sort_sample;

ID NAME
---------- ----------------------------------------
1 aa
5 33
90 23s
23 fdisk
746 2343
24 format
3 low format
7 rows selected.

scott@testdb> create index sort_id_idx on sort_sample(id);

Index created.
scott@testdb> set autotrace on explain
scott@testdb> --way 1:
scott@testdb> select * from (select * from sort_sample order by id desc) where rownum<3;

ID NAME
---------- ----------------------------------------
746 2343
90 23s

Execution Plan
-------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 COUNT (STOPKEY)
2 1 VIEW
3 2 SORT (ORDER BY STOPKEY)
4 3 TABLE ACCESS (FULL) OF 'SORT_SAMPLE'

scott@testdb> --way 2 :wrong result
scott@testdb> select * from sort_sample where rownum<3;

ID NAME
---------- ----------------------------------------
1 aa
5 33

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 COUNT (STOPKEY)
2 1 TABLE ACCESS (FULL) OF 'SORT_SAMPLE'

scott@testdb> ANALYZE TABLE SORT_SAMPLE COMPUTE STATISTICS;

Table analyzed.

scott@testdb> ANALYZE INDEX SORT_ID_IDX COMPUTE STATISTICS;

Index analyzed.

scott@testdb> --way 3: can work in oracle8 and oracle7
scott@testdb> select * from (select /*+index_desc(sort_sample sort_id_idx)*/ * from sort_sample)
2 where rownum<3;

ID NAME
---------- ----------------------------------------
1 aa
5 33

//原因:col sort_id_idx列为nullable,所以CBO不能确定,加上not null约束即可达到目的。

用group by可以生成从小开始的排序:

scott@testdb> SELECT ID,NAME FROM
2 (SELECT ID,NAME,COUNT(*) FROM SORT_SAMPLE GROUP BY ID, NAME)
3 WHERE ROWNUM<3;

ID NAME
---------- ----------------------------------------
1 aa
3 low format

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=7 Bytes=175)
1 0 COUNT (STOPKEY)
2 1 VIEW (Cost=3 Card=7 Bytes=175)
3 2 SORT (GROUP BY STOPKEY) (Cost=3 Card=7 Bytes=56)
4 3 TABLE ACCESS (FULL) OF 'SORT_SAMPLE' (Cost=1 Card=7

6。怎么每隔N条记录获得一条记录?比如第3,6,9等?
=================================
CHAO@PING>select * from testseq;

ID NAME
---------- -------------------------------------
1 this is 1th record
2 this is 2th record
3 this is 3th record
4 this is 4th record
5 this is 5th record
6 this is 6th record
7 this is 7th record
8 this is 8th record
9 this is 9th record
10 this is 10th record

10 rows selected.

CHAO@PING>select id, name from
2 (select id, name, rownum rz from testseq) temp
3 where mod(rz,3)=0;

ID NAME
---------- -------------------------------------------
3 this is 3th record
6 this is 6th record
9 this is 9th record

CHAO@PING>



如何删除一个列?
===========
从Oracle8i开始,Oracle支持一个列的删除,语法如下:

alter table tab_name drop column col1;

7。如何重命名一个列?
==============
CHAO@PING> create table testrename(id number, nama varchar2(30));

Table created.

CHAO@PING> begin
2 for x in 1..10 loop
3 insert into testrename values(x,'this is '||to_char(x)||'th record');
4 end loop;
5 end;
6 /

PL/SQL procedure successfully completed.

CHAO@PING> commit;

Commit complete.

CHAO@PING> alter table testrename add name varchar2(30);

Table altered.

CHAO@PING> update testrename set name=nama;

10 rows updated.

CHAO@PING> alter table testrename drop column nama;

Table altered.

CHAO@PING> select * from testrename;

ID NAME
---------- ---------------------------------------------
1 this is 1th record
2 this is 2th record
3 this is 3th record
4 this is 4th record
5 this is 5th record
6 this is 6th record
7 this is 7th record
8 this is 8th record
9 this is 9th record
10 this is 10th record

10 rows selected.




8。强大的Decode的用法:在SQl里面实现IF-THEN-ELSE的控制?
========================================
CHAO@PING>create table testdecode(id number, sex char, name varchar2(20));

Table created.

CHAO@PING>insert into testdecode values(1,'F','this is a woman');

1 row created.

CHAO@PING>insert into testdecode values(1,'M','this is a man');

1 row created.

CHAO@PING>commit;

Commit complete.

CHAO@PING>select decode(sex,'F','女','M','男','未知') as 性别, name as 名字 from testdecode;

性别 名字
-------- ----------------------------------------
女 this is a woman
男 this is a man



9。怎样从表中随机选择一些数据?(Oracle8i/9i支持)--sample语句
==========================================
scott@QINGH> create table testsample(a number);

Table created.

scott@QINGH> begin
2 for x in 1..1000 loop
3 insert into testsample values(x);
4 end loop;
5 end;
6 /

PL/SQL procedure successfully completed.

scott@QINGH> commit;

Commit complete.

scott@QINGH> select * from testsample sample(1);

A
----------
8
156
234
373
416
469
494
603
714
827
829

A
----------
925

12 rows selected.

限制:只能够用于单个表的查询

关于数据类型转换;

long-clob: to_lob

long-varchar:in sql*plus, set copytypecheck off, and use copy command.

clob->varchar

SQL> create table lob(a clob);

Table created.

SQL> insert into lob values('this is for test');

1 row created.

SQL> commit;

Commit complete.

SQL> select dbms_lob.getlength(a) from lob;

DBMS_LOB.GETLENGTH(A)
---------------------
16

SQL> declare
2 x long;
3 y clob;
4 begin
5 select a into y from lob;
6
7 x:=dbms_lob.substr(y,dbms_lob.getlength(y),1);
8 dbms_output.put_line(x);
9 end;
10 /
this is for test

PL/SQL procedure successfully completed.

在SQLplus里面这么返回函数的执行结果?
===========================
scott@QINGHAI> create or replace function test (inputvar in varchar)
2 return varchar
3 is
4 begin
5 return upper(inputvar);
6 end;
7 /

Function created.

scott@QINGHAI> declare x varchar2(20);
2 y varchar2(20);
3 begin
4 x:='thistest';
5 select test(x) into y from dual;
6 dbms_output.put_line(y);
7 end;
8 /
THISTEST

PL/SQL procedure successfully completed.
或者:

sql>exec dbms_output.put_line(test('this is for test'));



 

初学者的一个大难题:Oracle里面的日期问题
===========================
1. init.ora中的nls_date_format几乎总是不会起作用。
这是因为: 系统参数的设置,session优先于system
而对于几乎所有的客户端, register或environment varible 中nls_lang都已被定义,从而造成nls_date_format或者被显式定义,或者根据nls_lang取得了一个缺省值。

2. 在nt的注册表中,oracle\homeX下的值优先于oracle下,所以你应该在oracle\homeX\下设置。

3. 如果还是不行,或者你有多个oracle home, 可通过在系统环境变量中设置,其作用等同于unix下.profile中设置。

4. 修改注册表中的参数,不必重新启动nt或oracle, 重新建立的新SESSION会使用新的设置。

13。关于View:什么样的View允许DML,怎么查看View的源代码,怎么修改View,怎么编译view?

scott@QINGH> CREATE TABLE MYTABLE(AA NUMBER);

Table created.

scott@QINGH> CREATE VIEW TESTVIEW AS SELECT * FROM MYTABLE;

View created.

scott@QINGH> SELECT OBJECT_NAME,OBJECT_TYPE FROM USER_OBJECTS
WHERE STATUS='INVALID';

no rows selected

scott@QINGH> DROP TABLE MYTABLE;

Table dropped.

scott@QINGH> SELECT OBJECT_NAME,OBJECT_TYPE FROM USER_OBJECTS

WHERE STATUS='INVALID';

OBJECT_NAME
-----------------------------------------------
TESTVIEW

scott@QINGH> CREATE TABLE MYTABLE(AA NUMBER);

Table created.

scott@QINGH> SELECT OBJECT_NAME,OBJECT_TYPE FROM USER_OBJECTS

WHERE STATUS='INVALID';

OBJECT_NAME
---------------------------------------------------------------
TESTVIEW

scott@QINGH> ALTER VIEW TESTVIEW COMPILE;

View altered.

scott@QINGH> SELECT OBJECT_NAME,OBJECT_TYPE FROM USER_OBJECTS

WHERE STATUS='INVALID';

no rows selected

scott@QINGH>


九佰 edited on 2003-07-24 10:42


http://www.teamlet.org

海纳百川,有容乃大
壁立千仞,无欲则刚
智者不惑,勇者无惧
止戈为武,仁者无敌

话题树型展开
人气 标题 作者 字数 发贴时间
7573 [精华] ORACLE使用若干技术 九佰 10101 2003-07-09 16:48
5090 Re:ORACLE使用若干技术 nothing 19 2003-07-09 16:57

flat modethreaded modego to previous topicgo to next topicgo to back
  已读帖子
  新的帖子
  被删除的帖子
Jump to the top of page

   Powered by Jute Powerful Forum® Version Jute 1.5.6 Ent
Copyright © 2002-2018 Cjsdn Team. All Righits Reserved. 闽ICP备05005120号
客服电话 0592-8750026    客服信箱 714923@qq.com    客服QQ 714923