oracle 笔记

块结构示意图：

Pl/sql块由三个部分构成：定义部分、执行部分、例外处理部分。

如下所示：
declear（可选）
定义部分—-定义常量、变量、游标、例外、复杂数据类型

begin（必须）
执行部分----要处理的pl/sql语句和sql语句

exception（可选）
例外处理部分-----处理运行的各种错误

自定义函数

create or replace function function_name
　　(
　　argu1 [mode1] datatype1,
　　argu2 [mode2] datatype2, ……..
　　)
　　return datatype
　　is/as
begin
funbody
　　end;

例：
create or replace function sp_fun2(v_ename in varchar2) return number is
v_yearSal number(7,2);
begin
–执行部分
selectsal*12 into v_yearSal from mytest where ename = v_ename;
returnv_yearSal;
end sp_fun2;

执行：
select sp_fun2(‘xiaowang’) from dual;

存储过程：

create or replace procedure proc_test_exit_when is
i number;
begin
i:=0;
loop
exit when(i>5);
dbms_output.put_line(i);
i:=i+1;
end loop;
endproc_test_exit_when;

执行：
call proc_test_exit_when();(sql视图中)
execproc_test_exit_when;

包

包用于在逻辑上组合过程和函数，它由包规范和包体两部分组成。
1）我们可以使用create package命令来创建包。
实例：
create or replace package PKG_TEST is
procedure PROC_TEST_FOR;
function sp_fun2(v_ename varchar2) return number;
end;
包的规范只包含了过程和函数的说明，但是没有过程和函数的实现代码。包体用于实现规范中的过程和函数

2）建立包体可以使用create package body命令
create or replace package body PKG_TEST is
procedureproc_test_for is
begin
for ii in 1..5 loop
dbms_output.put_line(ii);
end loop;
endproc_test_for;

function sp_fun2(v_ename varchar2) return number is
v_yearSal number(7,2);
begin
–执行部分
selectsal*12 into v_yearSal from mytest where ename = v_ename;
returnv_yearSal;
end sp_fun2;
end;
3）如何调用包的过程或是函数
当调用包的过程或是函数时，在过程和函数前需要带有包名，如果要访问其它方案的包，还需要包名前加方案名。
如：

调用包中的存储过程：
execpkg_test.PROC_TEST_FOR();

调用包中的函数：
select pkg_test.sp_fun2(‘xiaoming’) from dual;

特别说明：
包是pl/sql中非常重要的部分，我们在使用过程从页时，将会再次体验它的威力。

数据类型

表类型变量table

语法如下：
type 表类型 is table of 类型 index by binary_integer;
表变量名 表类型;
类型可以是前面的类型定义，index by binary_integer子句代表以符号整数为索引，这样访问表类型变量中的数据方法就是“表变量名(索引符号整数)”。table类型，相当于java中的Map容器，就是一个可变长的数组，key（符号整数索引）必须是整数，可以是负数，value（类型）可以是标量，也可以是record类型。可以不按顺序赋值，但必须先赋值后使用。

1. 定义一维表类型变量

   ―――――――――――――――――――――――――――――――――――――
   declare
   type t_tb is table of varchar2(20) index by binary_integer;
   v_tbt_tb;
   begin
   v_tb(100):=’hello’;
   v_tb(98):=’world’;
   dbms_output.put_line(v_tb(100));
   dbms_output.put_line(v_tb(98));
   end;
   类型为record的表类型变量
   declare
   type t_rd is record(id number,name varchar2(20));
   type t_tb is table of t_rd index by binary_integer;
   v_tb2 t_tb;
   begin
   v_tb2(100).id:=1;
   v_tb2(100).name:=’hello’;
   –dbms_output.put_line(v_tb2(100).id);
   –dbms_output.put_line(v_tb2(100).name);
   dbms_output.put_line(v_tb2(100).id||’ ‘||v_tb2(100).name);
   end;

2、%type

使用%type定义变量，为了让PL/SQL中变量的类型和数据表中的字段的数据类型一致，Oracle 9i提供了%type定义方法。这样当数据表的字段类型修改后，PL/SQL程序中相应变量的类型也自动修改。
―――――――――――――――――――――――――――――――――――――
create table student(
id number,
name varchar2(20),
age number(3,0)
);
insert into student(id,name,age) values(1,’susu’,23);
–查找一个字段的变量
declare
v_name varchar2(20);
v_name2 student.name%type;
begin
select name into v_name2 from student where rownum=1;
dbms_output.put_line(v_name2);
end;
–查找多个字段的变量
declare
v_idstudent.id%type;
v_namestudent.name%type;
v_agestudent.age%type;
begin
select id,name,age into v_id,v_name,v_age from student where rownum=1;
dbms_output.put_line(v_id||’ ‘||v_name||’ ‘||v_age);
end;

3、%ROWTYPE

DECLARE
TYPE emp_table_type IS TABLE OF my_emp%ROWTYPE INDEX BY BINARY_INTEGER;
v_emp_table emp_table_type;
BEGIN
SELECT * BULK COLLECT INTO v_emp_table FROM my_emp WHERE deptno=&deptno;
FOR i IN 1..v_emp_table.COUNT LOOP
dbms_output.put_line(‘EMPLOYEE_INFO:’||v_emp_table(i).ename||
‘,’||v_emp_table(i).job||
‘,’||v_emp_table(i).hiredate);
END LOOP;
END;

说明部分:
1.DECLARE 说明以下你要声明的部分
2.Type 声明是类型 emp_table_type 类型的名字
3.IS TABLE OF 指定是一个集合的表的数组类型, 简单的来说就是一个可以存储一列多行的数据类型 ,my_emp指出在哪个表上( 存在的表 ) %ROWTYPE指在表上的行的数据类型.
4.INDEX BY BINARY_INTEGER 指索引组织类型
5.v_emp_table定义一个变量来存储集合数据类型
6.BULK COLLECT INTO 指是一个成批聚合类型, 简单的来说 , 它可以存储一个多行多列存储类型 ,into 后面指定从哪里来 ,
7.v_emp_table.COUNT用来v_emp_table里面的数量
8.(i)表示下标号