数据库编程

PL/SQL程序

1.PL/SOL程序块

整个PL/SQL块分三部分：声明部分、执行部分、异常处理部分；

示例：

declare   --变量声明

v_sno   varchar2(10) := ‘04001’;

v_cno   varchar2(10) :=‘001’;

v_grade   number := 90;

begin  --程序入口

insert into sc values (v_sno,v_cno,v_grade);

commit; //insert update和delete的提交步骤

end;

2.特殊类型

%type ：已声明的变量和前边给出的变量类型相同，若前边变量类型改变，该声明变量自动改变

declare    

n sc.sno%type;

m sc.cno%type;    

x sc.grade%type;

y x%type;

%rowtype : 返回一个记录类型，其数据类型和数据库表的数据结构相一致。

declare    

a1 sc%rowtype;

begin  

select *  

into a1  

from sc  

where sno=‘04001’ and cno=‘003’ ；

end;  //a1--一个类似于sc的数据结构；

3.运算符

一般运算符

：=  赋值号   =>  关系号(一般用于给形参赋值)   ..  范围运算符  ||  字符连接符

关系运算符

<>  !=  ~=  ^=  不等于   

4.控制语句

①顺序语句：

goto语句：无条件跳转到指定的标号

null语句：说明“不用做任何事情”的意思，相当于一个占位符

②条件语句:

if 语句

if <布尔表达式> then     

pl/sql 和 sql语句

elsif < 其它布尔表达式> then     

其它语句

elsif < 其它布尔表达式> then     

其它语句

else     

其它语句

end if;

③循环语句:

loop…end loop语句

while语句

for循环语句

loop      

       要执行的语句;      

      whexiten <条件语句>

end loop;

while <布尔表达式> loop    

        要执行的语句;

end loop; 

if-else示例：

declare 
    n sc.sno%type; 
    m sc.cno%type; 
    x sc.grade%type;
begin
    select sno,cno,grade 
    into n,m,x
    from sc
    where sno='105698' and cno='2092508';    //对一个学生的一门科目进行成绩评级输出
    if x<60 then
        dbms_output.put_line(n||','||m||','||x||','||'不及格');
    elsif x>=60 and x<80 then
        dbms_output.put_line(n||','||m||','||x||','||'及格');
    elsif x>=80 and x<90 then
        dbms_output.put_line(n||','||m||','||x||','||'良好');
    else
        dbms_output.put_line(n||','||m||','||x||','||'优秀');
    end if;
    exception
      when no_data_found then dbms_output.put_line('没有查询结果');
end;

for循环示例：

begin
   	for  x  in 1..10 loop
       	       dbms_output.put_line('x 的当前值为:'||x);
   	end loop;
end;

游标

1.声明游标

cursor <游标名>[(<游标参数>[, <游标参数>]…)]            

is  <查询语句>;

游标参数只能为输入参数，其格式为：  

parameter_name [in] datatype [{:= | default} expression]

2. 打开游标

 open <游标名> [ ( [ <参数名> => ] <实参> [,…n] ) ];

3. 提取游标

  fetch <游标名> into {variable_list | record_variable };

4.游标关闭

当提取和处理完游标结果集合数据后，应及时关闭游标，以释放该游标所占用的系统资源，并使该游标的工作区变成无效，不能再使用fetch 语句取其中数据。

close <游标名>;

代码改进

declare 
    n sc.sno%type; 
    m sc.cno%type; 
    x sc.grade%type;
    cursor c1 is   //游标声明
    select sno,cno,grade 
    from sc
    where sno='04002' ;   //存在同一个人的多个课程信息和成绩
begin
    open c1;  //游标打开
    loop  //循环
        fetch c1 into n,m,x;  //游标提取
        exit when c1%notfound;  跳出循环的条件
        if x<60 then
            dbms_output.put_line(n||','||m||','||x||','||'不及格');
        elsif x>=60 and x<80 then
            dbms_output.put_line(n||','||m||','||x||','||'及格');
        elsif x>=80 and x<90 then
            dbms_output.put_line(n||','||m||','||x||','||'良好');
        else
            dbms_output.put_line(n||','||m||','||x||','||'优秀');
        end if;
     end loop;
     close c1;
end;   
     ……

 游标属性  

%found：布尔型属性 游标刚打开，指针指向第一行元组之前时，值为null； 游标指针指向查询结果集时，值为true； 其它，值为false。  

%notfound：布尔型属性，与%found相反。  

%isopen：布尔型属性，当游标已打开时返回 true。

 %rowcount：数字型属性，返回已从游标中读取的记录数 。

存储过程

1.创建存储过程

create [or replace] procedure <过程名>

      [(<参数名> <参数类型> <数据类型>[default <默认值>][,…n])]

{ is | as }

      [<变量声明> ]

begin <过程体>

end[过程名];

注：参数类型：

in：输入参数，参数在执行时被赋值，将值传入到过程体。

out：输出参数，参数在过程中将被赋值，可以传给过程体的外部。

in out：输入输出参数，既可以向过程体传值，也可以在过程体中赋值。

2. 调用存储过程

begin     

     <过程名>[([<参数名>=>]<实参>[,…n])];

end;

示例1：

创建存储过程，查询信息学院学生的考试成绩，列出学生的姓名、课程名和成绩，将查询结果在过程体中输出

//过程块
create or replace procedure p_select_xx
is
  	v_sname student.sname%type; 
    v_cname course.cname%type;  
    v_grade sc.grade%type;
  	cursor c1 is  select sname, cname,grade from student,sc,course
             where student.sno = sc.sno and course.cno = sc.cno and scollege = '信息工程学院';
begin
 	 open c1;
 	 loop
      	 fetch c1 into v_sname,v_cname,v_grade;
      	 exit when c1%notfound;
      	 dbms_output.put_line(v_sname||'，'||v_cname||'，'||v_grade);
  	end loop;
 	close c1;
end;

//调用过程块
begin
	 p_select_xx;
end;

示例2：

创建存储过程，查询指定学院学生的考试成绩，列出学生的姓名、课程名和成绩，将查询结果在过程体中输出

create or replace procedure p_select_xy(v_college in student.scollege%type)
is
  	v_sname studnet.sname%type; 
    v_cname course.cname%type;  
    v_grade sc.grade%type;
  	cursor c1 is  select sname, cname,grade  from student,sc,course
             where student.sno = sc.sno and course.cno = sc.cno and scollege = v_college;
begin
 	 open c1;
 	 loop
      	 fetch c1 into v_sname,v_cname,v_grade;
      	 exit when c1%notfound;
      	 dbms_output.put_line(v_sname||'，'||v_cname||'，'||v_grade);
  	end loop;
 	close c1;
end;
//调用
begin
	 p_select_xy(&输入学院名称);   //调用程序会显示要求输入学院名称，注意在输入时加单引号！！！
end;

示例3：

创建存储过程，统计指定课程的平均成绩和选课人数，将统计结果用输出参数返回给调用的主程序。

create or replace procedure p1
(v_cno in sc.cno%type,avg_grade out number, total out number) is
begin
  select avg(grade),count(sno)
  into avg_grade,total   //2个输出参数
  from sc
  where cno=v_cno;
end ;

//调用
declare
	a1 varchar2(10):='001’;
	a2 number;
	a3 number;
begin
	p1(a1,a2,a3);
    //或者
/*p1(avg_grade=>a2,total=>a3,v_cno=>a1);*/
	dbms_output.put_line(a2||'，’||a3);
end;

3. 删除存储过程          

drop proceurde <存储过程名>

存储过程小结

在创建存储过程中，需要考虑参数的种类和个数；

另外，当过程体中有查询语句时，如果查询结果为多行元组，需要游标；当查询结果为一行元组，则不需要游标，直接使用into语句即可；

触发器

触发器是用户定义在表上的一类由事件驱动的特殊的存储过程，当某个Oracle事件发生时系统自动地运行，所以运行触发器就叫触发。

存储过程通过其他程序来启动运行或直接启动运行，而触发器是由一个事件来启动运行，即触发器是当某个事件发生时自动地隐式运行。

触发器不能接收参数。

1.创建触发器语法格式

create [or replace] trigger  <触发器名称>

{before | after | instead of} <触发事件>   

//触发事件--insert、update、delete、create、alter、drop等

on {<表名>|<视图名>}

[for each row] [when <触发条件>]  //for each row行级触发器

declare      

        [变量声明;]

begin    

         <触发动作体>

end;

2.修饰符

:new修饰符，如果激活触发器的语句为Insert，Update，被插入的数据与被更新后的数据在触发体中由:new引用。

:old修饰符，如果激活触发器的语句为Delete，Update，被删除的数据与被更新前的数据在触发体中由:old引用。

示例： 创建触发器实现如下约束：当修改S表中学生学号时，级联更新相应的选课信息。

create or replace trigger tri_update_cascade  

after update  

on s  

for each row

begin  

        update sc  

        set sno=:new.sno  

        where sno=:old.sno;

end tri_update_cascade;

创建触发器实现：当在教师工资表中插入元组或者修改工资表时，要求教授的工资不得低于4000元，如果低于4000元，自动改为4000元。

create or replace trigger zdxg_sal    

after insert on sal_teach      

for each row

declare    

-- local variables here begin  

if :new.job='教授' and :new.sal<4000  then      

        update sal_teach      

        set sal=4000      

        where eno=:new.eno;  

end if;

end zdxg_sal;  错误

//原因：如果是后触发，触发体中不能对触发器所在的表做修改和查询操作。

create or replace trigger zdxg_sal    

before insert or update on sal_teach      

for each row

declare    

-- local variables here

begin  

if :new.job='教授' and :new.sal<4000 then      

:new.sal:=4000;  

end if; end zdxg_sal;

3.删除触发器

语法格式： drop trigger <触发器名>; 

4.总结

行级触发器：当DML语句对每一行数据进行操作时都会引起该触发器的运行。

语句级触发器：无论DML语句影响多少行数据，它所引起的触发器都仅执行一次。

替换触发器：定义在视图之上的触发器。

用户事件触发器：与DDL操作或用户登录、退出数据库等事件相关的触发器。

系统事件触发器：在Oracle数据库系统的事件中触发的触发器，如Oracle实例的启动与关闭。

①一个表上同一时间、同一事件、同一类型的触发器只能有一个;

②在触发器的执行部分只能用DML语句(select, insert, update, delete)，不能使用DDL语句(create, alter, drop);

 ③在触发语句中不能包含事务控制语句(commit, rollback, savepoint)。在触发器主体中调用的任何过程、函数，都不能使用事务控制语句。//存储过程可以包括；