二、Oracle数据操作和查询
1、 Oracle语言
2、Oracle数据类型
3、创建表和约束
ALTER TABLE 表名 ADDCONSTRAINT 约束名 约束内容
注意:Oracle中default是一个值,不存在default约束
4、 数据操纵语言(DML)
5、高级查询
5.1、 操作符 (DISTINCT、NULL、IN、BETWEEN...AND..)
5.2、 集合操作 UNION、UNION ALL、INTESECT、MINUS
5.3、表联接(+)
(+):Oracle专用连接符,被加号修饰的表示补充表。在条件中出现在左边的指右连接,反之。
CREATE [OR REPLACE](创建或替换)[{FORCE|NOFORCE}](基表不存在可以创建视图|反之)VIEWview_name
AS
SELECT 查询
[WITH READ ONLY](只读)
3、 表空间
4、索引(基于ROWID)
1、 Oracle语言
2、Oracle数据类型
3、创建表和约束
ALTER TABLE 表名 ADDCONSTRAINT 约束名 约束内容
注意:Oracle中default是一个值,不存在default约束
4、 数据操纵语言(DML)
5、高级查询
5.1、 操作符 (DISTINCT、NULL、IN、BETWEEN...AND..)
5.2、 集合操作 UNION、UNION ALL、INTESECT、MINUS
5.3、表联接(+)
(+):Oracle专用连接符,被加号修饰的表示补充表。在条件中出现在左边的指右连接,反之。
三、子查询和常用函数
1、ANY和ALL子查询
2、 伪列 (ROWID、ROWNUM)
3、 Oracle函数
2、视图1、ANY和ALL子查询
2、 伪列 (ROWID、ROWNUM)
3、 Oracle函数
四、数据库对象
1、同义词
CREATE [OR REPLACE] [PUBLIC]SYNONYM [schema.]synonym_name FOR[schema.] object_name
1、同义词
CREATE [OR REPLACE] [PUBLIC]SYNONYM [schema.]synonym_name FOR[schema.] object_name
CREATE [OR REPLACE](创建或替换)[{FORCE|NOFORCE}](基表不存在可以创建视图|反之)VIEWview_name
AS
SELECT 查询
[WITH READ ONLY](只读)
3、 表空间
4、索引(基于ROWID)
五、PL/SQL程序设计
1、PL/SQL块
2、 数据类型
2.1、标量数据类型(基本数据类型)
2.2、属性数据类型(%TYPE、%ROWTYPE)
3、SELECT ...INTO 赋值
可以赋多个值且没有返回值,赋值时类型和个数都要匹配,不能使多行或者没有记录,一般用于聚合。
4、PL/SQL条件和 循环控制
5、PL/SQL动态执行SQL语句
sql_stmt:='insert into dept values(:1,:2,:3)'
参数占位符可以使字符或数字,也可以是相同的(:1,:1,:3);
返回多个值
declare sql_stmt varchar2(100);sal number(11);mgr number(11);
begin
sql_stmt:='update scott.emp set sal=2000 where empno=:1 returningsal,MGR into :2,:3';
EXECUTE IMMEDIATE sql_stmt(输入参数) using 7499 returning intosal,mgr(输出参数);
dbms_output.put_line(mgr||' '||sal);
end;
6、 PL/SQL异常处理
6.1、预定义异常
6.2、自定义异常
6.3、引发应用程序异常
2、PL\SQl集合
2.1、索引表
索引表的元素个数没有限制,下标可以为负值。元素连续,下标不一定连续。且不能作为表列的数据类型使用。
2.2、嵌套表
嵌套表元素下标从1开始,并且元素个数没有限制。嵌套表元素的数组元素值也是可以稀疏的,所以当删除一个元素后,该下标还存在,并还可以为该下标重新赋值。
2.3、变长数组(VARRAY)
和嵌套表一致,但VARRAY构造函数初始化不能超过size_limit的值。
2.4、记录表
结合了记录和索引表的优点。
2.5、集合方法
集合方法是Oracle所提供的用于操作集合变量的内置对象或过程,其中EXISTS,COUNT,LIMIT,NEXT,PRIOR是函数,而EXTEND,TRIM,DELETE则是过程。
3、批量绑定
批量绑定使用BULK COLLECT子句和FORALL语句来完成,其中BULKCOLLECT子句用于取得批量数据,该子句只能用于SELECT语句、FETCH语句和DML返回子句中,而FORALL语句只使用于执行批量的DML操作。
3.1、FORALL语句
语法一:
FORALL indexIN lower_bound..upper_bound
sql_statement;
语法二:
FORALL indexIN INDICES OF collection
[BETWEEN lower_bound.AND.upper_bound]
sql_statement;
用于跳过没有赋值的下标位置
语法三:
FORALL indexIN VALUES OF collection
sql_statement;
用于遍历集合中的值,index为当前索引的值
3.2、BULK COLLECT
eg:select * BULK COLLECT into emp_table fromscott.emp;--将所有数据填充到集合里
delete fromscott.emp where deptno=&no RETURN ename BULKCOLLECT into emp_table;--取得DML所作用的多行数据
2、重新编译子程序
ALTER (PROCEDURE or VIEW or FUNCTION...) name COMPILE;
1、建立包
1.1、建立包规范
在包规范里定义的所有组件都是公有的
1.2、建立包体
包体用于实现包规范所定义的过程和函数,也可以单独定义私有组件
1.3、不同作用域可以定义相同变量,包的内部不能定义游标变量,但可以定义游标类型,也可以定义静态游标(普通游标)
2、包重载
2.1、函数重载时返回值的数据类型必须完全相同
2.2、判断是否重载参数名也要匹配,否则不是重载
3、包构造过程
当同一会话第一次调用包的公用组件时,会自动执行其构造过程,而后不会再调用构造过程。
4、纯度级别(限制公用函数)
1、PL/SQL块
2、 数据类型
2.1、标量数据类型(基本数据类型)
2.2、属性数据类型(%TYPE、%ROWTYPE)
3、SELECT ...INTO 赋值
可以赋多个值且没有返回值,赋值时类型和个数都要匹配,不能使多行或者没有记录,一般用于聚合。
4、PL/SQL条件和 循环控制
5、PL/SQL动态执行SQL语句
sql_stmt:='insert into dept values(:1,:2,:3)'
参数占位符可以使字符或数字,也可以是相同的(:1,:1,:3);
返回多个值
declare sql_stmt varchar2(100);sal number(11);mgr number(11);
begin
sql_stmt:='update scott.emp set sal=2000 where empno=:1 returningsal,MGR into :2,:3';
EXECUTE IMMEDIATE sql_stmt(输入参数) using 7499 returning intosal,mgr(输出参数);
dbms_output.put_line(mgr||' '||sal);
end;
6、 PL/SQL异常处理
6.1、预定义异常
6.2、自定义异常
6.3、引发应用程序异常
六、复合数据类型
1、PL\SQL记录2、PL\SQl集合
2.1、索引表
索引表的元素个数没有限制,下标可以为负值。元素连续,下标不一定连续。且不能作为表列的数据类型使用。
2.2、嵌套表
嵌套表元素下标从1开始,并且元素个数没有限制。嵌套表元素的数组元素值也是可以稀疏的,所以当删除一个元素后,该下标还存在,并还可以为该下标重新赋值。
2.3、变长数组(VARRAY)
和嵌套表一致,但VARRAY构造函数初始化不能超过size_limit的值。
2.4、记录表
结合了记录和索引表的优点。
2.5、集合方法
集合方法是Oracle所提供的用于操作集合变量的内置对象或过程,其中EXISTS,COUNT,LIMIT,NEXT,PRIOR是函数,而EXTEND,TRIM,DELETE则是过程。
3、批量绑定
批量绑定使用BULK COLLECT子句和FORALL语句来完成,其中BULKCOLLECT子句用于取得批量数据,该子句只能用于SELECT语句、FETCH语句和DML返回子句中,而FORALL语句只使用于执行批量的DML操作。
3.1、FORALL语句
语法一:
FORALL indexIN lower_bound..upper_bound
sql_statement;
语法二:
FORALL indexIN INDICES OF collection
[BETWEEN lower_bound.AND.upper_bound]
sql_statement;
用于跳过没有赋值的下标位置
语法三:
FORALL indexIN VALUES OF collection
sql_statement;
用于遍历集合中的值,index为当前索引的值
3.2、BULK COLLECT
eg:select * BULK COLLECT into emp_table fromscott.emp;--将所有数据填充到集合里
delete fromscott.emp where deptno=&no RETURN ename BULKCOLLECT into emp_table;--取得DML所作用的多行数据
七、
使用游标
(只能逐行向下读取)
1、显示游标属性(只能判断当前状态)
当游标取值到末尾时,继续取值将取得最后缓存里的值。
2、使用游标
2.1、使用FETCH..BULK COLLECT INTO提取游标所有数据(不需要初始化集合大小)
2.2、使用FETCH..BULK COLLECTINTO..LIMIT提取部分数据
3、参数游标
DECLARE cursor_name(parameter_name datatype default value) ISselect_statement;
游标参数只能指定数据类型,不能指定长度。当不给参数时不需要括号。
4、使用游标更新或删除数据
语法:
CURSOR cursor_name(parameter_name datatype)
ISselect_statment FOR UPDATE [OF column_reference][NOWAIT]
FORUPDATE子句用于在游标结果集数据上加行共享锁,OF子句可以确定哪些表要加锁,如果没有OF子句,则会在SELECT语句所应用的全部表上加锁。NOWAIT子句用于指定不等待锁。
为了更新或删除当前游标行数据,必须在UPDATE或DELETE语句中引用WHERE CURRENT OF子句
5、游标FOR循环
5.1、使用游标FOR循环
5.2、在游标FOR循环中直接使用子查询
begin
for emp in (select * from scott.emp) loop
dbms_output.put_line(emp.ename);
end loop;
end;
6、使用游标变量
当定义REFCURSOR类型是指定RETURN子句时,结果集的数据类型只能是RETURN的类型。
7、使用CURSOR表达式(游标中嵌套游标)
1、显示游标属性(只能判断当前状态)
当游标取值到末尾时,继续取值将取得最后缓存里的值。
2、使用游标
2.1、使用FETCH..BULK COLLECT INTO提取游标所有数据(不需要初始化集合大小)
2.2、使用FETCH..BULK COLLECTINTO..LIMIT提取部分数据
3、参数游标
DECLARE cursor_name(parameter_name datatype default value) ISselect_statement;
游标参数只能指定数据类型,不能指定长度。当不给参数时不需要括号。
4、使用游标更新或删除数据
语法:
CURSOR cursor_name(parameter_name datatype)
FORUPDATE子句用于在游标结果集数据上加行共享锁,OF子句可以确定哪些表要加锁,如果没有OF子句,则会在SELECT语句所应用的全部表上加锁。NOWAIT子句用于指定不等待锁。
为了更新或删除当前游标行数据,必须在UPDATE或DELETE语句中引用WHERE CURRENT OF子句
UPDATE table_name SET column=.. WHERE CURRENT OFcursor_name
DELETE table_name WHERE CURRENT OF cursor_name
5.1、使用游标FOR循环
5.2、在游标FOR循环中直接使用子查询
begin
end;
当定义REFCURSOR类型是指定RETURN子句时,结果集的数据类型只能是RETURN的类型。
7、使用CURSOR表达式(游标中嵌套游标)
八、开发子程序
1、函数和过程比较
1、函数和过程比较
过程 | 函数 | |
---|---|---|
语法: | CREATE [OR REPLACE] PROCEDURE procedure_name (argu1[moderl] type1,argu2 [moderl]type2,...) IS[AS] | CREATE [OR REPLACE] FUNCTION function_name (argu1[moderl] type1,argu2 [moderl]type2,...) RETURN datatype IS[AS] |
参数: | 相同,类型不能指定长度 | 相同,类型不能指定长度 |
区别: | 无需返回值 | 必须有返回值且调用时有限制 |
ALTER (PROCEDURE or VIEW or FUNCTION...) name COMPILE;
九、开发包
1、建立包
1.1、建立包规范
在包规范里定义的所有组件都是公有的
1.2、建立包体
包体用于实现包规范所定义的过程和函数,也可以单独定义私有组件
1.3、不同作用域可以定义相同变量,包的内部不能定义游标变量,但可以定义游标类型,也可以定义静态游标(普通游标)
2、包重载
2.1、函数重载时返回值的数据类型必须完全相同
2.2、判断是否重载参数名也要匹配,否则不是重载
3、包构造过程
当同一会话第一次调用包的公用组件时,会自动执行其构造过程,而后不会再调用构造过程。
4、纯度级别(限制公用函数)