Oracle PL/SQL编程

pl/sql(procedural   language/sql)是Oracle在标准的sql语言上的扩展。pl/sql不仅允许嵌入sql语言，还可以自定义变量和常量，允许使用条件语句和循环语句，允许使用例外处理各种错误。

（1）过程、函数、触发器是pl/sql编写。

（2）存储过程、函数、触发器是在Oracle中的

（3）pl/sql是非常强大的数据库过程语言

（4）存储过程和函数可以再Java程序中调用。

pl/sql编程分类：

块是最小的单位，可以编写存储过程、函数、触发器、包

1、编写规范

（1）注释

单行注释  --

多行注释  /*........*/

（2）标志符号的命名规范

①定义变量时，用v_作为前缀，v_sal

②定义常量时，用c_作为前缀，c_rate

③定义游标时，用_cursor作为后缀，emp_cursor

④定义例外时，用e_作为前缀，e_error

2、块（block）

块是pl/sql的基本程序单元，编写pl/sql程序实际上就是编写pl/sql块。要完成相对简单的应用功能，可能只需编写一个pl/sql块，但是要实现复杂的功能，可能需要在一个pl/sql块中嵌套其他的pl/sql块。

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

declear

/*定义部分——定义常量、变量、游标、例外、复杂数据类型*/

begin

/*执行部分——要执行的pl/sql语句和sql语句*/

exception

/*例外处理部分——处理运行的各种错误*/

end;

定义部分是从declare开始的，该部分可选；执行部分从begin开始，该部分必须；例外处理部分从exception开始的，该部分可选。

①定义部分和执行部分

declare 

v_ename  varchar2(5);

v_sal   number(7,2);

begin

select   ename,sal  into   v_ename,v_sal  from  emp  where   empno=&aa;

dbms_output.put_line('用户名：'|| v_ename ||'工资：'|| v_sal);

end;

②定义部分、执行部分和例外处理部分

declare 

v_ename  varchar2(5);

v_sal   number(7,2);

begin

select   ename,sal  into   v_ename,v_sal  from  emp  where   empno=&aa;

dbms_output.put_line('用户名：'|| v_ename ||'工资：'|| v_sal);

exception

when  no_data_found  then

dbms_output.put_lin('输入有误');

end;

3、存储过程

过程用于执行特定的操作。 当建立过程时，既可以指定输入参数（in）,也可以指定输出参数（out）.通过在过程中使用输入参数，可以将数据传递到执行部分，通过使用输出参数，可以将执行部分的数据传递到应用环境。

使用create  procedure来建立过程。

（1）创建存储过程

①只有执行部分

create   or  replace   procedure   pro1  is

begin

insert  into   mytest  values('scott','tiger');

end;

/

replace表示如果有pro1就替换

查看错误信息：show  error;

（2）调用存储过程

①exec  过程名（参数值1，参数值2....）；

②call   过程名（参数值1，参数值2....）；

eg:

create   procedure   pro2 (empName   varchar2,newSal  number)  is--只是表明类型，没必要表明大小

--在存储过程中定义变量，定义在is和begin之间

update emp  set  sal=newSal  where   ename=empName;

end;

/

（3）在Java程序中调用存储过程

<span style="font-family:Microsoft YaHei;font-size:14px;">try {//1.加载驱动Class.forName("oracle.jdbc.driver.OracleDriver");//2.得到连接Connection ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:MYORAL","scott","tigger");//3.创建CallableStatementCallableStatement cs = ct.prepareCall("{call pro(?,?)}");//给？赋值cs.setString(1, "SCOTT");cs.setInt(2, 10);//5.执行cs.execute();//关闭资源cs.close();ct.close();} catch (Exception e) {e.printStackTrace();}</span>

4、函数

函数用于返回特定的数据，只返回一个值。当建立函数时，在函数头部必须包含return子句，而在函数体内必须包含return语句返回的数据。使用create  function 来建立函数。

（1）建立函数

create  function  fun1(empName  varchar2) 

return  number  is yearSal number(7,2);--返回number,number名字是yearSal，类型是number（7,2）

begin

select  sal*12+nvl(comn,0)*12  into  yearSal  from  emp  where  ename=empName;

return  yearSal;

end;

（2）调用函数

①在sqlplus中调用

>var  income  number  // 定义一个变量

>call  fun1("SCOTT")  into :income;

②在Java程序中调用

select   fun1("SCOTT")  from   emp;

通过rs.getInt(1)得到返回结果。

5、包

包用于在逻辑上组合过程和函数，它由包规范和包体两部分组成

（1）使用create  package命令来创建包

eg：创建一个包package1,声明该包有一个过程update_sal,声明该包有一个函数anual_income

create   package  package1  is   

procedure    update_sal (name   varchar2,newsal  number);   --过程声明

function  anual_income(name  varchar2)  return  number;--函数声明

end;

包规范值包含了过程和函数的声明，但是没有过程和函数的实现代码。包体用于实现包规范中的过程和函数。

（2）建立包体使用create  package  body 命令，实现包规范中的函数和过程

create  or  replace  package   body package1  is

procedure  update_sal(name  varchar2,newsal  number)  is

begin

update  emp  set   sal = newsal  where  ename=name;

end;

function annual_income(name  varchar2)

return  number  is  annual_salary  number;

begin

select   sal*12+nvl(comm,0)  into  annual_salary  from emp

where ename = name;

return  annual_salary;

end;

end;

（3）调用包的过程或是函数

当调用包的过程或是函数时，在过程和函数前需要带有包名，如果要访问其他方案的包，还需要在包名前加方案名

exec  package1.update_sal('SCOTT',120);  //或是call

6、触发器

触发器是指隐含的执行的存储过程。用户不会主动调用。当定义触发器时，必须要指定出发的事件和触发的操作，常用的触发事件包括insert,update,delete语句，而触发操作实际上就是一个pl/sql块。

使用create  trigger来建立触发器。

7、定义并使用变量

在编写pl/sql程序时，可以定义变量和常量；在pl/sql程序中包括

（1）标量类型--scalar  只能存储单个数据

①的定义标量

在编写pl/sql块时，如果要使用变量，需要在定义部分定义变量。pl/sql中定义变量和常量的语法如下：

identifier  [constant]  datatype  [not null]  [:=|  default  expr]

identifier:名称

constant:指定常量。需要指定它的初始值，且其值是不能改变的。

datatype:数据类型

not  null:指定变量值不能为null

：= 给变量或是常量指定初始值

default： 用于指定初始值

expr：指定初始值的pl/sql表达式，可以使文本值、其它变量、函数等。

eg：定义一个小数并给一个初始值为5.4，即v_sal是常量。

v_sal  number(6,2):=5.1   //   :=是pl/sql的赋值号

定义一个布尔变量，不能为空，初始值为false

v_valid   boolean  not  null  defalt  false;

②使用标量

declare 

c_tax_rate  number(3,2):=0.03;

v_name vchar(5);

v_sal  number(7,2);

v_tax_sal  number(7,2);

begin

select  ename,sal  into  v_ename,v_sal  from  emp  where  empno=&no;

v_tax_sal:=v_sal * c_tax_rate;

dbms_output.put_lime(v_name || v_sal || v_tax_sal );

end;

③使用%type

场景：如上若v_name的大小超过了5个字符就会报错，它会按照数据库列来确定你定义的变量的类型和长度。

使用：标识符名   表名.列名%type；

v_name  emp.ename%type;     --和emp表的ename字段的大小和类型一样

（2）复合类型--composite   存放多个值的变量

①pl/sql记录

类似高级语言中的结构体（类）

declare

type   emp_record _type  is  record(

name  emp.ename%type,

salary  emp.sal%type,

title  emp.job%type);   --定义了一种emp_record_type记录类型

scott_record   emp_record_type;  --定义了一个scott_record变量，类型是上述的定义类型

begin

select   ename,sal,job  into  scott_record  from  emp  where  empno=7788;

dbms_output.put_line(emp_record.name);

end;

②pl/sql表

相当于高级语言中的数组。但是数组下标可以为负数，并且表元素的下标没有限制。

eg：

declare

type   table_type  is  table  of   emp.ename%type

index  by  binary_integer; --定义了一个pl/sql表类型table_type,该类型用于存放emp.ename%type这种类型的数据数组，index  by  binary_integer表示下标是整数，可为负数

scott_table   table_type;  --定义一个scott_table变量，类型是table_type

begin

select  ename  into  scott_table(0)  from  emp  where  empno=7788;

dbms.output.put_line(scott_table(0));

end;
③嵌套表--nested  table
④varray--变长数组

（3）参照类型--reference

指用于存放数值指针的变量。通过使用参照变量，可以使应用程序共享相同的对象，从而降低占用的空间。

参照变量包括游标变量（ref  cursor）和对象类型变量（ref  obj_type）两种类型。

①游标变量

使用游标时，当定义游标时不需要指定相应的select语句，但是当使用游标时（open）需要指定select语句，这样一个游标就与一个select语句结合了。

eg：

declare

type  emp_cursor  is  ref  cursor;  --定义游标类型

test_cursor  emp_cursor; --定义游标变量

v_ename  emp.ename%type;

v_sal  emp.sal%type;

begin

open  test_cursor  for  select  ename,sal  from  emp  where  deptno=&no;  //让游标指向结果集，即把test_cursor和一个select结合。

loop                    --循环取出存在变量中

fetch  test_cursor  into v_ename,v_sal;

//退出条件，判断test_cursor是否为空

exit  when  test_cursor%notfound;

dbms_output.put_line(v_ename||v_sal);

end  loop;

end;

（4）lob--large  object