Pl/SQL 编程

原创

一品堂_技术学习笔记 2021-07-16 15:43:39 ©著作权

©著作权归作者所有：来自51CTO博客作者一品堂_技术学习笔记的原创作品，请联系作者获取转载授权，否则将追究法律责任

Pl/SQL 编程

一：前言

二：Pl/Sql 概述

二 —— 1： Pl/Sql块结构

  1 【declare】
  2 --声明部分，可选
  3 begin
  4 --执行部分，必须
  5 [exception]
  6 --异常处理部分，可选
  7 end

  1 SQL> set serveroutput on;
  2 SQL>
  3 SQL> declare
  4   2  a int:=10;
  5   3  b int:=200;
  6   4  c number;
  7   5  begin
  8   6    c:=(a+b)/(a-b);
  9   7    dbms_output.put_line(c);
 10   8  exception
 11   9     when zero_divide then
 12  10       dbms_output.put_line('除数不许为零');
 13  11   end;
 14  12  /
 15 
 16 -1.10526315789473684210526315789473684211
 17 
 18 PL/SQL procedure successfully completed
 19 
 20 SQL>

View Code

二 —— 2：代码注释和标识符

二 —— 2_____1：单行注释

  1 SQL>  set serveroutput on;       --在服务器端 输出结果
  2 SQL>  declare
  3   2
  4   3     Num_sal number;          --- 声明一个数值变量
  5   4     Var_ename varchar(20);   --- 声明一个字符串变量
  6   5   begin
  7   6      select e.ename,e.sal into Var_ename,Num_sal  from emp e where empno=7839;  --检索指定的值并储存到变量中
  8   7      dbms_output.put_line(Var_ename||'工资是'||Num_sal);
  9   8  end;
 10   9
 11  10
 12  11  /
 13 
 14 KING工资是5000
 15 
 16 PL/SQL procedure successfully completed

View Code

二 —— 2_____2：多行注释

  1 set serveroutput on;      /*在服务器端 输出结果*/
  2  declare
  3    Num_sal number;
  4    Var_ename varchar2(20);
  5  begin
  6    /*检索指定的值并储存到变量中*/
  7       select e.ename,e.sal into Var_ename,Num_sal  from emp e where empno=7839;  --检索指定的值并储存到变量中
  8     dbms_output.put_line(Var_ename||'工资是'||Num_sal);
  9 end;
 10 /

View Code

二 —— 2_____3：PL/SQL字符集

三：数据类型与定义变量和常量

三 —— 1：基本数据类型

三 —— 1_____1：数值类型

三 —— 1_____2：字符类型

三 —— 1_____3：日期类型

三 —— 1_____4：布尔类型

三 —— 2 ：特殊数据类型

三 —— 2_____1： %TYPE 类型

  1 SQL>  set serveroutput on      /*在服务器端 输出结果*/
  2 SQL>  declare
  3   2     var_ename emp.ename%type; /*声明与ename 列类型相同的变量*/
  4   3     var_job   emp.job%type;   /*声明与job列类型相同的变量*/
  5   4   begin
  6   5      select ename,job into var_ename,var_job from emp where empno=7839 ;/*检索数据，并保存在变量中*/
  7   6      dbms_output.put_line(var_ename||'工资是'||var_job);
  8   7  end;
  9   8  /
 10 
 11 KING工资是PRESIDENT
 12 
 13 PL/SQL procedure successfully completed

View Code

三 —— 2_____2： record 类型

  1 set serveroutput on  /**/
  2 declare
  3   type emp_type is record
  4    (
  5     var_ename varchar2(20),  /*定义字段--成员变量  */
  6     var_job varchar2(20),
  7     var_sal number
  8    );
  9    empinfo emp_type;  /*定义变量*/
 10 begin
 11   select ename,job,sal into empinfo from emp where empno=7839 ;/*检索数据*/
 12     dbms_output.put_line( '雇员'||empinfo.var_ename||'的职位是'||empinfo.var_job||'、工资是'||empinfo.var_sal);
 13 
 14 end;
 15 /

View Code

三 —— 2_____3： %rowtype 类型

  1 SQL> set serveroutput  on
  2 SQL> declare
  3   2       rowVar_emp emp%rowtype;/*定义能够储存emp表中一行数据的变量 rowVar_emp*/
  4   3  begin
  5   4    select * into rowVar_emp from emp where empno=7839 ;/*检索数据*/
  6   5      dbms_output.put_line( '雇员'||rowVar_emp.ename||'的职位是'||rowVar_emp.job||'、工资是'||rowVar_emp.sal);
  7   6
  8   7  end;
  9   8  /
 10 
 11 雇员KING的职位是PRESIDENT、工资是5000
 12 
 13 PL/SQL procedure successfully completed
 14 
 15 SQL>

View Code

三 —— 3：定义变量和常量

四：流程控制语句

四 —— 1：选择语句 if …then 语句

  1 SQL> set serveroutput on
  2 SQL> declare
  3   2    var_name1 varchar2(50); --//定义2个字符串变量
  4   3    var_name2 varchar2(50);
  5   4
  6   5  begin
  7   6    var_name1 :='East';    --//给2个变量赋值
  8   7    var_name2 :='xiaoke';
  9   8
 10   9    if length(var_name1)<length(var_name2) then
 11  10      /*输出比较后的结果*/
 12  11      dbms_output.put_line('字符串 “'||var_name1||'”的长度比字符串“'||var_name2||'”的长度小');
 13  12     end if;
 14  13   end;
 15  14  /
 16 
 17 字符串 “East”的长度比字符串“xiaoke”的长度小
 18 
 19 PL/SQL procedure successfully completed
 20 
 21 SQL>

View Code

四 ——1_____ 2：选择语句 if …then … else 语句

  1 SQL> set serveroutput on
  2 SQL> declare
  3   2   age int:=55;/*定义整型变量并赋值*/
  4   3  begin
  5   4    if age >=56 then
  6   5      dbms_output.put_line('您可以申请退休了！');
  7   6    else
  8   7       dbms_output.put_line('您小于56岁，不可以申请退休了！');
  9   8    end if;
 10   9  end;
 11  10  /
 12 
 13 您小于56岁，不可以申请退休了！
 14 
 15 PL/SQL procedure successfully completed
 16

View Code

四 ——1_____3：选择语句 if …then … elseif 语句

  1 SQL> set serveroutput on
  2 SQL> declare
  3   2   month int :=10; /*声明整型变量并赋值*/
  4   3
  5   4  begin
  6   5     if month >=0 and month<=3 then /*判断春节*/
  7   6      dbms_output.put_line('这是春季');
  8   7     elsif month >=4 and month <=6 then /*片段夏季*/
  9   8      dbms_output.put_line('这是夏季');
 10   9     elsif month >=7 and month<=9 then
 11  10      dbms_output.put_line('这是秋季');
 12  11     elsif month >=10 and month<=12 then
 13  12      dbms_output.put_line('这是冬季');
 14  13     else
 15  14      dbms_output.put_line('对不起，月份不合法！');
 16  15     end if;
 17  16  end;
 18  17  /
 19 
 20 这是冬季
 21 
 22 PL/SQL procedure successfully completed
 23 
 24 SQL>

View Code

四 ——1_____4：选择语句 case 语句

  1 SQL> set serveroutput on
  2 SQL> declare
  3   2   season int:=3;/*定义整型变量并赋值*/
  4   3   aboutInfo varchar2(50);/*声明月份信息*/
  5   4  begin
  6   5      case season  /*片段月份*/
  7   6      when 1 then
  8   7            aboutInfo := season || '季度包括 1,2,3月份';
  9   8       when 2 then
 10   9             aboutInfo := season || '季度包括 4,5,6月份';
 11  10       when 3 then
 12  11             aboutInfo := season || '季度包括 7,8,9月份';
 13  12       when 4 then
 14  13             aboutInfo := season || '季度包括 10,11,12月份';
 15  14       else
 16  15             aboutInfo := season || ' 季度不合法';
 17  16       end case ;
 18  17
 19  18       dbms_output.put_line(aboutInfo);
 20  19
 21  20  end;
 22  21  /
 23 
 24 3季度包括 7,8,9月份
 25 
 26 PL/SQL procedure successfully completed
 27 
 28 SQL>

View Code

四 ——2：循环语句

四 ——2_____1： looｐ　语句

  1 set serveroutput on
  2 declare
  3   sum_i int:=0; /*定义整数变量，存储整数和*/
  4   i int:=0;/*定义整数变量 储存自然数*/
  5 begin
  6   loop   /*循环累加自然数*/
  7     i:= i+1; /*得出自然数*/
  8     sum_i := sum_i + i;/*计算前n个自然数的和*/
  9     exit when i=100;  /*当循环100次后，程序退出循环体*/
 10   end loop;
 11    dbms_output.put_line('前100个自然数的和是'||sum_i);/*计算前100个自然数的和*/
 12 end;
 13 /
 14

View Code

四 ——2_____２：　ｗｈｉｌｅ　语句

  1 set serveroutput on
  2 declare
  3   sum_i int:=0;/*定义变量并赋值*/
  4   i int :=0;/*定义变量并赋值*/
  5 begin
  6   while i<99 loop
  7      i:=i+1;/*当i的值等于100时，程序退款while循环*/
  8     sum_i :=sum_i +i;/*计算前n个自然数的和*/
  9   end loop;
 10    dbms_output.put_line('前100个自然数的和是'||sum_i);/*计算前100个自然数的和*/
 11 end;
 12 /

View Code

四 ——2_____３：　ｆｏｒ　语句

  1 set serveroutput on
  2 declare
  3   sum_i int :=0;/*定义变量并赋值*/
  4 begin
  5    for i in  1..100 loop /*遍历前100个自然数*/
  6      if mod(i,2)=0 then /*判断是否为偶数*/
  7             sum_i:=sum_i +i; /*计算偶数和*/
  8      end if;
  9    end loop;
 10    dbms_output.put_line('前100个自然数的和是'||sum_i);/*计算前100个自然数的和*/
 11 end;
 12 /

View Code

五：Ｐｌ／Ｓｑｌ　游标

五 —1：显示游标　

五 ——1_____1：声明游标　

五 ——1_____2：打开游标　

五 ——1_____3：读取游标　

  1 
  2 set serveroutput on
  3 declare
  4   /*声明游标，检索雇员信息*/
  5   cursor cur_emp(var_job varchar2 := 'SALESMAN') is
  6     select empno, ename, sal from emp where job = var_job;
  7   type record_emp is record /*声明一个记录类型 record 类型*/
  8   (
  9     /*定义当前记录的成员变量*/
 10     var_empno emp.empno%type,
 11     var_ename emp.ename%type,
 12     var_sal   emp.sal%type);
 13   emp_row record_emp; /*声明一个record_emp 类型变量*/
 14 begin
 15   open cur_emp('MANAGER'); /*打开游标*/
 16   fetch cur_emp
 17     into emp_row; /*先让指针指向结果集中的第一行，并将值保存到emp_row 中*/
 18   while cur_emp%found loop
 19     dbms_output.put_line(emp_row.var_ename || '的编号是' || emp_row.var_empno ||
 20                          '，工资是' || emp_row.var_sal);
 21 
 22     fetch cur_emp
 23       into emp_row; /*让指针指向结果集的下一行，并将值保存到emp_row中*/
 24   end loop;
 25   close cur_emp; /*关闭游标*/
 26 end;
 27 /

View Code

五 ——1_____4：关闭游标　

五 ——2：游标的属性

  1 set serveroutput on
  2 declare
  3    var_ename varchar2(50);/*声明变量，用来储存雇员名称*/
  4    var_job   varchar2(50);/*声明变量，用来储存雇员的职务*/
  5    /*声明游标，检索指定员工编号的雇员信息*/
  6   cursor cur_emp /*定义游标，检索指定编号的记录信息*/
  7    is select ename ,job from emp where empno=7499;
  8 begin
  9    open cur_emp;/*打开游标*/
 10    fetch cur_emp into var_ename,var_job ;/*读取游标，并且储存雇员名称和职务*/
 11      if cur_emp%found then /*若检索到数据记录，则输出雇员信息*/
 12        dbms_output.put_line('编号是7499的雇员名称为'||var_ename||',职务是：'||var_job);
 13       else
 14         dbms_output.put_line('无数据记录');/*提示无记录信息*/
 15       end if;
 16 end;
 17 /

View Code

五 ——3：隐式游标

  1 /*在scott 用户下，把emp 表中销售员（即：SALESMAN）的工资上调20% 然后使用隐式游标sql的%rowcount属性输出上调工资的员工数量*/
  2 set serveroutput on
  3 begin
  4   update emp set sal=sal*(1+0.2) where job='SALESMAN';/*把销售员工的工资上调20%*/
  5   if sql%notfound then
  6      dbms_output.put_line(' 没有雇员需要上调工资');
  7   else
  8      dbms_output.put_line('有'||sql%rowcount||'个雇员工资上调20%');
  9   end if;
 10 end;
 11 /

View Code

五 ——3：通过for 语句循环游标

  1 /*使用隐式游标和for语句检索出职务是销售员的雇员信息并输出*/
  2 set serveroutput on
  3 begin
  4    for emp_record in(select empno,ename,sal from emp where job='SALESMAN') /*遍历隐式游标中的记录*/
  5    loop
  6         dbms_output.put_line('雇员编号：'|| emp_record.empno); /*输出雇员编号*/
  7         dbms_output.put_line('雇员名称：'|| emp_record.ename);/*输出雇员名称*/
  8         dbms_output.put_line('雇员工资：'|| emp_record.sal); /*输出雇员工资*/
  9    end loop;
 10 end;
 11 /

View Code

  1 /*使用显示游标 和 for 语句检索出部门编号是30的雇员信息并输出 */
  2 set serveroutput on
  3 declare
  4   cursor cur_emp is select * from emp where deptno =30;  /*检索部门编号为30的雇员信息*/
  5 begin
  6   for emp_rocord in cur_emp/*遍历雇员信息*/
  7    loop
  8         dbms_output.put_line('雇员编号：'|| emp_rocord.empno); /*输出雇员编号*/
  9         dbms_output.put_line('雇员名称：'|| emp_rocord.ename);/*输出雇员名称*/
 10         dbms_output.put_line('雇员职务：'|| emp_rocord.job); /*输出雇员工资*/
 11    end loop;
 12 end;
 13 /

View Code

六： PL/SQL 异常处理

六 ——1_____1：预定义异常

  1 /*使用select into语句检索emp 表中部门编号为10 的雇员编号记录信息  然后使用too_many_rows一定*/
  2 
  3 
  4 set serveroutput on
  5 declare
  6    var_empno number ;/*定义变量，储存雇员编号*/
  7    var_ename varchar2(50);/*定义变量，储存雇员名称*/
  8 begin
  9    select empno,ename into var_empno,var_ename from  emp where deptno=10;/*查询部门编号为10的雇员的信息*/
 10  if sql%found then /*如果检索成功，则输出雇员信息*/
 11    dbms_output.put_line('雇员编号：'||var_empno||';雇员名称:'||var_ename);
 12  end if;
 13 exception   /*捕获异常*/
 14    when too_many_rows then    /*若 select into 语句返回的记录超过一行*/
 15     dbms_output.put_line('返回记录超出一行');
 16    when no_data_found then   /*若select into  语句的返回结果为0行*/
 17      dbms_output.put_line('无数据记录');
 18 end;
 19 /

View Code

六 ——2：自定义异常

六 ——2_____1：错误编号异常

  1 
  2 set seroutput on
  3 declare
  4  primary_iterant exception;/*定义野怪异常变量*/
  5  pragma exception_init(primary_iterant,-00001);/*关联错误号 和 异常变量名*/
  6 begin
  7   /*向dept表中插入一条与已有主键值重复的记录，以便引发异常*/
  8   insert into dept values(10,'rrr','rrr');
  9 exception
 10    when primary_iterant then /*若Oracle系列捕获到的异常为-000001异常*/
 11    dbms_output.put_line('主键不允许重复！'); /*输出异常描述信息*/
 12 end;
 13 /

View Code

六 ——2_____2：业务逻辑异常

  1 set serveroutput on
  2 declare
  3     null_exception exception ;/*声明一个exception 类型的异常变量*/
  4     dept_row dept%rowtype;   /*声明rowtype 类型的变量 dept_now*/
  5 begin
  6    dept_row.deptno :=66; /*给部门编号变量赋值*/
  7    dept_row.dname := '公关部';/*给部门名称变量赋值*/
  8    insert into  dept values(dept_row.deptno,dept_row.dname,dept_row.loc);/*向dept表插入一条记录*/
  9    if dept_row.loc is null then
 10       raise null_exception; /*引发 null 异常 程序进入exception部分*/
 11    end if;
 12 exception
 13     when null_exception then    /*当 raise 引发的异常是 null_exception 时*/
 14         dbms_output.put_line('loc 字段的值不允许为null');     /*则输出异常提示信息*/
 15         rollback; /*回滚插入的数据记录*/
 16 end ;
 17 /

View Code

六 ——2_____3： Oracle存储过程的异常处理

注：本段内容来源于：《Oracle存储过程的异常处理》

1、为了提高存储过程的健壮性，避免运行错误，当建立存储过程时应包含异常处理部分。
2、异常(EXCEPTION)是一种PL/SQL标识符，包括预定义异常、非预定义异常和自定义异常；
3、预定义异常是指由PL/SQL提供的系统异常；非预定义异常用于处理与预定义异常无关的Oracle错误(如完整性约束等)；自定义异常用于处理与Oracle错误的其他异常情况。
4、RAISE_APPLICATION_ERROR用于自定义错误消息，并且消息号必须在－20000～－20999之间

命名的系统异常     产生原因
access_into_null    未定义对象
case_not_found     case中若未包含相应的when，并且没有设置
collection_is_null    集合元素未初始化
curser_already_open    游标已经打开
dup_val_on_index    唯一索引对应的列上有重复的值
invalid_cursor     在不合法的游标上进行操作
invalid_number    内嵌的 sql 语句不能将字符转换为数字
no_data_found     使用 select into 未返回行，或应用索引表未初始化的
too_many_rows     执行 select into 时，结果集超过一行
zero_divide     除数为 0
subscript_beyond_count    元素下标超过嵌套表或varray的最大值
subscript_outside_limit   使用嵌套表或 varray 时，将下标指定为负数
value_error     赋值时，变量长度不足以容纳实际数据
login_denied     pl/sql 应用程序连接到 oracle 数据库时，提供了不正确的用户名或密码      确的用户名或密码
not_logged_on     pl/sql 应用程序在没有连接 oralce 数据库的情况下访问数据      问数据
program_error     pl/sql 内部问题，可能需要重装数据字典＆ pl./sql系统包     统包
rowtype_mismatch          主游标变量与 pl/sql 游标变量的返回类型不兼容
self_is_null     使用对象类型时，在 null 对象上调用对象方法
storage_error     运行 pl/sql 时，超出内存空间
sys_invalid_id     无效的 rowid 字符串
timeout_on_resource    oracle 在等待资源时超时
  1 --自定义异常处理
  2 CREATE OR REPLACE PROCEDURE stu_proc
  3 (
  4 --多个用逗号隔开
  5 v_id IN NUMBER
  6 ) IS
  7 --多个用分号隔开
  8 v_max_id NUMBER;
  9 v_name VARCHAR2(20);
 10 v_raise EXCEPTION;
 11 BEGIN
 12 SELECT MAX(a.id) INTO v_max_id FROM student a;
 13 IF v_id>v_max_id THEN
 14 RAISE v_raise;
 15 END IF;
 16 SELECT o.sname INTO v_name FROM student o WHERE o.id=v_id;
 17 dbms_output.put_line('学生名称为：'||v_name);
 18 EXCEPTION
 19 WHEN v_raise THEN
 20 RAISE_APPLICATION_ERROR(-20010, 'v_id not exists!');
 21 WHEN NO_DATA_FOUND THEN
 22 RAISE_APPLICATION_ERROR(-20011, 'ERROR：不存在！');
 23 END stu_proc;
——————————————

——————————————————————————————————————————————————————————

为人：谦逊、激情、博学、审问、慎思、明辨、笃行
学问：纸上得来终觉浅，绝知此事要躬行
为事：工欲善其事，必先利其器。
转载请标注出处！

上一篇：Pl/SQL 编程

下一篇：SQL语言基础

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯

Pl/SQL 编程

Pl/SQL 编程

Pl/SQL 编程

一：前言

二：Pl/Sql 概述

二 —— 1： Pl/Sql块结构

二 —— 2： 代码注释和标识符

二 —— 2_____1：单行注释

二 —— 2_____2：多行注释

二 —— 2_____3：PL/SQL字符集

三：数据类型与定义变量和常量

三 —— 1：基本数据类型

三 —— 1_____1：数值类型

三 —— 1_____2：字符类型

三 —— 1_____3：日期类型

三 —— 1_____4：布尔类型

三 —— 2 ：特殊数据类型

三 —— 2_____1： %TYPE 类型

三 —— 2_____2： record 类型

三 —— 2_____3： %rowtype 类型

三 —— 3： 定义变量和常量

四 ：流程控制语句

四 —— 1：选择语句 if …then 语句

四 ——1_____ 2：选择语句 if …then … else 语句

四 ——1_____3：选择语句 if …then … elseif 语句

四 ——1_____4：选择语句 case 语句

四 ——2：循环语句

四 ——2_____1： looｐ 语句

四 ——2_____２： ｗｈｉｌｅ 语句

四 ——2_____３： ｆｏｒ 语句

五 ：Ｐｌ／Ｓｑｌ 游标

五 —1：显示游标

五 ——1_____1：声明游标

五 ——1_____2：打开游标

五 ——1_____3： 读取游标

五 ——1_____4： 关闭游标

五 ——2： 游标的属性

五 ——3： 隐式游标

五 ——3： 通过for 语句循环游标

六 ： PL/SQL 异常处理

六 ——1_____1： 预定义异常

六 ——2： 自定义异常

六 ——2_____1： 错误编号异常

六 ——2_____2： 业务逻辑异常

六 ——2_____3： Oracle存储过程的异常处理

51CTO博客

二 —— 2：代码注释和标识符

三 —— 3：定义变量和常量

四：流程控制语句

四 ——2_____1： looｐ　语句

四 ——2_____２：　ｗｈｉｌｅ　语句

四 ——2_____３：　ｆｏｒ　语句

五：Ｐｌ／Ｓｑｌ　游标

五 —1：显示游标　

五 ——1_____1：声明游标　

五 ——1_____2：打开游标　

五 ——1_____3：读取游标　

五 ——1_____4：关闭游标　

五 ——2：游标的属性

五 ——3：隐式游标

五 ——3：通过for 语句循环游标

六： PL/SQL 异常处理

六 ——1_____1：预定义异常

六 ——2：自定义异常

六 ——2_____1：错误编号异常

六 ——2_____2：业务逻辑异常