**
Oracle数据库：是一个文件系统，是无理概念。
实例：在Oracle的数据库中可有多个实例，通常我们只用一个实例
用户：一个实例下有多个用户。
表空间：一个实例下有多个表空间，表空间是逻辑概念，一个表空间对应着一个或者多个物理存储文件（.dbf,.ora）
用户和表空间的关系：一个用户有一个默认的表空间，一个表空间可以为多个用户作为默认表空间，用户和用户之间的数据是隔离，数据不会混。
Oracle数据库源：
driver: oracle.jdbc.OracleDriver
url: jdbc:oracle:thin:@localhost:1521:orcl
username: zhangsan
password: zhangsan
oracle中用户的概念相当于mysql中的database的概念
六、 命令行常用操作
1. 登录
运行cmd进入命令行
Sqlplus 用户名/密码 [as sysdba]
如果是超级管理员需要在用户名/密码后面加上 as sysdba,是以系统管理员的身份来登录的，如图。
如果是普通用户不需要as sysdba
2.查看当前连接数据库的用户
使用show user查看当前的用户<br /> 
3. 用户的切换
在登录的状态下输入：conn 用户名/密码 [as sysdba]
如图：
Ø 切换为超级管理员
Ø 切换为普通的用户
4. 查看用户下的表
为了防止命令行中数据展示表格错乱的情况可以设计行宽和列宽<br /> Set linesize 300; 每行展示300个字符<br /> Col 列名 for a[数字]，某一列占几个字符<br />在用户登录的情况下输入：select * from tab;<br /><br />查看表中的数据<br />Col ename for a8<br />Col mgr for 9999<br />Col sal for 9999<br />Select * from emp;<br /><br />如果使用dba用户登录查询该表无法查看到，因为sys用户下没有这个emp表<br /><br />由于超级管理员的权限很大可以查看Scott用户下的表<br />可以使用select * from [用户名].[表名] 来查看某用户下的表的数据<br />
5. 查看表的结构
Desc 表名
Number(4) 最大长度为4为的数值类型
Varchar2(10) 最大长度为10的字符串，varchar2用于存储可变长度的字符串，.varchar2把所有字符都占两字节处理(一般情况下)，varchar只对汉字和全角等字符占两字节，数字，英文字符等都是一个字节，VARCHAR2把空串等同于null处理，而varchar仍按照空串处理；建议在oracle中使用varchar2
Number(7,2) 数值类型整数位占5位，小数位占2位，一共7位。
Date 时间类型
6.oracle中新建用户和赋予权限
1.新建个用户
create user xxxxx(用户名) identified by “密码”
alert user 用户名 identified by “新密码” —修改用户密码
因为新建的用户和默认的用户是锁住的，没有权限。所以新建用户后要给用户赋予权限
grant dba to 用户名 —给用户赋予所有权限，connect是赋予连接数据库的权限，resource 是赋予用户只可以创建实体但是没有创建数据结构的权限。
grant create session to 用户名 —这个是给用户赋予登录的权限。
grant create table to 用户名 —给用户赋予表操作的权限
grant unlimited tablespace to 用户名 —给用户赋予表空间操作的权限
grant select any table to 用户名 —给该用户赋予访问任务表的权限 同理可以赋予update 和delete 的
grant select on srapp_hz_zhpt_yl.jggl to srapp_hz_zhpt_ylcs —这里是给srapp_hz_zhpt_ylcs用户赋予selectsrapp_hz_zhpt_yl用户的jggl表的查询的权限。同理可以有alter，drop，insert等权限。 ——————————————-注意 这个语句在没有访问另一个用户的权限情况下这个语句要在另一个用户登录情况下执行，这样才能生效。
——————-撤销权限
基本语法同grant,关键字为revoke 如： revoke create tabel to 用户名 就是取消这个用户的对表操作的权限。
—————-查看权限
select from user_sys_privs;//查看当前用户所有权限
select from user_tab_privs;//查看所用用户对表的权限
—————-角色
角色即权限的集合，可以把一个角色授予给用户，管理角色的权限比较简单，可以在一个用户下创建多个角色，用户只需要添加角色就可以管理权限了，便于用户管理权限。
create role myrole;//创建角色
grant create session to myrole;//将创建session的权限授予myrole
grant myrole to zhangsan;//授予zhangsan用户myrole的角色
drop role myrole;删除角色
七、Scott用户下的表结构
八、sql
**
1.表的管理
01.常用的数据类型
| No | 数据类型 | 描述 |
|---|---|---|
| 1 | Varchar， varchar2 | 表示一个字符串 |
| 2 | NUMBER | NUMBER(n)表示一个整数，长度是n |
| NUMBER(m,n):表示一个小数，总长度是m，小数是n，整数是m-n | ||
| 3 | DATA | 表示日期类型 |
| 4 | CLOB | 大对象，表示大文本数据类型，可存4G |
| 5 | BLOB | 大对象，表示二进制数据，可存4G |
02.建表
语法：
Create table 表名（
字段1 数据类型 [default 默认值],
字段2 数据类型 [default 默认值],
…
字段n 数据类型 [default 默认值]
）;
范例：创建person表
create table person(
pid number(10),
name varchar2(10),
gender number(1) default 1,
birthday date
);
insert into person(pid, name, gender, birthday)
values(1, ‘张三’, 1, to_date(‘1999-12-22’, ‘yyyy-MM-dd’));
03.表删除
语法：DROP TABLE 表名
04.表的修改
在sql中使用alter可以修改表
添加语法：ALTER TABLE 表名称 ADD(列名1 类型 [DEFAULT 默认值]，列名1 类型 [DEFAULT 默认值]…)
修改语法：ALTER TABLE 表名称 MODIFY(列名1 类型 [DEFAULT 默认值]，列名1 类型 [DEFAULT 默认值]…)
注意修改时如果原有某列的数据长度为200，则不可以把该列修改成小于200的列
范例：在person表中增加列address
alter table person add(address varchar2(10));
范例：把person表的address列的长度修改成20长度
alter table person modify(address varchar2(20));
05. 截断表
在person表中使用delete语句删除数据，则可以使用rollback来回滚，如果想要清空一张表的数据，同时不想回滚可以立即释放资源需要使用截断表的语法
语法：TRUNCATE TABLE 表名
范例：截断person表
truncate table person;
2.约束
在数据库开发中，约束是必不可少，使用约束可以更好的保证数据的完整性。
1.主键约束
主键约束都是在id上使用，而且本身已经默认了内容不能为空，可以在建表的时候指定。
创建一张表，把pid作为主键
create table person(
pid number(10) primary key,
name varchar2(10),
gender number(1) default 1,
birthday date
);
主键不可重复， SCOTT.SYS_C0017981是系统自动分配的约束的名字
主键不可为空
我们可以自己来指定主键约束的名字
create table person(
pid number(10),
name varchar2(10),
gender number(1) default 1,
birthday date,
constraint person_pk_pid primary key(pid)
);
2.非空约束
使用非空约束，可以使指定的字段不可以为空。
范例：建立一张pid和name不可以为空的表
create table person(
pid number(10) not null,
name varchar2(10) not null,
gender number(1) ,
birthday date,
);
3.唯一约束（unique）
表中的一个字段的内容是唯一的
范例：建表一个name是唯一的表
create table person(
pid number(10) ,
name varchar2(10) unique,
gender number(1) ,
birthday date
);
唯一约束的名字也可以自定义
create table person(
pid number(10) ,
name varchar2(10),
gender number(1) ,
birthday date,
constraint person_name_uk unique(name)
);
4.检查约束
使用检查约束可以来约束字段值的合法范围。
范例：创建一张表性别只能是1或2
create table person(
pid number(10) ,
name varchar2(10),
gender number(1) check(gender in (1, 2)),
birthday date
);
检查约束也可以自定义
create table person(
pid number(10) ,
name varchar2(10),
gender number(1),
birthday date,
constraint person_gender_ck check(gender in (1,2))
);
5.外键约束
之前所讲的都是单表的约束，外键是两张表的约束，可以保证关联数据的完整性。
范例：创建两张表，一张订单表，一张是订单明细表，订单和明细是一对多的关系
create table orders(
order_id number(10) ,
total_price number(10,2),
order_time date,
constraint orders_order_id_pk primary key(order_id)
);
create table order_detail(
detail_id number(10) ,
order_id number(10),
item_name varchar2(10),
quantity number(10),
constraint order_detail_detail_id_pk primary key(detail_id)
);
insert into orders values(1, 200, to_date(‘2015-12-12’,’yyyy-MM-dd’));
insert into order_detail values(1, 2, ‘java’,1);
我们在两张表中插入如上两条数据，我们发现在order_detail表中插入的order_id在order表中并不存在，这样在数据库中就产生了脏数据。此时需要外键来约束它。
我们再次建表
create table orders(
order_id number(10) ,
total_price number(10,2),
order_time date,
constraint orders_order_id_pk primary key(order_id)
);
create table order_detail(
detail_id number(10) ,
order_id number(10),
item_name varchar2(10),
quantity number(10),
constraint order_detail_detail_id_pk primary key(detail_id),
constraint order_detail_order_id_fk foreign key(order_id) references orders(order_id)
);
外键关联一定注意：
外键一定是主表的主键
删表时一定先删子表再删主表，如果直接删主表会出现由于约束存在无法删除的问题
但是可以强制删除drop table orders cascade constraint;(不建议)
删除主表的数据可以删除子表的关联数据，再删主表，也可以使用级联删除。级联删除在外键约束上要加上on delete cascade 如
constraint order_detail_order_id_fk foreign key(order_id)
references orders(order_id) on delete cascade
这样删除主表数据的时候会把字表的关联数据一同删除
3. rownum
ROWNUM:表示行号，实际上此是一个列,但是这个列是一个伪列,此列可以在每张表中出现。
查询emp表带有rownum列 ：select rownum, t.* from emp t
我们可以根据rownum来取结果集的前几行，比如前5行
但是我们不能取到中间几行，因为rownum不支持大于号，只支持小于号，如果想 实现我们的需求怎么办呢？答案是使用子查询，也正是oracle分页的做法。
select
from (select rownum rm, a. from (
select * from emp —固定写法，此处写具体查询逻辑
) a where rownum < 11) b
where b.rm > 5
分页：
pageNo :当前页码
pageSize:每页记录数
startNum:开始行号
endNum:结束行号
startNum = (pageNo - 1) pageSize ;
endNum = pageNo pageSize + 1 ;
select rownum, t.* from emp t;
select rownum, t.* from emp t where rownum < 6;
--rownum 不支持大于号，所以下面的这种分页方式是错误的
select rownum, t.* from emp t where rownum > 5 and rownum < 11;
--分页步骤
--第一步查询全量的数据
select * from emp;
--第二步以第一步的结果集作为一张表，限定条件是rownum小于结束行号， 结果列把rownum作为结果集
select rownum rw, a.* from (select * from emp) a where rownum < 6;
--第三步以第二步的结果集作为一张表，限定条件是第二步的rownum列大于开始行号，结果列是*
select *
from (select rownum rw, a.* from (
select * from emp
) a where rownum < 16) b
where b.rw > 10;
/*
已知条件
pageNo:当前的页码
pageSize:每页记录数
未知条件
startNum：开始行号
endNum：结束行号
pageNo pageSize startNum endNum
1 5 0 6
2 5 5 11
3 5 10 16
startNum = (pageNo - 1)*pageSize;
endNum = pageNo*pageSize + 1;
*/
4. 视图
视图就是封装了一条复杂查询的语句。
语法1.：CREATE VIEW 视图名称 AS 子查询
范例：建立一个视图，此视图包括了20部门的全部员工信息
create view empvd20 as select * from emp t where t.deptno = 20
视图创建完毕就可以使用视图来查询，查询出来的都是20部门的员工
语法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
select * from emp t where t.deptno = 20;
--创建部门是20的员工的视图
create view view_d20 as select * from emp t where t.deptno = 20;
--查询视图
select * from view_d20;
--创建视图的时候查询的sql不能有重复的列名
create view view_grade as select e.empno,
e.ename,
d.dname,
decode(s.grade,
1,
'一级',
2,
'二级',
3,
'三级',
4,
'四级',
5,
'五级',
'无级') egrade,
e1.ename enamel,
decode(s1.grade,
1,
'一级',
2,
'二级',
3,
'三级',
4,
'四级',
5,
'五级',
'无级') e1grade
from emp e, dept d, salgrade s, emp e1, salgrade s1
where e.deptno = d.deptno
and e.sal between s.losal and s.hisal
and e.mgr = e1.empno
and e1.sal between s1.losal and s1.hisal;
--查询视图
select * from view_grade;
--创建视图或者覆盖视图
create or replace view view_d20 as select * from myemp t where t.deptno = 20;
select * from view_d20;
--修改视图, 修改视图其实是修改视图对于的表的数据，视图不建议修改
update view_d20 t set t.ENAME = '史密斯' where t.EMPNO = 7369;
select * from myemp;
--创建只读的视图
create or replace view view_d20 as select * from myemp t where t.deptno = 20 with read only;
5. 序列
在很多数据库中都存在一个自动增长的列,如果现在要想在oracle 中完成自动增长的功能, 则只能依靠序列完成,所有的自动增长操作,需要用户手工完成处理。
语法：CREATE SEQUENCE 序列名
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE/ MINVALUE n|NOMAXVALUE}]
[{CYCLE|NOCYCLE}]
[{CACHE n|NOCACHE}];
范例:创建一个seqpersonid的序列,验证自动增长的操作
CREATE SEQUENCE seqpersonid;
序列创建完成之后,所有的自动增长应该由用户自己处理,所以在序列中提供了以下的两种操作：
NextVal :取得序列的下一个内容
CurrVal :取得序列的当前内容
select seqpersonid.nextval from dual;
select seqpersonid.currval from dual;
在插入数据时需要自增的主键中可以这样使用
在实际项目中每一张表会配一个序列，但是表和序列是没有必然的联系的，一个序列被哪一张表使用都可以，但是我们一般都是一张表用一个序列。
序列的管理一般使用工具来管理。
--序列虽然是给某个表使用，但是序列并没有绑定字某一张表，任何一张表使用这个序列都可以
create sequence seqpersonid;
--查询序列的下一个值(重点)
select seqpersonid.nextval from dual;
--查看当前值是多少
select seqpersonid.currval from dual;
insert into person
(person_id, pname, gender, birthday)
values
(seqpersonid.nextval, '张三', 1, sysdate);
select * from person;
每查询一次，序列也会增加1
6. 索引
索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低i/o 次数,从而提高数据访问性能。索引有很多种我们主要介绍常用的几种:为什么添加了索引之后，会加快查询速度呢？
图书馆：如果杂乱地放书的话检索起来就非常困难,所以将书分类，然后再建一个箱子，箱子里面放卡片，卡片里面可以按类查询,按姓名查或者类别查,这样的话速度会快很多很多，这个就有点像索引。索引的好处就是提高你找到书的速度，但是正是因为你建了索引，就应该有人专门来维护索引，维护索引是要有时间精力的开销的，也就是说索引是不能乱建的，所以建索引有个原则：如果有一个字段如果不经常查询，就不要去建索引。现在把书变成我们的表，把卡片变成我们的索引，就知道为什么索引会快，为什么会有开销。
创建索引的语法：
创建索引：
1． 单例索引
单例索引是基于单个列所建立的索引，比如:
CREATE index 索引名 on 表名(列名)
2． 复合索引
复合索引是基于两个列或多个列的索引。在同一张表上可以有多个索引，但是
要求列的组合必须不同,比如：
Create index emp_idx1 on emp(ename,job);
Create index emp_idx1 on emp(job,ename);
范例：给person表的name建立索引
create index pname_index on person(name);
范例：给person表创建一个name和gender的索引
create index pname_gender_index on person(name, gender);
索引的使用原则：
Ø 在大表上建立索引才有意义
Ø 在where子句后面或者是连接条件上建立索引
索引的层次不要超过4层
select * from PERSON t;
--创建单例索引
create index pname_index on person(pname);
--索引不需要主动使用，根据索引列查询时就自动的使用了索引
select * from person t where t.pname = '张三';
--复合索引,查询数据使用的时候是有索引顺序的
create index pg_index on person(pname, gender);
select * from person t where t.pname = '张三' and t.gender = 1;
--下面这sql就不会使用pg_index
select * from person t where t.gender = 1 and t.pname = '张三';
create index gp_index on person(gender, pname);
九、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.程序语法：
declare
说明部分 （变量说明，游标申明，例外说明 〕
begin
语句序列 （DML语句〕…
exception
例外处理语句
End;
2.常量和变量定义
在程序的声明阶段可以来定义常量和变量。
Ø 变量的基本类型就是oracle中的建表时字段的变量如char, varchar2, date, number, boolean, long)，
定义语法：varl char(15);
Psal number(9,2);
说明变量名、数据类型和长度后用分号结束说明语句。
常量定义：married boolean:=true
--基本数据类型变量和常量
declare
pname varchar2(10);
age number(3) := 20;
begin
pname := 'zhangsan';
dbms_output.put_line(pname);
dbms_output.put_line(age);
end;
Ø 引用变量
Myname emp.ename%type;
引用型变量，即my_name的类型与emp表中ename列的类型一样
在sql中使用into来赋值
--引用数据类型变量
declare
pname myemp.ename%type;
begin
select t.ename into pname from myemp t where t.empno = 7369;
dbms_output.put_line(pname);
end;
Ø 记录型变量
Emprec emp%rowtype
记录变量分量的引用
emp_rec.ename:=’ADAMS’;
declare
p emp%rowtype;
begin
select * into p from emp t where t.empno = 7369;
dbms_output.put_line(p.ename || ‘ ‘ || p.sal);
end;
select * from myemp;
update myemp t set t.sal = t.sal + 100 ;
--declare
begin
dbms_output.put_line('hello world');
end;
--基本数据类型变量和常量
declare
pname varchar2(10);
age number(3) := 20;
begin
pname := 'zhangsan';
dbms_output.put_line(pname);
dbms_output.put_line(age);
end;
--引用数据类型变量
declare
pname myemp.ename%type;
begin
select t.ename into pname from myemp t where t.empno = 7369;
dbms_output.put_line(pname);
end;
--记录类型变量，可以对应java中的对象类型变量
declare
prec myemp%rowtype;
begin
select * into prec from myemp t where t.empno = 7369;
dbms_output.put_line(prec.ename || ' '|| prec.sal);
end;
if
declare
pno number(4) := #
begin
if pno = 1 then
dbms_output.put_line('我是1');
elsif pno = 2 then
dbms_output.put_line('我是2');
else
dbms_output.put_line('其他');
end if;
end;
loop
declare
total number(4) := 0;
begin
while total < 100 loop
total := total + 1;
dbms_output.put_line(total);
end loop;
end;
------------------------------------
--最常用的循环方式
declare
total number(4) := 0;
begin
loop
exit when total = 100;
total := total + 1;
dbms_output.put_line(total);
end loop;
end;
----------------------------------
--适合于连续的数值的遍历
declare
total number(4) := 0;
begin
for total in 1 .. 100 loop
dbms_output.put_line(total);
end loop;
end;
Cursor
--使用游标方式输出emp表中的员工编号和姓名
declare
cursor c1 is
select * from emp; --定义游标
prec emp%rowtype; --定义记录型的变量
begin
open c1;--打开游标
loop
fetch c1
into prec;--从游标中取值，取值后游标会自动向下移动一步
exit when c1%notfound;
dbms_output.put_line(prec.empno || ' '|| prec.ename);
end loop;--结束循环
close c1; --关闭游标
end;
--按员工的工种长工资,总裁1000元，经理长800元其，他人员长400元。
select * from myemp;
declare
prec myemp%rowtype;
cursor c1 is
select * from myemp;
addsal number(4);
begin
--打开游标
open c1;
loop
fetch c1
into prec;
exit when c1%notfound;
if prec.job = 'PRESIDENT' then
addsal := 1000;
elsif prec.job = 'MANAGER' then
addsal := 800;
else
addsal := 400;
end if;
update myemp t set t.sal = t.sal + addsal where t.empno = prec.empno;
end loop;
--关闭游标
close c1;
commit;
end;
----带有参数的游标
--写一段PL/SQL程序，为部门号为10的员工涨工资。
select * from myemp t where t.deptno = 10;
declare
cursor c1(dno myemp.deptno%type) is
select * from myemp t where t.deptno = dno;
prec myemp%rowtype;
begin
--打开带有参数游标，除了此处其他的地方游标都不用带参数
open c1(10);
loop
fetch c1
into prec;
exit when c1%notfound;
update myemp t set t.sal = t.sal + 1000 where t.empno = prec.empno;
end loop;
--关闭游标
close c1;
commit;
end;
例外
系统定义例外
no_data_found (没有找到数据)
too_many_rows (select …into语句匹配多个行)
zero_divide ( 被零除)
value_error (算术或转换错误)
timeout_on_resource (在等待资源时发生超时)
declare
pnum number(4) := 5;
begin
pnum := pnum / 0; --发生异常
exception
---处理异常
when zero_divide then
dbms_output.put_line('被0除');
when value_error then
dbms_output.put_line('数值转换错误');
when others then
dbms_output.put_line('其他异常');
end;
---查询部门编号是50的员工
declare
prec emp%rowtype;
cursor c1 is
select * from emp t where t.deptno = 50;
no_data exception; --异常类型的定义
begin
open c1;
loop
fetch c1
into prec;
if c1%notfound then
raise no_data; --抛出异常
end if;
end loop;
close c1;
exception
--处理异常
when no_data then
dbms_output.put_line('没有员工');
when others then
dbms_output.put_line('其他异常');
end;
declare
pnum number(4) := 5;
begin
pnum := pnum / 0; --发生异常
exception
---处理异常
when zero_divide then
dbms_output.put_line('被0除');
when value_error then
dbms_output.put_line('数值转换错误');
when others then
dbms_output.put_line('其他异常');
end;
---查询部门编号是50的员工
declare
prec emp%rowtype;
cursor c1 is
select * from emp t where t.deptno = 50;
no_data exception; --异常类型的定义
begin
open c1;
loop
fetch c1
into prec;
if c1%notfound then
raise no_data; --抛出异常
end if;
end loop;
close c1;
exception
--处理异常
when no_data then
dbms_output.put_line('没有员工');
when others then
dbms_output.put_line('其他异常');
end;
存储过程
存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。
创建存储过程语法：
create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]
AS
begin
PLSQL子程序体；
End;
或者
create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]
is
begin
PLSQL子程序体；
End 过程名;
—
create or replace procedure countYSal(pno in emp.empno%type, ysal out number) is
psal emp.sal%type;
pcomm emp.comm%type;
begin
--多个值的赋值是按照先后顺序来赋值的
select t.sal, t.comm into psal, pcomm from emp t where t.empno = pno;
ysal := psal*12 + nvl(pcomm, 0);
end countYSal;
create or replace procedure helloword as
begin
dbms_output.put_line('hello world');
end;
create or replace procedure addsal(pno in myemp.empno%type) as
--定义变量
prec myemp%rowtype;
begin
select * into prec from myemp t where t.empno = pno;
update myemp t set t.sal = t.sal + 100 where t.empno = pno;--事务的提交要在调用端做
dbms_output.put_line('涨工资前是：' || prec.sal || ' 涨工资后是：' ||
(prec.sal + 100));
end;
select * from myemp t where t.empno = 7369;
---计算指定员工的年薪
create or replace procedure countYSal(pno in emp.empno%type, ysal out number) is
psal emp.sal%type;
pcomm emp.comm%type;
begin
--多个值的赋值是按照先后顺序来赋值的
select t.sal, t.comm into psal, pcomm from emp t where t.empno = pno;
ysal := psal*12 + nvl(pcomm, 0);
end countYSal;
--通过pl/sql程序调用存储过程
declare
ysal number;
begin
countysal(7369, ysal);
dbms_output.put_line(ysal);
end;
Oracle创建包（Package）和包体（Package Body）
http://www.yvyong.com/shujuku/Oracle/35.html
简介 在oracle数据库中，package包如同java中package中的作用大致类似；可以把一些过程和函数组织到一起，把PL/SQL代码模块化，构建其他人员重用的代码，在oracle中，包里面可以写存储过程，写方法，写sql语句，定义一个结构体等等。包有包头和包体，包头和包体的名称要相同。
创建包
--创建包
CREATE OR REPLACE PACKAGE PKG_PRINT IS
PROCEDURE PROC_PRINT_SAL_BY_EMPNO(V_EMPNO IN NUMBER);
PROCEDURE PROC_PRINT_DNAME_BY_DEPTNO(V_DEPTNO IN NUMBER);
END PKG_PRINT;
创建包体
--创建包体
CREATE OR REPLACE PACKAGE BODY PKG_PRINT IS
PROCEDURE PROC_PRINT_SAL_BY_EMPNO(V_EMPNO IN NUMBER) AS
V_SAL NUMBER;
BEGIN
SELECT A.SAL INTO V_SAL FROM EMP A WHERE A.EMPNO = V_EMPNO;
DBMS_OUTPUT.PUT_LINE(V_SAL);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END PROC_PRINT_SAL_BY_EMPNO;
PROCEDURE PROC_PRINT_DNAME_BY_DEPTNO(V_DEPTNO IN NUMBER) AS
V_DNAME VARCHAR2(40);
BEGIN
SELECT A.DNAME INTO V_DNAME FROM DEPT A WHERE A.DEPTNO = V_DEPTNO;
DBMS_OUTPUT.PUT_LINE(V_DNAME);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END PROC_PRINT_DNAME_BY_DEPTNO;
END PKG_PRINT;
调用
--调用包中的过程
BEGIN
PKG_PRINT.PROC_PRINT_SAL_BY_EMPNO(7521);
END;
BEGIN
PKG_PRINT.PROC_PRINT_DNAME_BY_DEPTNO(10);
END;
函数
create or replace function 函数名(Name in type, Name in type, …) return 数据类型 is
结果变量 数据类型;
begin
return(结果变量);
end函数名;
存储过程和存储函数的区别
一般来讲，过程和函数的区别在于函数可以有一个返回值；而过程没有返回值。
但过程和函数都可以通过out指定一个或多个输出参数。我们可以利用out参数，在过程和函数中实现返回多个值。
create or replace function countysalf(pno in emp.empno%type) return number is
Result number;
psal emp.sal%type;
pcomm emp.comm%type;
begin
select t.sal, t.comm into psal, pcomm from emp t where t.empno = pno;
Result := psal * 12 + nvl(pcomm, 0);
return(Result);
end countysalf;
触发器
数据库触发器是一个与表相关联的、存储的PL/SQL程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时，Oracle自动地执行触发器中定义的语句序列。
触发器可用于
数据确认
实施复杂的安全性检查
做审计，跟踪表上所做的数据操作等
数据的备份和同步
触发器的类型
语句级触发器 ：在指定的操作语句操作之前或之后执行一次，不管这条语句影响 了多少行 。
行级触发器（FOR EACH ROW） ：触发语句作用的每一条记录都被触发。在行级触 发器中使用old和new伪记录变量, 识别值的状态。
create or replace trigger insertptrg
before insert on person
begin
dbms_output.put_line('一条记录被插入');
end insertptrg;
insert into person values(seqpersonid.nextval, '王五', 1, sysdate);
create or replace trigger valid_insert_p
before insert on person
declare
cruday varchar2(10);--定义字符串的时候要给长度
begin
select to_char(sysdate, 'day') into cruday from dual;
if cruday in ('星期三') then
raise_application_error(-20001, '星期三不允许插入数据');
end if;
end valid_insert_p;
create or replace trigger vaild_addsal
before update of sal on myemp
for each row
begin
if :new.sal <= :old.sal then
raise_application_error(-20002, '涨后的工资不能比涨前的低');
end if;
end valid_addsal;
update myemp t set t.sal = t.sal - 100 where t.empno = 7369;
在触发器中触发语句与伪记录变量的值
| 触发语句 | :old | :new |
|---|---|---|
| Insert | 所有字段都是空(null) | 将要插入的数据 |
| Update | 更新以前该行的值 | 更新后的值 |
| delete | 删除以前该行的值 | 所有字段都是空(null) |
十、java调用存储过程
package cn.itcast.pro;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import oracle.jdbc.OracleTypes;
public class TestProFun {
public static void main(String[] args) {
//testPro();
testFun();
}
/**
* 存储过程调用
*/
public static void testPro(){
String driver = "oracle.jdbc.OracleDriver";
String connStr = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
Connection conn = null;
CallableStatement cs = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(connStr, "scott", "tiger");
//获得CallableStatement对象, 调用的sql ：{call countysal(?,?)}
cs = conn.prepareCall("{call countysal(?,?)}");
cs.setInt(1, 7369);
//注册Oracle的输出参数的类型
cs.registerOutParameter(2, OracleTypes.NUMBER);
//执行存储过程
cs.execute();
//获得返回值
int ysal = cs.getInt(2);
System.out.println(ysal);
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
if(cs != null){
cs.close();
}
if(conn != null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 存储函数调用
*/
public static void testFun(){
String driver = "oracle.jdbc.OracleDriver";
String connStr = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
Connection conn = null;
CallableStatement cs = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(connStr, "scott", "tiger");
//获得CallableStatement对象, 调用的sql ：{? = call countysal(?)}
cs = conn.prepareCall("{? = call countysalf(?)}");
cs.setInt(2, 7369);
//注册Oracle的输出参数的类型
cs.registerOutParameter(1, OracleTypes.NUMBER);
//执行存储过程
cs.execute();
//获得返回值
int ysal = cs.getInt(1);
System.out.println(ysal);
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
if(cs != null){
cs.close();
}
if(conn != null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
若有收获，就点个赞吧
0 人点赞
