发表于 2009-1-5 19:48
| 
Oracle鏁版嵁搴撴父鏍囦娇鐢ㄥぇ鍏(2)
鍙戝竷: 2008-11-30 16:19 | 浣滆: 缃戠粶杞浇 | 鏉ユ簮: 缃戠粶杞浇 | 鏌ョ湅: 18娆
浠庢父鏍囧緱鍒颁竴琛屾暟鎹娇鐢‵ETCH鍛戒护銆傛瘡涓娆℃彁鍙栨暟鎹悗锛屾父鏍囬兘鎸囧悜缁撴灉闆嗙殑涓嬩竴琛屻傝娉曞涓嬶細
FETCH cursor_name INTO variable[,variable,...]
瀵逛簬SELECT瀹氫箟鐨勬父鏍囩殑姣忎竴鍒楋紝FETCH鍙橀噺鍒楄〃閮藉簲璇ユ湁涓涓彉閲忎笌涔嬬浉瀵瑰簲锛屽彉閲忕殑绫诲瀷涔熻鐩稿悓銆
渚嬶細
SET SERVERIUTPUT ON
DECLARE
v_ename EMP.ENAME%TYPE;
v_salary EMP.SALARY%TYPE;
CURSOR c_emp IS SELECT ename,salary FROM emp;
BEGIN
OPEN c_emp;
FETCH c_emp INTO v_ename,v_salary;
DBMS_OUTPUT.PUT_LINE(Salary of Employee|| v_ename
||is|| v_salary);
FETCH c_emp INTO v_ename,v_salary;
DBMS_OUTPUT.PUT_LINE(Salary of Employee|| v_ename
||is|| v_salary);
FETCH c_emp INTO v_ename,v_salary;
DBMS_OUTPUT.PUT_LINE(Salary of Employee|| v_ename
||is|| v_salary);
CLOSE c_emp;
END
杩欐浠ｇ爜鏃犵枒鏄潪甯搁夯鐑︾殑锛屽鏋滄湁澶氳杩斿洖缁撴灉锛屽彲浠ヤ娇鐢ㄥ惊鐜苟鐢ㄦ父鏍囧睘鎬т负缁撴潫寰幆鐨勬潯浠讹紝浠ヨ繖绉嶆柟寮忔彁鍙栨暟鎹紝绋嬪簭鐨勫彲璇绘у拰绠娲佹ч兘澶т负鎻愰珮锛屼笅闈㈡垜浠娇鐢ㄥ惊鐜噸鏂板啓涓婇潰鐨勭▼搴忥細
SET SERVERIUTPUT ON
DECLARE
v_ename EMP.ENAME%TYPE;
v_salary EMP.SALARY%TYPE;
CURSOR c_emp IS SELECT ename,salary FROM emp;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO v_ename,v_salary;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(Salary of Employee|| v_ename
||is|| v_salary);
END
璁板綍鍙橀噺
瀹氫箟涓涓褰曞彉閲忎娇鐢═YPE鍛戒护鍜%ROWTYPE锛屽叧浜%ROWsTYPE鐨勬洿澶氫俊鎭鍙傞槄鐩稿叧璧勬枡銆
璁板綍鍙橀噺鐢ㄤ簬浠庢父鏍囦腑鎻愬彇鏁版嵁琛岋紝褰撴父鏍囬夋嫨寰堝鍒楃殑鏃跺欙紝閭ｄ箞浣跨敤璁板綍姣斾负姣忓垪澹版槑涓涓彉閲忚鏂逛究寰楀銆
褰撳湪琛ㄤ笂浣跨敤%ROWTYPE骞跺皢浠庢父鏍囦腑鍙栧嚭鐨勫兼斁鍏ヨ褰曚腑鏃讹紝濡傛灉瑕侀夋嫨琛ㄤ腑鎵鏈夊垪锛岄偅涔堝湪SELECT瀛愬彞涓娇鐢*姣斿皢鎵鏈夊垪鍚嶅垪鍑烘潵瑕佸畨鍏ㄥ緱澶氥
渚嬶細
SET SERVERIUTPUT ON
DECLARE
R_emp EMP%ROWTYPE;
CURSOR c_emp IS SELECT * FROM emp;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO r_emp;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUT.PUT.PUT_LINE(Salary of Employee||r_emp.ename||is|| r_emp.salary);
END LOOP;
CLOSE c_emp;
END;
%ROWTYPE涔熷彲浠ョ敤娓告爣鍚嶆潵瀹氫箟锛岃繖鏍风殑璇濆氨蹇呴』瑕侀鍏堝０鏄庢父鏍囷細
SET SERVERIUTPUT ON
DECLARE
CURSOR c_emp IS SELECT ename,salary FROM emp;
R_emp c_emp%ROWTYPE;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO r_emp;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUT.PUT.PUT_LINE(Salary of Employee||r_emp.ename||is|| r_emp.salary);
END LOOP;
CLOSE c_emp;
END;
甯﹀弬鏁扮殑娓告爣
涓庡瓨鍌ㄨ繃绋嬪拰鍑芥暟鐩镐技锛屽彲浠ュ皢鍙傛暟浼犻掔粰娓告爣骞跺湪鏌ヨ涓娇鐢ㄣ傝繖瀵逛簬澶勭悊鍦ㄦ煇绉嶆潯浠朵笅鎵撳紑娓告爣鐨勬儏鍐甸潪甯告湁鐢ㄣ傚畠鐨勮娉曞涓嬶細
CURSOR cursor_name[(parameter[,parameter],...)] IS select_statement;
瀹氫箟鍙傛暟鐨勮娉曞涓嬶細
Parameter_name [IN] data_type[{:=|DEFAULT} value]
涓庡瓨鍌ㄨ繃绋嬩笉鍚岀殑鏄紝娓告爣鍙兘鎺ュ彈浼犻掔殑鍊硷紝鑰屼笉鑳借繑鍥炲笺傚弬鏁板彧瀹氫箟鏁版嵁绫诲瀷锛屾病鏈夊ぇ灏忋
鍙﹀鍙互缁欏弬鏁拌瀹氫竴涓己鐪佸硷紝褰撴病鏈夊弬鏁板间紶閫掔粰娓告爣鏃讹紝灏变娇鐢ㄧ己鐪佸笺傛父鏍囦腑瀹氫箟鐨勫弬鏁板彧鏄竴涓崰浣嶇锛屽湪鍒寮曠敤璇ュ弬鏁颁笉涓瀹氬彲闈犮
鍦ㄦ墦寮娓告爣鏃剁粰鍙傛暟璧嬪硷紝璇硶濡備笅锛
OPEN cursor_name[value[,value]....];
鍙傛暟鍊煎彲浠ユ槸鏂囧瓧鎴栧彉閲忋
渚嬶細
DECALRE
CURSOR c_dept IS SELECT * FROM dept ORDER BY deptno;
CURSOR c_emp (p_dept VARACHAR2) IS
SELECT ename,salary
FROM emp
WHERE deptno=p_dept
ORDER BY ename
r_dept DEPT%ROWTYPE;
v_ename EMP.ENAME%TYPE;
v_salary EMP.SALARY%TYPE;
v_tot_salary EMP.SALARY%TYPE;
BEGIN
OPEN c_dept;
LOOP
FETCH c_dept INTO r_dept;
EXIT WHEN c_dept%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(Department:|| r_dept.deptno||-||r_dept.dname);
v_tot_salary:=0;
OPEN c_emp(r_dept.deptno);
LOOP
FETCH c_emp INTO v_ename,v_salary;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(Name:|| v_ename|| salary:||v_salary);
v_tot_salary:=v_tot_salary+v_salary;
END LOOP;
CLOSE c_emp;
DBMS_OUTPUT.PUT_LINE(Toltal Salary for dept:|| v_tot_salary);
END LOOP;
CLOSE c_dept;
END;
