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;
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
