Oracle学习(十一):PL/SQL

作者:v123411739 和数据库相关  

1.知识点:可以对照下面的录屏进行阅读

PL/SQL程序结构
declare
      说明部分    (变量说明,光标申明,例外说明 〕
begin
      语句序列   (DML语句〕… 
exception
      例外处理语句   
End;
/
------------------------------------------------------------------
--第一个PL/SQL程序:HelloWorld
set serveroutput on	--如果要在屏幕上输出信息,需要将serveroutput开关打开
declare
  --变量的说明
begin
   --程序体
   --程序包package
   dbms_output.put_line('Hello World');
end;
/
------------------------------------------------------------------

--引用型变量: 查询并打印7839的姓名和薪水

set serveroutput on

declare 
  --定义变量,
  pename emp.ename%type;		--将pename的类型设置为emp表中ename的类型
  psal   emp.sal%type;	--将psal的类型设置为emp表中sal的类型

begin

  --查询: 
  --ename,sal into pename,psal:将查询的ename,sal分别赋值给pename,psal按顺序赋值
  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
  --得到一行,赋值给emp_rec
  select * into emp_rec from emp where empno=7839;

  dbms_output.put_line(emp_rec.ename||'的薪水是'||emp_rec.sal);

end;
/
------------------------------------------------------------------
--if语句:判断用户输入的数字

set serveroutput on

/*
1.提示信息
2. 接收键盘输入
num 是一个地址值

SQL优化: num绑定变量(尽量使用绑定变量)
select * from emp where deptno=10;  --> 执行计划
select * from emp where deptno=20;  --> 执行计划
-->
select * from emp where deptno=#

*/
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;
/
------------------------------------------------------------------
--循环: 打印1~10,有3中方法,见下图

set serveroutput on

declare
  pnum number := 1;	--给pnum赋值1,pnum类型为number
begin
  loop
    --退出:成立退出,不成立循环
    exit when pnum > 10;
    
    --隐式转换,number转varchar2
    dbms_output.put_line(pnum);
    
    pnum := pnum + 1;

  end loop;
end;
/
------------------------------------------------------------------

光标(Cursor)==ResultSet

说明光标语法:
CURSOR  光标名  [ (参数名  数据类型[,参数名 数据类型]...)]
      IS  SELECT   语句;
用于存储一个查询返回的多行数据
例如:
cursor c1 is select ename from emp;
打开光标:      open c1;    (打开光标执行查询)
取一行光标的值:fetch c1 into pjob; (取一行到变量中)
关闭光标:      close  c1;(关闭游标释放资源)
注意: 上面的pjob必须与emp表中的job列类型一致:
定义:pjob emp.empjob%type;
------------------------------------------------------------------
--光标: 使用游标查询员工姓名和工资,并打印

/*
光标的3个属性:
%isopen 是否被打开:打开true,关闭false;
%rowcount 行数
%notfound 是否有值:没有值true,有值false;
*/

set serveroutput on

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;
/
------------------------------------------------------------------
--光标练习:给员工涨工资  总裁1000 经理800 其他400
--光标同时能打开的最大数量默认为300个
/*
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

*/
set serveroutput on
declare
  --光标代表员工
  cursor cemp is select empno,job from emp;
  pempno emp.empno%type;
  pjob   emp.job%type;
begin
  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;	--总裁涨1000
      elsif pjob = 'MANAGER' then update emp set sal=sal+800 where empno=pempno; --经理涨800
      else update emp set sal=sal+400 where empno=pempno;	--其他涨400
    end if;
  end loop;
  close cemp;
  
  --提交: 隔离级别
  commit;
  
  dbms_output.put_line('完成');
end;
/
------------------------------------------------------------------
--带参数的光标:查询某个部门的员工姓名 

set serveroutput on

declare
  --定义一个带参数的光标
  cursor cemp(pdno number) is  select ename from emp where deptno=pdno;
  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      (在等待资源时发生超时)

--举例 Zero_Divide   ( 被零除)

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

用户自己定义的例外
--自定义例外: 查询50号部门的员工姓名

set serveroutput on

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

/*
  if cemp%isopen then 
    close 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;
/




图:循环的3种写法,文中采用右上角的方法。

2.在Sqlplus下实际执行的结果录屏

SQL> --第一个PL/SQL程序:HelloWorld
SQL> set serveroutput on	--如果要在屏幕上输出信息,需要将serveroutput开关打开
SQL> declare
  2    --变量的说明
  3  begin
  4     --程序体
  5     --程序包package
  6     dbms_output.put_line('Hello World');
  7  end;
  8  /
 
Hello World
 
PL/SQL procedure successfully completed
 
SQL> 
SQL> --引用型变量: 查询并打印7839的姓名和薪水
SQL> set serveroutput on
SQL> declare
  2    --定义变量,
  3    pename emp.ename%type;		--将pename的类型设置为emp表中ename的类型
  4    psal   emp.sal%type;	--将psal的类型设置为emp表中sal的类型
  5  
  6  begin
  7  
  8    --查询:
  9    --ename,sal into pename,psal:将查询的ename,sal分别赋值给pename,psal按顺序赋值
 10    select ename,sal into pename,psal from emp where empno=7839;
 11  
 12    --打印
 13    dbms_output.put_line(pename||'的薪水是'||psal);
 14  
 15  end;
 16  /
 
KING的薪水是5000
 
PL/SQL procedure successfully completed
 
SQL> 
SQL> -记录型变量 查询并打印7839的姓名和薪水
SQL> set serveroutput on
SQL> declare
  2    --定义变量,代表一行
  3    emp_rec emp%rowtype;
  4  begin
  5    --得到一行,赋值给emp_rec
  6    select * into emp_rec from emp where empno=7839;
  7  
  8    dbms_output.put_line(emp_rec.ename||'的薪水是'||emp_rec.sal);
  9  
 10  end;
 11  /
 
KING的薪水是5000
 
PL/SQL procedure successfully completed
 
SQL> 
SQL> --if语句:判断用户输入的数字
SQL> set serveroutput on
SQL> /*
  2  1.提示信息
  3  2. 接收键盘输入
  4  num 是一个地址值
  5  
  6  SQL优化: num绑定变量(尽量使用绑定变量)
  7  select * from emp where deptno=10;  --> 执行计划
  8  select * from emp where deptno=20;  --> 执行计划
  9  -->
 10  select * from emp where deptno=#
 11  
 12  */
 13  accept num prompt '请输入一个数字';
SQL> declare
  2    --变量保存输入的数字
  3    pnum number  := #
  4  begin
  5    --判断
  6    if pnum = 0 then
  7      dbms_output.put_line('您输入的是0');
  8    elsif pnum = 1 then
  9      dbms_output.put_line('您输入的是1');
 10    elsif pnum = 2 then
 11      dbms_output.put_line('您输入的是2');
 12    else
 13      dbms_output.put_line('其他数字');
 14    end if;
 15  end;
 16  /
 
您输入的是2
 
PL/SQL procedure successfully completed
 
SQL> 
SQL> --循环: 打印1~10
SQL> set serveroutput on
SQL> declare
  2    pnum number := 1;	--给pnum赋值1,pnum类型为number
  3  begin
  4    loop
  5      --退出:成立退出,不成立循环
  6      exit when pnum > 10;
  7  
  8      --隐式转换,number转varchar2
  9      dbms_output.put_line(pnum);
 10  
 11      pnum := pnum + 1;
 12  
 13    end loop;
 14  end;
 15  /
 
1
2
3
4
5
6
7
8
9
10
 
PL/SQL procedure successfully completed
 
SQL> 
SQL> -光标: 使用游标查询员工姓名和工资,并打印
SQL> /*
  2  光标的3个属性:
  3  %isopen 是否被打开:打开true,关闭false;
  4  %rowcount 行数
  5  %notfound 是否有值:没有值true,有值false;
  6  */
SQL> set serveroutput on
SQL> declare
  2    --光标
  3    cursor cemp is select ename,sal from emp;
  4    pename emp.ename%type;		--将用来接收的变量类型设置为和查询的类型相同
  5    psal   emp.sal%type;
  6  begin
  7    open cemp;		--打开光标执行查询
  8    loop
  9      --从集合中取值
 10      fetch cemp into pename,psal;
 11      --光标中没有值时退出
 12      exit when cemp%notfound;
 13  
 14      dbms_output.put_line(pename||'的薪水是'||psal);
 15  
 16    end loop;
 17    close cemp;
 18  end;
 19  /
 
SMITH的薪水是800
ALLEN的薪水是1600
WARD的薪水是1250
JONES的薪水是2975
MARTIN的薪水是1250
BLAKE的薪水是2850
CLARK的薪水是2450
SCOTT的薪水是3000
KING的薪水是5000
TURNER的薪水是1500
ADAMS的薪水是1100
JAMES的薪水是950
FORD的薪水是3000
MILLER的薪水是1300
 
PL/SQL procedure successfully completed
 
SQL> --光标练习:给员工涨工资  总裁1000 经理800 其他400
SQL> --先查询没涨之前的
SQL> select * from emp;
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980/12/17     800.00               20
 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30
 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20
 7839 KING       PRESIDENT       1981/11/17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20
 7900 JAMES      CLERK      7698 1981/12/3      950.00               30
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20
 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10
 
14 rows selected
 
SQL> --执行PLSQL语句
SQL> 
SQL> set serveroutput on
SQL> declare
  2    --光标代表员工
  3    cursor cemp is select empno,job from emp;
  4    pempno emp.empno%type;
  5    pjob   emp.job%type;
  6  begin
  7    open cemp;
  8    loop
  9      fetch cemp into pempno,pjob;
 10      exit when cemp%notfound;
 11  
 12      --判断
 13      if pjob = 'PRESIDENT' then update emp set sal=sal+1000 where empno=pempno;	--总裁涨1000
 14        elsif pjob = 'MANAGER' then update emp set sal=sal+800 where empno=pempno; --经理涨800
 15        else update emp set sal=sal+400 where empno=pempno;	--其他涨400
 16      end if;
 17    end loop;
 18    close cemp;
 19  
 20    --提交: 隔离级别
 21    commit;
 22  
 23    dbms_output.put_line('完成');
 24  end;
 25  /
 
完成
 
PL/SQL procedure successfully completed
 
SQL> --查询执行PLSQL语句后的薪水
SQL> select * from emp;
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980/12/17    1200.00               20
 7499 ALLEN      SALESMAN   7698 1981/2/20     2000.00    300.00     30
 7521 WARD       SALESMAN   7698 1981/2/22     1650.00    500.00     30
 7566 JONES      MANAGER    7839 1981/4/2      3775.00               20
 7654 MARTIN     SALESMAN   7698 1981/9/28     1650.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981/5/1      3650.00               30
 7782 CLARK      MANAGER    7839 1981/6/9      3250.00               10
 7788 SCOTT      ANALYST    7566 1987/4/19     3400.00               20
 7839 KING       PRESIDENT       1981/11/17    6000.00               10
 7844 TURNER     SALESMAN   7698 1981/9/8      1900.00      0.00     30
 7876 ADAMS      CLERK      7788 1987/5/23     1500.00               20
 7900 JAMES      CLERK      7698 1981/12/3     1350.00               30
 7902 FORD       ANALYST    7566 1981/12/3     3400.00               20
 7934 MILLER     CLERK      7782 1982/1/23     1700.00               10
 
14 rows selected
 
SQL> --对比涨之前,可以看出涨工资成功
SQL> 
SQL> --带参数的光标:查询某个部门的员工姓名
SQL> set serveroutput on
SQL> declare
  2    --定义一个带参数的光标
  3    cursor cemp(pdno number) is  select ename from emp where deptno=pdno;
  4    pename emp.ename%type;
  5  begin
  6    open cemp(20);	--打开光标,并传递参数
  7    loop
  8      fetch cemp into pename;
  9      exit when cemp%notfound;
 10  
 11      dbms_output.put_line(pename);
 12  
 13  
 14    end loop;
 15    close cemp;
 16  end;
 17  /
 
SMITH
JONES
SCOTT
ADAMS
FORD
 
PL/SQL procedure successfully completed
 
SQL> 
SQL> --举例 Zero_Divide   ( 被零除)
SQL> set serveroutput on
SQL> declare
  2    pnum number;
  3  begin
  4  
  5    pnum := 1/0;
  6  
  7  exception
  8    when Zero_Divide then dbms_output.put_line('1: 0不能做被除数');
  9                          dbms_output.put_line('2: 0不能做被除数');
 10    when Value_error then dbms_output.put_line('算术错');
 11    when others then dbms_output.put_line('其他例外');
 12  end;
 13  /
 
1: 0不能做被除数
2: 0不能做被除数
 
PL/SQL procedure successfully completed
 
SQL> 
SQL> --自定义例外: 查询50号部门的员工姓名
SQL> set serveroutput on
SQL> declare
  2    cursor cemp is select ename from emp where deptno=50;
  3    pename emp.ename%type;
  4  
  5    --自定义例外
  6    no_emp_found exception;
  7  begin
  8    open cemp;
  9    --取一个员工
 10    fetch cemp into pename;
 11    if cemp%notfound then
 12      raise no_emp_found;
 13    end if;
 14  
 15    close cemp;
 16  
 17  exception
 18    when no_emp_found then dbms_output.put_line('没有找到员工');
 19    when others then dbms_output.put_line('其他例外');
 20  
 21  end;
 22  /
 
没有找到员工
 
PL/SQL procedure successfully completed
 
SQL> spool off
Stopped spooling to c:\PLSQL.txt


相关资料:

Oracle学习(十一):PL/SQL来源网络,如有侵权请告知,即处理!

编程Tags: