发表于 2009-1-5 19:48
| 
Oracle鏁版嵁搴撴父鏍囦娇鐢ㄥぇ鍏(3)
鍙戝竷: 2008-11-30 16:20 | 浣滆: 缃戠粶杞浇 | 鏉ユ簮: 缃戠粶杞浇 | 鏌ョ湅: 17娆
1銆佹墦寮娓告爣2銆佸紑濮嬪惊鐜3銆佷粠娓告爣涓彇鍊4銆佹鏌ラ偅涓琛岃杩斿洖5銆佸鐞6銆佸叧闂惊鐜7銆佸叧闂父鏍囧彲浠ョ畝鍗曠殑鎶婅繖涓绫讳唬鐮佺О涓烘父鏍囩敤浜庡惊鐜備絾杩樻湁涓绉嶅惊鐜笌杩欑绫诲瀷涓嶇浉鍚岋紝杩欏氨鏄疐OR寰幆锛岀敤浜嶧OR寰幆鐨勬父鏍囨寜鐓ф甯哥殑澹版槑鏂瑰紡澹版槑锛屽畠鐨勪紭鐐瑰湪浜庝笉闇瑕佹樉寮忕殑鎵撳紑銆佸叧闂佸彇鏁版嵁锛屾祴璇曟暟鎹殑瀛樺湪銆佸畾涔夊瓨鏀炬暟鎹殑鍙橀噺绛夌瓑銆
娓告爣FOR 寰幆鐨勮娉曞涓嬶細
FOR record_name IN(corsor_name[(parameter[,parameter]...)]| (query_difinition)LOOPstatementsEND LOOP;
涓嬮潰鎴戜滑鐢╢or寰幆閲嶅啓涓婇潰鐨勪緥瀛愶細
DECALRECURSOR c_dept IS SELECT deptno,dname FROM dept ORDER BY deptno;CURSOR c_emp (p_dept VARACHAR2) ISSELECT ename,salaryFROM empWHERE deptno=p_deptORDER BY enamev_tot_salary EMP.SALARY%TYPE;BEGINFOR r_dept IN c_dept LOOPDBMS_OUTPUT.PUT_LINE(Department:|| r_dept.deptno||-||r_dept.dname);v_tot_salary:=0;FOR r_emp IN c_emp(r_dept.deptno) LOOPDBMS_OUTPUT.PUT_LINE(Name:|| v_ename|| salary:||v_salary);v_tot_salary:=v_tot_salary+v_salary;END LOOP;DBMS_OUTPUT.PUT_LINE(Toltal Salary for dept:|| v_tot_salary);END LOOP;END;
鍦ㄦ父鏍嘑OR寰幆涓娇鐢ㄦ煡璇㈠湪娓告爣FOR寰幆涓彲浠ュ畾涔夋煡璇紝鐢变簬娌℃湁鏄惧紡澹版槑鎵浠ユ父鏍囨病鏈夊悕瀛楋紝璁板綍鍚嶉氳繃娓告爣鏌ヨ鏉ュ畾涔夈
DECALRE銆v_tot_salary EMP.SALARY%TYPE;BEGIN銆FOR r_dept IN (SELECT deptno,dname FROM dept ORDER BY deptno) LOOPDBMS_OUTPUT.PUT_LINE(Department:|| r_dept.deptno||-||r_dept.dname);v_tot_salary:=0;FOR r_emp IN (SELECT ename,salary銆FROM emp銆WHERE deptno=p_dept銆ORDER BY ename) LOOPDBMS_OUTPUT.PUT_LINE(Name:|| v_ename|| salary:||v_salary);v_tot_salary:=v_tot_salary+v_salary;END LOOP;DBMS_OUTPUT.PUT_LINE(Toltal Salary for dept:|| v_tot_salary);銆END LOOP;END;
娓告爣涓殑瀛愭煡璇㈣娉曞涓嬶細
CURSOR C1 IS SELECT * FROM empWHERE deptno NOT IN (SELECT deptnoFROM deptWHERE dname!=ACCOUNTING);
鍙互鐪嬪嚭涓嶴QL涓殑瀛愭煡璇㈡病鏈変粈涔堝尯鍒傛父鏍囦腑鐨勬洿鏂板拰鍒犻櫎鍦≒L/SQL涓緷鐒跺彲浠ヤ娇鐢║PDATE鍜孌ELETE璇彞鏇存柊鎴栧垹闄ゆ暟鎹銆傛樉寮忔父鏍囧彧鏈夊湪闇瑕佽幏寰楀琛屾暟鎹殑鎯呭喌涓嬩娇鐢ㄣ侾L/SQL鎻愪緵浜嗕粎浠呬娇鐢ㄦ父鏍囧氨鍙互鎵ц鍒犻櫎鎴栨洿鏂拌褰曠殑鏂规硶銆
UPDATE鎴朌ELETE璇彞涓殑WHERE CURRENT OF瀛愪覆涓撻棬澶勭悊瑕佹墽琛孶PDATE鎴朌ELETE鎿嶄綔鐨勮〃涓彇鍑虹殑鏈杩戠殑鏁版嵁銆傝浣跨敤杩欎釜鏂规硶锛屽湪澹版槑娓告爣鏃跺繀椤讳娇鐢‵OR UPDATE瀛愪覆锛屽綋瀵硅瘽浣跨敤FOR UPDATE瀛愪覆鎵撳紑涓涓父鏍囨椂锛屾墍鏈夎繑鍥為泦涓殑鏁版嵁琛岄兘灏嗗浜庤绾э紙ROW-LEVEL)鐙崰寮忛攣瀹氾紝鍏朵粬瀵硅薄鍙兘鏌ヨ杩欎簺鏁版嵁琛岋紝涓嶈兘杩涜UPDATE銆丏ELETE鎴朣ELECT...FOR UPDATE鎿嶄綔銆傝娉曪細FOR UPDATE [OF [schema.]table.column[,[schema.]table.column]..[nowait]鍦ㄥ琛ㄦ煡璇腑锛屼娇鐢∣F瀛愬彞鏉ラ攣瀹氱壒瀹氱殑琛,濡傛灉蹇界暐浜哋F瀛愬彞锛岄偅涔堟墍鏈夎〃涓夋嫨鐨勬暟鎹閮藉皢琚攣瀹氥
濡傛灉杩欎簺鏁版嵁琛屽凡缁忚鍏朵粬浼氳瘽閿佸畾锛岄偅涔堟甯告儏鍐典笅ORACLE灏嗙瓑寰咃紝鐩村埌鏁版嵁琛岃В閿併傚湪UPDATE鍜孌ELETE涓娇鐢╓HERE CURRENT OF瀛愪覆鐨勮娉曞涓嬶細
WHERE{CURRENT OF cursor_name|search_condition}
渚嬶細DELCARECURSOR c1 IS SELECT empno,salaryFROM empWHERE comm IS NULLFOR UPDATE OF comm;v_comm NUMBER(10,2);BEGINFOR r1 IN c1 LOOPIF r1.salary<500 THENv_comm:=r1.salary*0.25;ELSEIF r1.salary<1000 THENv_comm:=r1.salary*0.20;ELSEIF r1.salary<3000 THENv_comm:=r1.salary*0.15;ELSEv_comm:=r1.salary*0.12;END IF;UPDATE emp;SET comm=v_commWHERE CURRENT OF c1l;END LOOP;END
