本文深入探讨Oracle数据库技术，包括rownum行号的使用，rowid行地址的理解，临时表的创建及其特性，行转列的wm_concat函数，以及分页查询。此外，还详细讲解了视图的概念和优点，序列的创建与应用，索引的创建、使用场景及优缺点，同义词的作用和创建，以及PL/SQL中的流程控制、光标、异常处理、存储过程和函数等核心概念。
3.1 rownum行号
|
SQL> select rownum,e.* from emp e; |
- rownum永远按照默认的顺序生成
|
SQL> select rownum,e.ename,e.sal from emp e order by sal desc; |
- rownum只能使用 < <=; 不能使用> >=
3.2 rowid行地址
|
SQL> select rowid,empno,ename,sal from emp;
SQL> select * from emp where rowid='AAAMfPAAEAAAAAgAAA'; |
3.3 临时表
1. create global temporary table *****
2. 自动创建: order by
特点：当事务或者会话结束的时候，表中的数据自动删除
|
SQL> create global temporary table tmp_table1 (tid number,tname varchar2(20)) on commit delete rows; |
|
SQL> insert into tmp_table1 values (1,'Tom'); |
|
SQL> select * from tmp_table1; |
|
SQL> commit;【commit后，数据会消失】 |
|
SQL> desc tmp_table1; |
3.4行转列
- wm_concat(varchar2) 组函数
|
SQL> select deptno,wm_concat(ename) namelist from emp group by deptno; |
|
|
第一题：【oracle的分页】
- 找到员工表中工资最高的前三名
|
select e.ename,e.sal from (select * from emp order by sal desc) e where rownum <=3 |
select e.ename,e.sal from (select * from emp order by sal desc) e where rownum >=3 and rownum <=6
select * from (select rownum r,e.* from (select * from emp order by sal) e where rownum <=6) where r>3 |
第二题：
- 找到员工表中薪水大于本部门平均薪水的员工
- 求出部门的平均工资
- 判断员工是否工资大于本部门的平均工资
|
第一种写法 select e.empno,e.ename,e.sal,d.avgsal from emp e, (select deptno,avg(sal) avgsal from emp group by deptno) d where e.deptno=d.deptno and e.sal > d.avgsal |
|
|
|
第二种写法：【相关子查询：将主查询中的值 作为参数传递给子查询】 select e.empno,e.ename,e.sal,(select avg(sal) from emp where deptno = e.deptno) avgsal from emp e where e.sal > (select avg(sal) from emp where deptno = e.deptno) |
|
第三种写法： select * from emp e1 where sal > (select avg(sal) from emp e2 where e2.deptno=e1.deptno group by deptno); |
第三题：
- 计算每年入职的员工数
|
select count(*) Total, sum(decode(to_char(hiredate,'yyyy'),'1980',1,0)) "1980", sum(decode(to_char(hiredate,'yyyy'),'1981',1,0)) "1981", sum(decode(to_char(hiredate,'yyyy'),'1982',1,0)) "1982", sum(decode(to_char(hiredate,'yyyy'),'1987',1,0)) "1987" from emp |
|
|
4数据处理
4.1SQL 的类型
- 1. DML（data manipulation Language 数据操作语言）: insert update delete select
- 2. DDL(Data Definition Language 数据定义语言):
create table,alter table,drop table,truncate table
create/drop view,sequence(序列),index,synonym(同义词)
- 3. DCL(Data Control Language 数据控制语言): grant(授权) revoke（撤销权限）
4.2 例子
|
PreparedStatement 预处理 |
insert into emp (empno,ename,sal,deptno) values (?,?,?,?); |
|
地址符 & |
SQL>insert into emp (empno,ename,sal,deptno) values (&empno,&ename,&sal,&deptno)
SQL> select empno,ename,sal,&t from emp;
SQL> select * from &t;
|
|
批处理 |
复制表结构 SQL> create table emp10 as select * from emp where 1=2; 一次性将emp中，所有10号部门的员工插入到emp10中 SQL> insert into emp10 select * from emp where deptno=10; |
|
delete和truncate的区别: |
1. delete逐条删除；truncate先摧毁表，再重建 2.(根本)delete是DML（可以回滚），truncate是DDL（不可以回滚） 3. delete不会释放空间 truncate会 4. delete可以闪回 (flashback), truncate不可以 5. delete会产生碎片；truncate不会 |
|
SQL> set feedback off SQL> @C:\课程\JavaEE\oracle【3天】\资料\testdelete.sql SQL> select count(*) from testdelete; SQL> set timing on SQL> delete from testdelete; SQL> drop table testdelete purge; SQL> @C:\课程\JavaEE\oracle【3天】\资料\testdelete.sql SQL> truncate table testdelete; SQL> set feedback on SQL> set timing off
| |
|
事务的标致 |
1. 起始标志：事务中第一条DML语句 2. 结束标志：提交： 显式 commit 隐式 正常退出（exit），DDL，DCL 回滚: 显式 rollback 隐式 非正常退出，掉电，宕机 |
|
保存点 |
SQL> create table testsavepoint (tid number, tname varchar2(20)); SQL> insert into testsavepoint values(1,'Tom'); SQL> insert into testsavepoint values(2,'Mary'); SQL> savepoint a; SQL> insert into testsavepoint values(3,'Maake'); SQL> select * from testsavepoint; SQL> rollback to savepoint a; SQL> select * from testsavepoint; SQL> commit; SQL> set transaction read only; SQL> select * from testsavepoint; SQL>insert into testsavepoint values(3,'Maake');【不能在 READ ONLY 事务处理中执行插入/删除/更新操作 SQL> rollback;
|
5创建和管理表
|
表创建 |
SQL> create table test3(tid number,tname varchar2(20),hiredate date default sysdate); SQL> insert into test3(tid,tname) values(1,'Tom'); SQL> select * from test3; |
|
|
创建表，保存20号部门的员工 SQL> create table emp20 as select * from emp where deptno=20; |
|
|
创建表，员工号 姓名 月薪 年薪 部门名称 SQL> create table empinfo as select e.empno,e.ename,e.sal,e.sal*12 annsal,d.dname from emp e,dept d where e.deptno=d.deptno |
|
|
添加表字段 SQL> alter table test3 add photo blob; 修改表字段 SQL> alter table test3 modify tname varchar2(40); 删除表字段 SQL> alter table test3 drop column photo; 重命名表字段 SQL> alter table test3 rename column tname to username; 改表名 SQL> rename test3 to test5; 删除表 SQL> drop table test5
显示回收站 SQL> show recyclebin
清空回收站 SQL> purge recyclebin; 删除的表，可以从回收站查询 SQL> drop table TESTSAVEPOINT; SQL> select * from "BIN$ULtYrrqvTlqfr+qeSXIHNA==$0"; 闪回删除 SQL> flashback table TESTSAVEPOINT to before drop; 注意：管理员没有回收站sys |
|
|
创建约束表字段 SQL>create table test5 (tid number, tname varchar2(20), gender varchar2(2) check (gender in ('男','女')), sal number check(sal > 0) ) SQL> insert into test5 values(1,'Jone','男',1000); SQL> insert into test5 values(1,'Jone','A',1000);【违反检查约束条件 (SCOTT.SYS_C005420)】 SQL> insert into test5 values(1,'Jone','男',-10); |
|
|
外键关联 SQL>create table student (sid number constraint student_pk primary key, sname varchar2(20) not null, deptno number constraint student_fk references dept(deptno)) SQL> insert into student values (1,'Jone',10);
|
6视图
6.1常见的数据库对象
|
对象 |
描述 |
|
表 |
基本的数据存储集合，由行和列组成 |
|
视图 |
从表中抽出的逻辑上的相关的数据集合 |
|
序列 |
提供有规律的数值 |
|
索引 |
提搞查询的效率 |
|
同义词 |
给对象起别名 |
6.2视图
- 视图是一张虚表
- 视图建立在已有表的基础，视图赖以建立的这些表称为基表
- 向视图提供数据的内容的语句为select语句，可以将视图理解为存储起来的select语句
- 视图是向用户提供基表数据的另一种形式
6.3视图的优点
- 限制数据访问
- 简化复杂查询
- 提供数据的相互独立
- 同样的数据可以有不同的显示方式
- 但视图不能提高性能
ora-01034 oracle not acailable
|
添加权限 |
在虚拟机里以管理员登录，并授权scott的视图权限 SQL> sqlplus sys/manager as sysdba SQL> grant create view to scott |
|
创建视图 |
SQL>create view empinfoview as select e.empno,e.ename,e.sal,e.sal*12 annsal,d.dname from emp e,dept d where e.deptno=d.deptno |
|
查看视图结构 |
SQL> desc empinfoview; |
|
查询视图 |
SQL> select * from empinfoview; |
|
只读视图 |
SQL>create or replace view empinfoview as select e.empno,e.ename,e.sal,e.sal*12 annsal,d.dname from emp e,dept d where e.deptno=d.deptno with read only |
|
|
|
7 序列
7.1 序列
- 可供多个用户用来产生唯一数值的数据库对象
- 自动提供惟一的数值
- 共享对象
- 主要用于提供主键
- 将序列值装入内存可以提高访问效率
7.2创建序列
|
|
SQL> create sequence myseq; |
|
|
SQL> create table testseq(tid number,tname varchar2(20)); |
|
|
SQL> select myseq.nextval from dual; |
|
|
SQL> select myseq.currval from dual; |
|
|
SQL> insert into testseq values(myseq.nextval,'aa'); SQL> insert into testseq values(myseq.nextval,'aa'); SQL> insert into testseq values(myseq.nextval,'aa'); SQL> slect * from testseq; SQL> commit; SQL> insert into testseq values(myseq.nextval,'aa'); SQL>rollback |
8 索引
8.1 索引
- 一种独立于表的模式对象，可以存储在与表不同的磁盘或者表空间
- 索引被删除或者损坏，不会对表产生影响，其影响只是查表的速度
- 索引一旦被建立，oracle管理系统会对其进行自动维护，而且由oracle管理系统决定何时使用索引，用户不用在查询查询语句中指定使用哪个索引
- 在删除一个表时，所有基于该表的索引会自动被删除
- 通过指针加速oracle服务器的查询速度
- 通过快速定位的方法，减少磁盘的I/O
索引可以提高查询速度，我们只需要创建索引，至于怎么使用索引，是oracle系统内部的事情
8.2 什么情况下创建索引
- 列中数据值分布很广
- 列经常在where语句或连接条件中出现
- 表经常被访问，而且量很大，访问的数据大概占总量的2%~4%
8.3 什么情况下不要创建索引
- 表很小
- 经常更新
- 列不经常在where语句或连接条件中出现
8.4 创建索引
|
SQL> create index myindex on emp(); |
9 同义词
9.1 同义词 取别名
- 使用同义词访问其它对象
- 方便访问其它用户的对象
- 缩短对象名字的长度
- 创建同义词需要授权grant create synonym to scott
9.2 使用
|
|
SQL> create synonym myemp for emp; SQL> select * from myemp; |
|
|
SQL> create synonym hremp for hr.employees; SQL> grant select on hr.employees to scott【需要授权才能访问其它用户的表】 SQL> select count(*) from hr.employees; SQL> select count(*) from hremp; |
|
|
|
使用oracle的图形化工具
1 PL/SQL
1.1什么是PL/SQL?
- PL:Process Language
- PL/SQL是oracle对sql语言的过程化扩展
- 在SQL语言中添加了过程处理语句（如分支，循环），使SQL语言具有过程处理能力
1.2 PL/SQL程序结构
|
Declare 说明部分 （变量、光标、例外声明） Begin 语句序列(DML)... Exception 例外处理语句 End; |
1.3变量声明
- 可以声明的变量类型 char,varchar2,boolean,date,number,long
|
--查找7839的员工信息 --打开输出 set serveroutput on declare pename emp.ename%type;--名字变量 psal emp.sal%type;--薪水变量 begin select ename,sal into pename,psal from emp where empno=7839; --输出 DBMS_OUTPUT.PUT_LINE(pename||'的薪水是'||psal); end; |
1.4 if语句
|
--判断用户从键盘输入的数字 --打开输出 set serveroutput on --num是地址值，用来保存输入的值 accept num prompt '请输入一个数字' declare pnum number :=# begin if pnum = 0 then dbms_output.put_line('你输入的是0'); elsif pnum = 1 then dbms_output.put_line('你输入的是1'); elsif pnum = 2 then dbms_output.put_line('你输入的是2'); else dbms_output.put_line('其它数字'); end if; end; |
|
|
|
|
1.5循环
|
--打开输出 set serveroutput on declare pnum number :=1; begin loop exit when pnum > 10; dbms_output.put_line(pnum); pnum :=pnum + 1; end loop; end; |
1.6光标
- 光标（cursor）也被称为游标。Oracle 使用两种光标：显示光标和隐式光标。
- 不管语句返回多少条纪录， PL/SQL 为使用的每一条 UPDATE 、 DELETE 和 INSERT 等 SQL 命令隐式的声明一个光标。
- 光标的作用
- 当 PL/SQL 光标查询返回多行数据时，这些记录组被称为活动集。 Oracle 将这种活动集存储在您创建的显示定义的已命名的光标中。Oracle 光标是一种用于轻松的处理多行数据的机制，没有光标， Oracle 开发人员必须单独地、显示地取回并管理光标查询选择的每一条记录。
- 光标的另一项功能事，它包含一个跟踪当前访问的记录的指针，这使您的程序能够一次处理多条记录。
- 练习
|
遍历员工薪水 |
|
set serveroutput on
declare cursor cemp is select ename,sal from emp; pename emp.ename%type; psal emp.sal%type; begin --打开光标 open cemp; loop fetch cemp into pename,psal; exit when cemp%notfound; dbms_output.put_line(pename||'的薪水是'||psal); end loop; --关闭光标 close cemp; end; |
|
涨工资，总裁1000 经理800 其他400 |
|
set serveroutput on
declare --job在plsql中是一个关键字，重命名一下 --alter table "SCOTT"."EMP" rename column "JOB" to empjob cursor cemp is select empno,empjob from emp; pempno emp.empno%type; pjob emp.empjob%type; begin --rollback; --打开光标 open cemp; loop fetch cemp into pempno,pjob; exit when cemp%notfound; if pjob='PRESIDENT' then update emp set sal = sal + 1000 where empno=pempno; elsif pjob='MANAGER' then update emp set sal = sal + 800 where empno=pempno; else update emp set sal = sal + 400 where empno=pempno; end if; end loop; --关闭光标 close cemp; --需要提交 commit; end; |
|
带参数的光标 |
|
set serveroutput on
declare cursor cemp(dNo number) is select ename,sal from emp where deptno = dNo; pename emp.ename%type; psal emp.sal%type; begin --打开光标 open cemp(20); loop fetch cemp into pename,psal; exit when cemp%notfound; dbms_output.put_line(pename||'的薪水是'||psal); end loop; --关闭光标 close cemp; end;
|
- 默认，一个会话中只能打开300个光标
SQL> show parameter cursor【只能系统管理员可查看】
SQL>alter system set open_cursors=400; 【修改】
1.7例外(异常)
- 例外是程序设计语言提供的一种功能，用来增强程序的键壮性和容错性
- oracle的异常处理
- 系统定义的例外【异常】
- No_data_found【没有找到数据】
- Too_many_row【select ...into语句匹配多个行】
- Zero_Divide【被零除】
- Value_error 【算术或转换错误】
- Timeout_on_resource【在等待资源时发生超时】
|
系统异常 |
|
set serveroutput on
declare pnum number; begin pnum:=1/0; --pnum:='b'; exception when zero_divide then DBMS_OUTPUT.PUT_LINE('0不能做分母'); DBMS_OUTPUT.PUT_LINE('0不能做分母');--可写多行 when value_error then DBMS_OUTPUT.PUT_LINE('算术转换异常'); when others then DBMS_OUTPUT.PUT_LINE('其它导常'); end;
|
|
自定义异常 |
|
--查询50号部门的员工姓名 set serveroutput on
declare cursor cemp is select ename from emp where deptno=50; pename emp.ename%type;
--自定义异常 no_emp_found exception; begin open cemp;
--取第一条记录 fetch cemp into pename;
if cemp%notfound then --抛出异常 raise no_emp_found; end if;
close cemp;
exception when no_emp_found then dbms_output.put_line('没有找到员工'); when others then dbms_output.put_line('其他例外'); end; |
1.8案例
- 统计每年入职的员工数
|
set serveroutput on declare cursor cemp is select to_char(hiredate,'yyyy') from emp; phiredate varchar2(4); --声明变量 count80 number :=0; count81 number :=0; count82 number :=0; count87 number :=0; begin open cemp; loop fetch cemp into phiredate; exit when cemp%notfound;
if phiredate = '1980' then count80:=count80+1; elsif phiredate = '1981' then count81:=count81+1; elsif phiredate = '1982' then count82:=count82+1; else count87:=count87+1; end if; end loop; close cemp;
DBMS_OUTPUT.PUT_LINE('Total'||(count80 + count81 + count82 + count87)); DBMS_OUTPUT.PUT_LINE('1980'||count80); DBMS_OUTPUT.PUT_LINE('1981'||count81); DBMS_OUTPUT.PUT_LINE('1982'||count82); DBMS_OUTPUT.PUT_LINE('1987'||count87); end; |
1.9案例
- 为员工涨工资。从最低工资起，每人涨10%，但总额不能超过5W，请计算涨工资的人数和长工资后的工资总额
|
/** 1.为员工涨工资。从最低工资起，每人涨10%， 2.但总额不能超过5W，请计算涨工资的人数和长工资后的工资总额
1.声明变量 涨工资的人数 countEmp number:0; 涨工资的总额 salTotal number; [select sum(sal) into salTotal from emp]; 2.得到初始的工资总和 3.光标遍历 4.如果总额大于5000就不加工资 */
set serveroutput on declare cursor cemp is select empno,sal from emp order by sal; pempno emp.empno%type; psal emp.sal%type;
countEmp number:=0;--涨工资的人数 salTotal number;--工资的总额 begin --获取当前总工资 select sum(sal) into salTotal from emp; open cemp; loop --大于5W不在加工资 exit when salTotal > 50000; fetch cemp into pempno,psal; --没有光标，无下条记录 exit when cemp%notfound; --涨工资 update emp set sal = sal*1.1 where empno=pempno;
--涨工资人数 countEmp :=countEmp+1; --涨后金额 salTotal :=salTotal + psal*0.1; end loop; close cemp; commit;--提交事务 DBMS_OUTPUT.PUT_LINE('涨工资人数'||countEmp); DBMS_OUTPUT.PUT_LINE('涨后工资总额'||salTotal); end; |
2.0案例
- 实现按部门分段(6000以上),(6000,3000),3000元以下统计各工资段的职工人数，以及各部门的工资总和
|
|
|
/* 1.部门 遍历打印部门编号 2.部门员工遍历,薪水 3.声明三个区间的变量 4.声明部门总工资变量 */
set serveroutput on declare --部门光标 cursor cdept is select deptno from dept; --部门ID变量 pdeptno dept.deptno%type;
--员工光标 cursor cemp(dNo number) is select sal from emp where deptno=dNo; psal emp.sal%type;
--每个区间的员工个数 count1 number;-->6000 count2 number;--<3000 count3 number;--3000~600
totalSal number;--部门上总工资 begin --打开光标 open cdept; --1.遍历部门 loop count1:=0;count2:=0;count3:=0;
fetch cdept into pdeptno; exit when cdept%notfound;
select sum(sal) into totalSal from emp where deptno = pdeptno; --打印部门编号 DBMS_OUTPUT.PUT_LINE ('部门编号'||pdeptno ||'总工资'||nvl(totalSal,0));
--2.根据部门编号遍历员工 open cemp(pdeptno); loop fetch cemp into psal; exit when cemp%notfound; --打印工资 --DBMS_OUTPUT.PUT_LINE ('--'||psal); --3.遍历员工工资区间 if psal>6000 then count1 :=count1+1; elsif psal<3000 then count2 :=count2+1; else count3 :=count3+1; end if; end loop; close cemp;
--打印工资区间人数 DBMS_OUTPUT.PUT_LINE ('工资>6000:'||count1||' 工资<3000:'||count2||' 工资3000~6000:'||count3); DBMS_OUTPUT.PUT_LINE ('========================================='); --结束循环 end loop; --关闭光标 close cdept; end; |
2 存储过程，函数
2.1存储过程和存储函数
- 指存储在数据库中供所有用户程序调用的子程序叫存储过程和存储函数
2.2创建存储过程语法
|
Create [or replace] procedure 过程名（参数） as PLSQL子程序 |
|
|
--创建一个存储过程 create or replace procedure sayHelloWord as --说明部份 begin DBMS_OUTPUT.PUT_LINE('Hello Word'); end;
--调用存储过程 第一方式 EXECUTE sayHelloWord();
--调用存储过程 第二方式 begin sayHelloWord(); sayHelloWord(); end; |
|
|
--给指定的员工涨工资100，并且涨前涨后的薪水 create or replace procedure raisesalary(eno in number) as psal emp.sal%type; begin --得到当前薪水 select sal into psal from emp where empno = eno;
--涨100 update emp set sal=sal+100 where empno=eno; --打印 DBMS_OUTPUT.put_line('涨前:'||psal||' 涨后:'||(psal + 100));
end;
--调用存储过程 set serveroutput on begin raisesalary(7369); raisesalary(7369); --需要在这里提交事务，不要在内部提交 commit; end; |
|
|
2.3存储过程的调试
- 如图，存储过程可调试，但在调试前需要给于权限
- 然后加断点调试，先选择调用，用法跟java的调试差不多
|
|
|
授权 |
|
|
2.4存储函数
- 函数(Function)为一命名的存储函数，可以带参数，并返回一计算值。函数和过程的结构相似，但必需要有一个return语句，用于返回函数值。函数说明要指定函数名、结果值的类型，以及参数类型。
2.5创建存储函数语法
|
Create [or replace] function 函数名（参数）return 返回值类型 as PLSQL子程序 |
|
函数声明 【计算年薪】 |
|
create or replace function queryincome(eno in number) return number as psal emp.sal%type;--月薪 pcomm emp.comm%type;--奖金 begin --得到当前月薪和奖金 select sal,comm into psal,pcomm from emp where empno = eno;
--返回年收入 return psal*12 + nvl(pcomm,0);
end; |
|
函数调用 |
|
set serveroutput on --函数的调用 declare y_sal number; begin y_sal:=queryincome(7369); DBMS_OUTPUT.put_line('年薪:'||y_sal); end; |
2.6 什么时候用存储函数和存储过程
- 如果有一个返回值就用存储函数，否则就用存储过程
3 java中调用存储过程和函数
|
创建一个存储过程【根据员工编号返回员工名字和工资】 |
|
create or replace procedure queryempinfo(eno in number,empname out varchar,empsal out number) as psal emp.sal%type; begin select ename,sal into empname,empsal from emp where empno = eno; end; |
|
创建一个java项目，导入ojdbc和junit包 |
|
写一个JDBC工具包 |
|
package demo;
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement;
public class JDBCUtils {
private static String driver = "oracle.jdbc.OracleDriver"; private static String url = "jdbc:oracle:thin:@192.168.56.102:1521:orcl"; private static String user = "scott"; private static String password = "scott";
static{ try { Class.forName(driver); } catch (ClassNotFoundException e) { // TODO: handle exception e.printStackTrace(); } }
/** * 返回一个数据连接 * */ public static Connection getConnection(){ try { return DriverManager.getConnection(url, user, password); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }
return null; }
public static void rlease(Connection conn,Statement st,ResultSet rs){
if(rs!=null){ try { rs.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ rs = null;//释放 } }
if(st != null){ try { st.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ st = null;//释放 } }
if(conn != null){ try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ conn = null;//释放 } } } }
|
|
存储过程的调用 |
|
@Test public void testProcedure(){ //存储过程的java写法{call <procedure-name>[(<arg1>,<arg2>, ...)]} String p = "{call queryempinfo(?,?,?)}";
Connection con =null; CallableStatement call = null;
try { con = JDBCUtils.getConnection(); call = con.prepareCall(p); //对于in参数，赋值 call.setInt(1, 7369);
//设置out参数类型 call.registerOutParameter(2, OracleTypes.VARCHAR); call.registerOutParameter(3, OracleTypes.NUMBER);
//执行 call.execute(); //取结果 String ename = call.getString(2); double sal = call.getDouble(3); System.out.println(ename); System.out.println(sal);
} catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { JDBCUtils.rlease(con, call, null); } } |
|
存储函数的调用{?=call <funtion-name>[(<arg1>,<arg2>, ...)]} |
|
@Test public void testFunction(){ //存储方法的java写法{?=call <funtion-name>[(<arg1>,<arg2>, ...)]} String p = "{?=call queryincome(?)}";
Connection con =null; CallableStatement call = null;
try { con = JDBCUtils.getConnection(); call = con.prepareCall(p); //对于in参数，赋值 call.setInt(2, 7369);
//设置out参数类型 call.registerOutParameter(1, OracleTypes.NUMBER);
//执行 call.execute(); //取结果 double y_sal = call.getDouble(1); System.out.println(y_sal);
} catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { JDBCUtils.rlease(con, call, null); } } |
4 触发器
4.1触发器定义
- 数据库触发器是一个与表相关联、存储的PL/SQL程序。每当一个特定的数据操作语句(Insert,Update,delete)在指定的表上发出时，Oracle自动地执行触发器中定义的语句序列。
- 触发器的类型
- 语句级触发器
- 在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行。
- 语句级触发器
- 行级触发器(FOR EACH ROW)
- 触发语句作用的每一条记录都被触发。在行级触发器使用:old和:new 伪记录变量，识别值的状态
4.2触发器语法格式
|
CREATE [or REPLACE] TRIGGER 触发器名 {BEFORE | ALTER} {DELETE | INSERT | UPDATE [OF 列名]} ON 表名 [FOR EACH ROW [WHEN(条件)]] PLSQL 块 |
4.3案例
|
|
create trigger abcd after insert on emp declare
begin DBMS_OUTPUT.PUT_LINE('成功插入新员工'); end;
set serveroutput on insert into emp (empno,ename,sal,deptno) values(1122,'zhangsan',1920,10); |
|
|
create trigger securityemp before insert on emp begin if to_char(sysdate,'day') in ('星期六','星期日') or to_number(to_char(sysdate,'hh24')) not between 9 and 17 then RAISE_APPLICATION_ERROR(-20001, '禁止非工作时间插入新员工'); end if; end;
set serveroutput on insert into emp (empno,ename,sal,deptno) values(1432,'zhangsan3',1920,10); commit; |
|
3涨后的工资不能少于涨前的工资 |
|
create or replace trigger checksalary before update on emp for each row begin if :new.sal < :old.sal then raise_application_error(-20002,'涨后的工资不能少于涨前的工资new:'||:new.sal||' old:'||:old.sal); end if; end;
set serveroutput on; update emp set sal = sal -10 where empno=1122; |
扫一扫
842
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
