Oracle基本操作+Java连接Oracle

最新推荐文章于 2023-08-10 18:19:48 发布

Eternal_Summer-

最新推荐文章于 2023-08-10 18:19:48 发布

阅读量2.1k

点赞数

文章标签： java oracle

本文链接：https://blog.csdn.net/qq_42278320/article/details/112958037

版权

Oracle

注：本文中使用Oracle10g

一. 创建表空间

create tablespace itoracle
datafile 'C:\Users\24016\Desktop\store\oracle\itoracle.dbf'  
size 10m --设定存储空间的大小
autoextend on --如果存储空间不够，开启自动扩展存储空间
next 10m; --每次扩展10m

二. 删除表空间

drop tablespace itoracle;

三. 创建用户

create user itoracle
identified by itoracle
default tablespace itoracle;--此时用户并不能登录，因为没有授权

给用户授权

给itoracle角色授予dba角色
grant dba to itoracle;
-----------------------------
Oracle中常用角色
connect --连接角色，基本角色
resource --开发者角色
dba --超级管理员角色

四. 切换到itoracle角色登录

session-log off-all--退出当前登录
session-log on --以itOracle角色登录

五. Oracle数据类型

1.Varchar, Varchar2 --表示一个字符串，其中最常用的是Varchar2,Varchar2会自动截掉多余的字符。
                    --比如定义一个Varchar2(6),如果只存进去一个字符，那么取出来的数据长度就是1，而不是6
2.NUMBER  --NUMBER(n)表示一个整数，长度是n，
          --NUMBEr(m,n)表示一个小数，整数是m-n，
3.DATA --表示日期类型
4.CLOB --大对象，存的是文本数据类型，可存4G
5.BLOB --大对象，存的是二进制数据，可存4G，如视频文件

六. 创建一张person表

create table person(
       pid number(20),
       pname varchar2(10)
);

添加一列

alter table person add gender number(1);
如果是添加多列，则以，隔开

修改列

alter table person modify gender char(1);

修改列名称【注意】

alter table person rename column gender to sex;

删除一列

alter table person drop column sex;

添加一条记录(注意：Oracle中的增删改操作有事务，必须手动提交，或回滚)

insert into person (pid,pname) values (1,'晓丽');
commit;

查询记录

select * from person;

修改记录

update person set pname='志超' where pid =1;
commit;

三个删除

delete from person;--删除表中全部记录，但是表结构还在
drop table person;--删除表结构
truncate table person;--先删除表，再创建表，效果等同于删除表中全部记录
--尤其是在数据量大的情况下，尤其是在带有索引的情况下，该操作效率高
--索引可以提高查询效率，但是会影响增删改的效率
truncate table person;

七. 序列

序列并不真的属于任何一张表，但是可以和表逻辑绑定
默认从1开始，依次递增，主要用来给主键赋值使用 s_person(命名规范)

创建序列
create sequence s_person;
查看序列
select s_person.nextval from dual; 
刚创建好序列之后，要想查看当前序列值，必须要先执行一下s_person.nextval，才能查看当前序列值

查看当前序列值
select s_person.currval from dual;
使用序列添加记录
insert into person (pid,pname) values(s_person.nextval,'晓丽');
commit;

八. 虚表/哑表：dual:虚表，无任何实际意义，只是为了补全语法

九. scott用户的使用（初学者使用的用户）

解锁Scott用户

alter user scott account unlock;

解锁scott用户的密码(默认密码为tiger)

alter user scott identified by tiger;

在OracleXE10g下，默认没有scott用户，需要手动创建。【注意】

创建步骤：
1.     登录数据库：sqlplus / as sysdba   账户：system 密码：password --本地的口令和密码
2.     创建scott用户：create user scott identified by tiger;
3.     为Scott用户分配权限：grant connect,resource to scott;
4.     使用Scott用户登录：conn scott/tiger
5.     执行scott.sql脚本：@?\scott.sql --其中@?\代表的是路径D:\Oracle\app\oracle\product\10.2.0\server 
--将scott.sql文件放到本地oracle安装目录下的server目录下，然后执行上句代码
6.     使用scott用户登录PL/SQL

scott.sql文件代码

Rem Copyright (c) 1990 by Oracle Corporation
Rem NAME
REM    UTLSAMPL.SQL
Rem  FUNCTION
Rem  NOTES
Rem  MODIFIED
Rem	gdudey	   06/28/95 -  Modified for desktop seed database
Rem	glumpkin   10/21/92 -  Renamed from SQLBLD.SQL
Rem	blinden   07/27/92 -  Added primary and foreign keys to EMP and DEPT
Rem	rlim	   04/29/91 -	      change char to varchar2
Rem	mmoore	   04/08/91 -	      use unlimited tablespace priv
Rem	pritto	   04/04/91 -	      change SYSDATE to 13-JUL-87
Rem   Mendels	 12/07/90 - bug 30123;add to_date calls so language independent
Rem
rem
rem $Header: utlsampl.sql 7020100.1 94/09/23 22:14:24 cli Generic<base> $ sqlbld.sql
rem
SET TERMOUT OFF
SET ECHO OFF
 
rem CONGDON    Invoked in RDBMS at build time.	 29-DEC-1988
rem OATES:     Created: 16-Feb-83
 
GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY TIGER;
ALTER USER SCOTT DEFAULT TABLESPACE USERS;
ALTER USER SCOTT TEMPORARY TABLESPACE TEMP;
CONNECT SCOTT/TIGER
DROP TABLE DEPT;
CREATE TABLE DEPT
       (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
	DNAME VARCHAR2(14) ,
	LOC VARCHAR2(13) ) ;
DROP TABLE EMP;
CREATE TABLE EMP
       (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
	ENAME VARCHAR2(10),
	JOB VARCHAR2(9),
	MGR NUMBER(4),
	HIREDATE DATE,
	SAL NUMBER(7,2),
	COMM NUMBER(7,2),
	DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
INSERT INTO DEPT VALUES
	(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
	(30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
	(40,'OPERATIONS','BOSTON');
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
DROP TABLE BONUS;
CREATE TABLE BONUS
	(
	ENAME VARCHAR2(10)	,
	JOB VARCHAR2(9)  ,
	SAL NUMBER,
	COMM NUMBER
	) ;
DROP TABLE SALGRADE;
CREATE TABLE SALGRADE
      ( GRADE NUMBER,
	LOSAL NUMBER,
	HISAL NUMBER );
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
COMMIT;
 
SET TERMOUT ON
SET ECHO ON

十. 函数

单行函数：作用在一行，返回一个值

字符函数

select upper('yes') from dual;
select lower('YES') from dual;

数值函数

select round(25.5) from dual;--四舍五入
select round(25.87,1) from dual;--四舍五入向后保留一位小数：25.9
select round(25.8,-1) from dual;--四舍五入向前保留一位：30

select trunc(56.16) from dual;--直接截取，小数点后保留0位，其余全部丢弃:56
select trunc(56.16,1) from dual;--保留小数点后1位，之后的直接丢弃：56.1
select trunc(56.16,-1) from dual;--保留小数点前1位，之后的 直接丢弃：50

日期函数

--查询出系统当前日期
select sysdate from dual;
--查询出emp表中所有员工入职距离现在多少天
select sysdate-e.hiredate from emp e;--oracle中日期以天为单位，直接计算出的结果就是多少天【重点】
--算出明天此刻时间（就是用系统时间+1--因为单位是天）
select sysdate+1 from dual;--:2021/1/20 18:58:55
--查询emp表中所有员工入职距离现在多少月
select months_between(sysdate,e.hiredate) from emp e;
--只有month月份有between函数，因为每个月天数不固定，所以需要进行中间值运算
--查询出emp表中所有员工距离现在几年
select months_between(sysdate,e.hiredate)/12 from emp e;
--查询出emp表中所有员工入职距离现在几周
select (sysdate-e.hiredate)/7 from emp e;

转换函数

--日期转字符串
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual; --：2021-01-19 07:48:59
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;--:2021-01-19 19:49:40
select to_char(sysdate,'fm yyyy-mm-dd hh24:mi:ss') from dual;--: 2021-1-19 19:50:22
--字符串改日期
select to_date('2021-01-19 07:48:59','yyyy-mm-dd hh24:mi:ss') from dual;--:2021/1/19 7:48:59

通用函数

--算出emp表中所有员工的年薪  
select e.sal*12+nvl(e.comm,0)from emp e;
--nvl(e.comm,0)：去除null值，如果e.comm是null，则使用0代替 
--oracle中所有与null进行运算的结果都是null

条件表达式

--给person表中pname起英文名
select p.pname, --千万注意，这里有个逗号，
       case p.pname
       when '志超' then 'Eternal' 
         when '晓丽' then 'Angle' --当不满足志超时，会自动判断是否是晓丽
           else 'none' --else选项可以不写
             end  --end必须要写
from person p;  --注意：这并不能改变原始数据，因为是select操作

--判断person表中员工id，如果是pid>3,显是girl，如果pid<3显是boy，如果=3，显是no
select p.pid,
       case 
           when p.pid>3 then 'girl'
             when p.pid <3 then 'boy'
               else 'no'
                 end
from person p;

Oracle专用条件表达式

--oracle中，除了起别名，都用单引号。起别名可以不用引号，或者双引号，包括中文
--给person表中pname起英文名
select p.pname,  --注意逗号
       decode(p.pname, --decode中紧跟着对哪个字段进行的操作,并且decode中除了最后一个不加逗号，其余都加
            '志超','Eternal', --一个对应一个，直到最后一个没有对应的，则最后一个就是else的，可以省略
            '晓丽','Angle',
            'no') 英文名       --没有end，并且起别名，不需要加单引号
from person p;

一般通用条件表达式写法比较常用

多行函数(聚合函数:count()、sum()、max()、min()、avg())

count()
select count(1) from person;  --count(1)==count(*),推荐使用count(1)
sum()、max()、min()、avg()与mysql中相同

十一. 分组查询

查询出每个部门的平均工资

select e.deptno,avg(e.sal)
from employees e
group by e.deptno;
--分组查询中，出现在group by后面的原始列，才能出现在select后面
--没有出现在group by后面的列，想在select后面，必须加上聚合函数.如：avg(e.sal)

查询平均工资高于2000的部门信息

select e.deptno,avg(e.sal)
from employees e
group by e.deptno
having avg(e.sal)>2000;--条件
--所有的条件（即：where或having等条件语句）都不能使用别名来判断：因为查询的顺序是先看条件，再查询。,二如果在条件中加了别名，则会发生找不到字段的错误。

查询出每个部门工资高于800的员工的平均工资

select e.deptno,avg(e.sal)
from employees e
where e.sal>800
group by e.deptno;
--where和having的区别：where是过滤分组前的数据，having是过滤分组后的数据
--表现形式：where必须在group by之前，having必须在group by之后。【重点】

查询出每个部门工资高于800的员工的平均工资，然后再查询出平均工资高于2000的部门

select e.deptno,avg(e.sal)
from employees e
where e.sal>800
group by e.deptno
having avg(e.sal)>2000;

十二. 多表查询

多表查询中的一些概念
笛卡尔积（与mysql相同）
select * from emp,dept;--得到笛卡尔积
等值连接[常用]
select * from emp e,dept d where e.deptno = d.deptno;
内连接
select *
from emp e inner join dept d
on e.deptno = d.deptno;
--内连接与等值连接效果相同
外连接
查询出所有部门，以及部门下的员工信息
select * 
from emp e right join dept d
on e.deptno = d.deptno;
查询所有员工，以及员工所属部门
select * 
from emp e left join dept d
on e.deptno = d.deptno;
oracle中专用外连接
select * 
from emp e,dept d
where e.deptno(+) = d.deptno; --(+)写在e.deptno后，显是的是d的全部信息，此时与右外连接效果相同
--当(+)写在d.deptno后，显是的是e的全部信息,此时与左外连接效果相同
自连接:自连接就是站在不同的角度把一张表看成多张表
查询出员工姓名，员工领导的姓名
select el.ename,e2.ename
from emp e1,emp e2
where e1.mgr = e2.empno;
查询出员工姓名、员工部门名称、员工领导姓名
select e1.ename,d.dname,e2.ename
from emp e1,dept d,emp e2
where e1.deptno = d.deptno
and e1.mgr = e2.empno;
查询出员工姓名、员工部门名称、员工领导姓名、员工领导部门名称
select e1.ename,d1.dname,e2.ename,d2.dname
from emp e1,dept d1,emp e2,dept d2
where e1.deptno = d1.deptno
and e1.mgr = e2.empno
and e2.deptno = d2.deptno;
子查询
子查询返回一个值
查询出工资和SCOTT一样的员工信息
select * from emp where sal in (select sal from emp where ename = 'SCOTT');
（注意：在与名字进行比较的时候，要考虑公司员工重名的现象，所以不能用=，而是用in）
子查询返回一个集合
查询出工资和10号部门任意员工一样的员工信息
select * from emp where sal in (select sal from emp where deptno = 10);
子查询返回一张表
查询出每个部门最低工资，和最低工资员工姓名，和该员工所在部门名称
--1.   先查询出每个部门最低工资
select deptno,min(sal) msal
from emp
group by deptno;
--2.  三表联查，得到最终结果[重点]
select t.deptno,t.msal,e.ename,d.dname
from(select deptno,min(sal) msal
     from emp
     group by deptno;
) t,emp e,dept d
where t.deptno = e.deptno
and t.msal = e.sal
and e.deptno = d.deptno;

十三. Oracle中的分页【重点】

行号：rownum

--rownum行号，当我们在做select操作的时候，每查询出一行记录，就会在该行上加一个行号rownum。
--rownum不属于任何一张表，多以不能使用表名/表的别名.rownum 行号从1开始，依次递增，不能跳着走
--注意：rownum 不能进行 大于一个非0正数的操作。如：where rownum>5(错)

emp表工资倒叙排列后，每页五条记录，查询第二页,即查询（第6-10条记录）

--排列操作会影响rownum的顺序
select * from(
       select rownum rn,e.* from(   --给内层rownum起别名，供外部调用
              select * from emp order by sal desc --先进性逆序排列，此时因为先执行的select * from emp，
                                                  --所以已经有了一个行号的排列，在进行逆序排列时候，行号就会变乱，
                                                  --所以需要外部再对逆序后的数据进行重新对行号排列，
                                                  --也就是再进行一次select。
       ) e where rownum<11
)where rn>5 --此处是调用内部rownum的记录，因为内层rownum并没有重新排列，且已经是内部查询结果表中的数值，
            --可以调用，且不是表名/表别名.rownum调用的方式

【重点】：记住分页架构，除了最内部查询语句外，外两层除了分页规则的数字外，都是固定的。

select * from(
       select rownum rn,e.* from( --外部架构不变
              select******可变*************
       ) e where rownum<11
)where rn>5 --其中，11和5是分页规则，应该是由程序传过来的可变值，不能写死

十四. 视图

视图的概念：视图就是提供一个查询的窗口，所有的数据来源于表
-创建表可以跨用户创建
--查询语句创建表
create table emp as select * from scott.emp;
创建视图【必须有dba权限】
create view v_emp as select ename,job from emp;--创建视图v_emp,注意视图命名规范
-查询视图
select * from v_emp;
修改视图:注意：修改视图，视图对应的表中数据也修改了。–不建议修改视图，视图只用来进行查询
update v_emp set job='CLERK' where ename = 'ALLEN';
commit;
创建只读视图
create view v_emp1 as select ename,job from emp with read only;
视图的作用
视图的作用？
第一：视图可以屏蔽敏感字段，如（工资）
第二：保证总部和分部数据及时统一（总部修改原表，分部查看视图）

十五. 索引

索引的概念：在表的列上建立一个二叉树

达到大幅度提高查询效率的目的，但是索引会影响增删改的效率

单列索引（建立在单列上）

--单列索引触发规则:条件必须是索引列中的原始值【面试】
--单行函数，模糊查询都会影响索引的触发
select * from emp where ename='SCOTT';

复合索引（建立在多列上）

--创建复合索引
create index idx_ename job on emp(ename,job);
--复合索引中第一列为优先检索列
--如果要触发复合索引，必须包含有优先检索列中的原始值。
select * from emp where ename = 'SCOTT' and job='xx';--触发复合索引
select * from emp where ename = 'SCOTT' or job='xx';--不触发索引
select * from emp where ename = 'SCOTT';--触发单列索引

十六. pl/sql编程语言

--pl/sql语言是对sql语言的扩展，使得sql语言具有过程化编程的特性
--pl/sql编程语言比一般的过程化编程语言，更加灵活高效
--pl/sql编程语言主要用来编写存储过程和存储函数等

pl/sql编程语言定义变量

declare
  --声明变量区域，也可在此区域进行变量赋值 
begin 
  --执行代码区域
end 
--框架固定

declare
    i number(2) := 10; --定义i变量， := 赋值语句
    s varchar2(10):= '小ing';
begin
  dbms_output.put_line(i);  --输出语句
  dbms_output.put_line(s);
end;

引用型变量和into查询语句赋值和记录型变量

declare
    i number(2) := 10; --定义i变量， := 赋值语句
    s varchar2(10):= '小ing';
    ena emp.ename%type; --引用型变量：引用一行数据中的某一列，用以存放某一列的值
    emprow emp%rowtype; --记录型变量：引用某一行数据，存放的是一行数据，类似JavaBean
begin
  dbms_output.put_line(i);  --输出语句
  dbms_output.put_line(s);
  select ename into ena from emp where empno = 7788; --使用select语句，查询一列的值，赋值给引用型变量：ena
  dbms_output.put_line(ena);
  select * into emprow from emp where empno = 7788; --使用select语句，查询一行的值，赋值给记录型变量：emprow
  dbms_output.put_line(emprow.ename || '的工作为：' || emprow.job); --||为连接符，类似于Java中的+，可进行字符串拼接
end;

emp.ename%type; --引用型变量：引用一行数据中的某一列，用以存放某一列的值
emprow emp%rowtype; --记录型变量：引用某一行数据，存放的是一行数据，类似JavaBean
pl/sql中的if判断

输入小于18的数字，输出未成年，输入大于18小于40的数字，输出中年人，输入大于40的数字，输出老年人
declare
  i number(3) := &i; --表示输入一个i值，赋值给i，输入：&加一个变量（变量名随意）
begin
  if i<18 then
    dbms_output.put_line('未成年');
  elsif i<40 then --注意是elsif 中间没有e，也可以省略elsif 直接if，else
    dbms_output.put_line('中年人');
  else
    dbms_output.put_line('老年人');
  end if;  --最后if结束才加;
end;

pl/sql中的循环关键字：loop

--用三种方式输出1-10十个数字

--while循环
declare
  i number(2) := 1; --定义变量，初始值为1
begin
  while i<11 loop --设定循环条件 i<11 ,后面跟上开始循环标志：loop
    dbms_output.put_line(i);    --循环体
    i := i+1;  --设定自增，以达到循环结束条件
    end loop;  --结束循环标志
end;

--exit循环  [常用]
declare
  i number(2) := 1;
begin
  loop --开始循环
    exit when i>10; --设立循环结束条件
    dbms_output.put_line(i); --循环体
    i := i+1;                --循环体，设定自增，以达到循环结束条件
  end loop; --结束循环
end;

--for循环
declare
                      --for循环不需要额外设定变量
begin
  for i in 1..10 loop --设定i变量，在1-10之间循环，loop开始循环
    dbms_output.put_line(i);
  end loop; --结束循环
end;

pl/sql中的游标（类似于Java中的集合，可存放多个变量，用于多行记录）:cursor

输出emp表中所有员工的姓名

declare
  cursor cl is select * from emp; --cursor:定义游标的关键字 ，此时已经将emp中所有行信息存入右边cl中
  emprow emp%rowtype; --定义一个记录型变量，因为遍历出每行数据都是一整行
begin
  open cl; --必须打开游标
      loop
        fetch cl into emprow； --fetch：遍历取出一行数据，存于emprow中
        exit when cl%notfound; --cl%notfound当cl中没有数据的时候，退出循环
        dbms_output.put_line(emprow.ename);
      end loop;
  close cl; --关闭游标
end;

给指定部门员工涨工资

declare
  cursor c2(eno emp.deptno%type) --定义一个带参数的游标：c2(eno emp.deptno%type) 参数的变量名：eno，
                                 --参数遍历的类型：emp.deptno%type：与emp.deptno的类型一致
  is select empno from emp where deptno = eno; --将eno号部门的员工编号存入游标c2
  en emp.empno%type;
begin
  open c2(10); --打开游标的同时传入参数10，即给10号部门员工涨工资
     loop
       fetch c2 into en; --取出游标中一行数据存入en
       exit when c2%notfound;
       update emp set sal=sal+100 where empno = en;
       commit;
     end loop;
  close c2;
end;

十七. 存储过程

存储过程：存储过程就是提前已经编译好的一段pl/sql语言，放置在数据库端：procedure

可以直接被调用，这一段pl/sql一般都是固定步骤的业务，有Java端直接调用

给指定员工涨工资

create or replace procedure p1(eno emp.empno%type) --如果不写in/out，则默认是in
is
       
begin
  update emp set sal=sal+100 where empno = eno;
  commit;
end;
--存储过程结构固定

--调用
select * from emp where empno = 7788;
--测试存储过程p1
declare
begin
  p1(7788);
end;

存储函数:有返回值，并且调用存储函数必须有返回值接收:function

计算指定员工的年薪

create or replace function f_yearsal(eno emp.empno%type) return number --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(7788);
  dbms_output.put_line(s);
end;

out类型参数

--使用存储过程来计算年薪
create or replace procedure p_yearsal(eno emp.empno%type,yearsal out number)--定义变量yearsal，用于将结果存入其中
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;

--测试p_yearsal
declare
  yearsal number(10);--定义结果接收参数
begin
  p_yearsal(7788,yearsal);--调用存储过程，将结果存入第二个参数中
  dbms_output.put_line(yearsal);
end;

in和out类型参数的区别

--凡是【参数】涉及到into查询语句赋值或者:=赋值，都必须使用out来修饰，也就是说，
--如果参数需要用into或者:=赋值，都需要定义为out类型

十八. JAVA连接Oracle,以及其中的存储函数和存储过程

连接Oracle需要的jar包

--Oracle10g对应：ojdbc14.jar，SID：xe
--Oracle11g对应：ojdbc6.jar SID:orcl

pom.xml文件引入jar包

 <dependencies>
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc14</artifactId>  ---连接Oracle必要的jar包--对应Oracle10g
            <version>10.2.0.4.0</version>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

连接参数

Dirver="oracle.jdbc.driver.OracleDriver"
URL = "jdbc:oracle:thin:@localhost:1521:xe";//xe为连接oracle的SID,@后为Oracle服务器所在的IP地址，1521：Oracle端口号
USER = "itoracle";
PASSWORD=""itoracle";"
    
--查看连接Oracle服务器的SID和数据库名(cmd中)
//查看本机的oracle的sid：select instance_name from v$instance;
//查看本机的oracle的数据库名：select name from v$database;

具体代码

java连接Oracle，执行简单的sql查询语句（PreparedStatement类）

 	@Test
    public void javaCallOracle() throws Exception{
        //加载oracle驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //得到Connection连接
        Connection connection = DriverManager.getConnection(url,user,password);
        //得到预编译Statement对象
        PreparedStatement pst = connection.prepareStatement("SELECT * FROM person where pid = ?");
        //给参数赋值
        pst.setObject(1,1);
        //执行查询
        ResultSet set = pst.executeQuery();
        while(set.next()){
            System.out.println(set.getString("pname"));
        }
        set.close();
        pst.close();
        connection.close();
    }

java连接Oracle，调用存储过程(CallableStatement类)，注意存储过程参数的写法：OracleTypes.NUMBER

	@Test
    public void javaCallOracleProcedure() throws Exception {
        //加载oracle驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //得到Connection连接
        Connection connection = DriverManager.getConnection(url,user,password);
        //CallableStatement类就是调用存储过程和存储函数的专门的类，使用prepareCall方法。
        CallableStatement call = connection.prepareCall("{call p_yearsal(?,?)}");//{call 存储过程名(参数1，参数2)}
        call.setObject(1,7788);
        //调用方法，给存储过程输出类型的参数，对应：OracleTypes.NUMBER
        call.registerOutParameter(2, OracleTypes.NUMBER);
        //执行sql
        call.execute();
        System.out.println(call.getObject(2));
        call.close();
        connection.close();
    }

java连接Oracle，调用存储函数(CallableStatement类)，注意存储函数参数的写法：返回值类型OracleTypes.NUMBER

	@Test
    public void javaCallOracleFunction() throws Exception{
        //加载oracle驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //得到Connection连接
        Connection connection = DriverManager.getConnection(url,user,password);
        //使用CallableStatement类调用存储函数
        CallableStatement call = connection.prepareCall("{? = call f_yearsal(?)}");
        //存储函数有返回值，所以有一个参数在最前面作为接收参数

        //传递参数
        call.setObject(1,OracleTypes.NUMBER);//为接收参数设定类型
        call.setObject(2,7788);
        //执行语句
        call.execute();
        //输出返回结果
        System.out.println(call.getObject(1));
        call.close();
        connection.close();
    }

CallableStatement类参数指定方法

/**
 * CallableStatement类调用存储过程和存储函数的参数指定方法
 * {call p_yearsal(?,?)} --调用存储过程，{call 存储过程名(参数1，参数2)} 存储过程无返回值，只有两个参数，一个是输入的数据，一个是数据类型
 * {? = call f_yearsal(?)} --调用存储函数，因为存储函数有返回值
 */