ORACLE笔记

来源：互联网发布：做淘宝客服有前途吗编辑：程序博客网时间：2024/06/05 15:49

**************sqlplus 连接远程数据库系统**********************
方式：简易连接，不用进行网络配置，其实就是tnsname.ora文件,但只支持oracle10G以上。
命令：sqlplus 用户名/密码@service_name [as sysdba]
示例：sqlplus sys/pwd@test as sysdba
备注：使用默认1521端口时可省略输入
-------------------------------------------------------------------------

---创建临时表空间
CREATE TEMPORARY TABLESPACE YMSDB_TMP
TEMPFILE '/ora/ymsDbFile/YMSDB_TMP.DBF'
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;

--创建用户表空间
CREATE TABLESPACE YMSDB
LOGGING
DATAFILE '/ora/ymsDbFile/YMSDB.DBF'
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;

--创建用户并制定表空间
CREATE USER ymsuser IDENTIFIED BY ymsuser
DEFAULT TABLESPACE YMSDB
TEMPORARY TABLESPACE YMSDB_TMP;

-------------------------------
--创建表空间

-------------------------------
--创建表空间

CREATE TABLESPACE ts_dayerrorstatic_2011
LOGGING
DATAFILE '/ora/ymsDbFile/ts_dayerrorstatic_2011.DBF'
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE 1024M
EXTENT MANAGEMENT LOCAL;

.......

--给用户授予权限
GRANT
　　CREATE SESSION, CREATE ANY TABLE, CREATE ANY VIEW ,CREATE ANY INDEX, CREATE ANY PROCEDURE,
　　ALTER ANY TABLE, ALTER ANY PROCEDURE,
　　DROP ANY TABLE, DROP ANY VIEW, DROP ANY INDEX, DROP ANY PROCEDURE,
　　SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE ,resource,connect
TO ymsuser;

grant dba,connect to ymsuser ;

-- ***********************************************************************************
-- 模块功能:
-- job job的唯一标识，自动生成的
-- broken 是否处于运行状态，N;运行；Y:停止
-- what 存储过程名称
-- interval 定义的执行时间
--补充：
--描述                    INTERVAL参数值
--一分钟执行一次          'TRUNC(sysdate,''mi'') + 1 / (24*60)'
--每天午夜12点            ''TRUNC(SYSDATE + 1)''
--每天早上8点30分         ''TRUNC(SYSDATE + 1) + （8*60+30）/(24*60)''
--每星期二中午12点         ''NEXT_DAY(TRUNC(SYSDATE ), ''''TUESDAY'''' ) + 12/24''
--每个月第一天的午夜12点    ''TRUNC(LAST_DAY(SYSDATE ) + 1)''
--每个季度最后一天的晚上11点 ''TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), ''Q'' ) -1/24''
--每星期六和日早上6点10分    ''TRUNC(LEAST(NEXT_DAY(SYSDATE, ''''SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY"))) + （6×60+10）/（24×60）''
-- 作者:     nourewang
-- 建立日期: 2011-12-15
-- 修改历史:
-- **********************************************************************************
--创建job，并且指定为每天午夜12点执行一次
declare
job number;
begin
dbms_job.submit(job, 'pagetfcountinfo_proc;', sysdate, 'TRUNC(SYSDATE + 1)');
end
commit;

select job,broken,what,interval,t.* from user_jobs t;

begin
dbms_job.remove(46);
end;

--------------------------------------------TEST
--------- 创建测试job , 一分钟执行一次.
declare
job number;
begin
dbms_job.submit(job, 'errorresultstatic_proc;', sysdate, 'TRUNC(sysdate,''mi'') + 1 / (24*60)');
dbms_job.submit(job, 'dayworkstatic_proc;', sysdate, 'TRUNC(sysdate,''mi'') + 1 / (24*60)');
dbms_job.submit(job, 'datacountInfo_proc;', sysdate, 'TRUNC(SYSDATE + 1)');
end
commit;

select job,broken,what,interval,t.* from user_jobs t;

begin
dbms_job.remove(49);
dbms_job.remove(50);
end;

创建分区索引

--   drop index   inx_pm_fcodestr_local;
create index inx_pm_fcodestr_local on d_pmresult(SUBSTR(FCODE,0,10))
local
(
        partition inx_pm_fcodestr_2011q4 tablespace ts_pmresult_2011q4,
        partition inx_pm_fcodestr_2012q1 tablespace ts_pmresult_2012q1,
        partition inx_pm_fcodestr_2012q2 tablespace ts_pmresult_2012q2,
        partition inx_pm_fcodestr_2012q3 tablespace ts_pmresult_2012q3,
        partition inx_pm_fcodestr_2012q4 tablespace ts_pmresult_2012q4,
        partition inx_pm_fcodestr_2013q1 tablespace ts_pmresult_2013q1,
        partition inx_pm_fcodestr_2013q2 tablespace ts_pmresult_2013q2,
        partition inx_pm_fcodestr_2013q3 tablespace ts_pmresult_2013q3,
        partition inx_pm_fcodestr_2013q4 tablespace ts_pmresult_2013q4
);

-------------存储过程

create or replace package p_page is
-- Author : PHARAOHS
-- Created : 2006-4-30 14:14:14
-- Purpose : 分页过程
TYPE type_cur IS REF CURSOR; --定义游标变量用于返回记录集

PROCEDURE Pagination(Pindex in number, --分页索引
                       Psql   in varchar2, --产生dataset的sql语句
                       Psize in number, --页面大小
                       Pcount out number, --返回分页总数
                       v_cur out type_cur --返回当前页数据记录
                       );

procedure PageRecordsCount(Psqlcount in varchar2, --产生dataset的sql语句
                             Prcount   out number --返回记录总数
                             );

end p_page;

create or replace package body p_page is

      PROCEDURE Pagination(Pindex in number,
                           Psql   in varchar2,
                           Psize in number,
                           Pcount out number,
                           v_cur out type_cur) AS

        v_sql   VARCHAR2(1000);
        v_count number;
        v_Plow number;
        v_Phei number;
      Begin
        ------------------------------------------------------------取分页总数
        v_sql := 'select count(*) from (' || Psql || ')';
        execute immediate v_sql
          into v_count;
        Pcount := ceil(v_count / Psize);
        ------------------------------------------------------------显示任意页内容
        v_Phei := Pindex * Psize + Psize;
        v_Plow := v_Phei - Psize + 1;
        --Psql := 'select rownum rn,t.* from zzda t' ;            --要求必须包含rownum字段
        v_sql := 'select * from (' || Psql || ') where rn between ' ||
                 v_Plow || ' and ' || v_Phei;

        open v_cur for v_sql;

      End Pagination;

    --**************************************************************************************

    procedure PageRecordsCount(Psqlcount in varchar2, Prcount out number) as

      v_sql     varchar2(1000);
      v_prcount number;

    begin

      v_sql := 'select count(*) from (' || Psqlcount || ')';
      execute immediate v_sql
        into v_prcount;
      Prcount := v_prcount; --返回记录总数

    end PageRecordsCount;

    --**************************************************************************************
end p_page;
--------------------------------测试

create or replace procedure Test_proc as
-- ***********************************************************************************
-- 模块功能:
-- 输入参数:
-- 输出参数:
-- 作者:     nourewang
-- 建立日期: 2011-12-24
-- 修改历史:
-- **********************************************************************************

----public group v
v_lastday VARCHAR2(50);
v_firstday VARCHAR2(50);
v_now      VARCHAR2(50);
-- exception
begin
      set transaction use rollback segment hdhouse_rs;
      v_now := '2012-3-2';
      dbms_output.put_line('v_now1111 ' || v_now);
      -- 获取当前月最后一天
   --   select to_char(last_day(sysdate), 'yyyy-mm-dd') into v_lastday from dual;
      -- dbms_output.put_line('获取当前月最后一天 ' || v_lastday);
      -- 获取当前月最后一天 + 1
     -- select to_char(last_day(sysdate) + 1, 'yyyy-mm-dd')   into v_firstday from dual;
      -- dbms_output.put_line('获取当前月最后一天 + 1 ' || v_firstday);
      v_now := to_char(to_date(v_now,'yyyy-mm-dd') +1, 'yyyy-mm-dd') ;
      dbms_output.put_line('v_now ' || v_now);



    /* if (v_now = v_lastday) then
        dbms_output.put_line('31号' || v_firstday);
      else
        dbms_output.put_line('获取当前' || v_now);
      end if;*/
exception
when others then
         dbms_output.put_line('其他异常,待查! ');
end;