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块内定义的存储子程序称为本地子程序，这样的子程序不能被其他应用程序调用，只供本地使用。

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

6、游标

分为 隐式游标、显式游标、动态游标；用到的属性

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类型，可以直接使用。