oracle： PL/SQL基本结构，语法，变量

作者: TryCatch菌 | 来源:发表于2018-10-05 01:06 被阅读0次

oracle： PL/SQL基本结构，语法，变量
PL/SQL程序块
ora2pg pl/sql转换为pl/pgsql测试
1- Oracle Select语句
oracle工具PL/SQL快速入门1简介
PL/SQL
JAVA学习笔记（PL/SQL）
PL/SQL的基本变量类型
PL/SQL块
PL_SQL_Oracle基础教程.pdf

PL/SQL是基于块结构，不论是命名块还是匿名块都由3个部分组成
定义部分：定义常量，变量，游标，基础及复杂数据类型
执行部分：包含要执行的PL/SQL语句，sql语句，实现应用模块功能
异常处理部分：用户不在执行部分可能产生的异常

declare --声明部分，可选
--定义部分，一般可以定义变量，游标，常量，复杂数据类型等
begin --必选，块开始语句
  --执行部分
exception --可以，异常处理
  --异常处理部分
end ;--结束部分，必选

最简单的PL/SQL语句

--简单的PL/SQL
declare
begin
  dbms_output.put_line('这是个简单的PL/SQL');
end;

PL/SQL块可以分为匿名块和命名块
匿名块：没有名称的PL/SQL块，可以内嵌到应用程序中直接运行，一般由客户端程序产生，不会保存到数据库
命名块：具有名称的PL/SQL块，可以细分为3个部分，一般会保存到数据库中
命名块3个部分：
1.使用<<块名称>>进行表示的块，通常是在块嵌套时，为了区分多级嵌套层次二进行命名区分
2.由函数或过程组成的子程序块，如存储过程，自定义函数等，这种块保存于数据库中
3.数据给触发块，触发器块是指当数据库中的某个事件触发后要执行的PL/SQL语句块

一个简单的匿名块

--使用匿名块插入一条员工信息，这里我们假设名字为唯一
declare
u_name varchar2(50) := '田七';
u_job varchar2(50) :='DB';
u_money number(10,2) :=2000.00;
u_depton number :=2;
u_count number; --查询计数变量 ，这里假设要录入员工信息，名字为唯一，没有才能添加成功
begin 
  select count(1) into u_count from t_emp t where t.name = u_name;
  if u_count = 0
    then
      insert into t_emp(id,name,job,money,deptno)values (seq_emp.nextval,u_name,u_job,u_money,u_depton);
   else
     dbms_output.put_line('人员存在');
   end if;
exception 
  when others --when others then 会吃掉所有异常
  then  dbms_output.put_line('插入异常');
end;

同样的PL/SQL也支持嵌套的写法，变量都有生命周期的，所有内部块能直接使用外部块定义的变量，但是外部块不能使用内部块定义的变量

--嵌套块的使用，这里我们通过外部查询的条件更新更新一个员工的年龄，如果员工不存在，则插入一条新数据
declare
s_name varchar2(20) :='王五';
s_age varchar2(20) :='30';
s_id number(20);
begin
 --外部块查询出
  select id into s_id from t_staff ts where ts.name=s_name;
  --内部块处理外部查询出的更新
  begin
    update t_staff ts set ts.age = s_age where ts.id = s_id;
    commit;
  end;
--异常处理，当没有查询到数据时插入一条数据
exception
  when no_data_found
    then
      --内部块处理当未查询到数据执行更新插入操作
      begin
      insert into t_staff(id,name,age,sex) values (seq_t_staff.nextval,s_name,s_age,'男');
      commit;
      exception
        when others
          then
            dbms_output.put_line(sqlerrm);
       end; 
end;

在实际项目中有的时候会嵌套多层块，如果嵌套层数较多，也不容易区分，PL/SQL就提供了一种命名块的机制，这里只是举例一种<<命名块>>的方法

--命名块，通过查询结果给所有人年龄加1
declare
begin
<<外部块>>
declare
type cursor_people_type IS REF CURSOR; --定义一个动态游标
t_person cursor_people_type;--定义一个游标变量
t_person_row t_staff%rowtype;--定义一个person对象，类型为游标对应的行数据对象
s_add_age number(20):=1;
begin
  <<内部查询块>>
  begin
    open t_person for select * from t_staff;
  end;
  <<内部循环更新块>>
  begin
    loop
      fetch t_person into t_person_row;--提取游标变量
      exit when t_person%notfound;--当循环到最后一个退出循环
      update t_staff ts set ts.age = (ts.age+s_add_age) where ts.id = t_person_row.id;
    end loop;
   end;
   close t_person;--关闭游标
   commit;
exception
  when others
    then
      dbms_output.put_line(sqlerrm);
end;
end;

这里需要特别注意，在PL/SQL的书上的代码片段和一些网络上的代码片段是这么写的

<<命名块>>
declare
 begin
    dbms_output.put_line('这是个简单的PL/SQL');
 end;

这样是会报错的，报错end-of-file或者没有结尾什么的，大概是这么个意思，需要在命名块的最外层再添加declare begin 结尾加上end;

image.png

正确写法

declare
begin
<<命名块>>
declare
 begin
    dbms_output.put_line('这是个简单的PL/SQL');
 end;
end;

PL/SQL中的变量
PL/SQL除了可以定义我常用的基本数据类型如varchar2，date，number之外还支持复核数据类型列如rowtype表示行数据类型，varray等类型

--自定义一个数据类型,查询出id为1的员工的姓名和性别
declare
t_id number(20):=3;
type re_person is record --定义一个标记数据类型
(
    p_name t_staff.name%type,--定义标记数据类型的变量为表中对应字段的类型
    p_sex t_staff.sex%type
);
t_person re_person;--定义变量为标记数据类型对应的类型
begin
  select ts.name,ts.sex into t_person from t_staff ts where ts.id=t_id;
  dbms_output.put_line('姓名：'||t_person.p_name||' '||'性别：'||t_person.p_sex);
exception
  when others
    then
      dbms_output.put_line(sqlerrm);
end;

image.png