PL/SQL语言小记

转载：https://blog.csdn.net/h294590501/article/details/81784494
转载：https://www.cnblogs.com/xujingyang/p/6642030.html
转载：https://www.cnblogs.com/xuxiaoli/p/9952319.html

declare – 可选
声明各种变量或游标的地方。
begin – 必要
开始执行语句。
--单行注释语句用两个连在一起的‘-’表示。
/*多行注释语句，
可以换行*/
exception – 可选
出错后的处理。
end; – 必要（请注意end后面的分号）

简单语句

declare
	-- 创建游标记录多行数据
	cursor empcursor is select last_name,salary from employees where rownum <11;
    v_temp number(6):=123; /* := 是一个赋值符号 */
begin
     dbms_output.put_line('middle');
     dbms_output.put_line(v_temp);
     
end;

注意
要想显示程序的东西需要设置环境

 set serveroutput off -->关闭输出（默认关闭）
 set serveroutput on -->打开输出

基本数据类型

Number 数字型
Int 整数型
Pls_integer 整数型，产生溢出时出现错误
Binary_integer 整数型，表示带符号的整数
Char 定长字符型，最大255个字符
Varchar2 变长字符型，最大2000个字符
Long 变长字符型，最长2GB
Date 日期型
Boolean 布尔型（TRUE、FALSE、NULL三者取一）
%type 表示不需要知道具体的一个字段类型，与指定的字段类型一致即可。例如:v_empno emp.empno%type;
%rowtype 与表结构完全一致 保存一行数据 %rowtype 举例:

declare 
v_tt emp%rowtype;
begin
 --给行类型的变量赋值: 使用关键字  into
	 --方式二：使用select..into子句进行赋值；
     select * into v_tt from emp where empno=7521;
     dbms_output.put_line(v_tt.job);
     dbms_output.put_line(v_tt.sal);
end;

declare 
v_tt emp.job%type; --引用型变量， v_tt具有和job列一样的数据类型
pename emp.ename%type;
psal emp.sal%type;
begin
 --给字段类型的变量赋值: 使用关键字  into
	 --方式二：使用select..into子句进行赋值；
     select emp.job into v_tt from emp where empno=7521;
     select ename,sal into pename,sal from emp where empno=7521;
     dbms_output.put_line(v_tt);
end;

流程控制

if 条件 then
    ….
elsif 条件2 then
    …
…
else
    …
end if;

while循环：
        while 条件  loop
           --循环体
        end loop;

loop循环：
        loop
            exit when 退出条件;
              --循环体
        end loop;

for循环：
        for 变量 in [reverse] 开始值..结束值
        loop
            --循环体
        end loop;

流程代码示例

--&val 即为控制台输入
--求1到&val的和
declare   
     i number(10) :=1;
     val number(10):=&val;
     sums number(10):=0;

begin
  while i<=val loop
  sums:= sums+i;
  i :=i+1;
  end loop;

  dbms_output.put_line(sums);

end;

declare
  i number(10):=1;
  val number(10):=&val;
  sums number(10):=0;
begin
 loop 
   exit when i>val;
   sums:=sums+i;
   i:=i+1;
   end loop; 
   dbms_output.put_line(sums);
end;

declare
  val number(10):=&val;
  sums number(10):=0;
begin
  for i in 1..val loop
    sums:=sums+i;
    end loop;
   dbms_output.put_line(sums);

end;

异常

Oracle异常分为两种：自带异常、自定义异常。

--捕获异常的命令格式：
exception
    when 异常名1 then
        …
    when 异常名2 then
        …
    …
    when others then
        …

系统定义异常
value_error (算术或转换错误)
ZERO_DIVIDE	除数为零时引发的异常
ACCESS_INTO_NULL	企图为某个未初始化对象的属性赋值
COLLECTION_IS_NULL	企图使用未初始化的集合元素
CURSOR_ALREADY_OPEN	企图再次打开一个已经打开过的游标，但在重打开之前，游标未关闭
INVALID_CURSOR	执行一个非法的的游标操作，如，关闭一个未打开的游标
INVALID_NUMBER	企图将一个字符串转换成一个无效的数字而失败
LOGIN_DENIED	企图使用无效的用户名或密码连接数据库
NO_DATA_FOUND	SELECT INTO 语句没有返回数据
ROWTYPE_MISMATCH	主游标变量与PL/SQL游标变量的返回类型不兼容
SELF_IS_NULL	使用对象类型时，使用空对象调用其方法
SUBSCRIPT_BEYOND_COUNT	元素下表超过嵌套表或VARRY中的元素
SUBSCRIPT_OUTSIDE_LIMIT	企图使用非法索引号引用嵌套表或VARRY中的元素
SYS_INVALID_ROWID	字符串向ROWID转换时的错误，因为该字符串不是一个有效的ROWID值
TIMEOUT_ON_RESOURCE	Oracle在等待资源时超时
TOO_MANY_ROWS	执行SELECT INTO 语句时，结果集超过一行引发的异常

异常示例

declare 
   i number(10);
   --行类型
   pemp emp%rowtype;
begin
   --给i赋值
   --i := 1/0;
   --i := 'abc';

   --模拟异常：找多条数据给行类型赋值会出现异常
   select * into pemp from emp;


exception
   when zero_divide then
      dbms_output.put_line('分母不能为零');
   when value_error then
      dbms_output.put_line('类型转换异常');
   when too_many_rows then
      dbms_output.put_line('期望一条数据，但是返回多条');
   when others then
      dbms_output.put_line('亲，有异常要处理');
end;

自定义异常

语法格式：
异常名 exception;

抛出异常的方式：
方式一：使用raise关键字;
格式: raise 自定义异常名

方式二：使用raise_application_error(code, message)抛出异常；
code：异常的编号，取值范围：-20999~-20000之间任意一个数字；
message：异常的信息；

如果要捕获raise_application_error抛出的异常，还需要把异常编号与一个异常的变量进行绑定。
pragma exception_init(异常变量, 异常编号);

自定义异常示例

--自定义异常
declare
      --定义异常
      feifeiexception exception;
      --定义变量
      i number := 1;
begin 
      if i=1 then
         -- 抛出一个自定义的异常
         raise feifeiexception;
      end if;
exception 
      when feifeiexception then
         dbms_output.put_line('自定义异常出现了.....');
end;

游标
对于不同的 SQL 语句，游标的使用情况不同：
非查询语句–》 隐式的
结果是单行的查询语句 --》隐式的或显示的
结果是多行的查询语句–》 显示的

1.声明一个检索emp表中雇员信息的游标，然后打开游标，并指定检索职务是“MANAGER"的雇员信息，接着使用 fetch…into语句和while循环
读取游标中的所有雇员信息，最后输出读取的雇员信息

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 没有取到记录;
       exit when cemp%notfound;
       dbms_output.put_line(pename||'的薪水是'||psal);
  end loop;
  --关闭
  close cemp;
end;

----------------
declare 
   --形参
   cursor cemp(dno number) is select ename from emp where deptno=dno;
   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;
---------------
declare
cursor cur_emp(var_job in varchar2:='SALESMAN')--定义有参游标
is select empno,ename,sal
from emp
where job=var_job;

type record_emp is record --定义一个具有属性的类（自我理解为java）
(
var_empno emp.empno%type,
var_ename emp.ename%type,
var_sal emp.sal%type
);
emp_row record_emp;-- 初始化这个类的对象（自我理解为java）
begin
open cur_emp('MANAGER'); --打开游标
fetch cur_emp into emp_row; --读取游标中内容，将游标指针移动到结果集中的第一行
while cur_emp%found loop
dbms_output.put_line(emp_row.var_ename||'的编号是'||emp_row.var_empno||',工资是'||emp_row.var_sal);
fetch cur_emp into emp_row;
end loop;
close cur_emp;--关闭游标
end;

游标属性：
%found:如果SQL语句至少影响到一行数据，则该属性为true，否则为false
%notfound:与上面的功能相反
%rowcount:返回受影响的行数
%isopen:游标打开时，返回true,关闭时，false
对于显示游标
定义游标名%found， 如cur_emp%found
对于隐式游标
sql%found

--for循环格式游标
declare
cursor emp_cursor5 is
select ename, sal from emp;
begin
for v_sal in emp_cursor5 loop
dbms_output.put_line(v_sal.ename || '--***--' || v_sal.sal);
end loop;
end;

--for循环格式游标带参数
declare
cursor emp_cursor5(dept_no number) is
select ename, sal from emp where deptno=dept_no;
begin
for v_sal in emp_cursor5(30) loop
dbms_output.put_line(v_sal.ename || '--***--' || v_sal.sal);
end loop;
end;

--隐式游标
DECLARE
v_rows NUMBER;
BEGIN
--更新数据
UPDATE emp SET sal = 30000
WHERE deptno = 20;
--获取默认游标的属性值
v_rows := SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('更新了'||v_rows||'个雇员的工资');
ROLLBACK;--回退更新，以便使数据库的数据保持原样
END;

存储过程和存储函数

存储在数据库供所有用户程序调用的子程序（PL/SQL）
存储过程：没有返回值
存储函数：有return

存储过程

无参存储过程

--创建过程
create or replace procedure sayhelloworld
as                                        --也可以是is，相当于declare
   --说明部分
begin
   dbms_output.put_line('Hello World');
end;
--调用存储过程
1.exec sayhelloworld()
2.begin  
	sayhelloworld();
  end;

有参存储过程

--给指定员工涨工资
create or replace procedure raiseSal(eno in number)--in说明是输入，调用时必须掺入
as                                        --也可以是is，相当于declare
   psal emp.sal%type   --定义接受薪水的值
begin
   select sal into psal from emp where empno = eno;--查找工资
   update emp set sal = sal+100 where empno = eno;
   dbms_output.put_line('涨前：'|| psal||'涨后:'|| (psal+100))
end;
--调用存储过程
begin
	raiseSal(7893);
end;

存储函数

必须有return 返回值
调用必须有接收值

--查询某个员工的年收入
create or replace function selectAual(eno in number)
return number  --返回类型
is
	psal emp.sal%type;--月薪
	pcomm emp.comm%type --奖金 
begin
	select sal,comm into psal,pcomm from emp where empno = eno;--查找月薪和奖金
	return psal * 12 + nvl(pcomm,0);
end selectAual;
--调用存储函数
begin
	:result := selectAual(7839);
end;

输出参数

过程和函数可以通过out指定一个或多个输出参数，可以利用 out参数，在过程和函数中实现返回多个值
什么时候用过程或者函数？
原则：
如果有一个返回值，用存储函数
如果有多个返回值，用存储过程

--通过员工号查询某个员工的姓名，薪水和职位
create or replace procedure seleVal(eno in number,pename out varchar2,
									psal out number,pjob out varchar)
as                                       
   psal emp.sal%type   --定义接受薪水的值
begin
   select ename,sal,job into pename,psal,pjob from emp where empno = eno;
end;
--调用
begin
	seleVal(eno => 7839,
			pename =>:pename,
			psal =>:psal,
			pjob =>pjob);
end;

返回集合

返回一个游标就可以

--查询某个部门中的所有员工信息
--申明包结构
create or replace package mypackage is
       type empcursor is ref cursor;
       procedure queryEmpList(dno in number,empList out empcursor);
end mypackage;
--创建包体
create or replace package body mypackage is
       procedure queryEmpList(dno in number,empList out empcursor)
       as
       begin
          open empList for select * from emp where deptno=dno;
       end;
end mypackage;

触发器

语句级触发器

在指定的操作语句操作之前或之后执行一次

create [or replace] trigger 触发器名
{after|before}
{delete |insert| update[of 列名]}
on 表名
[for each row[when(条件)]] --有这一行就是行级触发器
PLSQL块

--每当成功插入新员工后，自动打印“成功插入新员工”
create trigger firsttrigger
after insert
on emp
declare
begin
  dbms_output.put_line('成功插入新员工');
end;
--禁止在非工作时间 插入新员工
--1、周末:  to_char(sysdate,'day') in ('星期六','星期日')
--2、上班前 下班后：to_number(to_char(sysdate,'hh24')) not between 9 and 17
create or replace trigger securityemp
before insert
on emp
begin
   if to_char(sysdate,'day') in ('星期六','星期日') or 
      to_number(to_char(sysdate,'hh24')) not between 9 and 17 then
      --禁止insert
      raise_application_error(-20001,'禁止在非工作时间插入新员工');
   end if;
end securityemp;

行级触发器（for each row）

insert into emp10 select * from emp where depto = 10；假设插入三条
如果定义成语句级触发器则执行一次
如果定义成行级触发器则执行三次

--数据的确认
--涨后的薪水不能少于涨前的薪水
create or replace trigger checksalary
before update
on emp
for each row
begin
    --if 涨后的薪水 < 涨前的薪水 then
    if :new.sal < :old.sal then
       raise_application_error(-20002,'涨后的薪水不能少于涨前的薪水。涨前:'||:old.sal||'   涨后:'||:new.sal);
    end if;
end checksalary;