--如何用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 :=