Oracle-PL/SQL基础

阅读量：4210 次

发布时间：2019-05-26

本文共 15299 字，大约阅读时间需要 50 分钟。

概述

PL/SQL简介

pl/sql（procedural language/sql）是Oracle在标准的sql语言上的扩展，pl/sql不仅允许嵌入Sql语言，还可以定义变量和常量，允许使用条件语句和循环语句，允许使用例外处理各种错误，这样使得它的功能变得更加强大。

pl/sql是面向过程的语言。

这里写图片描述

不同数据库的SQL扩展

PL/SQL 是Oracle数据的SQL扩展。

SQL/PL 是DB2数据库的SQL扩展。

T-SQL 是SQL Server数据库的SQL扩展。

PL/SQL的必要性：

1、提高应用程序的运行性能。

2、模块化的设计思想。

3、减少网络传输量。

4、提高安全性。

PL/SQL编写规范

1、注释

单行注释–

sql>select * from emp where empno=7788; –取得员工信息

多行注释

/………………../

2、标识符号的命名规范

（1）当定义变量时，建议用v_作为前缀 v_sal。

（2）当定义常量时，建议用c_作为前缀 c_rate。

（3）当定义游标时，建议用_cursor作为后缀emp_cursor。

（4）当定义例外时，建议用e_作为前缀 e_error。

PL/SQL块

块（block）是pl/sql的基本程序单元，编写pl/sql程序实际上就是编写pl/sql块。要完成相对简单的应用功能，可能只需要编写一个pl/sql块；但是如果想要实现复杂的功能，可能需要在一个pl/sql块中嵌套其他的pl/sql块。

块结构示意图

pl/sql块由三个部分构成：定义部分、执行部分、例外处理部分。

declear：定义部分是从declare开始的，这部分是可选的。定义常量、变量、游标例外、复杂数据类型。

begin：执行部分是从begin开始的，这部分是必须的。

exception：例外处理部分是exception开始的，该部分可选的。

这里写图片描述

第一个PL/SQL程序

PLSQL中的命令窗口

SQL> set serveroutput on ;--打开输出SQL> declare      --定义部分 ，如果没有定义，declare可以省略。 可选 ，定义常量、变量、游标、例外、复杂数据类型  2  begin      --程序  3  dbms_output.put_line('66666666');  4  end;  5  /66666666PL/SQL procedure successfully completedSQL> /--表示执行上一个PL/SQL块。66666666PL/SQL procedure successfully completed

这里写图片描述

PL/SQL基础语法

程序结构

declare    说明部分（变量说明\游标声明\例外说明）begin    语句序列(DML语句)exception    例外处理语句end ;/

基本变量类型

定义

基本变量类型：

char 、 varchar2、date、number、boolean、long

举例：

var1 char(20);married boolean := false ;psal number(7,2);

栗子

SQL> set serveroutput on ;SQL> SQL> declare  2   -- 定义基本变量类型  3   v_name varchar2(20);--varchar2 字符串类型  4   v_num  number(7,2);--number 数字类型  5   v_date date ;--date 日期类型  6    7   begin  8    9     v_name :='小工匠'; 10     dbms_output.put_line('name:'||v_name); 11   12     v_num :=999; 13     dbms_output.put_line('num:'||v_num); 14   15     v_date :=sysdate; 16     dbms_output.put_line('数据库时间:'||v_date); 17   18     dbms_output.put_line('明天的时间:'||(v_date+1)); 19   end ; 20  /name:小工匠num:999数据库时间:22-JUN-16明天的时间:23-JUN-16PL/SQL procedure successfully completedSQL>

引用型变量 %type

定义

使用%TYPE类型的变量

举例

v_name emp.ename%type ;

栗子

打印7369员工的姓名和薪水信息

SQL> set serveroutput on ;SQL> declare  2  --打印 7369 员工的姓名和薪水信息  3  /* 可以使用基本类型来定义变量的类型，推荐使用引用型变量来定义变量类型*/  4  --vname varchar2(20);  5  --v_sal  number;  6    7  --定义引用变量  8  v_name    emp.ename%type ;  9  v_sal      emp.sal%type; 10  begin 11    --业务操作，变量赋值   (两种赋值方式   1.   v_sal  := 20   第二种   into的方式 ) 12    select ename, sal into v_name, v_sal from emp e where e.empno = 7369; 13    --输出信息 14    dbms_output.put_line(v_name||'的薪水是'||v_sal); 15  end; 16  /SMITH的薪水是800PL/SQL procedure successfully completedSQL>

这里写图片描述

记录型变量 %rowtype

定义

%rowtype 记录型变量代表表中的一行，而一行中有很多列。

举例

emp_rec emp%rowtype

记录型变量分量的引用

手工赋值emp_rec.ename := 'ADMIN' 或者 into 赋值之后， 使用 emp_rec.enmae获取

栗子

SQL> set serveroutput on ;SQL> declare  2  --打印 7369 员工的姓名和薪水信息  3    4  --定义记录型变量 ，得到 7369一行的所有列的信息  5  v_emp_rec  emp%rowtype ;  6  begin  7    --业务sql  8    select * into v_emp_rec from emp a where a.empno=7369;  9    --获取姓名和薪水，并打印 10    dbms_output.put_line(v_emp_rec.ename||'的薪水是'||v_emp_rec.sal); 11  end ; 12  /SMITH的薪水是800PL/SQL procedure successfully completedSQL>

这里写图片描述

if语句的使用

形式一

if 条件 then 语句1;语句2；end if;

形式2

if 条件 then 语句1;else 语句2;end if;

形式3 注意elsif

elsif没有e 且是连在一起的

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

栗子

/*判断用户从键盘输入的数字1、如何使用if语句2、接收一个键盘输入（从键盘上获取的都是字符串）*/set serveroutput on ;--接收一个键盘输入--num ：地址值，含义是：在该地址上保存了输入的值 ，获取地址值上对应的值，需要使用 &accept num prompt'请输入一个数字';declare  --定义变量保存用户从键盘输入的数字  这里并没有对异常进行处理，输入字符串，会抛异常  pnum number := #begin  --执行if 语句进行条件判断      提示信息 不能使用双引号，必须使用单引号，否则报错  if pnum = 0 then dbms_output.put_line('输入的数字是'||pnum);     elsif pnum = 1 then dbms_output.put_line('输入的数字是'||pnum);     elsif pnum = 2 then dbms_output.put_line('输入的数字是'||pnum);     else dbms_output.put_line('其他数字'||pnum);  end if;end;/

这里写图片描述

循环语句的使用

形式1

while 条件  loop     .....end loop;

当条件满足时，执行循环体，不满足时，退出循环体。

set serveroutput on ;declare --定义循环变量  初始值为1    记得加上变量的类型  否则报错 v_num  number  :=1 ;begin   while v_num <=5 loop         --打印 v_num        dbms_output.put_line(v_num);        --变量+1  不能使用v_num++的形式，oracle不支持这种写法        v_num :=v_num+1 ;  end loop;end;/

这里写图片描述

形式2

loopexit [when 条件];.....end loop;

条件成立时，退出循环体，不成立时执行循环体。

set serveroutput on ;declare  --定义循环变量 v_num number :=1 ;begin loop     exit when v_num>10 ; --记得加 标点符号     dbms_output.put_line(v_num);    v_num := v_num+1;    end loop ; end;/

这里写图片描述

推荐使用第二种，因为在控制游标的时候比较方便。

形式3

for i in 1 .. 10 loop     语句;end loop;

set serveroutput on ;declare v_num number :=1 ;begin       for i in 1 .. 5 loop  -- .. 前后可以有空格 也可以没有         dbms_output.put_line(v_num);          v_num := v_num+1;      end loop; end;/

这里写图片描述

游标

游标的概念

游标（游标）：一个结果集

不能把一个集合赋值给一个基本型变量，否则会抛出 too many rows的异常。

可带参数，可不带参数。

游标的语法

这里写图片描述

cursor 游标名 [(参数名 数据类型[,参数名，数据类型]...)]is select 语句;

比如

cursor c1 is select enama from emp;

操作游标的步骤

打开游标

open c1;(打开游标，执行查询 即执行游标的查询语句)

取一行游标的值

fetch c1 into v_name;(  取一行到变量中)

最开始时，游标指向集合的第一条记录，记录返回后，指针移动到下一条记录。

这里写图片描述

关闭游标

close c1 ;(关闭游标释放资源)

栗子

使用游标查询员工姓名和工资，并打印。

Loop循环游标

推荐写法

....    Loop      Fetch  游标名 InTo  临时记录或属性类型变量；     Exit  When   游标名%NotFound；    End   Loop； .....

/*使用游标查询员工姓名和工资，并打印*/set serveroutput on ;declare--定义 游标    推荐将游标和游标对应的变量写在一块，比较好维护cursor cemp is select  e.ename , e.sal from emp e ;--定义游标对应的变量   这里使用引用型变量v_name     emp.ename%type ;v_sal      emp.sal%type;begin   --打开游标  open cemp ;  --循环获取游标中的值  loop        --取一条数据       fetch cemp into  v_name,v_sal ; -- into 后变量的顺序一定要和定义游标时select的字段对应       exit when cemp%notfound ;       --打印       dbms_output.put_line(v_name||'的工资是'||v_sal);  end loop;  --关闭游标  if cemp%isopen then     close cemp;     dbms_output.put_line('Closing...');  end if;end;/

这里写图片描述

或者

For 循环游标

循环游标隐式打开游标，自动滚动获取一条记录，并自动创建临时记录类型变量存储记录。处理完后自动关闭游标。

For  变量名  In  游标名       Loop      数据处理语句；     End Loop；

/*使用游标查询员工姓名和工资，并打印*/set serveroutput on ;declare--定义 游标    推荐将游标和游标对应的变量写在一块，比较好维护cursor cemp is select  e.ename , e.sal from emp e ;--定义游标对应的变量   这里使用引用型变量v_name     emp.ename%type ;v_sal      emp.sal%type;begin   --循环获取游标中的值   for c in cemp  LOOP        select  c.ename , c.sal into v_name,v_sal from dual;        dbms_output.put_line(v_name||'的工资是'||v_sal);    end loop;end;/

这里写图片描述

实例：给员工涨工资

/*给员工涨工资。总裁涨1000，经理涨800，普通员工涨400；*/declare   --定义游标  cursor c_emp is select empno,empjob from emp;  --定义游标对应的变量  p_empno emp.empno%type;  p_empjob emp.empjob%type;begin  --打开游标  open c_emp;  --取出一个员工  loop    fetch c_emp into p_empno,p_empjob;    exit when c_emp%notfound;    --判断职位    if p_empjob = 'PRESIDENT' then       update emp set sal=sal+1000 where empno=p_empno;    elsif p_empjob = 'MANAGER' then       update emp set sal= sal+800 where empno = p_empno;    else       update emp set sal=sal+400 where empno=p_empno;    end if;  end loop;  --关闭游标   if c_emp%isopen then       close c_emp;   end if ;  --提交事务，oracle默认的隔离级别是read committed ，不同连接只能读取提交之后的；  commit;  dbms_output.put_line('涨工资完成');end;/

游标的属性

游标的4个属性

%Found ：Fetch语句（获取记录）执行情况 True or False

%NotFound : 最后一条记录是否提取出 True or False

%ISOpen ：游标是否打开True or False

%RowCount ：游标当前提取的行数

游标数的限制

Oracle默认的一个会话最多可以打开300个游标.

可以通过使用 show parameter cursors; （表示模糊查询 %cursors%）

查看包含cursors的参数设置

这里写图片描述

修改游标数的限制

使用DBA权限的用户

alter system set open_cursors=400 scope = both;

其中scope的取值：both，memory，spfile

memory：表示只更改当前实例，不更改参数文件

spfile：表示只更改参数文件，不更改当前示例，数据库服务需要重启

both：表示上边两个同时更改

带参数的游标

注意定义（带参数）和打开游标（传递参数）时的区别。其余的和无参的游标一样。

set serveroutput on ;declare--定义带参数的游标cursor cemp (dno number) is select ename from emp where deptno =dno;--定义游标中对应的变量v_name  emp.ename%type;begin  --打开游标   传入对应的入参        open cemp(10);              --loop循环 遍历游标             loop                        fetch cemp into v_name;                       exit when cemp%notfound;                       dbms_output.put_line(v_name);             end loop;        close cemp;end;/

这里写图片描述

显式游标和隐式游标

上面介绍的是显式游标，下面说下隐式游标

DML操作和单行SELECT语句会使用隐式游标，它们是：

插入操作：INSERT

更新操作：UPDATE

删除操作：DELETE

单行查询操作：SELECT … INTO …

隐式游标的名字为SQL，这是由ORACLE 系统定义的。

对于隐式游标的操作，如定义、打开、取值及关闭操作，都由ORACLE 系统自动地完成，无需用户进行处理。用户只能通过隐式游标的相关属性，来完成相应的操作。

格式调用为： SQL%

隐式游标可以使用名字SQL来访问，但要注意，通过SQL游标名总是只能访问前一个DML操作或单行SELECT操作的游标属性。所以通常在刚刚执行完操作之后，立即使用SQL游标名来访问属性。

游标的属性有四种，如下所示。

隐式游标的属性   返回值类型    意    义SQL%ROWCOUNT    整型       代表DML语句成功执行的数据行数SQL%FOUND       布尔型     值为TRUE代表插入、删除、更新或单行查询操作成功SQL%NOTFOUND    布尔型     与SQL%FOUND属性返回值相反SQL%ISOPEN      布尔型     DML执行过程中为真，结束后为假

DECLARE     v_rows NUMBER;  BEGIN     --更新数据     UPDATE employees SET salary = 30000       WHERE department_id = 90 AND job_id = 'AD_VP';     --获取默认游标的属性值     v_rows := SQL%ROWCOUNT;     DBMS_OUTPUT.PUT_LINE('更新了'||v_rows||'个雇员的工资');      --删除指定雇员；如果部门中没有雇员，则删除部门      DELETE FROM employees WHERE department_id=v_deptno;      IF SQL%NOTFOUND THEN          DELETE FROM departments WHERE department_id=v_deptno;      END IF;  END;

例外

例外的概念

在oracle中错误被叫做例外：分为系统例外和自定义例外。

系统例外比如：

- No_data_found 没有找到数据

- Too_many_rows select..into语句匹配多个行

- Zero_Divide 被零除

- Value_error 算数或转换错误，算术错误比如说负数开平方

- Timeout_on_resource 在等待资源时发生超时，常见于分部署数据库。

系统例外之no_data_found

/*系统例外  no_data_found*/ set serveroutput on ;--查询empno为222的姓名declare --定义引用型变量v_name emp.ename%type;begin   --业务sql    select ename into v_name from emp where empno=222;     exception       when no_data_found  then dbms_output.put_line('no data found');      when others then dbms_output.put_line('others exception');end;/

这里写图片描述

系统例外之too_many_rows

/*系统例外  too_many_rows */set serveroutput on ;--查询 10号部门的员工declare --定义引用型变量v_name emp.ename%type;begin   --业务sql    select ename into v_name from emp where deptno=10;     exception       when too_many_rows  then dbms_output.put_line('too_many_rows');      when others then dbms_output.put_line('others exception');end;/

这里写图片描述

系统例外之zero_divide

/*系统例外  zero_divide */set serveroutput on ;declare --定义引用型变量v_num number;begin   --业务sql      v_num := 1/0;    exception       when zero_divide  then dbms_output.put_line('zero_divide');dbms_output.put_line('0不能做除数');      when others then dbms_output.put_line('others exception');end;/

这里写图片描述

系统例外之value_error

/*系统例外  value_error */set serveroutput on ;declare --定义引用型变量v_num number;begin   --业务sql      v_num := 'xiaogongjiang';    exception       when value_error  then dbms_output.put_line('value_error');      when others then dbms_output.put_line('others exception');end;/

这里写图片描述

自定义例外

步骤

定义变量，类型是exception

使用raise抛出自定义意外

栗子

--自定义例外：（没有查找到的例外no_emp_found）declare   cursor c_emp is select ename from emp where deptno=50;  p_ename emp.ename%type;  --定义一个例外  no_emp_found exception;begin  open c_emp;    --获取一条记录    fetch c_emp into p_ename;    --如果没有查到则抛出自定义例外    if c_emp%notfound then       raise no_emp_found;    end if;  --此处，当前一句抛出例外执行完exception后，  --oracle会自动启动一个pmon(process monitor)的一个进程  --将pl/sql程序中未关闭的资源释放  --所以  close c_emp; 还是会执行的  close c_emp;  --捕获例外  exception     when no_emp_found then  dbms_output.put_line('没有该部门下的员工');    when others then  dbms_output.put_line('其他例外');end;/

这里写图片描述

PL/SQL调测

可以在pl/sql工具中新建测试窗口，调测过程和调测存过的方式一样，可以一步一步的跟踪sql执行的过程。

这里写图片描述

案例

运用瀑布模型完成PLSQL程序的设计

瀑布模型

1.需求分析

2.设计
- 2.1概要设计
- 2.2详细设计

3.编码coding

4.测试Testing

5.上线（部署）

拿到一个需求后，不找着急写程序，先分析明白了

sql语句

变量初始值

变量如何获取

….

案例：统计每年入职的员工人数

分析过程：

每年入职的员工人数

1.所有的年份集合–>定义cursor保存

2.每个员工的入职年份–>定义v_hiredate保存

3.每年入住的人数总和–>定义v_count_XX保存

先把大框架打起来

declare ---定义变量balabalabegin ---业务逻辑end;/

然后再一步一步的填充业务逻辑。

set serveroutput on ;declare --入职年份的游标cursor hiredate_cursor  is  select to_char(hiredate,'yyyy') from emp;--定义入职年份v_hiredate varchar2(4);--定义每个年份入职人员总数v_count_80 number :=0;v_count_81 number :=0;v_count_82 number :=0;v_count_87 number :=0;begin   --打开游标  open  hiredate_cursor ;  --loop循环遍历游标  loop         --取值      fetch hiredate_cursor into  v_hiredate ;      exit when  hiredate_cursor%notfound ;         if v_hiredate = '1980' then v_count_80 :=v_count_80+1 ;         elsif v_hiredate = '1981' then v_count_81 :=v_count_81+1 ;         elsif v_hiredate ='1982'   then v_count_82 :=v_count_82+1;         elsif v_hiredate ='1987' then v_count_87 :=v_count_87+1;         end if ;    end loop;  --关闭游标  close hiredate_cursor;   dbms_output.put_line('总共入职人数:'||(v_count_80+v_count_81+v_count_82+v_count_87));--加括号    dbms_output.put_line('80入职的:'||v_count_80);    dbms_output.put_line('81入职的:'||v_count_81);    dbms_output.put_line('82入职的:'||v_count_82);    dbms_output.put_line('87入职的:'||v_count_87);end;/

这里写图片描述

案例：员工涨工资问题

案例2：涨工资问题，从最低工资的员工开始涨起，没人涨10%，工资总额不能超过50000，返回涨工资的人数和涨后的工资总额.

/*分析：1、用到的sql语句：      select empno,sal from emp order by sal;      select sum(sal) into totalsal from emp;2、需要声明的变量：      工资总额：totalsal   涨工资人数：count3、循环推出的条件：      工资总额>5W   or  全部员工都涨完工资*/declare  cursor cemp is select empno,sal from emp order by sal;  p_no emp.empno%type;  p_sal emp.sal%type;  countemp number:=0;--涨工资人数  totalsal emp.sal%type;begin  --获取初始工资总额  select sum(sal) into totalsal from emp;  open cemp;    --判断当前工资总额是否大于5W    if totalsal<50000 then       loop        fetch cemp into p_no,p_sal;        exit when cemp%notfound;        --获取当前员工涨工资后的工资总额        --如果工资总额超过5W直接退出循环        exit when (totalsal+p_sal*0.1)>50000;        update emp set sal=sal*1.1 where empno=p_no;        --涨工资人数加1        countemp:=countemp+1;      end loop;    end if;  close cemp;  commit;  dbms_output.put_line('共有'countemp'人涨工资，工资总额为：'totalsal);end;/

案例：涉及两张表的员工涨工资问题

这里写图片描述

declare  --获取所有部门  cursor c_dept is select deptno from dept;  --各部门编号  p_dno dept.deptno%type;  --各部门总金额  p_totalsal number;  --各工资分段人数：  num1 number;  num2 number;  num3 number;  --定义一个游标存放该部门下所有员工(带参数)  cursor c_emp(dno number) is select sal from emp where deptno = dno;  --员工的薪水  p_sal number;begin  --打开部门游标  open c_dept;  loop    --部门循环    fetch c_dept      into p_dno;    exit when c_dept%notfound;    --初始化变量：    p_totalsal := 0;    num1       := 0;    num2       := 0;    num3       := 0;    --  --获取本部门下所有员工,打开员工游标    open c_emp(p_dno);    loop      --员工循环      fetch c_emp into p_sal;      exit when c_emp%notfound;      if p_sal < 3000 then        num1 := num1 + 1;      elsif p_sal >= 3000 and p_sal <= 6000 then        num2 := num2 + 1;      elsif p_sal > 6000 then        num3 := num3 + 1;      end if;      --获取总金额      p_totalsal := p_totalsal + p_sal;    end loop;    close c_emp;    --保存统计结果到sal_msg    insert into sal_msg values (p_dno, num1, num2, num3, p_totalsal);  end loop;  close c_dept;  commit;  dbms_output.put_line('统计完成');end;/

你可能感兴趣的文章

【unix网络编程第三版】阅读笔记（二）：套接字编程简介

查看>>

【一天一道LeetCode】#115. Distinct Subsequences

查看>>

【一天一道LeetCode】#116. Populating Next Right Pointers in Each Node

查看>>

【一天一道LeetCode】#117. Populating Next Right Pointers in Each Node II

查看>>

【一天一道LeetCode】#118. Pascal's Triangle

查看>>

【一天一道LeetCode】#119. Pascal's Triangle II