ORACLE游标的用法
-- 声明游标；CURSOR cursor_name IS select_statement--For 循环游标--（1）定义游标--（2）定义游标变量--（3）使用for循环来使用这个游标DECLARE--类型定义CURSOR C_JOB IS SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE JOB = 'MANAGER';--定义一个游标变量v_cinfo c_emp%ROWTYPE ，该类型为游标c_emp中的一行数据类型C_ROW C_JOB%ROWTYPE;BEGINFOR C_ROW IN C_JOB LOOP DBMS_OUTPUT.PUT_LINE(C_ROW.EMPNO || '-' || C_ROW.ENAME || '-' || C_ROW.JOB || '-' || C_ROW.SAL);END LOOP;END;--Fetch游标--使用的时候必须要明确的打开和关闭DECLARE--类型定义CURSOR C_JOB IS SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE JOB = 'MANAGER';--定义一个游标变量C_ROW C_JOB%ROWTYPE;BEGINOPEN C_JOB;LOOP --提取一行数据到c_row FETCH C_JOB INTO C_ROW; --判读是否提取到值，没取到值就退出 --取到值c_job%notfound 是false --取不到值c_job%notfound 是true EXIT WHEN C_JOB%NOTFOUND; DBMS_OUTPUT.PUT_LINE(C_ROW.EMPNO || '-' || C_ROW.ENAME || '-' || C_ROW.JOB || '-' || C_ROW.SAL);END LOOP;--关闭游标CLOSE C_JOB;END;--1：任意执行一个update操作，用隐式游标sql的属性%found,%notfound,%rowcount,%isopen观察update语句的执行情况。BEGINUPDATE EMP SET ENAME = 'ALEARK' WHERE EMPNO = 7469;IF SQL%ISOPEN THEN DBMS_OUTPUT.PUT_LINE('Openging');ELSE DBMS_OUTPUT.PUT_LINE('closing');END IF;IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE('游标指向了有效行'); --判断游标是否指向有效行ELSE DBMS_OUTPUT.PUT_LINE('Sorry');END IF;IF SQL%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE('Also Sorry');ELSE DBMS_OUTPUT.PUT_LINE('Haha');END IF;DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);EXCEPTIONWHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Sorry No data');WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('Too Many rows');END;DECLAREEMPNUMBER EMP.EMPNO%TYPE;EMPNAME EMP.ENAME%TYPE;BEGINIF SQL%ISOPEN THEN DBMS_OUTPUT.PUT_LINE('Cursor is opinging');ELSE DBMS_OUTPUT.PUT_LINE('Cursor is Close');END IF;IF SQL%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE('No Value');ELSE DBMS_OUTPUT.PUT_LINE(EMPNUMBER);END IF;DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);DBMS_OUTPUT.PUT_LINE('-------------');SELECT EMPNO, ENAME INTO EMPNUMBER, EMPNAME FROM EMP WHERE EMPNO = 7499;DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);IF SQL%ISOPEN THEN DBMS_OUTPUT.PUT_LINE('Cursor is opinging');ELSE DBMS_OUTPUT.PUT_LINE('Cursor is Closing');END IF;IF SQL%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE('No Value');ELSE DBMS_OUTPUT.PUT_LINE(EMPNUMBER);END IF;EXCEPTIONWHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No Value');WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('too many rows');END;--2,使用游标和loop循环来显示所有部门的名称--游标声明DECLARECURSOR CSR_DEPT IS--select语句 SELECT DNAME FROM DEPTH;--指定行指针,这句话应该是指定和csr_dept行类型相同的变量ROW_DEPT CSR_DEPT%ROWTYPE;BEGIN--for循环FOR ROW_DEPT IN CSR_DEPT LOOP DBMS_OUTPUT.PUT_LINE('部门名称:' || ROW_DEPT.DNAME);END LOOP;END;--3,使用游标和while循环来显示所有部门的的地理位置（用%found属性）DECLARE--游标声明CURSOR CSR_TESTWHILE IS--select语句 SELECT LOC FROM DEPTH;--指定行指针ROW_LOC CSR_TESTWHILE%ROWTYPE;BEGIN--打开游标OPEN CSR_TESTWHILE;--给第一行喂数据FETCH CSR_TESTWHILE INTO ROW_LOC;--测试是否有数据，并执行循环WHILE CSR_TESTWHILE%FOUND LOOP DBMS_OUTPUT.PUT_LINE('部门地点：' || ROW_LOC.LOC); --给下一行喂数据 FETCH CSR_TESTWHILE INTO ROW_LOC;END LOOP;CLOSE CSR_TESTWHILE;END;SELECT *FROM EMP --4,接收用户输入的部门编号，用for循环和游标，打印出此部门的所有雇员的所有信息(使用循环游标) --CURSOR cursor_name[(parameter[,parameter],...)] IS select_statement; --定义参数的语法如下:Parameter_name data_type[{:=|DEFAULT} value] DECLARE CURSOR C_DEPT(P_DEPTNO NUMBER) IS SELECT * FROM EMP WHERE EMP.DEPNO = P_DEPTNO;R_EMP EMP%ROWTYPE;BEGINFOR R_EMP IN C_DEPT(20) LOOP DBMS_OUTPUT.PUT_LINE('员工号：' || R_EMP.EMPNO || '员工名：' || R_EMP.ENAME || '工资：' || R_EMP.SAL);END LOOP;END;SELECT *FROM EMP --5：向游标传递一个工种，显示此工种的所有雇员的所有信息(使用参数游标) DECLARE CURSOR C_JOB(P_JOB NVARCHAR2) IS SELECT * FROM EMP WHERE JOB = P_JOB;R_JOB EMP%ROWTYPE;BEGINFOR R_JOB IN C_JOB('CLERK') LOOP DBMS_OUTPUT.PUT_LINE('员工号' || R_JOB.EMPNO || ' ' || '员工姓名' || R_JOB.ENAME);END LOOP;END;SELECT *FROM EMP --6：用更新游标来为雇员加佣金：(用if实现,创建一个与emp表一摸一样的emp1表，对emp1表进行修改操作),并将更新前后的数据输出出来 --http://zheng12tian.iteye.com/blog/815770 CREATE TABLE EMP1 AS SELECT * FROM EMP;DECLARECURSOR CSR_UPDATE IS SELECT * FROM EMP1 FOR UPDATE OF SAL;EMPINFOCSR_UPDATE%ROWTYPE;SALEINFO EMP1.SAL%TYPE;BEGINFOR EMPINFO IN CSR_UPDATE LOOP IF EMPINFO.SAL < 1500 THEN SALEINFO := EMPINFO.SAL * 1.2; ELSIF EMPINFO.SAL < 2000 THEN SALEINFO := EMPINFO.SAL * 1.5; ELSIF EMPINFO.SAL < 3000 THEN SALEINFO := EMPINFO.SAL * 2; END IF; UPDATE EMP1 SET SAL = SALEINFO WHERE CURRENT OF CSR_UPDATE;END LOOP;END;--7:编写一个PL/SQL程序块，对名字以‘A’或‘S’开始的所有雇员按他们的基本薪水(sal)的10%给他们加薪(对emp1表进行修改操作)DECLARECURSOR CSR_ADDSAL IS SELECT * FROM EMP1 WHERE ENAME LIKE 'A%' OR ENAME LIKE 'S%' FOR UPDATE OF SAL;R_ADDSAL CSR_ADDSAL%ROWTYPE;SALEINFO EMP1.SAL%TYPE;BEGINFOR R_ADDSAL IN CSR_ADDSAL LOOP DBMS_OUTPUT.PUT_LINE(R_ADDSAL.ENAME || '原来的工资:' || R_ADDSAL.SAL); SALEINFO := R_ADDSAL.SAL * 1.1; UPDATE EMP1 SET SAL = SALEINFO WHERE CURRENT OF CSR_ADDSAL;END LOOP;END;--8：编写一个PL/SQL程序块，对所有的salesman增加佣金(comm)500DECLARECURSOR CSR_ADDCOMM(P_JOB NVARCHAR2) IS SELECT * FROM EMP1 WHERE JOB = P_JOB FOR UPDATE OF COMM;R_ADDCOMM EMP1%ROWTYPE;COMMINFOEMP1.COMM%TYPE;BEGINFOR R_ADDCOMM IN CSR_ADDCOMM('SALESMAN') LOOP COMMINFO := R_ADDCOMM.COMM + 500; UPDATE EMP1 SET COMM = COMMINFO WHERE CURRENT OF CSR_ADDCOMM;END LOOP;END;--9：编写一个PL/SQL程序块，以提升2个资格最老的职员为MANAGER（工作时间越长，资格越老）--（提示：可以定义一个变量作为计数器控制游标只提取两条数据；也可以在声明游标的时候把雇员中资格最老的两个人查出来放到游标中。）DECLARECURSOR CRS_TESTCOMPUT IS SELECT * FROM EMP1 ORDER BY HIREDATE ASC;--计数器TOP_TWO NUMBER := 2;R_TESTCOMPUT CRS_TESTCOMPUT%ROWTYPE;BEGINOPEN CRS_TESTCOMPUT;FETCH CRS_TESTCOMPUT INTO R_TESTCOMPUT;WHILE TOP_TWO > 0 LOOP DBMS_OUTPUT.PUT_LINE('员工姓名：' || R_TESTCOMPUT.ENAME || ' 工作时间：' || R_TESTCOMPUT.HIREDATE); --计速器减一 TOP_TWO := TOP_TWO - 1; FETCH CRS_TESTCOMPUT INTO R_TESTCOMPUT;END LOOP;CLOSE CRS_TESTCOMPUT;END;--10：编写一个PL/SQL程序块，对所有雇员按他们的基本薪水(sal)的20%为他们加薪，--如果增加的薪水大于300就取消加薪(对emp1表进行修改操作，并将更新前后的数据输出出来) DECLARECURSOR CRS_UPADATESAL IS SELECT * FROM EMP1 FOR UPDATE OF SAL;R_UPDATESAL CRS_UPADATESAL%ROWTYPE;SALADD EMP1.SAL%TYPE;SALINFO EMP1.SAL%TYPE;BEGINFOR R_UPDATESAL IN CRS_UPADATESAL LOOP SALADD := R_UPDATESAL.SAL * 0.2; IF SALADD > 300 THEN SALINFO := R_UPDATESAL.SAL; DBMS_OUTPUT.PUT_LINE(R_UPDATESAL.ENAME || ':加薪失败。' || '薪水维持在：' || R_UPDATESAL.SAL); ELSE SALINFO := R_UPDATESAL.SAL + SALADD; DBMS_OUTPUT.PUT_LINE(R_UPDATESAL.ENAME || ':加薪成功.' || '薪水变为：' || SALINFO); END IF; UPDATE EMP1 SET SAL = SALINFO WHERE CURRENT OF CRS_UPADATESAL;END LOOP;END;--11:将每位员工工作了多少年零多少月零多少天输出出来 --近似--CEIL(n)函数：取大于等于数值n的最小整数--FLOOR(n)函数：取小于等于数值n的最大整数--truc的用法 http://publish.it168.com/2005/1028/20051028034101.shtmlDECLARECURSOR CRS_WORKDAY IS SELECT ENAME, HIREDATE, TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE) / 12) AS SPANDYEARS, TRUNC(MOD(MONTHS_BETWEEN(SYSDATE, HIREDATE), 12)) AS MONTHS, TRUNC(MOD(MOD(SYSDATE - HIREDATE, 365), 12)) AS DAYS FROM EMP1;R_WORKDAY CRS_WORKDAY%ROWTYPE;BEGINFOR R_WORKDAY IN CRS_WORKDAY LOOP DBMS_OUTPUT.PUT_LINE(R_WORKDAY.ENAME || '已经工作了' || R_WORKDAY.SPANDYEARS || '年,零' || R_WORKDAY.MONTHS || '月,零' || R_WORKDAY.DAYS || '天');END LOOP;END;--12:输入部门编号，按照下列加薪比例执行(用CASE实现，创建一个emp1表，修改emp1表的数据),并将更新前后的数据输出出来--deptnoraise(%)--10 5%--20 10%--30 15%--40 20%--加薪比例以现有的sal为标准--CASE expr WHEN comparison_expr THEN return_expr--[, WHEN comparison_expr THEN return_expr]... ENDDECLARECURSOR CRS_CASETEST IS SELECT * FROM EMP1 FOR UPDATE OF SAL;R_CASETEST CRS_CASETEST%ROWTYPE;SALINFO EMP1.SAL%TYPE;BEGINFOR R_CASETEST IN CRS_CASETEST LOOP CASE WHEN R_CASETEST.DEPNO = 10 THEN SALINFO := R_CASETEST.SAL * 1.05; WHEN R_CASETEST.DEPNO = 20 THEN SALINFO := R_CASETEST.SAL * 1.1; WHEN R_CASETEST.DEPNO = 30 THEN SALINFO := R_CASETEST.SAL * 1.15; WHEN R_CASETEST.DEPNO = 40 THEN SALINFO := R_CASETEST.SAL * 1.2; END CASE; UPDATE EMP1 SET SAL = SALINFO WHERE CURRENT OF CRS_CASETEST;END LOOP;END;--13:对每位员工的薪水进行判断，如果该员工薪水高于其所在部门的平均薪水，则将其薪水减50元，输出更新前后的薪水，员工姓名，所在部门编号。--AVG( expr) over (analytic_clause)---作用：--按照analytic_clause中的规则求分组平均值。--分析函数语法:--FUNCTION_NAME(<argument>,<argument>...)--OVER--(<Partition-Clause><Order-by-Clause><Windowing Clause>)--PARTITION子句--按照表达式分区(就是分组),如果省略了分区子句,则全部的结果集被看作是一个单一的组SELECT *FROM EMP1 DECLARE CURSOR CRS_TESTAVG IS SELECT EMPNO, ENAME, JOB, SAL, DEPNO, AVG(SAL) OVER(PARTITION BY DEPNO) AS DEP_AVG FROM EMP1 FOR UPDATE OF SAL;R_TESTAVG CRS_TESTAVG%ROWTYPE;SALINFO EMP1.SAL%TYPE;BEGINFOR R_TESTAVG IN CRS_TESTAVG LOOP IF R_TESTAVG.SAL > R_TESTAVG.DEP_AVG THEN SALINFO := R_TESTAVG.SAL - 50; END IF; UPDATE EMP1 SET SAL = SALINFO WHERE CURRENT OF CRS_TESTAVG;END LOOP;END;
页:
[1]