Oracle_2

最新推荐文章于 2021-09-08 19:15:02 发布

theONLY24

最新推荐文章于 2021-09-08 19:15:02 发布

阅读量300

点赞数

分类专栏： oracle 文章标签： oracle

本文链接：https://blog.csdn.net/m0_46157986/article/details/104148886

版权

oracle 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

Oracle_2

一、视图
二、索引
三、pl/sql 基本语法
四、存储过程
五、存储函数
- 1. 存储过程和存储函数的区别
六、触发器
七、Java 程序调用存储过程

一、视图

视图：

就是封装了一条复杂查询的语句。

1. 创建视图

CREATE VIEW 视图名称 AS 子查询;

范例1：

建立一个视图，此视图包括了 20 部门的全部员工信息

create view empvd20 as select * from emp t where t.deptno = 20;

视图创建完毕就可以使用视图来查询，查询出来的都是 20 部门的员工

使用视图查询：

select * from EMPVD20 t;

2. 创建或覆盖已存在的视图

CREATE OR REPLACE VIEW 视图名称 AS 子查询

如果视图已经存在我们可以使用语法 2 来创建视图，这样已有的视图会被覆盖。

create or replace view empvd20 as select * from emp t where t.deptno = 20;

3. 创建或覆盖已存在的视图并设置只读

CREATE OR REPLACE VIEW 视图名称 AS 子查询 WITH READ ONLY

设置视图为只读。

create or replace view empvd20 as select * from emp t where t.deptno = 20 with read only;

我们一般不会去修改视图。我们可以设置视图为只读。

二、索引

索引是用于加速数据存取的数据对象。

合理地使用索引可以大大降低 I/O 次数，从而提高数据访问性能。

索引有很多种，我们主要介绍常用的几种。

单列索引
复合索引

1. 为甚么索引会加快查询速度

图书馆：如果杂乱地放书的话检索起来就非常困难,所以将书分类，然后再建一个箱子，箱子里面放卡片，卡片里面可以按类查询,按书名查或者类别查,这样的话速度会快很多很多，这个就有点像索引。索引的好处就是提高你找到书的速度，但是正是因为你建了索引，就应该有人专门来维护索引，维护索引是要有时间精力的开销的，也就是说索引是不能乱建的，所以建索引有个原则：如果有一个字段如果不经常查询，就不要去建索引。现在把书变成我们的表，把卡片变成我们的索引，就知道为什么索引会快，为什么会有开销。

2. 创建索引

2.1 单列索引

单列索引是基于单个列所建立的索引，比如:

create index 索引名 on 表名(列名);

范例：给 person 表的name建立索引

create index pname_index on person(name);

2.2 复合索引

复合索引是基于两个列或多个列的索引。在同一张表上可以由多个索引，但是要求列的组合必须不同。

比如：

create index emp_idx1 on emp(ename,job);
create index emp_idx2 on emp(job,ename);

范例：给 person 表创建一个 name 和 gender 的索引

create index pname_gender_index on person(name, gender);

3. 索引的使用原则

在大表上建立索引才有意义
在 where子句后面或者是连接条件上的字段建立索引
表中数据修改频率高时不建议建立索引

三、pl/sql 基本语法

什么是pl/sql？

PL/SQL（Procedure Language/SQL）

PLSQL 是 Oracle 对sql 语言的过程化扩展，指在 SQL 命令语言中增加了过程处理语句（如分支、循环等），使 SQL语言具有过程处理能力。把SQL 语言的数据操纵能力与过程语言的数据处理能力结合起来，使得 PLSQL面向过程但比过程语言简单、高效、灵活和实用。

范例 1：为职工涨工资，每人涨 10％的工资。
update emp set sal=sal*1.1
范例 2：例 2: 按职工的职称长工资,总裁涨 1000元,经理涨800 元，其他人员涨 400 元。
这样的需求我们就无法使用一条 SQL来实现，需要借助其他程序来帮助完成，也可以使用 pl/sql。

1. pl/sql 程序语法

declare
	说明部分 （变量说明，游标申明，例外说明 〕
begin
	语句序列 （DML 语句〕…
exception
	例外处理语句
End;

2. 常量和变量定义

在程序的声明阶段可以来定义常量和变量。

:=：给变量赋值

变量的基本类型就是 oracle 中的建表时字段的变量如 char, varchar2, date, number,boolean, long

定义语法：

变量名数据类型(长度)

例如：varl char(15);，Psal number(9,2);
说明变量名、数据类型和长度后用分号结束说明语句。
常量定义：married constant boolean:=true

引用变量

例如：Myname emp.ename%type;
引用型变量，即my_name 的类型与emp 表中 ename 列的类型一样
在 sql中使用 into 来赋值：

declare 
	emprec emp.ename%type; -- 声明变量emprec的数据类型为emp表中ename的数据类型
begin 
	-- 将emp表中员工编号为7396的ename的值赋给变量emprec
	select t.ename into emprec from emp t where t.emptno = 7396; 
	dbms_output.put_line(emprec);
end;

记录型变量

Emprec emp%rowtype;

记录变量分量的引用
emp_rec.ename:='ADAMS';

declare 
	p emp%rowtype; -- 声明变量p的数据类型为emp表的一行
begin 
	-- 将将emp表中员工编号为7396这行数据赋给变量emprec
	select * into p from emp t where t.empno = 7369; 
	dbms_output.put_line(p.ename || ' ' || p.sal);
end;

3. if分支

语法1：

IF 条件 THEN 语句1;
	语句2;
END IF;

范例 1：

如果从控制台输入 1 则输出我是 1

declare 
	pnum number := &num;
begin
	if pnum = 1 then
		dbms_output.put_line('我是1');
  end if;
end;

语法2：

IF 条件 THEN 语句序列1;
	ELSE 语句序列2;
END IF;

范例 2：

如果从控制台输入 1 则输出我是 1否则输出我不是 1

declare 
	pnum number := &num;
begin
	if pnum = 1 then
		dbms_output.put_line('我是1');
	else
		dbms_output.put_line('我不是1');
  end if;
end;

语法3：

IF 条件 THEN 语句;
ELSE IF 语句 THEN 语句;
ELSE 语句;
END IF;

范例 3：

判断人的不同年龄段 18岁以下是未成年人，18岁以上 40以下是成年人，40以上是老年人

declare
	mynum number := &num;
begin
	if mynum < 18 then
		dbms_output.put_line('未成年人');
	elsif mynum > 18 then 
		dbms_output.put_line('成年人呢');
	elsif mynum >= 40 then  
		dbms_output.put_line('来年人');
	end if;
end;

4. lOOP循环语句

语法1：

WHILE 条件 LOOP
	语句序列;
END LOOP;

范例:

使用语法 1 输出 1 到10 的数字

declare 
  n number := 1;
begin
  while n <= 10 loop
        dbms_output.put_line(n);
        n := n+1;
  end loop;
end;

语法2：

LOOP
EXIT [WHEN 条件];
	语句序列;
END LOOP;

范例：

使用语法 2 输出 1 到10 的数字

declare 
  n number := 1;
begin
  loop
    exit when n > 10;
         dbms_output.put_line(n);
        n := n+1;
    end loop;
end;

语法3：

FOR 条件 LOOP
 语句序列;
END LOOP;

范例：

使用语法 3 输出 1 到10 的数字

declare 
    n number := 1;
begin
  for n in 1 .. 10 loop
      dbms_output.put_line(n);
  end loop;
end;

5. 游标 Cursor

在写Java程序中有集合的概念，那么在pl/sql中也会用到多条记录，这时候我们就需要用到游标。

游标可以存储查询返回的多条数据。

5.1 语法：

CURSOR 游标名称 [(参数名 数据类型. 参数名 数据类型......)] IS SELECT 语句;

例如：

cursor c1 is select ename from emp;

5.2 使用步骤

打开游标：
open 游标名;（打开游标进行查询）
取一行游标的值：

fetch 游标名 into 变量名;（去一行到变量中）
关闭游标：

close 游标名;（关闭游标释放资源）
游标的结束方式

exit when 游标名%notfound;

**注意：**上面的变量名必须和表中对应的数据类型一致

范例 1：

使用游标方式输出 emp 表中的员工编号和姓名

declare
	cursor pc is		-- 定义游标
		select * from emp;
	pemp emp%rowtype; 	-- 声明变量 pemp 数据类型为 emp表中的一行数据
begin
	open pc;			-- 打开游标
	loop				-- loop循环
	fetch pc into pemp;	 -- 取一行游标中的值到变量 pemp 中
	exit when pc%notfound; -- 定义关闭游标的方式
	dbms_output.put_line(pemp.empno || ' ' || pemp.ename); -- 
	end loop; -- 
	close pc; -- 关闭游标
end;

范例 2：

按员工的工种涨工资,总裁 1000 元，经理涨 800 元其，他人员涨 400 元。
备份出一张新表为 myemp：create table myemp as select * from emp;

declare
	cursor pc is
		select * from myemp;
	addsal myemp.sal%type;
	pemp myemp%rowtype;
begin
	open pc;
	loop
        fetch pc into pemp;
        exit when pc%notfound;
        if pemp.job = 'PRESIDENT' then
            addsal := 1000;
        elsif pemp.job = 'MANAGER' then
            addsal := 800;
        else
            addsal := 400;
        end if;
	update myemp t set t.sal = t.sal + addsal where t.empno = pemp.empno;
	end loop;
	close pc;
end;

范例 3：

写一段PL/SQL 程序，为部门号为 10 的员工涨工资。

declare
	cursor pc(dno myemp.deptno%type) is
		select empno from myemp where deptno = dno;
	pno myemp.empno%type;
begin
	open pc(20);
	loop
	fetch pc into pno;
	exit when pc%notfound;
	update myemp t set t.sal = t.sal + 1000 where t.empno = pno;
	end loop;
	close pc;
end;

四、存储过程

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL语句集，经过编译后存储在数据库中，用户可以通过制定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

存储过程是数据库的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

1. 创建存储过程

语法：

CREATE [OR REPLACE] PROCEDURE 
	存储过程名称[(参数名 in/out 数据类型)]
AS
BEGIN
	PLSQL子程序体;
END;

-- 或者

CREATE [OR REPLACE] PROCEDURE 
	存储过程名称[(参数名 in/out 数据类型)]
IS
BEGIN
	PLSQL子程序体;
END 存储过程名称;

**范例：**创建一个输出 helloword 的存储过程

create or replace procedure helloword 
is
begin 
	dbms_output.put_line('hello world');
end helloworld;

2. 调用存储过程

在PL/SQL中调用存储过程

BEGIN
	helloworld;
END;

3. 涨工资范例

给指定的员工涨 100 工资，并打印出涨前和涨后的工资
分析：我们需要使用带有参数的存储过程

create or replace procedure addSall(eno in number)
is
	pemp myemp%rowtype;
begin 
	select * into pemp form myemp where empno = eno;
	update myemp set sal = sal + 100 where empno = eno;
	dbms_output.put_line('涨工资前' || pemp.sal || '涨工资后' || (pemp.sal + 100));
end addSall;

-- 调用
begin
	addSall(eno => 7902);
	commit;
end;

五、存储函数

CREATE OR REPLACE FUNCTION 
	存储函数名(Name in type, Name in type ......) RETURN 
	数据类型 
IS 
	结果变量名 数据类型;
BEGIN
	......
	RETURN(结果变量);
END 存储函数名;

1. 存储过程和存储函数的区别

一般来讲，过程和函数的区别在于函数可以有一个返回值，而过程没有。

但过程和函数都可以通过 out 指定一个或多个输出参数。

我们可以利用 out 参数，在过程和函数中实现返回多个值。

范例：

使用存储函数来查询指定员工的年薪

create or replace function 
	empincome(eno in emp.empno%type)
return 
	number
is
	psal emp.sal%type;
	pcomm emp.comm%type;
begin
	select t.sql, t.comm into psal, pcomm from emp t where t.empno = eno;
	return 
		psal * 12 + nvl(pcomm, 0);
end;

-- 调用
declare 
	yearSal number;
begin
	yearSal := empincome(7369);
	dbms_output.put_line(yearSal);
end;

使用存储过程来替换上面的例子

create or replace procedure 
	empincomep(eno in emp.empno%type, income out number)
is
	psal emp.sal%type;
	pcomm emp.comm%type;
begin
	select t.sql, t.comm into psal, pcomm from emp t where t.empno = eno;
	income := psal * 12 + nvl(pcomm, 0);
end empincomep;

-- 调用
declare 
	income number;
begin
	empincomp(7369, income);
	dbms_output.put_line(income);
end;

六、触发器

数据库触发器是一个与表相关联的、存储的 PL/SQL 程序。

每当一个特定的数据操作语句（insert、update、delete）在制定的表上发出时，Oracle自动地执行触发器中定义的语句序列。

1. 触发器用处

数据确认
实施复杂的安全性检查
做审计，跟踪表上所做的数据操作等
数据的备份和同步

2. 触发器的类型

语句级触发器

在指定的操作语言操作之前或之后执行一次，不管这条语句影响了多少行。
行级触发器

触发语句作用的每一条记录都会被触发。在行级触发器中使用 old 和 new 伪记录变量，识别值的状态。

3. 触发器的使用

创建语法：

CREATE OR REPLACE TRIGGER
	触发器名称
	{BEFORE|AFTER}	
	{DELETE|INSERT|UPDATE[OF 列名]}
	ON 表名
BEGIN
	PLSQL 程序块
END 触发器名称;

**范例1：**插入员工后打印一句话“一个新员工插入成功”

create or replace triggert estTrigger
	after insert on person
declare
	-- local variables here
begin 
	dbms_output.put_line('插入一条员工信息');
end testTrigger;

**范例2：**不能在休息时间插入员工

create or replace trigger validInsertPerson
	before insert on person

declare
	weekend varchar2(10);
begin 
	select to_char(sysdate, 'day') into weekend from dual;
	if weekend in ('星期一') then
		raise_application_error(-20001, '不能在非法时间插入员工');
	end if;
end validInsertPerson;

在触发器中触发语句与伪记录变量的值

触发语句	:old	:new
insert	所有字段都是空（null）	将要插入的数据
update	更新以前该行的值	更新后的值
delete	删除以前该行的值	所有字段都是kong（null）

**范例3：**判断员工涨工资之后的工资的值一定要大于涨工资之前的工资

create or replace trigger addSal4p
	before update of sal on myemp
	for each row
begin 
	if :old.sal >= :new.sal then
		raise_application_error(-20002, '涨前的工资不能大于涨后的工资');
	end if;
end addSal4p;

-- 修改工资
update myemp t set t.sal = t.sal - 1;

如果员工涨工资之后的工资小于了涨工资之前的工资
在这里插入图片描述

七、Java 程序调用存储过程

1. java 连接 oracle 的的 jar

ojdbc14.jar：

ojdbc6.jar：

maven

pom.xml

<dependencies>
        <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>
            <scope>test</scope>
        </dependency>
    </dependencies>

2. 数据库连接参数

jdbc.oracle.driver=oracle.jdbc.OracleDriver
jdbc.oracle.url=jdbc:oracle:thin:@127.0.0.1:1521:orcl
jdbc.oracle.username=scott
jdbc.oracle.password=tiger

连接测试代码：

@Test
    public void javaCallOracle() throws Exception {
        //加载数据库驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //得到Connection连接
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.88.6:1521:orcl", "itheima", "itheima");
        //得到预编译的Statement对象
        PreparedStatement pstm = connection.prepareStatement("select * from emp where empno = ?");
        //给参数赋值
        pstm.setObject(1, 7788);
        //执行数据库查询操作
        ResultSet rs = pstm.executeQuery();
        //输出结果
        while(rs.next()){
            System.out.println(rs.getString("ename"));
        }
        //释放资源
        rs.close();
        pstm.close();
        connection.close();
    }

3. 实现存储过程的调用

3.1 定义存储过程

create or replace procedure proc_countyearsal(eno in emp.empno%type, esal out number)
AS
begin 
	select sal * 12 + nvl(comm, 0) into esal from emp where empno = eno;
end;

-- 掉用
declare
	esal number;
begin 
	proc_countyearsal(7839, esal);
	dbms_output.put_line(esal);
end;

3.2 存储过程调用

/**
 * java调用存储过程
 * {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}   调用存储函数使用
 *  {call <procedure-name>[(<arg1>,<arg2>, ...)]}   调用存储过程使用
 * @throws Exception
 */
@Test
public void javaCallProcedure() throws Exception {
    //加载数据库驱动
    Class.forName("oracle.jdbc.driver.OracleDriver");
    //得到Connection连接
    Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.88.6:1521:orcl", "itheima", "itheima");
    //得到预编译的Statement对象
    CallableStatement pstm = connection.prepareCall("{call p_yearsal(?, ?)}");
    //给参数赋值
    pstm.setObject(1, 7788);
    pstm.registerOutParameter(2, OracleTypes.NUMBER);
    //执行数据库查询操作
    pstm.execute();
    //输出结果[第二个参数]
    System.out.println(pstm.getObject(2));
    //释放资源
    pstm.close();
    connection.close();
}

4. 存储函数调用

4.1 定义存储函数

4.2 存储函数调用

/**
 * java调用存储函数
 * {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}   调用存储函数使用
 * {call <procedure-name>[(<arg1>,<arg2>, ...)]}   调用存储过程使用
 * @throws Exception
 */
@Test
public void javaCallFunction() throws Exception {
	//加载数据库驱动     				
	Class.forName("oracle.jdbc.driver.OracleDriver");
    //得到Connection连接
    Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.88.6:1521:orcl", "itheima", "itheima");
    //得到预编译的Statement对象
    CallableStatement pstm = connection.prepareCall("{?= call f_yearsal(?)}");
    //给参数赋值
    pstm.setObject(2, 7788);
    pstm.registerOutParameter(1, OracleTypes.NUMBER);
    //执行数据库查询操作
    pstm.execute();
    //输出结果[第一个参数]
    System.out.println(pstm.getObject(1));
    //释放资源
    pstm.close();
    connection.close();
}

4. 存储函数调用

4.1 定义存储函数

4.2 存储函数调用

/**
 * java调用存储函数
 * {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}   调用存储函数使用
 * {call <procedure-name>[(<arg1>,<arg2>, ...)]}   调用存储过程使用
 * @throws Exception
 */
@Test
public void javaCallFunction() throws Exception {
	//加载数据库驱动     				
	Class.forName("oracle.jdbc.driver.OracleDriver");
    //得到Connection连接
    Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.88.6:1521:orcl", "itheima", "itheima");
    //得到预编译的Statement对象
    CallableStatement pstm = connection.prepareCall("{?= call f_yearsal(?)}");
    //给参数赋值
    pstm.setObject(2, 7788);
    pstm.registerOutParameter(1, OracleTypes.NUMBER);
    //执行数据库查询操作
    pstm.execute();
    //输出结果[第一个参数]
    System.out.println(pstm.getObject(1));
    //释放资源
    pstm.close();
    connection.close();
}

theONLY24

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle_2

OracleOracle_2Oracle一、视图1. 创建视图2. 创建或覆盖已存在的视图3. 创建或覆盖已存在的视图并设置只读二、索引1. 为甚么索引会加快查询速度2. 创建索引2.1 单列索引2.2 复合索引3. 索引的使用原则三、pl/sql 基本语法1. pl/sql 程序语法2. 常量和变量定义3. if分支4. lOOP循环语句5. 游标 Cursor5.1 语法：5.2 使用步骤...
复制链接

扫一扫