Oracle基础及使用

oracle

启动

* 服务启动：oracle每个数据库是一个实例，每个实例对应一个服务，可以同时启动多个实例，需要在服务管理器中一个个开启。启动oracle至少要启动一个监听服务和一个数据库服务

登陆

* 运行输入sqlplusw或sqlplus打开sqlplus，

* 图形界面：oracle\ora92\bin\oemapp.bat console

三个oracle自动创建的用户：

sys：超级用户，最高权限，可以做任何操作。(初始密码:change_on_install)

system：管理员用户，可以操作除创建数据库外的其他操作，可以操作其他用户的对象。(初始密码:manager)

scott：一般用户，只能操作属于自己的对象。(初始密码:tiger)

登陆窗口中的"主机字符串"即实例名。

常用命令

登陆命令

show user;显示当前登陆用户名

conn system; 切换当前用户到system用户，要被要求输入密码。要切换到sys用户必须写完整：conn sys/tiger@myora1 as sysdba或者conn sys/tiger@myora1 as sysoper

其中tiger是密码，但在windows系统下作为sysdba可以用任何密码登陆。

SQL语句。

passw[ord];更改密码。

disc[connect];断开连接,如切换用户登陆不成功，也会断开连接。

desc 表名;查看表结构。

oracle数据库中的表、视图、存储过程、触发器等等都叫做数据对象。不同的用户登陆到同一个数据库实例中看到的数据对象是不一样的。

clear：清屏

文件操作

start [@]：运行一个脚本，例如 start d:\aa.sql

edit：编辑一个脚本，例如 edit d:\aa.sql

spool：将命令窗口中显示的内容放到文件中去。

例如：sql>spool d:\b.sql;

sql>select * from emp;

sql>spool off;

则会自动创建b.sql文件并把以上内容存到文件中。

交互式输入

用"&"符号：

例如：

select * from emp where ename='&name';

则执行时会提示用户输入一个值来替代&name.

设置环境变量

set linesize 50：设置显示行宽是50

set pagesize 10：每页显示10条（分页）

用户管理

创建用户

create user xiaoming identified by a123;

创建用户名是xiaoming密码是123的用户。（需要具有dba权限的用户才能使用该命令）。

给用户修改密码

给自己修改密码

password [用户名];

给别人修改密码

alter user 用户名 identified by 新密码;

需要具有dba权限或alter user权限才能修改别人的密码。

删除用户

drop user 用户名 [cascade];

如果删除的用户下有表，则需要加cascade级联删除。

角色权限

新创建的用户是无法登陆到数据库的，因为不具有登陆权限，需要首先为其赋权。

权限分为"系统权限"和"对象权限"，系统权限是对数据库的相关权限；对象权限是用户对其他用户的数据对象操作的权限。

oracle中系统权限有140多种，对象权限有25种。为了提高赋权效率，将多个权限整合到一起，创建一个角色，将角色赋给某个用户就相当于把角色中的权限赋给了用户。

角色分为"预定义角色"和"自定义角色"，预定义角色是oracle自动创建的，自定义角色是用户自己定义角色拥有的权限。例如连接数据库需要的7个权限就整合到了connect角色中。

角色之间可以有包含关系，例如dba拥有connect的所有权限。

* 授权预定义角色命令(授权系统权限)：

grant 角色名 to 用户名 [with admin option]; 授予角色

grant create session, create table to emp with admin option;授予系统权限

with admin option 被授权用户可以继续将此权限授权给其他用户.

例如：grant connect to xiaoming;

* 3个重要角色：

connect:登陆数据库

dba:管理员权限

resource:可以在任意表空间建表的权限。

* 授权对象权限

grant select on 表名 to用户名 [with grant option];(grant all on 表示将增删改查权限一起授权).

一个用户也可以叫一个方案，不同方案下面可以有相同的表名，将scott的emp表查询权限授权给xiaoming：grant select on emp to xiaoming;则小明可以查询到emp表，不过使用时表名前要加方案名.，如select * from scott.emp;

with grant option 被授权用户可以继续将此权限授权给其他用户。

* 收回权限

revoke select on 表名 from 用户名;

收回权限是的登陆用户必须是授权出去的用户。

回收权限时对象权限会级联回收，系统权限不会级联回收。

profile管理

profile是对口令、资源进行管理的命令集合。profile可以理解为一种规则。(dba角色才可操作)

例如：

口令加锁

sql> create profile aaa limit failed_login_attempts 3 password_lock_time 2;

创建规则aaa如果3此密码错误则将用户锁定2天。

sql> alter user scott profile lock_accout;

将规则aaa应用到scott用户上。

解锁

sql> alter user scott account unlock;

删除profile

drop profile aaa [cascade];

如果profile被删除，则受它约束的资源也解除约束。

表的管理

数据类型

字符型

char 定长，不足定长部分自动用空格补全。最长2000。char型字段查询效率很高。

varchar2 变长，最长4000

clob 字符型大对象，最大4G。

数字型

number 可以表示小数或整数，负的10的38次方到10的38次方。

number(5,2)表示小数，5位有效数字，其中2位小数。-999.99到999.99

number(5)表示5位整数。-99999到99999.

日期型

date 年月日时分秒

timestamp 时间戳

图片型

blob 可以存放图片、声音。一般情况很少把图片、声音等存放到数据库中，除非安全保密性很高才存到数据库中。

建表

create table student(

xh number(4),

xm varchar2(20),

sex char(2),

birthday date,

sal number(7,2)

);

修改表

添加字段：alter table student add(classid number(2));

修改字段(修改类型时不能有数据)：alter table student modify(xm char(30));

删除字段: alter table student drop column sal;

修改表名:rename student to stu;

删除表:drop table student;

添加数据

insert into student(xh, xm, sex, birthday, sal) values('a001', '张三', '男', '01-5月-05', null);

oracle默认日期格式'DD-MON-YY'，日期格式可修改,例如:

alter session set nls_date_format='yyyy-mm-dd';

可以使用to_date函数说明日期格式插入数据，例如：

insert into emp(empno, ename, hiredate, sal) values('9999', 'ZS', to_date('2002-02-03', 'yyyy-mm-dd'), 500);

当需要插入大量数据可以利用子查询：insert into ….select …..

修改数据

update student set sal=sal*2, classid='3' where sex='男';

修改数据时可以使用子查询同时更新多个字段，例如：

update emp set (job, mgr) = (select job, mgr from emp where ename='SMITH') where ename='ZS';

删除数据

delete from student where xh='A001';

记日志，可恢复。

truncate table student;

不记日志，不可恢复。

回滚

创建保存点：

savepoint aa;

回滚:

rollback to aa;

查询

单表查询

* 快速复制数据

insert into users(user_name, mima) select user_name,mima from users;

重复执行这个语句即可。

* 取消重复行：select dintinct …

* oracle中关键字不区分大小写，但数据内容是大小写区分的。

* 查询时列的别用可用as也可以不用，表的别名不能用as.

* 空值处理nvl(comm, 0),相当于sybase的isnull(comm, 0).

* 连接字符串用||，例如select empno||'is'||ename from emp;

* 模糊匹配：%代表0到多个字符,_代表1个字符

* 条件是等于特定几个值中的一个，用where …in (…)

* 结果排序，order by ，默认升序(asc), 降序用desc,也可以用列的别名排序。

* 聚合函数:max最大;min最小;avg平均;sum求和;count记录数量，聚合函数只能出现在select选择列或者order by中。

* 结果分组用group by,有group by时select后面有的字段除了聚合函数中的，其他都必须在group by后面出现。having 后面可以加条件对结果进行过滤。

* 子查询，返回多行结果的叫多行子查询，例如用在from后面代替一张表（也叫内嵌视图，必须取别名）；返回单行结果的叫单行子查询，例如用在where里面用查询结果代替一个常数，例如查询工资高于平均工资的员工 where sal>(select avg(sal) from emp);

* 多列子查询，例如查询和SMITH部门和岗位都相同的员工信息。

select * from emp where (deptno, job)=(select deptno, job from emp where ename='SMITH');

* all，所有的；any任意的：all相当于max+min，any同理，例如

select * from emp where sal > all(select sal from emp where deptno='30').使用all、any的查询效率很低，因为每个都要比较

* 优化效率：oracle在执行sql查询时是从后往前扫描，所以把能排除大量数据的条件放在后面可以提高效率。

多表查询

全连接实质是笛卡尔积。所以n个表关联查询至少要n-1个条件才可以。

自连接实质是把一个表看成两个表，查询方式一样。

分页

select查询时会返回一个隐含字段rownum（行号），可以利用行号大于或小于多少来指定显示第几到第几条记录，实现分页，但oracle的rownum只能用在小于多少，所以一次查询只能显示前多少条记录，可以将此结果作为一个子查询，再嵌套一个查询即可确定大于多少条。例如：

select * from (select a.*,rownum rn from emp a where rownum<=10) where rn>=6;

显示第6到第10条记录。

这个语句可以作为公式使用：

sql的改动如指定查询列、排序等只用改最里层的视图。

用查询结果创建表

create table mytable(name, sal comm)

as select ename, sal, comm from emp;

字段类型和emp中对应的一致。导数据时常用。

合并查询结果union、union all、intersect、minus

union,去掉重复行。

union all，不去掉重复行。
intersect,取交集.

minus,取差集。

使用这种操作比and、or等语句的效率高很多。

java连接oracle

1、通过odbc桥连接（不需要拷贝驱动包），只能连接本机。

Connection con = null;

try {

//1、加载驱动

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

//2、得到连接

Connection con = DriverManager.getConnection("jdbc:odbc:myora", "scott", "tiger");

//3、以下同其他数据库一样

st = con.createStatement();

rs = st.executeQuery("select * from (select a.*,rownum rn from emp a where rownum<=10) where rn>=6");

for(;rs.next();) {

System.out.println(rs.getString(2));

}

rs.close();

st.close();

con.close();

}catch(Exception e) {

e.printStackTrace();

}

其中myora是odbc数据源的名字。

2、通过jdbc连接（需要拷贝oracle驱动包"classes12.jar"），可以远程连接

//1、加载驱动

Class.forName("oracle.jdbc.driver.OracleDriver");

//2、得到连接

Connection con =

DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:myora1", "scott", "tiger");

jsp页面中oracle分页

try {

//1、加载驱动

Class.forName("oracle.jdbc.driver.OracleDriver");

//2、得到连接

con = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:myora1", "scott", "tiger");

//3、以下同其他数据库一样

st = con.createStatement();

int pageCount=0;//页数

int rowCount=0;//记录条数

int pageSize=3;//每页显示条数

int rowNum=1;//第几页

rs = st.executeQuery("select count(1) FROM emp");

rs.next();

rowCount = rs.getInt(1);

pageCount = (rowCount + pageSize - 1 )/pageSize;

for(int i=1; i<=pageCount; i++) {

out.print("<a href=index.jsp?rowNum=" + i +"> "+ i +" </a>");

}//创建选择页的超链接，将页码作为参数带入。

rowNum = Integer.parseInt(request.getParameter("rowNum"));

rs = st.executeQuery

("select * from (select a.*,rownum rn from emp a where rownum<=" + rowNum * pageSize + ") where rn>=" + (pageSize * (rowNum-1) + 1)); //根据传入的页码参数分页。

for(;rs.next();) {

out.print("<tr>");

out.print("<td>");

out.print(rs.getString(2));

out.print("</td>");

out.print("<td>");

out.print(rs.getString(6));

out.print("</td>");

out.print("</tr>");

}

rs.close();

st.close();

con.close();

}catch(Exception e) {

e.printStackTrace();

}

页数的算法：页数=(记录条数 + 每页显示条数 -1)/每页显示条数

事务

事务概念

oracle会在事务作用的表上自动加锁。

创建保存点：

savepoint a1;

回滚:

rollback to a1;回滚到a1，只能向前回滚不能向后回滚，例如先创建a1，在创建a2，则回滚到a1之后不能再回滚到a2.

rollback;回滚到最开始的保存点。

如果执行了commit则会立刻删除所有保存点，在关闭命令窗口时会自动执行commit.

只读事务

如果一个用户执行了命令：set transaction read only;则他会保存数据库数据现在状态，即使其他用户有修改，取的也是设置点的数据。例如机票代收点统计某一时间点的机票信息就需要用到只读事务。

java中使用事务

将Connection的autoCommit设置为false，然后手动提交，con.commit();,异常时手动回滚,

con.rollback();

Connection con = null;

try {

//1、加载驱动

Class.forName("oracle.jdbc.driver.OracleDriver");

//2、得到连接

con = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:myora1", "scott", "tiger");

//3、以下同其他数据库一样

Statement st = con.createStatement();

con.setAutoCommit(false);//关闭自动提交

st.executeUpdate("update emp set sal=30000 where ename='ZS'");

con.commit();//手动提交事务

st.close();

con.close();

}

catch(Exception e) {

try {

con.rollback();//手动回滚事务

} catch (SQLException e1) {

// TODO 自动生成 catch 块

e1.printStackTrace();

}

e.printStackTrace();

}

函数

数学函数

upper(char),转换为大写

lower(char),转换为小写

length(char),取长度

substr(char, m, n)取子串，取从第m位开始的n个字符。

replace(char, char1, char2)将char中的所有char1字符用char2替换。

round(n, [m])将n保留m位小数四舍五入，默认取整数。m可为负数，-1表示保留到10位，等等。

turnc(n, [m])将n保留m位后面全部舍掉。

mod(n, m)取余数,n/m的.

floor(向下取整)

ceil(向上取整)

亚元表

亚元表dual 可以作为函数测试，例如：

select floor(3.5) from dual;

日期函数

sysdate用于返回系统时间，例如select sysdate from dual;

add_months(d, n)返回日期d加上n个月的日期。

例如查询入职8个月以上的员工：

select * from emp where sysdate > add_months(hiredate, 8);

两个日期型相减得到的是天数。

last_day(d)，返回日期d当月的最后一天。

转换函数

默认情况下，oracle会自动进行数据类型转换，例如数字和字符的转换，所以将常量都加一个单引号就不容易出错。

to_char函数可以按指定格式转换字符串、日期等。

日期转换

select to_char(hiredate, 'yyyy-mm-dd hh24:mi:ss') from emp;

yyyy:年

mm:月

dd:日

hh24(12):小时(24小时或12小时)

mi:分钟

ss:秒

如果添加日期时未指定时分秒，则时分秒默认都为0.

select to_char(hiredate, 'yyyy') from emp;可以取出年份

select to_char(hiredate, 'mm') from emp;可以取出月份

货币转换

select to_char(sal, 'L99999.99') from emp;

L代表本地货币符号(local),如RMB，也可以写$或其他货币符号

9代表数字，如果是0则不足的位用0补齐。

系统函数

select sys_context('userenv', 'db_name') from dual;查询当前连接的数据库名。

select sys_context('userenv', 'language') from dual;查询当前语言

select sys_context('userenv', 'session_user') from dual;查询当前连接用户

select sys_context('userenv', 'current_schema') from dual; 查询当前方案名

数据库管理

主要角色sysdba、sysoper、dba

sys用户具有3个角色：dba(数据库管理员)、sysdba(系统管理员)、sysoper(系统操作员)。

system具有2个角色：dba(数据库管理员)、sysdba(系统管理员)

sysdba是最高权限的角色、sysoper次之、dba再次。

system实质上和sys拥有的权限一样，因为sysoper拥有的权限sysdba都拥有。

sysdba可以创建和删除数据库、改变字符集，登陆之后用户是sys。

sysoper不能进行以上操作，且登陆之后用户是public.

dba不能启动或关闭数据库。

关闭数据库：shutdown;

启动数据库：startup;执行了shutdown之后只要不断开连接，就可以执行startup.

数据库导出

导出表

* 导出表命令，操作系统命令行窗口中执行：

exp userid=scott/tiger@myora1 tables=(emp,dept) file=d:\scott.dmp

如果是其他方案的表，表名前加"方案名.".如果导出多个表，则多个表都放到了一个文件中。

* 导出表结构，只需在导出表命令后面加上 rows=n

exp userid=scott/tiger@myora1 tables=(emp,dept) file=d:\scott.dmp rows=n

* 快速导出表，在导出表的语句后加direct=y

可以加快导出速度，前提是数据库的字符集要和客户端的一致，否则会报错。

scott.dmp中可能存在多个表，但只要存在需要导入的表，即可导入。scott.dmp中记录了导出时的用户(即其中的每个表的所有者)，如果导入的用户和导出的用户不同，会有提示，不影响导入，但如果原来的表有外键关系，则很容易出错。

如果方案中已经存在有该表，则导入不成功，不会覆盖已有表。

* 导入表结构，只需在导入表命令后面加上 rows=n

exp userid=scott/tiger@myora1 tables=(emp,dept) file=d:\scott.dmp rows=n

* 导入数据，只需在导入表命令后面加上 ignore=y

exp userid=scott/tiger@myora1 tables=(emp,dept) file=d:\scott.dmp ignore=y

相当于创建表，然后insert数据，实质是导入表，只是如果数据库中如果已有该表则会跳过错误而继续导入数据，如果没有该表就创建表再导入数据。

数据字典

数据字典记录数据库的系统信息，包括只读基表和视图，所有者是sys用户。一般用户只能查询数据字典视图，而不能访问基表。

数据字典分为3类：

user_XXX：该用户下的对象，例如

select table_name from user_tables;返回该用户方案下的所有表。

all_XXX：该用户能访问到的对象，例如其他用户对其授权的对象,如：

select table_name from all_tables;

dba_XXX:所有方案下的对象，只有dba角色的用户才可查看

select table_name from dba_tables;

system用户的all_tables和dba_tables是相同的。

用户权限、角色查询

当用户创建时，oracle会将用户的权限、角色信息放到数据字典中：

select * from dba_users;返回所有用户信息。

dba_sys_privs：所有用户都拥有哪些系统权限

user_sys_privs：当前用户拥有的系统权限

dba_tab_privs:：所有用户都拥有哪些对象权限

user_tab_privs：当前用户拥有的对象权限

dba_col_privs:：所有用户都拥有哪些列权限

user_col_privs：当前用户拥有的列权限

dba_role_privs:：所有用户都拥有哪些角色

user_role_privs：当前用户拥有的角色

重要查询

* 查询所有系统权限：

select * from system_privilege_map order by name;

* 查询所有角色：

select * from dba_roles;

* 查询所有对象权限：

select distinct privilege from dba_tab_privs;

* 查询数据库的表空间：

select tablespace_name from dba_tablespaces;

select * from dba_sys_privs where grantee='CONNECT';查询CONNECT'角色拥有的系统权限。

select * from dba_sys_privs where grantee='DBA';查询DBA角色拥有的系统权限。

select * from dba_tab_privs where grantee='DBA';查询DBA角色拥有的对象权限。

也可以这样：select * from role_sys_privs where role='DBA', 还可以通过plsql查看

查询用户拥有哪些角色：

select * from dba_role_privs where grantee='用户名'

查询当前用户可以访问的所有数据字典视图：

select * from dict where comments like '%grant%';

查询当前数据库的全称：

select * from global_name;

表空间管理

oracle中的数据对象(表、视图、存储过程、触发器等)物理上存放于数据文件中，逻辑上则存放在表空间中，一个数据库会有很多个表空间。

表空间由"段"构成、段由"区"构成、区由"块"构成。

表空间的作用：

1、控制数据库占用的磁盘空间。

2、dba可以将不同数据类型部署到不同表空间，提高i/o效率，有利于备份、恢复等操作。

维护数据完整性

数据完整性维护有三种方式：约束、触发器、应用程序（过程、函数）

约束

约束概述

not null、unique、primary key、foreign key、check五种。

其中unique和foreign可以为空。

使用方法：

create table goods(goodsId char(8) primary key,--主键

goodsName varchar(30),

unitprice number(10,2) check (unitprice > 0),

category varchar2(8),

provider varchar2(30));

create table customer(customerId char(8) primary key,--主键

name varchar2(50) 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));

可以在定义约束的同时为约束取名字(也叫列级定义)(如果没有名字则oracle自动为其生产一个)例如

goodsId char(8) constraint pk_goodsId primary key

name varchar2(50) constraint not_null_name not null

check、foreignkey、unique同理。

也可以在定义完字段以后增加约束定义(也叫表级定义)：如：

constraint pk_goodsId primary key(goodsId)

增加约束

not null：not null约束不用增加约束名称。

alter table goods modify goodsName not null;

unique:需要增加约束名称

alter table customer add constraint customer_cardid_unique unique(cardId);

check: 需要增加约束名称

alter table customer add constraint customer_address_check check(address in ('北京', '北极'));

primary: 需要增加约束名称

alter table emp add constraint pk_emp primary key(empno);

删除约束

alter table 表名 drop constraint 约束名;

删除主键约束：

alter table 表名 drop primary key [cascade];如果存在外键约束，则用cascade可以级联删除外键。

not null不需要删除约束，直接修改字段为null或not null即可

查询约束

可以在plsql中查询，也可以用命令：

查询一张表的约束

select * from user_constraints where table_name = 'GOODS';

查询约束列

select * from user_cons_columns where constraint_name='约束名';

管理索引

创建索引

创建单一索引：

create index ind_emp on emp(ename);

创建复合索引

create index ind_emp1 on emp(empno, ename);

oracle会为unique字段自动增加索引。

查询索引

可以在plsql中查，也可以用语句：

select * from user_indexs where table_name='表名';

查询索引列

select * from user_ind_columns where index_name='索引名';

PL/SQL

PL/SQL是oracle在标准sql上的扩展(procedual language/sql),可以定义变量，使用条件、循环语句，功能更强大。

开发工具：

sqlplus(oracle自带)、pl/sql developer(第三方工具)

例外："e_"开头

游标："_cursor"结尾

块的结构

块由定义部分、执行部分、例外处理部分组成，其中begin部分是必须的，declear和exception部分是可选的。

如下：

declare
/*定义部分，定义变量、常量、游标、例外、复杂数据类型等*/
begin
/*执行部分*/
exeception
/*例外处理部分*/
end;
/--结尾要加"/"

块的简单实例

实例1：只含执行部分

set serveroutput on;--打开输出选项，可以输出字符。
begin
dbms_output.put_line('hello');
end;
/
执行以后会输出hello,其中dbms_output是oracle自带的一个包，put_line是这个包中的一个存储过程。

实例2：包含定义部分

declare
v_ename varchar2(30);
v_sal number(7,2);
begin
select ename, sal into v_ename, v_sal from emp where empno=&aa;
dbms_output.put_line('雇员是:'||v_ename);
dbms_output.put_line('工资是:'||v_sal);
end;
/
其中into v_ename, v_sal表示将查询结果存入变量v_ename, v_sal中；

&aa表示让用户输入一个参数，会弹出输入框。

实例3：包含异常部分

declare
v_ename varchar2(30);
v_sal number(7,2);
begin
select ename, sal into v_ename, v_sal from emp where empno=&aa;
dbms_output.put_line('雇员是:'||v_ename);
dbms_output.put_line('工资是:'||v_sal);
exception
when no_data_found then
dbms_output.put_line('输入错误了！！！');
end;
/
其中no_data_found是异常名字，表示没有查询到数据。
实例3：带参数存储过程

create procedure alter_sal(kkname varchar2, newSal number) is
begin
update emp set sal=newSal where ename=kkname;
end;
/

变量

plsql变量有标量类型、复合类型、参照类型、lob类型4中，其中前三种常用。

标量类型

常量一般以c_开头，变量以v_开头

字符串如：

v_ename varchar2(30);

v_ename emp.ename%type;

和表emp的ename字段类型一致。

赋值用:=,如：

v_age number(5):=20

定义变量可以定义初始值和一些约束，例如定义一个布尔变量，非空，初始值false

v_valid boolean not null default false;

复合类型

复合类型包括pl/sql记录、pl/sql表等。

1、pl/sql记录相当于结构体或类，就是标量的组合。需要首先定义一个类型(理解为定义一个类)，然后把该类型赋给一个变量(定义该类的对象)。

使用方法：

declare
type v_emp_record is record(
v_ename emp.ename%type,
v_sal emp.sal%type
);
v_emp1 v_emp_record;
begin
select ename, sal into v_emp1 from emp where empno=&aa;
dbms_output.put_line(v_emp1.v_ename||v_emp1.v_sal);
end;
/
其中首先定义了v_emp_record类型，然后将其赋给v_emp1,引用其中元素时用".".

2、pl/sql表相当于数组，使用方法：

declare
type enametype is table of varchar2(50) index by binary_integer;
v_ename enametype;
begin
select ename into v_ename(-1) from emp where empno=7788;
dbms_output.put_line(v_ename(-1));
end;
/
其中定义了一个类型enametype，想当于定义了一个数组(大写没有限制，动态的)，定义了数组元素的类型是varchar2(50).然后定义了一个具体的数组v_ename，index by binary_integer表示数组下标是整数(可以为负)。v_ename(-1)表示下标是-1的元素。

参照类型(游标)

参照类型主要是游标，前面两种类型所能接收的记录只能是一条，如果返回多条记录就需要用游标，游标实质是一个指针通过移动指针来访问返回的每一条记录。使用方法：

--定义游标
type kk_cursor1 is ref cursor;
emp_cursor1 kk_cursor1;
--定义变量
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
open emp_cursor1 for select ename, sal from emp;
loop
fetch emp_cursor1 into v_ename, v_sal;
dbms_output.put_line('用户名:'||v_ename||'工资'||v_sal);
exit when emp_cursor1%notfound;
end loop;
close emp_cursor1;
end;
/
定义方法和复合类型类似，kk_cursor1表示游标类型，emp_cursor1是一个具体的游标。

open emp_cursor1 for select ename, sal from emp;表示将游标emp_cursor1指向一个select语句。

fetch emp_cursor1 into v_ename, v_sal;表示将游标emp_cursor1指向的当前记录的值存放到v_ename、v_sal。

exit when emp_cursor1%notfound;是一个固定写法，如果游标移动到了最后的一条记录的后面(即没有记录对应)，则退出循环。

close emp_cursor1;关闭游标。

游标刚指向一个select语句时处于第一条记录前面，执行一次fetch就向下移动一条，直到移动到最后一条记录之后(类似于java的ResultSet)。

条件语句

1、if then endif;

2、if then else endif;

3、if then elsif then else endif;

示例：编写一个块，输入员工姓名，如果该员工职位是PRESIDENT就将工资增加3000，如果职位是MANAGER就将工资增加2000，其他职位增加1000.

declare
v_job emp.job%type;
kkname emp.ename%type:='&&kname';
begin
select job into v_job from emp where ename=kkname;
if v_job='PRESIDENT'
then
update emp set sal=sal+3000 where ename=kkname;
elsif v_job='MANAGER'
then
update emp set sal=sal+2000 where ename=kkname;
else
update emp set sal=sal+1000 where ename=kkname;
end if;
end;
/

如果条件是不等于用"<>",而不是"!="。

declare
i number:=1;
begin
loop
dbms_output.put_line(i);
i:=i+1;
if i>10 then goto end_loop;
end if;
end loop;
<<end_loop>>
dbms_output.put_line('over');
end;
/

end_loop就是标号的label,用<<end_loop>>表示。

null语句

null语句不做任何操作，目的是增强可读性。

示例：

declare
kname varchar2(50):='&&k_name';
begin
if kname='kkk' then
dbms_output.put_line('回答正确，答案就是'||kname);
else
null;
end if;
end;
/

Exception处理

Oracle中Exception(例外)分为预定义例外、非预定义例外和自定义例外3种。

预定义例外

预定义例外处理常见的错误。例如：

when no_data_found then
dbms_output.put_line('输入错误了！！！');
end;

pl/sql共有21个预定义例外，常见的有：

case_not_found：case语句中，找不到case对应的when条件分支时触发。

cursor_already_open：打开已经打开的游标时触发。

dup_val_on_index：在唯一索引列插入重复值时触发。

invalid_cursor：试图在不合法的游标执行操作时触发，例如从没有打开的游标提取数据，关闭没有打开的游标等。

invalid_number：输入数字不合法时触发。

no_data_found：没有找到记录。

too_many_rows：例如返回结果是多条记录赋值给一个一般变量时触发。

zero_divide：除数为0.

value_error：例如变量长度比实际数据小，赋值给该变量就会触发这个异常。

login_denide：非法登陆

not_logged_on：未登陆

非预定义例外(很少用)

非预定义例外用于处理其他的一些oracle错误，例如dml语句违反约束规定等。

自定义例外

自定义例外是用户自己定义的例外，例如在用update语句更新数据时，如果没有找到记录oracle不会报错，而用户希望返回信息，可以将其定义成一种例外：

declare
myex exception;--定义例外类型
begin
update emp set sal=6666 where empno=&no;
if sql%notfound then--固定写法，表示上面的语句没有找到记录
raise myex;--抛出意外
end if;
exception
when myex then--捕捉意外
dbms_output.put_line('kk未找到kk数据');
end;
/

存储过程

创建存储过程

create [or replace] procedure 存储过程名(参数类型, 参数类型,……) is

带有or replace表示如果有就替换原存储过程。参数只用写类型，不用写长度。

定义部分不用写declare

例如：
create or replace procedure kk_pro1(a1 number) is
ename1 varchar2(50);
begin
select ename into ename1 from emp where empno=a1;
dbms_output.put_line(ename1);
end;

执行存储过程

exec 存储过程名;

例如：exec kk_pro1(7788);

如果执行过程有错误，执行show error;即可查看错误。

java中调用存储过程

try {

// 1、加载驱动

Class.forName("oracle.jdbc.driver.OracleDriver");

// 2、得到连接

Connection con = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:myora1", "scott", "tiger");

// 3、创建CallableStatement

CallableStatement cs = con.prepareCall("{call alter_sal(?,?)}");

// 4、设置参数

cs.setString(1, "SMITH");

} catch (Exception e) {

// TODO 自动生成 catch 块

e.printStackTrace();

}

有返回值的存储过程

(1)返回一条记录的存储过程：

存储过程的参数有in和out，in表示传入参数，out表示传出参数，不写默认为in。

使用方法：

根据员工编号查询姓名和工资。

create or replace procedure kk_pro5(ao_empno in number, ao_ename out varchar2, ao_sal out number) is
begin
select ename, sal into ao_ename, ao_sal from emp where empno=ao_empno;
end;
/

将返回员工名字和工资，一般不会在plsql中调用(Oracle存储过程本身没有返回值，而是通过参数返回的)，而在java程序中调用，java中取得参数：

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection con = DriverManager.getConnection

("jdbc:oracle:thin:@127.0.0.1:1521:myora2", "scott", "tiger");

CallableStatement cs = con.prepareCall("{call kk_pro5(?, ?, ?)}");

cs.setInt(1, 7788);

cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);

cs.registerOutParameter(3, oracle.jdbc.OracleTypes.DOUBLE);

cs.execute();

String ename = cs.getString(2);

double sal = cs.getDouble(3);

System.out.println("名字："+ename);

System.out.println("工资："+sal);

cs.close();

con.close();

} catch (Exception e) {

1、其中cs.setInt(1, 7788);表示设置第一个参数值。

2、返回值必须全部注册，否则会报错：

cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);

注册时需要用oracle提供的类型。

3、String ename = cs.getString(2);表示根据下标取出返回值。

(2)返回多条记录(结果集)的存储过程

返回多条记录的存储过程不能用一个普通变量来接收，而要用游标，返回的是一个游标，java中用ResultSet来接收游标，就可以循环遍历了。

方法：

1、首先要定义一个游标类型，这个游标类型由于需要在传出参数中声明，所以不能定义在存储过程内，而要定义在一个包里：

create package kk_pac2 as
type kk_cursor is ref cursor;
end kk_pac2;
/

和一般的包不同，这里用的是as，并且后面是end kk_pac2。这样就定义了一个包，里面包含一个游标类型kk_cursor.

2、定义存储过程，将传出参数声明为刚才定义的游标类型kk_cursor.

create procedure kk_pro7(kno in number, kcursor out kk_pac2.kk_cursor) is
begin
open kcursor for select * from emp where empno=kno;
end;
/

其中kk_pac2.kk_cursor表示kcursor的类型是刚才定义的游标类型。

3、java中，传出参数注册为oracle.jdbc.OracleTypes.CURSOR：

cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);

cs.execute();

ResultSet rs = (ResultSet) cs.getObject(2);

分页存储过程

分页就是限定取出查询出记录的多少条到多少条，实质就是一个限制条件，可以将其认为是一个查询条件，传入查询条件，返回结果。例如传入表名、每页条数、第几页和其他查询条件等然后返回记录数、页数、结果集等。

create or replace procedure kk_fenye1(k_tablename in varchar2, k_pagesize in number, k_pagenow in number, k_rowcount out number, k_pagecount out number, k_cursor out kk_pac2.kk_cursor) is
beginrow number;--开始记录位置
endrow number;--末尾记录位置
sqls varchar2(1000);--查询语句
begin
--由于表名作为参数传入，而from后面的表名不能用参数，所以需要定义一个sql语句，并且用execute immediate 取得返回值。
sqls:='select count(1) from '||k_tablename;
execute immediate sqls into k_rowcount;
k_pagecount:=trunc((k_rowcount+k_pagesize-1)/k_pagesize);
beginrow:=(k_pagenow-1)*k_pagesize+1;
endrow:=k_pagenow*k_pagesize;
sqls:='select * from (select a.*, rownum rn from '|| k_tablename ||' a where rownum<='|| endrow ||') where rn >='|| beginrow;
open k_cursor for sqls;
end;
/

其中参数：

k_tablename:所要查询的表名。
k_pagesize:每页记录数

execute immediate sqls into k_rowcount;表示立即执行sqls语句并将结果放到k_rowcount.

k_pagecount:=trunc((k_rowcount+k_pagesize-1)/k_pagesize);是分页的页数算法。

open k_cursor for sqls;打开游标，此处不能关闭游标，否则java会查不到数据，提示游标已关闭。

函数

函数必须有返回值，定义方法：

create or replace function fun_kk1(empno1 varchar2) return varchar2 is ename1 varchar2(50);
begin
select ename into ename1 from emp where empno=empno1;
return ename1;
end;
/

使用时：

SQL> var a varchar2;

SQL> call fun_kk1(7788) into:a;

create or replace package body kk_pac1 is
procedure pro1 is
begin
dbms_output.put_line('pro1');
end;
function fun1 return varchar2 is op1 varchar(50);
begin
select ename into op1 from emp where empno=7788;
return op1;
end;
end;
/

包体名字必须和包规范名字一致，表示对应关系，且包规范的声明和包体的内容也要一致。

需要首先定义包规范再定义包体，否则直接定义包体会报错。

刷新页面返回顶部

启动

登陆

常用命令

登陆命令

文件操作

交互式输入

设置环境变量

用户管理

创建用户

给用户修改密码

删除用户

角色权限

profile管理

口令加锁

解锁

删除profile

表的管理

数据类型

字符型

数字型

日期型

图片型

建表

修改表

添加数据

修改数据

删除数据

回滚

查询

单表查询

多表查询

分页

用查询结果创建表

合并查询结果union、union all、intersect、minus

java连接oracle

jsp页面中oracle分页

事务

事务概念

只读事务

java中使用事务

函数

数学函数

亚元表

日期函数

转换函数

日期转换

货币转换

系统函数

公告