Oracle基础（四）pl/sql

PL/SQL也是一种程序语言，叫做过程化SQL语言（Procedural Language/SQL）。PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点，所以PL/SQL就是把数据操作和查询语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算的程序语言。

总结下来就是是sql语言的扩展，sql语句+ 变量和常量+条件语句+循环语句+例外处理各种错误!

PL/SQL的作用

使用PL/SQL可以编写具有很多高级功能的程序，虽然通过多个SQL语句可能也能实现同样的功能，但是相比而言，PL/SQL具有更为明显的一些优点：

⒈能够使一组SQL语句的功能更具模块化程序特点；

⒉采用了过程性语言控制程序的结构；

⒊可以对程序中的错误进行自动处理，使程序能够在遇到错误的时候不会被中断；

⒋具有较好的可移植性，可以移植到另一个Oracle数据库中；

⒌集成在数据库中，调用更快；

⒍减少了网络的交互，有助于提高程序性能。

通过多条SQL语句实现功能时，每条语句都需要在客户端和服务端传递，而且每条语句的执行结果也需要在网络中进行交互，占用了大量的网络带宽，消耗了大量网络传递的时间，而在网络中传输的那些结果，往往都是中间结果，而不是我们所关心的。

而使用PL/SQL程序是因为程序代码存储在数据库中，程序的分析和执行完全在数据库内部进行，用户所需要做的就是在客户端发出调用PL/SQL的执行命令，数据库接收到执行命令后，在数据库内部完成整个PL/SQL程序的执行，并将最终的执行结果返馈给用户。在整个过程中网络里只传输了很少的数据，减少了网络传输占用的时间，所以整体程序的执行性能会有明显的提高。

pl/sql基础

接下来主要介绍下用pl/sql编写的在块的基础上编写过程，函数，包以及pl/sql进阶的三大控制语句下篇介绍 视图,触发器,以及分页的存储过程

块结构示意图

Pl/sql块由定义，执行，例外处理部分组成

Declear 定义常量，变，游标，例外，复杂数据类型

Begin 执行

Exception 例外处理

End;

例如：

Declare

V_ename varchar2(5);定义字符串变量

V_sal number(7,2);

Begin

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

Dbms_output.put_line(‘雇员名：’||v_ename ||’工资：’|| v_sal);

--异常处理

Exception

When no_date_found thendbms_output.put_line(‘朋友，输入错误’)；

End

使用Sqlplus开发工具:Pl/sql develper独立工具

(一)1、创建一个简单的表

Createtable mytest(name varchar2(30),passwd varchar2(30));

2、创建过程

Create or replace procedure sp_prol is

Begin

--执行部分

Insert into mytest values(‘韩顺平’，‘m1234’);

End;

3、调用

Exec 过程名（参数）

Call过程名（参数）

 

无返回值的存储过程

Create procedure sp_pro3（spnamevarchar2,newSal number）is

Begin

--执行部分，根据用户名去修改工资

Updateemp set sal= newsal where ename=spName;

End;

(二) 函数

Createfunction sp_fun2(spName varchar2) return

Number isyearSal number(7,2);

Begin

--执行部分

Selectsal*12+nvl(comm,0)*12 into yearSal from emp where ename =spName;

ReturnyearSal;

End;

调用函数

Sql varincome number

call annual_income(‘scott’) from into:income;

Sql>printincome

Java程序中通过rs.getInt（1）得到返回的结果

Sql>showerorr--显示错误

（三）包

--创建包

--创建一个包sp_package

__声明了该包里有一个过程update_sal

---声明一个函数

Createpackage sp_packge is

procedureupdate_sal(name varchar2,newsal number);

Functionannual_income(name varchar2) reture number;

End ;

创建包体

Createpackage body sp_package is

Procedure

Function

Begin

Select

Return

End

End

调用

Sql>call sp_package.function （procedule）

Pl/sql进阶

三种条件分支

If then

Create or replace procedure sp_pro6(spName varchar2) is

--定义

v_sal smp.sal%type;

Begin

--执行

Select sal into v_sal from empwhere ename=spName;

--判断

If v_sal<2000 then

Update empset sal=sal-sal*10% where ename =spName;

End if;

End;

调用

Sql> execsp_pro6('scott') scott为用户名

二重条件分支 if- then -else

Create or replace proceduresp_pro6(spName varchar2) is

- -定义

v_sal smp.sal%type;

Begin

--执行

Select sal into v_sal from empwhere ename=spName;

--判断

If v_com<>0 then

Update empset comm=comm+100 where ename =spName;

Else

Update empset comm=comm+200 where ename=spName;

End if;

End;

调用

Sql> execsp_pro6('scott') scott为用户名

多重条件分支 if-then-elseif --else

如果该员工的职位是president,就给他的工资增加1000，如果该员工的职位是manager就给他的工资增加500 ，其他职位的员工增加200

Create orreplace procedure sp_pro6(spNo number) is

--定义

v_job emp.job%type;

Begin

--执行

Selectjob into v_job from emp where empno=spNo;

If v_job='president' then

Update emp set sal=sal + 1000 where empno=spNo;

Elseif v_job='manager ' then

Update empset sal=sal+500 where empno-spNo;

Else

Update empset sal =sal+200 where empno=spNo;

End if ;

End;

调用 sql>Execsp_pro6(7839)

循环语句

Loop End loop 至少执行一次，先循环再判断

create or replace proceduresp_pro6 (spName varchar2) is

--定义 ：= 表示赋值

v_num number:=1;

Begin

Loop

insert into users values (v_num，spName);

--判断是否要退出循环

exit when v_num=10;

--自增

v_num :=v_num+1

End loop;

End;

循环语句 while循环

先判断后循环

create or replace proceduresp_pro6 (spName varchar2) is

--定义 ：= 表示赋值

v_num number:=11;

Begin

While v_num <=20 loop

--执行

insert into users values (v_num，spName);

--自增

v_num :=v_num+1

End loop;

End;

for循环

Begin

For I inreverse 1….10 loop

Insert into users values(I,'顺平');

End loop;

End;不建议使用

顺序控制语句

Goto 建议不用 循环嵌套不要超过三层

用于跳转到特定标号去执行 语句，注意用于使用go to增加复杂性，可读性差

Declare

i int :=1;

Begin

Loop

dbms_output.put_line('输出i=' || i);

if I =10 then

got end_loop;

end if;

i:=i+1;

end loop;

<>

--<<>>goto 标号

Dbms_output.put_line('循环结束')；

End;

Null

不会执行任何操作，将控制传递下一句

提高可读性

Declare

v_salemp.sal%type;

v_enameemp.ename%type;

Begin

Select ename,sal into v_ename,v_val

From empwhere empno=%no;

If v_sal<3000 then

Update empsel comm =sal*0.1 where ename=v_ename;

Else

Null;

End if ;

End;

pl/sql出于sql，所以很大一部分沿袭了sql,之前sql server的学习中没有这么深入的接触，这次在pl/sql里面学到的东西更多，学习就这样从不同的角度看问题，然后全面了解它。