608.txt

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

create or replace function checkid(tempid int)
return int
as
  res int;
begin
 select count(1) into res from account where id = tempid;

 if res > 0 then
    return 1;
 else
    return 0;
 end if;

end;


create or replace function check_acount(v_temp int)
return int
is
temp int;

begin
select count(1) into temp from emp where empno=v_temp;
if temp>0 then
return 1;
else
return 0;
end if;
end;


create or replace function getmaxid(maxid out int)
return int
as
  --maxid int ;
begin
  select max(id) into maxid from account;
  return maxid;
end;


create or replace procedure adddata(money number)
is
  max_id int;
begin
  max_id := getmaxid(max_id);
  max_id := max_id + 1;

  insert into account values(max_id,money);
  commit;

end;


create or replace procedure shuchu(sourceid in int,mudi int,money number,jieguo out varchar2)
as
  tmpmoney number(8,2);
  balance_shao exception;
  is_exit exception;
begin
  if checkid(sourceid) = 0 then
     raise is_exit;
  end if;

  select salary into tmpmoney from account where id = sourceid;
  if tmpmoney < money then
     raise balance_shao;
  end if;

  update account set salary = salary - money where id = sourceid;
  update account set salary = salary + money where id = mudi;

  commit;
  jieguo := 'true';
  exception
      when balance_shao  then
        jieguo  := 'false';
         dbms_output.put_line('yu e bu zu ');
      when is_exit then
         dbms_output.put_line('zhanghao bu  cun zai');
end shuchu;


create or replace procedure shuchu2(bianhao int)
is
type tablez is  table of emp%rowtype index by binary_integer;
datas tablez;
begin
  dbms_output.put_line(bianhao);
  select * into datas(1) from emp where empno=bianhao;
  dbms_output.put_line(datas(1).ename);
exception
when no_data_found then
dbms_output.put_line('对应的记录不存在');
end shuchu2;