pl/sql编程语言

pl/sql编程语言用于存储过程和存储函数

声明方法

  1. declare
  2.      申明字段
  3. begin
  4.      业务处理
  5. end;

例子：

  1. -- 声明方法
  2. declare
  3.    i number(2) := 10;
  4.    s varchar2(10) := '小明';
  5.    ena emp.ename%type;-- 引用型变量
  6.    emprow emp%rowtype;-- 记录型变量
  7. begin
  8.    dbms_output.put_line(i);
  9.    dbms_output.put_line(s);
  10.    -- 必须查询出值才能输出
  11.    select ename into ena from emp where empno = 7900;
  12.    dbms_output.put_line(ena);
  13.    select * into emprow from emp where empno = 7900;
  14.    -- 只能选择一列一列输出
  15.    dbms_output.put_line(emprow.ename || '职位是' || emprow.job);
  16. end;

输出：
image.png

if判断

  1. if 条件 then
  3.    业务处理A
  4. elsif 条件 then
  6.   业务处理B
  7. else
  9.   最终业务处理C
  10. end if;

例子： 

  1. declare
  2.    i number(3) := &n; -- 输入流
  3. begin
  4.   if i<18 then
  5.     dbms_output.put_line('未成年');
  6.   elsif i<40 then
  7.     dbms_output.put_line('中年人');
  8.   else
  9.     dbms_output.put_line('老年人');
  10.   end if;
  11. end;

输出：
image.png
image.png

loop循环

  1. while 条件 loop
  3.      业务处理，该业务必须有退出循环处理，否则就是死循环
  5. end loop;

例子：

  1. declare
  2.    i number(2) := 1;
  3. begin
  4.    while i<11 loop
  5.       dbms_output.put_line(i);
  6.       i := i+1;
  7.    end loop;
  8. end;

输出：
image.png

exit退出循环

  1. exit when 条件;

例子：

  1. declare
  2.    i number(2) := 1;
  3. begin
  4.    loop
  5.      exit when i>10;
  6.      dbms_output.put_line(i);
  7.      i := i+1;
  8.    end loop;
  9. end;

输出：
image.png

for循环

  1. for 变量名 in 数字范围 loop
  2.     业务处理
  3. end loop;

例子：

  1. declare
  3. begin
  4.    for i in 2..10 loop
  5.      dbms_output.put_line(i);
  6.    end loop;
  7. end;

输出：
image.png

游标

可以存放多个对象，多行记录

  1. declare
  2.     cursor 游标名 is 查询语句;
  3. begin
  4.     open 游标名;
  5.          业务处理
  6.     close 游标名;
  7. end;

例子：
— 输出员工表中所有的员工名 declare cursor c1 is select * from emp; emprow emp%rowtype; begin open c1; loop fetch c1 into emprow; exit when c1%notfound; dbms_output.put_line(emprow.ename); end loop; close c1; end;
输出：
image.png

存储过程

  1. create or replace procedure 存储过程名(参数名 参数类型)
  2. is
  3. begin
  5.      业务处理
  6. end;

例子：

  1. -- 存储过程：提前编译好的SQL语句，放置在数据库端，可以直接使用。
  2. -- or replace ：重名时，修改存储名，保证存储过程编写正确
  3. create or replace procedure p1
  4.        (eno emp.empno%type)
  5. is 
  6. begin
  7.   update emp set sal = sal +100 where empno = eno;
  8.   commit;
  9. end;
  11. select * from emp where empno = 7499;
  12. -- 测试存储过程 p1
  13. declare
  15. begin
  16.   p1(7499);
  17. end;

输出：
未调用存储过程前
image.png
调用后
image.png

存储函数

  1. create or replace function 存储函数名(参数名 [in]  参数类型) return 返回类型
  2. is
  4.     返回名 返回参数类型
  5. begin
  7.      查询业务处理
  8.      return 返回名;
  9. end;

例子：

  1. -- 存储函数
  2. -- 存储过程和存储函数区别
  3. -- 函数有返回值，过程没有返回值
  5. -- 返回存储函数结果类型不能带长度 
  6. create or replace function f_yearsal(eno in emp.empno%type) 
  7.        return number
  8. is 
  9.       -- 输出结果参数声明
  10.        s number(10);
  11. begin
  12.   select sal* 12 +nvl(comm,0) into s from emp where empno = eno;
  13.   return s;
  14. end;
  16. -- 存储函数在调用的时候，返回值需要接收
  17. declare
  18.    s number(10);
  19. begin
  20.   s := f_yearsal(7499);
  21.   dbms_output.put_line(s);
  22. end;

输出：
image.png
例子：

  1. -- out 类型参数
  2. create or replace procedure p_yearsal(eno emp.empno%type,yearsal out number)
  3. is
  4.        s number(10);
  5.        c emp.comm%type;
  6. begin
  7.   select sal* 12 , nvl(comm,0) into s ,c from emp where empno =eno;
  8.   yearsal := s+c;
  9. end;
  11. declare
  12.   yearsal number(10);
  13. begin
  14.   p_yearsal(7499,yearsal);
  15.   dbms_output.put_line(yearsal);
  16. end;

输出
image.png

in 和 out类型参数区别

如果使用了into查询语句赋值或者:= 赋值操作，都需要使用out来修饰

  1. -- 查询员工名字和部门名称 (传统方式)
  2. select e.ename , d.dname from emp e inner join dept d using(deptno);
  4. -- 存储函数,查询出员工所在的部门名称
  5. create or replace function fdna(dno in dept.deptno%type) 
  6. return dept.dname%type
  7. is
  8.   dna dept.dname%type;
  9. begin
  10.   select dname into dna from dept where deptno = dno;
  11.   return dna;
  12. end;
  13. -- 查询出员工名字
  14. select e.ename ,fdna(e.deptno) from emp e;

触发器

制定一个规则，进行增删改查操作的时候，符合条件情况下，自动触发
for each row ： 为了使用：old或者：new对象或者一行记录

  1. create or replace trigger 触发器名
  2. after/before
  3. insert/update
  4. on 表名
  5. declare
  6. begin
  8.    业务处理
  9. end;

语句级触发器

不含有for each row 的触发器

  1. -- 插入一条记录，输出一个新员工入职
  2. create or replace trigger t1
  3. -- 增加语句之前执行
  4. after
  5. insert
  6. on person
  7. declare
  9. begin
  10.   dbms_output.put_line('一个新员工入职');
  11. end;
  12. -- 触发
  13. insert into person values(1,'小红','0');
  14. commit;
  16. select * from person;

行级触发器

含有for each row 的触发器

  1. -- 不能给员工降薪
  2. -- raise_application_error(-20001~-20999之间, '错误提示信息');
  3. create or replace trigger t2
  4. -- 修改后执行
  5. before
  6. update
  7. on emp
  8. for each row
  9. declare
  11. begin
  12.   -- 旧工资比新工资高
  13.   if :old.sal>:new.sal then
  14.     -- 报异常
  15.      raise_application_error(-20001, '不能给员工降薪');
  16.   end if;
  17. end;
  18. ----触发t2
  19. select * from emp where empno = 7499;
  20. update emp set sal=sal-1 where empno = 7499;
  21. commit;

触发器实现主键自增

  1. -- 触发器实现主键自增
  2. create or replace trigger auid
  3. before
  4. insert
  5. on person
  6. for each row
  7. declare
  9. begin
  10.   select s_person.nextval into :new.id from dual;
  11. end;
  13. select * from person;
  15. -- 使用auid实现主键自增
  16. insert into person (name) values('aa');
  17. commit;
  18. -- 新增id，主键索引也不会增加id值
  19. insert into person values(1,'bb','1');
  20. commit;

Java 连接Oracle

下载Oracle的jdbc
JDBC and UCP Downloads page
选择自己电脑安装的jdk版本，我安装的是JDK1.8版本，即下载ojdbc8.jar
image.png
下载完成后，打开idea
创建一个maven工程
pom

  1.     <dependencies>
  2.         <!-- https://mvnrepository.com/artifact/com.oracle/ojdbc14 -->
  3.         <dependency>
  4.             <groupId>com.oracle</groupId>
  5.             <artifactId>ojdbc14</artifactId>
  6.             <version>10.2.0.4.0</version>
  7.             <scope>runtime</scope>
  8.         </dependency>
  9.         <dependency>
  10.             <groupId>junit</groupId>
  11.             <artifactId>junit</artifactId>
  12.             <version>4.10</version>
  13.         </dependency>
  14.     </dependencies>

odbc8需要自己导入
image.png
我选择更改ojdbc名称
image.png
引入jar包即可。

  1. package com.hikktn.domian;
  3. import org.junit.Test;
  5. import java.sql.Connection;
  6. import java.sql.DriverManager;
  7. import java.sql.PreparedStatement;
  8. import java.sql.ResultSet;
  10. /**
  11.  * @ClassName OracleDemo
  12.  * @Description TODO
  13.  * @Author lisonglin
  14.  * @Date 2021/5/2 3:19
  15.  * @Version 1.0
  16.  */
  17. public class OracleDemo {
  19.     @Test
  20.     public void javaConnectionOracle() throws Exception {
  21.         Class.forName("oracle.jdbc.driver.OracleDriver");
  22.         System.out.println("开始尝试连接数据库！");
  23.         String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";//Oracle的默认数据库名
  24.         String user = "C##HIKKTN";// 系统默认的用户名
  25.         String password = "hikktn";// 安装时设置的密码
  26.         Connection connection = DriverManager.getConnection(url, user, password);// 获取连接
  27.         // 预编译设置statement对象
  28.         PreparedStatement ps = connection.prepareStatement("select * from emp where empno = ?");
  29.         // 设置参数
  30.         ps.setObject(1,7499);
  31.         // 输出结果
  32.         ResultSet resultSet = ps.executeQuery();
  33.         while (resultSet.next()){
  34.             System.out.println(resultSet.getString("ename"));
  35.         }
  36.         // 释放资源
  37.         resultSet.close();
  38.         ps.close();
  39.         connection.close();
  40.     }
  41. }

输出：
image.png