如果没有安装ORACLE客户端提示oci.dll未加载
oracle数据库开发编程中,没有找到oci.dll,一般是系统的 path 设置有问题, 查找oci.dll, 然后加入到系统路径。
oci.dll 可下载解压到系统盘的system32目录下。然后打开“开始-运行-输入regsvr32 oci.dll”,回车即可解决错误提示!
regsvr32.exe 这个命令可以了解下:
Regsvr32命令用于注册动态链接库文件,是 Windows 系统提供的用来向系统注册控件或者卸载控件的命令,以命令行
方式运行。WinXP系统的regsvr32.exe在windows\system32文件夹下
创建数据库使用工具
dbca【数据库配置助手】 配置监听器
oracle database configuration assistant
conn scott/tiger@ORCAL
关闭防火墙,关闭网络,ip地址,实例名
如何插入列带有日期的表,并按照年-月-日的格式插入;
to_date函数:
(1)默认格式:
insert into emp values(9998,'alice','manager',7782,'12-12月-1988','yyyy-mm-dd');
(2)借助函数
insert into emp values(9998,'alice','manager',7782,to_date('1988-12-12','yyyy-mm-dd');
把旧表中的数据放到新表中
insert into new_tab (myid,myname,mydept ) select empno,ename,deptno from emp where deptno =10;
希望员工scott的岗位,工资,补助与smith员工一样;
update emp set(job,sal,comm) =(select job,sal,comm from emp where ename='smith') where ename='scott'
什么是事务?
事务用于保证数据的一致性,它由一组相关的dml语句组成,改组的dml语句要么全部成功,要么全部失败.
当执行事务操作时候(dml语句),oracle会在被作用的表上加锁,防止其他用户改变表结构
提交事务?
当执行使用commit语句可以提交事务,当执行了commit语句后,会确认事务的变化,结束事务,删除保存点
释放锁,当使用commit语句结束事务后,其它会话可以查看到事务变化后的新数据
回退事务?
在介绍回退事务前,得认识savepoint的概念和作用,保存点是事务中的一点,用于取消部分事务,当结束事务
时候,会自动的删除该事务所定义的所有保存点,当执行rollback时候,通过指定保存点可以回退到指定这里,
一旦提交所有事先的保存点都失去意义了;
也涉及到一个内嵌表问题,数据一个专门的区域来存放在在操作的内嵌表;
只读事务:
只读事务是指只容许执行查询的操作,而不容许执行任何 其它dml操作的事务,使用只读事务可以确保用户只能取得某事件点的数据,假定机票代售点每天18点开始统计今天的售票情况,这是可以使用只读事务
在设置了只读事务后,尽管其它会话可能会提交新的事务,但是只读事务将不会取得最新数据的变化,从而
可以保证取得特定时间点的数据信息;
abs(n) //返回数字n的绝对值
power(m,n) //返回m的n次幂
日期函数用于处理date类型的数据 (时间类型的数据是可以运算的,设计人员决定的)
默认情况下日期格式是dd-mon-yy 即 12-7月-14
sysdate 该函数返回系统时间
add_months(d,n) ///d给定的时间,在给定时加上n个月
last_day(d): 返回指定日期所在月份的最后一天 eg:1988-5-31
select sysdate from dual;
select to_char(sysdate,'yyyy-mm')
查找已经入职8个多月的员工
select * from emp where sysdate>add_months(hiredate,8);
select * from emp where sysdate>=add_months(hiredate,12*10);
select sysdate -hiredate "入职天数" from emp;
select trunc(sysdate -hiredate) "入职天数" from emp;
select hiredate,last_day(hiredate) from emp;
select hiredate,ename from emp where last_day(hiredate)-2=hiredate;
-----------------------------------------------------------------------------
yy yyyy mm dd hh24 hh12 mi ss
9 显示数字,并且忽略前面0
0 显示数字 如位数不足则用0补齐
$ 在数字前加美元符号
L 在数字前加本地货币符号
显示本地的货币 to_char函数 (总之对输出的东西进行字符串格式化处理)
to_char(sal,'L99999.99'); RMB1245.00 //五个整数,两个小数
to_char(sal,'L99,999.99'); //几个整数根据实际情况来处理
显示1980年入职的所有员工: (自由排列组合的获取字符段)
select * from emp where to_char(hiredate,'yyyy')=1980;
显示所有12月份入职的员工
select * from emp where to_char(hiredate,'dd')=12
系统函数
language
db_name
nls_date_format 对应的日期格式
host 主机名
查询正在使用的数据库
select sys_context('userenv'm,'db_name') from dual;
abs(n) //返回数字n的绝对值
power(m,n) //返回m的n次幂
日期函数用于处理date类型的数据 (时间类型的数据是可以运算的,设计人员决定的)
默认情况下日期格式是dd-mon-yy 即 12-7月-14
sysdate 该函数返回系统时间
add_months(d,n) ///d给定的时间,在给定时加上n个月
last_day(d): 返回指定日期所在月份的最后一天 eg:1988-5-31
select sysdate from dual;
select to_char(sysdate,'yyyy-mm')
查找已经入职8个多月的员工
select * from emp where sysdate>add_months(hiredate,8);
select * from emp where sysdate>=add_months(hiredate,12*10);
select sysdate -hiredate "入职天数" from emp;
select trunc(sysdate -hiredate) "入职天数" from emp;
select hiredate,last_day(hiredate) from emp;
select hiredate,ename from emp where last_day(hiredate)-2=hiredate;
-----------------------------------------------------------------------------
yy yyyy mm dd hh24 hh12 mi ss
9 显示数字,并且忽略前面0
0 显示数字 如位数不足则用0补齐
$ 在数字前加美元符号
L 在数字前加本地货币符号
显示本地的货币 to_char函数 (总之对输出的东西进行字符串格式化处理)
to_char(sal,'L99999.99'); RMB1245.00 //五个整数,两个小数
to_char(sal,'L99,999.99'); //几个整数根据实际情况来处理
显示1980年入职的所有员工: (自由排列组合的获取字符段)
select * from emp where to_char(hiredate,'yyyy')=1980;
显示所有12月份入职的员工
select * from emp where to_char(hiredate,'dd')=12
系统函数
language
db_name
nls_date_format 对应的日期格式
host 主机名
查询正在使用的数据库
select sys_context('userenv'm,'db_name') from dual;
数据表的逻辑备份和恢复
Oracle管理员的基本职责
表空间,数据字典
对于高级dba,要求能参与项目开发,会编写sql 语句,存储过程和触发器,规则,约束和包
sys 董事长 system 总经理
管理数据库的用户主要有sys 和 system
在前面我们已经提到这俩用户,区别是:
(1) 最重要的区别,存储的数据的重要性不同
sys: 所有oracle的数据字典的基表和视图都存放在sys用户中,这些基表和视图对于Oracle
的运行时至关重要的,由数据库自己维护,任何用户不能手动更改,sys用户拥有dba,sysdba,
sysoper角色或权限,是Oracle权限最高的用户
sys 只能以sysdba 和sysoper身份登录
conn sys/change_on_install as sysoper;
conn sys/change_on_install as sysdba;
基表---->动态视图 sysdba > sysoper >dba
------------------------------------------------
数据库/表的逻辑备份和恢复 --介绍
逻辑备份是指使用工具export将数据对象的结构和数据导出到文件的过程,逻辑恢复
是指当数据库对象被误操作而损坏后使用工具import利用备份的文件把数据对象导入到数据库
的过程,而物理备份可在open的状态下进行也可以在关闭数据库后进行,但是逻辑备份和恢复只能在
open的状态下进行;
导出表 导出自己的表
exp userid =scott/tiger@myoral tables=(emp) file=d:\e1.dmp
导出其他方案的表
exp userid=system/manager@myoral tables=(scott.emp) file=d:\e2.dmp
步骤:
特别说明: 在导入和导出的时候,要到oracle目录的 bin目录下
视频 oralce 17讲,只要表结构
exp userid =scott/tiger@myoral tables=(emp) file=d:\e1.dmp rows=n;
数据字典是oracle数据库中最重要的组成部分,它提供了数据库的一些系统信息
动态性能视图记载了例程启动后的相关信息;
数据字典
数据字典记录了数据库的系统信息,它是只读表和视图的集合,数据字典的所有者为sys用户
用户只能在数据字典上执行查询操作select 操作,而其维护和修改是由系统自动完成的
数据字典视图主要包括 user_xxx all_xxxx dba_xxx 三种类型
select table_name from user_tables;
all_tables: 用于显示当前用户可以访问的表,它不仅会返回当前用户方案的表,还会
返回当前用户可以访问的其他方案的表;
select table_name from all_tables;
To the world you may be one person, to me, you are my whole world
—— From Charles Dickens
查看Scott具有的角色
select * from dba_role_privs where GRANTEE='SCOTT';
select * from dba_roles;
用户名,权限,角色
在建立用户的时候,Oracle会把用户的信息存放到数据字典中,当给用户授予权限或是角色
的时候,oralce会将权限和角色的信息存放到数据字典
要查看Scott具有的角色 dba_role_privs;
如何查看一个角色包括的权限?
a, 一个角色包含的系统权限
select * from dba_sys_privs where grantee='CONNECT'
b, 一个角色包含的对象权限,connect resource dba都是角色
select * from role_sys_privs where role="CONNECT";
select * from dba_role_privs where grantee='SCOTT'
查询到scott有connect / resource 角色
表空间和数据文件:
表空间是数据库的逻辑组成部分,从物理上讲,数据库数据存放在数据文件中;
从逻辑上讲,数据库则是存放在表空间中,表空间由一个或是多个数据文件组成;
Oracle中的逻辑结构包括表空间,段,区和块.
说明一下数据库由表空间构成,而表空间又是由段构成,而段又是由区构成,而区又
是由oracle块构成的这样的一种结构,可以提高数据库的效率;
维护数据的完整性:
数据的完整性用于确保数据库数据遵从一定的商业和逻辑规则,在Oracle中,
数据完整性可以使用约束,触发器,应用程序(过程,函数)三种方法来实现,在
这三种方法中,因为约束易于维护,并且具有最好的性能,所以作为维护数据完整
性的首选;
约束用于确保数据库满足特定的商业规则,在Oracle中,约束包括:not null,unique,
primary key,foreign key 和check 五种;
not null(非空): 如果列上定义了not null,那么当插入数据的时候,必须为列提供数据;
unique唯一: 当定义了唯一约束后,该列是不能重复的,但是可以为null;
primary key主键:用于唯一标示表行的数据,当定义主键约束后,该列不但不鞥重复而且不能为
null.
需要说明的是: 一张表最多只能有一个主键,但是可以有多个unique约束:
foreign key外键:
用于定义主表和从表之间的关系,外键约束要定义在从表上,主表则具有主键约束或是unique约束
当定义外键约束后,要求外键列数据必须在主表的主键列存在或是为null
check 用于强制行数据必须满足的条件,假定在sal列上定义了check约束,并要求sal列值
在1000-2000之间如果不在其间就提示出错;
现有一个商店的数据库:记录客户及其购物情况,由三张表组成:
商品goods(商品号goodsId,商品名goodsName
单价unitprice,商品类别category,供应商provider)
客户customer(客户号customerId,姓名name,住宅address 电邮email
性别sex,身份证cardId)
购买purchase(客户号customerId,商品号goodsId,购买数量nums);
create table goods(goodId char(8) primary key,
goodsName varchar2(30),
unitprice number(10,2) check(unitprice>0),
category varchar2(8),
provider varchar2(30)
);
create table customer(customerId char(8) primary key,
name varchar2(20) not null,
address varchar2(50),
email varchar2(50) unique,
sex char(2) default '男' check(sex in('男','女')),
cardId char(18)
);
//列级定义不等于表级定义;
create table purchase(customerId char(8) references customer(customerId),
goodsId char(8) references goods(goodsId),
nums number(10) check(nums between 1 and 30));
如果在建表时候忘记了必要的约束,则可以在建表后使用alter table命令为表增加约束
但是要注意:增加not null约束时候,需要使用modify选项,而增加其他四种约束使用add
选项;
商品名不能为空:
alter table goods modify goodsName not null;
alter table customer add constraint cus_constraint unique(cardId);
alter table customer add constraint address_chk check(address in('东城','西城'));
删除约束
当不再需要某个约束时候,可以删除:
alter table 表名 drop constraint 约束名称;
特别声明:
在删除主键约束的时候,可能有错误,比如:
alter table 表名 drop primary key;
这是因为如果在两张表存在主从关系,那么删除主表的主键约束时候,必须带上cascade选项
alter table 表名 drop primary key cascade; //主动破坏住外键关系
显示约束信息:
select constraint_name,constraint_type,status,validated from user_constraints
where table_name='表名';
列级定义:
列级定义,列级定义是在定义列的同时定义约束
如在department表定义主键约束
create table department(dept_id number(2) costraint pk_department primary key,
name varchar2(12),
loc varchar2(12));
)
表级定义:
表级定义是指在定义了所有列后,再定义约束,这里需要注意:
not null约束只能在列级上定义.
以在建立employee2表时定义主键约束和外键约束为例:
create table employee2(
emp_id number(4),
name varchar2(15),
dept_id number(2),
constraint pk_emploee primary key(emp_id),
constraint fk_department foreign key (dept_id)
references department(dept_id)
);
管理索引:
介绍: 索引是用于加速数据存取的数据对象,合理的使用索引可以大大降低i/o次数
从而提高数据访问性能,索引有很多种我们主要介绍的几种:
为什么添加索引以后,会加快查询速度呢?
另外有个箱子里装卡片:
把书变成表,把箱子里的卡片变为索引
单列索引:
单列索引是基于当个列所建立的索引,比如:
create index 索引名 on 表名(列名)
复合索引:
符合索引是基于两列或是多列的索引,在同一张表上可以有多个索引,但是要求列的组合必须不同
create index emp_idex on emp(ename,job);
create index emp_idex on emp(job,ename);
create table goods(goodId char(8) primary key,
goodsName varchar2(30),
unitprice number(10,2) check(unitprice>0),
category varchar2(8),
provider varchar2(30)
);
create table customer(customerId char(8) primary key,
name varchar2(20) not null,
address varchar2(50),
email varchar2(50) unique,
sex char(2) default '男' check(sex in('男','女')),
cardId char(18)
);
//列级定义不等于表级定义;
create table purchase(customerId char(8) references customer(customerId),
goodsId char(8) references goods(goodsId),
nums number(10) check(nums between 1 and 30));
拿上面的表说事
select * from customer where name="??";
建立单列索引:
create index nameIndex on customer(name);
使用原则:
1):在大表上建立索引才有意义:(海量数据)
2):在where子句或是连接条件上经常引用的列上建立索引
3):索引的层次不要超过4层
索引缺点分析:
更新数据的时候,系统必须要占用额外的时间来同时对索引进行更新,以维持数据和索引
的一致性:
实践表明,不恰当的索引不但于事无补,反而会降低系统的性能,因为大量的索引在进行插入
修改和删除操作时比没有索引花费更多的系统时间;
介绍
按照数据存储方式,可以分为b树,反向索引,位图索引;
按照索引的个数分类,可以分为当列索引,复合索引;
按照索引列值的唯一性,可以分为唯一索引,非唯一索引;
此外还有函数索引,全局索引,分区索引----
注意:
在不同的情况下我们会在不同的列上建立索引,甚至不同种类的索引,甚至建立不同种类的索引
请记住,技术是死的,人是活的.比如:b树建立在重复值很少的列上,而位图索引建立在重复值很多
不同值相对固定的列上;
介绍:
这一部分我们主要看看oracle如何管理权限和角色,权限和角色的区别的那里:
当刚刚建立用户时,用户没有任何权限,也不能执行任何操作,如果要执行某种特定的数据库操作
如果要执行某种特定的数据库操作,则必须为其授予系统的权限,如果用户要访问其他方案的对象
则必须为其授予对象的权限,为了简化权限的管理,可以使用角色;
系统权限 对象权限 方案
oralce提供了25中角色:
可以自定义角色:
系统权限介绍:
系统权限是指执行特定类型sql命令的权利,它用于控制用户可以执行的一个或是一组数据库操作
比如当用户具有create table权限时候,可以在其方案中建表,当用户具有create any table权限时
可以在任何方案中建表,oralce提供了100多种系统权限;
常用的有:
create session
create table
create view
create public synonym
create procedure
create trigger
create cluster
带有with admin option选项时,这样,被授予权限的用户或是角色还可以将该系统权限授予其他的角色
或是用户
create user ken identified by ken;
grant create session,create table to ken with admin option;
grant create view to ken;
------------------------------------
对象权限介绍:
指访问其它方案对象的权利,用户可以直接访问自己方案的对象,但是如果要访问别的方案的对象
则必须具有对象的权限,比如smith用户要访问scott.emp表(scott:方案 emp表),则必须在scott.emp
表上具有对象的权限:
常用的有:
alter delete select insert update index references execute
grant select on emp to monkey //grant update on emp to monkey //grant delete on emp to monkey =grant all om emp to monkey;
oracle考虑得很周全:
可以让monkey只可以修改scott.emp的表的sal字段,怎样操作:
grant update on emp(sal) to monkey;
继续讲角色:
角色就是相关权限的命令集合,使用角色的主要目的就是为了简化权限的管理:
为了简化对权限的管理,oracle事先把一系列的权限集中在一起,打包给某个用户;
可以考虑使用自定义角色来解决问题:
预定义角色:
connect角色具有一般应用开发人员需要的大部分权限,当建立一个用户后,多数情况下,
只要给用户授予connect 和resource角色就够了,那么connect角色具有哪些系统权限呢?
alter session
create cluster
create database link
create session
create table
create view
create sequence
---------------------------- 创建角色 赋予角色
create role myrole not identified;
grant create session to myrole with admin option;
grant select on emp to myrole;
grant update on emp to myrole;
grant delete on emp to myrole;
grant myrole to along;
理解oracle的pl/sql概念
掌握pl/sql编程技术(包括编写过程,函数,触发器....)
pl/sql是什么: 过程化语言;
procedure language/sql 是oracle在标准的sql语言上的扩展,pl/sql不仅容许嵌入sql语言;
还可以定义变量和常量,容许使用条件语句和循环语句,容许使用例外处理各种错误,这使得它的
功能变得更加的强大;
java所具有的逻辑判断功能全部具有;
pl/sql是非常强大的数据库过程语言;
pl/sql编写的过程,函数可以在Java程序中调用;
学习必要性:
提高应用程序的运行性能
模块化的设计思想[分页的过程| 订单的过程|转账的过程--]
减少网络传输
写一个简单的存储过程:
1:创建一个简单表
2: 查看错误信息 show error;
SQL> create table mytest(name varchar2(30),passwd varchar2(30));
Table created
SQL> create or replace procedure sp_pro is
2 begin
3 insert into mytest values('rufus','m123');
4 end;
3:如何调用该过程
exec 过程名(参数值1,参数值2,....);
call 过程名(参数值1,参数值2,....);
exec sp_pro;
create or replace sp_pro2 is
begin
----执行部分
delete from mytest where name="韩顺平";
end;
/
----调用过程
exec sp_pro2;
开发人员使用pl/sql编写应用模块时候,不仅需要掌握sql语句的编写方法,
还要掌握pl/sql语句及语法规则,pl/sql编程可以使用变量和逻辑控制语句
从而可以编写非常有用的功能模块,而且使用pl/sql编程,可以轻松完成非
常复杂的查询;
简单过程 块(编程)--->过程/函数/触发器/包
标志符号的命名规范;
1):当定义变量时候,建议使用V_ 作为前缀 v_sal;
2):当定义常量时,建议使用c_ 作为前缀 c_rate;
4):当定义例外时,建议使用 e_作为前缀 e_error;
3):当定义游标时,建议用_cursor 作为后缀 emp_cursor;
pl/sql块
块block是pl/sql的基本程序单元,编写pl/sql程序实际上就是pl/sql块,
要完成相对简单的应用功能,可能只需要编写一个pl/sql块,但是如果要想
实现复杂的功能,可能需要在一个pl/sql块中嵌套其他的pl/sql块;
块结构示意图
pl/sql块由三个部分构成:定义部分,执行部分,例外处理部分
如下所示:
declear
/* 定义部分----定义常量,变量,游标,例外,复杂数据类型*/
begin
/*执行部分---要执行的pl/sql语句和sql语句*/
exception
/*例外处理部分---处理运行的各种错误*/
special
定义部分是从declare开始的
该部分是可选的
执行部分是从begin开始的
该部分是必须的
例外处理部分是从exceptin开始的
该部分是可选的
set serveroutput off---关闭输出选项
set serveroutput on ---打开输出选项
begin
dbms_output.put_line('hello');
end;
相关说明:
dbms_output是oracle所提供的包(类似Java的开发包),该包包含
一些过程,put_line就是dbms_output包的一个过程
declare
v_ename varchar2(5);
begin
select ename into v_ename from emp where empno=&no; //取出一个值赋值给变量
dbms_output.put_line('雇员名:'||v_ename);
end;
/
会提示对话框,要输入员工号.
declare
v_ename varchar2(5);
v_sal number(7,2);
begin
select ename,sal into v_ename,v_sal from emp where empno=&no;
dbms_output.put_line('雇员名:'||v_ename||'薪水是:'||v_sal);
end;
/
&表示要接受参数;
为了避免pl/sql程序的运行错误,提高pl/sql的健壮性,应该对可能的错误进行处理
相关说明,oracle事先预定义了一些例外,no_data_found就是找不到数据的例外;
declare
v_ename varchar2(5);
v_sal number(7,2);
begin
select ename,sal into v_ename,v_sal from emp where empno=&no;
dbms_output.put_line('雇员名:'||v_ename||'薪水是:'||v_sal);
exception
when no_data_found then
dbms_output.put_line('你的输入不存在');
end;
/
---------------------------------------------
过程:
过程用于执行特定的操作,当建立过程时候,既可以指定输入参数in
也可以指定输出参数out,通过在过程中使用输入参数,可以将数据
传递到执行部分,通过使用输出参数,可以将执行部分地数据传递到应
用环境,在sqlplus中可以使用create procedure 命令来建立过程
--编写一个过程只要指明其数据类型就可以了
create procedure ssp_proce (spName varchar2, newSal number) is
begin
------执行部分
update emp set sal=newSal where ename=spName;
end;
调用存储过程:
call ssp_proce('SCOTT','4562');
exec ssp_proce('SCOTT','4562');
如何在java程序中调用一个存储过程:
public staitc void main(String[] args){
try{
//加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//得到连接
Connection ct =DriverManger.getConnection("jdbc:oracle:thin:@localhost:152:1ORCL","scott","tiger");
//创建callableStatement;
CallableStatement cs =ct.prepareCall("{call ssp_proce(?,?)}");
cs.setString(1,"SMITH");
cs.setInt(2,10);
cs.execute();
cs.close();
ct.close();
}catch(Exception e){
e.printStackTrace();
}
}
/如何使用过程返回值.
函数
函数用于返回特定的数据,当建立函数时,在函数头部必须包含return子句,而在
函数体内必须包含return语句返回的数据,我们可以使用
create function 来建立函数
create function annual_income(name varchar2)
return number is
annual_salary number(7,2);
begin
select sal*12 +nvl(comm,0)*12 into annual_salary from emp where ename=name;
return annual_salary;
end;
/
在sqlplus中调用函数
Function created
SQL> var income number ///var定义object类型的变量
SQL> call annual_income('SCOTT') into:income;
Method called
income
---------
54744
同样我们可以在java程序中调用该函数
select annual_income('SCOTT') from dual;
rs.getInt(1);
包
包用于在逻辑上组合过程和函数,它由包规范和包体两部分组成
我们可以使用create package命令来创建包
create or replace package sp_package is
procedure update_sal(name varchar2,newsal number);
function annual_income(name varchar2) return number;
end;
包的规范只包含了过程和函数的说明,但是没有过程和函数的实现代码,包体
用于实现包规范中的过程和函数
建立包体可以使用create package body命令
create or replace package body sp_package is
procedure update_sal(name varchar2,newsal number)
is
begin
update emp set sal=newsal where ename=name;
end;
function annual_income(name varchar2)
return number is
annual_salary number;
begin
select sal*12+nvl(comm,0) into annual_salary from emp
where ename=name;
return annual_salary;
end;
end;
exe sp_package.update_sal('SCOTT',120
oracle视频学习笔记(二)
最新推荐文章于 2024-05-06 03:52:34 发布