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, -- 产品代码，默认为INI_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 ISU_INDUSTRIESALIAS VARCHAR2(12); --行业版块，用于获取执行结果，当前的执行结果为单个值，所以使用 VARCHAR2 定义V_CLOB            VARCHAR2(2000);V_HANGYE_TYPE     VARCHAR2(20) DEFAULT 'ZJH';-- 定义变量，并设置默认值e_exception exception; -- 声明异常pragma exception_init(e_exception,-20018); -- 注册自定义的异常类型，需要写入一个异常代码，不要与数据库本身的异常代码冲突了BEGINbegin -- begin end 代码块SELECT APH_HY_CODEINTO V_HANGYE_TYPEFROM PR_PAR_ACTUAL_PORTFOLIO_HYWHERE APH_CODE = I_CPDM;exception -- 数据未找到的异常，如果select没有数据，into会报错的-- 如果设置了默认值，即使没有数据，也不会报错，会使用默认的数据when no_data_found thenV_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, -- 可以设置默认值，当前默认值为0O_MESSAGE OUT VARCHAR2) isE_FLAG INT DEFAULT 0;E_MESSAGE VARCHAR2(4000);               			cursor c_fund_code is -- 也可以在直接定义游标，查询所有产品信息select distinct vc_cpdm from vjk_wbfk_cpxx  bwhere 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);beginFOR C_CURSOR IN ( -- 直接使用 cursor进行循环取数据SELECT DISTINCT PRODUCT_CODE FROM dcetl.vw_out_df_perf_req WHERE REQ_TYPE = '01' AND RPT_FREQUENCY <> '0')LOOPEND 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，进行主动提交EXCEPTIONwhen others then-- 可以做一些其他操作end GET_CLASS_99_SHIZHI;
end PCK_ACHIEVEMENTS_A_C_S;

自定义 oracle 数据结构

create or replace type MARKET_VALUE_RECORD force IS object(--股票--》市值 RECORDEN_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) ISloopCount number(5);strlong   varchar2(2000); --单次输出的临时变量beginif '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_HISTpctfree 10initrans 1maxtrans 255storage(initial 80Knext 1Mminextents 1maxextents unlimitedpctincrease 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

DECLAREJOBNO 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_LINKconnect 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 AINDEXHS300WEIGHTfor 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 SCDMfrom ASHAREINDUSTRIESCODE A,(select max(SEC_IND_CODE) SEC_IND_CODE,S_INFO_WINDCODEfrom (select substr(SEC_IND_CODE, 0, 4) SEC_IND_CODE,substr(S_INFO_WINDCODE,1,instr(S_INFO_WINDCODE, '.') - 1) S_INFO_WINDCODEfrom ASHARESECINDUSTRIESCLASSwhere CUR_SIGN = '1') group by S_INFO_WINDCODE) Bwhere used = 1and 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;