趁周末有时间总结下论坛中大家常提的问题。
由于个人能力有限,如果有问题希望大家指出,谢谢!
欢迎大家进行修改和补充

解决方案 »

  1.   


    --当前日期所在月的第一个星期天和最后一个星期天
    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;
      

  2.   


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

  3.   


    --创建表空间
    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(+)
      

  4.   


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