--当前日期所在月的第一个星期天和最后一个星期天 select trunc(trunc(add_months(sysdate,-1),'mm'),'day')+7, trunc(trunc(sysdate,'mm'),'day') from dual--本周的第一天和最后一天 select trunc(sysdate,'d')+1 from dual; select trunc(sysdate,'d')+7 from dual; --本月的第一天和最后一天 select trunc(sysdate,'mm') from dual; select last_day(trunc(sysdate)) from dual;--本季的第一天和最后一天 select trunc(sysdate,'Q') from dual; select add_months(trunc(sysdate,'Q'),3)-1 from dual;--本年的第一天和最后一天 select trunc(sysdate,'yyyy') from dual; select add_months(trunc(sysdate,'yyyy'),12)-1 from dual;--当前时间所在的季度数 Select to_char(sysdate,'Q') from dual;--当前时间为年的第几周 (注意当前年的第一天为周几) select to_char(sysdate,'ww') from dual;--当前时间为月的第几周 (注意当前月的第一天为周几) select to_char(sysdate,'w') from dual;--当前时间为年的第几天 Select to_char(sysdate,'DDD') from dual;--当前时间为当前季度的第几天 select ceil(sysdate- trunc(sysdate,'Q')) from dual;--修改系统时间格式 alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss.sssss'; --时间显示格式2011-2-3 加fxfm位数不足两位的时候不会补0 select to_char(to_date('20100102','yyyymmdd'),'yyyy-FMmm-FXDD') from dual;--时间显示为2010年2月3日 select to_char(sysdate,'YYYY"年"MM"月"DD"日"') from dual;
--sqlplus打开输出 set serveroutput on --随机生成字母和数字 begin for i in 1..10 loop DBMS_OUTPUT.PUT_LINE(sys_guid()); end loop; end; --expdp导出时排除某些表 EXCLUDE=TABLE:"IN ('TABLENAME1', 'TABLENAME2')" --查看scott用户下各个表的数据量 declare cnt number; sqlstr varchar2(4000); v_tablename varchar2(30); cursor c_tablename is select table_name from all_tables where owner='SCOTT'; begin open c_tablename; loop fetch c_tablename into v_tablename; exit when c_tablename%notfound; sqlstr:='select count(*) from '||v_tablename; execute immediate sqlstr into cnt; dbms_output.put_line('table_name: '||v_tablename||'; count: '||cnt); end loop; close c_tablename; end;
--创建表空间 create tablespace test logging datafile 'D:\oracle\oradata\ypcost\test01.dbf' size 10M autoextend on next 1M maxsize 20M extent management local--删除表空间 包括所有的数据对象和数据文件 drop tablespace tablesapcename including contents and datafiles;--增加数据文件 alter tablespace tablespacename add datafile 'D:\oracle\oradata\ypcost\test01.dbf' size 10M autoextend on next 1M maxsize 20M--修改表空间为自动增长 alter database datafile 'D:\oracle\oradata\aa\test01.dbf' autoextend on; --各个表空间的使用率、select Total.Tname "表空间名称", Total.Total_Size "表空间大小", Total.Total_Size - Used.free_size as "已使用大小", Used.Free_size as 表空间剩余大小, Round((Total.Total_Size - Used.free_size) / Total.Total_Size,4)* 100 || '%' as 表空间使用率 from ( -- 表空间数据文件的大小 select tablespace_name as TName, round(sum(user_bytes)/(1024*1024),1) as Total_size from dba_data_files group by tablespace_name ) Total, ( -- 表空间剩余的大小 select tablespace_name as TName, round(sum(bytes)/(1024*1024),1) as Free_size from dba_free_space group by tablespace_name ) Used where Total.TName = Used.TName(+)
--oracle分页的存储过程 create or replace procedure fenye_pro( v_tablename varchar2, --表名 v_pagesize int, --一页显示的记录数 v_pagenow int,--要显示第几页 v_pagerows out number,--总页数 v_counts out number,--总记录数 recode_cursor out sys_refcursor) as v_begin number:=1+(v_pagenow-1)*v_pagesize; v_end number:=v_pagenow*v_pagesize; v_sqlstr varchar2(4000); v_flag number:=0; begin select count(*) into v_flag from user_tables where table_name=v_tablename; if v_flag=0 then dbms_output.put_line('输入的表'||v_tablename||'不存在'); else v_sqlstr:='select count(*) from '||v_tablename; execute immediate v_sqlstr into v_counts; v_pagerows:=ceil(v_counts/v_pagesize); v_sqlstr:='select * from (select rownum rn,t.* from (select * from '||v_tablename|| ') t where rownum<='||v_end||') where rn>='||v_begin; open recode_cursor for v_sqlstr; --dbms_output.put_line(v_sqlstr); end if; exception when others then dbms_output.put_line('参数输入格式或类型不符'); end;--修改序列 Alter sequence seq [increment by n] [{maxcalue n/nomaxalue}] [{minvalue n/nominvalue}] [{cycle/nocycle}] [{cache n/nocache}]; --merge into的使用 merge into fzq1 aa --fzq1表是需要更新的表 using fzq bb -- 关联表 on (aa.id=bb.id) --关联条件 when matched then --匹配关联条件,作更新处理 update set aa.chengji=bb.chengji+1, --不能更新on中包含的字段 aa.name=bb.name --此处只是说明可以同时更新多个字段。 when not matched then --不匹配关联条件,作插入处理。如果只是作更新,下面的语句可以省略。 insert values( bb.id, bb.name, bb.sex,bb.kecheng,bb.chengji); --修改连接数 show parameter processes; show parameter sessions; alter system set processes=300 scope=spfile; alter system set sessions=300 scope=spfile; shutdown immediate;--修改完后要重启数据库 startup --数字金额转化为中文大写 CREATE OR REPLACE FUNCTION money_to_chinese (money IN number) RETURN VARCHAR2 IS c_money VARCHAR2 (12); m_string VARCHAR2 (60) := '分角圆拾佰仟万拾佰仟亿'; n_string VARCHAR2 (40) := '壹贰叁肆伍陆柒捌玖'; b_string VARCHAR2 (80); n CHAR; len NUMBER (3); i NUMBER (3); tmp NUMBER (12); is_zero BOOLEAN; --标记当前的前一个数值是否为0 z_count NUMBER (3); --万位、各位和最后尾数前连续0的个数 l_money NUMBER; l_sign VARCHAR2 (10); BEGIN l_money := ABS (money); --得到传入阿拉伯数值的绝对值
--判断传入的数值是正还是负,如果是负则加上'负' IF money < 0 THEN l_sign := '负'; ELSE l_sign := ''; END IF;
tmp := ROUND (l_money, 2) * 100; c_money := TRIM (TO_CHAR (tmp, '999999999999')); len := LENGTH (c_money); is_zero := TRUE; z_count := 0; i := 0;
WHILE i < len LOOP i := i + 1; n := SUBSTR (c_money, i, 1 );
IF n = '0' THEN IF len - i = 6 OR len - i = 2 OR len = i --判断是否到万位、各位和最后位 THEN IF is_zero --如果前一位为0把拼接成的 '零' 删除掉 THEN b_string := SUBSTR (b_string, 1, LENGTH (b_string) - 1 ); is_zero := FALSE; END IF;
IF len - i = 6 THEN b_string := b_string || '万'; END IF;
IF len - i = 2 THEN b_string := b_string || '圆'; END IF;
IF len = i THEN b_string := b_string || '整'; END IF;
z_count := 0; ELSE IF z_count = 0 THEN b_string := b_string || '零'; is_zero := TRUE; END IF;
z_count := z_count + 1; END IF; ELSE b_string := b_string || SUBSTR (n_string, TO_NUMBER (n), 1 ) || SUBSTR (m_string, len - i + 1, 1 ); z_count := 0; is_zero := FALSE; END IF; END LOOP;
b_string := l_sign || b_string; RETURN b_string; EXCEPTION WHEN OTHERS THEN RETURN (SQLERRM); END; --oracle10g给blob字段插入值 create table t_blob(stuname varchar2(20),dblob blob); scott@YPCOST> ed 已写入 file afiedt.buf 1* insert into t_blob values ('tom',utl_raw.cast_to_raw('tom is a good boy!')) scott@YPCOST> /已创建 1 行。scott@YPCOST> select stuname,utl_raw.cast_to_varchar2(dblob) from t_blob;STUNAME -------------------- UTL_RAW.CAST_TO_VARCHAR2(DBLOB) ---------------------------------------------------------------------------------------------------- tom tom is a good boy! scott@YPCOST> update t_blob set dblob=utl_raw.cast_to_raw('tom is not a good boy!') where stuname='t om';已更新 1 行。scott@YPCOST> commit;提交完成。scott@YPCOST> select stuname,utl_raw.cast_to_varchar2(dblob) from t_blob;STUNAME -------------------- UTL_RAW.CAST_TO_VARCHAR2(DBLOB) ---------------------------------------------------------------------------------------------------- tom tom is not a good boy! --修改blob字段的值Declare b_c Blob; Begin select dblob Into b_c From t_blob For Update; dbms_lob.append(b_c,utl_raw.cast_to_raw('and you?')); Update t_blob set dblob = b_c; End;commit;select utl_raw.cast_to_varchar2(dblob) from t_blob;UTL_RAW.CAST_TO_VARCHAR2(DBLOB) --------------------------------------------------------------------- tom is a good boy!and you?--十进制转为二进制 create or replace function number_2_bit(n_num number) return varchar is v_rtn varchar(2000); v_n1 number; v_n2 number; v_num number; v_sign char(1); begin v_num:=abs(n_num); v_n1:= v_num; loop v_n2 := mod(v_n1, 2); v_n1 := trunc(v_n1 / 2); v_rtn := to_char(v_n2) || v_rtn; exit when v_n1 = 0; end loop; return sign(n_num)*v_rtn; exception when others then return(sqlerrm); end;--删除表中重复数据 delete test a where a.rowid=(select max(rowid) from test b where a.id=b.id and a.num=b.num);--上下行的差with tb as( select 2001 a,1 b,2 c from dual union all select 2002, 8, 4 from dual union all select 2003, 6, 6 from dual union all select 2004, 9, 8 from dual) select a,b,c,lead(c) over(order by a),b-lead(c) over(order by a) from tb--如何实现行列转换 create table course ( student_name varchar2(10), subject varchar2(10), grade number );insert into course values('张三','语文',80); insert into course values('张三','数学',70); insert into course values('张三','英语',62); insert into course values('李四','语文',90); insert into course values('李四','数学',80); insert into course values('李四','英语',100);select * from course; STUDENT_NAME SUBJECT GRADE ------------ ---------- ---------- 张三 语文 80 张三 数学 70 张三 英语 62 李四 语文 90 李四 数学 80 李四 英语 100--转换语句如下 select student_name "姓名",max(decode(subject,'语文', grade,null)) "语文", max(decode(subject,'数学', grade,null)) "数学", max(decode(subject,'英语', grade,null)) "英语" from course group by student_name; --转换为 姓名 语文 数学 英语 ---------- ---------- ---------- ---------- 李四 90 80 100 张三 80 70 62 --1.先创建序列 scott@YPCOST> create sequence orderNo_seq start with 100 increment by 1 maxvalue 999;序列已创建。scott@YPCOST> create table test(id number,name varchar2(20));表已创建。--2、再加触发器 scott@YPCOST> ed 已写入 file afiedt.bufcreate or replace trigger insert_tri before insert on test for each row declare begin select orderNo_seq.nextval into :new.id from dual; end; scott@YPCOST> /触发器已创建scott@YPCOST> insert into test(name) values('tom');已创建 1 行。scott@YPCOST> commit;提交完成。scott@YPCOST> select * from test;ID NAME -------------------- -------------------- 100 tom --查询字段中包含_(%)的雇员名 (escape 'a'表示a为转义字符) select * from emp where ename like '%a_%' escape 'a'; select * from emp where ename like '%a%%' escape 'a';select * from emp where ename like '%%' 相当于 select * from emp where ename is not null
--当前日期所在月的第一个星期天和最后一个星期天
select trunc(trunc(add_months(sysdate,-1),'mm'),'day')+7,
trunc(trunc(sysdate,'mm'),'day')
from dual--本周的第一天和最后一天
select trunc(sysdate,'d')+1 from dual;
select trunc(sysdate,'d')+7 from dual; --本月的第一天和最后一天
select trunc(sysdate,'mm') from dual;
select last_day(trunc(sysdate)) from dual;--本季的第一天和最后一天
select trunc(sysdate,'Q') from dual;
select add_months(trunc(sysdate,'Q'),3)-1 from dual;--本年的第一天和最后一天
select trunc(sysdate,'yyyy') from dual;
select add_months(trunc(sysdate,'yyyy'),12)-1 from dual;--当前时间所在的季度数
Select to_char(sysdate,'Q') from dual;--当前时间为年的第几周 (注意当前年的第一天为周几)
select to_char(sysdate,'ww') from dual;--当前时间为月的第几周 (注意当前月的第一天为周几)
select to_char(sysdate,'w') from dual;--当前时间为年的第几天
Select to_char(sysdate,'DDD') from dual;--当前时间为当前季度的第几天
select ceil(sysdate- trunc(sysdate,'Q')) from dual;--修改系统时间格式
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss.sssss';
--时间显示格式2011-2-3 加fxfm位数不足两位的时候不会补0
select to_char(to_date('20100102','yyyymmdd'),'yyyy-FMmm-FXDD') from dual;--时间显示为2010年2月3日
select to_char(sysdate,'YYYY"年"MM"月"DD"日"') from dual;
--sqlplus打开输出
set serveroutput on
--随机生成字母和数字
begin
for i in 1..10 loop
DBMS_OUTPUT.PUT_LINE(sys_guid());
end loop;
end;
--expdp导出时排除某些表
EXCLUDE=TABLE:"IN ('TABLENAME1', 'TABLENAME2')"
--查看scott用户下各个表的数据量
declare
cnt number;
sqlstr varchar2(4000);
v_tablename varchar2(30);
cursor c_tablename is select table_name from all_tables where owner='SCOTT';
begin
open c_tablename;
loop
fetch c_tablename into v_tablename;
exit when c_tablename%notfound;
sqlstr:='select count(*) from '||v_tablename;
execute immediate sqlstr into cnt;
dbms_output.put_line('table_name: '||v_tablename||'; count: '||cnt);
end loop;
close c_tablename;
end;
--创建表空间
create tablespace test
logging
datafile 'D:\oracle\oradata\ypcost\test01.dbf'
size 10M
autoextend on
next 1M maxsize 20M
extent management local--删除表空间 包括所有的数据对象和数据文件
drop tablespace tablesapcename including contents and datafiles;--增加数据文件
alter tablespace tablespacename add
datafile 'D:\oracle\oradata\ypcost\test01.dbf'
size 10M
autoextend on
next 1M maxsize 20M--修改表空间为自动增长
alter database datafile 'D:\oracle\oradata\aa\test01.dbf' autoextend on;
--各个表空间的使用率、select Total.Tname "表空间名称",
Total.Total_Size "表空间大小",
Total.Total_Size - Used.free_size as "已使用大小",
Used.Free_size as 表空间剩余大小,
Round((Total.Total_Size - Used.free_size) / Total.Total_Size,4)* 100 || '%' as 表空间使用率
from
(
-- 表空间数据文件的大小
select tablespace_name as TName,
round(sum(user_bytes)/(1024*1024),1) as Total_size
from dba_data_files
group by tablespace_name
) Total,
(
-- 表空间剩余的大小
select tablespace_name as TName,
round(sum(bytes)/(1024*1024),1) as Free_size
from dba_free_space
group by tablespace_name
) Used
where Total.TName = Used.TName(+)
--oracle分页的存储过程
create or replace procedure fenye_pro(
v_tablename varchar2, --表名
v_pagesize int, --一页显示的记录数
v_pagenow int,--要显示第几页
v_pagerows out number,--总页数
v_counts out number,--总记录数
recode_cursor out sys_refcursor)
as
v_begin number:=1+(v_pagenow-1)*v_pagesize;
v_end number:=v_pagenow*v_pagesize;
v_sqlstr varchar2(4000);
v_flag number:=0;
begin
select count(*) into v_flag from user_tables where table_name=v_tablename;
if v_flag=0 then
dbms_output.put_line('输入的表'||v_tablename||'不存在');
else
v_sqlstr:='select count(*) from '||v_tablename;
execute immediate v_sqlstr into v_counts;
v_pagerows:=ceil(v_counts/v_pagesize);
v_sqlstr:='select * from (select rownum rn,t.* from (select * from '||v_tablename||
') t where rownum<='||v_end||') where rn>='||v_begin;
open recode_cursor for v_sqlstr;
--dbms_output.put_line(v_sqlstr);
end if;
exception
when others then
dbms_output.put_line('参数输入格式或类型不符');
end;--修改序列
Alter sequence seq
[increment by n]
[{maxcalue n/nomaxalue}]
[{minvalue n/nominvalue}]
[{cycle/nocycle}]
[{cache n/nocache}];
--merge into的使用
merge into fzq1 aa --fzq1表是需要更新的表
using fzq bb -- 关联表
on (aa.id=bb.id) --关联条件
when matched then --匹配关联条件,作更新处理
update set
aa.chengji=bb.chengji+1, --不能更新on中包含的字段
aa.name=bb.name --此处只是说明可以同时更新多个字段。
when not matched then --不匹配关联条件,作插入处理。如果只是作更新,下面的语句可以省略。
insert values( bb.id, bb.name, bb.sex,bb.kecheng,bb.chengji);
--修改连接数
show parameter processes;
show parameter sessions;
alter system set processes=300 scope=spfile;
alter system set sessions=300 scope=spfile;
shutdown immediate;--修改完后要重启数据库
startup
--数字金额转化为中文大写
CREATE OR REPLACE FUNCTION money_to_chinese (money IN number)
RETURN VARCHAR2
IS
c_money VARCHAR2 (12);
m_string VARCHAR2 (60) := '分角圆拾佰仟万拾佰仟亿';
n_string VARCHAR2 (40) := '壹贰叁肆伍陆柒捌玖';
b_string VARCHAR2 (80);
n CHAR;
len NUMBER (3);
i NUMBER (3);
tmp NUMBER (12);
is_zero BOOLEAN; --标记当前的前一个数值是否为0
z_count NUMBER (3); --万位、各位和最后尾数前连续0的个数
l_money NUMBER;
l_sign VARCHAR2 (10);
BEGIN
l_money := ABS (money); --得到传入阿拉伯数值的绝对值
--判断传入的数值是正还是负,如果是负则加上'负'
IF money < 0
THEN
l_sign := '负';
ELSE
l_sign := '';
END IF;
tmp := ROUND (l_money, 2) * 100;
c_money := TRIM (TO_CHAR (tmp, '999999999999'));
len := LENGTH (c_money);
is_zero := TRUE;
z_count := 0;
i := 0;
WHILE i < len
LOOP
i := i + 1;
n := SUBSTR (c_money,
i,
1
);
IF n = '0'
THEN
IF len - i = 6 OR len - i = 2 OR len = i --判断是否到万位、各位和最后位
THEN
IF is_zero --如果前一位为0把拼接成的 '零' 删除掉
THEN
b_string := SUBSTR (b_string,
1,
LENGTH (b_string) - 1
);
is_zero := FALSE;
END IF;
IF len - i = 6
THEN
b_string := b_string || '万';
END IF;
IF len - i = 2
THEN
b_string := b_string || '圆';
END IF;
IF len = i
THEN
b_string := b_string || '整';
END IF;
z_count := 0;
ELSE
IF z_count = 0
THEN
b_string := b_string || '零';
is_zero := TRUE;
END IF;
z_count := z_count + 1;
END IF;
ELSE
b_string :=
b_string
|| SUBSTR (n_string,
TO_NUMBER (n),
1
)
|| SUBSTR (m_string,
len - i + 1,
1
);
z_count := 0;
is_zero := FALSE;
END IF;
END LOOP;
b_string := l_sign || b_string;
RETURN b_string;
EXCEPTION
WHEN OTHERS
THEN
RETURN (SQLERRM);
END; --oracle10g给blob字段插入值
create table t_blob(stuname varchar2(20),dblob blob);
scott@YPCOST> ed
已写入 file afiedt.buf 1* insert into t_blob values ('tom',utl_raw.cast_to_raw('tom is a good boy!'))
scott@YPCOST> /已创建 1 行。scott@YPCOST> select stuname,utl_raw.cast_to_varchar2(dblob) from t_blob;STUNAME
--------------------
UTL_RAW.CAST_TO_VARCHAR2(DBLOB)
----------------------------------------------------------------------------------------------------
tom
tom is a good boy!
scott@YPCOST> update t_blob set dblob=utl_raw.cast_to_raw('tom is not a good boy!') where stuname='t
om';已更新 1 行。scott@YPCOST> commit;提交完成。scott@YPCOST> select stuname,utl_raw.cast_to_varchar2(dblob) from t_blob;STUNAME
--------------------
UTL_RAW.CAST_TO_VARCHAR2(DBLOB)
----------------------------------------------------------------------------------------------------
tom
tom is not a good boy!
--修改blob字段的值Declare
b_c Blob;
Begin
select dblob Into b_c From t_blob For Update;
dbms_lob.append(b_c,utl_raw.cast_to_raw('and you?'));
Update t_blob
set dblob = b_c;
End;commit;select utl_raw.cast_to_varchar2(dblob) from t_blob;UTL_RAW.CAST_TO_VARCHAR2(DBLOB)
---------------------------------------------------------------------
tom is a good boy!and you?--十进制转为二进制
create or replace function number_2_bit(n_num number) return varchar is
v_rtn varchar(2000);
v_n1 number;
v_n2 number;
v_num number;
v_sign char(1);
begin
v_num:=abs(n_num);
v_n1:= v_num;
loop
v_n2 := mod(v_n1, 2);
v_n1 := trunc(v_n1 / 2);
v_rtn := to_char(v_n2) || v_rtn;
exit when v_n1 = 0;
end loop;
return sign(n_num)*v_rtn;
exception
when others then
return(sqlerrm);
end;--删除表中重复数据
delete test a where a.rowid=(select max(rowid) from test b where a.id=b.id and a.num=b.num);--上下行的差with tb as(
select 2001 a,1 b,2 c from dual union all
select 2002, 8, 4 from dual union all
select 2003, 6, 6 from dual union all
select 2004, 9, 8 from dual)
select a,b,c,lead(c) over(order by a),b-lead(c) over(order by a)
from tb--如何实现行列转换
create table course
(
student_name varchar2(10),
subject varchar2(10),
grade number
);insert into course values('张三','语文',80);
insert into course values('张三','数学',70);
insert into course values('张三','英语',62);
insert into course values('李四','语文',90);
insert into course values('李四','数学',80);
insert into course values('李四','英语',100);select * from course;
STUDENT_NAME SUBJECT GRADE
------------ ---------- ----------
张三 语文 80
张三 数学 70
张三 英语 62
李四 语文 90
李四 数学 80
李四 英语 100--转换语句如下
select student_name "姓名",max(decode(subject,'语文', grade,null)) "语文",
max(decode(subject,'数学', grade,null)) "数学",
max(decode(subject,'英语', grade,null)) "英语"
from course
group by student_name;
--转换为
姓名 语文 数学 英语
---------- ---------- ---------- ----------
李四 90 80 100
张三 80 70 62
--1.先创建序列
scott@YPCOST> create sequence orderNo_seq start with 100 increment by 1 maxvalue 999;序列已创建。scott@YPCOST> create table test(id number,name varchar2(20));表已创建。--2、再加触发器
scott@YPCOST> ed
已写入 file afiedt.bufcreate or replace trigger insert_tri
before insert on test
for each row
declare
begin
select orderNo_seq.nextval into :new.id from dual;
end;
scott@YPCOST> /触发器已创建scott@YPCOST> insert into test(name) values('tom');已创建 1 行。scott@YPCOST> commit;提交完成。scott@YPCOST> select * from test;ID NAME
-------------------- --------------------
100 tom --查询字段中包含_(%)的雇员名 (escape 'a'表示a为转义字符)
select * from emp where ename like '%a_%' escape 'a';
select * from emp where ename like '%a%%' escape 'a';select * from emp where ename like '%%'
相当于
select * from emp where ename is not null