oracle pl/sql

  pl/sql是什么？

  pl/sql（procedural language/sql）是oracle在标准的sql语言上的扩展，pl/sql不仅允许嵌入sql语言，还可以定义变量和常量，允许使用条件语句和循环语句，允许使用例外处理各种错误，这样使得他的功能变得更加强大，使用pl/sql我们可以轻松的完成复杂的查询要求

 

 学习pl/sql的知识铺垫： 

 <1.过程，函数，触发器是pl/sql编写

 <2.过程，函数，触发器是在oracle中的

 <3.pl/sql是非常强大的数据语言

 <4.过程，函数可以在java程序用调用

 

  pl/sql的优点

  <1.提高应用程序的运行性能

  <2.模块化的设计思想（分页的过程，订单的过程，转账的过程...）

  <3.减少网络传入量

  <4.提高安全性

 

  pl/sql的缺点

  移植性性不好

 

  pl/sql的第一个小例子

 创建表

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

 创建存储过程

 SQL> create procedure sp_test is
        begin
        insert into mytest values('lms','m123');
        end;
        /

 调用该过程的方式的两种方式

    <1 exec 过程名(参数值1，参数值2......)

    <2  call   过程名(参数值1，参数值2......)  //注意这种方式在过程名后一定要带括号

 

 

 pl/sql的编程基础单位是块

 简单的分类如下

 以块为基础主要可以编写四类东西：（存储）过程，函数，触发器，包

 

编写规范

<1注释

当行注释   --

select  * from emp where empno=7788;--取得员工信息

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

<2标识符号的命名规范

(1.当定义变量的时候，建议v_作为前缀 v_sal

(2.当定义常量的时候，建议用c_作为前缀 c_rate

(3.当定义游标的时候，建议用_cursor作为后缀 emp_cursor;

(4.当定义例外的时候，建议用e_作为前缀e_error

 

块

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

 

 块的结构示意图

pl/sql块由三个部分构成：定义部分，执行部分，例外处理部分

如下所示：

declare

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

begin

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

exception

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

end;

特别说明：

定义部分是从declare开始的，该部分是可选的

执行部分是从begin开始的，该部分是必须的

例外处理部分是从exception开始的，该部分是可选的

 

------输出示例------

set serveroutput on;--打开输出选项

begin
  dbms_output.put_line('hello,word');   --调用包dbms_output下的存储过程put_line(?)，这个类似于java中的类
end;

 

 -------变量示例------
declare
v_ename varchar(20);--定义变量
begin
select ename into v_ename from emp where empno=&empno;
dbms_output.put_line('用户名是'||v_ename);
end;

 

 

declare
v_ename varchar(20);--定义变量
v_sal number(7,2);
 begin
select ename,sal into v_ename,v_sal from emp where empno=&empno;
dbms_output.put_line('用户名是'||v_ename||'；薪水是'||v_sal);
end;

--&控制台参数输入符号

 

--------异常处理--------

为了避免pl/sql程序运行错误，提高pl/sql的健壮性，应该对可能的错误进行处理，这个很有必要

比如，<1.在上面的例子中，当输入了不存在的雇员号时，应当做例外处理

        < 2.有时出现异常，希望用另外的逻辑处理

相关说明：oracle预定义了一些异常，no_data_found表示找不到数据的例外

例如：

SQL> declare
   v_ename varchar(20);--定义变量
   begin
   select ename into v_ename from emp where empno=&empno;
   dbms_output.put_line('用户名是'||v_ename);
   ---异常处理
   exception
   when no_data_found
   then dbms_output.put_line('朋友，你的编号输入有误');
   end;
   /
 
结果：朋友，你的编号输入有误

 

 

 

 

 

过程

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

 

----------------传参的存储过程-------------
编写带参数的存储过程：

create procedure sp_updateemp(updatename varchar2,newsal number) is
begin
update emp set sal=newsal where ename=updatename;
end;

 

调用命名存储过程命令：exec sp_updateemp('SCOTT','1111');

 

函数

函数用于返回特定的数据，当建立函数时，在函数头部必须包含return字句，而在函数体内必须包含return返回的数据。我么可以用create function来建立函数，例如：

create function sp_fun1(spname varchar2) return number is

 yearsal number(7,2);
 begin
 --执行部分
 select sal*12+nvl(comm,0)*12 into yearsal from emp where ename=spname;
 return yearsal;
 end;
 ---执行方式--
--SQL> var abc number;
--SQL> call sp_fun1('SCOTT') into:abc;

--SQL> print abc;

 

 

包

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

<1可以使用create package命令来创建包:

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

eg:

create package sp_package is
procedure sp_updateemp(updatename varchar2,newsal number);   --存储过程
function sp_fun1(spname varchar2) return number ;            --函数
end;

 

<2建立包体可以使用create package body命令

create or replace package body sp_package is
procedure sp_updateemp(updatename varchar2,newsal number) is
begin
update emp set sal=newsal where ename=updatename;
end;
function sp_fun1(spname varchar2) return number is
yearsal number(7,2);
begin
select sal*12+nvl(comm,0)*12 into yearsal from emp where ename=spname;
return yearsal;
end;
end;
--包的调用
--调用包sp_package下的updateemp（）存储过程
--exec sp_package.sp_updateemp('SCOTT',1200); 

 

触发器

  触发器是指隐含的执行的存储过程，当定义触发器时，必须要指定触发的事件和触发的操作，常用的触发事件包括insert,update,delete语句，而触发器操作实际就是一个pl/sql块。可以使用create  trigger来建立触发器

特别说明:

我们会在后面

 

 

 

----------标量--------

eg:根据员工编号输出雇员名称，工资，个人所得税

 declare
 c_taxrate number(3,2):=0.03;    --税率,赋值用:=
 v_ename emp.ename%type;           --雇员姓名, 此种定义的方式是使v_ename的大小随着emp表中ename的大小而变化
 v_sal number(7,2);              --工资
 v_taxsal number(7,2);           --个人所得税
 begin
 select ename,sal into v_ename,v_sal from emp where empno=&no;
 v_taxsal:=c_taxrate*v_sal;
 dbms_output.put_line('员工姓名是:'||v_ename||'  个人工资是：'||v_sal||'  个人所得税是:'||v_taxsal);
 end; 结果是：员工姓名是:SCOTT  个人工资是：1200  个人所得税是:36

 

复合变量（composite）

用于存放多个值的变量，主要包括几种：

<1. pl/sql记录

<2.pl/sql表

<3.嵌套表

<4.varray

 

--记录类型--

类似于c语言中的结构体

eg：

 --定义一个pl/sql记录类型，类型包含三个数据v_name，v_sal，v_job
 declare
 type emp_record_type is record(
 v_name emp.ename%type,
 v_sal  emp.sal%type,
 v_job emp.job%type);
 --定义了一个sp_record变量，该变量类型是emp_record_type
 sp_record emp_record_type;
 begin
 select ename,sal,job into sp_record from emp where empno=&no;  --将查询到的值一次性赋值到 sp_record 中
 dbms_output.put_line('雇员姓名是：'||sp_record.v_name);
 end;

 

 --复合类型

类似于高级语言中的链表

 --pl/sql表实例
declare
--定义了一个pl/sql表类型sp_table_type，该类型用于存放emp.ename%type类型的数据
--index by binary_integer表示下标是整数
type sp_table_type is table of emp.ename%type index by binary_integer;
--定义了sp_table_type类型的变量sp_table
sp_table sp_table_type;
begin
select ename into sp_table(2) from emp where empno=7788;
dbms_output.put_line('雇员姓名是：'||sp_table(2));
end;
--注意取得的可以随意插入到table中的任何地方

 

参照变量

参照变量是指用于存放数值指针的变量，通过使用参照变量，可以使得应用程序共享相同对象，从而降低占用的空间，在编写pl/sql程序时，可以使用游标变量（ref cursor）和对象类型变量（ref  obj_type）两种参照变量类型

declare
--定义游标类型sp_emp_cursor
type sp_emp_cursor is ref cursor;
--定义一个游标变量test_cursor
test_cursor sp_emp_cursor;
--定义变量
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
--把teset_cursor和一个select结合
open test_cursor for select ename ,sal from emp where deptno=&no;
--循环取出
loop
    fetch test_cursor into v_ename,v_sal;
    exit when test_cursor%notfound;
    dbms_output.put_line('雇员姓名是：'||v_ename||'薪资是：'||v_sal);
end loop;   
close test_cursor;
end;

 

-----if语句；if--then-----
create or replace procedure sp_pro6(spName varchar2) is
v_sal emp.sal%type;
begin
   select sal into v_sal from emp where ename=spName;
if v_sal<2000
then update emp set sal=sal*1.1 where ename=spName;
end if;    --不能掉了这里
end;

-------二重if语句-if--then--else---
create or replace procedure sp_pro6(spName varchar2) is
v_comm emp.comm%type;
begin
   select comm into v_comm from emp where ename=spName;
if(v_comm<>0)then
 update emp set comm=comm+100 where ename=spName;
else
update emp set comm=comm+200 where ename=spName;
end if;
end;

----------多重条件分支-if--then--elsif---else---
create or replace procedure sp_pro7(spEmpno number) is
v_job emp.job%type;
begin
select job into v_job from emp where empno=spEmpno;
if v_job='PRESIDENT'then
update emp set sal=sal+1000 where empno=spEmpno;
elsif v_job='MANAGER'then
update emp set sal=sal+500 where empno=spEmpno;
else
update emp set sal=sal+200 where empno=spEmpno;
end if;
end;

-----------循环语句loop 至少执行一回--相当与while-----
create table Users(
uno  number(3),
uname varchar2(10)
);

create or replace procedure sp_user(spUname varchar2) is
v_num Users.Uno%type:=1;
begin
loop
insert into Users values(v_num,spUname);
v_num:=v_num+1;
exit when v_num=11;     --退出时的判断语句
end loop;
end;

-------------while循环--先判断再循环---相当于do-while-------------
create or replace procedure sp_user(spUname varchar2) is
v_num Users.Uno%type:=11;
begin
while v_num<=20 loop
--执行
  insert into Users values(v_num,spUname);
   v_num:=v_num+1;
end loop;
end;

---------------for 循环-------------
create or replace procedure sp_user(spUname varchar2) is
begin
for i in  21..30 loop         --如果想从30到20反过来插入则在in后面加关键字 reverse
--执行
  insert into Users values(i,spUname);
end loop;
end;
---------------goto语句-------一般不常用-----------

---------------null语句---null语句不会执行任何操作，并且会直接将控制传递到下一条语句-，使用null语句主要是为了提高代码的可读性-----------------
create or replace procedure sp_pro6(spName varchar2) is
v_sal emp.sal%type;
begin
   select sal into v_sal from emp where ename=spName;
if v_sal<2000
then update emp set sal=sal*1.1 where ename=spName;
else
null;
end if;    --不能掉了这里
end;