Oracle中的PL/SQL介绍详解数据库

PL/SQL（Procedure Language/SQL）
PLSQL是Oracle对sql语言的过程化扩展
指在SQL命令语言中增加了过程处理语句（如分支、循环等），使SQL语言具有过程处理能力。

PL/SQL程序结构

declare 
      说明部分    （变量说明，光标申明，例外说明 〕 
begin 
      语句序列   （DML语句〕…  
exception 
      例外处理语句    
End; 
/ 

PL/SQL 的hello word

set serveroutput on 
 
declare 
   --变量的说明 
begin 
   --程序 
   dbms_output.put_line('Hello World'); 
end; 
/

PL/SQL 中的变量和常量
说明变量 （char, varchar2, date, number, boolean, long)
varl vhar(12) 说明变量名、数据类型和长度后用分号结束说明语句。
psal emp.sal%type; 引用型变量，即my_name的类型与emp表中ename列的类型一样
emp_rec emp%rowtype; 记录型变量

--查询并打印7839的姓名和薪水 
set serveroutput on 
 
declare 
  --定义引用型变量 
  pename emp.ename%type; 
  psal   emp.sal%type; 
begin 
 
  --得到7839的姓名和薪水 
  select ename,sal into pename,psal from emp where empno=7839; 
 
  --打印 
  dbms_output.put_line(pename||'的薪水是'||psal); 
 
end; 
/ 
 
 
----查询并打印7839的姓名和薪水 
set serveroutput on 
 
declare 
  -- 定义记录型变量 ，代表一行 
  emp_rec emp%rowtype; 
begin 
 
  select * into emp_rec from  emp where empno=7839; 
 
  dbms_output.put_line(emp_rec.ename||'的薪水是'||emp_rec.sal); 
 
end; 
/ 

PL/SQL中的IF语句

IF   条件  THEN 语句1; 
语句2; 
END IF; 

IF  条件  THEN  语句序列1；    
ESLE   语句序列 2； 
END   IF； 

 IF   条件  THEN 语句; 
 ELSIF  语句  THEN  语句; 
 ELSE    语句; 
 END  IF; 

--判断用户从键盘上输入的数字 
 
set serveroutput on 
 
--键盘输入 
--num 地址值，在该地址上，保存了键盘输入的值 
accept num prompt '请输入一个数字'; 
 
declare 
  --定义变量保存输入的数字 
  pnum number := # 
begin 
  --判断 
  if pnum = 0 then dbms_output.put_line('您输入的是0'); 
    elsif pnum = 1 then dbms_output.put_line('您输入的是1'); 
    elsif pnum = 2 then dbms_output.put_line('您输入的是2'); 
    else dbms_output.put_line('其他数字'); 
  end if; 
 
end; 
/

PL/SQL中的循环语句

Loop 
EXIT [when   条件]; 
…… 
End loop 

FOR   I   IN   1 . . 3    LOOP 
语句序列 ; 
END    LOOP ;  

--打印1~10 
 
 
set serveroutput on 
 
declare 
  pnum number := 1; 
begin 
 
  loop 
    --退出条件 
    exit when pnum > 10; 
 
    dbms_output.put_line(pnum); 
 
    --加一 
    pnum := pnum + 1; 
 
  end loop; 
end; 
/

光标 Cursor
说明光标语法：

CURSOR  光标名  [ (参数名  数据类型[,参数名 数据类型]...)] 
      IS  SELECT   语句;

用于存储一个查询返回的多行数据

打开光标： open c1; (打开光标执行查询)
取一行光标的值：fetch c1 into pjob; (取一行到变量中)
关闭光标： close c1;(关闭游标释放资源)

注意： 上面的pjob必须与emp表中的job列类型一致：

定义：pjob emp.empjob%type;

--使用游标查询员工姓名和工资，并打印 
 
set serveroutput on 
 
/* 
1. 光标的属性 
%isopen 是否打开 
%rowcount 记录数 
%notfound 没有记录 
 
2.  
SQL> show parameters cursor 
 
NAME                                 TYPE        VALUE 
------------------------------------ ----------- --------- 
cursor_sharing                       string      EXACT 
cursor_space_for_time                boolean     FALSE 
open_cursors                         integer     300 
session_cached_cursors               integer     20 
修改300 
alter system set open_cursors=400 scope=both; 
 
*/ 
declare 
  --定义光标  
  cursor cemp is select ename,sal from emp; 
  pename emp.ename%type; 
  psal   emp.sal%type; 
begin 
  open cemp; 
  loop 
    --取一条记录 
    fetch cemp into pename,psal; 
 
    --退出: 光标中没有值 
    exit when cemp%notfound; 
 
    --打印 
    dbms_output.put_line(pename||'的薪水是'||psal); 
 
  end loop; 
  close cemp; 
end; 
/ 

带参数的光标

--查询某个部门中员工的姓名 
set serveroutput on 
 
declare 
  --定义带参数的光标 
  cursor cemp(dno number) is select ename from emp where deptno=dno; 
  pename emp.ename%type; 
begin 
  open cemp(20); 
  loop 
    fetch cemp into pename; 
    --退出 
    exit when cemp%notfound; 
 
    --打印 
    dbms_output.put_line(pename); 
 
  end loop; 
  close cemp; 
end; 
/

Oracle的异常处理
1.系统定义例外
No_data_found (没有找到数据)
Too_many_rows (select …into语句匹配多个行)
Zero_Divide ( 被零除)
Value_error (算术或转换错误)
Timeout_on_resource (在等待资源时发生超时)

--被0除 
 
set serveroutput on  
declare 
  pnum number; 
begin 
  pnum := 1/0; 
 
exception 
  when Zero_Divide  then dbms_output.put_line('1:0不能做被除数'); 
                         dbms_output.put_line('2:0不能做被除数'); 
  when Value_error then dbms_output.put_line('算术或转换错误');                           
  when others then dbms_output.put_line('其他例外');   
end; 
/

2.用户定义的例外

--查询50号部门的员工姓名 
set serveroutput on 
 
declare 
  --定义光标代表50号部门的员工 
  cursor cemp is select ename from emp where deptno=50; 
  pename emp.ename%type; 
 
  --自定义例外 
  no_emp_found exception; 
begin 
  open cemp; 
 
  --取一个员工 
  fetch cemp into pename; 
 
  if cemp%notfound then 
    --抛出例外 
    raise no_emp_found; 
  end if; 
 
  --如果产生例外，自动关闭 
  close cemp; 
 
exception 
  when no_emp_found then dbms_output.put_line('没有找到员工'); 
  when others then dbms_output.put_line('其他例外');     
end; 
/ 
 
 
 
 
 
 

demo

--给员工涨工资  总裁1000 经理800 其他400 
 
set serveroutput on 
 
declare 
  --定义光标 
  cursor cemp is select empno,empjob from emp; 
  pempno emp.empno%type; 
  pjob   emp.empjob%type; 
begin 
  rollback; 
 
  open cemp; 
  loop 
    --取一个员工 
    fetch cemp into pempno,pjob; 
    exit when cemp%notfound; 
 
    --判断职位 
    if pjob = 'PRESIDENT' then update emp set sal=sal+1000 where empno=pempno; 
      elsif pjob = 'MANAGER' then update emp set sal=sal+800 where empno=pempno; 
      else update emp set sal=sal+400 where empno=pempno; 
    end if; 
 
  end loop; 
  close cemp; 
 
  --提交 
  commit; 
 
  dbms_output.put_line('完成'); 
end; 
/