分享
 
 
 

ORACLE的几个常见小问题的详细解答

王朝oracle·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

第一部分、SQL&PL/SQL

[Q]怎么样查询非凡字符,如通配符%与_

[A]select * from table where name like 'A\_%' escape '\'

[Q]如何插入单引号到数据库表中

[A]可以用ASCII码处理,其它非凡字符如&也一样,如

insert into t values('i'chr(39)'m');-- chr(39)代表字符'

或者用两个单引号表示一个

or insert into t values('I''m');-- 两个''可以表示一个'

[Q]怎样设置事务一致性

[A]set transaction [isolation level] read committed;默认语句级一致性

set transaction [isolation level] serializable;

read only; 事务级一致性

[Q]怎么样利用游标更新数据

[A]cursor c1 is

select * from tablename

where name is null for update [of column]

……

update tablename set column = ……

where current of c1;

[Q]怎样自定义异常

[A] pragma_exception_init(exception_name,error_number);

假如立即抛出异常

raise_application_error(error_number,error_msg,truefalse);

其中number从-20000到-20999,错误信息最大2048B

异常变量

SQLCODE 错误代码

SQLERRM 错误信息

[Q]十进制与十六进制的转换

[A]8i以上版本:

to_char(100,'XX')

to_number('4D','XX')

8i以下的进制之间的转换参考如下脚本

create or replace function to_base( p_dec in number, p_base in number )

return varchar2

is

l_str varchar2(255) default NULL;

l_num number default p_dec;

l_hex varchar2(16) default '0123456789ABCDEF';

begin

if ( p_dec is null or p_base is null ) then

return null;

end if;

if ( trunc(p_dec) <> p_dec OR p_dec < 0 ) then

raise PROGRAM_ERROR;

end if;

loop

l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) l_str;

l_num := trunc( l_num/p_base );

exit when ( l_num = 0 );

end loop;

return l_str;

end to_base;

/

create or replace function to_dec

( p_str in varchar2,

p_from_base in number default 16 ) return number

is

l_num number default 0;

l_hex varchar2(16) default '0123456789ABCDEF';

begin

if ( p_str is null or p_from_base is null ) then

return null;

end if;

for i in 1 .. length(p_str) loop

l_num := l_num * p_from_base + instr(l_hex,upper(substr(p_str,i,1)))-1;

end loop;

return l_num;

end to_dec;

/

[Q]能不能介绍SYS_CONTEXT的具体用法

[A]利用以下的查询,你就明白了

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

[Q]怎么获得今天是星期几,还关于其它日期函数用法

[A]可以用to_char来解决,如

select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;

在获取之前可以设置日期语言,如

ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';

还可以在函数中指定

select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;

其它更多用法,可以参考to_char与to_date函数

如获得完整的时间格式

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') fromdual;

随便介绍几个其它函数的用法:

本月的天数

SELECT to_char(last_day(SYSDATE),'dd') days FROM dual

今年的天数

select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual

下个星期一的日期

SELECT Next_day(SYSDATE,'monday') FROM dual

[Q]随机抽取前N条记录的问题

[A]8i以上版本

select * from (select * from tablename order by sys_guid()) where rownum < N;

select * from (select * from tablename order by dbms_random.value) where rownum< N;

注:dbms_random包需要手工安装,位于$Oracle_HOME/rdbms/admin/dbmsrand.sql

dbms_random.value(100,200)可以产生100到200范围的随机数

[Q]抽取从N行到M行的记录,如从20行到30行的记录

[A]select * from (select rownum id,t.* from table where ……

and rownum <= 30) where id > 20;

[Q]怎么样抽取重复记录

[A]select * from table t1 where where t1.rowed !=

(select max(rowed) from table t2

where t1.id=t2.id and t1.name=t2.name)

或者

select count(*), t.col_a,t.col_b from table t

group by col_a,col_b

having count(*)>1

假如想删除重复记录,可以把第一个语句的select替换为delete

[Q]怎么样设置自治事务

[A]8i以上版本,不影响主事务

pragma autonomous_transaction;

……

commitrollback;

[Q]怎么样在过程中暂停指定时间

[A]DBMS_LOCK包的sleep过程

如:dbms_lock.sleep(5);表示暂停5秒。

[Q]怎么样快速计算事务的时间与日志量

[A]可以采用类似如下的脚本

DECLARE

start_time NUMBER;

end_time NUMBER;

start_redo_size NUMBER;

end_redo_size NUMBER;

BEGIN

start_time := dbms_utility.get_time;

SELECT VALUE INTO start_redo_size FROM v$mystat m,v$statname s

WHERE m.STATISTIC#=s.STATISTIC#

AND s.NAME='redo size';

--transaction start

INSERT INTO t1

SELECT * FROM All_Objects;

--other dml statement

COMMIT;

end_time := dbms_utility.get_time;

SELECT VALUE INTO end_redo_size FROM v$mystat m,v$statname s

WHERE m.STATISTIC#=s.STATISTIC#

AND s.NAME='redo size';

dbms_output.put_line('Escape Time:'to_char(end_time-start_time)' centiseconds');

dbms_output.put_line('Redo Size:'to_char(end_redo_size-start_redo_size)' bytes');

END;

[Q]怎样创建临时表

[A]8i以上版本

create global temporary tablename(column list)

on commit preserve rows;--提交保留数据 会话临时表

on commit delete rows;--提交删除数据 事务临时表

临时表是相对于会话的,别的会话看不到该会话的数据。

[Q]怎么样在PL/SQL中执行DDL语句

[A]1、8i以下版本dbms_sql包

2、8i以上版本还可以用

execute immediate sql;

dbms_utility.exec_ddl_statement('sql');

[Q]怎么样获取IP地址

[A]服务器(817以上):utl_inaddr.get_host_address

客户端:sys_context('userenv','ip_address')

[Q]怎么样加密存储过程

[A]用wrap命令,如(假定你的存储过程保存为a.sql)

wrap iname=a.sql

PL/SQL Wrapper: Release 8.1.7.0.0 - ProdUCtion on Tue Nov 27 22:26:48 2001

Copyright (c) Oracle Corporation 1993, 2000.All Rights Reserved.

Processing a.sql to a.plb

提示a.sql转换为a.plb,这就是加密了的脚本,执行a.plb即可生成加密了的存储过程

[Q]怎么样在ORACLE中定时运行存储过程

[A]可以利用dbms_job包来定时运行作业,如执行存储过程,一个简单的例子,提交一个作业:

VARIABLE jobno number;

BEGIN

DBMS_JOB.SUBMIT(:jobno, 'ur_procedure;',SYSDATE,'SYSDATE + 1');

commit;

END;

之后,就可以用以下语句查询已经提交的作业

select * from user_jobs;

[Q]怎么样从数据库中获得毫秒

[A]9i以上版本,有一个timestamp类型获得毫秒,如

SQL>select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ssxff') time1,

to_char(current_timestamp) time2 from dual;

TIME1 TIME2

----------------------------- ----------------------------------------------------------------

2003-10-24 10:48:45.65600024-OCT-03 10.48.45.656000 AM +08:00

可以看到,毫秒在to_char中对应的是FF。

8i以上版本可以创建一个如下的Java函数

SQL>create or replace and compile

java source

named "MyTimestamp"

as

import java.lang.String;

import java.sql.Timestamp;

public class MyTimestamp

{

public static String getTimestamp()

{

return(new Timestamp(System.currentTimeMillis())).toString();

}

};

SQL>java created.

注:注重java的语法,注重大小写

SQL>create or replace function my_timestamp return varchar2

as language java

name 'MyTimestamp.getTimestamp() return java.lang.String';

/

SQL>function created.

SQL>select my_timestamp,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') ORACLE_TIME from dual;

MY_TIMESTAMP ORACLE_TIME

------------------------ -------------------

2003-03-17 19:15:59.6882003-03-17 19:15:59

假如只想获得1/100秒(hsecs),还可以利用dbms_utility.get_time

[Q]假如存在就更新,不存在就插入可以用一个语句实现吗

[A]9i已经支持了,是Merge,但是只支持select子查询,

假如是单条数据记录,可以写作select …… from dual的子查询。

语法为:

MERGE INTO table

USING data_source

ON (condition)

WHEN MATCHED THEN update_clause

WHEN NOT MATCHED THEN insert_clause;

MERGE INTO course c

USING (SELECT course_name, period,

course_hours

FROM course_updates) cu

ON (c.course_name = cu.course_name

AND c.period = cu.period)

WHEN MATCHED THEN

UPDATE

SET c.course_hours = cu.course_hours

WHEN NOT MATCHED THEN

INSERT (c.course_name, c.period,

c.course_hours)

VALUES (cu.course_name, cu.period,

cu.course_hours);

[Q]怎么实现左联,右联与外联

[A]在9i以前可以这么写:

左联:

select a.id,a.name,b.address from a,b

where a.id=b.id(+)

右联:

select a.id,a.name,b.address from a,b

where a.id(+)=b.id

外联

SELECT a.id,a.name,b.address

FROM a,b

WHERE a.id = b.id(+)

UNION

SELECT b.id,'' name,b.address

FROM b

WHERE NOT EXISTS (

SELECT * FROM a

WHERE a.id = b.id);

在9i以上,已经开始支持SQL99标准,所以,以上语句可以写成:

默认内部联结:

select a.id,a.name,b.address,c.subject

from (a inner join b on a.id=b.id)

inner join c on b.name = c.name

where other_clause

左联

select a.id,a.name,b.address

from a left outer join b on a.id=b.id

where other_clause

右联

select a.id,a.name,b.address

from a right outer join b on a.id=b.id

where other_clause

外联

select a.id,a.name,b.address

from a full outer join b on a.id=b.id

where other_clause

or

select a.id,a.name,b.address

from a full outer join b using (id)

where other_clause

[Q]怎么实现一条记录根据条件多表插入

[A]9i以上可以通过Insert all语句完成,仅仅是一个语句,如:

INSERT ALL

WHEN (id=1) THEN

INTO table_1 (id, name)

values(id,name)

WHEN (id=2) THEN

INTO table_2 (id, name)

values(id,name)

ELSE

INTO table_other (id, name)

values(id, name)

SELECT id,name

FROM a;

假如没有条件的话,则完成每个表的插入,如

INSERT ALL

INTO table_1 (id, name)

values(id,name)

INTO table_2 (id, name)

values(id,name)

INTO table_other (id, name)

values(id, name)

SELECT id,name

FROM a;

[Q]如何实现行列转换

[A]1、固定列数的行列转换

studentsubject grade

---------------------------

student1语文 80

student1数学 70

student1英语 60

student2语文 90

student2数学 80

student2 英语 100

……

转换为

语文数学英语

student1 8070 60

student2 9080 100

……

语句如下:

select student,sum(decode(subject,'语文', grade,null)) "语文",

sum(decode(subject,'数学', grade,null)) "数学",

sum(decode(subject,'英语', grade,null)) "英语"

from table

group by student

2、不定列行列转换

c1 c2

--------------

1我

1是

1谁

2知

2道

3不

……

转换为

1 我是谁

2 知道

3 不

这一类型的转换必须借助于PL/SQL来完成,这里给一个例子

CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER)

RETURN VARCHAR2

IS

Col_c2 VARCHAR2(4000);

BEGIN

FOR cur IN (SELECT c2 FROM t WHERE c1=tmp_c1) LOOP

Col_c2 := Col_c2cur.c2;

END LOOP;

Col_c2 := rtrim(Col_c2,1);

RETURN Col_c2;

END;

/

SQL> select distinct c1 ,get_c2(c1) cc2 from table;即可

[Q]怎么样实现分组取前N条记录

[A]8i以上版本,利用分析函数

如获取每个部门薪水前三名的员工或每个班成绩前三名的学生。

Select * from

(select depno,ename,sal,row_number() over (partition by depno

order by sal desc) rn

from emp)

where rn<=3

[Q]怎么样把相邻记录合并到一条记录

[A]8i以上版本,分析函数lag与lead可以提取后一条或前一天记录到本记录。

Select deptno,ename,hiredate,lag(hiredate,1,null) over

(partition by deptno order by hiredate,ename) last_hire

from emp

order by depno,hiredate

[Q]如何取得一列中第N大的值?

[A]select * from

(select t.*,dense_rank() over (order by t2 desc) rank from t)

where rank = [$N]

[Q]怎么样把查询内容输出到文本

[A]用spool如

如sqlplus –s" / as sysdba" <

set heading off

set feedback off

spool temp.txt

select * from tab;

dbms_output.put_line(‘test’);

spool off

exit

EOF

[Q] 如何在SQL*PLUS环境中执行OS命令?

[A] 比如进入了SQLPLUS,启动了数据库,忽然想起监听还没有启动,此时不用退出SQLPLUS,也不用另外起一个命令行窗口,直接输入:

SQL> host lsntctl start

或者unix/Linux平台下

SQL>!

windows平台下

SQL>$

总结:HOST 可以直接执行OS命令。

备注:cd命令无法正确执行。

[Q]怎么设置存储过程的调用者权限

[A]普通存储过程都是所有者权限,假如想设置调用者权限,请参考如下语句

create or replace

procedure ……()

AUTHID CURRENT_USER

As

begin

……

end;

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有