oracle数据库开发笔记

1、创建job示例

DECLARE job NUMBER;begin  sys.dbms_job.submit(job => job,                      what => 'p_auto_assign_channel_task;',                      next_date => to_date(to_char(trunc(sysdate+1/24,'hh24'),'dd-mm-yyyy hh24:mi:ss'), 'dd-mm-yyyy hh24:mi:ss'),                      interval => 'sysdate+1/24');  commit;end;

2、创建视图示例

create or replace view tm_testview asselect "CONTENTID","CONTENTNAME","COMPANYID","COMPANYNAME" from S_V_TMS_CONTENT_TEST;

3、创建同义词示例

create or replace synonym V_TMS_BASE  for MM_PPMS_NEW.V_TMS_BASE_SELECT@DBLK_MMPORTAL_PPMSNEW;

4、创建DBLink示例

create database link DBLK_MMPORTAL_PPMSNEW  connect to MM_PPMS_NEW  using 'ppmsdb';

5、创建序列示例

create sequence SEQ_TM_MIGU_APPminvalue 1maxvalue 9999999999start with 1increment by 1cache 10;

6、创建表示例

create table tm_migu_app(  migu_app_id     NUMBER(11) not null,  data_type       VARCHAR2(12),  app_submit_date Date,  del_flag        CHAR(1) default '0');

7、创建主键

ALTER TABLE tm_migu_app ADD CONSTRAINT pk_tm_migu_app PRIMARY KEY (migu_app_id);

8、创建表、字段描述示例

comment on table tm_migu_app is '咪咕应用表';comment on column tm_migu_app.migu_app_id is '主键ID';

9、创建触发器示例

示例一：

create or replace trigger tri_tm_user  after update on TM_USER_20160112  for each rowbegin  if updating('password') then    insert into reset_password_log      (user_id, old_password, new_password, operate_date)    values      (:new.user_id,:old.password,:new.password,sysdate);  end if;end tri_tm_user;

示例二：

create or replace trigger tri_task_inserafter insert on tm_task for each rowdeclare  v_count number;begin    select count(1) into v_count from tm_test_item2 where test_item_id=:new.test_item_id;    if v_count = 0 then      insert into tm_test_item2      select b.* from tm_test_item b where  b.test_item_id=:new.test_item_id;    end if;end;

示例三：

create or replace trigger tri_task_statubefore update of status  on TM_TASK  for each rowwhen (new.status='5' or new.status='6')declare    pragma  autonomous_transaction;    v_pack_id   NUMBER(15);    v_ntotal    number;    begin     select   count(9) into v_ntotal  from tm_test_item  where task_id = :new.task_id and test_type='2' and rownum<2;end;

10、创建存储过程示例

示例一：

create or replace procedure p_bak_and_encode is  type user_record is record(       user_id      tm_user.user_id%type,       id_no        tm_user.id_no%type,       tel          tm_user.tel%type,       mobile       tm_user.mobile%type,       email        tm_user.email%type,       address      tm_user.address%type  );  type user_records is table of user_record;  v_user_record_list user_records;  v_tm_user_bak_num         number;  v_id_no                   varchar2(100);  --用户信息；  cursor c1 is  select t.user_id,t.id_no,t.tel,t.mobile,t.email,t.address from tm_user t order by t.user_id;begin  v_tm_user_bak_num := 1;  begin    select COUNT(1) into v_tm_user_bak_num from user_tables t where t.TABLE_NAME = 'TM_USER_FOR_BAK';  exception when others then    v_tm_user_bak_num := 1;    dbms_output.put_line('Get Table Num Error!');  end;  if(v_tm_user_bak_num =0) then    begin      --创建备份表并备份数据      execute immediate 'create table TM_USER_FOR_BAK as select user_id,id_no,tel,mobile,email,address from tm_user';      --开始对原表数据加密      open c1;      fetch c1 bulk collect            into v_user_record_list;      close c1;      if v_user_record_list.last > 0 then        for i in v_user_record_list.first .. v_user_record_list.last loop          v_id_no := '';          if v_user_record_list(i).id_no is not null then            v_id_no := utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(v_user_record_list(i).id_no)));          else            v_id_no := '';          end if;          update tm_user t set t.id_no = v_id_no where t.user_id = v_user_record_list(i).user_id;        end loop;      end if;    exception when others then      rollback;      dbms_output.put_line('Error!');    end;  else    dbms_output.put_line('Bak Table is exist!');  end if;end p_bak_and_encode;

示例二：

create or replace procedure test(v_test1 in varchar2,v_test2 out varchar2) isbegin  if v_test1 = '1' then    v_test2 := '11';  else    v_test2 := '22';  end if;end test;

11、创建函数示例

示例一：

create or replace function f_query_channel_assign_user(v_group_id varchar2)  return number is  v_result          number;  v_least_task_user varchar2(15);  v_user            tm_user.user_id%type;  v_saturation      number;begin  begin    select executor || '_' || saturation      into v_least_task_user      from (select t.executor,                   trunc(t.assign_task / c.mainsubft, 2) as saturation              from tm_user_capacity c,                   (select u.user_id as executor,                           (select count(k.task_id) as assign_task                              from tm_task k, tm_test_item i                             where k.test_item_id = i.test_item_id                               and i.test_type = '3'                               and k.is_actual_test_pkg != '0'                               and k.executor = u.user_id                               and k.plan_begin_date >= trunc(sysdate)                               and k.plan_begin_date < trunc(sysdate) + 1) as assign_task                      from tm_user u                     where instr(v_group_id,'|'||u.group_id||'|') > 0                       and u.status = '1'                       and u.del_flag = '0') t             where c.user_id = t.executor               and c.mainsubft > 0             order by saturation)     where rownum = 1;  exception    when no_data_found then      v_result := 9999999999;      return(v_result);  end;  v_user := to_number(substr(v_least_task_user,1,instr(v_least_task_user, '_') - 1));  v_saturation := to_number(substr(v_least_task_user,instr(v_least_task_user, '_') + 1,length(v_least_task_user)));  if v_saturation < 1 then    v_result := v_user;  else    v_result := 9999999999;  end if;  return(v_result);end f_query_channel_assign_user;

示例二（--根据指定分隔符，从指定字符串中取值，类似java的spit方法）：

--根据指定分隔符，从指定字符串中取值，类似java的spit()create or replace function Get_StrArrayStrOfIndex(  av_str varchar2,  --要分割的字符串  av_split varchar2,  --分隔符号  av_index number --取第几个元素)return varchar2is  lv_str varchar2(1024);  lv_strOfIndex varchar2(1024);  lv_length number;begin  lv_str:=ltrim(rtrim(av_str));  lv_str:=concat(lv_str,av_split);  lv_length:=av_index;  if lv_length=0 then      lv_strOfIndex:=substr(lv_str,1,instr(lv_str,av_split)-length(av_split));  else      lv_length:=av_index+1;     lv_strOfIndex:=substr(lv_str,instr(lv_str,av_split,1,av_index)+length(av_split),instr(lv_str,av_split,1,lv_length)-instr(lv_str,av_split,1,av_index)-length(av_split));  end if;  return  lv_strOfIndex;end Get_StrArrayStrOfIndex;

示例二使用示例：

select Get_StrArrayStrOfIndex('dsafs:2123:a2332',':',1) from dual;

示例三（获取指定字符串中根据分隔符分隔的个数）：

--获取指定字符串中根据分隔符分隔的个数create or replace function Get_StrArrayLength(  av_str varchar2,  --要分割的字符串  av_split varchar2  --分隔符号)return numberis  lv_str varchar2(1000);  lv_length number;begin  lv_str:=ltrim(rtrim(av_str));  lv_length:=0;  while instr(lv_str,av_split)<>0 loop     lv_length:=lv_length+1;     lv_str:=substr(lv_str,instr(lv_str,av_split)+length(av_split),length(lv_str));  end loop;  lv_length:=lv_length+1;  return lv_length;end Get_StrArrayLength;

示例三使用示例：

select Get_StrArrayLength('dsafs:2123:a2332',':') from dual;