亚洲免费在线-亚洲免费在线播放-亚洲免费在线观看-亚洲免费在线观看视频-亚洲免费在线看-亚洲免费在线视频

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

系統 2093 0

游標:
隱式游標:%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 游標和觸發器


更多文章、技術交流、商務合作、聯系博主

微信掃碼或搜索:z360901061

微信掃一掃加我為好友

QQ號聯系: 360901061

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。

【本文對您有幫助就好】

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描上面二維碼支持博主2元、5元、10元、自定義金額等您想捐的金額吧,站長會非常 感謝您的哦?。。?/p>

發表我的評論
最新評論 總共0條評論
主站蜘蛛池模板: 欧美毛片又粗又长又大 | 青青久操视频 | 国产中文字幕一区 | 狠狠的日视频 | 天天干狠狠操 | 免费在线看h | 天天躁夜夜躁狂狂躁综合 | 欧美一级二级aaa免费视频 | 午夜精品福利视频 | 亚洲成人7777 | 国产精品久久久久久久成人午夜 | 亚洲欧美综合网 | 久久r8这里只精品99re66 | 色综合色综合色综合色综合网 | 精品亚洲一区二区三区在线播放 | 国产一级视频在线观看 | 久久久久久久国产高清 | 六月成人网 | 欧美国产精品一区二区免费 | 免费看欧美理论片在线 | 欧美视频色| 一级黄色免费毛片 | 91久久线看在观草草青青 | 清纯唯美亚洲综合日韩第 | 国产日韩精品一区二区 | 天天操夜夜添 | 亚洲一级毛片视频 | 手机看片福利日韩国产 | 欧美日韩在线观看区一二 | 久久久亚洲欧洲国产 | 99高清免费国产自产拍 | 天天舔天天射 | 女人18一级特级毛片免费看 | 四虎成人精品 | 久久亚洲精品国产亚洲老地址 | 欧美人一级淫片a免费播放 欧美人与zoxxxx另类9 | 99热热久久这里只有精品166 | 欧美亚洲精品小说一区二三区 | 激情国产视频 | www精品久久 | 国产91在线chines看 |