Oracle PL/SQL (1) - 编程基础知识

PL/SQL概述

Oracle提供了在数据库服务器端编程的语言，即PL/SQL。而SQL只是一种声明式语言，是非过程性的，语句之间相互独立。在实际工作中，许多事务处理应用都是过程性的，前后语句之间存在一定关联。
PL/SQL是过程化SQL，可以弥补SQL语句的不足。可以编写过程、函数、触发器，而这些存放在oracle数据库中可以在java程序中调用就节省了传输量和解析时间，同时提高了性能。

在Oracle 中使用PL/SQL来扩展SQL的功能，PL/SQL可以通过IF和LOOP语句控制程序的执行流程；可以定义变量以便在语句之间传递数据信息。PL/SQL是对标准SQL语言的扩展， SQL语句完全可以嵌套在PL/SQL程序代码中，将SQL的数据处理能力和PL/SQL的过程处理能力结合在一起。使得SQL能够更加的灵活，功能更加强大，效率更高

本文主要讲述PL/SQL的基本语法、条件判断语句、循环语句。

1、PL/SQL变量的数据类型

number：数字类型，可以代表整数和浮点数。
int：整数型。
pls_integer：整数型，计算速度快（直接由cpu执行），占用较少的存储空间。但产生溢出时将触发异常。
binary_integer：带符号的整数型，不会出现溢出，但计算速度较慢（由oracle模拟执行）。
char：定长字符，最长255个字符。
varchar2：变长字符，最长2000个字符。
long：变长字符，最长2gb。
date：日期型。
boolean：布尔型(true或false)。

2、变量的声明和赋值

1）声明变量的语法
变量名数据类型 := 初值 ;
变量名数据类型 default 初值 ;
A.长度不能超过30个字符，不能有空格。
B.由字母、数字、下划线、美元符号$和#号组成，必须以字母开头。
C.不能使用PL/SQL或SQL的关键字。可以使用help reserved words 命令获得关键字列表。
D.变量默认值为null。
变量命名规范

image.png

2)使用%TYPE声明变量类型
%TYPE用来定义与数据表中指定字段数据类型相同的变量。如果表中字段的数据类型或长度发生变化，%TYPE变量会自动随之变化。这样用户不必查看表中各个字段的数据类型，就可以确保所定义的变量能够正确存储字段数据。
格式为：变量名方案名.表名.字段名%type
如：var_username scott.emp.empname%type; --员工名称

3)使用%ROWTYPE声明变量类型
%ROWTYPE类型可以根据数据表的行结构定义数据类型，用于存储从数据表中检索到的一行完整的数据。如果数据库表的结构发生变化，记录变量中的结构也将随之改变。
格式为：变量名方案名.表名%rowtype
例如：输入职工编号，输出其详细信息

declare
    var_emp  scott.emp%rowtype ; --数据行变量
    var_empno scott.emp.empno%type;
begin
    var_empno:=&var_empno;
    select * into var_emp from scott.emp where  empno=var_empno ;
    dbms_output.put (var_emp.ename) ;
    dbms_output.put_line(' '||var_emp.job) ; 
    dbms_output.put_line(' '||var_emp.sal) ;
end;

4)自定义变量类型
自定义记录数据类型可以表示由多个字段值组成的一行数据。使用时首先要定义记录类型的结构，然后声明该类型的变量存储数据。

type 类型名称 is record (
     变量名称  数据类型 := 默认值 , 
     ……
     变量名称  数据类型 := 默认值 
) ;

例如:自定义员工姓名与工资类型，输入职工编号，输出其姓名与工资

declare
    type employee_type is record (  --声明自定义记录类型
         ename  scott.emp.ename%type, 
         sal  scott.emp.sal%type
);
    var_emp employee_type ; --声明自定义记录类型变量
    var_empno scott.emp.empno%type;
begin
    var_empno:=&var_empno;
    select ename, sal into var_emp from scott.emp where  empno=var_empno ; 
     dbms_output.put (var_emp.ename) ;
     dbms_output.put_line(' '||var_emp.sal) ; 
end;

例如：允许同一记录类型的两个变量整体赋值。

declare
    type employee_type is record ( 
         ename  scott.emp.ename%type, 
         sal  scott.emp.sal%type
);
    var_emp1 employee_type ;
    var_emp2 employee_type ; 
    var_empno scott.emp.empno%type;
begin
    var_empno:=&var_empno;
    select ename, sal into var_emp1 from scott.emp where empno=var_empno ;
    var_emp2:=var_emp1; 
    dbms_output.put (var_emp2.ename) ;
    dbms_output.put_line(' '||var_emp2.sal) ; 
end;

3、PL/SQL的语法结构

　　　　declare 
　　　　　　//这部分用于声明变量
　　　　begin
　　　　　　//这部分用于处理业务逻辑
　　　　exception
　　　　　　//这部分用于处理例外也就是异常情况
　　　　end;

例如：

　declare
　　　　　　　　uname varchar2(10);
　　　　　　　　hello varchar2(10) := 'helloworld'; --为变量赋值
　　　　　　　　vsal emp.sal%type; --声明一个引用类型的变量
　　　　　　　　pall emp%rowtype; --声明一个记录类型的变量
　　　　　　　　digit number:=&num;---提示用户输入，然后根据用户输入执行相应的操作
　　　　　　　　...
　　　　　　begin
　　　　　　　　dbms_output.put_line(hello);
　　　　　　exception
　　　　　　　　when exception_name then
　　　　　　　　　　　//操作
　　　　　　end;

例如：根据用户输入的员工编号获得其工资和奖金

declare
    var_empno number;  --员工编号
    var_sal number;    --工资
    var_comm number;   --奖金
begin
    --接收用户输入的员工编号
    var_empno:=&var_empno;
    --获得工资
    select sal,nvl(comm,0) into var_sal,var_comm from scott.emp where empno=var_empno;
    dbms_output.put_line('员工编号是'||var_empno||'的工资是'||var_sal||'奖金是'||var_comm);
end;

4、PL/SQL的条件判断语句

if语句

　if 条件 then 
　　　　　　语句；
　end if；

if...else语句

　if 条件 then 
　　　　　　语句；
　else 
　　　　　　语句；
　end if；

if...elseif...else语句

　if 条件 then 
　　　　　　语句；
　elsif 条件then 
　　　　　　语句；
　else 
　　　　　　语句；
　end if；

例如：根据输入的数字判断大小

declare
   v_url VARCHAR(50):='www.baidu.com';
   v_num1 NUMBER:= 100;
   v_num2 NUMBER:=200;
   v_num3 NUMBER;
 begin
   dbms_output.put_line('请输入第三个数字：');
   v_num3 :=&num3;
   if v_num1 < v_num3 then
      dbms_output.put_line('第一个字符比第三个字符小');
   end if;
   if v_url like '%baidu%' then
      dbms_output.put_line('网址之中包含baidu单词');
   end if;
 end;

5、case语句

case语句语法为：

case 变量
    when <表达式1> then 语句块1;
    when <表达式2> then 语句块2;
      ……
    when <表达式n> then 语句块n;
   [ else 语句块 n+1 ]
end ;

例如：使用case语句判断数值

 declare
    v_choose NUMBER:=1;
 BEGIN
    case v_choose 
     when 0 then
          dbms_output.put_line('选择的是0');
     when 1 then
          dbms_output.put_line('选择的是1');
     else
          dbms_output.put_line('没有选项满足');
    end case;
 END;

6、PL/SQL的循环语句

在PL/SQL中有三种循环结构
1).loop循环
loop循环语法为：

loop
　语句块；
    exit when 循环结束条件;
　循环语句修改;
end loop;

例如：输出1-99的数。

　　　　　　declare
　　　　　　　　step number := 1;
　　　　　　begin
　　　　　　　　loop
　　　　　　　　　　exit when step > 100;--当step>100退出循环
　　　　　　　　　　dbms_output.put_line(step || ' ');
　　　　　　　　　　step := step + 1;
　　　　　　　　end loop;
　　　　　　end;

2).while...loop循环
while...loop循环语法为：

while 循环结束条件 loop
            语句块；
　　　 循环语句修改;
end loop;

例如：循环输出1-100的数。

　　　　　　declare
　　　　　　　　step number := 1;
　　　　　　begin
　　　　　　　　while step <= 100 loop
　　　　　　　　　　dbms_output.put_line(step);
　　　　　　　　　　step := step + 1;
　　　　　　　　end loop;
　　　　　　end;

3).for语句循环
for语句循环语法为：

　for 循环变量 in n..N loop
　　　　　　语句块；
　　　　end loop;

例如：输出1-100之间的数

　　　　declare
　　　　begin
　　　　　　for i in 1.. 100 loop
　　　　　　dbms_output.put_line(i);
　　　　end loop;
　　　　end;

7、异常处理

PL/SQL程序运行时出现的错误称为异常。可以分为两类：
预定义异常：当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发
用户自定义异常：用户可以声明自定义异常，自定义的异常通过 raise 语句手动引发
系统预定义异常

begin
     过程及sql语句;
exception
     when 异常名称then
          过程及sql语句;
     when others then
          过程及sql语句;
end;

image.png

例如：当在程序中使用0作为除数进行运算时，将触发该异常。

DECLARE
v_test1 NUMBER := 100;
v_test2 NUMBER := 0;
v_test3 NUMBER := 0;
BEGIN
v_test3 := v_test1 / v_test2;
dbms_output.put_line(v_test3);
EXCEPTION
WHEN zero_divide THEN
dbms_output.put_line('0不能作为除数.');
END;
/

例如：当程序中的对象还没有先进行对象初始化的操作，就直接为对象的属性赋值，将触发该异常。

DECLARE
v_test test_type;
BEGIN
v_test.v_name := 'test';
EXCEPTION
WHEN access_into_null THEN
dbms_output.put_line('首先初始化对象v_test');
END;
/

例外函数1： SQLCODE，SQLERRM 在PL/SQL块中出现Oracle错误时,通过使用例外函数可以取得错误号以及相关的错误消息。
SQLCODE 返回Oracle错误号。
SQLERRM 返回错误号对应的错误消息。

begin
  insert into scott.emp(empno , ename , job , sal , deptno)
  values('7999','ATG','CLERK',1500,'ABC');
exception
  when dup_val_on_index then
    dbms_output.put_line('捕获dup_val_on_index异常');
    dbms_output.put_line('该主键值已经存在');
  when others then  -- others子句必须放在各种异常处理的最后
    dbms_output.put_line( '错误号：' || sqlcode );
    dbms_output.put_line( '错误描述：' ||sqlerrm );
end;

备注:others可以捕获所有异常，如果使了其它具体异常则others子句必须放在这些异常处理的最后。

例外函数2：RAISE_APPLICATION_ERROR在存储过程,函数和包中使用RAISE_APPLICATION_ERROR可以自定义错误号和消息。
raise_application_error：用于自定义错误消息(用于程序段中)

语法：
raise_application_error(error_number,message[,{TRUE | FALSE}]);
error_number : 错误号,范围是: -20000 ~ -20999之间的负整数;
message : 错误消息,长度不能超过2048字节;
第三个可靠选参数,如果TRUE,该错误会被放在先前错误堆栈中;如果FALSE(默认),则替换先前所有错误.
例如：

CREATE OR REPLACE PROCEDURE raise_comm
(
eno NUMBER,
commission NUMBER
) IS
v_comm emp.comm%TYPE;
BEGIN
SELECT comm INTO v_comm FROM emp WHERE emp = eno;
IF v_comm IS NULL THEN
raise_application_error(-20001, '该员工无补助.');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('该雇员不存在.');
END;
/

用户自定义异常
自定义异常使用预定义异常和非预定义异常，程序可以捕获Oracle错误，并且在出现Oracle错误时自动触发对应的异常。但是在实际应用中，可能还会遇到其它错误，这时可以为特定的情况自定义异常，不过需要显示触发该异常：通过定义异常，然后关联异常和错误，显示触发异常，最后在EXCEPTION 中处理该异常。
自定义异常的处理步骤：
定义异常处理：异常名 exception ;
触发异常处理：raise 异常名
例如：

--自定义异常  
declare  
        e_wage EXCEPTION;  
        v_wage teachers.wage%type;  
    begin  
            v_wage:=&wage;  
            insert into teachers values (10111,'王彤','教授','01-9月-1990',1000,v_wage,101);  
            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;  
/

最后编辑于：2020.03.28 14:04:37

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 203,772评论 6赞 477
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,458评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 150,610评论 0赞 337
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,640评论 1赞 276
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,657评论 5赞 365
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,590评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,962评论 3赞 395
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,631评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,870评论 1赞 297
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,611评论 2赞 321
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,704评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,386评论 4赞 319
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,969评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,944评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,179评论 1赞 260
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 44,742评论 2赞 349
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,440评论 2赞 342