维勒斯.txt

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

1, 把咱们练习（6月8号）里面写到的过程和函数放到包里进行管理，然后调用测试

create or replace package pk_6_8
as
function intsum(n int) return int;
function getsal(n int) return number;
procedure updateemp(n int,s number);
end pk_6_8;
----------------------------------------------------
create or replace package body pk_6_8
as
pragma serially_reusable;
amount int;
overlarge exception;
sall emp.sal%type;

function intsum(n int)
return int
as 
begin
 if(n>0) then
  amount := 0;
   for i in 1..n loop
   amount := amount + i;
  end loop;
  return amount;
 else 
  raise overlarge;
 end if;    
 exception
  when overlarge then
  dbms_output.put_line('请输入大于0的整数');   
end intsum;   


function getsal(n int)
return number
as
begin
 select sal into sall from emp where empno =n;
 return sall;
end; 

procedure updateemp(n int,s number)
is
begin
 update emp set sal=s where empno=n;
 exception
  when NO_DATA_FOUND then
   dbms_output.put_line('未查到数据！');
end updateemp;  

begin
 null;
end  pk_6_8;  
----------------------------------------------------------------
SQL> variable n number
SQL> execute :n := pk_6_8.intsum(100);
SQL> execute :n := pk_6_8.getsal(7900);
SQL> execute pk_6_8.updateemp(7900,400);







2，把昨天最后一道题写出来

create or replace view temp_20_view 
as
select temp_20.name,temp_20.age from temp_20;
-----------------------------------------------------------------------
create or replace trigger tri_20
instead of insert
on temp_20_view
for each row
declare 
 maxnum number;
begin  
 select max(id) into maxnum from temp_20;
  if maxnum is null then 
    insert into temp_20 values(:new.name,:new.age,1);  
  else  
    insert into temp_20 values(:new.name,:new.age,maxnum+1);     
  end if;  
  dbms_output.put_line('触发器被触发');  
end;






3，把刚才的例子敲一遍
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 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;






4，自己写一个应用的例子，这个例子里面用到游标，过程（把写的游标放到过程里），触发器（把过程放到触发器里），题目不限，自己想象

create or replace procedure salplus(n int)           ----过程 给新进人员合同工资上提1k
is
  tempsal emp_20.salary%type;
  tempno emp_20.id%type;
  cursor sp                                          -----游标
  is
    select id,salary from emp_20 where id = n;    
begin
    open sp;     
    fetch sp into tempno,tempsal;    
      update emp_20 set salary=tempsal+1000 where id=tempno;
      dbms_output.put_line('更新完毕!');     
    close sp;
end;
-------------------------------------------------------------------
create or replace view empview                       ----视图
as
select id,salary from emp_20;
-------------------------------------------------------------------
create or replace trigger empview_tri                -------新进员工加薪触发器
instead of
insert 
on empview
for each row
declare
 counter number;

begin
   insert into emp_20 (id,salary) values (:new.id,:new.salary);
   salplus(:new.id);
   dbms_output.put_line('从今天起入职的员工加薪1000');    
end;



   



5，每人对6，7章学的内容做一个总结（包含讲了那些内容，自己掌握了多少等等）
 
 答：第六章主要讲述数据库查询语言SQL基础，重点在于DML语句的掌握。我已经基本掌握select和insert语句，update语句更容易出错些。在select查询中，分组是难点。在DDL语句方面，我练习了增减列，增减各种约束(primary,foreign,unique,check)，等内容，基本掌握其要领。对于事物控制，回滚点和提交方法也有所练习。
 
 第七章讲述了oracle数据库设计语言PL/SQL基础，主要讲述了数据类型(type,rowtype,record,table)，程序控制中的分支语句和循环语句，异常的概念，游标，触发器，过程与函数，程序封装等，对上述内容已分别进行了课堂练习，我基本可以理解其要点。难点在于程序的调试，寻找语法错误和逻辑错误。语法错误包括数据输入格式，SQL方法函数的误用，关键字的缺失，声明顺序错误等。逻辑错误比如循环中语句执行顺序的混淆，不被执行语句，null视为0等。调试程序的经验还是要多积累。