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 顶的大哥大姐 放心理就行 小弟心领了
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
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;
----------------------------------------------------------------------------------------------------------------------
-- 对象名称: 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;
----------------------------------------------------------------------------------------------------------------------
-- 对象名称: 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;
/
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
where id in (select top 3 from table1 where 物品=a.物品 order by price desc)每一种物品有很多价格，每一种物品选择排在前三的纪录
SQL: select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....
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;
/*
相差小时数
----------
25
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;
/*
相差天数
----------
2
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')) months
from dual;
/*
MONTHS
----------
13
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
1 row selected
*/
--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;
SELECT lpad(ROWNUM,2,'0') FROM dual CONNECT BY ROWNUM <=99;
这些狼心狗肺的就知道吃
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;
/
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;
copy from ems/ems@sdmis
to test01/test01@testdb
append c_form
using select * from c_form;COPY {FROM database | TO database
| FROM database TO database}
{APPEND|CREATE|INSERT|REPLACE}
destination_table [(column, column, column, ...)]
USING query
in (select sql_hash_value,sql_address from v$session where sid=&sid)
order by address,piece;SID
由这得到
select sid,machine from v$session;
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;
in (select sql_hash_value,sql_address from v$session where sid=&sid)
order by address,piece; SID
由这得到
select sid,machine from v$session;
http://www.cnblogs.com/liuweitoo/archive/2007/04/02/697293.html
在WINDOWS NT/2000下手动创建数据库
我机子里已经有一个数据库为sjpdb,现在要手动再建第二个数据库sjpdb02 步骤如下：
步骤一：确定数据库名 实例名 服务名
步骤二：编辑修改参数文件
步骤三：创建数据库实例
步骤四：启动新创建的实例，创建数据库
步骤五：创建数据字典
步骤六：网络配置 即配置监听器(lisener.ora)和网络连接解析配置（tnsnames.ora）
具体步骤如下： 步骤一：确定数据库名 实例名 服务名 因为我是在自己机子上创建DB 所以域名取为空，所以服务名和数据库名相同 参数格式如下：
db_name=sjpdb02
instance_name=sjp02
db_domain=""
service_names=sjpdb02 步骤二：编辑修改参数文件 从其他WINDOWS NT/2000的数据库ORACLE 9I中拷贝一个参数文件作为基础修改。一般先要修改以下一些参数其他参数在数据库创建后再修改。 我的ORACLE 安装目录为F盘根目录下
db_name=sjpdb02
insance_name=sjpdb02
db_domain=
service_names=sjpdb02
control_files=("F:\oracle\oradata\sjpdb02\CONTROL01.CTL", "F:\oracle\oradata\sjpdb02\CONTROL02.CTL", "F:\oracle\oradata\sjpdb02\CONTROL03.CTL")
db_block_size=4096
background_dump_dest=F:\oracle\admin\sjpdb02\bdump
core_dump_dest=F:\oracle\admin\sjpdb02\cdump
user_dump_dest=F:\oracle\admin\sjpdb02\udump
修改完后保存,文件名为init.ora保存路径
f:\oracle\admin\sjpdb02\pfile\init.ora 步骤三：创建数据库实例 创建数据库实例的原因是数据库启动时首先要启动实例，若对应此数据库的实例启动失败或每没启动，则数据库不能正常启动发生协议适 配器错误，数据库实例在WINDOW下就是服务，本步骤要利用创建数据库实例的程序Oradim.exe创建实例OracleServiceSJPDB02。首先要在
F:\oracle\ora90\database下创建initsjpdb02.ora文件，因为用oradim命令创建服务时要用到参数文件，initsjpdb02.ora文件内容为
IFILE='F:\ORACLE\ADMIN\SJPDB02\PFILE\INIT.ORA' 这句话实际上是对上面我们建的参数文件的一个引用。
使用ORAPWD命令创建数据库中具有SYSDBA权限的口令字文件，该用户可以启动和关闭数据库，此文件保存目录：
f:\oracle\ora90\database\PWDsjpdb02.ora
创建口令字方法为：
C:\>rapwd file=f:\oracle\ora90\database\PWDsjpdb02.ora password=sjp entries=30
使用ORADIM命令创建数据库实例
c:\>set oracle_sid=sjpdb02 在这之前ORACLE_SID=SJPDB
C:\>ORADIM -NEW -SID SJPDB02 -STARTMODE M -PFILE F:\oracle\ora90\database\initsjpdb02.ora 启动模式为手动
创建成功后 系统自动在WINDOWS的服务表中增加一个新的服务“OracleServiceSJPDB02” 并且自动启动了，虽然启动模式为手动。
其实创建口令字过程可以添在创建实例语句中 两个参数 －intpwd sjp -maxusers 30 步骤四：启动新创建的实例，创建数据库 步骤五：创建数据字典 这两个步骤用一个脚本实现 在创建数据库之前要以新的参数文件启动数据库实例，即nomount数据库，因没有控制文件还不能MOUNT或OPEN
c:\>sqlplus "/as sysdba"
SQL>startup nomount
ORACLE 例程已经启动。 Total System Global Area 118255568 bytes
Fixed Size 282576 bytes
Variable Size 83886080 bytes
Database Buffers 33554432 bytes
Redo Buffers 532480 bytes
若数据库参数文件不在F:\oracle\ora90\database下，或参数文件名称没有按initSID.ora取名，则启动实例时要加参数PFILE指出参数文件路 径，例SQL>startup pfile=路径\参数文件名 nomount
运行脚本文件 名为createdb.sql 其中文件最后是加载一些程序包以创建数据字典
SQL>存放路径\createdb.sql
createdb.sql文件内容为： create database sjpdb02
logfile
group 1 ('f:\oracle\oradata\sjpdb02\redo01a.log',
'f:\oracle\oradata\sjpdb02\redo01b.log') size 5M,
group 2 ('f:\oracle\oradata\sjpdb02\redo02a.log',
'f:\oracle\oradata\sjpdb02\redo02b.log') size 5M,
group 3 ('f:\oracle\oradata\sjpdb02\redo03a.log',
'f:\oracle\oradata\sjpdb02\redo03b.log') size 5M
maxlogfiles 32
maxlogmembers 3
maxloghistory 1
datafile 'f:\oracle\oradata\sjpdb02\system01.dbf' size 100M
autoextend on next 512k maxsize unlimited
maxdatafiles 254
maxinstances 1
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET UTF8; alter tablespace system default storage
(initial 64K minextents 1
maxextents unlimited pctincrease 50);
alter tablespace system minimum extent 64k; create undo tablespace undotbs
datafile 'f:\oracle\oradata\sjpdb02\undotbs01.dbf' size 50M
autoextend on next 512k
extent management local;
create temporary tablespace temp
Tempfile 'f:\oracle\oradata\sjpdb02\temp01.dbf' size 32M
autoextend on next 512k
extent management local; create tablespace tools
datafile 'f:\oracle\oradata\sjpdb02\tools01.dbf' size 32M
autoextend on next 512k
extent management dictionary
minimum extent 32k
default storage(initial 32k next 32k minextents 1
maxextents 4096 pctincrease 0); create tablespace indx
datafile 'f:\oracle\oradata\sjpdb02\indx01.dbf' size 32M
autoextend on next 512k
minimum extent 128k
default storage(initial 128k next 128k minextents 1
maxextents 4096 pctincrease 0); create tablespace users
datafile 'f:\oracle\oradata\sjpdb02\users01.dbf' size 20M
autoextend on next 512k
extent management dictionary
minimum extent 128k
default storage(initial 128k next 128k minextents 1
maxextents 4096 pctincrease 0); @f:\oracle\ora90\rdbms\admin\catalog.sql
@f:\oracle\ora90\rdbms\admin\catproc.sql
@f:\oracle\ora90\rdbms\admin\catexp7.sql
@f:\oracle\ora90\rdbms\admin\catrep.sql
@f:\oracle\ora90\rdbms\admin\caths.sql
connect system/manager
@f:\oracle\ora90\sqlplus\admin\pupbld.sql 步骤六：网络配置 即配置监听器(lisener.ora)和网络连接解析配置（tnsnames.ora） 可以使用NET MANAGER来配置
copy from ems/ems@sdmis
to test01/test01@testdb
append c_form
using select * from c_form;COPY {FROM database | TO database
| FROM database TO database}
{APPEND|CREATE|INSERT|REPLACE}
destination_table [(column, column, column, ...)]
USING query
where a.rowid <> (select min(b.rowid) from dumpy_part b
where a.id = b.id)
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 = 1
connect by prior job = job
and prior rn = rn - 1
order by jobJOB SCBP
--------- --------------------------------------------------------------------------------
ANALYST FORD,SCOTT
CLERK ADAMS,JAMES,MILLER,SMITH
MANAGER BLAKE,CLARK,JONES
PRESIDENT KING
SALESMAN ALLEN,MARTIN,TURNER,WARD
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
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的同仁必须掌握手动建库。
Select power(10, Sum(Log(10, columnName))) From t
2,'no',
'unknow') from dual
把常量變成你需要處理的字段內容即可了。
sp_helpconstraint emp
/*执行指定的SQL指令*/
sp_executesql N'select * from emp'
/*N表示后面的字符串采用unicode编码体系（无论中英文均一字符双字节）*/
/*模糊查询like*/
select * from emp where eid not like '[0-8][0-8][0-12]'
/*中括号内代表‘一’位字符的范围，[0-12]即[0-1]||[0-2]*/
select * from emp where ename like 'w_' 下划线
/*"_"通配符，代表所有字符，就是麻将里的“混”*/
select * from emp where ename like 'w%'
/*"%"代表任意位的任意字符*/
select * from emp where not ename='we'
/*not不等于*/
select * from emp where ename is null
/*is null不是＝null！*/
select * from emp where ename is not null
/*is not null不是not is null*/
select * from emp where eid not in (2002)
/*in 包含于*/
select * from emp where eid between 1001 and 1002
/*between 在两值之间的范围内取值*/
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最基础的