分享
 
 
 

ORACLE之常用FAQ V1.0 (1)

王朝oracle·作者佚名  2006-01-09
窄屏简体版  字體: |||超大  

第一部分、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,true|false);

其中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 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;

……

commit|rollback;

[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.656000 24-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.688 2003-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、固定列数的行列转换

student subject grade

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

student1 语文 80

student1 数学 70

student1 英语 60

student2 语文 90

student2 数学 80

student2 英语 100

……

转换为

语文 数学 英语

student1 80 70 60

student2 90 80 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_c2||cur.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 host lsntctl start

或者unix/linux平台下

SQL>!

windows平台下

SQL>$

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

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

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

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

create or replace

procedure ……()

AUTHID CURRENT_USER

As

begin

……

end;

[Q]怎么快速获得用户下每个表或表分区的记录数

[A]可以分析该用户,然后查询user_tables字典,或者采用如下脚本即可

SET SERVEROUTPUT ON SIZE 20000

DECLARE

miCount INTEGER;

BEGIN

FOR c_tab IN (SELECT table_name FROM user_tables) LOOP

EXECUTE IMMEDIATE 'select count(*) from "' || c_tab.table_name || '"' into miCount;

dbms_output.put_line(rpad(c_tab.table_name,30,'.') || lpad(miCount,10,'.'));

--if it is partition table

SELECT COUNT(*) INTO miCount FROM User_Part_Tables WHERE table_name = c_tab.table_name;

IF miCount >0 THEN

FOR c_part IN (SELECT partition_name FROM user_tab_partitions WHERE table_name = c_tab.table_name) LOOP

EXECUTE IMMEDIATE 'select count(*) from ' || c_tab.table_name || ' partition (' || c_part.partition_name || ')'

INTO miCount;

dbms_output.put_line(' '||rpad(c_part.partition_name,30,'.') || lpad(miCount, 10,'.'));

END LOOP;

END IF;

END LOOP;

END;

[A]怎么在Oracle中发邮件

[Q]可以利用utl_smtp包发邮件,以下是一个发送简单邮件的例子程序

/****************************************************************************

parameter: Rcpter in varchar2 接收者邮箱

Mail_Content in Varchar2 邮件内容

desc: ·发送邮件到指定邮箱

·只能指定一个邮箱,如果需要发送到多个邮箱,需要另外的辅助程序

****************************************************************************/

CREATE OR REPLACE PROCEDURE sp_send_mail( rcpter IN VARCHAR2,

mail_content IN VARCHAR2)

IS

conn utl_smtp.connection;

--write title

PROCEDURE send_header(NAME IN VARCHAR2, HEADER IN VARCHAR2) AS

BEGIN

utl_smtp.write_data(conn, NAME||': '|| HEADER||utl_tcp.CRLF);

END;

BEGIN

--opne connect

conn := utl_smtp.open_connection('smtp.com');

utl_smtp.helo(conn, 'oracle');

utl_smtp.mail(conn, 'oracle info');

utl_smtp.rcpt(conn, Rcpter);

utl_smtp.open_data(conn);

--write title

send_header('From', 'Oracle Database');

send_header('To', '"Recipient" ');

send_header('Subject', 'DB Info');

--write mail content

utl_smtp.write_data(conn, utl_tcp.crlf || mail_content);

--close connect

utl_smtp.close_data(conn);

utl_smtp.quit(conn);

EXCEPTION

WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN

BEGIN

utl_smtp.quit(conn);

EXCEPTION

WHEN OTHERS THEN

NULL;

END;

WHEN OTHERS THEN

NULL;

END sp_send_mail;

[A]怎么样在Oracle中写操作系统文件,如写日志

[Q]可以利用utl_file包,但是,在此之前,要注意设置好Utl_file_dir初始化参数

/**************************************************************************

parameter:textContext in varchar2 日志内容

desc: ·写日志,把内容记到服务器指定目录下

·必须配置Utl_file_dir初始化参数,并保证日志路径与Utl_file_dir路径一致或者是其中一个

****************************************************************************/

CREATE OR REPLACE PROCEDURE sp_Write_log(text_context VARCHAR2)

IS

file_handle utl_file.file_type;

Write_content VARCHAR2(1024);

Write_file_name VARCHAR2(50);

BEGIN

--open file

write_file_name := 'db_alert.log';

file_handle := utl_file.fopen('/u01/logs',write_file_name,'a');

write_content := to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss')||'||'||text_context;

--write file

IF utl_file.is_open(file_handle) THEN

utl_file.put_line(file_handle,write_content);

END IF;

--close file

utl_file.fclose(file_handle);

EXCEPTION

WHEN OTHERS THEN

BEGIN

IF utl_file.is_open(file_handle) THEN

utl_file.fclose(file_handle);

END IF;

EXCEPTION

WHEN OTHERS THEN

NULL;

END;

END sp_Write_log;

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
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- 王朝網路 版權所有