游標：
隱式游標：%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('數(shù)據(jù)已經(jīng)更新');
?????? --? dbms_output.put_line('rowCount = '||mrowcount);
else
? dbms_output.put_line('數(shù)據(jù)沒有找到');
end if;
end;
/
以下代碼演示了創(chuàng)建了一個游標，返回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;
/


以下代碼演示了創(chuàng)建了一個游標，返回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;
/
以下代碼演示了帶參數(shù)的游標，根據(jù)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; /
觸發(fā)器：
觸發(fā)器是當(dāng)特定事件出現(xiàn)時自動執(zhí)行的存儲過程
特定事件可以是執(zhí)行更新的DML語句和DDL語句
觸發(fā)器不能被顯式調(diào)用
觸發(fā)器的功能：
自動生成數(shù)據(jù)
自定義復(fù)雜的安全權(quán)限
提供審計和日志記錄
啟用復(fù)雜的業(yè)務(wù)邏輯
創(chuàng)建觸發(fā)器語法：
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;

創(chuàng)建觸發(fā)器，以下代碼演示了插入或者修改 employees2 表中的first_name 如果等于 ‘scott’時觸發(fā)器就會執(zhí)行：

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;
執(zhí)行以上觸發(fā)器：
insert into employees2 values(38,'SUP','WOR','scott','mp',50000);
或者：
update employees2 set salary=90000,first_name='scott' where employee_id=38;





以下代碼針對數(shù)據(jù)完整性進行操作：
? 刪除操作：
???????? 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;
/
執(zhí)行以上觸發(fā)器：
?? 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;
/
?? 執(zhí)行以上觸發(fā)器：
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;
/
執(zhí)行以上觸發(fā)器：
update dept set id=8 where id=1;
查看employee 表中的 deptid 記錄


以下代碼演示了行級觸發(fā)器：
創(chuàng)建表：
drop table rowtable;
????? create? table rowtable (id number(8) , name varchar2(100));
創(chuàng)建序列
??????? create sequence rowtablesequence;
創(chuàng)建觸發(fā)器：
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;
/
執(zhí)行SQL語句：
???? insert into rowtable values(232,'scott');
以下代碼演示了語句級觸發(fā)器:
創(chuàng)建表：
create table mylog(curr_user varchar2(100),curr_date date,opera varchar2(10));
創(chuàng)建觸發(fā)
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 觸發(fā)器
?? INSTEAD OF 觸發(fā)器是在視圖上而不是在表上定義的觸發(fā)器，它是用來替換所使用實際語句的觸發(fā)器。
?? 以下代碼創(chuàng)建了視圖：
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;
以下代碼創(chuàng)建 INSTEAD OF 觸發(fā)器。
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;
/
執(zhí)行以下語句查看操作：
??? insert into employee_job values('OTH',43,'abc','dd','OTHER');


模式觸發(fā)器：可以在模式級的操作上建立觸發(fā)器，如：create ,alter,drop,grant,revoke 和truncate 等 DDL語句：
以下示例對用戶所刪除的所有對象進行日志記錄。
1． 創(chuàng)建數(shù)據(jù)庫表：
? drop table dropped_obj;
???? CREATE TABLE dropped_obj
(
? obj_name VARCHAR2(30),
? obj_type VARCHAR2(20),
? drop_date DATE
);
??? 2．創(chuàng)建觸發(fā)器：
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．創(chuàng)建和刪除對象：
????????? 創(chuàng)建對象：CREATE? TABLE? for_drop ( x CHAR );
????????? 刪除對象：DROP? TABLE? for_drop;
4．查看日志表中的信息：
???????? SELECT? *? FROM? dropped_obj;



起用和禁用觸發(fā)器：
?? 以下代碼演示了禁用biu_emp_deptno 觸發(fā)器：
???????? ALTER TRIGGER biu_emp_deptno DISABLE;
?? 以下代碼演示了啟用biu_emp_deptno 觸發(fā)器：??
???????? ALTER TRIGGER biu_emp_deptno enable;

可以使用：
??????? Alter table table_name{enable | disable} all triggers;
禁用或者起用在特定表上建立的所有觸發(fā)器。

刪除觸發(fā)器：
??????? Drop trigger trigger_name;

查看觸發(fā)器信息，可以使用user_trigers 數(shù)據(jù)字典視圖。
Desc user_triggers



在使用delete語句刪除數(shù)據(jù)時，數(shù)據(jù)庫是要做日志記錄的，以便將來可以恢復(fù)數(shù)據(jù)，可是我在刪除上百萬條數(shù)據(jù)時，十分緩慢甚至死機，請問有沒有什么好方法?
　　網(wǎng)友觀點一:
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;
　　網(wǎng)友觀點二:
這個是我平常用來批量刪除數(shù)據(jù)，每500條數(shù)據(jù)提交一次。
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. 如果刪除的數(shù)據(jù)是大部分,建議使用樓上的方法把要保留的數(shù)據(jù)放在一個臨時表里,truncate table后再放回來
　　2. 也可以分段提交,樓上也提到了
　　3. 專門使用一個大回滾段
　　4. 如果確認將來不需要做恢復(fù),改為非歸檔模式,刪除完改回來再做個備份.
　　專家給出的解決方案:
有條件的分步刪除數(shù)據(jù)表中的記錄
--創(chuàng)建測試表
create table test as select * from dba_objects;
Table created.
--創(chuàng)建刪除表的存儲過程
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;
--創(chuàng)建刪除的存儲過程
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;
? /
--執(zhí)行語句
SQL> exec deleteTab('TEST','object_id >0','10000')



oracle上有條件的百萬條數(shù)據(jù)刪除方法
myrtle 發(fā)表于 2006-5-12 1:42:00

1、建一個臨時表，把要保存的紀錄插入到臨時表，TRUNCATE原表，然后把臨時表中的數(shù)據(jù)倒回。?? (在一個不帶索引的表中插入幾百萬紀錄其實是很快的，費時以分鐘計。如果有索引，先禁用索引或者刪除索引，裝載完后重建）
2、用exp/imp，先用exp???? query='滿足保存條件"備份。然后truncate表，然后將備份文件imp回去
3、對于分區(qū)表可以直接truncate partition 或者drop partition
alter table tablename truncate partition partitionname;
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
土方法：（影響刪除的要素是：rollback segment size, log, index for where statments)
? 在其它機器上建一個臨時用的數(shù)據(jù)庫；??
? 把要刪除的表導(dǎo)出來，再導(dǎo)入臨時用的數(shù)據(jù)庫，在臨時數(shù)據(jù)庫中作以下操作：??
? 0.?? 根據(jù)delete?? 的條件建立合適的索引，刪除其它沒有用的索引和約束；??
? 1.?? 寫一個過程刪除若干條數(shù)據(jù)后提交；（?? 要點在于批量提交可減少rooloback segment的需求以及加快速度）
? 2.?? 刪除完成后，刪掉表的索引和約束關(guān)系；??
? 3.?? 導(dǎo)入原數(shù)據(jù)庫；??
? 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萬條數(shù)據(jù)插入!
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可能需要重建索引，存儲過程、觸發(fā)器等
(查詢表相關(guān)索引：
select index_name,index_type,table_name,table_type, UNIQUENESS from dba_indexes where table_name='T_CUSTOMER_LOG';
查詢表相關(guān)的觸發(fā)器:
查詢表相關(guān)的約束(因此，比較好用truncate，不要用drop，引起太多相關(guān)處理的麻煩。但如果倒入數(shù)據(jù)量很大時，是可以考慮這種操作的。)
注意使能觸發(fā)器!
*將目標表設(shè)成nologging, 將目標表上的索引,約束,觸發(fā)器
先禁用, 然后使用直接路徑方式插入數(shù)據(jù), 可使用
set timing on來測試時間
注意: 這種方式操作過程中如果發(fā)生錯誤，將不能恢復(fù).
如果數(shù)據(jù)庫已經(jīng)是archive log 模式,設(shè)置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;
清除執(zhí)行delete后的空間
delete與truncate都是把表的的數(shù)據(jù)清空.但它們是有區(qū)別的.
DELETE 把數(shù)據(jù)清除后可以rollback,但TRUNCATE不可ROLLBACK.
DELETE 是屬DML ,TRUNCATE是DDL.
DELETE 刪除數(shù)據(jù)后不會回收空間,即如果原來的table已占了10M,你刪除了2M的記錄,這個表公然還是占10M. TRUNCATE在清空數(shù)據(jù)后可以回收空間,即 high water mark會降下來.
TRUNCATE不激活任何DELETE TRIGGER.
PS:
在你用delete清除記錄后,可以用
alter table table_name deallocate unused;來回收沒用的空間.

Oracle學(xué)習(xí)筆記 5 游標和觸發(fā)器