分类： Oracle
2009-07-21 09:28:27
练习一：
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_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之间的素数：
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:
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
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;