首页 > 代码库 > PL/SQL简介

PL/SQL简介

前言


正文


PL/SQL


(Procedure Language,过程化语言)SQL 1999各大数据库厂商通用的一种结构化语言  PL/SQL只支持Oracle数据库


基本语法


多行注释   /*    */      单行注释   --



PLSQL程序块


结构

[declare]
定义变量
begin
过程语句
[exception]
处理 异常
end;


例子 输出helloworld


begin
  dbms_output.put_line(‘hello‘);
end;
--如果过程执行完没有输出,必须将服务器输出打开

执行
 
SQL> set serveroutput on/off;
SQL> 
SQL> begin
  2    dbms_output.put_line(‘hello‘);
  3  end;
  4  /
 
hello
 
PL/SQL procedure successfully completed


变量


declare
变量名称   变量类型;
begin

end;
标识符:不能以数字开始 长度不能超过30个字符


变量初始化

采用:= 进行赋值
v_age number(10) := 10;

变量类型


number(m,n)  数字类型
char()       固定长度字符串
varchar2()   可变长度字符串 
date         日期类型
boolean(true/false/null)      布尔类型
binary_integer  有符号整型
注意:布尔类型不能直接输出


代码示例


<script src="https://code.csdn.net/snippets/411106.js" type="text/javascript"></script>


注意:变量名称不能是SQL关键字,最好不要和表中字段同名
变量命名:
v_实际含义的单词
常量命名:
c_实际含义 
参数命名
p_实际含义
游标类型
名称_cursor
表类型
名称_table
记录类型
名称_record
变量作用域
变量从声明开始到对应模块的end结束为它的作用域

--变量作用域代码示例

<script src="https://code.csdn.net/snippets/411107.js" type="text/javascript"></script>

变量的其他类型


1.表类型 


定义表类型
type  表类型名称 is table  of  表类型存储的基本类型 index by binary_integer
声明变量为表类型
变量名称  表类型名称


表类型类似java中的数组,用来存储具有同种类型的数据




属性:
first  表示获得表类型的第一个下标
last   表示获得表类型的最后一个下标
count  表示表类型的数据个数
next(下标) 表示下一个下标
prior(下标) 表示前一个下标
代码示例:
<script src="https://code.csdn.net/snippets/411109.js" type="text/javascript"></script>


表类型通过下标操作


2.记录类型


 用来存储一条记录
 type  记录类型名称 is record(field1 类型,field2 类型)
 定义变量,将变量类型设置成记录类型
 v_record 记录类型名称
代码示例

<script src="https://code.csdn.net/snippets/411110.js" type="text/javascript"></script>


定义记录类型的简单方式


 %rowtype 定义记录类型
 表名%rowtype
代码示例
<div><script src="https://code.csdn.net/snippets/411111.js" type="text/javascript"></script></div>


定义表类型的简单方式


 %type  定义表类型
 表名.字段名%type
代码示例

<script src="https://code.csdn.net/snippets/411113.js" type="text/javascript"></script>


代码示例: 将表中的所有记录存在一个变量中

<script src="https://code.csdn.net/snippets/411114.js" type="text/javascript"></script>

操作符


算数运算符


跟java一致
v_age number(3) := 0;

v_age := v_age +1;


比较运算符


>  < >= = 不等于!= <> ~= ^= 

逻辑运算符


and or not 


between   and 


赋值运算符


:=

流程控制


   分支:


   1.if分支
     if  布尔表达式  then
     …………
     else
     …………
     end if;


   多个分支:
     if  布尔表达式  then
     …………
     elsif 布尔表达式 then
     …………
     elsif 布尔表达式 then
     …………
     else
     …………
     end if;


案例: --查找员工,根据职位进行工资的增加


<script src="https://code.csdn.net/snippets/411115.js" type="text/javascript"></script>


2.case分支,类似java中的switch````case



case 变量
when 条件 then
语句·····
when 条件 then
语句·····
when 条件 then
语句·····
else
语句·····
end case;


案例

<script src="https://code.csdn.net/snippets/411117.js" type="text/javascript"></script>

    循环:


1.loop循环


定义一个循环指数
v_index binary_integer := 0;


loop
if v_index=循环次数 then
  exit;
end if;
循环体




循环指数自增
v_index := v_index+1;
 


end loop;

案例:
--loop循环
declare
--定义循环指数
v_index binary_integer :=1;
begin
--定义loop循环
loop
--定义推出循环的条件
if v_index=5 then
exit;
end if;
--执行循环体
dbms_output.put_line(v_index);
--循环指数自增
v_index := v_index+1;
end loop;
end;

2.for循环

定义一个循环指数 
v_index binary_integer;


for 循环指数 in 下限指数..上限指数 loop
循环体
end loop;


案例:
--for循环
<script src="https://code.csdn.net/snippets/411119.js" type="text/javascript"></script>


注意:循环指数的开始一定要小于结束

通过reverse关键字实现循环反转

--for循环反转
declare
  v_index binary_integer;
begin
  for v_index in reverse 10..20 loop
  dbms_output.put_line(v_index);
  end loop;
end;



3.while循环



定义循环指数
v_index binary_integer := 1;

while 布尔表达式 loop
循环体
循环指数自增
end loop;


案例:
--while循环
declare
  v_index binary_integer := 1;
begin
  while v_index<5 loop
  dbms_output.put_line(v_index);
  v_index := v_index+1;
  end loop;
end;



循环嵌套


通过<<label>>标签方式来控制循环


案例:
<script src="https://code.csdn.net/snippets/411123.js" type="text/javascript"></script>


4.goto跳转



<script src="https://code.csdn.net/snippets/411125.js" type="text/javascript"></script>



PL/SQL中书写定义sql


1.DQL语言


select 语句要求:1.必须写into关键字
2.查询语句只能有一条返回值
异常示例: 没有值  no_data_found;值过多  too_many_rows

2.DML(insert/update/delete)



注意:执行DML语句要处理事务

动态传参
&+变量来实现动态传参


--执行DML语句
declare
begin
    --普通DML语句
    delete from emp where empno=&xx;
    --事务处理
    commit;
end;


3.DDL(create/drop/alter/truncate)


DDL语句要在 execute  immediate(‘DDL‘)

<script src="https://code.csdn.net/snippets/411133.js" type="text/javascript"></script>


游标(cursor)



sql语句执行时会在内存中开辟一个区域,用来存放执行的sql语句以及返回的数据,我们把这个内存区域叫做上下文环境(context);游标就是指向这个上下文环境的指针。


游标分类

隐式游标:由数据库管理系统创建执行
显示游标:有程序员负责创建执行和关闭的游标

游标的属性

%rowcount  存储的是游标执行时所影响的记录条数

操作属性

显示游标:自定义游标名称%rowcount
隐式游标:sql%rowcount
%found  判断当前数据有没有下一条 true/fase
%notfound 判断当前数据有没有下一条 true/fase
%isopen  判断游标是否开启 




<script src="https://code.csdn.net/snippets/411135.js" type="text/javascript"></script>



显示游标


1.创建游标
cursor 游标名称 is 查询语句
2.开启游标
open 游标名称
3.获取数据
fetch 游标名称 into 变量
4.关闭游标
close 游标名称

案例: --显示游标
<script src="https://code.csdn.net/snippets/411139.js" type="text/javascript"></script>


for循环来循环游标


<script src="https://code.csdn.net/snippets/411146.js" type="text/javascript"></script>



定义游标时传递参数


<script src="https://code.csdn.net/snippets/411163.js" type="text/javascript"></script>


注意:设置形参时不要写参数的长度,实参是在开启游标的时候传递
     游标不能重复开启和关闭


定义游标类型的变量



1.定义游标类型


type 游标类型名称 is ref cursor return 返回结果类型


2.定义游标类型的变量



变量名称   游标类型名称



<script src="https://code.csdn.net/snippets/411170.js" type="text/javascript"></script>


过程和函数


过程(procedure)又叫存储过程(stored procedure),是一个有名称的PL/SQL程序块
过程相当于java中的方法,它注重的是实现某种业务功能
函数(function)也相当于java中的方法,它注重计算并且总是有返回结果
过程和函数都是能够永久存储在数据库中的程序代码块,应用时通过调用执行


过程的基本结构


create [or replace ] procedure 过程名称(形参名称  形参类型,形参名称  形参类型······)
is | as 
定义变量
begin
过程体
exception
异常
end;


1.含有输入参数的过程


输入参数  用in 标识  可省略

<script src="https://code.csdn.net/snippets/411186.js" type="text/javascript"></script>


2.无参的过程

<script src="https://code.csdn.net/snippets/411192.js" type="text/javascript"></script>

3.有输出参数的过程


输出参数用 out 标识
<script src="https://code.csdn.net/snippets/411194.js" type="text/javascript"></script>




过程的调用



1.通过匿名块调用


输入参数
<script src="https://code.csdn.net/snippets/411195.js" type="text/javascript"></script>


输出参数过程

<script src="https://code.csdn.net/snippets/411201.js" type="text/javascript"></script>


无参的过程

begin
    mypro_noparam;
end;


2.命令行调用


调用输入参数
SQL> exec mypro(7788,3000);
输出参数
SQL> var v_sal number;   注册变量
SQL> exec mypro(7788,:v_sal); :变量名称 使用变量接收输出
调用无参
SQL> exec mypro;

示例代码:
1.写一个过程封装emp表中的数据
<script src="https://code.csdn.net/snippets/411214.js" type="text/javascript"></script>

2.写一个过程输入员工编号,通过游标获取输出该员工对应下属的信息

<script src="https://code.csdn.net/snippets/411215.js" type="text/javascript"></script>
3./*计算100-200的素数*/
<script src="https://code.csdn.net/snippets/411233.js" type="text/javascript"></script>


3.通过java调用过程

java调用过程代码示例

<script src="https://code.csdn.net/snippets/411220.js" type="text/javascript"></script>



自定义函数


function.sql代码示例

<script src="https://code.csdn.net/snippets/411238.js" type="text/javascript"></script>


异常


系统异常分为预定义异常和非预定义异常
预定义异常,是由数据库定义好,含有异常编码,异常名称,异常信息的这么一种异常;大概有20种,例如too_many_rouws;no_data_found;zero_divide.
非预定义异常:异常编码,异常信息,但没有异常名称
非预定义异常代码示例
<script src="https://code.csdn.net/snippets/411246.js" type="text/javascript"></script>

自定义异常


自定义异常代码示例
<script src="https://code.csdn.net/snippets/411282.js" type="text/javascript"></script>

包用于管理过程和函数
包分为包头和包体

包的代码示例


<script src="https://code.csdn.net/snippets/411288.js" type="text/javascript"></script>


jdbc调用含有包的过程体的代码示例

<script src="https://code.csdn.net/snippets/411294.js" type="text/javascript"></script>

注:包一定要有包头,包头负责声明函数、过程、变量和常量
包体具体来实现包头所声明定义的函数和过程
包体封装实现
包头可单独定义,那么单独定义的包头只能含有常量


触发器 trigger


类似java中的Listener

触发器由数据库管理系统负责调用和执行
通过触发触发器所监听的事物来实现触发器的调用

表级别的触发器(对于整个数据库表做监听)
行级别的触发器(对于表中的每一行做监听)

触发器代码示例
<script src="https://code.csdn.net/snippets/411301.js" type="text/javascript"></script>




注:触发器的执行顺序

有表级别的触发器,行级别的触发器作用于同一个表

1.before表级别触发器
2.before 行级别触发器
3.after 行级别触发器
4.after 表级别触发器


系统触发器


DBA用来调试系统
on database


注:
触发器不带参数,没有放回值,不作事务处理




总结