PL/sql

• pl/sql(procedual language/sql)过程化
• 是oracle在标准的sql语言上的扩展
• 不仅允许嵌入sql语言，还可以定义变量和常量

• 允许使用条件语句和循环语句

• 提高应用程序的运行性能

• 模块化的设计思想
• 减少网络传输量

• 提高安全性

• 移植性不好

• 编写一个存储过程，该过程可以向某表中添加记录

  create table mytest (name varchar2(20),passwd varchar2 (30))

• 创建过程

  create or replace procedure sp_prol is 
  begin
  insert into mytest values ("zhangsan","m123");
  end;

• 查看错误

  show error;

• 如何调用该过程

  exec 过程名(参数值1,2,..)
  call 过程名(参数值1,..)

• 创建过程，删除用户

  create or replace procedure sp is 
  begin
  delete from mytest where name='zhangsan';
  end;

命名规范

• 标识符的命名规范
  
  • 当定义变量时，使用v_作为前缀
  • 当定义常量时，使用c_作为前缀
  • 当定义游标时，使用_cursor作为后缀
  • 当定义例外时，使用e_作为前缀

块

• 块（编程基础单位），包括过程、函数、触发器、包
• 单行注释 –

• 多行注释 /* */

• 块的组成

  • 定义部分 declear 可选
  • 执行部分 begin 必须
  • 例外处理部分 exception 可选

• 1.只包括执行部分的块

  begin
      dbms_output.put_line('hello');
      end;

dbms_output是oracle所提供的包，其中包含一些过程，put_line就是一个过程

set serveroutput on/off

• 2.包含定义部分和执行部分的块

  declare 
  v_name varchar2(20);
  begin
  select last_name into v_name from s_emp where id=&id;
  dbms_output.put_line('员工名:'||v_name);
  end;

  相当于从s_emp表中查出last_name赋值给v_name
  & 表示要接受从控制台输入的变量

• 3.包含定义部分、执行部分、和例外处理部分

  declare 
  v_name varchar2(20);
  v_sal number (7,2);
  begin
  --从表中查出数据赋值给变量
  select last_name,salary into v_name,v_sal from s_emp where id=&id;
  dbms_output.put_line('员工名:'||v_name );
  dbms_output.put_line('工资:'||v_sal );
  exception
    when no_data_found then
      dbms_output.put_line('输入编号有误');
  end;

• 过程
  执行特定操作 输入、输出

• 编写一个过程，可以输入员工名、新工资、可以修改员工的工资

过程：

create procedure demo (name varchar2,newSal number) is
    begin
    --执行部分--根据用户名去修改工资
    update s_emp set salary=newSal where last_name=name;
    end;

执行：

exec demo ('Smith',45454545);

• 函数

• 案例： 输入员工姓名，返回员工年薪

  create function fun (name varchar2) return
  number is yearSal number(12,2);
  begin
    --执行部分
    select salary*12+nvl(COMMENTS,0)*12 into yearSal from s_emp where last_name =name;
  return yearSal;
  end;

调用：

var sal number ;
    call fun ('Smith') into:sal;

变量

• 标量类型 scalar

  • 标量定义

    • 字符串 v_name varchar2(20);
    • 小数 v_sal number (7,2);
    • 定义数字并赋值 v_sal number(7,2) := 3.0;
    • 定义日期 v_date date;

    • 定义布尔变量 ，不能为空，初始值为false

      • v_xx boolean not null default false;

        --输入员工id，显示员工姓名、工资、个人所得税 （税率为0.03）  说明变量的作用
        
        declare 
        c_tax_rate number (3,2) := 0.03;
        --用户名
        v_name varchar2 (20);
        v_sal number (7,2);
        v_tax_sal number (7,2);
        begin
        --执行
        select last_name,v_sal from s_emp where id=&id;
        --计算所得税
        v_tax_sal := v_sal * c_tax_rate;
        --输出
        dbms_output.put_line('姓名：'|| v_name||'工资:'||v_sal||'交税：'||v_tax_sal);
        end;

  • 使用%type类型

    v_name s_emp.last_name%type;

与数据库中查到的数据类型一模一样

• 复合类型

  • 用于存放多个值得变量，主要包括

    • pl/sql记录
    • pl/sql表
    • 嵌套表
    • varray

  • pl/sql记录实例

  • 相当于结构体，一个变量可以存放多种类型的数值 .xx 取出

    ---pl/sql记录实例
    declare 
    --定义一个pl/sql记录类型s_emp_record 
    --类型包含三个数据name。salary。title
    
    type s_emp_record_type is record (name s_emp.last_name%type,salary s_emp.salary%type,title s_emp.title%type);
    --定义一个sp_record变量  类型是s_emp_record_type 一个变量里面可以存放三个数据
    sp_record s_emp_record_type;
    begin
    select last_name,salary,title into sp_record from s_emp where id= 1;
    dbms_output.put_line('员工名：'||sp_record.name);
    end;

  • pl/sql表
    相当于数组
    下标可以为负数

    declare
    --定义了一个pl/sql表类型，s_table_type
    --该类型是用于存放s_emp.last_name%type类型的数据
    --index by binary_integer下标是按整数排序
    type s_table_type is table of s_emp.last_name%type index by binary_integer;
    --定义s_table_type类型变量 s_table
    s_table s_table_type;
    begin
    select last_name into s_table(0) from s_emp where id=1;
    dbms_output.put_line('员工名:'|| s_table(0));
    end;

• 参照类型

是指用于存放数值指针的变量。通过使用参照变量可以使得程序共享相同对象，从而降低占用的空间。
在编写的时候，可以使用游标变量。

• 游标变量
  定义时不需要select语句但是在使用时必须要select语句。

  --使用p/sql编写一个块，可以输入部门号，并显示该部门所有员工姓名和他的工资
  declare 
  --定义游标类型
  type s_emp_cursor is ref cursor;
  --定义一个游标变量
  test_cursor s_emp_cursor;
  --定义变量
  v_name s_emp.last_name%type;
  v_salary s_emp.salary%type;
  begin
  --执行
  --把test_cursor和一个select结合
  open test_cursor for select last_name,salary from s_emp where dept_id=&id;
  --循环取出
  loop
  fetch test_cursor into v_name,v_salary;
  --判断是否test_cursor为空
  exit when test_cursor%notfound;
  dbms_output.put_line('名字 ； '||v_name||'工资:'||v_salary);
  end loop;
  end;

• lob

模块控制

• 条件分支

  if XX then
      xxx
      elsif
      xxx
      else
      xxx 
      end if;

• 循环

  --1.loop循环
  loop
  XX
  exit when xx
  end loop;
  --2.while循环
  while xx loop
  end loop;
  end;
  --3.for循环
  for i in reverse 1..10 loop
  xx
  end loop;
  end;

• 控制语句 goto 和 null

  • goto lable 语句 直接跳转到lable位置接着执行
  • null 不会执行任何操作

• 用于在逻辑上组合过程和函数，由包规范和包体组成

  • 创建包

    --创建包aa_package
    --声明该包有一个过程 update_sal
    --声明该包有一个函数 getSal
    create package aa_package is
    procedure update_sal (name varchar2,newSal number);
    function  getSal (name varchar2) return number;
    end;

  • 创建包体

    --创建包体 
    create or replace package body aa_package is
    procedure update_sal (name varchar2,newSal number) is
    begin
    update s_emp set salary=newSal where last_name=name;
    end;
    
    function getSal (name varchar2)
    return number is
    yearSal number;
    begin
    select salary*12+nvl(COMMENTS,0)*12 into yearSal from s_emp where last_name =name;
    return yearSal;
    end;
    end;

  • 调用包里的过程及函数

    • 调用过程

      call aa_package.update_sal ('Smith');

    • 调用函数

      var sal number ;
      call aa_package.getSal ('Smith') into:sal;

分页

Java使用JDBC调用

• 调用一个过程

pl/sql中

create table stu (
           id number,name varchar2(20)
     );

    create or replace procedure ss (s_id in number,s_name in varchar2) is 
    begin
    insert into stu values (s_id,s_name);
    end;

Java中

// 1.加载驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        // 2.得到连接
        Connection connection = DriverManager.getConnection(
                "jdbc:oracle:thin:@127.0.0.1:1521:XE","hito","root");
        // 3.创建CallableStatement
        // ss是过程名
        // ？？表示参数，有几个参数就有几个？
        CallableStatement cs = connection.prepareCall("{call ss(?,?)}");
        // 给？赋值
        cs.setInt(1,1);
        cs.setString(2,"Smith");
        // 执行
        cs.execute();

        cs.close();
        connection.close();

• 调用一个带有返回值的过程

–输入一个id，得到姓名
pl/sql过程

create or replace procedure getName (s_id in number,s_name out varchar2 ) is 
begin
select name into s_name from stu where id=s_id;
end;

Java代码

// 创建CallableStatement
        CallableStatement cs = connection
            .prepareCall("{call getName(?,?)}");
        // 给第一个？赋值
        cs.setInt(1,10);
        // 给第二个？赋值
        cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
        // 执行
        cs.execute();
        // 得到返回值
         String name = cs.getString(2);
        System.out.println("" + name);

• 调用有多个返回值的过程
  由于返回值较多，返回一个结果集[列表],所以不能用一般的参数，必须要用package以及要用到游标变量
  1.创建一个包

  --1.创建包，在包中定义了 test_cursor游标类型
  create or replace package testpackage AS
  TYPE test_cursor is ref cursor;
  end testpackage;

  2.建立存储过程

  create or replace procedure getCursor
  (d_id in number,p_cursor out testpackage.test_cursor ) is 
  begin
  open p_cursor for select * from s_emp where dept_id=d_id;
  end;

  3.java中调用

  CallableStatement cs = connection
              .prepareCall("{call getCursor (?,?)}");
      // 给第一个？赋值
      cs.setInt(1,41);
      cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);
      // 执行
      cs.execute();
      // 得到结果集
      ResultSet rs = (ResultSet) cs.getObject(2);
      while (rs.next()) {
          System.out.println(rs.getInt(1) + "---" + rs.getString(2) + "-"
                  + rs.getString(3));
      }

触发器

是指隐含的执行的存储过程。当定义触发器时，必须要指定触发的事件和触发的操作 常用的触发事件包括insert update delete语句，而触发操作实际就是一个pl/sql块。 可以使用create trigger来建立触发器