Oracle常用语句

--如何用grade表的资料去更新usertable表的资料(有关联的字段userid)
 update usertable u set u.grade =
 (select g.grade from grade g where g.userid = u.userid);
 
--如何使查询结果字段生成序号
select rownum, t.* from sm_t_pad_new t

--如何快速做一个和原表一样的备份表
 create table new_table as (select * from user);

--如何查看数据文件的存放路径
select tablespace_name, file_id, bytes/1024/1024, file_name
from dba_data_files order by file_id;

 

--查询姓名相同的员工的信息
select u1.userid, u1.username from user u1,
(select username, count(*) from user group by username having count(username) > 1) u2
where u1.username = u2.username;

 

--根据时间查询

select * from user
where create_time >= to_date(‘2010-4-16 00:00:00‘,‘YYYY-MM-DD HH24:mi:ss‘)
and create_time <= to_date(‘2010-4-16 12:00:00‘,‘YYYY-MM-DD HH24:mi:ss‘)

 

-- 批量删除方法一
declare
v_temp number;
begin
  loop
    begin
      select 1 into v_temp from user
      where create_time <= to_date(‘2010-4-16 17:35:22‘,‘YYYY-MM-DD HH24:mi:ss‘) and rownum < = 1;
     
      delete from user
      where create_time <= to_date(‘2010-4-16 17:35:22‘,‘YYYY-MM-DD HH24:mi:ss‘) and rownum < = 2;
     
      commit;
      exception when no_data_found then exit;
    end;
  end loop;
end;

-- 批量删除方法二
declare
v_log_num number;  -- 数据库中拥有的日志文件数
v_archive number;  -- 需要归档的日志文件数
begin
  select count(1) into v_log_num from v$log;
  loop
    loop
      select count(1) into v_archive from v$archive;
      if v_archive < v_log_num - 1 then exit;
      else dbms_lock.sleep(60);
      end if;
    end loop;
    delete from user
    where create_time <= to_date(‘2010-4-16 17:39:44‘,‘YYYY-MM-DD HH24:mi:ss‘) and rownum < = 2;
    if sql%rowcount = 0 then exit;
    end if;
    commit;
  end loop;
end;

 

--批量删除方法三
declare
v_ids varchar2(4000);
v_id varchar(20);
v_char char;
begin
v_ids := ‘2121,2141‘;
v_char := ‘,‘;
while(length(v_ids) > 0)
  loop
  begin
    if(instr(v_ids, v_char) > 0)
      then v_id := substr(v_ids, 0, instr(v_ids, v_char) - 1);
      v_ids := substr(v_ids, instr(v_ids, v_char) + 1);
    else
      v_id := v_ids;
      v_ids := ‘‘;
    end if;
    delete from user where userId = v_id;
    --if sql%rowcount = 0 then exit;
    --end if;
    dbms_output.put_line(‘删除一条数据。‘);
  end;
  commit; 
  dbms_output.put_line(v_id);
  end loop; 
end; 

-- 批量删除的存储过程
create or replace procedure batchInsert(ids in varchar2, v_char in varchar2)
as
  v_ids varchar2(4000);
  v_id varchar2(20);
begin 
v_ids := ids;
while(length(v_ids) > 0)
  loop
    begin
      if(instr(v_ids, v_char) > 0) -- 在ids中搜索‘,‘, 返回发现‘,‘的位置,若不存在则返回0;
        then v_id := substr(v_ids, 1, instr(v_ids, v_char) - 1);
             v_ids := substr(v_ids, instr(v_ids, v_char) + 1);
      else
        v_id := v_ids;
        v_ids := ‘‘;
      end if;
      delete from user where userId = v_id;
      --if sql%rowcount = 0 then exit;
      --end if;
      dbms_output.put_line(‘删除一条数据。‘);
    end;
    commit;
    dbms_output.put_line(v_id);
  end loop; 
end batchInsert;

-- 执行存储过程
declare
v_ids varchar2(4000);
v_char varchar2(20);
begin
  v_ids := ‘2062,2081,2101‘;
  v_char := ‘,‘;
  batchInsert(v_ids, v_char);
end;

 

===============================================================

CREATE OR REPLACE PROCEDURE sm_p_sendNotice(
v_noticeId in varchar2,     --公告编号, 对应SM_T_NOTICE表的NOTICE_ID
v_unitId in varchar2,       --机构ID
v_title in varchar2,        --公告标题, 对应SM_T_NOTICE表的TITLE
v_model_id in VARCHAR2      --公告类别编号, 对应SM_T_NOTICE表的MODEL_ID
)
IS
v_notice_model_name NVARCHAR2(100);
BEGIN
    SELECT NOTICE_NAME INTO v_notice_model_name FROM sm_t_notice_model WHERE NOTICE_MODEL_ID = v_model_id;
    insert into sm_t_notice_newest(NOITCE_STAFF_ID, NOTICE_ID, STAFF_ID, TITLE, out_time, notice_model_name)
    (select SM_S_NOITCE_NEWEST_ID.Nextval, v_noticeId, t.staff_id, v_title, sysdate, v_notice_model_name from SM_t_STAFF t
    WHERE t.unit_id IN(SELECT unit_id FROM sm_t_unit START WITH unit_id = v_unitId CONNECT BY super_unit_ID = PRIOR unit_id));   
END sm_p_sendNotice;
--
DECLARE
v_noticeId varchar2(4000);
v_unitId varchar2(20);
v_title VARCHAR2(500);
v_model_id NVARCHAR2(100);
BEGIN
    v_noticeId := ‘1003‘;
    v_unitId := ‘01‘;
    v_title := ‘929555993应答处理口径‘;
    v_model_id := ‘28‘;
    sm_p_sendNotice(v_noticeId, v_unitId, v_title, v_model_id);
END;
--

 

--任务队列管理器

begin
  sys.dbms_job.submit(job => :job,
                      what => ‘sm_p_insertMessage;‘,
                      next_date => to_date(‘07-09-2010 02:30:00‘, ‘dd-mm-yyyy hh24:mi:ss‘),
                      interval => ‘TRUNC(SYSDATE + 1) + (2*60+30)/(24*60)‘);
  commit;
end;

 

--

oracle 创建表时判断表是否存在语句

declare  cnt number;

begin
   ---查询要创建的表是否存在
   select count(*)into cnt from user_tables where table_name=‘ENTRY_MODIFYSTATUS‘;
   ---如果存在则删除该表
   if cnt>0 then
      dbms_output.put_line(‘表存在不创建‘);
   else
       dbms_output.put_line(‘表不存在‘);
    execute immediate ‘create table ENTRY_MODIFYSTATUS  (
       ENTRY_ID             VARCHAR2(18)                    not null,
       APPLY_TIME           DATE                            not null,
       STATUS               NUMBER(2),
       constraint PK_ENTRY_MODIFYSTATUS primary key (ENTRY_ID, APPLY_TIME)
    )‘;
  end if;
 
  cnt:=0;
end;

 

==============================================

CREATE OR REPLACE PROCEDURE AP_T_RPT_PRO_LOGINCOUNT
AS
login_count NVARCHAR2(100);
week_login_count NVARCHAR2(100);
--date_time DATE;
now_time DATE;
flag CHAR(1);
BEGIN
SELECT TRUNC(SYSDATE) INTO now_time FROM dual;
--date_time := to_date(‘2010-6-7‘, ‘YYYY-MM-DD‘);
--WHILE(date_time < now_time-6)
--date_time := date_time + 7;
--SELECT d.ANALYSIS_FLAG INTO flag FROM ap_t_rpt_login_dict d
FOR A IN (SELECT start_time FROM ap_t_rpt_login_dict WHERE start_time < now_time-6)
LOOP
BEGIN
SELECT d.ANALYSIS_FLAG INTO flag FROM ap_t_rpt_login_dict d WHERE d.start_time = A.START_TIME;
IF(flag = ‘N‘)
THEN
    FOR C IN (SELECT u1.unit_id FROM sm_t_unit u1)
    LOOP
        SELECT COUNT(DISTINCT t.staff_id) INTO login_count FROM SM_t_login T
        WHERE t.unit_id = C.UNIT_ID
        AND t.login_time > add_months(A.START_TIME + 7,-2)
        AND t.login_time < A.START_TIME + 7;

        SELECT COUNT(DISTINCT t.staff_id) INTO week_login_count FROM SM_t_login T
        WHERE t.unit_id = C.UNIT_ID
        AND t.login_time > A.START_TIME
        AND t.login_time < A.START_TIME + 7;

        INSERT INTO ap_t_rpt_login (rpt_login_id, unit_id, start_time, user_count, week_login_user)
        VALUES(ap_s_rpt_login.nextval, C.UNIT_ID, A.START_TIME, login_count, week_login_count);

    END LOOP;
    UPDATE ap_t_rpt_login_dict SET ANALYSIS_FLAG = ‘Y‘ WHERE start_time = A.START_TIME;

END IF;
END;
END LOOP;

END;

Oracle常用语句,布布扣,bubuko.com

Oracle常用语句

上一篇:python批量下载图片


下一篇:数据库恢复:对page header的恢复