章猛.txt

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

1，
declare
 x number :=6; 
 k number;
 xp number;
 kp number;
 xx number;
begin
 while abs(x)<=6 loop
   k:=abs(x)/2;
   kp:=1;
   xp:=1;
   
   <<p_k>>
   while kp<=k loop
         dbms_output.put(' ');
         kp:=kp+1;
    end loop;
    
    xx:=7-abs(x);
    
    <<p_x>>
    while xp<=xx loop
          dbms_output.put('*');
          xp:=xp+1;
    end loop;
    
    kp:=1;
    <<p_kk>>
    while kp<=k loop
         dbms_output.put(' ');
         kp:=kp+1;
    end loop;
   dbms_output.put_line(' ');
   x:=x-2;
 end loop ;
 end;


          
          
2，在程序块里写一个事务分成两个单元（一个存储点）
   存储点之前给emp表插入一行数据
   存储点之后给emp表的某一行修改下age ，然后查询该行的age ，如果age > 40 回滚到存储点提交事
declare
  ageee myemp.age%type;
begin
  insert into myemp(empno,age) values(1236,2l);
  savepoint ps1;
  updata myemp set age=age+10 where empno=1236;
  select age into ageee from myemp where empno=1236;
  if ageee >40 then
    rollback to sp1;
    commit;
  else
   commit;
  end if;
end;

3
(1)declare
 egtype myemp.hiredate%type;
begin
 select hiredate into egtype from myemp where empno=&emp_no;
 dbms_output.put_line(egtype);
exception
 when no_data_found then
 dbms_output.put_line('对应的数据不存在');
end;

(2)declare
 eg_rowtype myemp%rowtype;
begin
 select * into eg_rowtype from myemp where empno=&emp_no;
 dbms_output.put_line(eg_rowtype.ename);
 dbms_output.put_line(eg_rowtype.job);
exception
 when no_data_found then
 dbms_output.put_line('对应的数据不存在');
end;

4;
declare
type tablez is  table of emp%rowtype index by binary_integer;
datas tablez;
vi=1
begin
for zmt in 1..5 loop
select * into datas(zmt) from emp where empno=&;
vi=vi+1;
end loop;
for zmm in 1..5 loop
dbms_output.put_line(datas(zmm).ename);
end loop;
exception
when no_data_found then
dbms_output.put_line('对应的记录不存在');
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 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 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 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;
*/
--execute shuchu(1,2,1000);
/*create or replace procedure shuchu(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 shuchu;*/

--execute shuchu(7499);

--进行转帐     1 转出帐号，  2，转入帐号 3，金额（input）  4，结果（output）






第一题
（为什么输入小数也可以得到值？）(pl/sql 中为什么没有int?)

create or replace function sum_n(n int)
  return int
as
   renn int :=1;
   n_sum int :=0;
   n_error exception;
begin
 if n<=0 then
    raise n_error;
 end if;
 while renn <= n loop
    n_sum:=renn+n_sum;
    renn:=renn+1;
 end loop;
 return n_sum;
exception
 when n_error then
     dbms_output.put_line('输入错误');
     return null;
end;

/* 
declare 
 zm int;
 mz int;
begin
 zm:=&aa;
 mz :=sum_n(zm);
 dbms_output.put_line(mz);
end;
*/

2.1
/*
create or replace function no_re_sal(f_empno number)
 return number
as
 f_sal number(8,2);
begin
 select sal into f_sal from zmemp where empno=f_empno;
 return f_sal;
exception
 when no_data_found then
   dbms_output.put_line('该员工不存在');
   return null;
end;
*/


2.2
/*
create or replace procedure up_emp(up_no number,up_sal number)
as
begin
  update zmemp set sal = up_sal where empno = up_no;
  commit;
end;
*/

2.3
declare
 type tabler is table of zmemp%rowtype index by binary_integer;--定义tabler类型
 datas tabler;
 p_sal number;--通过函数返回sal值
 i int :=1;
 --sum_row int;
begin
 --select count(1) into sum_row from zmemp;
 
 select * into datas(1) from emp where empno=7369;
 select * into datas(2) from emp where empno=7499;
 select * into datas(3) from emp where empno=7566;
 select * into datas(4) from emp where empno=7521;
 select * into datas(5) from emp where empno=7698;
 while i<=5 loop
    p_sal :=no_re_sal(datas(i).empno);--通过函数返回sal值
    if p_sal < 2500 then              --比较返回的sal值
      up_emp(datas(i).empno,3600);    --更新sal值到emp表中
    else
      null;
    end if;
    i:=i+1;
    commit;
 end loop;
exception
  when no_data_found then
  dbms_output.put_line('数据不存在');
end;