[Oracle之plsql] plsql初步学习、了解游标、存储过程、函数、触发器等概念

plsql 编程：

什么是plsql编程：

一种过程化sql语言（procedural language/ sql）
pl/sql 是 oracle 数据库对sql语句拓展，在普通的sql语句中添加编程语言的特点，通过逻辑判断，循环实现复杂功能
pl/sql结构
pl/sql程序以块（block）为单位，整个plsql分三个部分：声明部分、执行部分、异常处理
语法：

    declare
   --           声明区：声明一些变量、游标等
   begin
   -- 执行部分:
    exception --不是必须的
    end; --结束

语法规范：
 1.标识符规范：
不能超过30个字符、不区分大小写、不能使用减号、不能使用sql关键词、变量名声明尽量不要和表中的字段一致[因为有可能会出错]
2.变量类型：
char, varchar2, date, number, boolean, long
%type：与表中的字段数据类型是一致的，相当于动态获取表中字段数据类型
定义语法： varl char(15);
3.运算符：
大部分与java一致 唯一不同的是赋值不同
+ - * / 常用运算符号
…  范围符号（1…10）
：=  赋值运算符
||   字符连接符

初试plsql

declare
v_name varchar2(50);
v_name2 varchar2(50) := '张三';
v_name3 varchar2(200) := &testName;
--&名称[名称随意取] 手动输入内容赋值
--如果输入字符串 加''
begin

Dbms_Output.put_line(v_name2);
Dbms_Output.put_line(v_name3);

--通过查询语句对plsql中的变量赋值
select ename into v_name from emp where empno = &emp_id;
Dbms_Output.put_line(v_name);

end;

if判断：

语法：
if 表达式 then 处理1
elsif 表达式2 then 处理2
else 其他情况
end if;

case表达式

case
when 表达式1 then 结果1
when 表达式2 then 结果2
…
else 默认结果
end; --case 结束

declare
   v_score number(10, 2) := &inputScore;
   v_result char(1);
begin
 v_result :=
   case 
   when v_score > 90 and v_score <= 
100
     then 'A'
   when v_score > 60 and v_score <=90
     then 'B'
   else 'C'
   end; --结束case的
   Dbms_Output.put_line(v_result);
end;

---

游标：

游标是属于sql的内存工作区，用于临时存放从数据库中提取的数据集合。（相当于是一个数据集合）
游标的状态：
%notfound 最近一次读取不成功返回true
%found 最近一次读取成功返回true
%rowcount 数据类型， 返回已从游标中读取条数
%isopen 判断是否开启 boolean

案例：使用游标方式输出emp表中的员工信息

declare
    cursor c1 is select * from emp;
    rowinfo emp%rowtype;
begin
    open c1;
    loop
      fetch c1 into rowinfo;
      exit when c1%notfound;
      dbms_output.put_line(rowinfo.empno || '====' || rowinfo.ename);
    end loop;
    close c1;
end;

游标的for循环：
游标for循环语句，自动执行游标中的open，fetch，close操作

for 游标中的变量 in 游标名[(参数1，…)]

--游标使用for循环

--v_emp是一个临时变量 存储的是游标中存储结果集每一行数据 类似java 增强for
declare
    cursor emp_cursor(v_sal number default 1) is select * from emp where sal > v_sal;
begin
    for v_emp in emp_cursor(&v_sal)
    loop
      DBMS_output.put_line(
      v_emp.ename || '==' || v_emp.job || '==' || v_emp.sal);
    end loop;
end;

---

存储过程

什么是存储过程？
是一个数据库对象，是一组为完成特定功能的pl/sql语句集合。
创建语法：create procedure 存储过程名[(参数1，…)]
{is | as}
变量声明区 declare
begin
执行的内容（可以包含很多个pl/sql语句段）
exception
end;
参数类型：in 输入参数 out 输出参数 in out 输入输出
存储过程优缺点？
1.存储过程是预编译的，可以防止sql注入，一次编译多次执行，提高效率，提高安全性。
2.可以将多个复杂的sql语句封装成存储过程，可以减少代码的复杂度，也可以减少数据库的连接次数，提高性能、效率。
3.存储过程给用户设定权限，较高的安全性。

缺点：调试比较麻烦，不方便维护和移植
创建存储过程:

--创建存储过程
create or replace procedure emp_proc(v_name in varchar2, v_salary out emp.sal%type)
--参数类型有三种    in 输入参数   out输出参数  in out 输入输出参数
as
--声明区  如果没有变量可以省略
--执行区
begin
  --多个plsql语句段
  update emp set sal = sal + 500 where ename = v_name;
  select sal into v_salary from emp where ename = v_name;
end;

存储过程调用：
plsql调用：

declare
        v_salary  emp.sal%type;
begin
        emp_proc(&v_name, v_salary);
        Dbms_Output.put_line('新的工资' ||  v_salary);
end;

jdbc调用

    String sql = "{call 存储过程名(?, ?, ?, ...)}";
    CallableStatement state = conn.prepareCall();
    给问号传参。。。

hibernate、mybatis如何调用？
框架本身会对对应类方法实现存储过程。

---

函数：

函数类似存储过程，函数是一个命名程序，可以带参数，并返回结果（必须有一个return结果）
语法：

    create [or replace] function 函数名[(参数1， 参数2， ...)]
    return 返回数据类型
    {is | as}
    --变量声明区  不需要declare
    begin 
     执行的内容（可以包含很多个pl/sql语句段）
     exception
    end;

函数调用：

1.使用plsql调用

declare
  v_count number;
begin
  v_count := empcount;
  Dbms_Output.put_line(v_count);
end;

2.使用sql语句调用

select empcount from dual;

函数和存储过程的区别：

1.函数必须有一个返回值，存储过程不需要
2.函数可以在sql语句中使用，而存储过程不能，但是可以通过代码在java程序中使用
如：select empcount from dual在虚拟表中调用函数

---

触发器：

触发器是一段plsql对象，由事件触发，不需要调用，不接受参数
语法：create [or replace] trigger 触发器名
{before | after} --事件之前或者之后
{insert | update | delete} --触发事件
on 表名
[for each row] --对每行数据修改都会触发
begin
执行的处理

--定义触发器
create or replace trigger emp_trigger
before --触发时机       之前
insert --执行插入时会触发       代表触发的事件
on emp --指定哪张表
for each row -- 对每行数据都起作用
declare
    v_next number;
begin
    select goodz.nextval into v_next from dual;
    --将新的主键列通过v_next赋值
    :new.empno := v_next;
    Dbms_Output.put_line('执行触发器' || v_next);
end;

---

数据库备份和还原

1.借助于plsql工具进行导入导出
2.借助于cmd命令进行备份还原（需要安装环境变量）

导出命令
exp 用户/密码/@ip/实例 file=导入路径.dmp
log=日志文件.log
如: exp scott/[e-mail]/xe file=d:/a.dmp log=d:/b.log
导入命令
lmp 用户/密码@ip/实例 file=还原文件.dmp
full=y --完全还原
ignore=y – 有错误忽略
log=日志文件.log