702.txt

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

create or replace package body my_pkg
is
pragma serially_reusable;
    --变量的声明
    v_flag number;
    --游标的声明
     cursor mycursor is
      select deptno, dname from dept;
    --函数声明和定义
      function check_dept(v_deptno number)
         return number
      is
      begin
        select count(*) into v_flag from dept
	 where deptno = v_deptno;
	 if v_flag >0 then
	    v_flag :=1 ;
	  end if;
	  return v_flag;
	end check_dept;
    -- 公有函数定义add_dept
     function add_dept(v_deptno number,v_deptname varchar2,v_deptloc varchar2)
      return number
      is
      begin
		if check_dept(v_deptno) = 0 then
		  insert into dept values(v_deptno,v_deptname,v_deptloc);
		  return 1;
		 else
		   return 0;
		 end if;
        exception
	     when  others then
	          v_sqlcode := sqlcode;
		   v_sqlerrm :=sqlerrm;
		    return -1;
        end add_dept;
-- 公有函数定义remove_dept
  function remove_dept(v_deptno number)
      return number
      is
      begin
         if check_dept(v_deptno) = 1 then
	     delete from dept where deptno=v_deptno;
	       return 1;
	   else
	       return 0;
	   end if;
       exception
           when others then
	       v_sqlcode := sqlcode;
	        v_sqlerrm :=sqlerrm;
		return -1;
         end remove_dept;

	
     -- 公有过程定义query_dept
     procedure query_dept(v_deptno number)
     is
     begin
       if check_dept(v_deptno) =1 then
          select  * into v_deptrec from dept
	   where deptno =v_deptno;
	 end if;
	 exception
	    when others then
	      v_sqlcode :=sqlcode;
	       v_sqlerrm := sqlerrm;
	   end query_dept;
 -- 公有过程定义 read_dept
 procedure read_dept
 is
     v_deptno number;
     v_dname varchar2(14);
  begin
      for c_mycursor in mycursor loop
         v_deptno := c_mycursor.deptno;
	  v_dname := c_mycursor.dname;
	  dbms_output.put_line(v_deptno||'   '||v_dname);
	 end loop;
  end read_dept;
   begin  --包体初始化部分，对公有变量进行初始化
      v_sqlcode :=null;
       v_sqlerrm :='初始化消息文本';
    end my_pkg;

/*create or replace package my_pkg
is
pragma serially_reusable;
    v_deptrec dept%rowtype;
    v_sqlcode number;
    v_sqlerrm varchar2(2048);
    function add_dept(v_deptno number,v_deptname varchar2,v_deptloc varchar2)
       return number;
     function remove_dept(v_deptno number)
        return number;
     procedure query_dept(v_deptno number);
     procedure  read_dept;
 end my_pkg;

create or replace package body oracle528
is
  cursor curtea(tempid int)
  is
  select empno ,ename from emp where empno > tempid;
  
  mydata curtea%rowtype; 
  
  k int;
  procedure shuchu(id int)
  is
    
  begin
    open curtea(id);
    
    loop
      /* fetch curtea into mydata ;
       dbms_output.put_line('老师的工号：'||mydata.id||' 老师的名字：'||mydata.name);
       exit when curtea%notfound;
       fetch curtea into mydata ;
      if curtea%found then
          dbms_output.put_line('老师的工号：'||mydata.empno||' 老师的名字：'||mydata.ename);
        else
          exit;
       end if;
    end loop;
    
    close curtea;
  end shuchu;
begin
  message := 'dsafldsafdsafdsa';
  k := 5;
end oracle528;

create or replace package oracle528
is
  procedure shuchu(id int);
  message varchar2(100);
end oracle528;

create or replace trigger stuteatrigger1
instead of insert
on tempview1
for each row
declare
  shu number;
begin
---------------------------------------------------------------------
  select count(*) into shu from temporacle;
  insert into temporacle values(:new.name,:new.age,shu + 1);
end;
--create table temporacle (name varchar2(10),age int, id int);
create view tempview1
as
select name ,age from temporacle;


create or replace trigger temporacletriger
after
insert
on tempview1
for each row
declare
    tempint int := 0;
begin

  select max(id) into tempint from temporacle;
  update temporacle set id = tempint + 1 --1002
  where id is null; --102
end;
create or replace trigger teacherupdate
after
update 
on banji
for each row
begin
  update xuesheng set bid = :new.bid --1002
  where bid = :old.bid; --102
end;
--create table xuesheng(xid int,name varchar2(10),bid int);
--alter table xuesheng add constraint xue_xid_pri primary key(xid);
--alter table xuesheng add constraint xue_bid_for foreign key(bid) references  banji(bid);
--create table banji(bid int , name varchar(20));
--alter table banji add constraint banji_bid_pri primary key(bid);



create or replace trigger student11
before
insert or update or delete
on emp
begin
     if (to_char(sysdate,'dd') between 1 and 20 ) then
        raise_application_error(-20001,'sorry.....bu neng insert');
     end if;
     dbms_output.put_line(to_char(sysdate,'dd'));
end;

declare
   CURSOR emp_fetch  ----声明游标
   is 
   select ename from emp ;
   
   cursor emp_fetch2 ----声明游标
   is 
   select ename,sal from emp;
   
   type myrecord is record(
   tempname emp.ename%type,
   tempsal  emp.sal%type,
   tempjob  emp.job%type);
   
   cursor emp_fetch3(tsal emp.sal%type default 1000)  -----声明游标
   return myrecord
   is 
   select ename,sal,job from emp where sal > tsal;
   
   cursor emp_fetch4(tsal emp.sal%type default 1000)  -----声明游标
   is 
   select ename,sal,job from emp where sal > tsal;
   
   tempcor emp_fetch4%rowtype;
   
   temprecord myrecord;
   tempname emp.ename%type;
   tempsal  emp.sal%type;
   tempjob  emp.job%type;
begin
   open emp_fetch4(100);
   
   
   loop
    fetch emp_fetch4 into tempcor ;
     if emp_fetch4%found then
       --fetch emp_fetch into tempname ;
       dbms_output.put_line(tempcor.ename || tempcor.sal);
     else
       dbms_output.put_line('shuju is over');
       exit;
     end if;
    
    end loop;
    
    close emp_fetch4;
    
   open emp_fetch3(100);
   
   
   loop
    fetch emp_fetch3 into temprecord ;
     if emp_fetch3%found then
       --fetch emp_fetch into tempname ;
       if temprecord.tempsal < 1000 then
          update emp set sal = sal + 500 where ename = temprecord.tempname;
       elsif temprecord.tempsal > 2000 then
          update emp set sal = 2000 where ename = temprecord.tempname;
       end if;
       dbms_output.put_line(temprecord.tempname || temprecord.tempsal);
     else
       dbms_output.put_line('shuju is over');
       exit;
     end if;
    
    end loop;
    
    commit;
    close emp_fetch3;
   
   open emp_fetch2;
   
   loop
    fetch emp_fetch2 into tempname,tempsal ;
     if emp_fetch2%found then
       --fetch emp_fetch into tempname ;
       dbms_output.put_line(tempname || tempsal);
     else
       dbms_output.put_line('shuju is over');
       exit;
     end if;
    
    end loop;
    
    close emp_fetch2;
  
   open emp_fetch;
   loop
    fetch emp_fetch into tempname ;
     if emp_fetch%found then
       --fetch emp_fetch into tempname ;
       dbms_output.put_line(tempname);
     else
       dbms_output.put_line('shuju is over');
       exit;
     end if;
    
    end loop;
     
   close emp_fetch;
end;


--------------------------------------------------------------------------------------
--execute shuchu2(7900);

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;
----------------------------------------

declare
  temp varchar2(10);
begin
  shuchu(7876,7900,5000,temp);
  dbms_output.put_line(temp);
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;

  if checkid(mudi) = 0 then
     raise is_exit;
  end if;

  select sal into tmpmoney 
  from emp where empno = sourceid;
  
  if tmpmoney < money then
     raise balance_shao;
  end if;

  update emp set sal = sal - money where empno = sourceid;
  update emp set sal = sal + money where empno = 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');
      when others then
         rollback;
end shuchu;

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

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

end;*/