pl/sql编程基础

PL/SQL

1、过程、函数、触发器是pl/sql编写的

2、过程、函数、触发器是存放在oracle数据库中的

3、pl/sql是非常强大的过程化语言

4、过程、函数、触发器可以在java程序中调用

pl/sql编写能节省一点时间就是提高了性能（量大），java直接调用数据库存放的过程，解析时间就节省下来了，提高了性能

模块化的设计思想-----》存储过程

网络传输（java程序中编写的sql语言），直接调用数据库的过程节省了传输量

提高安全性（存储过程避免了数据库信息的泄漏）

缺点：

移植性不好

pl/sql编程基本单位是块，通过块可以编写出过程、函数、触发器、包

下面进行一个最基本的编程

案例：向某表中插入一条数据

create or repalce procedure 名称 is :replace表示如果名称已存在，就替换

begin

insert into test values ('xiaoming','redhat')

end;

/

create procedure创建存储过程关键字

or repalce：表示如果名称已存在，就替换

is：也是关键字

存储过程定义的头和begin之间是定义部分（定义变量常量等等），后面提到

begin：关键字

end：结束符

begin与end结束之间就是执行部分

上面一个简单的存储过程是向某表中添加一条数据，现在先创建一张表

SQL> create table names(name varchar2(20),password varchar2(30));

Table created.

然后通过编写一个存储过程向其中添加数据

SQL> create or replace procedure sp_pro1 is

  2  begin

  3  insert into names values ('xiaoming','redhat');

  4  end;

  5  /

Procedure created.

存储过程已建立，该如何执行呢？使用关键字exec或者call，如下

SQL> exec sp_pro1;

PL/SQL procedure successfully completed.

然后查询表，看是否添加了数据

SQL> select * from names;

NAME                 PASSWORD

-------------------- ------------------------------

xiaoming             redhat

当若编写的过程有误时，可以通过show error这条命令查看错误具体信息

pl/sql编程是由最小单位块组成的，看看块的组成部分

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

declare

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

begin

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

exception

例外处理部分，处理运行的各种错误

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

执行部分从begin开始，必选

例外处理部分从exception，可选

下面编写一条最简单的块，输出hello world

编写之前打开系统的屏幕输出信息，不然看不到效果

SQL> set serveroutput on;

SQL> begin

  2  dbms_output.put_line('hello world');

  3  end;

  4  /

hello world

PL/SQL procedure successfully completed.

最简单的块编程，只有执行部分，而且只输出了一条信息hello world。

相关说明：dbms_output是oracle所提供的包（类似java的开发包），该包包含一些过程，put_line就是

dbms_output包的一个过程（包里面的一个过程）

实例2：包含定义部分和执行部分

SQL> declare 

  2  v_name varchar2(20);    改行表示定义的变量，变量名v_name，数据类型为varchar2

  3  begin

  4  select ename into v_name from emp where empno=&empno;  &由键盘输出

  5  dbms_output.put_line(v_name);

  6  end;

  7  /

Enter value for empno: 7788

old   4: select ename into v_name from emp where empno=&empno;

new   4: select ename into v_name from emp where empno=7788;

SCOTT        输出到屏幕的信息v_name

PL/SQL procedure successfully completed.

实例3：包含定义部分、执行部分和例外处理部分

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

相关说明：oracle事先预定义了一些例外，no_data_found就是找不到数据的例外

如上述例子，如果输入烦人不是emp表中的empno号码，那么将会报错，报错该如何处理呢，这里就定义例外部分，交给他处理

SQL> declare 

  2  v_name varchar2(20);

  3  v_sal number(7,2);

  4  begin

  5  select ename,sal into v_name,v_sal from emp where empno=&empno;

  6  dbms_output.put_line(v_name||' '||v_sal);

  7  exception             定义例外关键字exception

  8  when no_data_found then    当查询不到数据时，采取措施打印error

  9  dbms_output.put_line('error');

 10  end;

 11  /

Enter value for empno: 78     78并不在emp表中的empno好中

old   5: select ename,sal into v_name,v_sal from emp where empno=&empno;

new   5: select ename,sal into v_name,v_sal from emp where empno=78;

error       打印error

过程

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

SQL> --过程编写

SQL> create or replace procedure sp_pro1(name varchar2,pass varchar2) is 

  2  begin

  3  insert into names values (name,pass);

  4  end;

  5  /

Procedure created.

sp_pro1(name varchar2,pass varchar2)：这里面的参数相当于编程里面的形参，传递的数据

然后调用该存储过程

SQL> exec sp_pro1('xiaobai','redhat');

PL/SQL procedure successfully completed.

然后查询数据是否已经插入了

SQL> select * from names;

NAME                 PASSWORD

-------------------- ------------------------------

xiaoming             redhat

xiaobai              redhat

修改表emp的雇员为smith的薪水，编写存储过程实现

SQL> create or replace procedure sp_pro1(name varchar2,v_sal number) is

  2  begin

  3  update emp set sal=v_sal where ename=name;

  4  end;

  5  /

Procedure created.

调用存储过程，传递形参数据

SQL> exec sp_pro1('SMITH',1200);

PL/SQL procedure successfully completed.

查询改变结果

SQL> select ename,sal from emp where ename='SMITH';

ENAME             SAL

---------- ----------

SMITH            1200

过程用于执行特定的操作，当建立过程时，既可以指定输入参数（in），也可以指定输出参数（out）

sp_pro1(name varchar2,pass varchar2)这里面加入的参数默认是in，如果要输出有返回值的存储过程必须加上out，看下面例子

给定一个empno雇员号，返回雇员名，编写一个存储过程

SQL> create or replace procedure sp_pro1(spno in number,spname out varchar2) is

  2  begin

  3  select ename into spname from emp where empno=spno;

  4  end;

  5  /

Procedure created.

该如何调用了，这里就不能直接exec procedure_name这样了，调用方式如下

SQL> declare

  2  v_name varchar(20);     定义一个变量，将存储过程返回出来的值存入到这个变量中

  3  begin

  4  sp_pro1(7788,v_name);

  5  dbms_output.put_line(v_name);   打印变量的值，也就是返回出来的值

  6  end;

  7  /

SCOTT

PL/SQL procedure successfully completed.

什么情况下用exec调用，什么情况下用PLSQL调用存储过程？

exec适合于调用存储过程无返回值

plsql适合于调用存储过程有返回值，不管多少个

函数

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

返回的数据，创建函数用create function

SQL> --函数案例

结构：create or replace function return ..is，看下面例子

SQL> create or replace function sp_fun1(name varchar2) return number is

  2  yearsal number(7,2);

  3  begin

  4  select sal*12 into yearsal from emp where ename=name;

  5  return yearsal;   定义需要返回的

  6  end;

  7  /

Function created.

函数创建完成，该如何调用呢？

SQL> declare 

  2  v_sal number(7,2);     定义一个变量用来接收函数返回的值

  3  begin

  4  v_sal:=sp_fun1('SMITH');  将函数返回的值赋值给v_sal，赋值为:=

  5  dbms_output.put_line(v_sal);

  6  end;

  7  /

14400

PL/SQL procedure successfully completed.