pl/sql编程语言
pl/sql编程语言用于存储过程和存储函数
声明方法
declare
申明字段
begin
业务处理
end;
例子:
-- 声明方法
declare
i number(2) := 10;
s varchar2(10) := '小明';
ena emp.ename%type;-- 引用型变量
emprow emp%rowtype;-- 记录型变量
begin
dbms_output.put_line(i);
dbms_output.put_line(s);
-- 必须查询出值才能输出
select ename into ena from emp where empno = 7900;
dbms_output.put_line(ena);
select * into emprow from emp where empno = 7900;
-- 只能选择一列一列输出
dbms_output.put_line(emprow.ename || '职位是' || emprow.job);
end;
输出:
if判断
if 条件 then
业务处理A
elsif 条件 then
业务处理B
else
最终业务处理C
end if;
例子:
declare
i number(3) := &n; -- 输入流
begin
if i<18 then
dbms_output.put_line('未成年');
elsif i<40 then
dbms_output.put_line('中年人');
else
dbms_output.put_line('老年人');
end if;
end;
输出:
loop循环
while 条件 loop
业务处理,该业务必须有退出循环处理,否则就是死循环
end loop;
例子:
declare
i number(2) := 1;
begin
while i<11 loop
dbms_output.put_line(i);
i := i+1;
end loop;
end;
输出:
exit退出循环
exit when 条件;
例子:
declare
i number(2) := 1;
begin
loop
exit when i>10;
dbms_output.put_line(i);
i := i+1;
end loop;
end;
输出:
for循环
for 变量名 in 数字范围 loop
业务处理
end loop;
例子:
declare
begin
for i in 2..10 loop
dbms_output.put_line(i);
end loop;
end;
输出:
游标
可以存放多个对象,多行记录
declare
cursor 游标名 is 查询语句;
begin
open 游标名;
业务处理
close 游标名;
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;
输出:
存储过程
create or replace procedure 存储过程名(参数名 参数类型)
is
begin
业务处理
end;
例子:
-- 存储过程:提前编译好的SQL语句,放置在数据库端,可以直接使用。
-- or replace :重名时,修改存储名,保证存储过程编写正确
create or replace procedure p1
(eno emp.empno%type)
is
begin
update emp set sal = sal +100 where empno = eno;
commit;
end;
select * from emp where empno = 7499;
-- 测试存储过程 p1
declare
begin
p1(7499);
end;
输出:
未调用存储过程前
调用后
存储函数
create or replace function 存储函数名(参数名 [in] 参数类型) return 返回类型
is
返回名 返回参数类型
begin
查询业务处理
return 返回名;
end;
例子:
-- 存储函数
-- 存储过程和存储函数区别
-- 函数有返回值,过程没有返回值
-- 返回存储函数结果类型不能带长度
create or replace function f_yearsal(eno in emp.empno%type)
return number
is
-- 输出结果参数声明
s number(10);
begin
select sal* 12 +nvl(comm,0) into s from emp where empno = eno;
return s;
end;
-- 存储函数在调用的时候,返回值需要接收
declare
s number(10);
begin
s := f_yearsal(7499);
dbms_output.put_line(s);
end;
输出:
例子:
-- out 类型参数
create or replace procedure p_yearsal(eno emp.empno%type,yearsal out number)
is
s number(10);
c emp.comm%type;
begin
select sal* 12 , nvl(comm,0) into s ,c from emp where empno =eno;
yearsal := s+c;
end;
declare
yearsal number(10);
begin
p_yearsal(7499,yearsal);
dbms_output.put_line(yearsal);
end;
输出
in 和 out类型参数区别
如果使用了into查询语句赋值或者:= 赋值操作,都需要使用out来修饰
-- 查询员工名字和部门名称 (传统方式)
select e.ename , d.dname from emp e inner join dept d using(deptno);
-- 存储函数,查询出员工所在的部门名称
create or replace function fdna(dno in dept.deptno%type)
return dept.dname%type
is
dna dept.dname%type;
begin
select dname into dna from dept where deptno = dno;
return dna;
end;
-- 查询出员工名字
select e.ename ,fdna(e.deptno) from emp e;
触发器
制定一个规则,进行增删改查操作的时候,符合条件情况下,自动触发
for each row : 为了使用:old或者:new对象或者一行记录
create or replace trigger 触发器名
after/before
insert/update
on 表名
declare
begin
业务处理
end;
语句级触发器
不含有for each row 的触发器
-- 插入一条记录,输出一个新员工入职
create or replace trigger t1
-- 增加语句之前执行
after
insert
on person
declare
begin
dbms_output.put_line('一个新员工入职');
end;
-- 触发
insert into person values(1,'小红','0');
commit;
select * from person;
行级触发器
含有for each row 的触发器
-- 不能给员工降薪
-- raise_application_error(-20001~-20999之间, '错误提示信息');
create or replace trigger t2
-- 修改后执行
before
update
on emp
for each row
declare
begin
-- 旧工资比新工资高
if :old.sal>:new.sal then
-- 报异常
raise_application_error(-20001, '不能给员工降薪');
end if;
end;
----触发t2
select * from emp where empno = 7499;
update emp set sal=sal-1 where empno = 7499;
commit;
触发器实现主键自增
-- 触发器实现主键自增
create or replace trigger auid
before
insert
on person
for each row
declare
begin
select s_person.nextval into :new.id from dual;
end;
select * from person;
-- 使用auid实现主键自增
insert into person (name) values('aa');
commit;
-- 新增id,主键索引也不会增加id值
insert into person values(1,'bb','1');
commit;
Java 连接Oracle
下载Oracle的jdbc
https://www.oracle.com/database/technologies/appdev/jdbc-ucp-19c-downloads.html
选择自己电脑安装的jdk版本,我安装的是JDK1.8版本,即下载ojdbc8.jar
下载完成后,打开idea
创建一个maven工程
pom
<dependencies>
<!-- https://mvnrepository.com/artifact/com.oracle/ojdbc14 -->
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc14</artifactId>
<version>10.2.0.4.0</version>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.10</version>
</dependency>
</dependencies>
odbc8需要自己导入
我选择更改ojdbc名称
引入jar包即可。
package com.hikktn.domian;
import org.junit.Test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
/**
* @ClassName OracleDemo
* @Description TODO
* @Author lisonglin
* @Date 2021/5/2 3:19
* @Version 1.0
*/
public class OracleDemo {
@Test
public void javaConnectionOracle() throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
System.out.println("开始尝试连接数据库!");
String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";//Oracle的默认数据库名
String user = "C##HIKKTN";// 系统默认的用户名
String password = "hikktn";// 安装时设置的密码
Connection connection = DriverManager.getConnection(url, user, password);// 获取连接
// 预编译设置statement对象
PreparedStatement ps = connection.prepareStatement("select * from emp where empno = ?");
// 设置参数
ps.setObject(1,7499);
// 输出结果
ResultSet resultSet = ps.executeQuery();
while (resultSet.next()){
System.out.println(resultSet.getString("ename"));
}
// 释放资源
resultSet.close();
ps.close();
connection.close();
}
}
输出: