（韩顺平讲解）pl/sql编程（一）

pl/sql(procedural language/sql) 是oracle在标准的sql语言上的扩展. pl/sql不仅允许嵌入sql语言，还可以定义变量和常量，允许使用条件语句和循环语句，允许使用例外处理各种错误,这样使得它的功能变得更加强大。

|-----过程(存储过程) procedure
|
|-----函数  function
块(编程、block)------- |
|-----触发器 trigger
|
|-----包  package

（1）pl/sql基础知识—编写规范

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

（2）pl/sql基础知识—pl/sql块结构

pl/sql块由三个部分构成:定义部分、执行部分、例外处理部分.如下所示:
declear
/*定义部分-----定义常量、变量、游标、例外、复杂数据类型*/
begin
/*执行部分-----要执行的pl/sql语句和sql语句*/
exception
/*例外处理部分----处理运行的各种错误*/
end;
注意：定义部分是从declare开始的，该部分是可选的。

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

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

（3）输出

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

set serveroutput on --打开输出选项 

（4）例外

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

（5）pl/sql基础知识

create package 命令来创建包

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

create function 来建立函数
使用create procedure 命令来建立过程。

可以使用create trigger来建立触发器。

标量定义的案例
①定义一个变长字符串
v_ename varchar2(10);
②定义一个小数 范围 -9999.99～9999.99
v_sal number(6,2);
③定义一个小数并给一个初始植为5.4 :=是pl/sql的赋值号
v_sal2 number(6,2):=5.4
④定义一个日期类型的数据
v_hiredate date;
⑥定义一个布尔变量，不能为空，初始植为false
v_valid boolean not null default false;

案例1：

编写一个存储过程，该过程可以向某表中添加记录。
1.创建一个简单的表
Sql代码
1. 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('nsz','1234')；
4. end;
5. /
replace:表示如果有sp_pro1，就替换 
如何查看错误信息：show error;
如何调用该过程：
1）exec 过程名（参数值1，参数值2...）；
2）call 过程名（参数值1，参数值2...）；

案例2

编写一个存储过程，该过程可以删除某表记录。
Sql代码
1. create or replace procedure sp_pro2 is
2. begin--执行部分
3. delete from mytest where name='nsz'；
4. end;
5./

案例3

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. /
如果要把薪水也显示出来，那么执行部分就应该这么写： 
Sql代码
1. select ename,sal into v_ename,v_sal from emp where empno=&aa;
相关说明： & 表示要接收从控制台输入的变量。

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

案例4

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. /

案例5

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. /
如果函数创建过程有编译错误，可以使用show error;命令显示错误
在sqlplus中调用函数
Sql代码
1. SQL> var income number
2. SQL> call annual_incomec('scott') into: income;
3. SQL> print income
同样我们可以在java程序中调用该函数 
select annual_income('SCOTT') from dual;
这样可以通过rs.getInt(l)得到返回的结果。

案例6

--创建一个包sp_package
--声明该包有一个过程update_sal
--声明该包有一个函数annual_income
Sql代码
1. create package sp_package is
2. procedure update_sal(name varchar2, newsal number);
3. function annual_income(name varchar2) return number;
4. end;

案例7

--给包sp_package实现包体
Sql代码
1. 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) return number is
8. annual_salary number;
9. begin
10. select sal * 12 + nvl(comm, 0) into annual_salary from emp
11. where ename = name;
12. return annual_salary;
13. end;
14. end;
15. /

案例8

下面以输入员工号，显示雇员姓名、工资、个人所得税(税率为0.03)为例。说明变量的使用，看看如何编写。
Sql代码
1. declare
2. c_tax_rate number(3,2):=0.03;
3. --用户名
4. v_ename varchar2(5);
5. v_sal number(7,2);
6. v_tax_sal number(7,2);
7. begin
8. --执行
9. select ename,sal into v_ename,v_sal from emp where empno=&no;
10. --计算所得税
11. v_tax_sal := v_sal*c_tax_rate;
12. --输出
13. dbms_output.put_line('姓名是：'||v_ename||'工资：'||v_sal||' 交税：'||v_tax_sal);
14. end;
15. /

案例9

1.请使用pl/sql编写一个块，可以输入部门号，并显示该部门所有员工姓名和他的工资。
Sql代码
1. declare
2. --定义游标sp_emp_cursor
3. type sp_emp_cursor is ref cursor;
4. --定义一个游标变量
5. test_cursor sp_emp_cursor;
6. --定义变量
7. v_ename emp.ename%type;
8. v_sal emp.sal%type;
9. begin
10. --执行
11. --把test_cursor和一个select结合
12. open test_cursor for select ename,sal from emp where deptno=&no;
13. --循环取出
14. loop
15. fetch test_cursor into v_ename,v_sal;
16. --判断是否test_cursor为空
17. exit when test_cursor%notfound;
18. dbms_output.put_line('名字：'||v_ename||' 工资：'||v_sal);
19. end loop;
20. end;
21. /