ORACLE基础实例

1.创建函数模板
CREATE OR REPLACE FUNCTION splitstr(p_string IN VARCHAR2, p_delimiter IN VARCHAR2)
    RETURN str_split
    PIPELINED
AS
    v_length   NUMBER := LENGTH(p_string);
    v_start    NUMBER := 1;
    v_index    NUMBER;
BEGIN
    WHILE(v_start <= v_length)
    LOOP
        v_index := INSTR(p_string, p_delimiter, v_start);

        IF v_index = 0
        THEN
            PIPE ROW(SUBSTR(p_string, v_start));
            v_start := v_length + 1;
        ELSE
            PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
            v_start := v_index + 1;
        END IF;
    END LOOP;

    RETURN;
END splitstr;

 

2.存储过程模板
CREATE OR REPLACE PROCEDURE Pro_Demo(biz_date varchar2,
                                             in_date  varchar2,
                                             out_code out varchar2,
                                             out_msg  out varchar2) is
  V_SQL      VARCHAR2(4000);
  V_TEMP_SQL VARCHAR2(4000);
  V_COUNT    INTEGER;
  V_PROC_NAME     VARCHAR2(50) := 'Pro_Demo';
  V_TARGET_SCHEMA VARCHAR2(50) := 'Error_Demo';
  V_ERR_MSG       VARCHAR2(4000) := ' ';
  V_ERR_SQL       VARCHAR2(200) := ' ';
  V_OWNER         VARCHAR2(20) := 'SYWG_BASE';

begin
  EXECUTE IMMEDIATE 'TRUNCATE TABLE T_TEST';  

  
  FOR S_DEL_EVENT_D IN (select *
                              from T_TEST
                             where event_flag = '02') LOOP
    DELETE FROM T_TEST
     where T_TEST.Event_Id = S_DEL_EVENT_D.Event_Id
       and T_TEST.Department_Id = S_DEL_EVENT_D.Department_Id;
  END LOOP;
  COMMIT;

  out_code := 0;
  out_msg  := '存储过程执行成功！';

  --异常处理
EXCEPTION
  WHEN OTHERS THEN
    V_ERR_MSG := '系统错误：SQLCODE=' || SQLCODE || ',SQLSTATE=' || SQLERRM ||
                 '  数据日期:';
    BEGIN
      ROLLBACK;
   
      INSERT INTO PROC_ERROR_MESSAGES
        (PROC_NAME, TARGET_SCHEMA, ERR_MSG, ERR_SQL, RUN_DATE)
      VALUES
        (V_PROC_NAME, V_TARGET_SCHEMA, V_ERR_MSG, V_ERR_SQL, SYSDATE);
      COMMIT;
   
      out_code := 1;
      out_msg  := '存储过程执行失败！';
   
      COMMIT;
      RETURN;
   
    END;
end Pro_Demo;

3.赋权模板
grant select on T_TEST to hothead007;

4.同义词模板
create  synonym  T_TEST  for CRMII.T_TEST;

5.视图模板
CREATE OR REPLACE VIEW T_TEST_VIEW AS
SELECT T.EVENT_FLOW_ID,
       T.EVENT_ID,
       T.EVENT_NAME；

6.表以及约束条件
create table T_PL_EVENT_MANAGE
(
  seq_no           NUMBER(19) not null,
  department_id    VARCHAR2(30),
   push_start_time  VARCHAR2(2),
  push_end_time    VARCHAR2(2)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints
alter table T_PL_EVENT_MANAGE
  add constraint PK_T_PL_EVENT_MANAGE primary key (SEQ_NO)
  using index
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table T_PL_EVENT_MANAGE
  add constraint UK_T_PL_EVENT_MANAGE unique (DEPARTMENT_ID, EVENT_ID)
  using index
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

7.创创建表空间
create tablespace HOTHEAD007_TBS 
logging 
datafile '/oracle/motdata/hothead007.dbf' size 1000m  autoextend on  next 200m
extent management local segment space management auto;

8.oralce环境数据库数据迁移
例如：以下是通过从已部署过mot的仿真环境，将基础数据源导入到新部署的oracle环境
1、从orac导出hothead007用户dmp数据源文件。
exp 用户名/密码@orac owner=hothead007 file=exp_hothead007.dmp log=exp_hothead007.log buffer=6000000
2、导入hothead007用户dmp数据文件至新部署的oracle环境。
imp 用户名/密码@jjrxd205 fromuser=hothead007 touser=hothead007 file=exp_hothead007.dmp log=imp_hothead007.log ignore=y
说明：导入数据库文件后，如果执行SQL缺少权限，则需要赋予相应的执行权限

 

8.分支语句

if ... then
...  
elsif ... then
...  

else
...  
end if;  
   

or    
if ... then
　　...  
else
...  
end if;  
   
or    
if ... then
...  
end if;

注：if后的条件不加括号

例子如下：
           if   p_fh <> ''   then
               p_strsql := p_strsql || ' and b.fh = p_fh';
           end if;