Oracle之PL/SQL编程基础

PL/SQL块简介：
块结构：
Declare         --定义部分
     Declarations       
Begin                  --执行部分
     Executable  code
Exception                --异常处理部分
     Exceptional handlers
End;
匿名块：
是动态生成，只能执行一次的块，没有名字，不能由其他应用程序调用；
  如：
　(1).编写一个块，输出 This a  my  block
   Sql> set serveroutput on
   Sql>begin
       Dbms_output.put_line(‘This a  my  block );
       End;
       /                
  (2).输出表students中学号为103的学生姓名：
　　　Sql> set serveroutput on
　　　Sql>declare 
　　　        v_name  varchar2(10);
　　　    begin
　　　       select name  into  v_name from students where students_id=’103’;
　　　    dbms_output.put_line(‘学生姓名’ ||v_name );
　　　    end;
　　　    /
   (3).根据输入学生的学号，输出该学生的姓名，并且考虑输入不存在的学号的情况
　　　   Sql> set serveroutput on
　　　　Sql>declare 
　　　        v_name  varchar2(10);
　　　    begin
　　　       select  name  into  v_name from students 
　　　　　　　where students_id=&students_id
　　　    dbms_output.put_line(‘学生姓名’ ||v_name );
　　　    exception 
　　　            when no_data_found  then 
　　　               dbms_output.put_line(‘输入学号不存在!’);
　　　    end;
　　　    /


命名块…….
PL/SQL基本语法要素：
字符集不区分大小写
只有PL/SQL中可以引用：-10E4，5.12e-6，7*10*2(表示为：7x10^2)
字符型文字：使用单引号如：’我是一名学生’,  ’kill’
Boolean型文字：
有：True,False,Null 三个值；
注释：
单行注释：
“--注释”
多行注释：
“/*注释*/”
变量及其数据类型：
如：
LOB变量
Declare 
   A  table.B %TYPE;      --将A与表table中的B建立联系，A随B变
在PL/SQL中执行SQL语句：
执行select语句：
在表department表中查询部门编号为101的记录，并把系部名称和系部所在地显示出来，使用标量变量：


Declare
  v_id departments.department_id %TYPE;
  v_name departments.department_name%TYPE;
  v_address departments.department_address %TYPE;
Begin
  Select *  into v_id,v_name,v_address   from deparments
  Where deparment_id=101;
　　Dems_output.put_line(‘系部名称’||v_name);
　　Dems_output.put_line(‘系部地址’||v_address);
End;
/
在表department表中查询部门编号为101的记录，并显示该生的姓名，性别，出生年月，使用记录变量：
Declare 
  v_student students%TYPE;
  Begin
       Select  *  into  v_student
       From students  where student_id=101
  Dbms_output.put_line(‘姓名 性别出生年月’);
  Dems_output.putline(v_student.name||v_student.sex||v_student.dob);
执行DML语句：
执行insert语句：
(1)插入一条记录（使用常量为插入记录提供数据）
Begin 
Insert into students
values(101,null,’小笨’,’女’,’07-5月-1988’,’计算机’);
end;
/
(2)插入一条记录（使用变量为插入记录提供数据）
Declare
   id user.id%TYPE:=1099;
   name user.name%TYPE:=’小笨’;
   begin 
  insert into user values(id,name);
end;
/
(3)插入多条记录（使用子查询为插入多条记录提供数据）
Begin
    Insert into user (select * from  user_two where
specialty=’计算机’);
   end;
   /


执行update语句：
修改一条记录（使用常量为修改记录提供数据）
Begin
Update user
  Set  id=101,
       Name=’小笨’
  Where id=103;
End;
/
修改一条记录（使用变量为修改记录提供数据）
Declare
  V_id user.id%TYPE:=1099;
   V_name user.name%TYPE:=’小笨’;
 begin 
  update user 
　　　　　　　　　set  id=v_id,
　　　　　　　　　     name=v_name
       where id=1099;
　　　　end;
　　/


修改多条记录（使用子查询为修改多条记录提供数据）
如：使用子查询将奖金未定的教师的奖金更新为平均奖金
Begin 
  Update teachers 
        Set  bonus=(select  avg(bonus) from teachers )
        Where  bonus is NULL;
End;
/
执行delete语句：
删除一条记录（使用常量为插入记录提供数据）
Begin
Delete from user where id=1;
End;
/
删除一条记录（使用变量为删除记录提供数据）
Declare
  v_id  user.id%TYPE :=1;
Begin 
　　　Delete  from user  where  id=v_id;
　　　　End;
　　　　/
删除多条记录（使用子查询为删除多条记录提供数据）
Begin
  Delete from  teachers
  Where  wage > (select  1.1*avg(wage)  from  teachers);
End;
/


执行事务处理语句：
对students表执行DML操作，在PL/SQL程序中使用commit,Rollback,savepoint等事务处理语句：
Sql>
Begin 
  Insert into students
　　values(101,null,’小笨’,’女’,’07-5月-1988’,’计算机’);
　 commit;
　delete  from students  where specialty=’计算机’;
　rollback;
　update  students set student_id=1023,
　                   dob=’02-3月-1989’,
　                    specialty = ‘自动化’,
where student_id = 10101;
savepoint  sp1;
delete from students where student_id=10101;
savepoint sp2;
rollback  to sp1;
commit;
end;
/


PL/SQL程序控制结构：
分支结构：
If语句：
IF-THEN-END IF
将讲师职称的某位教师的工资提高10%（其他教师工资不变）
Sql>declare
　　　V_id teachers.id%Type;
　　　V_id teachers.title%Type;
　　　Begin
　　　   V_id : =&id;
　　　   Select title  into v_title  from  teachers  
　　　Where   id=v_id;
If  v_title = ‘讲师’  then update  teachers  set  wage=1.1*wage  where  id=v_id;
End if;
End;
/
IF-THEN-ELSE-END  IF
将讲师职称的某位教师的工资提高10%,其他教师工资提高100元
Sql>declare
V_id teachers.id%Type;
　　V_id teachers.title%Type;
Begin
 v_id : =&id;
 Select title  into v_title  from  teachers  
Where   id=v_id;
If  v_title = ‘讲师’  then update  teachers  set  wage=1.1*wage  where  id=v_id;
else 
　　　Update  teacher  
　　　Set  wage = wage + 100  where id=v_id;
End if;
End;
/


(3)IF – THEN –ELSE –THEN –ELSE-END IF
将讲师职称的某位教师的工资提高10%,其他教师职称不是
   教授，而是高工或是副教授则工资提高5%，否则工资提高100
Sql>declare
V_id teachers.id%Type;
　　V_id teachers.title%Type;
Begin
 v_id : =&id;
 Select title  into v_title  from  teachers  
Where   id=v_id;
If  v_title = ‘讲师’  then update  teachers  set  wage=1.1*wage  where  id=v_id;
elseif  v_title=’高工’  or v_title=’副教授’  then 
update  teachers  
set wage=1.05*wage  where  id=v_id;
esle
　　　Update  teacher  
　　　Set  wage = wage + 100  where id=v_id;
End if;
End;
/
CASE语句：
1.
Declare
  V_id  teachers.id%Type;
  V_title  teachers.title%Type;
Begin
  V_id : =&id;
 Select title  into v_title  
From teachers  where id=v_id;
Case v_title
　　　When ‘教授’ then 
　　　     Update  teachers
　　　        Set  wage=1.15*wage  where  id=v_id;
　　　When ‘高工’ then 
　　　     Update  teachers
　　　        Set  wage=1.05*wage  where  id=v_id;
　　　When ‘副教授’ then 
　　　     Update  teachers
　　　        Set  wage=1.005*wage  where  id=v_id;
　　　Else
　　　      Update  teachers
　　　          Set wage=wage+10  where  id=v_id;
　　　End  case;
　　　End;
　　　/
　　　2.
Declare
  V_id  teachers.id%Type;
  V_title  teachers.title%Type;
Begin
  V_id : =&id;
 Select title  into v_title  
From teachers  where id=v_id;
Case v_title
　　　When  v_title=‘教授’ then 
　　　     Update  teachers
　　　        Set  wage=1.15*wage  where  id=v_id;
　　　When  v_title=‘高工’  or  v_title=’ 副教授’  then 
　　　     Update  teachers
　　　        Set  wage=1.05*wage  where  id=v_id;
　　Else
　　　      Update  teachers
　　　          Set wage=wage+100  where  id=v_id;
　　　End  case;
　　　End;
　　　/
　　　3.
 Declare
  V_id  teachers.id%Type;
  V_income  teachers.income%Type;
Begin
  V_id : =&id;
 Select incmoe  into v_income  
　From teachers  where id=v_id;
　Case v_income
　　　When  v_income <1000 then 
　　　   Dbms_output.put_line(‘个人工资增加10%：’||v_income*1.1);
　　　When  5000<v_income <10000 then 
　　　   Dbms_output.put_line(‘个人工资减少10%：’||v_income*0.9);
　　　end  case;
　　　End;
　　　/


循环结构：
Loop循环：
建表：
Create table total (
V_i  int,
V_sum int);
================================================
Declare
V_i  int:=1;
V_sum  int:=0;
Begin
　　　Loop
　　　 V_sum :=v_sum+v_i;
　　　Insert  into total  values (v_i,v_sum);
　　　Exit when v_i = 10;
　　　V_i : =v_i+1;
　　　End loop;
　　　End;
　　　/
while循环：
declare 
       v_i  int: =1;
       v_sum int:=1;
begin
  while v_i<10  loop
  v_factorial : =v_factorial*v_i;
  insert  into total values(v_i,v_factorial);
  end  loop;
end;
/
3.GOTO和NULL语句：
1.goto语句：
Set serveroutput on
Declare
  V_i  int:=1
  V_sum int: =0;
Begin
   Loop
   V_sum:=v_sum+v_i;
   Insert into total values(v_i,v_sum);
   If  v_i=10  then 
         Goto  output;
  End if;
V_i := v_i+1;
End  loop;
<<output>>
Dbms_output.put_line(‘v_sum’||v_sum);
End;
/


4.NULL语句
Null语句为空语句，不执行任何语句，直接将程序交给下一条语句
Set serveroutput on
Declare
  V_i  int:=1
  V_sum int: =0;
Begin
   Loop
   V_sum:=v_sum+v_i;
   Insert into total values(v_i,v_sum);
   If  v_i=10  then 
         Goto  output;
  End if;
V_i := v_i+1;
End  loop;
<<output>>
Null;
End;
/




异常处理：
系统预定义异常(除数为0）ZERO_DIVIDB：
Declare
  a  number:=0;
  b  number:=100;
  c  number;
begin
  c = b/a;
exception
when  ZERO_DIVIDB  then
dbms_output.put_line(‘除数为0’);
end;
/


系统预定义异常(未找到数据的错误)NO_DATA_FOUND：
Set serverout on
Declare
V_id  student.id %TYPE;
V_name  student.name %TYPE;
begin
   v_id:=&id
   select name into v_name from student where  id=v_id;
   dbms_output.put_line(‘姓名’||v_name);
exception
  when NO_DATA_FOUND  then
  dbms_output.put_line(‘学号不存在！’);
   end; 
/


系统预定义异常(select into时返回多行数据的错误)TOO_MANY_ROWS：
Set serverout on
Declare
V_id  student.id %TYPE;
V_name  student.name %TYPE;
begin
   v_id:=&id
   select name into v_name from student where  id=v_id;
   dbms_output.put_line(‘姓名’||v_name);
　exception
  when TOO_MANY_ROWS  then
  dbms_output.put_line(‘返回记录多余一行！’);
  end; 
/


同时出现TOO_MANY_ROWS和NO_DATA_FOUND系统预定义异常：
Set serverout on
Declare
V_id  student.id %TYPE;
V_name  student.name %TYPE;
begin
   v_id:=&id
select name into v_name from student where  id=v_id;
   dbms_output.put_line(‘姓名’||v_name);
Exception
when NO_DATA_FOUND  then
  dbms_output.put_line(‘学号不存在！’);
  when TOO_MANY_ROWS  then
 dbms_output.put_line(‘返回记录多余一行！’);
 end; 
　　/
非预定义异常名：
在编译时，将异常名与一个Oracle错误代码相关联：
Set  serverout  on 
Declare
     E_id  exception;
     Pragma  exception_init (E_id,-2292);
   Begin
       Delete  from  departments   where  id=101;
  Exception   
        When E_id then
      dbms_output.put_line(‘表中存在子目录！’);
  end;
/


用户自定义异常名：
定义异常名称，安排何时抛出异常
如：当工资为负时，抛出异常
Declare
E_wage  exception;        --用户定义异常
V_wage  teachers.wage%TYPE;
Begin 
 V_wage :=&wage;
Insert  into teachers  values(101,’小笨’,’v_wage’);
If  v_wage<0  then
Raise  E_wage;     --抛出异常
End if;
Exception  
   When  E_wage  then
      dbms_output.put_line(‘工资为负！’);
      rollback;
end;
/
带when  others  then的异常处理：


Declare
E_wage  exception;        --用户定义异常
V_wage  teachers.wage%TYPE;
Begin 
 V_wage :=&wage;
Insert  into teachers  values(101,’小笨’,’v_wage’);
If  v_wage<0  then
Raise  E_wage;     --抛出异常
End if;
Exception  
   When  E_wage  then
      dbms_output.put_line(‘工资为负！’);
      rollback;
when  others then 
   dbms_output.put_line(‘插入出现错误！’);
end;
/
使用异常函数：
函数SQLCODE： 获得Oracle错误代码
函数SQLERRM：获得与之相应的错误描述
Declare
E_wage  exception;        --用户定义异常
V_wage  teachers.wage%TYPE;
Begin 
 V_wage :=&wage;
Insert  into teachers  values(101,’小笨’,’v_wage’);
If  v_wage<0  then
Raise  E_wage;     --抛出异常
End if;
Exception  
   When  E_wage  then
      dbms_output.put_line(‘工资为负！’);
      rollback;
when  others then 
   dbms_output.put_line(‘错误代码：’||SQLCODE);
　　dbms_output.put_line(‘错误描述：’||SQLERRM);
end;
/