杨勇6.8.txt

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

1.
create or replace function he(n int,qqq out int)
return int
as
  v_sum int:=0;
  i int:=1;
begin
 for i in 1..n
 loop
     v_sum:=v_sum+i;    
 end loop;
 return v_sum;
end;
SQL> variable a2 number;
SQL> execute :a2:=he(10, :a2);

PL/SQL procedure successfully completed
a2
---------
55

2.
create or replace function get_salary(v_empno emp_yang.empno%type)
return number
as
  v_sum number:=0;
  flag number:=-1;
  is_exit exception;
begin
   select count(emp_yang.empno) into flag from emp_yang where emp_yang.empno = v_empno;
   if flag = 0 then raise is_exit;
   end if;
   select sal into v_sum from emp_yang where emp_yang.empno=v_empno;
   return v_sum;
   exception
      when is_exit then dbms_output.put_line('bu cun zai');
   return v_sum; 
end;
SQL> execute :a2:=get_salary(10);

PL/SQL procedure successfully completed
a2
---------
800
create or replace procedure update_emp(v_empno emp_yang.empno%type,v_sal emp_yang.sal%type)
as 
    flag number:=-1;
    is_exit exception;
begin
    select count(emp_yang.empno) into flag from emp_yang where emp_yang.empno = v_empno;
    if flag = 0 then raise is_exit;
    end if;
    update emp_yang set emp_yang.sal = v_sal where emp_yang.empno = v_empno;
    commit;
    exception
      when is_exit then dbms_output.put_line('bu cun zai');
end;

SQL> execute update_emp(10,810);

create or replace procedure hgfhgh(dfg number)
as
    type row_emp is table of emp_yang%rowtype index by binary_integer;
    datas row_emp;
    v_empno emp_yang.empno%type:=10;
    i int:=0;
begin
    select * into datas(0) from emp_yang where emp_yang.empno=v_empno;
    update_emp(datas(i).empno,3500);
    /*while i!=5 
    loop
        select * into datas(i) from emp where emp.empno=v_empno;
        v_empno:=v_empno+100;i:=i+1;
        if get_salary(datas(i).empno)<2500 then update_emp(datas(i).empno,get_salary(datas(i).empno));
        end if;
    end loop;*/
    dbms_output.put_line('bu cun zai');
end;