SELECT [DISTICT|ALL]{*|column[,column,...]} INTO (variable[,variable,...] |record) FROM {table|(sub-query)}[alias] WHERE............ |
v_empno SCOTT.EMP.EMPNO%TYPE; v_salary EMP.SALARY%TYPE; |
DELCARE V_A NUMBER(5):=10; V_B V_A%TYPE:=15; V_C V_A%TYPE; BEGIN DBMS_OUTPUT.PUT_LINE ('V_A='||V_A||'V_B='||V_B||'V_C='||V_C); END SQL>/ V_A=10 V_B=15 V_C= PL/SQL procedure successfully completed. SQL> |
CREATE OR REPLACE PROCEDURE FIRE_EMPLOYEE (pempno in number) AS v_ename EMP.ENAME%TYPE; BEGIN SELECT ename INTO v_ename FROM emp WHERE empno=p_empno; INSERT INTO FORMER_EMP(EMPNO,ENAME) VALUES (p_empno,v_ename); DELETE FROM emp WHERE empno=p_empno; UPDATE former_emp SET date_deleted=SYSDATE WHERE empno=p_empno; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Employee Number Not Found!'); END |
隱式游標 | 顯式游標 |
PL/SQL維護,當執(zhí)行查詢時自動打開和關(guān)閉 | 在程序中顯式定義、打開、關(guān)閉,游標有一個名字。 |
游標屬性前綴是SQL | 游標屬性的前綴是游標名 |
屬性%ISOPEN總是為FALSE | %ISOPEN根據(jù)游標的狀態(tài)確定值 |
SELECT語句帶有INTO子串,只有一行數(shù)據(jù)被處理 | 可以處理多行數(shù)據(jù),在程序中設(shè)置循環(huán),取出每一行數(shù)據(jù)。 |
CURSOR cursor_name IS select_statement; |
DELCARE CURSOR C_EMP IS SELECT empno,ename,salary FROM emp WHERE salary>2000 ORDER BY ename; ........ BEGIN |
OPEN cursor_name |
OPEN C_EMP; |
CLOSE cursor_name |
CLOSE C_EMP; |
FETCH cursor_name INTO variable[,variable,...] |
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 |
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; |
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; |
FOR record_name IN (corsor_name[(parameter[,parameter]...)] | (query_difinition) LOOP statements END LOOP; |
DECALRE CURSOR c_dept IS SELECT deptno,dname FROM dept ORDER BY deptno; CURSOR c_emp (p_dept VARACHAR2) IS SELECT ename,salary FROM emp WHERE deptno=p_dept ORDER BY ename v_tot_salary EMP.SALARY%TYPE; BEGIN FOR r_dept IN c_dept LOOP DBMS_OUTPUT.PUT_LINE('Department:'|| r_dept.deptno||'-'||r_dept.dname); v_tot_salary:=0; FOR r_emp IN c_emp(r_dept.deptno) LOOP DBMS_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; |
DECALRE v_tot_salary EMP.SALARY%TYPE; BEGIN FOR r_dept IN (SELECT deptno,dname FROM dept ORDER BY deptno) LOOP DBMS_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) LOOP DBMS_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 emp WHERE deptno NOT IN (SELECT deptno FROM dept WHERE dname!='ACCOUNTING'); |
FOR UPDATE [OF [schema.]table.column[,[schema.]table.column].. [nowait] |
WHERE{CURRENT OF cursor_name|search_condition} |
DELCARE CURSOR c1 IS SELECT empno,salary FROM emp WHERE comm IS NULL FOR UPDATE OF comm; v_comm NUMBER(10,2); BEGIN FOR r1 IN c1 LOOP IF r1.salary<500 THEN v_comm:=r1.salary*0.25; ELSEIF r1.salary<1000 THEN v_comm:=r1.salary*0.20; ELSEIF r1.salary<3000 THEN v_comm:=r1.salary*0.15; ELSE v_comm:=r1.salary*0.12; END IF; UPDATE emp; SET comm=v_comm WHERE CURRENT OF c1l; END LOOP; END |
煤炭網(wǎng)版權(quán)與免責聲明:
凡本網(wǎng)注明"來源:煤炭網(wǎng)m.shanesbookmarks.com "的所有文字、圖片和音視頻稿件,版權(quán)均為"煤炭網(wǎng)m.shanesbookmarks.com "獨家所有,任何媒體、網(wǎng)站或個人在轉(zhuǎn)載使用時必須注明"來源:煤炭網(wǎng)m.shanesbookmarks.com ",違反者本網(wǎng)將依法追究責任。
本網(wǎng)轉(zhuǎn)載并注明其他來源的稿件,是本著為讀者傳遞更多信息的目的,并不意味著本網(wǎng)贊同其觀點或證實其內(nèi)容的真實性。其他媒體、網(wǎng)站或個人從本網(wǎng)轉(zhuǎn)載使用時,必須保留本網(wǎng)注明的稿件來源,禁止擅自篡改稿件來源,并自負版權(quán)等法律責任。違反者本網(wǎng)也將依法追究責任。 如本網(wǎng)轉(zhuǎn)載稿件涉及版權(quán)等問題,請作者在兩周內(nèi)盡快來電或來函聯(lián)系。
網(wǎng)站技術(shù)運營:北京真石數(shù)字科技股份有限公司、喀什中煤遠大供應鏈管理有限公司、喀什煤網(wǎng)數(shù)字科技有限公司
總部地址:北京市豐臺區(qū)總部基地航豐路中航榮豐1層
京ICP備18023690號-1 京公網(wǎng)安備 11010602010109號