drop table student;
create table student
(
stuNo int primary key,
Name varchar2(10),
address varchar2(30),
birthday date
);
insert into student values(1,'梅超风','山东',to_date('1860-02-12 12:12:23','YYYY-MM-DD HH24:MI:SS'));
insert into student values(2,'陆成风','山西',to_date('1860-2-12','YYYY-MM-DD'));
insert into student values(3,'冯默风','安徽','10-2月-1886');
insert into student values(4,'曲灵风','湖南常德',to_date('1870-02-12 12:12:23','YYYY-MM-DD HH24:MI:SS'));
Rem ===================================================================
Rem 隐式游标
Rem ===================================================================
SET SERVEROUTPUT ON;
BEGIN
UPDATE student SET address='河南洛阳'
WHERE stuNo = 1;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('表已更新');
END IF;
END;
/
BEGIN
UPDATE student SET address=replace(address,'南','北')
WHERE address LIKE '%南%';
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('未找到数据........');
ELSE
DBMS_OUTPUT.PUT_LINE('影响了'||SQL%ROWCOUNT||'行');
END IF;
END;
/
Rem ===================================================================
Rem 显式游标
Rem ===================================================================
--使用游标显示name列的值
DECLARE
l_name VARCHAR2(20);
CURSOR stu_name_cur IS SELECT name from student;
BEGIN
OPEN stu_name_cur; --打开游标
LOOP
FETCH stu_name_cur INTO l_name;
DBMS_OUTPUT.PUT_LINE(stu_name_cur%ROWCOUNT);
EXIT WHEN stu_name_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('姓名:'||l_name);
END LOOP;
CLOSE stu_name_cur; --关闭游标
END;
/
--带参数的游标
DECLARE
l_name VARCHAR2(20);
l_stuNO NUMBER(2);
CURSOR stu_name_cur(sNo NUMBER)
IS SELECT name from student
WHERE stuNo>sNo;
BEGIN
l_stuNO:=&stuNO;
OPEN stu_name_cur(l_stuNO); --打开游标
LOOP
FETCH stu_name_cur INTO l_name;
EXIT WHEN stu_name_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('姓名:'||l_name);
END LOOP;
CLOSE stu_name_cur; --关闭游标
END;
/
Rem ===================================================================
Rem 使用显示游标修改数据
Rem ===================================================================
DECLARE
l_birthday DATE;
l_stuNO NUMBER(2);
CURSOR stu_name_cur(sNo NUMBER)
IS SELECT birthday from student
WHERE stuNo>sNo FOR UPDATE OF birthday;
BEGIN
l_stuNO:=&stuNO;
OPEN stu_name_cur(l_stuNO); --打开游标
LOOP
FETCH stu_name_cur INTO l_birthday;
EXIT WHEN stu_name_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('出生年月:'||to_char(l_birthday,'yyyy"年"mm"月"dd"日"'));
UPDATE student SET birthday=ADD_MONTHS(l_birthday,2)
WHERE CURRENT OF stu_name_cur; --更新当前行
END LOOP;
CLOSE stu_name_cur; --关闭游标
COMMIT;
END;
/
Rem ===================================================================
Rem 使用显示游标删除记录
Rem ===================================================================
DECLARE
l_stu_rec student%ROWTYPE;
CURSOR del_cur IS select * from student FOR UPDATE;
BEGIN
OPEN del_cur;
LOOP
FETCH del_cur into l_stu_rec;
EXIT WHEN del_cur%NOTFOUND;
IF l_stu_rec.stuno=2 THEN
DELETE FROM student WHERE CURRENT OF del_cur; --删除当前行
END IF;
END LOOP;
END;
/
Rem ===================================================================
Rem 循环游标
Rem ===================================================================
DECLARE
CURSOR stu_cur IS
SELECT stuNo,name,address FROM student;
BEGIN
FOR stu IN stu_cur --不要加分号
LOOP
DBMS_OUTPUT.PUT_LINE(stu.stuNO||' '||stu.name||' '||stu.address);
END LOOP;
END;
/
Rem ===================================================================
Rem 弱类型REF游标
Rem ===================================================================
DECLARE
sNo student.stuNo%type;
sName student.name%type;
sAddress student.address%type;
TYPE stu_cur_ref IS REF CURSOR; --声明REF游标类型
stu_cur stu_cur_ref; --声明“stu_cur_ref”游标类型变量
BEGIN
OPEN stu_cur FOR
SELECT stuNo,name,address FROM student;
LOOP
FETCH stu_cur INTO sNo,sName,sAddress;
EXIT WHEN stu_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(sNo||' '||sName||' '||sAddress);
END LOOP;
CLOSE stu_cur;
END;
/
DECLARE
l_stuno student.stuno%TYPE;
l_name student.name%TYPE;
TYPE stu_cur_ref IS REF CURSOR;
curStu stu_cur_ref;
BEGIN
OPEN curStu FOR
SELECT stuno,name FROM student;
LOOP
FETCH curStu INTO l_stuno,l_name;
EXIT WHEN curStu%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(l_stuno||' '||l_name);
END LOOP;
CLOSE curStu;
END;
/
Rem ===================================================================
Rem 强类型REF游标
Rem ===================================================================
DECLARE
TYPE l_stu_type IS RECORD( --自定义记录类型
sNo student.stuNo%type,
sName student.name%type,
sAddress student.address%type
);
l_stu l_stu_type; --声明自定义类型变量
TYPE stu_cur_ref IS REF CURSOR --声明REF游标类型
RETURN l_stu_type; --返回类型为自定义类型
stu_cur stu_cur_ref; --声明“stu_cur_ref”游标类型变量
BEGIN
OPEN stu_cur FOR
SELECT stuNo,name,address FROM student;
LOOP
FETCH stu_cur INTO l_stu;
EXIT WHEN stu_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(l_stu.sNo||' '||
l_stu.sName||' '||l_stu.sAddress);
END LOOP;
CLOSE stu_cur;
END;
/
DECLARE
TYPE l_stu_type IS RECORD(
sno student.stuno%TYPE,
sname student.name%TYPE
);
l_stu l_stu_type;
TYPE stu_cur_ref IS REF CURSOR RETURN l_stu_type;
stu_cur stu_cur_ref;
BEGIN
OPEN stu_cur FOR
SELECT stuno,name FROM student;
LOOP
FETCH stu_cur INTO l_stu;
EXIT WHEN stu_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(l_stu.sno||' '||l_stu.sname);
END LOOP;
CLOSE stu_cur;
END;
/
Rem ===================================================================
Rem 使用游标变量执行动态 SQL
Rem ===================================================================
DECLARE
l_stu student%ROWTYPE;
TYPE stu_cur_ref IS REF CURSOR; --声明REF游标类型
stu_cur stu_cur_ref; --声明“stu_cur_ref”游标类型变量
l_stuNo NUMBER(2);
BEGIN
l_stuNo := &sNo;
OPEN stu_cur FOR
'SELECT * FROM student WHERE stuNo>:1'
USING l_stuNo; --绑定参数
LOOP
FETCH stu_cur INTO l_stu;
EXIT WHEN stu_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(l_stu.stuNo||' '||l_stu.name||' '||l_stu.address||
' '||l_stu.birthday);
END LOOP;
CLOSE stu_cur;
END;
/
游标语法
最新推荐文章于 2024-06-01 21:18:13 发布