pl/sql编程基础

最新推荐文章于 2022-09-24 17:41:38 发布

宏微

最新推荐文章于 2022-09-24 17:41:38 发布

阅读量882

点赞数 1

分类专栏：数据库文章标签： oracle plsql

本文链接：https://blog.csdn.net/shuaicihai/article/details/53958743

版权

数据库专栏收录该内容

11 篇文章 0 订阅

订阅专栏

PL/SQL是一种程序语言，叫做过程化SQL语言（Procedural Language/SQL）。PL/SQL是Oracle数据库对SQL语句的扩展。

语法

pl/sql语句块：

控制台输出信息
set serveroutput on;//设置为on才能在控制台输出

基本语法格式

declare
声明部分,声明变量或类型等
v_id integer;变量名在前,类型在后

begin
执行部分
v_id :=3; // :=赋值
dbms_output.put_line(‘hell0 world’ || v_id);
//异常处理
exception
when others then
//执行语句
end;

PL/SQL程序四个部分：
声明部分主要用来声明变量并且初始化变量，
执行部分，执行程序
异常处理部分，处理异常程序
结束语句

语法规范

字符值和日期值必须用单引号括起
数字值可以使用简单数字或者科学计数法表示
注释，/和/之间的多行注释，–开始的单行注释
每条语句必须以分号结束，包括PL/SQL结束部分的END关键字后面也需要分号

常亮、变量命令规则

定义的标识符名称应该遵循命名规则
⑴名称最多可以包含30个字符；
⑵不能直接使用保留字，如果需要，需要使用双引号括起来；
⑶第一个字符必须以字母开始；
⑷不要用数据库的表或者科学计数法表示；
在声明常量和变量的时候可以为其设置初始化值，也可以强制设置not null
可以使用赋值运算符（：=）或DEFAULT保留字来初始化标识符，为标识符赋初始值
在声明标识符时，每行只能声明一个标识符

变量、常量类型

简单变量
复合（组合）变量
外部变量，在函数外部定义的全局变量，它的作用域是从变量的定义处开始，到本程序文件的结尾。

自定义变量类型

oracle数据类型：integer varchar2等
自定义数据类型
1. 定义和列的类型保持一致
v_sal emp.sal%type;– %type固定格式前面写表名.列名
2. 定义和表达类型保持一致
v_emp emp%rowtype; – 和emp表的结构一致
3. 定义自己的封装类（对象）
–声明的是类型
type type_emp_name_sal is record(v_empname EMP.ENAME%type,v_empsal EMP.SAL%type);
–变量名是v_name_sal 类型是type_emp_name_sal
v_name_sal type_emp_name_sal;
4. 数组
–声明数组类型
type int_array is table of integer index by binary_integer;–binary_integer 正负
–int类型数组的变量
v_numbers int_array;

流程控制语句

if语句

if v_id = 1 then
dbms_output.put_line(v_id);
elsif v_id = 2 then
dbms_output.put_line(‘elsif’);
else
dbms_output.put_line(v_id);
end if;

2.case语句

case
when v_id = 1 then
    dbms_output.put_line(v_id);
when v_id = 2 then
    dbms_output.put_line('elsif');
else 
    dbms_output.put_line(v_id);
end case;

循环语句
1. for循环（用到变量不需要声明）
  –for循环
  for v_i in reverse 1..10 loop
  SYS.DBMS_OUTPUT.PUT_LINE(v_i);
  end loop;
2.while循环
–while循环
while v_id < 10 loop
SYS.DBMS_OUTPUT.PUT_LINE(v_id);
–条件的改变
v_id := v_id + 1;
end loop;

3.loop 循环
loop
SYS.DBMS_OUTPUT.PUT_LINE(v_id);
–条件的改变
v_id := v_id + 1;
exit when v_id = 10;–exit跳出循环
end loop;

4.通过goto语句完成循环
<<a>> – 标记
SYS.DBMS_OUTPUT.PUT_LINE(v_id);
v_id := v_id + 1;
if v_id < 10 then
goto a;
end if;

执行操作

游标的操作

1.声明游标
2.打开游标
3.循环提前游标
4.关闭游标（释放游标占用的空间）

游标的操作

游标的作用:迭代结果集

set serveroutput on;//打开打印设置

declared
–声明游标
–语法 cursor 游标名 is 查询语句
cursor c_emp is select * from emp;

v_emprow emp%rowtype

begin
–打开游标
open c_emp ;

--循环提取游标
loop
    fetch c_emp into v_emprow ;
    --判断
    if c_emp%notfound then --%found 找到了 %notfound 没有找到
        exit;--跳出循环
    end if;
    --处理数据
    dbms_output.put_line(c_emprow.empno);

end loop;

--关闭游标
close c_emp;

end

for循环与游标

declare
cursor c_temp is select * from dept;
befgin
–for循环提取游标
for v_temprow in c_temp loop
dbms_ouput.put_line(v_temprow.dname);
end loop;
end;

Oracle系统包

Oracle 提供的系统包多达几百个，查看所有定义包的文档使用说明 https://docs.oracle.com/database/121/ARPLS/toc.htm 。

–随机整数,0-99
select abs(mod(dbms_random.random,100)) from dual;

–随机字符串
dbms_random.string(‘a’,5);

存储过程

作用: 封装逻辑代码,对pl/sql语句的封装，命名语句块，通过名字直接调用

无参数存储过程
create or replace procedure pro1
is –declare 换成 is 或 as

begin
dbms_output.put_line(‘prol’);
end;

有参数
– in 输入参数
– out 输出参数
–in out 输入输出参数

create or replace procedure update_emp_sal(v_empno emp.empno%type,v_sal emp.sal%type)
is
begin
update emp set sal = v_sal where empno = v_empno;
commit;
exception
when others then
rollback;
end;

函数

除了有返回值外,和过程语法完全一样

create or replace FUNCTION get_sal_from_empno(v_empno in EMP.EMPNO%type) return emp.sal%type is

–声明部分
v_empno EMP.EMPNO%type := 7782;
v_sal EMP.SAL%type;
begin

select sal into v_sal from emp where empno = v_empno;

return v_sal;

end;

存储过程与函数使用上的区别
1. 函数注重数字运算
2. 过程注重复杂的业务处理

过程或函数调用方式之参数绑定

declare
 v_sal emp.sal%type;
begin
  -- get_sal(v_empno in EMP.EMPNO%type,v_sal in out emp.sal%type)
  get_sal(v_sal  => v_sal,v_empno => 7782);
  SYS.DBMS_OUTPUT.PUT_LINE(v_sal);
end;

注：形式参数 => 实际参数

程序包和包体

作用: 封装过程函数类型

程序包 –类似java接口，在包中声明的过程或函数，在包体中必须实现
create or replace package my_pack is

程序包体–类型实现接口的类

触发器

触发器中不能写事务的提交或者回滚

1.事后触发器(行级触发器)

create or replace trigger tri_dept
after
update on dept
for each row
begin
   --语句
end;

2.语句触发器(事前触发器)

 create or replace trigger tri_op_emp
before
insert or update or delete on emp
begin
  --星期日不能对emp做任何操作
  if to_char(sysdate,'day') = '星期六' then
    RAISE_APPLICATION_ERROR(-20008, '星期六不能操作该表');
  end if;
end;
是星期六不能操作该表

通过jdbc调用过程或函数

1. 加载驱动jar
    /demo/oracle-10.0.jar
2. jdbc连接封装
3. 调用过程或函数

public static void updateEmpSalPro(){
    Connection con = JdbcUtils.getConection();
    CallableStatement cs = null;
    try {
        cs = con.prepareCall("{call my_pack.update_emp_sal(?,?) }");
        cs.setInt(1, 7782);
        cs.setDouble(2, 999.99);
        cs.execute();

    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }finally{
        JdbcUtils.close(cs, null, con);
    }
}

/**
 * 测试返回结果集的存储过程
 */
public static void testProResultSet(){
    Connection con = JdbcUtils.getConection();
    CallableStatement cs = null;
    ResultSet rs = null;
    try {
        cs = con.prepareCall("{call my_pack.get_refdata(?) }");
        cs.registerOutParameter(1, OracleTypes.CURSOR);
        cs.execute();
        OracleCallableStatement ocs = (OracleCallableStatement) cs;
        rs = ocs.getCursor(1);
        while (rs.next()) {
            System.out.println(rs.getDouble(1));
        }
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }finally{
        JdbcUtils.close(cs, rs, con);
    }
}

public static void testCallFunction(){
    Connection con = JdbcUtils.getConection();
    CallableStatement cs = null;
    ResultSet rs = null;
    try {
        cs = con.prepareCall("{? = call my_pack.add_number(?,?) }");
        cs.registerOutParameter(1, OracleTypes.INTEGER);
        cs.setInt(2, 3);
        cs.setInt(3, 4);

        cs.execute();
        int sum = cs.getInt(1);
        System.out.println(sum);
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }finally{
        JdbcUtils.close(cs, rs, con);
    }
}

jdbc调用存储过程优点

        1. 效率高
        2. 安全

异常

根据异常名字和错误代号

decalre

begin

    --exception异常处理模块
    exception
        when Too_many_rows then
            dbms_output.put_line('Too_many_rows');--异常名字
        when others then
            case 
                when sqlcode = -1476 then -- 异常代号
                    dbms_output.put_line('除数为0');
                when sqlcode = -1422 then
                    sys.dbms_output.put_line('返回了多行');
            end case; 
            dbms_output.put_line('exception'|| sqlerrm);--sqlerrm异常详细信息

end;

自定义异常

declare
    --自定义异常
    v_myexcp exception;
    v_sal emp.sal%type;

    pragma exception_init(v_myexcp,-1644);--没有异常信息
begin
    select sal into v_sal from emp where empno = 9527;
    if v_sal > 3000 then
        --抛出异常
        raise v_myexcp;
        --抛出异常的函数
        raise_application_error(-20999,'自定义异常');--没有异常名字
    end if;

    exception 
        when others then 
            dbms_output.put_line(sqlcode||'<>'||sqlerrm);
end;