select * from scott.emp;
==============for 循环=======================
declare
empno NUMBER(4,0);
ename VARCHAR2(10 BYTE);
sal NUMBER(7,2);
begin
for REC in
(select empno, ename, sal from scott.emp)
loop
begin
empno := REC.empno;
ename := REC.ename;
sal := REC.sal;
/*---------------------------------写入一条明细开始------------------------------*/
if NVL(empno,0) <> 0 then
begin
dbms_output.put_line('记录: ' || empno || ' ' || ename || ' ' || sal );
end;
end if;
/*---------------------------------写入一条明细开始------------------------------*/
end;
end loop;
end;
/
--===============带参数的游标==================--
DECLARE
dept_code emp.deptno%TYPE; --声明列类型变量三个
emp_code emp.empno%TYPE;
emp_name emp.ename%TYPE;
CURSOR emp_cur(deptparam NUMBER) IS
SELECT empno, ename FROM EMP WHERE deptno = deptparam; --声明显示游标
BEGIN
dept_code := &部门编号; --请用户输入想查看的部门编号
OPEN emp_cur(dept_code); --打开游标
LOOP
--死循环
FETCH emp_cur
INTO emp_code, emp_name; --提取游标值赋给上面声明的变量
EXIT WHEN emp_cur%NOTFOUND; --如果游标里没有数据则退出循环
DBMS_OUTPUT.PUT_LINE(emp_code || '' || emp_name); --输出查询
END LOOP;
CLOSE emp_cur; --关闭游标
END;
--=================REF游标==================--
ACCEPT tab FROMPT '你想查看什么信息?员工(E)或部门信息(D):'; --使用ACCEPT命令弹出对话框让用户输入数据
DECLARE
TYPE refcur_t IS REF CURSOR; --声明REF游标类型
refcur refcur_t; --声明REF游标类型的变量
pid NUMBER;
p_name VARCHAR2(100);
selection VARCHAR2(1) := UPPER(SUBSTR('&tab', 1, 1)); --截取用户输入的字符串并转换为大写
BEGIN
IF selection = 'E' THEN
--如果输入的是'E',则打开refcurr游标,并将员工表查询出来赋值给此游标
OPEN refcur FOR
SELECT EMPNO ID, ENAME NAME FROM EMP;
DBMS_OUTPUT.PUT_LINE('=====员工信息=====');
ELSIF selection = 'D' THEN
--如果输入是'D',则打开部门表
OPEN refcur FOR
SELECT deptno id, dname name FROM DEPT;
DBMS_OUTPUT.PUT_LINE('=====部门信息======');
ELSE
--否则返回结束
DBMS_OUTPUT.PUT_LINE('请输入员工信息(E)或部门信息(D)');
RETURN;
END IF;
FETCH refcur
INTO pid, p_name; --提取行
WHILE refcur%FOUND LOOP
DBMS_OUTPUT.PUT_LINE('#' || pid || ':' || p_name);
FETCH refcur
INTO pid, p_name;
END LOOP;
CLOSE refcur; --关闭游标
END;
--===================动态SQL=================--
VARIABLE maxsal NUMBER; --声明变量
EXECUTE :maxsal := 2500; --执行引用并给变量赋值
DECLARE
r_emp EMP%ROWTYPE; --声明一个行类型变量
TYPE c_type IS REF CURSOR; --声明REF游标类型
cur c_type; --声明REF游标类型的变量
p_salary NUMBER; --声明一个标量变量
BEGIN
p_salary := :maxsal; --引用变量
--使用USING语句将引用到的值传给动态SQL语句'SAL >: 1'中的'1'
OPEN cur FOR 'SELECT * FROM EMP WHERE SAL >: 1 ORDER BY SAL DESC'
USING p_salary;
DBMS_OUTPUT.PUT_LINE('薪水大于' || p_salary || '的员工有:');
LOOP
FETCH cur
INTO r_emp;
EXIT WHEN cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('编号:' || r_emp.empno || '姓名:' || r_emp.ename ||
'薪水:' || r_emp.sal);
END LOOP;
CLOSE cur; --关闭游标
END;
-- 例子:
CREATE OR REPLACE PROCEDURE x_ne_change
AS
CURSOR cur_new
IS
SELECT int_id, omc_id || ':' || msc_id || ':' || bsc_id AS related_id,
omc_id, msc_id, bsc_id, ne_name, cell_id, lac, freq_band,
trx_count, tch, sdcch, gprs_enabled, gprs_trx, dedicated_pdch,
max_pdch, device_type, software_version, dumpfre_type, site_no,
cell_no, rac, ncc, bcc, sms_cb_used, bsc_omc_int_id, omc_int_id,
TIMESTAMP
FROM appuser.K_C_CELL
WHERE TIMESTAMP = '2004-04-23 8' AND cell_id < 2000;
CURSOR cur_old (c_no NUMBER)
IS
SELECT int_id, omc_id || ':' || msc_id || ':' || bsc_id AS related_id,
omc_id, msc_id, bsc_id, ne_name, cell_id, lac, freq_band,
trx_count, tch, sdcch, gprs_enabled, gprs_trx, dedicated_pdch,
max_pdch, device_type, software_version, dumpfre_type, site_no,
cell_no, rac, ncc, bcc, sms_cb_used, bsc_omc_int_id, omc_int_id,
TIMESTAMP
FROM appuser.K_C_CELL
WHERE TIMESTAMP = '2004-04-21 6' AND cell_id = c_no;
BEGIN
FOR v_new IN cur_new
LOOP
FOR v_old IN cur_old (v_new.cell_id)
LOOP
BEGIN
IF v_new.related_id <>; v_old.related_id
THEN
INSERT INTO TEST_NE_CHANGE
(omc_id, omc_int_id, ne_id,
old_value, now_value,
modify_item, modify_time
)
VALUES (v_new.omc_id, v_new.omc_int_id, v_new.cell_id,
v_old.related_id, v_new.related_id,
'related_id', v_new.TIMESTAMP
);
COMMIT;
END IF;
IF v_new.tch <>; v_old.tch
THEN
INSERT INTO TEST_NE_CHANGE
(omc_id, omc_int_id, ne_id,
old_value, now_value, modify_item, modify_time
)
VALUES (v_new.omc_id, v_new.omc_int_id, v_new.cell_id,
v_old.tch, v_new.tch, 'TCH', v_new.TIMESTAMP
);
COMMIT;
END IF;
END;
END LOOP;
END LOOP;
END;
--- 多行数据提取
declare
cursor cur_tsalary is
select employeeid,positionid from tsalary whererownum < 10;
type rec_tsalary isrecord(
employeeid tsalary.employeeid%type,
positionid tsalary.positionid%type);
type all_rec_tsalary_type istableof rec_tsalary;
all_rec_tsalary all_rec_tsalary_type;
begin
--一次处理所有
fetch cur_tsalary bulkcollectinto all_rec_tsalary limit5;
for i in1..all_rec_tsalary.countloop dbms_output.put_line(all_rec_tsalary(i).employeeid||all_rec_tsalary(i).positionid);
endloop;
--使用limit分批提出大量数据
open cur_tsalary;
loop
fetch cur_tsalary bulkcollectinto all_rec_tsalary limit5;
for i in1..all_rec_tsalary.countloop dbms_output.put_line(all_rec_tsalary(i).employeeid||all_rec_tsalary(i).positionid);
endloop;
exitwhen cur_tsalary%notfound;
endloop;
close cur_tsalary;
end;
-- 例六:%NotFound
BEGIN
DELETE FROM Rs_Employees
Where HeTongId = ' WL-090001 ' ;
if sql % Notfound then
Dbms_Output.put_line( ' 没有找到要删除的记录 ' );
else
Dbms_Output.put_line( ' 已删除记录 ' );
end if ;
END ;
-- 例七:%RowCount,查询记录行数
Declare
v_name Rs_Employees.Name % type;
BEGIN
SELECT Name Into v_Name
FROM Rs_Employees
Where HeTongId = ' WL-090010 ' ;
if sql % RowCount > 0 Then
Dbms_Output.put_line( ' 已从表中选择行,Name为: ' || v_Name);
else
Dbms_Output.put_line( ' 从表中未选择行 ' );
end if ;
END ;
-- 再演示以下代码
BEGIN
DELETE FROM Rs_Employees
Where HeTongId <= ' WL-090010 ' ;
Dbms_Output.put_line( ' 已从表中删除 ' || To_Char(sql % RowCount ) || ' 条记录 ' );
END ;
-- 例八:显式游标
-- 以下示例在所有游标的记录中的Name字段中加一字串
-- 同时在此示范了如何使用%NotFound属性
-- set serveroutput on;
Declare
v_Id Rs_Employees.Hetongid % type;
v_name Rs_Employees.Name % type;
v_Count Number : = 0 ;
Cursor MyCur Is
SELECT HetongId,Name FROM Rs_Employees
Where HeTongId <= ' WL-090010 ' ;
BEGIN
-- 打开游标
Open MyCur;
-- 进入循环
Loop
Fetch MyCur Into v_id,v_name;
Exit When MyCur % NotFound;
Update Rs_Employees
Set Name = Name || ' X '
Where HeTongId = v_Id;
v_Count : = v_Count + 1 ;
End Loop;
Dbms_Output.put_line( ' 已更新 ' || v_Count || ' 行 ' );
END ;
-- 例10:以下示范%RowCount和%IsOpen,同时示范了%RowType的使用
-- 例10:以下示例%RowCount
Declare
v_Row Rs_Employees % RowType;
Cursor MyCur Is
SELECT * FROM Rs_Employees
Where HeTongId <= ' WL-090010 ' ;
BEGIN
if Not MyCur % IsOpen then
Dbms_Output.put_line( ' 游标未打开 ' );
-- 打开游标
Open MyCur;
end if ;
-- 进入循环
Loop
Fetch MyCur Into v_row;
Exit When MyCur % NotFound;
Dbms_Output.put_line( ' 当前已取得 ' || MyCur % RowCount || ' 行 ' );
Dbms_Output.put_line( ' 姓名: ' || v_row.Name || ' ' || ' 合同号: ' || v_row.HeTongId);
Dbms_Output.put_line( '' );
End Loop;
Dbms_Output.put_line( ' 总共已取得 ' || MyCur % RowCount || ' 行 ' );
if MyCur % IsOpen then
Dbms_Output.put_line( ' 游标已打开 ' );
Close MyCur;
end if ;
if Not MyCur % IsOpen then
Dbms_Output.put_line( ' 游标已关闭 ' );
end if ;
END ;
-- 例12:查询嵌套表中数据的游标
-- 1 创建类型
CREATE OR REPLACE TYPE emp_type As Object
(eno number ,
ename varchar2 ( 20 ),
esal number );
-- 2 使用Table of 子句创建Table类型
CREATE TYPE emp_nt AS Table Of emp_type;
-- 3 使用emp_nt数据类型创建myemp表
CREATE