常见问题小结

--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_ordercol varchar2,--要排序的字段 如果有多个用,隔开
v_pagesize  int,  --一页显示的记录数
v_pagenow int,--要显示第几页
v_pagerows out number,--总页数
v_counts   out number,--总记录数
recode_cursor out sys_refcursor,
v_order varchar2 default 'asc') --降序还是升序 默认是升序
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=upper(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||
           ' order by '||v_ordercol||' '||v_order||') 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;

 

--调用分页过程

var cnt number;
var pagerow number;
var r_c refcursor;
exec fenye_pro('emp','sal desc,hiredate',4,1,:cnt,:pagerow,:r_c); 

print r_c;

      RN      EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
-------- ---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
       1       7777 aspen      SALESMAN                                                      10
       2       7839 KING       PRESIDENT            1981-11-17       5000                    10
       3       7902 FORD       ANALYST         7566 1981-12-03       3000        900         20
       4       7788 SCOTT      ANALYST         7566 1987-04-19       3000        900         20
 

 

--修改序列
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.buf

create 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