Oracle之pl/sql编程（一）函数，过程，包

pl/sql是什么？

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

作用：过程，函数，触发器是用pl/sql编写的且存在于在oracle中，可以在java程序中调用，pl/sql强大的数据库过程语言。

1、创建一个表：

SQL> create table  mytest(name varchar2(30),passwd  varchar2(30));

2、创建过程

SQL> create procedure  sp_prol  is   

begin

--执行部分

insert  into  mytest（'hjadsfhas'，'sdkhfdsj'）；

end;

/

也可以使用（replace 表示如果有sp_prol就替换）

SQL> create   or  replace procedure  sp_prol  is
  2  begin
  3  --执行部分
  4  insert  into  mytest('hjadsfhas','sdkhfdsj');
  5  end;
  6  /

show  error ————————查看错误

正确的过程创建

SQL> create  or  replace  procedure sp_prol is 
  2  begin
  3  insert  into mytest values('hansjd','2sduf');
  4  end;
  5  /
 
Procedure created

3、执行过程

exec  过程名；

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

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

4、删除过程

drop procedure  过程名

使用pl/sql编写模块时，pl/sql的基础单位是块 ,其包括过程（存储过程），函数，触发器，包

编写规范：

①注释

单行注释  — —

多行注释/*     */

②标识符号的命名规范

1、当定义变量时，建议使用v_为前缀，

2、定义常量时，建议使用c_为前缀

3、当定义游标时，使用_cursor作为后缀

4、定义例外时，使用e_为 前缀  e_error

图1

实例1—之包括执行部分的pl/sql块

  set  serveroutput  on  ——打开输出选项

begin 

dbms_output.put_line('hello');

end;

dbms_output是oracle所提供的包（类似于java的开发包），该包包含一些过程，put_line就是dbms_output包的一个过程。

案例：一

只包括执行部分的pl/sql块

 
SQL> begin
  2  dbms_output.put_line('hello  world!');
  3  end;
  4  /
 
PL/SQL procedure successfully completed

当打开输出选项时：（就会在下面输出内容）

SQL> set serveroutput on;
SQL> begin 
  2  dbms_output.put_line('你好，智障！0_0');
  3  end;
  4  /
 
你好，智障！0_0
 
PL/SQL procedure successfully completed

案例：二

包含定义部分和执行部分的pl/sql

SQL> declare
  2  v_ename  varchar2(5);
  3  begin
  4  select ename into v_ename  from  emp  where  empno =&no;
  5  dbms_output.put_line('雇员名'||v_ename);
  6  exception 
  7  when  no_data_found  then 
  8  dbms_output.put_line('异常');
  9  end;
 10  /
 
雇员名SCOTT
 
PL/SQL procedure successfully completed

SQL> declare
  2  v_ename  varchar2(5);--定义字符串常量
  3  begin
  4           select ename into v_ename  from  emp where  empno=&no;--将查询的值传给v_ename  
  5           dbms_output.put_line('雇员名'||v_ename);--字符串连接||
  6  end;
  7  /

&:表示要接受从控制台输入变量

过程

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

实例：①可以输入雇员名，可以修改雇员的工资，新工资

PL/SQL procedure successfully completed
 
SQL> create procedure sp_pro3(spName  varchar2,newSal  number)  is 
  2  begin
  3  update emp set  sal = newSal  where ename = spName;
  4  end;
  5  /
Warning: connection was lost and re-established
 
Procedure created

 
SQL> exec sp_pro3('SCOTT',4768);
 
PL/SQL procedure successfully completed

函数

用于返回特定数据，当建立函数时，在函数头部必须包含return子句，而在函数体内必须包含return语句返回的数据。

使用create  function  来建立函数，实际案例：

SQL> create  function sp_fun2(spName  varchar2)  return  
  2  number  is  yearSal  number(7,2);
  3  begin
  4  --执行部分
  5  select sal*12+nvl(comm,0)  into yearSal   from emp  where ename = spName;
  6  return yearSal;
  7  end;
  8  /

创建一个函数名为sp_fun2 ，输入sp_Name（形参），返回一个number  叫yearSal  类型为number（7,2）；

函数的调用

SQL> var abc number;
SQL> call sp_function('SCOTT')  into:abc;
SQL>print abc

包

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

①使用create   package 命令来创建包：

SQL> create package sp_package is 
  2  procedure update_sal(name  varchar2,newsal  number);
  3  function annual_income(name  varchar2)  return  number;
  4  end;
  5  /

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

②建立包体：

使用create  package  body  命令

SQL> create   or replace package body sp_package is
  2   procedure  update_sal(name  varchar2,newsal number)
  3   is
  4   begin
  5   update emp  set sal=newsal where ename = name;
  6   end;
  7   function annual_income(name varchar2)
  8   return number  is
  9   annual_salary number;
 10   begin
 11   select  (sal+nvl(comm,0))*12   into annual_salary  from emp  where ename=name;
 12   return annual_salary;
 13    end;
 14    end;
 15   /
 
Package body created
 
SQL> 

包的执行：

SQL> exec sp_package.update_sal('SCOTT',120);
 
PL/SQL procedure successfully completed
 
SQL>