谢妮娜.txt

来自「关于oracle和sql的书籍和ppt教程,非常好,本人珍藏品」· 文本 代码 · 共 78 行

1，定义一个函数，输入一个大于0的整数n， 返回1到n的整数和

create or replace function zsh(a int)
return int
as
v_1 number;
v_2 number;
shuru_error exception;
begin
    if a<=0 then
    raise shuru_error;
    end if;
  for v_2 in 1..a loop
    v_1:=v_1+v_2;
    end loop;
  return v_1;
exception
 when shuru_error then
 dbms_output.put_line('请输入正整数');
end zsh;

================================================
2，定义一个函数，输入emp的empno，返回该行的sal

create or replace function xnn_1(myno emp.empno%type) 
return number
as
mysal emp.sal%type;
begin
select sal into mysal from emp where empno=myno;
return mysal; 
exception
when no_data_found then
 dbms_output.put_line('雇员号不存在，请重新输入');

end xnn_1;
===================================================
   定义一个更新emp表sal的存储过程，输入参数为empno,sal,根据empno更新相应的sal
create or replace procedure xnn_2(myno emp.empno%type,mysal emp.sal%type)
is
begin
update emp set sal=mysal where empno=myno;
commit;

exception
when no_data_found then
 dbms_output.put_line('雇员号不存在，请重新输入');

end xnn_2;
====================================================
   定义一个table 里面存放的是%rowtype类型，用该类型定义一个变量datas，从emp表取五行记录放到datas里面进行循环，循环里完成以下操作：先通过刚才定义的函数取sal，如果sal<2500 ,调用刚才写的存储过程更新sal为3600
declare
  type xnn_3 is table of a_emp%rowtype index by binary_integer;
  datas xnn_3;
  type xnn_4 is table of a_emp.empno%type index by binary_integer;
  data2 xnn_4;
  i int;
  j int;
  v_sal number;
  v_xuhao int;
begin
        j:=&j;
    select xuhao into v_xuhao from a_emp ;
      for v_xuhao in j..j+5 loop
      select empno into data2(j) from a_emp where xuhao=j;
      end loop;
        for i in 1..5 loop
          select * into datas(i) from a_emp where empno = data2(j);
        v_sal := xnn_1(data2(j));
        if v_sal<2500 then
        xnn_2(data2(j),3600);
        end if;
     end loop;  
       dbms_output.put_line(datas(i).ename);
       dbms_output.put_line(datas(i).sal);
  
 end;