oracle——基础知识（二）未完
1、select 查询
单独的一条 select 语句 可以一次查询多条记录；但是在 pl/sql 块中，select语句只能 使用INTO子句；必须并且只能返回一行
2、EXECUTE IMMEDIATE。解析并马上执行动态的SQL语句或非运行时创建的PL/SQL块。
注：
1. EXECUTE IMMEDIATE将不会提交一个DML事务执行，应该显式提交事务，如果通过EXECUTE IMMEDIATE处理DDL命令,它提交所有以前改变的数据
2. 不支持返回多行的查询。
3. 当执行SQL语句时，不要用分号，当执行PL/SQL块时，在其尾部用分号；
4.举例：execute immediate 'select count(1) from tempA'；
3、存储过程
定义在包内的存储子程序称为打包子程序；单独定义的存储子程序称为独立子程序；而在另外一个子程序或PL/SQL块内定义的存储子程序称为本地子程序，这样的子程序不能被其他应用程序调用，只供本地使用。
| in 类型参数 | out 类型参数 | in out 类型的参数 | |
| 值 | 传递给子程序 | 返回给调用环境 | 传递给子程序，返回给调用环境 |
| 参数形式 | 常量 | 未初始化的变量 | 初始化的变量 |
| 使用时 | 默认类型 | 必须明确指定 | 必须明确指定 |
4、函数
格式：
create or replace function 函数名(参数1 参数类型,……) return 返回值类型
as
返回值变量 类型;
begin
……
return 返回值变量;
end;
- 只能使用in模式参数传入参数值.
- 必须要有返回类型,并且返回值必须要使用.
5、触发器
格式：
Create [or replace] trigger 触发器名
Before|after insert|delete|(update of 列名)
On 表名
[for each row]
begin
……
end;
For each row的意义是：在一次操作表的语句中，每操作成功一行就会触发一次；不写的话，表示是表级触发器，则无论操作多少行，都只触发一次；
触发器用到的表名 :new 、:old
| 特性 | insert | update | delete |
| :old | null | 有效 | 有效 |
| :new | 有效 | 有效 | null |
6、游标
分为 隐式游标、显式游标、动态游标；用到的属性
| 属性 | 类型 | 描述 |
| %isopen | boolean | true：游标打开；false：游标未打开 |
| %notfound | boolean | true：未提取到记录；false：提取到记录 |
| %found | boolean | true：提取到记录；false：未提取到记录 |
| %rowcount | number | 到目前为止，SQL语句通过游标提取到的总行数 |
SQL语句中 insert、delete、update 语句即是通过隐式游标完成的，它自动打开、自动关闭；
(1)显式游标：
- 格式：
DECLARE
变量 变量类型;
CURSOR 游标名 IS select语句; --1、声明游标
BEGIN
OPEN 游标名; --2、打开游标
LOOP
FETCH 游标名 INTO 变量; --3、从游标中循环提取数据
EXIT WHEN 游标名%NOTFOUND;
……
END LOOP;
CLOSE 游标名;--4、关闭游标
END;
- 通过使用For循环来读取游标中的数据
格式：
for 表示记录行的变量 IN 游标 loop
……
end loop
- 声明带参数的游标：
格式：CURSOR 游标名(参数名 类型) IS 使用参数的select语句; 打开游标 OPEN 游标名(参数值);
- 可以通过游标来更新、删除数据，只需在声明游标时按如下设置：
格式：CURSOR 游标名 IS select语句 for update; --for update 表示该游标可以用于更新、删除数据。
获取游标数据后：update 表名 set 字段名 = 变量*1.1 WHERE CURRENT OF 游标名； --WHERE CURRENT OF 游标名； 表示游标当前指向的那行记录。
例：
create or replace procedure temp_1(deptid number) is
emp_record emp%rowtype; --声明行类型
CURSOR emp_cursor IS SELECT * FROM emp where deptno = deptid for update; --声明可以修改、删除数据的游标
begin
for emp_record in emp_cursor loop
dbms_output.put_line(emp_record.sal||' '||emp_record.ename);
update emp set sal = emp_record.sal*1.1 WHERE CURRENT OF emp_cursor;--通过游标更改数据
dbms_output.put_line(emp_record.sal||' '||emp_record.ename);
end loop;
end temp_1;
(2)引用类型游标：可以向客户端返回结果集
声明类型格式： type 类型名 is ref cursor;
声明变量格式：变量名 变量类型;
7、包：是由存储在一起的相关对象组成的PL/SQL存储程序；由包头和包体两个独立的部分组成，需要分别定义；
包头与包体的关系：
- 在包头声明过的是外部可以调用的过程、函数、变量；只在包体内定义而未在包头声明的的只能在包体内可见；
- 在包中声明的时候，必须是前向声明的，也就是对象需要引用的另一个对象必须在前面首先声明；
- 包头是必须存在的，包体是可选的；
- 包头必须在包体前创建，否则创建不了包体；
- 只在包头中包括公用变量、过程、函数的声明；
- 包头：包头主要包含了包中出现的过程、函数的声明，但是不包括实际的代码部分；
格式：
CREATE [OR REPLACE] PACKAGE package_name{IS | AS}
type_definition -- 类型定义
procedure_specification -- 过程定义
function_specification --函数定义
variable_declaration --变量定义
cursor_declaration -- 游标定义
END [ package_name];
例：
第一步：创建一个包头，包含一个普通的变量和一个过程。
CREATE OR REPLACE PACKAGE mypackage_1
is
a number := 1;
procedure mypro(x number,y number,z out number);
function myfunc(x number)return number;
END mypackage_1;
第二步：使用包中定义的变量。
declare
begin
dbms_output.put_line(mypackage_1.a);
mypackage_1.a := 2;
dbms_output.put_line(mypackage_1.a);
end;
第三步：重新启动sql*plus，然后输出mypackage_1.a的值。此时会发现，仍然是1。
- 包体：是真正的过程、函数的执行部分定义
CREATE OR REPLACE PACKAGE body mypackage_1
is
b number := 2;
procedure mypro(x number,y number,z out number)
is
b number := 4;
begin
z := x*y;
end;
function myfunc(x number)return number
is
x1 number;
begin
x1 := b*x;
return x1;
end;
function myfunc1(x number)return number
is
x1 number;
begin
x1 := b*x;
return x1;
end;
END mypackage_1;
第二步：在sql*plus里面调用使用包。
分别输出包头中定义的变量，包体中定义的变量。
begin
dbms_output.put_line(mypackage_1.a);
dbms_output.put_line(mypackage_1.b);
end;
结果：不能使用b,因为b是包体中定义的，不能在外部访问。
调用myfunc/myfunc1函数。
declare
begin
dbms_output.put_line(mypackage_1.myfunc1(2));
end;
结果：无法调用myfunc1，只能使用在包头中定义好的过程，函数等，在只在包体中定义的函数或过程等相当于private 只能在包内部使用。
8、返回多条记录：
- 创建包头；
- 包头中用 type 声明引用类型游标；
- 包头中声明带有 out 关键字的引用类型；
- 在包体中用open for 打开引用游标；
第一步：创建包头
create or replace package testpage_1 is
type my_cursor is ref CURSOR;--声明引用游标类型
procedure mypro(arr out my_cursor);--声明过程
end testpage_1;
第二步：创建包体
create or replace package body testpage_1 is
procedure mypro(arr out my_cursor)
is
begin
open arr for SELECT * FROM employees;
end;
end testpage_1;
注：ref cursor是Oracle旧版本中使用的数据类型，用这种对象的缺点在于必须在包中定义；而现在用 sys_refCursor类型，可以直接使用。
