8.命令
1. show user 显示当前用户
2. 连接命令 conn[ect] 用户名/密码 网络服务器名[as sysdba/sysoper] 当用特权用户登录连接时，必须带上 as sysdba 或是 as sysoper
3. 断开数据库连接 disc[onnect]
4. 修改用户的密码，如果想修改其他用户的密码，需要用 sys/system登录
5. 断开与数据库的连接，exit 同时退出 sqlplus
6. 文件操作命令
1. 运行sql脚本 start 案例: start/@ d:/a.sql
2. 编辑指定的sql脚本 edit 案例: edit d:/a.sql
3. 将sql屏幕上的内容输出到指定文件命令中去 案例: d:/b.sql 并输入spool off
7. 显示和设置环境变量
1. 设置显示行的宽度，默认是80个字符 linesize 案例: set linesize 120
2. 设置每页显示的行数目，默认是14 pagesize 案例: set pagesize 5
8. Oracle 中默认的日期格式'DD-MON-YY' dd 日子 mon 月份 yy 两位的年 '09-6月-99' 1999年6月9号 改日期的默认格式 alter session set nls_date_format='yyyy-mm-dd'
9. clear 清屏
10. set timing on 显示执行时间
11. show paramcter 显示初始化参数
12. shutdown 关闭数据库?
13. shartup 启动数据库?
9. 用户管理
1. 创建 create user 用户名 identified by 密码（只能以字母开头而且普通用户不能创建） 还有一种 企业管理器-->安全-->用户-->创建
2. 修改用户密码 alter user 用户名 identified by 新密码; 案例：alter user scott identified by tiger;
3. 删除用户（不能删自己）drop drop user 用户名[cascade] 说明如果已经在用户下创建了一个表，删除时需带一个参数[cascade]
4. 给用户赋权限 案例: grant 角色名 to 用户名
10. 权限
1. 系统权限（用户对数据库的相关权限 比如:建库，建表，建索引，登录数据库，修改密码）
1. 授予系统权限 grant create session,create view,create table... to 用户名
2. 收回系统权限 revoke create session,create view,create table... from 用户名 谁授权谁回收
2. 对象权限（用户对其他用户的数据对象操作权限）说明 自己的表也可以授权给别人
1. all 比如 grant all on 表名 to 用户名 收回权限 案例 revoke select on 表名 from 用户名 说明
2. insert
3. update
4. select
5. delete
6. index
3. 对列的控制 grant update on 表名(字段...)to 用户名
11. 角色(批量权限)
1. 预定义角色
1. dba角色 数据库管理员（没有启动和关闭数据库权限）
1. connect角色 授角色 grant connect to 用户名
3. resource:在表空间可以创建存储过程、触发器,建表
4. SYSDBA 系统管理员
5. SYSOPER 系统操作员
2. 自定义角色
1. 创建一个角色 create role 角色名 not identified(不需要验证)
cteate role 角色名 identified by 密码（需要验证）
2. 给角色授权 grant create session,create view,create table... to 角色名 说明: 和上面的权限授权和回收差不多
3. 删除角色 drop role 角色名 说明: 级联
12. 方案
方案名称和用户名称一致（一个用户对应一个方案）
方案包括（数据对象[表，视图，存储器...]）
查询其他用户的表 比如: select *from 其他用户名.表名 一个用户对应一个方案
13. 权限的维护（权限的传替）
1. 如果是对象权限，就加入 with grant option 案例: grant select on 表名 to 用户名 with grant option 说明: 回收时级联
2. 系统权限 案例: grant connect to 用户名 with admin option 说明: 回收时不级联
14. 用户的管理
1. 账户锁定 create profile 规则名称 limit failed_login_attempts 尝试数字 password_lock_time 锁定天数 案例: create profile aa limit failed_login_attempts 3 password_lock.time 2——alter user 用户名 profile aa;
2. 账户解锁 alter user 用户名 account unlock;
3. 终止口令 create profile 规则名称 limit password_life_time 每隔多少天数 password_grance_time 宽限天数 案例: create profile bb limit password_life_time 10 password_grance_time 2——alter user 用户名 profile bb
4. 口令历史 create profile 规则名称 limit password_life_time 每隔多少天数 password_grance_time 宽限天数 password_reuse_time 天数
5. 删除profile drop profile 规则名称 [cascade]
15. 数据类型
1、字符:
varchar2:最大4000，长度可变，节省空间
char:最大2000节节，长度恒定,不够用空格补之，速度快
char(10)
long:2G,数据库中只能有一long型字段，不可做索引
不可做主键
2、数字:
number(5,2):一共5位，2位小数，范围 -999.99-999.99
int
3、日期
date:年月日时分钞
4、字节
row:字节(2000)
long row:2g
5、大文本
clob:4g,一个表可以有多个字段,存字符
blob:4g,一个表可以有多个字段,存字节
bfile:是一个指针，指向外部的一个文件，文件并没有存储
在数据库中
16. 建表
1. 命名规范
必须以字母开头
长度不能超过30个字符
不能使用 Oracle 的保留字
只能使用如下字符 A-Z,a-z,0-9,$,#等
drop table buy;
create table buy
(
buyID int primary key,
buyName varchar2(20),
price number(10,2),
amount number(10,2),
customerName varchar2(20),
constraint buy_cusomer_fk foreign key (customerName) references customer(customerName)
);
17. 表[结构]
1. 添加一个字段 alter table 表名 add (字段 类型);
2. 修改字段的长度/类型 alter table 表名 modify(字段 类型);
3. 删除一个字段 alter table 表名 drop column 字段
4. 修改表的名字 rename 原表名 to 修改后的表名
5. 查看表结构 desc 表名
6. 删除表 drop table 表名
7. 删除表所有数据 truncate table student;(速度快，不写日志)
18. 表数据
insert into buy values(6,'buyName6',70.2,120,'a2');
insert into student values('A001','张三','男','01-5月-1995',10)
--添加空值 insert into student(xh,sm,sex,birthday) values(3,'aa','女',null)
--sql注意:
一句话结束用/或;，官方认何的/
--基于老表创建新表并加入数据
create table customer1 as select * from customer;
--只要结构
create table customer2 as select * from customer where 1=2;
--批量插入
insert into customer2(customerName,sex)
select customerName,sex from customer;
--回滚
savepoint a（保存点）;——sql语句——rollback to a;(回滚到了a点时的状态)
--select的基本语法
select 列 from 表
[where 条件]:原始的行
[group by 字段]
[having 条件]:通分组的结果计算
[order by 字段]
--查buyid在1到3之前的数据
--and
select * from buy where buyID>=1 and buyID<=3;
--between
select * from buy where buyID between 1 and 3;
--or
select * from buy where buyID=1 or buyID=3 or buyID=2;
--in
select * from buy where buyID in(1,2,3);
--rowNum,代表前三行
select * from buy where rowNum<=3;
--like
%:0-n个字符
_:任意一个字符
[1-9]:一个范围
select * from buy where buyName like 'buyName_';
select * from buy where buyName like 'buy%';
--空
--取空值
select * from buy where customerName is null;
--取不为空的值
select * from buy where customerName is not null;
--distinct(去除重复项)
select distinct 字段,字段 from 表名
--关联
select c.*,b.* from customer c join buy b
on c.customerName=b.customerName;
select c.*,b.* from customer c , buy b
where c.customerName=b.customerName;
--左联接,左边所有，右边匹配
select c.*,b.* from customer c left join buy b
on c.customerName=b.customerName;
--右联接,右边所有，左边匹配
select c.*,b.* from customer c right join buy b
on c.customerName=b.customerName;
--写一个综合的select
select sum(price),b.customerName
from buy b join customer c on c.customerName=b.customerName
where b.customerName is not null
group by b.customerName having sum(price)>= 130.8
order by customerName;
18. 简单查询
1. 如何处理null值 示例: select sal*12+nvl(comm,0)*12 "年工资",ename,comm form emp 说明: 当comm(薪水)为null时就用0代替否则为原值
2. 如何连接字符串(||) 示例: select ename || 'is a' || job from emp;
3. 使用列的别名排序 示例: select ename [as] '别名' from 表名
4. order by(排序) 示例: select *from order by deptno asc(升序),sal desc;
5. 聚合函数（max,min,avg,sun,count） 示例: select max(sal),min(sal) from emp;
6. group by 和 having 字句 示例: select avg(sal),max(sal),deptno from emp group by deptno having avg(sal) >2000 order by avg(sal);
7. between(介于两个表的条件查询) 示例: select a.ename,a.sal,b.grade from emp [as] a,salfrade [as] b
a.sal between b.lower and b.hight；
8. 自连接 示例: select worked.ename,boss.ename from emp worked,emp boss
where worked.mgr=boss.empno and worked.ename='FORD';(雇员名字为ford的老板)；
9. 子查询
1. 单行子查询 示例: select *from emp where deptno=(select deptno from emp where ename='SMITH')
2. 多行子查询
1. in 示例: select *from emp where job in (select distinct job from emp where deptno=10)
2. all 示例: select ename,sal,deptno from emp where sal>all(select sal from emp where deptno=30)
3. any 示例: select ename,sal, dept from emp where sal>any (select sal from emp where deptno=30)
3. 多列子查询 示例: select *from emp where(deptno,job)=(select deptno,job from emp where ename='SHITH') 说明: 字句中的列要与where 后的列一致
4. 内嵌视图 示例: select a2.ename,a2,sal,a2.deptno,a1.mysal from emp a2,(select deptno,avg(sal) mysal from emp group by deptno) a1 where a2.deptno=a1.deptno and a2.sal>a1.mysal;
5. 分页一共有3种方法
1. rownum 示例: select * from (select a1.*,rownum rn from (select *from emp) a1 where rownum<=10) where rownum>=6;
6. 联合查询
1. union 示例: select ename,sal,job from emp where sal>2500 union select ename,sal,job from emp where job ='manager' ;
2. union all(不取消重复行) 示例: select ename,sal,job from emp where sal>2500 union all select ename,sal,job from emp where jon='manager';
3. intersect(交集) 说明: 取两个集合的交集 示例: select ename,sal,job from emp where sal>2500 intersect select ename,sal,job from emp where jon='manager';
4. minus (差集) 示例: select ename,sal,job from emp where sal>2500 minus select ename,sal,job from emp where jon='manager';
7. 使用JDBC连接Oracle
1. 加载驱动 Class.forName("oracle.jdbc.driver.OracleDriver");
2. 得到连接 Connection con=DriverManager.getConnection("jdbc:oracle:thin:@
127.0.0.1:1521:数据库实例","用户名","密码");
8. to_date函数 示例: inset into emp values(9998,'小红','MANAGER',7782,to_date('1988-01-01','yyyy-mm-dd'),78,9,55,10)
9. decode 示例: select username 姓名,decode(empno,20,'年龄是20','年龄太小') from emp; 说明: 如果是20则打印出'年龄是20'否则打印出'年龄太小'
19. 事务和锁
1. JAVA 中加载事务处理
1. connection.setAutoCommit(false);(设置不能自动提交)——操作—— connection.commit();(提交)——catch方法中 connection.rollback()(回滚);
2. 只读事务
set transaction read only;(只读)?
20. 函数
1.字符函数
1. lower(char) 转换为小写
2. upper(char) 转换为大写
3. length(char) 返回字符串的长度 说明: 类型必须是varcar(2)类型
4. substr(char,m,n) 取字符串的子串 说明: m(从第几位取),n(取多少个)
（1-4）综合案例 select upper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1)) from emp;
5. replace(char,scerch.string,replace.string) 替换 案例: select replace(ename,'A','我是老鼠')
6. instr(string,char,index) 示例: select instr('yuechaotianyuechao','ao', 7) position from dual; 返回：17
2. 数学函数
1. round(n,[m]) 四舍五入 案例: select round(sal) from emp where ename='shunping'; 说明sal为2456.64,m为保留小数默认为0 返回2457
2. trunc(n,[m]) 截取数字 案例: select round(sal) from emp where ename='shunping'; 返回2456
3. floor(n) 返回小于或是等于n的最大整数 案例: select floor(sal) from emp where ename='shunping'; 返回2456
4. ceil(n) 返回大于或是等于n的最小整数 案例: select ceil(sal) from emp where ename='ename'; 返回2457
5. mod(n,m) 相当于n%m
6. abs(n) 返回数字n的绝对值
7. power(n,m) 返回m的n次幂
3. 日期函数
1. sysdate/current_date 说明：sysdate系统时间，current_date会话时间 该函数返回系统时间 select sysdate/current_date from dual(虚表,起帮助作用);
2. add_months(d,n) 示例: select *from emp where sysdate>add_months(hiredate,8) 说明: 八个月以前入职的所有员工信息
3. last_day(d) 返回指定日期的最后一天 示例: select lass_day(sysdate)
4. next_day('从这里开始','星期几的日期') 示例: select nest_day(sysdate,'星期一') from dual;
5. to_char 示例: select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp 2. select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss'),to_char(sal,'L99999.99') from emp
6. sys_context 通过该函数可以查询一些重要的信息 示例: select sys_context('userenv','n') n 可以为db_name(当前数据库名称)，language(语言),session_user(当前用户名),current_schema(当前方案)
21. 数据库（表）的备份 说明在oracle 的bin 目录下用dos命令且一般以dmp作为文件名
1. 数据库（表）逻辑备份（恢复）
1. 导出数据库 exp[ort] userid=system/manager@数据库实例 full=y() inctype=complete(斟量备份) file=URL
1. 在命令窗口中exp scott/tiger@数据库实例
2. 输入获取数据缓存大小可默认
3. 导出文件名称 d:exptest.dmp
4. 要导出的数据库,表
5. 是不是要导出数据 yes
6. 是不是要压缩 yes
2. 导入数据库 imp userid=system/manager full=y file=URL
2. 导入
1. userid: 用于指定执行导出操作的用户名，口令，连接字符串
2. Tables: 用于指定执行导出操作的表
3.
2. 导出表
1. 自己的表 exp userid=scott/tiger@数据库实例 tables=(emp，...) file=URL
2. 导出其他方案的表 exp userid=system/manager@数据库实例 tables=(scott.emp，...) file=URL
3. 导入表
1. 导入自己的表 imp userid=scott/tigger@数据库实例 tables=(emp) file=URL
2. 导入其他方案的表 imp userid=system/manager@数据库实例 tables=(emp) file=URL
4. 导出表的结构 exp userid=scott/tigger@数据库实例 tables=(emp，...) filed:URL rows=n
5. 导入表的结构 imp userid=scott/tigger@数据库实例 tables=(emp) file=URL rows=n
6. 使用直接导出(包含数据) exp userid=scott/tigger@数据库实例 tables=(emp，...) file=URL derect=y 说明:用于大表
2. 导出方案
1. 导出自己方案 exp userid=scott/tigger@数据库实例 owner=scott file=URL
2. 导出其它方案 exp userid=system/manager@数据库实例 owner=(system,...) file=URL
3. 导入方案
1. 导入自己的方案 imp userid=scott/tiger file=URL
2. 导入其它方案 imp userid=system/manager file=URL fromuser="从哪个用户" touser="导到那个用户"
22 .数据字典视图
1. user_tables 返回当前用户所对应方案的所有表
2. all_tables 用于显示当前用户可以访问的所有表（包括自身方案的所有表）
3. dba_tables(要求用户具备dba的权限) 返回整个数据库方案的所有表
23. 数据库字典（只读表和视图的集合）
1. dba_users 可以显示所有数据库用户的详细信息
2. dba_sys_privs 可以显示用户所具有的系统权限
3. dba_tab_privs 可以显示用户具有的对象权限
4. dba_col_privs 可以显示用户具有的列权限
5. dba_role_privs 可以显示用户所具有的角色
6. dba_roles; 查询Oracle中所有角色 示例: select * from dba_roles
(1-6)示例
1. 一个角色包含的系统权限 select * from dba_sys_privs where grantee='CONNECT' /select * from role_sys_privs where role='CONNECT'
2. 一个角色包含的对象权限 select * from dba_tab_privs where grantee='CONNECT'
3. 一个用户具有什么样的角色 select *from dba_role_privs where grantee='SCOTT'
4. 查询当前数据库全称 select *from global_name
5. 查询当前用户可以访问的所有数据字典视图 select *from dict
22. 表空间和数据文件
1. 创建表空间 create tablespace 表空间名 datafile URL size 20M uniform size 128k(区)
2. 使用表空间 create table buy
(
buyID int primary key,
buyName varchar2(20)
) tablespace 表空间名;
3. 改变表空间状态
1. 使表空间脱机 alter tablespace 表空间名 offline;
2. 使表空间联机 alter tablespace 表空间名 online;
3. 只读表空间 alter tablespace 表空间名 read only;
4. 可读，可写 alter tablespace 表空间名 read write;
4. 删除表空间 drop tablespace '表空间名' including contents and datafiles;
5. 扩展表空间
1. 增加数据文件 alter tablespace 表空间名 add datafile URL size 20M
2. 增加数据文件的大小 alter tablespace 表空间名 URL resize 20M
3. 设置文件的自动增长 alter tablespace 表空间名 URL autoextend on next 10M maxsize 500M
6. 移动数据文件
1. 确定数据文件所在的表空间 select tablespace_name from dba_data_files where file_name=URL;
2. 使表空间脱机 alter tablespace 表空间名 offline;
3. 使用命令移动数据文件到指定的目标位置 host move Url Url(目标URL)
4. 执行 alter tablespace 命令 alter tablespace 表空间名 rename datafile URL to 目标URL
5. 使用表空间联机 alter tablespace 表空间名 online
问题:
1. 知道表空间名，显示该表空间包括的所有表 select *from all_tables where tablespace_name=表空间名
2. 知道表名，查看该表属于那个表空间 select tablespace_name from user_tables where table_name=表名
23. 约束
1. 主键约束(一个表中只能有一个，不能为空) alter table 表名 add constraint 约束名 primary key(字段)
2. 唯一约束（可以有多个，允许为空，但只能出现一个空值） alter table 表名 add constraint 约束名 unique(字段)
3. 默认约束 alter table 表名 add constraint 约束名 default ('信息') for 字段
4. 检查约束 alter table 表名 add constraint 约束名 check(stuAge between 15 and 40)
5. 外键约束 alter table 表名 add constraint 约束名 foreign key(stuNo) references 主表名（字段）
6. null约束 alter table 表名 modify 字段 not null
7. 删除约束 alter table 表名 drop constraint 约束名
8. 如果两张表存在关系 alter table 表名 drop primary key cascade;
24. 索引
1. 建立索引
1. 单列索引 create[unique] index 索引名 on 表名（列名）
2. 复合索引 create index 索引名 on 表名（列名1，列名2...）
3. 索引范围 Unique(唯一索引) clustered(聚集索引) nonclustered(非聚集索引) 说明: 可选
2. 使用索引查询 select * from 表名 with(index=索引名)
25. 块(定义部分，执行部分，例外部分 )
declare /*定义部分-----定义常量，变量，游标，复杂数据类型*/ 可选
begin /*执行部分-----要执行的pl/sql语句和sql语句*/ 必须
exception /*例外处理部分----处理运行的各种错误*/ 可选
end
1. 存储过程
2. 函数
3. 触发器
4. 包
(1-4) 实例
1. set serveroutput on size 10000--打开输出选项
2. --最简单的块
set serveroutput on
begin
dbms_output.put_line('hello word');
end;
3. --包含定义部分的块
declare
v_name varchar2(20); --定义一个变量
v_sal number(7,2); --定义一个薪水变量
begin
select ename,sal into v_name,v_sal from emp where empno=&aa;
dbms_output.put_line('v_name的值为: '||v_name||' 薪水为: '||v_sal);
end;
4. --包含定义，例外的块
declare
v_name varchar2(20); --定义一个变量
v_sal number(7,2); --定义一个薪水变量
begin
select ename,sal into v_name,v_sal from emp where empno=&aa;
dbms_output.put_line('v_name的值为: '||v_name||' 薪水为: '||v_sal);
--异常处理
exception
when no_data_found then dbms_output.put_line('朋友，你的标号输入有误');
end;
3. declare
v_test varchar2(10);
begin
v_test:='This is...';
dbms_output.put_line('v_test的值为:'||v_test);
end;
/
26. 存储过程
1. 创建过程
create or replace procedure 过程名 [(testsal number...这里不需要指定大小,输出的话 变量 out 类型) ]
is
begin
--执行部分
select *from emp where sal=testsal;
end;
说明: 如果有错误信息，可执行 show error; 显示错误
2. 调用过程
1. exec 过程名[（参数...）]
2. call 过程名[（参数...）] 在java中调用
1-2案例：
create or replace procedure sp_pro3(spName varchar2,newSal number) is
begin
update test2 set sal=newSal where ename=spName;
end;
exec sp_pro3(1800,'SMITH');
3. 删除过程 drop procedure 过程名
4. 如何在 java 程序中调用存储过程
1. 加载驱动
Class.forName("oracle.jdbc.driver.oracleDriver");
2. 得到连接
Connectiong con=DriverManger.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:数据库名","sa","tigger");
3. 创建CallableStatement
CallableStatement cs=con.prepareCall("{call 储存过程名(?...)}");
4. 赋值
cs.setString(1,"值");
如果是取 cs.registerOutParameter(2,oracle.jdbc.OracleTypes.对应pl/sql类型);
5. 执行
cs.execute();
取返回值 String 变量=cs.getString(index);
去游标值 ResaultSet rs=(ResaultSet)cs.getObject(2);
while(rs.next()){
System.out.println(rs.getInt(1)+" "+rs.rs.getString(2));
}
6. 关闭资源
cs.close();
con.close();
27. 编写规范
1. 注释
1. 单行 --
2. 多行 /* */
2. 表示符号
1. 定义变量时，建议用 v_ 作为前缀
2. 定义常量时, 建议用 c_ 作为前缀
3. 定义游标时, 建议用 _cursor 作为后缀
4. 定义例外时, 建议用 e_ 作为前缀
28. 函数
1. 创建函数
create or replace function 函数名（sal number...) return 返回类型 is yearSal number(7,2);
begin
--执行部分
select sal*12+nvl(econn,0)*12 into yearSal from emp where ename=sal
return yearSal;
end;
说明: 如果有错误信息，可执行 show error; 显示错误
2. 调用函数
1. var abc number;
2. call 函数名(参数) into:abc;
3. exec 函数名（参数...）
案例：
create or replace function sp_fun3(spName varchar2)
return number
is
yearSal number(7,2);
begin
select sal*12+nvl(comm,0)*12 into yearSal from test2 where ename=spName;
return yearSal;
end;
--调用
var abc number;
call sp_fun3('SCOTT') into:abc;
29. 包
1. 创建包
create or replace package 包名 is
procedure 过程名(sal number...)
function 函数名(sal number...) return 返回类型
end;
说明: 如果有错误信息，可执行 show error; 显示错误
2. 创建包体
cteate package body 包名 is
procedure 过程名(参数...)
is
begin
--执行部分
end;
function 函数名(参数...) return 返回类型 is yearSal number(7,2);
begin
--执行部分
return yearSal;
end;
end;
3. 调用包名的过程和函数
1. exec 包名.过程/函数
4. 示例
--创建包
create or replace package packagetes is
procedure proceduretes(testsal number);
function functiontes(name varchar2) return number;
end;
--创建包体
create or replace package body packagetes is
procedure proceduretes(testsal number)
is
begin
update tabletest set sal=testsal where ename='SMITH';
end;
function functiontes(name varchar2) return number is newsal number(7,2);
begin
select sal*12 into newsal from tabletest where ename='SMITH';
return newsal;
end;
end;
--调用
exec packagetes.proceduretes(120);
create or replace package testpackage as
type test_cursor is ref cursor;
end testpackage;
//利用一个包创建一个类型
30. 变量(参照变量，游标变量，对象类型变量)
1. 标量变量 v_ename varchar2(20):='hehe'
案例：
declare
v_tax_rate number(3,2):=0.03;
--用户名
v_ename test2.ename%type;
v_sal test2.sal%type;
v_tax_sal number(7,2);
begin
--执行
select ename,sal into v_ename,v_sal from test2 where empno=&aa;
--计算所得税
v_tax_sal:=v_sal*v_tax_rate;
--输出
dbms_output.put_line('姓名是：'||v_ename||' 工资：'||v_sal||' 缴税：'||v_tax_sal);
end;
标示符名 %type 表名.列名%type
2. 复合变量
1. pl/sql 记录
--定义一个pl/sql记录类型 该类型包含了3个数据name,salary,title
declare
type emp_record_type(记录类型) is record(name 表名.列名%type,salary 表名.列名%type,title 表名.列名%type);
--定义了一个变量，这个变量类型是emp_record_type
变量 emp_record_type;
begin
select ename,sal,job into 变量 from emp where empno=&aa;
--打印出值
dbms_output.put_line('员工名：'||变量.name||' 工资'||变量.salary); ?
end;
示例:
declare
type test_type is record(name tabletest.ename%type,salary tabletest.sal%type);
--定义了一个变量，这个变量类型是emp_record_type
test2 test_type;
begin
select ename,sal into test2 from tabletest where empno='7788';
--打印出值
dbms_output.put_line('员工名：'||test2.name||' 工资'||test2.salary);
end;
2. pl/sql表(相当于高级语言中的数据)
--定义了一个pl/sql 表类型 sp_table_type 该类型用于存放 表名.列名%ytpe
--index by binary_integer代表下标整数
declare
type sp_table_type is table of 表名.列名%ytpe index by binary_integer;
--定义了一个变量，这个变量类型
sp_table(变量) sp_table_type;
begin
--赋值
select ename into sp_table(0) from emp where empno='SMITH'
用SQL赋值 execute immediate v_sql into 变量名
--打印出值
dbms_putout_outline('员工名：'||sp_table(0))
end;
示例
declare
type sp_table_type is table of tabletest.ename%type index by binary_integer;
--定义了一个变量，这个变量类型
sp_table sp_table_type;
begin
--赋值
select ename into sp_table(0) from tabletest where empno='7788';
--用SQL赋值 execute immediate v_sql into 变量名
--打印出值
dbms_output.put_line('员工名：'||sp_table(0));
end;
3. 参照变量
1. 游标变量
declare
--定义游标类型
type sp_emp_curser is ref cursor;
--定义一个游标变量
test_cursor sp_emp_curser;
--定义变量
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
--执行
--把test_cursor 和一个select 结合
open test_cursor for select ename,sal from where deptno=&no;
--循环取出
loop
fetch test_cursor into v_ename,v_sal;
--判断是否test_cursor 为空
exit when test_cursor%notfound;
dbms_putout_outline('名字：'||v_ename||' 工资'||v_sal)
end loop;
end;
示例:
declare
--定义一个游标类型
type test_cursor is ref cursor;
--定义一个游标变量
test2_cursor test_cursor;
--定义两个变量
v_name tabletest.ename%type;
v_sal tabletest.sal%type;
begin
--打开一个游标
open test2_cursor for select ename,sal from tabletest;
--循环取出
loop
fetch test2_cursor into v_name,v_sal;
--跳出循环条件
exit when test2_cursor%notfound;
--打印出值
dbms_output.put_line('员工的姓名为: '||v_name||' 工资为:'||v_sal);
end loop;
end;
2. 对象类型变量
31. 逻辑控制语句
1. if__then
create or replace procedure a(spname varchar2) is
--定义
v_sal emp.sal%type;
begin
--执行
select sal into v_sal from emp where ename=spname;
--判断
if v_sal<2000 then
update emp set sal=sal*1.1 where ename=spname;
end if;
end;
--调用
exec a('SMITH');
2. if__then__else
begin
if 条件 then 处理1
else 处理2
end if;
end;
3. if__then__elsif__else
begin
if 条件1 then 处理1
elsif 条件2 then 处理2
else 处理3
end if;
end;
4. loop (先循环一次再判断)
loop
--执行语句
--判断是否要退出循环
exit when 字段=值
--自增语句
end loop;
5. while
while 条件 loop
--执行部分
--自增语句
end loop
6. case
when 条件 then 处理
when 条件2 then 处理2
else 处理3
end case;
32. 例外处理
1. 预定义例外
1. no_data_found
2. case_not_found 如果在where 子句中没有包含必须的条件分支时
3. cursor_already_open
4. dup_val_on_index 在唯一索引所对应的列上插入重复的值时，会隐含的触发例外
5. invalid_cursor 试图从没有打开的游标提取数据，或是关闭没有打开的游标时
6. invalid_number 当输入数据有误时
7. too_many_rows 当执行select into 语句时，如果返回超过了一行时
8. zero_divide 当分母为0时
9. value_error 标量的长度不足以容纳实际数据
10. login_denide 非法用户登陆
11. not_logged_on 如果用户没有登陆就进行操作
12. storage_error
13. timeout_on_resoruce
2. 非预定义例外
3. 自定义例外
1. 定义一个例外
例外名 exception
2. 触发例外
if sql%notfound then raise 例外名
3. 捕获
exception
when 例外名 then dbms_output_put_line('没有更新任何用户')
33. 视图
1. 创建视图
create or replace view 视图名
as
select emp.emptno,emp.ename,dept.dname from emp,dept where emp.deptno=dept.deptno;
[with read only]
2. 查看视图
select * from 视图名
3. 删除视图
drop view 视图名
说明: 视图和视图也可以联合查询
2. 视图的改变影响到真正的表
34. 触发器
1. 创建触发器
create or replace trigger 触发器名
BEFORE/after insert or delete or update on 表名
begin
if deleting then 处理1
elseif inserting then 处理2
else 处理3
end if;
end;
2. 示例
create or replace trigger trigertest
after insert or update or delete on tabletest
begin
if inserting then
insert into test values(sysdate,user,'i');
elsif updating then
insert into test values(sysdate,user,'u');
elsif deleting then
insert into test values(sysdate,user,'d');
else
nsert into test values(sysdate,user,'s');
end if;
end;
35. 创建数据库
1. 在database configuration assint界面中
2. new database
3. 取一个全局名
4. 选择专用
5. 修改内存大小
6.
36. 补充
1. 去除重复项 DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(y.ROWID) FROM EMP y WHERE y.EMPNO = E.EMPNO )
案例：delete from test a1 where rowid > (select min(rowid) from test a2 where a1.username = a2.username);
2. 查询员工资(由高到底)前5的员工信息
案例：select * from (select a.*,rownum rn from (select * from emp order by sal desc) a) where rn <=5;
扫一扫
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
