oracle plsql 编程

转载自http://blog.csdn.net/y13530828499/article/details/6755823

为什么学pl/sql 
n 学习必要性 
1.提高应用程序的运行性能 
2.模块化的设计思想【分页的过程，订单的过程，转账的过程。。】 
3.减少网络传输量 
4.提高安全性（sql会包括表名，有时还可能有密码，传输的时候会泄露。PL/SQL就不会） 

为什么PL/SQL会快呢？看图： 
不好的地方： 
移植性不好（换数据库就用不了）， 

用什么编写pl/sql 
n sqlplus开发工具 
sqlplus是oracle公司提供的一个工具，这个因为我们在以前介绍过的： 
举一个简单的案例： 
编写一个存储过程，该过程可以向某表中添加记录。 
1.创建一个简单的表 
  

Sql 代码 

1. create table mytest(name varchar2(30),passwd varchar2(30));  

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

2.创建过程 

Sql代 码 

1. create or replace procedure sp_pro1 is  

2. begin--执行部分  

3. insert into mytest values('韩顺平','m1234')；  

4. end;  

5. /  

  create or replace procedure sp_pro1 is

  begin--执行部分

  insert into mytest values('韩顺平','m1234')；

  end;

  /

replace:表示如果有sp_pro1，就替换 
如何查看错误信息：show error; 
如何调用该过程： 
1）exec 过程名（参数值1，参数值2...）； 
2）call 过程名（参数值1，参数值2...）；

 

n pl/sql developer开发工具 
pl/sql developer是用于开发pl/sql块的集成开发环境(ide)，它是一个独立的产品，而不是oracle的一个附带品。 
举一个简单案例： 
编写一个存储过程，该过程可以删除某表记录。 

Sql代 码 

1. create or replace procedure sp_pro2 is  

2. begin--执行部分  

3. delete from mytest where name='韩顺平'；  

4. end;  

create or replace procedure sp_pro2 is

begin--执行部分

delete from mytest where name='韩顺平'；

end;

pl/sql基础 
pl/sql介绍 
n 介绍 
  开发人员使用pl/sql编写应用模块时，不仅需要掌握sql语句的编写方法，还要掌握pl/sql语句及语法规则。pl/sql编程可以使用变量和逻辑 控制语句，从而可以编写非常有用的功能模块。比如：分页存储过程模块、订单处理存储过程模块、转账存储过程模块。而且如果使用pl/sql编程，我们可以 轻松地完成非常复杂的查询要求。 

pl/sql可以做什么 
n 简单分类

n 编写规范 
1.注释 
单行注释 -- 

Sql代 码 

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

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

多行注释 /*...*/来划分 
2.标志符号的命名规范 
1).当定义变量时，建议用v_作为前缀v_sal 
2).当定义常量时，建议用c_作为前缀c_rate 
3).当定义游标时，建议用_cursor作为后缀emp_cursor 
4).当定义例外时，建议用e_作为前缀e_error 

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

n 块结构示意图 
pl/sql块由三个部分构成：定义部分，执行部分，例外处理部分。 
如下所示： 
declare 
/*定义部分——定义常量、变量、游标、例外、复杂数据类型*/ 
begin 
/*执行部分——要执行的pl/sql语句和sql语句*/ 
exception 
/*例外处理部分——处理运行的各种错误*/ 
end; 
定义部分是从declare开始的，该部分是可选的； 
执行部分是从begin开始的，该部分是必须的； 
例外处理部分是从exception开始的，该部分是可选的。 
可以和java编程结构做一个简单的比较。 

pl/sql块的实例(1) 
n 实例1-只包括执行部分的pl/sql块 

Sql代 码 

1. set serveroutput on --打开输出选项  

2. begin   

3.     dbms_output.put_line('hello');  

4. end;  

set serveroutput on --打开输出选项

begin 

    dbms_output.put_line('hello');

end;

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

pl/sql块的实例(2) 
n 实例2-包含定义部分和执行部分的pl/sql块 

Sql代 码 

1. declare  

2.     v_ename varchar2(5); --定义字符串变量  

3. begin  

4.     select ename into v_ename from emp where empno=&aa;  

5.     dbms_output.put_line('雇员名：'||v_ename);  

6. end;  

7. /  

declare

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

begin

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

    dbms_output.put_line('雇员名：'||v_ename);

end;

/

  如果要把薪水也显示出来，那么执行部分就应该这么写： 

Sql代 码 

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

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

相 关说明： 
& 表示要接收从控制台输入的变量。看图： 

pl/sql块的实例(3) 
n 实例3-包含定义部分，执行部分和例外处理部分 
  为了避免pl/sql程序的运行错误，提高pl/sql的健壮性，应该对可能的错误进行处理，这个很有必要。 
1.比如在实例2中，如果输入了不存在的雇员号，应当做例外处理。 
2.有时出现异常，希望用另外的逻辑处理，[网示] 
我们看看如何完成1的要求。 
相关说明： 
oracle事先预定义了一些例外，no_data_found就是找不到数据的例外。 

Sql代 码 

1. declare  

2.     --定义变量  

3.     v_ename varchar2(5);  

4.     v_sal  number(7,2);  

5. begin  

6.     --执行部分  

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

8.     --在控制台显示用户名  

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

10.     --异常处理  

11. exception  

12. when no_data_found then  

13.     dbms_output.put_line('朋友，你的编号输入有误！')；  

14. end;  

15. /  

17. pl/sql分类 -- 过程，函数，包，触发器

文章分类:数据库 

n 过程 
过程用于执行特定的操作，当建立过程时，既可以指定输入参数(in)，也可以指定输出参数(out)， 通过在过程中使用输入参数，可以将数据传递到执行部分；通过使用输出参数，可以将执行部分的数据传递到应用环境。在sqlplus中可以使用create procedure命令来建立过程。 
实例如下： 
1.请考虑编写一个过程，可以输入雇员名，新工资，可修改雇员的工资 
2.如何调用过程有两种方法； exec  call 
3.如何在java程序中调用一个存储过程 
问题：如何使用过程返回值？ 
特别说明： 
对于过程我们会在以后给大家详细具体的介绍，现在请大家先有一个概念。 

create procedure sp_pro3(spName varchar2, newSal number) is 
--不要写成number(3,2),表明类型就可以了，不需要大小。就好像Java写方法时的参数一样 

Sql代 码 

1. begin  

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

3. update emp set sal=newSal where ename=spName;  

4. end;  

5. /  

begin

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

update emp set sal=newSal where ename=spName;

end;

/

java程序中调用一个存储过程 
//演示java程序去调用oracle的存储过程案例 

Java 代码 

1. import java.sql.*;  

2. public class TestOraclePro{  

3.     public static void main(String[] args){  

4.           

5.         try{  

6.             //1.加载驱动  

7.             Class.forName("oracle.jdbc.driver.OracleDriver");  

8.             //2.得到连接  

9.             Connection ct = DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");  

10.   

11.             //3.创建CallableStatement  

12.             CallableStatement cs = ct.prepareCall("{call sp_pro3(?,?)}");  

13.             //4.给?赋值  

14.             cs.setString(1,"SMITH");  

15.             cs.setInt(2,10);  

16.             //5.执行  

17.             cs.execute();  

18.             //关闭  

19.             cs.close();  

20.             ct.close();  

21.         } catch(Exception e){  

22.             e.printStackTrace();  

23.         }  

24.     }  

25. }  

n 函数 
  函数用于返回特定的数据，当建立函数时，在函数头部必须包含return子句。而在函数体内必须包含return语句返回的数据。我们可以使用 create function来建立函数，实际案例： 

Sql代 码 

1. --输入雇员的姓名，返回该雇员的年薪  

2. create function annual_incomec(name varchar2)  

3. return number is  

4. annual_salazy number(7,2);  

5. begin  

6.     --执行部分  

7.     select sal*12+nvl(comm, 0) into annual_salazy from emp where ename=name;  

8.     return annual_salazy;  

9. end;  

10. /  

--输入雇员的姓名，返回该雇员的年薪

create function annual_incomec(name varchar2)

return number is

annual_salazy number(7,2);

begin

    --执行部分

    select sal*12+nvl(comm, 0) into annual_salazy from emp where ename=name;

    return annual_salazy;

end;

/

如果函数创建过程有编译错误，可以使用show error;命令显示错误 
在sqlplus中调用函数 

Sql代 码 

1. SQL> var income number  

2. SQL> call annual_incomec('scott') into: income;  

3. SQL> print income  

SQL> var income number

SQL> call annual_incomec('scott') into: income;

SQL> print income

同样我们可以在java程序中调用该函数 
select annual_income('SCOTT') from dual; 
这样可以通过rs.getInt(l)得到返回的结果。