oracle存储过程教程

存储过程分为包头和包体两部分

创建包头

create or replace package PCK_ACHIEVEMENTS_A_C_S is -- PCK_ACHIEVEMENTS_A_C_S存储过程名
	CONSTANT M_INDUSTRYCODE varchar2(4) := '9999'; -- 定义常量，供包体使用，这种定义的形式，也可以在其他存储过程或者DML/DQL中使用
	M_XI_PIAO VARCHAR2(4) := '6011'; -- 定义变量
	TYPE REF_CURSOR_TYPE IS REF CURSOR; -- 定义游标类型
	-- 定义方法
  	FUNCTION GET_INDUSTRIESALIAS(I_CPDM    IN VARCHAR2, -- 产品代码
                               	 I_VC_SCDM IN VARCHAR2 --市场代码
                                 ) RETURN VARCHAR2;
	-- 定义存储过程
	PROCEDURE GET_CLASS_99_SHIZHI(I_CPDM   VARCHAR2, -- 产品代码，默认为IN
                                I_DATE   DATE, -- 上一日市值
                                O_RESULT OUT NUMBER -- 大类其他需要减去的市值汇总
                                );
end PCK_ACHIEVEMENTS_A_C_S;

创建包体

create or replace package body PCK_ACHIEVEMENTS_A_C_S is -- 需要和包头对应
  --方法需要和包头定义的参数保持一致，如参数名称和参数类型  --获得行业版块
  FUNCTION GET_INDUSTRIESALIAS(I_CPDM    IN VARCHAR2, -- 产品代码
                               I_VC_SCDM IN VARCHAR2 --市场代码
                               ) RETURN VARCHAR2 IS
    U_INDUSTRIESALIAS VARCHAR2(12); --行业版块，用于获取执行结果，当前的执行结果为单个值，所以使用 VARCHAR2 定义
    V_CLOB            VARCHAR2(2000);
    V_HANGYE_TYPE     VARCHAR2(20) DEFAULT 'ZJH';-- 定义变量，并设置默认值
    e_exception exception; -- 声明异常
    pragma exception_init(e_exception,-20018); -- 注册自定义的异常类型，需要写入一个异常代码，不要与数据库本身的异常代码冲突了
  BEGIN
  	begin -- begin end 代码块
        SELECT APH_HY_CODE
          INTO V_HANGYE_TYPE
          FROM PR_PAR_ACTUAL_PORTFOLIO_HY
         WHERE APH_CODE = I_CPDM;
      exception -- 数据未找到的异常，如果select没有数据，into会报错的
        -- 如果设置了默认值，即使没有数据，也不会报错，会使用默认的数据
        when no_data_found then
         V_HANGYE_TYPE:='1'; -- 变量赋值使用英文的冒号等号，例 变量:=值 
      end;
    if V_HANGYE_TYPE<>0 then -- 当触发什么条件的时候，进行抛出异常
         raise_application_error(-20018,E_MESSAGE); -- 抛出异常，需要指定定义的异常代码，并写入异常描述
    end if;
    V_CLOB := 'SELECT NVL(MAX(HANG_YE), ''OTHERS'') FROM PR_VW_PAR_HY_' || V_HANGYE_TYPE || '
           WHERE SCDM = ''' || I_VC_SCDM || ''' ';
    EXECUTE IMMEDIATE V_CLOB INTO U_INDUSTRIESALIAS; -- 动态SQL执行器
    
    RETURN U_INDUSTRIESALIAS; -- 返回 结果
  EXCEPTION -- function必须有这个异常捕捉器，当然了，也会向上抛出异常的，PROCEDURE 如果不写的话，会向上抛出 
  	WHEN e_exception THEN -- 可以捕获自定义异常
  		RETURN 'ERROR';
    WHEN OTHERS THEN -- others 异常，就如和java中的exception一致，属于兜底的，也可以写入多个异常类型
      RETURN 'OTHERS'; -- 当出现错误的时候，返回什么样的结果
  END GET_INDUSTRIESALIAS;
  
  -----------------------存储过程
  	-- 需要和包头定义的存储过程的参数保持一致，如参数名，参数类型等
	procedure GET_CLASS_99_SHIZHI(i_date varchar2 -- 运行的日期
                           		  O_FLAG OUT INT default 0, -- 可以设置默认值，当前默认值为0
                                  O_MESSAGE OUT VARCHAR2
                                 ) is
     E_FLAG INT DEFAULT 0;
     E_MESSAGE VARCHAR2(4000);               			
	cursor c_fund_code is -- 也可以在直接定义游标，查询所有产品信息
    select distinct vc_cpdm from vjk_wbfk_cpxx  b
                                 where exists (
                                 select vc_cpdm from (
                                 select vc_cpdm from vjk_wbfk_vouchers where D_MAKE=to_date(i_date, 'yyyyMMdd')
                                 union 
                                 SELECT vc_cpdm FROM vjk_wbfk_gzb where D_YWRQ = to_date(i_date, 'yyyyMMdd')
                                 ) a where a.vc_cpdm = b.vc_cpdm);
  	begin
		FOR C_CURSOR IN ( -- 直接使用 cursor进行循环取数据
            SELECT DISTINCT PRODUCT_CODE FROM dcetl.vw_out_df_perf_req WHERE REQ_TYPE = '01' AND RPT_FREQUENCY <> '0')
        LOOP
           
         END LOOP; -- 循环结束标志
         
	open c_fund_code; -- 读取定义的游标
    loop -- 循环标志
      fetch c_fund_code into v_func_code; -- 读取游标
      exit when c_fund_code%notfound; -- 当没有数据的时候，进行退出
      /**根据日期进行初始化化数据--实际组合规模
      */
      APSD_CODE(v_func_code, --实际组合代码
                i_date --日期
                ,E_FLAG,E_MESSAGE);
       IF E_FLAG<>0 THEN 
         O_FLAG:=-1;
         O_MESSAGE:=O_MESSAGE||E_MESSAGE||';';
         END IF;
    end loop;
    close c_fund_code;
    -- 高级一点的用法，获取数据表格的内容
    select * from table(v_MARKET_VALUE_TABLE);
	commit;-- 如果存在DML语句，需要使用COMMIT，进行主动提交
   EXCEPTION
      when others then
        -- 可以做一些其他操作
  end GET_CLASS_99_SHIZHI;
end PCK_ACHIEVEMENTS_A_C_S;

自定义 oracle 数据结构

create or replace type MARKET_VALUE_RECORD force IS object(
       --股票--》市值 RECORD
    EN_SZ_BEFORE        NUMBER NULL , --上一日市值(科目类不可为空)
    EN_SZZJZ       NUMBER(19,4) NULL, --权重
    EN_SZ           NUMBER NULL, --市值(科目类不可为空)
    VC_SCDM         VARCHAR2(20) NULL, --证券代码
    INDUSTRIESALIAS VARCHAR2(12) NULL --版块信息
    )

将数据结构定义为数据类型，定义为表格类型

create or replace type MARKET_VALUE_TYPE IS TABLE OF MARKET_VALUE_RECORD;

如果在使用动态SQL的时候，出现字符溢出的话，可以使用一下方法，进行打印sql

 --打印输出
  procedure DBMS_CLOB_SHOW(i_clob in clob,
                           i_show in varchar2 --是否显示dbms_output
                           ) IS
    loopCount number(5);
    strlong   varchar2(2000); --单次输出的临时变量
  begin
    if 'true' = i_show AND i_clob IS NOT NULL then
      --dbms_output 的buffersize
      -- dbms_output.enable(null);--表示输出无限制
      dbms_output.enable(500000);
      dbms_output.put_line('length(clob):' || dbms_lob.getlength(i_clob));
      --按照1000分块
      loopCount := floor(dbms_lob.getlength(i_clob) / 1000);
      dbms_output.put_line('loopcount:' || loopcount);
      for i in 0 .. loopCount loop
        --dbms_output.put_line('loop-----------------' || i);
        strlong := substr(i_clob, i * 1000 + 1, 1000);
        dbms_output.put_line(strlong);
      end loop;
    
      --dbms_output.freetemporary(i_clob);
      dbms_output.put_line('finish---------------------');
    end if;
  end DBMS_CLOB_SHOW;

创建自动分区表

-- Create table
create table SYS_OPERATE_LOG
(
  operate_log_id NUMBER(16) not null,
  ip_address     VARCHAR2(64),
  mac_address    VARCHAR2(32),
  user_info      VARCHAR2(32),
  menu_code      VARCHAR2(8),
  operation      VARCHAR2(64),
  is_success     VARCHAR2(16),
  operate_time   VARCHAR2(20),
  range_time     DATE -- RANGE_TIME 必须为日期类型，才能使用自动分区
)
partition by range(RANGE_TIME)
  interval(numtoyminterval(1,'year'))(
              partition SYS_OPERATE_LOG_T01 values less than(to_date('2005-01-01','yyyy-mm-dd'))
  )tablespace TBS_PERFORM_HIST
	pctfree 10
	initrans 1
	maxtrans 255
	storage
	(
	  initial 80K
	  next 1M
	  minextents 1
	  maxextents unlimited
	  pctincrease 0
	)
;

启动oracle

export ORACLE_SID=perform
sqlplus /nolog
connect / as sysdba
startup force;
exit;

修改oracle的进程数

-- 若无特殊必要，不建议修改
alter system set processes=3000 scope=spfile;
-- sessions 必须要比 processes 大
alter system set sessions=3350 scope=spfile;

表空间赋权

-- perform 用户名 ，tbs_perform 表空间名称
alter user perform quota unlimited on tbs_perform;
alter user perform quota unlimited on tbs_perform_hist;
alter user perform quota unlimited on tbs_perform_idx;

创建数据库实例，一般使用静默方式创建实例和删除实例，其他方式的创建实例，一般不建议

在我们申请数据库机器的时候，会默认安装好数据库，也有默认的实例，若需要新建实例，可以参考，oracle的home下的soft文件夹的rsp文件

rsp文件里面有具体的说明，一般只使用创建实例和删除实例，只需要修改sys的密码和使用的模式即可

最好是新建一个rsp文件，可对文件有绝对控制权限

使用命令

dbca -silent -responseFile /home/oracle/soft/dbca.rsp 

若出现ora-27102,应该是共享页参数设置过小了，在root下执行命令

# 此命令会在系统重启的时候有一定的几率失效
echo '8388608' > /proc/sys/kernel/shmall

创建表空间，可参考以下sql

CREATE TABLESPACE  TBS_PERFORM  NOLOGGING DATAFILE  'TBS_PERFORM.ORA' SIZE 10M  REUSE AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL  UNIFORM SIZE 1024K SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE  TBS_PERFORM_IDX  NOLOGGING DATAFILE  'TBS_PERFORM_IDX.ORA' SIZE 10M  REUSE AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL  UNIFORM SIZE 1024K SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE  TBS_PERFORM_HIST  NOLOGGING DATAFILE  'TBS_PERFORM_HIST.ORA' SIZE 10M  REUSE AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL  UNIFORM SIZE 1024K SEGMENT SPACE MANAGEMENT AUTO;

当磁盘不足时，会报 ORA-27102: out of memory 这个错误，需要查看磁盘使用情况，主要看dbf文件的路径所在的磁盘空间

使用命令 df -h 查看，如果使用率超过98%，则可考虑增加磁盘

如何挂载磁盘，可以参考如下命令

# 查看磁盘信息,查看当前是有存在没有被使用的磁盘，如果存在没有被使用的磁盘，则可以进行挂载
fdisk -l
# 可能会出现磁盘未分区的情况，可参考百度，分区之后，需要格式化磁盘，之后可使用一下命令进行挂载
# 创建挂载路径，新建文件夹
mkdir /sdb
# 使用mount 进行挂载磁盘
mount /dev/sdb1 /sdb
#挂载完成之后，需要设置开机启动
vi /etc/fstab
#添加挂载信息，一般使用ext4为默认挂载格式
/dev/sdb1 /sdb ext4 defaults 0 0

挂载之后，我们需要迁移数据

-- 需要先关闭数据库例程
shutdown immedate;
-- 移动表空间
cp /sdb/oracle/oradata/ds/undotbs03.dbf /oracle/oradata/orcltest/undotbs03.dbf 
-- 删除之前的表空间
rm -rf /sdb/oracle/oradata/ds/undotbs03.dbf

需要重启系统，才能释放磁盘空间

reboot

重启之后，加载表空间

 # 切换到oracle用户
 su - oracle
 # 使用sqlplus进行登录
 sqlplus /nolog

-- 使用本地连接数据库
connect / as sysdba
-- 启动数据库例程，若在此处报 ORA-27102: out of memory 异常，请参照上面修改内存分页大小，设置完成之后，并重新启动数据库例程
startup;
-- 将表空间进行下线操作
alter database datafile '/sdb/oracle/oradata/ds/undotbs03.dbf' offline drop;
-- 将表空间进行重命名操作
alter database rename file '/sdb/oracle/oradata/ds/undotbs03.dbf' to '/oracle/oradata/orcltest/undotbs03.dbf';
-- 在此处需要进行重连数据库操作，否则可能会报错 ORA-03135: connection lost contact
connect / as sysdba 
-- 将表空间进行上线操作 或者也可以将 '/oracle/oradata/orcltest/undotbs03.dbf' 转换为 数据库中的数字编号 
alter database datafile '/oracle/oradata/orcltest/undotbs03.dbf' online;
-- 重新强制启动数据库
startup force;
-- 若不报错，则到此数据库启动成功

oracle 密码重试不进行锁定

alter profile default limit FAILED_LOGIN_ATTEMPTS UNLIMited;

oracle 账户解锁

 alter user ds account unlock;

oracle 导出数据

exp "'sys/oracle@10.46.25.45/orcltest as sysdba'" file=D:/ds.dump owner=ds
exp "'sys/oracle@10.46.25.45/orcltest as sysdba'" file=D:/ds.dump full=y
expdp "'sys/oracle@10.46.25.45/orcltest as sysdba'" directory=DATA_DIR dumpfile=ds_test3.dmp schemas=ds

oracle 导入数据

imp trusteeVisual/trusteeVisual@ds full=y file=/sdb/oracleExp/trusteeVisual.dmp
imp DS_TEST/DS_TEST@ds file=/sdb/oracleExp/ds_test.dump fromuser=ds touser=ds_test statistics=none
impdp DS_TEST/DS_TEST@ds dumpfile=ds_test3.dmp remap_schema=ds:DS_TEST
impdp DS2/DS2@ds directory=DATA_DIR  dumpfile=ds2.dmp schemas=ds2

转移数据库文件的一般操作

-- 1.关闭数据库
shutdown immediate;
-- 2.开始复制文件
-- 3.启动数据库到mount状态
startup mount;
-- 4.修改文件位置
alter database rename file '/home/oracle/app/oracle/oradata/ds/temp01.dbf' to '/sdb/oradata/ds/temp01.dbf';
-- 5.打开数据库
alter database open;
-- 6.查看数据库文件位置
select name from v$databasefile;

清理表空间

# 设置sid实例
export ORACLE_SID=perform
# sqlplus 登录
sqlplus /nolog
# 本地登录
connect / as sysdba

#压缩临时表空间
alter tablespace temp shrink space;
#清空回收站
purge recyclebin;

#清理undo表空间
#创建一个新的undo表空间,不限制最大值
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/sdb/oracle/app/oracle/oradata/perform/undotbs02.dbf' SIZE 10M MAXSIZE UNLIMITED;
#将系统的undo表空间指向新的undo表空间
ALTER SYSTEM SET undo_tablespace =UNDOTBS2;
#删除之前的undo表空间和表空间文件
DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;
# 强制重新启动
startup force;

#关闭undo自动优化特性
ALTER SYSTEM SET "_undo_autotune" =FALSE;

当我们创建索引之后，查询还是比较慢的时候，可以考虑执行表分析操作，数据量越大，表分析越慢

-- User_Base_info为需要进行表分析的表名
ANALYZE TABLE User_Base_info COMPUTE STATISTICS;

创建scheduler job

 dbms_scheduler.create_job(JOB_NAME   => 'JOB$_B' || c_job_no,
                              JOB_TYPE   => 'PLSQL_BLOCK',
                              JOB_ACTION => 'BEGIN 
                                  PCK_ACHIEVEMENTS_JOBS.INIT_DATE_CPDM(''' ||i_date ||''');
                                  END;',
                              START_DATE => SYSDATE,
                              ENABLED    => TRUE);
-- 查看启动的job                             
SELECT * FROM user_scheduler_jobs;
-- 查看job日志
SELECT * FROM user_scheduler_job_log;
-- 查看job运行的信息
SELECT * FROM user_scheduler_job_run_details;

创建 job

DECLARE
  JOBNO NUMBER;
begin
  -- 同步咨询数据
  DBMS_JOB.submit(JOBNO,
                  'PCK_DATA_SYN.SYN_ZX_DATA();',
                  sysdate,
                  'TRUNC(SYSDATE,''dd'')+1+22/24');
  COMMIT;
end;
-- 查看job信息
SELECT * FROM user_jobs;

创建DB_LINK

drop public database link DFZQWDZX_LINK;
prompt Creating database link DFZQWDZX_LINK...
-- Create database link 
create public database link DFZQWDZX_LINK
  connect to tgread IDENTIFIED BY "Y*iaciej123456"
 using '(DESCRIPTION= (ADDRESS_LIST=
     (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.21.3)(PORT = 1521))
   ) (CONNECT_DATA=(SERVICE_NAME=wdzx) ) )';

创建 同义词

-- Create the synonym 
create or replace synonym AINDEXHS300WEIGHT
  for WIND_FILESYNC.AINDEXHS300WEIGHT@DFZQWDZX_LINK;

创建物化视图

-- 删除物化视图
drop materialized view pr_vw_par_hang_ye ;
-- 创建物化视图
create  materialized view pr_vw_par_hang_ye 
tablespace tbs_perform
build immediate
refresh force 
on demand
start WITH SYSDATE next TRUNC(SYSDATE,'dd')+1+20/24
as
select nvl(INDUSTRIESALIAS, 'M') HANG_YE,'ZJH' FEN_LEI, B.S_INFO_WINDCODE SCDM
          from ASHAREINDUSTRIESCODE A,(select max(SEC_IND_CODE) SEC_IND_CODE,S_INFO_WINDCODE
          from (select substr(SEC_IND_CODE, 0, 4) SEC_IND_CODE,
                       substr(S_INFO_WINDCODE,
                              1,
                              instr(S_INFO_WINDCODE, '.') - 1) S_INFO_WINDCODE
                  from ASHARESECINDUSTRIESCLASS
                 where CUR_SIGN = '1') group by S_INFO_WINDCODE) B
         where used = 1
           and LEVELNUM = '2' and  INDUSTRIESCODE like B.SEC_IND_CODE||'%';
-- 创建物化视图索引
CREATE INDEX INDEX_VW_HANG_YE ON pr_vw_par_hang_ye(SCDM) tablespace tbs_perform_idx;