----------------------游标(CURSOR)-------------------
--游标分为:显示游标和隐式游标
-----隐式游标针对非查询语句(INSERT/UPDATE/DELETE)
-----显式游标针对查询语句。显式游标分为静态游标和动态游标。
--------静态游标是指定义游标的时候就确定好SQL语句,SQL紧跟游标后面;
--------动态游标是在定义又表示没有确定好SQL语句,在执行语句中定义。
------静态游标结构
DECLARE
CURSOR 游标名 已知SQL语句;
游标变量名 游标名%ROWTYPE;
BEGIN
执行语句(FOR循环、loop循环、WHILE 循环)
END;
-----静态游标实例详解
---for循环
e1:
DECLARE
CURSOR my_cur IS SELECT * FROM emp WHERE deptno=10;
ca my_cur%ROWTYPE;
BEGIN
FOR ca IN my_cur LOOP
dbms_output.put_line(ca.ename);
END LOOP;
END;
e2:
DECLARE
CURSOR my_cur1 IS SELECT * FROM emp WHERE sal>3000;
ca1 my_cur1%ROWTYPE;
BEGIN
FOR ca1 IN my_cur1 LOOP
dbms_output.put_line(ca1.empno);
dbms_output.put_line(ca1.ename);
dbms_output.put_line(ca1.job);
dbms_output.put_line(ca1.mgr);
dbms_output.put_line(ca1.deptno);
dbms_output.put_line(ca1.sal);
dbms_output.put_line(ca1.hiredate);
END LOOP;
END;
e3:---参数游标
DECLARE
CURSOR cc(eno emp.empno%TYPE) IS SELECT * FROM emp WHERE eno=deptno;
aa cc%ROWTYPE;
BEGIN
FOR aa IN cc(30) LOOP
dbms_output.put_line(aa.hiredate);
END LOOP;
END;
----while loop//while循环比较复杂。
DECLARE
CURSOR my_cur IS SELECT * FROM emp WHERE deptno=10;
ca my_cur%ROWTYPE;
BEGIN
OPEN my_cur;
FETCH my_cur INTO ca ;---将第一行的值赋值给ca
WHILE my_cur%FOUND LOOP
dbms_output.put_line(ca.ename);
FETCH my_cur INTO ca ;---游标下移(必不可失)
END LOOP;
END;
---loop 循环
格式:OPEN 游标
LOOP
循环内容
END LOOP;
CLOSE 游标
--无参数循环
DECLARE
CURSOR my_cur2 IS select* FROM emp WHERE deptno=10;
ca1 my_cur2%ROWTYPE;
BEGIN
OPEN my_cur2;
LOOP
FETCH my_cur2 INTO ca1;
EXIT WHEN my_cur2%NOTFOUND;
dbms_output.put_line(ca1.ename);
dbms_output.put_line(ca1.sal);
END LOOP;
CLOSE my_cur2;
END;
--带参数循环
DECLARE
CURSOR my_cur2(mno NUMBER) IS select* FROM emp WHERE deptno=mno;
ca1 my_cur2%ROWTYPE;
BEGIN
OPEN my_cur2(10);--//参数必须加载到此处
LOOP
FETCH my_cur2 INTO ca1;
EXIT WHEN my_cur2%NOTFOUND;
dbms_output.put_line(ca1.ename);
dbms_output.put_line(ca1.sal);
END LOOP;
CLOSE my_cur2;
END;
--游标分为:显示游标和隐式游标
-----隐式游标针对非查询语句(INSERT/UPDATE/DELETE)
-----显式游标针对查询语句。显式游标分为静态游标和动态游标。
--------静态游标是指定义游标的时候就确定好SQL语句,SQL紧跟游标后面;
--------动态游标是在定义又表示没有确定好SQL语句,在执行语句中定义。
------静态游标结构
DECLARE
CURSOR 游标名 已知SQL语句;
游标变量名 游标名%ROWTYPE;
BEGIN
执行语句(FOR循环、loop循环、WHILE 循环)
END;
-----静态游标实例详解
---for循环
e1:
DECLARE
CURSOR my_cur IS SELECT * FROM emp WHERE deptno=10;
ca my_cur%ROWTYPE;
BEGIN
FOR ca IN my_cur LOOP
dbms_output.put_line(ca.ename);
END LOOP;
END;
e2:
DECLARE
CURSOR my_cur1 IS SELECT * FROM emp WHERE sal>3000;
ca1 my_cur1%ROWTYPE;
BEGIN
FOR ca1 IN my_cur1 LOOP
dbms_output.put_line(ca1.empno);
dbms_output.put_line(ca1.ename);
dbms_output.put_line(ca1.job);
dbms_output.put_line(ca1.mgr);
dbms_output.put_line(ca1.deptno);
dbms_output.put_line(ca1.sal);
dbms_output.put_line(ca1.hiredate);
END LOOP;
END;
e3:---参数游标
DECLARE
CURSOR cc(eno emp.empno%TYPE) IS SELECT * FROM emp WHERE eno=deptno;
aa cc%ROWTYPE;
BEGIN
FOR aa IN cc(30) LOOP
dbms_output.put_line(aa.hiredate);
END LOOP;
END;
----while loop//while循环比较复杂。
DECLARE
CURSOR my_cur IS SELECT * FROM emp WHERE deptno=10;
ca my_cur%ROWTYPE;
BEGIN
OPEN my_cur;
FETCH my_cur INTO ca ;---将第一行的值赋值给ca
WHILE my_cur%FOUND LOOP
dbms_output.put_line(ca.ename);
FETCH my_cur INTO ca ;---游标下移(必不可失)
END LOOP;
END;
---loop 循环
格式:OPEN 游标
LOOP
循环内容
END LOOP;
CLOSE 游标
--无参数循环
DECLARE
CURSOR my_cur2 IS select* FROM emp WHERE deptno=10;
ca1 my_cur2%ROWTYPE;
BEGIN
OPEN my_cur2;
LOOP
FETCH my_cur2 INTO ca1;
EXIT WHEN my_cur2%NOTFOUND;
dbms_output.put_line(ca1.ename);
dbms_output.put_line(ca1.sal);
END LOOP;
CLOSE my_cur2;
END;
--带参数循环
DECLARE
CURSOR my_cur2(mno NUMBER) IS select* FROM emp WHERE deptno=mno;
ca1 my_cur2%ROWTYPE;
BEGIN
OPEN my_cur2(10);--//参数必须加载到此处
LOOP
FETCH my_cur2 INTO ca1;
EXIT WHEN my_cur2%NOTFOUND;
dbms_output.put_line(ca1.ename);
dbms_output.put_line(ca1.sal);
END LOOP;
CLOSE my_cur2;
END;