游标：
隐式游标：%FOUND, %NOTFOUND ,%ROWCOUNT
1．%FOUND 用法，只有在DML 语句影响一行或者多行时，%FOUND 属性才返回 TRUE。下列示例演示了 %FOUND 的用法：
begin
update employees2 set first_name = first_name ||' t' where employee_id = 2;
if SQL%found then
dbms_output.put_line('数据已经更新');
-- dbms_output.put_line('rowCount = '||mrowcount);
else
dbms_output.put_line('数据没有找到');
end if;
end;
/
以下代码演示了创建了一个游标，返回employees2 表中 salary 大于300000 的记录，注意type 的使用：
declare
csalary employees2.salary%type;
cursor emp2_cursor is select salary from employees2 where salary >300000;
begin
open emp2_cursor ;
loop
fetch emp2_cursor into csalary;
exit when emp2_cursor%notfound;
dbms_output.put_line('csalary = '||csalary);
end loop;
end;
/
以下代码演示了创建了一个游标，返回employees2 表中 division_id=’SAL’ 的记录。
注意rowtype 的使用：
declare
cursor employee2_cursor is select * from employees2 where division_id='SAL';
myrecord employees2%rowtype;
begin
open employee2_cursor;
fetch employee2_cursor into myrecord;
while employee2_cursor%found loop
dbms_output.put_line('employee id ='||myrecord.employee_id);
dbms_output.put_line('first Name ='||myrecord.first_name);
dbms_output.put_line('last name ='||myrecord.last_name);
fetch employee2_cursor into myrecord;
end loop;
end;
/
以下代码演示了带参数的游标，根据division id 查询指定的记录：
declare
myrecord employees2%rowtype;
cursor emp_cursor(divisionid varchar2) is select * from employees2 where division_id =divisionid;
begin
open emp_cursor('&divisionid');
--loop
fetch emp_cursor into myrecord;
while emp_cursor%found loop
-- exit when emp_cursor%notfound;
dbms_output.put_line('employee id = '||myrecord.employee_id);
dbms_output.put_line('division id = ' ||myrecord.division_id);
dbms_output.put_line('first name = ' ||myrecord.first_name);
fetch emp_cursor into myrecord;
end loop;
close emp_cursor;
end;
/
以下代码演示了如何更新 employees2 表中的 first_name 字段：
set serveroutput on
declare
firstName varchar2(20);
cursor employees2_cursor is select first_name from employees2 where employee_id=1 for update of
first_name;
begin
open employees2_cursor;
loop
fetch employees2_cursor into firstName;
exit when employees2_cursor%notfound;
update employees2
set first_Name='jeff ' where current of employees2_cursor;
end loop;
close employees2_cursor;
commit;
end; /
触发器：
触发器是当特定事件出现时自动执行的存储过程
特定事件可以是执行更新的DML语句和DDL语句
触发器不能被显式调用
触发器的功能：
自动生成数据
自定义复杂的安全权限
提供审计和日志记录
启用复杂的业务逻辑
创建触发器语法：
CREATE [OR REPLACE] TRIGGER trigger_name
AFTER | BEFORE | INSTEAD OF
[INSERT] [[OR] UPDATE [OF column_list]]
[[OR] DELETE]
ON table_or_view_name
[REFERENCING {OLD [AS] old / NEW [AS] new}]
[FOR EACH ROW]
[WHEN (condition)]
pl/sql_block;
创建触发器，以下代码演示了插入或者修改 employees2 表中的first_name 如果等于 ‘scott’时触发器就会执行：
create or replace trigger tri_employees2
before insert or update of first_name
on employees2
referencing NEW as newdata OLD as olddata
for each row
when (newdata.first_name='scott')
begin
:newdata.salary :=20000;
dbms_output. _line('new.salary:' || :newdata.salary);
dbms_output.put_line('old.salary:' ||
lddata.salary);
end;
执行以上触发器：
insert into employees2 values(38,'SUP','WOR','scott','mp',50000);
或者：
update employees2 set salary=90000,first_name='scott' where employee_id=38;
以下代码针对数据完整性进行操作：
删除操作：
create or replace trigger del_deptid
after delete on dept
for each row
begin
delete from employee where deptid =
ld.id;//old.id表示主键表中的ID
end del_deptid;
/
执行以上触发器：
delete from dept where id=1; 查看employee 表中的 deptid 记录;
添加操作：
create or replace trigger insert_dept
after insert on dept
for each row
begin
insert into employee(id,name,deptid) values('6','chenmp',:new.id);
end;
/
执行以上触发器：
insert into dept values(6,'销售部门');
查看employee 表中的 deptid 记录
修改操作：
create or replace trigger update_dept
after update on dept
for each row
begin
update employee set deptid = :new.id where deptid =
ld.id;
end;
/
执行以上触发器：
update dept set id=8 where id=1;
查看employee 表中的 deptid 记录
以下代码演示了行级触发器：
创建表：
drop table rowtable;
create table rowtable (id number(8) , name varchar2(100));
创建序列
create sequence rowtablesequence;
创建触发器：
create or replace trigger set_sequence
before insert on rowtable
for each row
declare
rsequence number(8);
begin
select rowtablesequence.nextval into rsequence from dual;
:NEW.id :=rsequence;
end;
/
执行SQL语句：
insert into rowtable values(232,'scott');
以下代码演示了语句级触发器:
创建表：
create table mylog(curr_user varchar2(100),curr_date date,opera varchar2(10));
创建触发
create or replace trigger tri_mylog
after insert or delete or update on employees2
begin
if inserting then
insert into mylog values(user,sysdate,'insert');
elsif deleting then
insert into mylog values(user,sysdate,'delete');
else
insert into mylog values(user,sysdate,'update');
end if;
end;
/
INSTEAD OF 触发器
INSTEAD OF 触发器是在视图上而不是在表上定义的触发器，它是用来替换所使用实际语句的触发器。
以下代码创建了视图：
create view employee_job as
select e.job_id,e.employee_id,e.first_name,e.last_name,j.name
from employees2 e,jobs j where e.job_id = j.job_id;
以下代码创建 INSTEAD OF 触发器。
create or replace trigger tri_view
instead of insert on employee_job
for each row
begin
insert into jobs values(:new.job_id,:new.name);
insert into employees2(employee_id,first_name,last_name,job_id) values(:new.employee_id,:new.first_name,:new.last_name,:new.job_id);
end;
/
执行以下语句查看操作：
insert into employee_job values('OTH',43,'abc','dd','OTHER');
模式触发器：可以在模式级的操作上建立触发器，如：create ,alter,drop,grant,revoke 和truncate 等 DDL语句：
以下示例对用户所删除的所有对象进行日志记录。
1． 创建数据库表：
drop table dropped_obj;
CREATE TABLE dropped_obj
(
obj_name VARCHAR2(30),
obj_type VARCHAR2(20),
drop_date DATE
);
2．创建触发器：
CREATE OR REPLACE TRIGGER log_drop_obj
AFTER DROP ON SCHEMA
BEGIN
INSERT INTO dropped_obj
VALUES (ORA_DICT_OBJ_NAME, ORA_DICT_OBJ_TYPE, SYSDATE);
END;
/
3．创建和删除对象：
创建对象：CREATE TABLE for_drop ( x CHAR );
删除对象：DROP TABLE for_drop;
4．查看日志表中的信息：
SELECT * FROM dropped_obj;
起用和禁用触发器：
以下代码演示了禁用biu_emp_deptno 触发器：
ALTER TRIGGER biu_emp_deptno DISABLE;
以下代码演示了启用biu_emp_deptno 触发器：
ALTER TRIGGER biu_emp_deptno enable;
可以使用：
Alter table table_name{enable | disable} all triggers;
禁用或者起用在特定表上建立的所有触发器。
删除触发器：
Drop trigger trigger_name;
查看触发器信息，可以使用user_trigers 数据字典视图。
Desc user_triggers
在使用delete语句删除数据时，数据库是要做日志记录的，以便将来可以恢复数据，可是我在删除上百万条数据时，十分缓慢甚至死机，请问有没有什么好方法?
网友观点一:
create or replace procedure delete_table
is
i number(10);
begin
for x in (select * from emp where DEPTNO like 'a%')
loop
delete emp where emp.id = x.id
i:=i+1;
if i>1000 then
commit;
i:=0;
end if;
end loop;
exception
when others then
dbms_out.put_line(SQLcode);
rollback;
end delete_table;
网友观点二:
这个是我平常用来批量删除数据，每500条数据提交一次。
DECLARE
CNT NUMBER(10):=0;
I NUMBER(10);
BEGIN
SELECT COUNT(*) INTO CNT FROM ep_arrearage_bak WHERE TO_CHAR(DF_DATE,'MM')='01';
FOR I IN 1..TRUNC(CNT/500)+1 LOOP
DELETE FROM ep_arrearage_bak WHERE TO_CHAR(DF_DATE,'MM')='01' AND ROWNUM<=500;
COMMIT;
END LOOP;
END;
专家意见:几个办法:
1. 如果删除的数据是大部分,建议使用楼上的方法把要保留的数据放在一个临时表里,truncate table后再放回来
2. 也可以分段提交,楼上也提到了
3. 专门使用一个大回滚段
4. 如果确认将来不需要做恢复,改为非归档模式,删除完改回来再做个备份.
专家给出的解决方案:
有条件的分步删除数据表中的记录
--创建测试表
create table test as select * from dba_objects;
Table created.
--创建删除表的存储过程
create or replace procedure deleteTab
--插入语句
SQL> insert into test select * from dba_objects;
6374 rows created.
SQL> /
6374 rows created.
SQL> /
6374 rows created.
SQL> commit;
--创建删除的存储过程
create or replace procedure deleteTab
/**
** Usage: run the script to create the proc deleteTab
** in SQL*PLUS, type "exec deleteTab('Foo','ID>=1000000','3000');"
** to delete the records in the table "Foo", commit per 3000 records.
** Condition with default value '1=1' and default Commit batch is 10000.
**/
(
p_TableName in varchar2, -- The TableName which you want to delete from
p_Condition in varchar2 default '1=1', -- Delete condition, such as "id>=100000"
p_Count in varchar2 default '10000' -- Commit after delete How many records
)
as
pragma autonomous_transaction;
n_delete number:=0;
begin
while 1=1 loop
EXECUTE IMMEDIATE
'delete from '||p_TableName||' where '||p_Condition||' and rownum <= :rn'
USING p_Count;
if SQL%NOTFOUND then
exit;
else
n_delete:=n_delete + SQL%ROWCOUNT;
end if;
commit;
end loop;
commit;
DBMS_OUTPUT.PUT_LINE('Finished!');
DBMS_OUTPUT.PUT_LINE('Totally '||to_char(n_delete)||' records deleted!');
end;
/
--执行语句
SQL> exec deleteTab('TEST','object_id >0','10000')
oracle上有条件的百万条数据删除方法
myrtle 发表于 2006-5-12 1:42:00
1、建一个临时表，把要保存的纪录插入到临时表，TRUNCATE原表，然后把临时表中的数据倒回。 (在一个不带索引的表中插入几百万纪录其实是很快的，费时以分钟计。如果有索引，先禁用索引或者删除索引，装载完后重建）
2、用exp/imp，先用exp query='满足保存条件"备份。然后truncate表，然后将备份文件imp回去
3、对于分区表可以直接truncate partition 或者drop partition
alter table tablename truncate partition partitionname;
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
土方法：（影响删除的要素是：rollback segment size, log, index for where statments)
在其它机器上建一个临时用的数据库；
把要删除的表导出来，再导入临时用的数据库，在临时数据库中作以下操作：
0. 根据delete 的条件建立合适的索引，删除其它没有用的索引和约束；
1. 写一个过程删除若干条数据后提交；（ 要点在于批量提交可减少rooloback segment的需求以及加快速度）
2. 删除完成后，删掉表的索引和约束关系；
3. 导入原数据库；
4. 重建索引和约束。
实例：
SQL> select count(1) from t_customer_log;
COUNT(1)
----------
25076317
有2500多万条记录
SQL> select count(1) from t_customer_Log where start_time>=20060312000000;
COUNT(1)
----------
9775788
有近1000
SQL> create table t_customer_log_t nologging as select * from t_customer_Log where start_time>=2006031200000;
（要注意nologging选项，可加 on tablespace XXX nologging指定表空间。)
9:54--10:06用了12分钟完成了1000万条数据插入!
drop table t_customer_log
alter table t_customer_log t rename to t_customer_log
注意：alter table t_customer_log_t rename t_customer_log可能需要重建索引，存储过程、触发器等
(查询表相关索引：
select index_name,index_type,table_name,table_type, UNIQUENESS from dba_indexes where table_name='T_CUSTOMER_LOG';
查询表相关的触发器:
查询表相关的约束(因此，比较好用truncate，不要用drop，引起太多相关处理的麻烦。但如果倒入数据量很大时，是可以考虑这种操作的。)
注意使能触发器!
*将目标表设成nologging, 将目标表上的索引,约束,触发器
先禁用, 然后使用直接路径方式插入数据, 可使用
set timing on来测试时间
注意: 这种方式操作过程中如果发生错误，将不能恢复.
如果数据库已经是archive log 模式,设置nologging 不起
作用.
)
也可用：
SQL> truncate table t_customer_Log
不到一分钟truncate!
SQL> insert into t_customer_Log select * from t_customer_Log_t;
SQL>drop table t_customer_Log_t;
清除执行delete后的空间
delete与truncate都是把表的的数据清空.但它们是有区别的.
DELETE 把数据清除后可以rollback,但TRUNCATE不可ROLLBACK.
DELETE 是属DML ,TRUNCATE是DDL.
DELETE 删除数据后不会回收空间,即如果原来的table已占了10M,你删除了2M的记录,这个表公然还是占10M. TRUNCATE在清空数据后可以回收空间,即 high water mark会降下来.
TRUNCATE不激活任何DELETE TRIGGER.
PS:
在你用delete清除记录后,可以用
alter table table_name deallocate unused;来回收没用的空间.
