经典SQL语句收集(ORACLE)

来源：互联网发布：浙江网络干部学院编辑：程序博客网时间：2024/04/29 06:53

1.彩票（有点缺陷）

2.查询新建用户

3.查询那些用户，操纵了那些表造成了锁机

4.看锁阻塞的方法是

5.各进制之间转换

6.获取系统信息

7.Oracle计算时间差表达式

8.不同的行insert到不同的表中 insert all /first

9.动态sql创建ORACLE存储过程

10.模糊查询

11.跟踪当前对话下用户的SQL脚本

12.刪除相同行

13.获得列乘积

14. 基本命令

15" target="_parent">15.判断字段是否可转换为整数

16.类似于SEQUENCE的函数，从目标表中返回一个最大的序号值（18位，36进制)

17.分析sql语句的执行效率

18.表维护树形结构的应用

19.找出比平均值大的数

20.程序中反SOFTICE跟踪法

21.实现行列转换

22.万年日历

1.彩票（有点缺陷）
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;

2.查询新建用户

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');

3.查询那些用户，操纵了那些表造成了锁机

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 为行锁定

4.看锁阻塞的方法是

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

5.各进制之间转换

CREATE OR REPLACE TYPE type_str_agg AS OBJECT
(
total VARCHAR2(4000),

STATIC FUNCTION odciaggregateinitialize(sctx IN OUT type_str_agg)
RETURN NUMBER,

MEMBER FUNCTION odciaggregateiterate
(
SELF IN OUT type_str_agg,
VALUE IN VARCHAR2
) RETURN NUMBER,

MEMBER FUNCTION odciaggregateterminate
(
SELF IN type_str_agg,
returnvalue OUT VARCHAR2,
flags IN NUMBER
) RETURN NUMBER,

MEMBER FUNCTION odciaggregatemerge
(
SELF IN OUT type_str_agg,
ctx2 IN type_str_agg
) RETURN NUMBER
)
/
CREATE OR REPLACE TYPE BODY type_str_agg IS

STATIC FUNCTION odciaggregateinitialize(sctx IN OUT type_str_agg)
RETURN NUMBER IS
BEGIN
sctx := type_str_agg(NULL);
RETURN odciconst.success;
END;

MEMBER FUNCTION odciaggregateiterate
(
SELF IN OUT type_str_agg,
VALUE IN VARCHAR2
) RETURN NUMBER IS
BEGIN
SELF.total := SELF.total || VALUE;
RETURN odciconst.success;
END;

MEMBER FUNCTION odciaggregateterminate
(
SELF IN type_str_agg,
returnvalue OUT VARCHAR2,
flags IN NUMBER
) RETURN NUMBER IS
BEGIN
returnvalue := SELF.total;
RETURN odciconst.success;
END;

MEMBER FUNCTION odciaggregatemerge
(
SELF IN OUT type_str_agg,
ctx2 IN type_str_agg
) RETURN NUMBER IS
BEGIN
SELF.total := SELF.total || ctx2.total;
RETURN odciconst.success;
END;

END;
/
CREATE OR REPLACE FUNCTION f_stragg(p_input VARCHAR2) RETURN VARCHAR2
PARALLEL_ENABLE
AGGREGATE USING type_str_agg;
/
CREATE OR REPLACE PACKAGE pkg_number_trans IS

FUNCTION f_bin_to_oct(p_str IN VARCHAR2) RETURN VARCHAR2;

FUNCTION f_bin_to_dec(p_str IN VARCHAR2) RETURN VARCHAR2;

FUNCTION f_bin_to_hex(p_str IN VARCHAR2) RETURN VARCHAR2;

FUNCTION f_oct_to_bin(p_str IN VARCHAR2) RETURN VARCHAR2;

FUNCTION f_oct_to_dec(p_str IN VARCHAR2) RETURN VARCHAR2;

FUNCTION f_oct_to_hex(p_str IN VARCHAR2) RETURN VARCHAR2;

FUNCTION f_hex_to_bin(p_str IN VARCHAR2) RETURN VARCHAR2;

FUNCTION f_hex_to_oct(p_str IN VARCHAR2) RETURN VARCHAR2;

FUNCTION f_hex_to_dec(p_str IN VARCHAR2) RETURN VARCHAR2;

FUNCTION f_dec_to_bin(p_int IN VARCHAR2) RETURN VARCHAR2;

FUNCTION f_dec_to_oct(p_int IN VARCHAR2) RETURN VARCHAR2;

FUNCTION f_dec_to_hex(p_int IN VARCHAR2) RETURN VARCHAR2;

END pkg_number_trans;
/
CREATE OR REPLACE PACKAGE BODY pkg_number_trans IS

FUNCTION f_bin_to_oct(p_str IN VARCHAR2) RETURN VARCHAR2 IS
----------------------------------------------------------------------------------------------------------------------
-- 对象名称: f_bin_to_oct
-- 对象描述: 二进制转换八进制
-- 输入参数: p_str 二进制字符串
-- 返回结果: 八进制字符串
-- 测试用例: SELECT pkg_number_trans.f_bin_to_oct('11110001010') FROM dual;
-- 备注: 需要定义f_stragg函数和type_str_agg类型
----------------------------------------------------------------------------------------------------------------------
v_return VARCHAR2(4000);
v_bin VARCHAR2(4000);
BEGIN
v_bin := substr('00' || p_str, -3 * ceil(length(p_str) / 3));
SELECT f_stragg(data1) INTO v_return
FROM (SELECT (CASE upper(substr(v_bin, (rownum - 1) * 3 + 1, 3))
WHEN '000' THEN '0'
WHEN '001' THEN '1'
WHEN '010' THEN '2'
WHEN '011' THEN '3'
WHEN '100' THEN '4'
WHEN '101' THEN '5'
WHEN '110' THEN '6'
WHEN '111' THEN '7'
END) data1
FROM dual
CONNECT BY rownum <= length(v_bin) / 3);
RETURN v_return;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END f_bin_to_oct;

FUNCTION f_bin_to_dec(p_str IN VARCHAR2) RETURN VARCHAR2 IS
----------------------------------------------------------------------------------------------------------------------
-- 对象名称: f_bin_to_dec
-- 对象描述: 二进制转换十进制
-- 输入参数: p_str 二进制字符串
-- 返回结果: 十进制字符串
-- 测试用例: SELECT pkg_number_trans.f_bin_to_dec('11110001010') FROM dual;
----------------------------------------------------------------------------------------------------------------------
v_return VARCHAR2(4000);
BEGIN
SELECT SUM(data1) INTO v_return
FROM (SELECT substr(p_str, rownum, 1) * power(2, length(p_str) - rownum) data1
FROM dual
CONNECT BY rownum <= length(p_str));
RETURN v_return;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END f_bin_to_dec;

FUNCTION f_bin_to_hex(p_str IN VARCHAR2) RETURN VARCHAR2 IS
----------------------------------------------------------------------------------------------------------------------
-- 对象名称: f_bin_to_hex
-- 对象描述: 二进制转换十六进制
-- 输入参数: p_str 二进制字符串
-- 返回结果: 十六进制字符串
-- 测试用例: SELECT pkg_number_trans.f_bin_to_oct('11110001010') FROM dual;
-- 备注: 需要定义f_stragg函数和type_str_agg类型
----------------------------------------------------------------------------------------------------------------------
v_return VARCHAR2(4000);
v_bin VARCHAR2(4000);
BEGIN
v_bin := substr('000' || p_str, -4 * ceil(length(p_str) / 4));
SELECT f_stragg(data1) INTO v_return
FROM (SELECT (CASE upper(substr(v_bin, (rownum - 1) * 4 + 1, 4))
WHEN '0000' THEN '0'
WHEN '0001' THEN '1'
WHEN '0010' THEN '2'
WHEN '0011' THEN '3'
WHEN '0100' THEN '4'
WHEN '0101' THEN '5'
WHEN '0110' THEN '6'
WHEN '0111' THEN '7'
WHEN '1000' THEN '8'
WHEN '1001' THEN '9'
WHEN '1010' THEN 'A'
WHEN '1011' THEN 'B'
WHEN '1100' THEN 'C'
WHEN '1101' THEN 'D'
WHEN '1110' THEN 'E'
WHEN '1111' THEN 'F'
END) data1
FROM dual
CONNECT BY rownum <= length(v_bin) / 4);
RETURN v_return;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END f_bin_to_hex;

FUNCTION f_oct_to_bin(p_str IN VARCHAR2) RETURN VARCHAR2 IS
----------------------------------------------------------------------------------------------------------------------
-- 对象名称: f_oct_to_bin
-- 对象描述: 八进制转换二进制
-- 输入参数: p_str 八进制字符串
-- 返回结果: 二进制字符串
-- 测试用例: SELECT pkg_number_trans.f_oct_to_bin('3612') FROM dual;
-- 备注: 需要定义f_stragg函数和type_str_agg类型
----------------------------------------------------------------------------------------------------------------------
v_return VARCHAR2(4000);
BEGIN
SELECT to_char(to_number(f_stragg(data1))) INTO v_return
FROM (SELECT (CASE upper(substr(p_str, rownum, 1))
WHEN '0' THEN '000'
WHEN '1' THEN '001'
WHEN '2' THEN '010'
WHEN '3' THEN '011'
WHEN '4' THEN '100'
WHEN '5' THEN '101'
WHEN '6' THEN '110'
WHEN '7' THEN '111'
END) data1
FROM dual
CONNECT BY rownum <= length(p_str));
RETURN v_return;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END f_oct_to_bin;

FUNCTION f_oct_to_dec(p_str IN VARCHAR2) RETURN VARCHAR2 IS
----------------------------------------------------------------------------------------------------------------------
-- 对象名称: f_oct_to_dec
-- 对象描述: 八进制转换十进制
-- 输入参数: p_str 八进制字符串
-- 返回结果: 十进制字符串
-- 测试用例: SELECT pkg_number_trans.f_oct_to_dec('3612') FROM dual;
----------------------------------------------------------------------------------------------------------------------
v_return VARCHAR2(4000);
BEGIN
SELECT SUM(data1) INTO v_return
FROM (SELECT substr(p_str, rownum, 1) * power(8, length(p_str) - rownum) data1
FROM dual
CONNECT BY rownum <= length(p_str));
RETURN v_return;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END f_oct_to_dec;

FUNCTION f_oct_to_hex(p_str IN VARCHAR2) RETURN VARCHAR2 IS
----------------------------------------------------------------------------------------------------------------------
-- 对象名称: f_oct_to_bin
-- 对象描述: 八进制转换十六进制
-- 输入参数: p_str 八进制字符串
-- 返回结果: 十六进制字符串
-- 测试用例: SELECT pkg_number_trans.f_oct_to_hex('3612') FROM dual;
----------------------------------------------------------------------------------------------------------------------
v_return VARCHAR2(4000);
v_bin VARCHAR2(4000);
BEGIN
SELECT pkg_number_trans.f_oct_to_bin(p_str) INTO v_bin FROM dual;
SELECT pkg_number_trans.f_bin_to_hex(v_bin) INTO v_return FROM dual;
RETURN v_return;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END f_oct_to_hex;

FUNCTION f_dec_to_bin(p_int IN VARCHAR2) RETURN VARCHAR2 IS
----------------------------------------------------------------------------------------------------------------------
-- 对象名称: f_dec_to_bin
-- 对象描述: 十进制转换二进制
-- 输入参数: p_str 十进制字符串
-- 返回结果: 二进制字符串
-- 测试用例: SELECT pkg_number_trans.f_dec_to_bin('1930') FROM dual;
----------------------------------------------------------------------------------------------------------------------
v_return VARCHAR2(4000);
v_hex VARCHAR2(4000);
BEGIN
SELECT pkg_number_trans.f_dec_to_hex(p_int) INTO v_hex FROM dual;
SELECT pkg_number_trans.f_hex_to_bin(v_hex) INTO v_return FROM dual;
RETURN v_return;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END f_dec_to_bin;

FUNCTION f_dec_to_oct(p_int IN VARCHAR2) RETURN VARCHAR2 IS
----------------------------------------------------------------------------------------------------------------------
-- 对象名称: f_dec_to_oct
-- 对象描述: 十进制转换八进制
-- 输入参数: p_str 十进制字符串
-- 返回结果: 八进制字符串
-- 测试用例: SELECT pkg_number_trans.f_dec_to_oct('1930') FROM dual;
----------------------------------------------------------------------------------------------------------------------
v_return VARCHAR2(4000);
v_bin VARCHAR2(4000);
BEGIN
SELECT pkg_number_trans.f_dec_to_bin(p_int) INTO v_bin FROM dual;
v_bin := substr('00' || v_bin, -3 * ceil(length(v_bin) / 3));
SELECT f_stragg(data1) INTO v_return
FROM (SELECT (CASE upper(substr(v_bin, (rownum - 1) * 3 + 1, 3))
WHEN '000' THEN '0'
WHEN '001' THEN '1'
WHEN '010' THEN '2'
WHEN '011' THEN '3'
WHEN '100' THEN '4'
WHEN '101' THEN '5'
WHEN '110' THEN '6'
WHEN '111' THEN '7'
END) data1
FROM dual
CONNECT BY rownum <= length(v_bin) / 3);
RETURN v_return;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END f_dec_to_oct;

FUNCTION f_dec_to_hex(p_int IN VARCHAR2) RETURN VARCHAR2 IS
----------------------------------------------------------------------------------------------------------------------
-- 对象名称: f_dec_to_oct
-- 对象描述: 十进制转换十六进制
-- 输入参数: p_str 十进制字符串
-- 返回结果: 十六进制字符串
-- 测试用例: SELECT pkg_number_trans.f_dec_to_hex('1930') FROM dual;
----------------------------------------------------------------------------------------------------------------------
v_return VARCHAR2(4000);
BEGIN
SELECT upper(TRIM(to_char(p_int, 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'))) INTO v_return FROM dual;
RETURN v_return;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END f_dec_to_hex;

FUNCTION f_hex_to_bin(p_str IN VARCHAR2) RETURN VARCHAR2 IS
----------------------------------------------------------------------------------------------------------------------
-- 对象名称: f_dec_to_oct
-- 对象描述: 十六进制转换二进制
-- 输入参数: p_str 十六进制字符串
-- 返回结果: 二进制字符串
-- 测试用例: SELECT pkg_number_trans.f_hex_to_oct('78A') FROM dual;
----------------------------------------------------------------------------------------------------------------------
v_return VARCHAR2(4000);
BEGIN
SELECT to_char(to_number(f_stragg(data1))) INTO v_return
FROM (SELECT (CASE upper(substr(p_str, rownum, 1))
WHEN '0' THEN '0000'
WHEN '1' THEN '0001'
WHEN '2' THEN '0010'
WHEN '3' THEN '0011'
WHEN '4' THEN '0100'
WHEN '5' THEN '0101'
WHEN '6' THEN '0110'
WHEN '7' THEN '0111'
WHEN '8' THEN '1000'
WHEN '9' THEN '1001'
WHEN 'A' THEN '1010'
WHEN 'B' THEN '1011'
WHEN 'C' THEN '1100'
WHEN 'D' THEN '1101'
WHEN 'E' THEN '1110'
WHEN 'F' THEN '1111'
END) data1
FROM dual
CONNECT BY rownum <= length(p_str));
RETURN v_return;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END f_hex_to_bin;

FUNCTION f_hex_to_oct(p_str IN VARCHAR2) RETURN VARCHAR2 IS
----------------------------------------------------------------------------------------------------------------------
-- 对象名称: f_dec_to_oct
-- 对象描述: 十六进制转换八进制
-- 输入参数: p_str 十六进制字符串
-- 返回结果: 八进制字符串
-- 测试用例: SELECT pkg_number_trans.f_hex_to_oct('78A') FROM dual;
----------------------------------------------------------------------------------------------------------------------
v_return VARCHAR2(4000);
v_bin VARCHAR2(4000);
BEGIN
SELECT pkg_number_trans.f_hex_to_bin(p_str) INTO v_bin FROM dual;
SELECT pkg_number_trans.f_bin_to_oct(v_bin) INTO v_return FROM dual;
RETURN v_return;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END f_hex_to_oct;

FUNCTION f_hex_to_dec(p_str IN VARCHAR2) RETURN VARCHAR2 IS
----------------------------------------------------------------------------------------------------------------------
-- 对象名称: f_hex_to_dec
-- 对象描述: 十六进制转换十进制
-- 输入参数: p_str 十六进制字符串
-- 返回结果: 十进制字符串
-- 测试用例: SELECT pkg_number_trans.f_hex_to_dec('78A') FROM dual;
----------------------------------------------------------------------------------------------------------------------
v_return VARCHAR2(4000);
BEGIN
SELECT SUM(data1) INTO v_return
FROM (SELECT (CASE upper(substr(p_str, rownum, 1))
WHEN 'A' THEN '10'
WHEN 'B' THEN '11'
WHEN 'C' THEN '12'
WHEN 'D' THEN '13'
WHEN 'E' THEN '14'
WHEN 'F' THEN '15'
ELSE substr(p_str, rownum, 1)
END) * power(16, length(p_str) - rownum) data1
FROM dual
CONNECT BY rownum <= length(p_str));
RETURN v_return;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END f_hex_to_dec;

END pkg_number_trans;

6.获取系统信息

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

7.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;

/*相差豪秒数

----------

86401000

1 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;

/*相差秒数

----------

86401

1 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;

/*相差分钟数

----------

1441

1 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;

/*相差小时数

----------

1 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;

/*相差天数

----------

1 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

----------

1 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

----------

1 row selected

8.不同的行insert到不同的表中

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

insert all（first）
when col1>2 then into tmp1 values(col1)
when col1>1 then into tmp2 values(col1)
select col1 from tmp;

区别在于 all表示所有的select col1 from tmp;中的记录挨个挨个的对一个个条件中执行，first表示select col1 from tmp;中的记录对第一个条件执行后插入的记录将不会对以后的条件再执行，相当于一锅肉，A吃了的B就吃不到了

9.动态sql创建ORACLE存储过程

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;
/

10.模糊查询

这有两个表A,B
A:MSGSTR MSTIME
1,1,ertetsgd,wiu 2008-01-01 10:00:05
1,1,iuoijhghuy,564 2009-01-08 00:00:08
B:SSTR STIME
et 2008-01-01 09:59:59
hg 2009-01-08 00:00:00

现在我要根据B表中的SSTR的内容查询A表中MSGSTR LIKE '% SSTR %'的记录，我该怎么写这个查询语句？？

答案：
1，select a.* from a,b where a.MSGSTR like '%'||b.SSTR||'%';

2，select a.* from a,b where instr(a.MSGSTR,b.SSTR)>0;

11.跟踪当前对话下用户的SQL脚本

select sql_text from v$sqltext_with_newlines where (hash_value,address)
in (select sql_hash_value,sql_address from v$session where sid=&sid)
order by address,piece;

SID
由这得到
select sid,machine from v$session;

12.刪除相同行

delete from dumpy_part a
where a.rowid <> (select min(b.rowid) from dumpy_part b
where a.id = b.id)

方法原理：
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

13.获得列乘积

Select power(10, Sum(Log(10, columnName))) From t

14. 基本命令

desc table;检查表结构
select * from tab where tabtype='TABLE';显示当前用户下的所有表。
select count(*) from table;显示此表的数据行数；
spool c:/tony.txt;日记路径
spool off;关闭记录后可以看到日记文件里的内容。
alter table stu add(classid number(2));添加字段
alter table stu modify(xm varchar2(12));修改字段的长度
alter table stu drop column sal;
drop table stu;
rename student to stu;
alter table student drop column sal; alter table stu add(salary number(7,2));
insert into stu values('A001','张三','男','01-5月-05',10);
insert into stu(xh,xm,sex) values ('A003','JOHN','女');
insert into student(xh,xm,sex,birthday) values ('A004','MARTIN','男',null);
修改
update
update stu set sex='女' where xh='A001';
update student set sex='男'，birthday='1980-04-01'where xh='A001';
update student set classid=20 where birthday is null;
delete from stu;drop table student;delete from stu where xh='A001';
truncate table stu;删除表中的所有记录，表结构还在不写日记无法找回记录
select * from stu;
select * from student where classid like '1%';
select * from student where xh like '%A%';
select * from student where xh like 'A%';
select * from student where xh like '%A';
select * from student where xh = 'A%';
select * from student order by birthday;
select * from student order by birthday desc,xh asc; --按birthday 降序按xh升序(asc/默认)
select * from student where sex='女' or birthday='1999-02-01';
select * from student where sex='女' and birthday='1999-02-01';
select * from student where salary > 20 and xh <> 'B002'; (!=)
oracle
函数的学习
单行函数返回值只有一个
分组函数返回值是多条记录
group by
sum
avg
select sysdate from dual;dual哑元素没有表需要查询的时候
select xm||'-----'||classid from stu;
select 1+1 from dual;
select job,next_date,next_sec,failures,broken from user_jobs;
show parameter job_queue_processes;
alter system set job_queue_processes=5;
select owner,object_name from dba_objects where object_type='DATABASE LINK';
select * from dual@gis3; 判断是否创建
select object_name,object_type
from user_objects
order by object_type; --用户所拥有的对象

select object_name,object_type
from user_objects
WHERE object_type='TABLE'; --用户所拥有的表
另外一种方法
select table_name from user_tables;

select object_name,object_type
from user_objects
WHERE object_type='INDEX'; --用户所拥有的索引
INSERT INTO Store_Information (store_name, Sales, Date)
SELECT store_name, Sales, Date
FROM Sales_Information
WHERE to_char(date,'yyyy')=1998;
exp/imp help=y 显示exp/imp命令下的参数
imp tony/gisinfo file=tony.dmp log=loga show=y full=y
if--then--end if的使用if v_test=1 then begin ..... end; end if;
while--loop--end loop的使用if v_test=1 then begin ........end;end if;
CASE的使用
update jzqld1 set bcrq=case
when id like '3%' then '09010102'
when id like '4%' then '09010204'
else '09010805'
end;
删除用户drop user tony/create user tony
showdown abort; startup;
希望在scott用户下能看到mk用户中test表的数据???
connect mk/m123
grant select on test to scott; //授权scott能select
connect scott/tiger
select * from mk.test;

create synonym mtest for mk.test; //为表建立的
//可以为任何对象建立同义词
select * from mtest; //相当于select * from mk.test;
位图索引
create bitmap index ind_aa on emp(job);
create index ind_dept on dept(dname,loc);
//联合索引分次序的
create index ind_dept1 on dept(loc,dname);
show parameter db_name数据库名

DBA要及时查看数据库中数据文件的状态(如被误删除)，根据实际情况决定如何进行处理，检查数据文件的状态的SQL如下:

select file_name,status from dba_data_files;

如果数据文件的STATUS列不是AVAILABLE，那么就要采取相应的措施，如对该数据文件进行恢复操作，
或重建该数据文件所在的表空间。
//给用户授予权限

grant connect,dba,resource to testserver_user;

//创建用户并指定表空间
create user ORACLE8I identified by ORACLE8I
default tablespace ORACLE8I
temporary tablespace test_temp ;

//创建数据表空间
create tablespace oracle8i
logging
datafile 'E:/oracle/product/10.2.0/oradata/testserver/test_data01.dbf'
size 32m
autoextend on
next 32m maxsize 2048m
extent management local;

//创建临时表空间

create temporary tablespace test_temp
tempfile 'E:/oracle/product/10.2.0/oradata/testserver/test_temp01.dbf'
size 32m
autoextend on
next 32m maxsize 2048m
extent management local;
表锁 : lock table emp in exclusive mode;
对emp表加的表锁
一般不使用表锁;
alter table aa move tablespace bb

15.判断字段是否可转换为整数

select * from table1 where trim(translate(field1 ,'0123456789',' ')) is null

16.类似于SEQUENCE的函数，从目标表中返回一个最大的序号值（18位，36进制)

--PUB_USER_ROLE为目标表

--PK_USER_ROLE为目标字段

create or replace function TEST return varchar2

str1 varchar2(100);

str2 varchar2(100);

str3 varchar2(100);

i number;

j number;

begin

select max(PK_USER_ROLE) into str1 from PUB_USER_ROLE;

j := 1;

for i in 1 .. 18 loop

begin

str2 := substr(str1, -i, 1);

if ascii(str2) + J <= ASCII('Z') THEN

IF ASCII(STR2) + J <= ASCII(9) THEN

STR2 := STR2 ;

ELSE

begin

STR2 := 'A';

J := 0;

end;

END IF;

ELSE

begin

STR2 := '0';

J := 1;

end;

END IF;

STR3 := STR2 || STR3;

END;

END LOOP;

STR3 := 'PK' || str3;

RETURN(STR3);

end;

17.分析sql语句的执行效率

先建一个表：
CREATE TABLE "PLAN_TABLE"
("STATEMENT_ID" VARCHAR2(30),
"TIMESTAMP" DATE,
"REMARKS" VARCHAR2(80),
"OPERATION" VARCHAR2(30),
"OPTIONS" VARCHAR2(30),
"OBJECT_NODE" VARCHAR2(128),
"OBJECT_OWNER" VARCHAR2(30),
"OBJECT_NAME" VARCHAR2(30),
"OBJECT_INSTANCE" FLOAT,
"OBJECT_TYPE" VARCHAR2(30),
"OPTIMIZER" VARCHAR2(255),
"SEARCH_COLUMNS" FLOAT,
"ID" FLOAT,
"PARENT_ID" FLOAT,
"POSITION" FLOAT,
"OTHER" LONG) ;

把以下内容存一文件中，需要时调出来填上要分析的语句执行它：
explain plan set statement_id = 'Allan' into plan_table for

/*此处放上要分析的sql语句*/

select lpad(' ',3*(level - 1))||operation||' '||options "query plan",object_name//,position
from plan_table
start with id = 0 and statement_id = 'Allan'
connect by prior id = parent_id
and statement_id = statement_id ;

18.表维护树形结构的应用

Start with...Connect By子句递归查询一般用于一个表维护树形结构的应用。
创建示例表：
CREATE TABLE TBL_TEST
(
ID NUMBER,
NAME VARCHAR2(100 BYTE),
PID NUMBER DEFAULT 0
);

插入测试数据：
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');

从Root往树末梢递归
select * from TBL_TEST
start with id=1
connect by prior id = pid

从末梢往树ROOT递归
select * from TBL_TEST
start with id=5
connect by prior pid = id

显示叶子结点和路径
SELECT A.*,CONNECT_BY_ISLEAF"ISLEAF",SYS_CONNECT_BY_PATH( ID, '/') "Path"
FROM TBL_TEST A
WHERE CONNECT_BY_ISLEAF = 1
start with A.ID='1' connect by prior A.ID = A.PID

19.找出比平均值大的数

SELECT distinct decode(sign(userid-AVG(userid) over ()),1,userid,max(userid)over()) FROM user;

20.程序中反SOFTICE跟踪法

function AntiSoftICE():Boolean;
var
SVStatus:TServiceStatus;
OpenSVM,ICESV:SC_HANDLE;
begin

OpenSVM:=OpenSCManager(nil,SERVICES_ACTIVE_DATABASE,SC_MANAGER_ALL_ACCESS);
if OpenSVM=0 then
Result:=False;

ICESV:=OpenService(OpenSVM,'NTice',SERVICE_ALL_ACCESS);
if QueryServiceStatus(ICESV,SVStatus) then
begin
CloseServiceHandle(ICESV);
Result:=False;
end;

if SVStatus.dwCurrentState=SERVICE_RUNNING then
begin
CloseServiceHandle(ICESV);
Result:=True;
end;
end;

21.实现行列转换

create table BASE
(
NAME VARCHAR2(40),
XM VARCHAR2(40),
CJ VARCHAR2(40),
XK VARCHAR2(40),
XF VARCHAR2(40),
CLA VARCHAR2(40),
STU VARCHAR2(40)
);
测试数据：
prompt PL/SQL Developer import file
prompt Created on 2009年3月16日 by Administrator
set feedback off
set define off
prompt Disabling triggers for BASE...
alter table BASE disable all triggers;
prompt Deleting BASE...
delete from BASE;
commit;
prompt Loading BASE...
insert into BASE (NAME, XM, CJ, XK, XF, CLA, STU)
values ('csdn', '5000', null, '20090310', '1', null, null);
insert into BASE (NAME, XM, CJ, XK, XF, CLA, STU)
values ('csdn', null, '500', '20090311', '2', null, null);
insert into BASE (NAME, XM, CJ, XK, XF, CLA, STU)
values ('csdn', null, '1000', '20090311', '3', null, null);
insert into BASE (NAME, XM, CJ, XK, XF, CLA, STU)
values ('csdn', '1000', null, '20090312', '4', null, null);
insert into BASE (NAME, XM, CJ, XK, XF, CLA, STU)
values ('csdn', null, '1000', '20090313 ', '5', null, null);
insert into BASE (NAME, XM, CJ, XK, XF, CLA, STU)
values ('cs', null, null, null, '11', null, null);
insert into BASE (NAME, XM, CJ, XK, XF, CLA, STU)
values ('cs', null, null, null, '12', null, null);
insert into BASE (NAME, XM, CJ, XK, XF, CLA, STU)
values ('cs', null, null, null, '13', null, null);
insert into BASE (NAME, XM, CJ, XK, XF, CLA, STU)
values ('cs', null, null, null, '14', null, null);
insert into BASE (NAME, XM, CJ, XK, XF, CLA, STU)
values ('cs', null, null, null, '15', null, null);
insert into BASE (NAME, XM, CJ, XK, XF, CLA, STU)
values ('cs', null, null, null, '16', null, null);
commit;
prompt 11 records loaded
prompt Enabling triggers for BASE...
alter table BASE enable all triggers;
set feedback on
set define on
prompt Done.

create or replace function f_group(v_tablename VARCHAR2,v_field1 varchar2,v_fileld2 varchar2)——写成通用的话自己改
return varchar2
--return sys_refcursor
as
v_name number;
v_char varchar2(100);
v_ch varchar2(400);
v_s varchar2(600) :='';
v_c varchar2(80);
--outCursor SYS_REFCURSOR; --输出的游标
cursor c1 is select max(seq) seq from (
select name,xf,
row_number() over
(partition by name order by xf ) seq
from base)
group by name;
begin
v_char:='select name';
v_ch:=' from (
select name,xf,
row_number() over
(partition by name order by xf ) seq
from base)
group by name';
open c1;
loop
fetch c1 into v_name;
exit when c1%notfound;
for i in 1..v_name loop
v_c:=',sum(decode(seq,'||i||',xf)) name'||i;
v_s:=v_s||v_c;
end loop;
/*v_s:= v_char||v_s||v_ch;
open outCursor for v_s;
return outCursor;*/
return v_char||v_s||v_ch;
end loop;
close c1;
end;

22.万年日历

SELECT CASE

WHEN (NEW_YWEEK = MIN(NEW_YWEEK)

OVER(PARTITION BY MON ORDER BY NEW_YWEEK)

)

THEN

MON_NAME

ELSE NULL

END AS MONTH,

NEW_YWEEK AS YWEEK,

ROW_NUMBER() OVER(PARTITION BY MON ORDER BY NEW_YWEEK) AS MWEEK,

SUM(DECODE(WDAY, '1', MDAY, NULL)) AS SUN,

SUM(DECODE(WDAY, '2', MDAY, NULL)) AS MON,

SUM(DECODE(WDAY, '3', MDAY, NULL)) AS TUE,

SUM(DECODE(WDAY, '4', MDAY, NULL)) AS WED,

SUM(DECODE(WDAY, '5', MDAY, NULL)) AS THU,

SUM(DECODE(WDAY, '6', MDAY, NULL)) AS FRI,

SUM(DECODE(WDAY, '7', MDAY, NULL)) AS SAT

FROM (

SELECT DAYOFYEAR AS EVERYDAY,

TO_CHAR(DAYOFYEAR, 'mm') AS MON,

TO_CHAR(DAYOFYEAR, 'Month') AS MON_NAME,

TO_CHAR(DAYOFYEAR, 'w') AS MWEEK,

TO_CHAR(DAYOFYEAR, 'ww') AS YWEEK,

CASE

WHEN (TO_CHAR(TO_DATE(&YEAR || '0101', 'yyyymmdd'), 'd') > '1')

AND (TO_CHAR(DAYOFYEAR, 'd') < TO_CHAR(TO_DATE(&YEAR || '0101', 'yyyymmdd'), 'd')

)

THEN TO_CHAR(TO_CHAR(DAYOFYEAR, 'ww') + 1, 'fm00')

ELSE TO_CHAR(DAYOFYEAR, 'ww') END AS NEW_YWEEK,

TO_CHAR(DAYOFYEAR, 'd') AS WDAY,

TO_CHAR(DAYOFYEAR, 'dd') AS MDAY

FROM (

SELECT TO_DATE(&YEAR || '0101', 'yyyymmdd') + LEVEL - 1 AS DAYOFYEAR

FROM DUAL CONNECT BY LEVEL <= TO_CHAR( TO_DATE(&YEAR || '1231', 'yyyymmdd'),'ddd' )

)

) GROUP BY MON, MON_NAME, NEW_YWEEK;