开源中文网

您的位置: 首页 > Oracle > 正文

oracle游标测试代码

来源:  作者:

练习一: 



Sql代码 
declare 
v_deptno dept.deptno %TYPE := &p_deptno; 
begin 
delete from emp where deptno = v_deptno; 
if sql%NOTFOUND then 
delete from dept where deptno = v_deptno; 
commit; 
end if; 
rollback; 
end; 
declare 
v_deptno dept.deptno %TYPE := &p_deptno; 
begin 
delete from emp where deptno = v_deptno; 
if sql%NOTFOUND then 
delete from dept where deptno = v_deptno; 
commit; 
end if; 
rollback; 
end; 
练习二: 

Sql代码 
declare 
v_empno emp.empno%TYPE; 
v_sal emp.sal%TYPE; 
v_new_sal emp.sal%TYPE; 
cursor c_cursor is select empno, sal from emp; 
begin 
open c_cursor; 
loop 
fetch c_cursor into v_empno, v_sal; 
exit when c_cursor%notfound; 
if v_sal <= 1200 then 
v_new_sal := v_sal + 100; 
update emp set sal = v_new_sal where empno = v_empno; 
dbms_output.put_line(v_empno||'update'||v_sal); 
end if; 
end loop; 
dbms_output.put_line(c_cursor%rowcount); 
close c_cursor; 
end; 
declare 
v_empno emp.empno%TYPE; 
v_sal emp.sal%TYPE; 
v_new_sal emp.sal%TYPE; 
cursor c_cursor is select empno, sal from emp; 
begin 
open c_cursor; 
loop 
fetch c_cursor into v_empno, v_sal; 
exit when c_cursor%notfound; 
if v_sal <= 1200 then 
v_new_sal := v_sal + 100; 
update emp set sal = v_new_sal where empno = v_empno; 
dbms_output.put_line(v_empno||'update'||v_sal); 
end if; 
end loop; 
dbms_output.put_line(c_cursor%rowcount); 
close c_cursor; 
end; 
练习三:输出从1到100之间的素数: 

Sql代码 
declare 

type prime_arr is table of number index by binary_integer; 
v_prime_arr prime_arr; 
v_x number := 2; 
v_y number; 
v_count number := 1; 

begin 

while v_x <= 100 
loop 
v_y := 2; 
while v_y <= v_x 
loop 
if mod(v_x,v_y)=0 then 

exit; 

end if; 
v_y := v_y + 1; 
end loop ; 

if v_y = v_x then 
v_prime_arr(v_count) := v_x; 
dbms_output.put_line(v_prime_arr(v_count)); 
v_count := v_count + 1; 

end if; 
v_x := v_x + 1; 

end loop; 
end; 
declare 

type prime_arr is table of number index by binary_integer; 
v_prime_arr prime_arr; 
v_x number := 2; 
v_y number; 
v_count number := 1; 

begin 

while v_x <= 100 
loop 
v_y := 2; 
while v_y <= v_x 
loop 
if mod(v_x,v_y)=0 then 

exit; 

end if; 
v_y := v_y + 1; 
end loop ; 

if v_y = v_x then 
v_prime_arr(v_count) := v_x; 
dbms_output.put_line(v_prime_arr(v_count)); 
v_count := v_count + 1; 

end if; 
v_x := v_x + 1; 

end loop; 
end; 


demo2: 

Sql代码 
declare 
type prime_arr is varray(30) of number; 
--type prime_arr is table of number index by binary_integer; 
v_list prime_arr; 
v_count int ; 
i int ; 
res int := 1; 
j int :=0; 
begin 
v_list := prime_arr(); 
v_list.extend(30); 
for v_count in 2..100 loop 
for i in 2..v_count-1 loop 
if mod(v_count , i) =0 --and (i != v_count) 
then 
j:=v_count; 
end if; 
end loop; 
if j =0 then 
-- dbms_output.put_line(v_list(res)); 
--dbms_output.put_line(v_count); 
v_list(res) :=v_count; 
dbms_output.put(v_list(res)||','); 
res :=res+1; 
end if; 
j :=0; 
end loop; 
dbms_output.put_line(''); 
end; 
declare 
type prime_arr is varray(30) of number; 
--type prime_arr is table of number index by binary_integer; 
v_list prime_arr; 
v_count int ; 
i int ; 
res int := 1; 
j int :=0; 
begin 
v_list := prime_arr(); 
v_list.extend(30); 
for v_count in 2..100 loop 
for i in 2..v_count-1 loop 
if mod(v_count , i) =0 --and (i != v_count) 
then 
j:=v_count; 
end if; 
end loop; 
if j =0 then 
-- dbms_output.put_line(v_list(res)); 
--dbms_output.put_line(v_count); 
v_list(res) :=v_count; 
dbms_output.put(v_list(res)||','); 
res :=res+1; 
end if; 
j :=0; 
end loop; 
dbms_output.put_line(''); 
end; 
练习四: 

Sql代码 
DECLARE 
v_job emp.job%TYPE; 
v_sal emp.sal%TYPE; 
CURSOR c_cursor 
IS 
SELECT job, sal FROM emp WHERE ename like '%' || upper('&ename') ||'%'; 
BEGIN 
OPEN c_cursor; 
FETCH c_cursor INTO v_job, v_sal; 
WHILE c_cursor%FOUND 
LOOP 
DBMS_OUTPUT.put_line (v_job || '---' || TO_CHAR (v_sal)); 
FETCH c_cursor INTO v_job, v_sal; 
END LOOP; 
CLOSE c_cursor; 
END; 
DECLARE 
v_job emp.job%TYPE; 
v_sal emp.sal%TYPE; 
CURSOR c_cursor 
IS 
SELECT job, sal FROM emp WHERE ename like '%' || upper('&ename') ||'%'; 
BEGIN 
OPEN c_cursor; 
FETCH c_cursor INTO v_job, v_sal; 
WHILE c_cursor%FOUND 
LOOP 
DBMS_OUTPUT.put_line (v_job || '---' || TO_CHAR (v_sal)); 
FETCH c_cursor INTO v_job, v_sal; 
END LOOP; 
CLOSE c_cursor; 
END; 

Tags:oracle 游标 测试
关于开源中文网 - 联系我们 - 广告服务 - 网站地图 - 版权声明