Oracle常用语句 .
来源:互联网 发布:删除存储过程 sql 编辑:程序博客网 时间:2024/06/05 20:05
--如何用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常用SQL语句
- oracle常用SQL语句
- oracle常用SQL语句
- 常用Oracle语句
- oracle 常用sql语句
- oracle常用sql语句
- Oracle常用语句(不断更新)
- oracle常用SQL语句
- Oracle常用SQL语句
- oracle 常用语句
- oracle常用SQL语句
- oracle常用语句
- oracle常用语句
- Oracle sql 常用语句
- oracle常用SQL语句
- 常用oracle sql语句
- oracle常用SQL语句
- oracle常用sql语句
- windows 2008下服务无法安装的解决方式
- mysql导入sql文件错误#1044 - Access denied for user 'root'@'localhost'
- [垃圾microsoft, 要啥缺啥] c# metro app keydown issue
- 创业是不归路
- tomcat启动报错,以下是错误日志
- Oracle常用语句 .
- OpenCV 2.4.3 Cheat Sheet学习
- JAVA-this
- 你想干嘛啊
- linux 进程间通信
- marquee 滚动
- 宣布与 NBC 合作直播索契冬季奥运
- Oracle必备语句 .
- Python字符串操作