首页 > 代码库 > Oracle知识整理
Oracle知识整理
1.自带三种登录方式：
Scott/tiger sys/manager system/manager
2.基本的操作
1） 建数据库
create tablespace 表空间的名称
datafile ‘路径+文件.dbf’
size 初始大小m
autoextend on --是否为自动增长
eg:
create tablespace myspace
datafile ‘e:\a.dbf‘
size 5m
autoextendon
2）建用户
create user 用户名
identified by 密码 --当密码为全数字是用”全数字”
[default tablespace 该用户默认的表空间即（数据库）]
[default temporary tablespace 该用户默认的临时表空间]
--系统当前的临时表空间 --系统默认的Temp
Eg:
create user myuser
identified by myuser
default tablespace myspace
3) 修改用户：
alter user 用户名
identified by 新密码
alter user 用户名
default tablespace 新表空间名称
修改用户名：
i. 查找数据库user$ 中的对应的用户名，得到user#的值
select user# from user$ where name = ‘用户名’
ii. 根据查到的user#的值，修改用户的名称信息
update user$ set name = ‘新用户名’ where user# = ‘查到的用户编号’
iii. 提交修改
commit system checkpoint
alter system checkpoint
alter system share_ pool
4) 授权、撤销
grant /revoke connect | resource | dba to 用户名
5) 用户账号的锁定和解锁
alter user 用户名 account unlock/lock;
6)常见的表和视图
Select * from cat;//查看该用户所有的表
Desc 表名;//查看该表的所有信息
select* from user_tables;//该用户下所有的表，与cat对应，只是这个查出的表的信息比cat详细
7）创建表
Create table 表名（
字段1 类型 primary key //主键
,字段2 类型 not null,default 20 //非空
,字段3 类型 unique//唯一索引
,字段4 类型check(gender in (‘男’,’女’))
,字段5 类型references 主表名（主表中的字段）
[ondelete cascade|set null | deferrableinitially deffered]
//级联删除、级联设空、级联更新
）
在所有字段后边，一起写
Createtable 表名（
Constraint约束名 primary key (字段名)
Constraint约束名 foreign key 本表的外键字段名 references 主表（主表字段名）
）
创建表后，添加约束
Alter table 表名
Addprimary key (主键字段名)
Modify现有字段名 not null
Addconstraint 约束名 check(约束条件)
Addunique(字段名称)
Addconstraint 外键名称 foreign key (本表的外键名称) references 主表（主表的逐渐名称）
8）修改表
a) 修改表名 rename 旧名 to 新名
b) 删除表 delete from 表名 / truncate table 表名 删除表中的数据
Drop table 表名 删除表
c) 修改表
Alter table 表名
Add 字段名 字段类型 [约束]
Modify 字段名 字段类型
Drop column 字段名 --删除字段
Drop constraint 约束名 -- 删除约束
9）查询
模糊查询 ‘%’(多个)’_’（一个）
10）函数
a)Add_months(日期，要加的月份) sysdate//当前的时间
select add_months(sysdate,10) as a from dual;
A
-----------
2014-7-911
b)Months_between(日期1,日期2) （日期1-日期2）的月份
Select months_between(to_date(‘2013/9/6‘,‘yyyy/mm/dd‘), to_date(‘2013/5/6‘,‘yyyy/mm/dd‘)) as b from dual
B
----------
4
c)last_day(日期) 该月份的最后一天
d)next_day(date,week)
select next_day(to_date(‘2013/5/6‘,‘yyyy/mm/dd‘),‘星期五‘) as b from dual
B
-----------
2013-5-10
e)连接字符串
concat(字符a,字符b)
f)求子串
substr(待处理的字符串,起始位置（从1开始）,截取的长度)
h)定位函数
instr(待处理的字符串,需要查找的字符串,开始查找的起始位置,第几次查找到)
返回位置要查找的字符串的位置
i) 修改大小写
Lower(字符串) 小写 upper（字符串） 大写
j) 首字符大写
initacap(字符串)
k)替换
replace(待处理的字符串，要替换的，替换为)
selectreplace(‘afafdsa‘,‘a‘,‘c‘) from dual;--将a替换为c
l)长度
length(字符串)
selectlength(‘afafdsa‘) from dual;
m)数字
round(数字，精度)
selectround (5.3157,2) as b from dual;
trunc(数字) 取整，不进行四舍五入
ceil(3.1) -- 4
floor(3.9) –3
n)混合函数
nvl(e1,e2) 如果e1为空，则取e2
coalesce(e1,e2,e3,…en) 如果e1为空，则取e2,如果e2为空，则取e3…
11）序列
a)创建序列
Create sequence <序列名>
Increment by n
Start with n
b)删除序列
drop sequence 序列名
c）修改序列 alter sequence 序列名
d) 使用序列的值
序列名.nextval—该序列下一个值
序列名.currval –该序列的当时的值
12）视图
a) 创建视图
Create or replace view 视图名
As
Select语句
b) 使用视图
Select * from 视图名
c) 特殊
没有使用连接 集合 组函数
select 语句中没有聚合 group by distinct(不重复)
13）同义词
Create or replace public synonym 用户名.同义词名 for 用户名.表或视图
Eg:
create or replace synonym scott.k for scott.m
14)索引
Create index 索引名称 on 表名（字段名称）
15）存储过程
procedure-存储过程
封装名字的PL块
create or replace procedure 存储过程名称 (参数列表)
as
去除declare关键字的pl/sql块；
end;
1）无参存储过程
create or replace procedure mypro1
as
cursor mycursor is
select * from emp where deptno =30;
myrow emp%rowtype;
begin
for myrow in mycursor
loop
dbms_output.put_line(myrow.ename);
end loop;
end;
执行
exec 存储过程名称
pl/SQL中执行
begin
mypro1;
end;
2）有参存储过程(输入类型-默认 in)
create or replace proceduremypro2(myempno in emp.empno%type)
as
myrow emp%rowtype;
begin
select * into myrow
from emp
where empno = myempno;
dbms_output.put_line(myrow.ename);
end;
执行：execmypro2(7788)
3）有参存储过程（输出类型-out）
create or replace proceduremypro3(myempsal out emp.sal%type)
as
-- myrow emp%rowtype;
begin
select sal into myempsal
from emp
where empno = 7788;
end;
执行：pl/sql块的形式
declare
ms emp.sal%type;
begin
mypro3(ms);
dbms_output.put_line(ms);
end;
16）函数
function-函数
语法：
create or replace function 函数名称
return 类型
as
局部变量声明
begin
。。。。。
return 返回函数值
end;
create or replace function myfunc
(num1 number, num2 number)
return number
as
sum1 number := 0;
begin
sum1 := num1 + num2;
return sum1;
end;
执行：
select myfunc(10, 20) from dual;
17）触发器
触发器-tigger
*满足、实现：用户自定义数据完整性
1）创建日志表：
create table userlog
(
empname varchar2(10)
, operateDate date
)
2）完成触发器：
create or replace trigger mytrigger
after|before insert|update|delete on 表名
for each row
begin
....
end;
+++++
create or replace trigger mytrigger
after delete on emp
for each row
begin
insert into userlog values(:old.ename,sysdate);
end;
==================
3）执行：满足条件，自动执行
3．数据库的概念
sql语句的分类
DDL：数据库定义语言
Create/alter/drop/truncate(都不存入日志文件)
DML：数据修改语言
CIUD:select /insert/update/delete ==将计入日志
DCL:控制语言
Revoke/grant
DTL:事务语言
Commit/rollback/savapoint
Truncate 和 delete 的区别
1、TRUNCATE速度快。
2、TRUNCATE是一个DDL语言，向其他所有的DDL语言一样，他将被隐式提交，不能对TRUNCATE使用ROLLBACK命令。
4、TRUNCATE不能触发任何DELETE触发器。
5、不能授予任何人清空他人的表的权限。
6、当表被清空后表和表的索引讲重新设置成初始大小，而delete则不能。
7、不能清空父表。 TRUNCATE TABLE(schema)table_name DROP(REUSE) STORAGE 在默认是 DROPSTORAGE 当使用DROP STORAGE时将缩短表和表索引，将表收缩到最小范围，并重新设置NEXT参数。REUSE STORAGE不会缩短表或者调整NEXT参数在特殊情况下使用 REUSE ST
DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的的删除操作作为事务记录在日志中保存以便进行进行回滚操作。
TRUNCATETABLE 则一次性地从表中删除所有的数据页并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。
*********************************
4. pl/sql语言
1）基本语句
Declare
变量名 变量类型
Begin
执行语句体（多句）;
Exception
异常处理代码
End
Eg:
declare
empid emp.empno%type;
begin
empid:= 15;
dbms_output.put_line(empid);
end;
输入：
declare
empid emp.empno%type;
begin
empid := &雇员号;--弹出输入框，输入框的给的提示
dbms_output.put_line(empid);
end;
2）循环
for：
for 循环变量 in 起始数值..终止数值
loop
具体语句
end loop;
eg:
begin
--外重循环1-9
for i in 1 .. 9
loop
--内重循环 1- i
for j in 1 .. i
loop
--内重循环，首先显示一行的信息
dbms_output.put( i || ‘*‘ || j || ‘=‘|| (i*j) || ‘ ‘);
end loop;
--完成换行
dbms_output.put_line(‘ ‘);
end loop;
end;
while：
while 条件
loop
。。。循环语句
end loop;
eg:
declare
--定义循环变量
i number(2);
begin
--定义循环变量的初始值
i := 1;
--给出终止条件
while i <= 10
loop
dbms_output.put_line(i);
--循环变量自增
i := i + 1;
end loop;
end;
loop：
loop
exit when 条件;
end loop;
eg:
declare
--定义循环变量
i number(2);
begin
--定义循环变量的初始值
i := 1;
loop
--给出终止条件
exit when i > 10;
dbms_output.put_line(i);
--循环变量自增
i := i + 1;
end loop;
end;
3）选择
if 条件 then
.....;
[
elsif 条件 then
。。。。；
]
else
....;
end if;
例子：
输入用户名(zhangsan)、密码(zhangsan)，判断登陆。
declare
--定义变量，用来保存用户名和密码
username varchar2(10);
password varchar2(10);
--定义一个用来存储用户名的变量，从数据表中获取
empname varchar(10);
begin
--输入用户名和密码
username := ‘&用户名‘;
password := ‘&密码‘;
--查出指定的用户名，并放入变量中
select ename into empname from emp where empno=7788;
--判断用户名和密码是否等于指定的数据
if username = empname and password = ‘zhangsan‘ then
dbms_output.put_line(‘ok‘);
else
dbms_output.put_line(‘error‘);
end if;
end;
4）异常：使用when .. then 处理异常 === catch --- others
1）处理Oracle已知的异常
exception
when异常种类(大写！) then
。。。。。
***异常种类：others表示所有的异常-顶级异常
2）自定义异常
raise === throw 抛给Oracle系统
raise_application_error == throw 抛给调用的语言程序
raise:
declare
--定义异常变量
异常变量 exception;
begin
if条件 then
raise异常变量;
.....
endif;
exception
when异常变量 then
。。。。;
例子：
输入年龄，20-60，输入错误，抛出异常，并提示
***输入：
age := &年龄
***判断：
if age < 20 or age > 60 then
raise ...
end if;
***异常处理：
exception
when 异常变量
declare
--定义年龄变量
agenumber(2);
--定义异常变量
myexp exception;
begin
--完成年龄输入
age:= &年龄;
--判断是否抛出异常
ifage < 20 or age > 60 then
--异常的抛出 --- 给Oracle系统用 给when用
raise myexp;
--raise_application_error(错误号， ‘错误消息‘) ; -- 给语言处理程序使用
endif;
--如果有异常抛出，后续语句不执行
dbms_output.put_line(age);
--异常处理
exception
--开始匹配异常 -- 可以配准确异常，也可以用others匹配全部的异常
when myexp then
dbms_output.put_line(‘age is in 20--30....‘);
end;
5）游标
游标： 结果集， 多行数据集合
定义、存储、处理多行数据组成的结果集。
分类：
隐式、显示《定义一个游标变量的名称，存游标数据》
显示游标：
使用显示游标的步骤：
1）定义、创建一个游标变量，定义游标的时候放入数据
declare
cursor 游标名称 is SQL 查询语句;
例子：
cursor mycur isselect * from emp;
2）打开游标：
begin
open 游标名称;
举例：
open mycur;
3）对游标数据进行处理 --- 遍历 loop|for
LOOP：
loop
fetch 游标名称 into 行变量
--对该行变量进行处理
exitwhen 游标名称%notfound; --- 放在循环的首句
end loop;
**FOR：
for 行级变量 in 游标名称
loop
--对行级变量处理
end loop;
4）关闭游标
close 游标名称。
例子：输入部分号，显示该部门的所有员工信息。使用游标实现。
动态游标：一个游标可以获取不同类型的数据集合
在代码动态通过select语句创建并赋予游标。**游标中数据集类型不确定，可以多次赋予。。
===declare
1）定义引用游标类型
type mycurtype is ref cursor;
2）定义该类型的游标变量
mycur mycurtype;
===begin
3）打开游标同时赋予游标的数据
open mycur forselect * from emp -- = 变量-输入获取;
4）遍历游标
。。。
5）关闭游标
带参数的游标--静态游标
定义游标的时候，使用格式：
cursormycur(mydeptno emp.deptno%type)
is select *from emp where deptno = mydeptno
用游标时：
mycur(&部门编号)
********************** 动态游标
declare
type mycurtype is ref cursor;
mycur mycurtype;
erow emp%rowtype;
begin
open mycur for select * from emp;
loop
fetch mycur into erow;
exit when mycur%notfound;
dbms_output.put_line(erow.ename);
end loop;
close mycur;
end;
*******************************
***********************带参数游标
declare
cursor mycur(mydeptno emp.deptno%type)
is select * from emp where deptno =mydeptno;
myrow emp%rowtype;
begin
for myrow in mycur(&部门编号)
loop
dbms_output.put_line(myrow.ename);
end loop;
end;
包的例子：
创建一个程序包，名为scottemp，并在其中加入四个函数或存储过程，具体要求如下
- 通过empid得到用户名，以存储过程的方式实现
- 通过empid修改薪水，以函数的方式实现，返回修改后的薪水
create or replace package scottemp AUTHID CURRENT_USER is
FUNCTIONmodifies(empid emp.empno%type,esal emp.sal%type)
RETURN emp.sal%type
is
begin
update emp set emp.sal =esal where emp.empno = empid;
return esal;
endmodifies;
PROCEDURE getname(ename2 out emp.ename%type,empid in emp.empno%type)
as
begin
selectemp.ename into ename2 from emp where emp.empno = empid;
end;
end;
5.Jdbc调用存储过程以及函数
package com.icss.dao;
import java.sql.SQLException;
import java.sql.Types;
public class ChangeDao extends BaseDao {
public boolean modifies(int empid,double esal){
try {
openDB();
String sql = "{?=callscottemp.modifies(?,?)}";
cstmt = conn.prepareCall(sql);
cstmt.setInt(2, empid);
cstmt.setDouble(3, esal);
cstmt.registerOutParameter(1, Types.DOUBLE);
rsi = cstmt.execute();
System.out.println(cstmt.getDouble(1));
} catch (SQLException e) {
// TODO Auto-generatedcatch block
e.printStackTrace();
}finally{
closeDB();
}
return rsi;
}
public String getname(int empid){
String ename = "";
try {
openDB();
String sql = "{callscottemp.getname(?,?)}";
//第一个问号是out类型的数据
cstmt = conn.prepareCall(sql);
cstmt.registerOutParameter(1, Types.NVARCHAR);
System.out.println(Types.NVARCHAR);
cstmt.setInt(2, empid);
cstmt.execute();
ename = cstmt.getNString(1);
} catch (SQLException e) {
// TODO Auto-generatedcatch block
e.printStackTrace();
}
closeDB();
return ename;
}
}
Oracle知识整理