游標：
隱式游標：%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;來回收沒用的空間.

Oracle學習筆記 5 游標和觸發器