Oracle学习笔记.docx
《Oracle学习笔记.docx》由会员分享，可在线阅读，更多相关《Oracle学习笔记.docx（70页珍藏版）》请在冰点文库上搜索。
Oracle学习笔记
Oracle学习笔记
Oracle学习笔记
（1）Oracle数据库编程基础
文章分类:
数据库
这段时间出差，一直在搞需求分析和设计，每天都是写文档画UML，都有好几个月没有写代码了，只怕都快忘记了！
这是我用Oracle时学习的Oracle编程做的笔记，今天也发上来与大家分享一下，一共有八章。
现在在有时间就学一下EJB3，因为在项目中会用到，等学完了把EJB3的笔记也发上来和大家分享！
通过SqlPlus登录Oracle，sqlplus用户名/密码@数据库名
建表
Sql代码
1.create table demo（
2. id number（8）,
3. name varchar2（20）
4.）
createtabledemo（
idnumber（8）,
namevarchar2（20）
）
插入数据
Sql代码
1.insert into demo values（1,'zhangshang'）;
2.insert into demo values（2,'lishi'）;
insertintodemovalues（1,'zhangshang'）;
insertintodemovalues（2,'lishi'）;
提交
Sql代码
1.commit;
commit;
查询数据字典（table_name='这里的值要大写'）
Sql代码
1.select * from dba_tab_cols a where a.table_name='DEMO';
select*fromdba_tab_colsawherea.table_name='DEMO';
创建视图
createorreplace:
如果该视图存在就替换，如果不存在就创建
Sql代码
1.create or replace view myView as select id 编号,name 姓名 from demo;
2.select * from myView;
createorreplaceviewmyViewasselectid编号,name姓名fromdemo;
select*frommyView;
创建同义词（同义词相当于表的别名或逻辑名，可以通过该名称来操作相对应的物理表）
在分布式Oracle数据库中，标识一个对象需要四部分：
主机名、实例名、模式名、对象名。
例如SYSTEM.auths@PX.orcl,其中模式名是SYSTEM，对象名是auths，PX.orcl是一个数据库链，它指向服务器PX上的实例orcl，我们可以为其指定一个同义词，使所有的用户都可以通过简单的同义词来访问表SYSTEM.auths@PX.orcl
创建公有的同义词
Sql代码
1.create public synonym syn1 for demo;
createpublicsynonymsyn1fordemo;
创建私有的同义词
Sql代码
1.create synonym syn2 for demo;
createsynonymsyn2fordemo;
创建并使用序列
创建序列的完整语法：
createsequence序列名incrementby增量种子数startwith起始数字maxvalue最大值;
我们可以使用序列的两个属性nextval和currval，其中nextval是返回下一个可用的序列值，而currval用于获得当前序列的值
创建序列的简单方法
Sql代码
1.create sequence mySeq;
createsequencemySeq;
使用创建的序列
Sql代码
1.insert into demo values（mySeq.nextval,'ddd'）;
insertintodemovalues（mySeq.nextval,'ddd'）;
获得当前序列的值
Sql代码
1.select mySeq.currval from dual;
selectmySeq.currvalfromdual;
删除一条记录
Sql代码
1.delete from demo where id=2;
deletefromdemowhereid=2;
查询所有记录
Sql代码
1.select * from demo;
select*fromdemo;
格式化日期时间
Sql代码
1.select to_char（sysdate,'yyyy-MM-dd hh24:
mm:
ss'） 格式化日期 from dual;
Oracle学习笔记
（2）PLSQL编程基础
文章分类:
数据库
这是第二章的学习笔记，学习完第一章的基础之后，从现在开始要学习Oracle编程了……，希望大家能多给俺一些支持啊！
编程时使用的工具是PLSQLDeveloper7.1.4
Sql代码
1.select * from Employee;
2.select * from dba_tab_cols t where t.table_name='EMPLOYEE';
select*fromEmployee;
select*fromdba_tab_colstwheret.table_name='EMPLOYEE';
Sql代码
1.-- 声明部分，用于定义变量
2.declare
--声明部分，用于定义变量
declare
constant用于定义常量，当定义常量时必须指定它的初始值，且数据不能改变
Sql代码
1.n_aaa constant number（4,2） :
=5.5;
n_aaaconstantnumber（4,2）:
=5.5;
boolean类型的值只能用于sqlplus语句块中，不能用在表字段的数据类型中
notnull用于强制初始化变量不能为空，此时必须为变量指定值
default用于指定变量或常量的默认值
Sql代码
1.v_valid boolean not null default false;
2.v_name varchar2（20）;
v_validbooleannotnulldefaultfalse;
v_namevarchar2（20）;
为了确保变量使用合适的数据类型和长度，可以使用%type属性定义变量，它会按照数据库列
或其它变量来确定新变量的类型和长度
Sql代码
1.v_job Employee.Job%type;
v_jobEmployee.Job%type;
将v_job2定义为与变量v_job的数据类型和长度完全一致
Sql代码
1.v_job2 v_job%type;
v_job2v_job%type;
复合变量：
处理单行单列数据时，用标量变量；处理单行多列时，用PL/SQL记录；处理单列多行时，
用集合；处理多行多列数据时，可以结合使用PL/SQL记录和集合
PL/SQL记录（RECORD）：
方法一：
emp_recode_type是PL/SQL记录类型并包含有三个成员（name,salary,job）
Sql代码
1.type emp_recode_type is record（
2. name employee.name%type,
3. salary employee.salary%type,
4. job employee.job%type
5.）;
typeemp_recode_typeisrecord（
nameemployee.name%type,
salaryemployee.salary%type,
jobemployee.job%type
）;
emp_recode是记录变量
Sql代码
1.emp_recode emp_recode_type;
emp_recodeemp_recode_type;
方法二：
使用表名的%rowtype属性定义记录变量
注意：
用%rowtype属性定义记录变量时，记录成员个数、名称、类型必须与
表或视图的列的个数、名称和类型完全相同
Sql代码
1.emp_recode2 employee%rowtype;
emp_recode2employee%rowtype;
集合类型
PL/SQL表（也称索引表，下标可以为负数，下标没有上限和下限，并且元素的个数无限）
Sql代码
1.--这里的table 实指集合
2.type name_table_type is table of employee.name%type index by binary_integer;
3. name_table name_table_type;
--这里的table实指集合
typename_table_typeistableofemployee.name%typeindexbybinary_integer;
name_tablename_table_type;
嵌套表:
嵌套表的下标不能为负值，其元素的个数没有限制并可以使用表列的数据类型
在使用嵌套表时必须先用createtype语句建立嵌套表类型
嵌套表只是比PL/SQL表少了indexbybinary_integer
Sql代码
1.type num_table_type is table of number（4）;
2.num_table num_table_type;
typenum_table_typeistableofnumber（4）;
num_tablenum_table_type;
varray（变长数组）:
varray的元素个数是有限制的，在使用其之前必须先建立varray类型
在此创建的varray只能存放20个varchar2类型的数据
注意：
嵌套表列数据需要存储在专门的存储表中，而varray数据则与
其它列数据一起存放在表字段中
Sql代码
1.type v_varray_type is varray（20） of varchar2（10）;
2.n_empno employee.empno%type;
typev_varray_typeisvarray（20）ofvarchar2（10）;
n_empnoemployee.empno%type;
Sql代码
1.-- 程序开始
2.begin
--程序开始
begin
执行部分，执行PL/SQL/和SQL语句
可以用into将从表人查询出来的值赋给指定的变量
将值赋给变量有两种方法，一是用:
=，二是用into
&符号用于提示用户输入一个值，&为sqlplus的替代变量
Sql代码
1.n_empno :
= &输入你要查询的员工编号：
;
2.select Name,Job into v_name,v_job from Employee where empno=n_empno;
n_empno:
=&输入你要查询的员工编号：
;
selectName,Jobintov_name,v_jobfromEmployeewhereempno=n_empno;
这里的||号与Java中的+号类似，表示要将两边的字符串连接起来
dbms_output是Oracle所提供的系统包，用于输出数据或消息，
而put_line是该包所包含的过程，用于输出字符串信息并换行
当在CMD中使用dbms_output包时，必须要先将sqlplus的环境变量
serveroutput设置为on
Sql代码
1.dbms_output.put_line（'你查询的员工是：
' || v_name || ' 他的工作是：
' || v_job）;
dbms_output.put_line（'你查询的员工是：
'||v_name||'他的工作是：
'||v_job）;
引用记录变量的成员并赋值（赋值可以用:
=和into两种方法）
注意：
用select……into语句给记录变量赋值时，select语句中的列名列表的名称和个数、
类型必须与记录变量的成员个数、名称、类型完全相同
Sql代码
1.dbms_output.put_line（'你查询的员工是：
' || v_name || ' 他的工作是：
' || v_job）;
2.
3.emp_recode.name :
= '钱森';
4. dbms_output.put_line（'引用记录变量的成员并赋值（Recode）：
' || emp_recode.name）;
5.
6. select name,job into emp_recode2.name,emp_recode2.job from employee where empno=2;
7. dbms_output.put_line（'引用记录变量的成员并赋值（RowType）：
' || emp_recode2.name || ' 的工作是：
' || emp_recode2.job）;
8. -- 使用PL/SQL表（索引表）
9. select name into name_table（-50） from employee where empno=1;
10. dbms_output.put_line（'雇员名：
' || name_table（-50））;
11. -- 使用嵌套表
12. -- 必须先设置嵌套表的下标，然后才能对该集合进行赋值
13. num_table :
= num_table_type（1,2,3,4,5）;
14. num_table
（1） :
= 10;
15. num_table（5） :
= 100;
16. dbms_output.put_line（'使用嵌套表：
' || num_table
（1） || ',' || num_table（5））;
17.
18.exception
19. -- 异常处理部分
20. when NO_DATA_FOUND then dbms_output.put_line（'查询不到员工编号为' || n_empno || '的员工！
'）;
21. when TOO_MANY_ROWS then dbms_output.put_line（'数据完整性错误，员工编号' || n_empno || '重复！
'）;
22. when OTHERS then dbms_output.put_line（'PL/SQL执行错误！
' || sqlerrm）;
23.-- 程序结束
24.end;
25./
dbms_output.put_line（'你查询的员工是：
'||v_name||'他的工作是：
'||v_job）;
emp_recode.name:
='钱森';
dbms_output.put_line（'引用记录变量的成员并赋值（Recode）：
'||emp_recode.name）;
selectname,jobintoemp_recode2.name,emp_recode2.jobfromemployeewhereempno=2;
dbms_output.put_line（'引用记录变量的成员并赋值（RowType）：
'||emp_recode2.name||'的工作是：
'||emp_recode2.job）;
--使用PL/SQL表（索引表）
selectnameintoname_table（-50）fromemployeewhereempno=1;
dbms_output.put_line（'雇员名：
'||name_table（-50））;
--使用嵌套表
--必须先设置嵌套表的下标，然后才能对该集合进行赋值
num_table:
=num_table_type（1,2,3,4,5）;
num_table
（1）:
=10;
num_table（5）:
=100;
dbms_output.put_line（'使用嵌套表：
'||num_table
（1）||','||num_table（5））;
exception
--异常处理部分
whenNO_DATA_FOUNDthendbms_output.put_line（'查询不到员工编号为'||n_empno||'的员工！
'）;
whenTOO_MANY_ROWSthendbms_output.put_line（'数据完整性错误，员工编号'||n_empno||'重复！
'）;
whenOTHERSthendbms_output.put_line（'PL/SQL执行错误！
'||sqlerrm）;
--程序结束
end;
/
Oracle学习笔记（3）PLSQL程序控制结构
文章分类:
数据库
这是第三章的学习笔记，学习完第二章的编程基础之后，从现在开始要学习Oracle编程了……，希望大家能多给俺一些支持啊！
这周六总算是不用加班，可以好好出去玩一下了！
今天去武大看樱花了，哈哈，不错！
编程时使用的工具是PLSQLDeveloper7.1.4
Sql代码
1.select * from employee;
2.select * from dba_tab_cols t where t.table_name='EMPLOYEE';
select*fromemployee;
select*fromdba_tab_colstwheret.table_name='EMPLOYEE';
Sql代码
1.declare
2. n_empno employee.empno%type;
3. v_name employee.name%type;
4. n_salary employee.salary%type;
5. v_temp varchar2（30）;
6. n_temp number（5） :
= 1;
7. -- 自定义异常
8. e_exception exception;
declare
n_empnoemployee.empno%type;
v_nameemployee.name%type;
n_salaryemployee.salary%type;
v_tempvarchar2（30）;
n_tempnumber（5）:
=1;
--自定义异常
e_exceptionexception;
exception_init是一个编译时指令，用于将一个内部错误与异常的名称关联。
一旦关联成功后，我们就可以通过名称抛出异常并用when处理器捕获错误
Sql代码
1.pragma exception_init（e_exception,-66666）;
2.
3.in
4.n_empno :
= &员工编号：
;
5.select name,salary into v_name,n_salary from employee where empno=n_empno;
pragmaexception_init（e_exception,-66666）;
begin
n_empno:
=&员工编号：
;
selectname,salaryintov_name,n_salaryfromemployeewhereempno=n_empno;
if语句
简单条件判断
Sql代码
1.if n_salary < 4000 then
2. update employee set salary = n_salary+500 where empno=n_empno;
3. dbms_output.put_line（v_name || '的工资增加了500元!
'）;
4.else
5. dbms_output.put_line（v_name || '的工资大于4000，不用加工资!
'）;
6.end if;
7.-- 多重条件分支
8.if n_salary < 4000 then
9. dbms_output.put_line（v_name || '的工资少于4000元！
'）;
10.elsif n_salary >= 4000 and n_salary < 5000 then
11. dbms_output.put_line（v_name || '的工资在4000~5000元之间！
'）;
12.elsif n_salary >= 5000 and n_salary < 6000 then
13. dbms_output.put_line（v_name || '的工资在5000~6000元之间！
'）;
14.else
15. dbms_output.put_line（v_name || '的工资大于6000元！
'）;
16.end if;
ifn_salary<4000then
updateemployeesetsalary=n_salary+500whereempno=n_empno;
dbms_output.put_line（v_name||'的工
升级会员 