黄松平.txt

关于oracle和sql的书籍和ppt教程,非常好,本人珍藏品

1
create or replace function output_sum(num int)
return int
is
  v_i int:=0;
 v_sum int:=0;

begin
if num<0 then
for v_i in num .. 0 loop
v_sum:=v_sum+v_i;
end loop;
else
  for v_i in 0..num loop
  v_sum:=v_sum+v_i;
  end loop;
  end if;
dbms_output.put('the sum is'||v_sum);
return v_sum;
end;


2
(1)
create or replace function output_sal(v_empno emp.empno%type)
return emp.sal%type
is v_sal emp.sal%type;
empno_error exception;
v_i int;
begin
select count(1) into v_i from emp where empno=v_empno;
if v_i=0 then
raise empno_error;
else
select sal into v_sal from emp where empno=v_empno;
return v_sal;
end if;
exception
when empno_error then
dbms_output.put_line('没有该雇员：'||v_empno);
end;
(2)
create or replace procedure update_emp_hsp(v_empno emp.empno%type,v_sal emp.sal%type)
is
v_i int;
empno_error02 exception;
begin
select count(1) into v_i from emp where empno=v_empno;
if v_i=0 then
raise empno_error02;
else
update emp set sal=v_sal where empno=v_empno;
end if;
exception
when empno_error02 then
dbms_output.put_line('不存在该雇员'||v_empno);
end;
(3)
declare
 type emp_table_type is table of emp%rowtype index by binary_integer;
 datas emp_table_type;
 type empno_type is table of emp.empno%type index by binary_integer;
 v_empno empno_type;
 v_i int :=1;
begin
  v_empno(1):=7369;
  v_empno(2):=7499;
  v_empno(3):=7566;
  v_empno(4):=7521;
  v_empno(5):=7698;
  for v_i in 1..5 loop
   select * into datas(v_i)  from emp where empno =v_empno(v_i);
   if(output_sal(v_empno(v_i)))<2500 then 
    update_emp_hsp(v_empno=>v_empno(v_i),v_sal=>3600);
   end if;
  end loop;
end;