一、PL/SQL概念
PL/SQL：Procedural language/sql，是oracle在标准sql语句上的扩展。PL/SQL不仅允许嵌入sql语言，还可以定义变量和常量，允许使用条件语句和循环语句，允许使用例外处理各种错误。
存储过程、函数、触发器都是存放在oracle数据库当中，比较复杂的任务一般可以用存储过程来实现，而这些都是用PL/SQL来编写的。存储过程、函数可以用Java程序来调用。
PL/SQL的优点：
提高应用程序的运行性能：
传统操作数据库方法是：SQL语句是写在Java程序中，然后Java程序得到一个connection，数据库拿到SQL语句 后需要编译（时间），将语句翻译成可识别的语言。然后返回结果。
存储过程：减少Java程序中的SQL语言，而且存储过程已经在数据库中编译好。
模块化的设计思想：直接传参数调用过程
减少网络传输量：与传整个sql语句相比，调用存储过程只需要穿过程名，参数即可。
提高安全性：存储过程避免了重要信息的暴露，因为直接在数据库中。
PL/SQL缺点：
移植性不好：如果数据库变换，则这些存储过程全都要推翻掉。
PL/SQL的编写工具：Sql plus 或 PL/SQL developer。后者是用于开发pl/sql块的集成开发环境（IDE），这个一个独立的产品，而不是oracle的附带品。
一个简单案例：编写一个存储过程，向某表添加记录。
--创建一个表
create table mytest(
name varchar2(20),
password varchar2(20)
);
--创建存储过程
create or replace procedure pro_insertMytest is
begin
--执行部分
insert into mytest values ('SMITH', 'm123');
end;
--最后输入/表示执行存储过程
--如果有错误，SQL> show error; 显示错误信息
--如何调用该存储过程
--方式一： exec procedureName(param1, param2...);
--方式二： call procedureName(param1, param2...);二、PL/SQL 基础知识
PL/SQL可以编写什么：
块（block）是pl/sql的基本程序单元，编写pl/sql程序实际上就是编写pl/sql块。要实现相对复杂的功能，可能需要在一个pl/sql块中嵌套其他的pl/sql块。
简单分类：
编写规范：
注释：
单行注释：-- 多行注释： /* ... */
标识符号的命名规范
1) 定义变量时，建议使用 v_ 作为前缀 v_name
2) 定义常量时，建议使用 c_ 作为前缀 c_rate
3) 定义游标时，建议使用 _cursor 作为后缀 emp_cursor
4) 定义例外时，建议使用 e_ 作为前缀 e_error
PL/SQL 块的结构：
由三个部分构成：定义部分、执行部分、例外处理部分，如下：
declare
/* 定义部分----定义常量、变量、游标、例外、复杂数据类型 */
begin
/* 执行部分----要执行的pl/sql 语句和 sql 语句 */
exception
/* 例外处理部分----处理运行时的各种错误 */
说明：定义部分是从declare开始的，可选；执行部分从begin开始，必须；处理部分是从exception开始，可选。
PL/SQL块的实例：
/* 实例1-最简单的块 说明：dbms_output 是oracle提供的包(package)，类似于java的开发包，该包包含一些过程，put_line是该包的一个过程。 */ set serveroutput on; --打开输出选项 set serveroutput off; 关闭 begin dbms_output.put_line('Hello World'); end; -------------------------------华丽的分割线------------------------------------------- /* 实例2-包含定义部分和执行部分的pl/sql块 说明： & 表示要接收从控制台输入的变量 */ declare v_ename varchar2(10); -- 定义字符串变量 v_sal number(7,2); begin --执行部分 select ename, sal into v_ename, v_sal from emp where empno=&EmployeeNo; --在控制台显示用户名 dbms_output.put_line('Employee Name:' || v_ename || ' Salary:' || v_sal); end; -------------------------------华丽的分割线------------------------------------------- /* 实例3-包含定义部分、执行部分和例外处理的部分的pl/sql块 说明： 上面的实例2，如果没有找到对应的empno，则需要进行异常处理 */ declare v_ename varchar2(10); -- 定义字符串变量 v_sal number(7,2); begin --执行部分 select ename, sal into v_ename, v_sal from emp where empno=&EmployeeNo; --在控制台显示用户名 dbms_output.put_line('Employee Name:' || v_ename || ' Salary:' || v_sal); exception when no_data_found then dbms_output.put_line('Employee number not exists!'); end;存储过程：用于执行特定的操作。建立存储过程时，可以指定输入参数（in）和指定输出参数（out）。使用输入参数，可以将数据传递到执行部分，而使用输出参数可以将执行部分的数据传递到应用环境。
实例：编写一个存储过程，输入员工名、新工资去修改该员工的工资。以及用java调用存储过程。
/* 实例4-编写一个存储过程，输入员工名、新工资去修改该员工的工资 说明： 执行带参数的存储过程： exec pro_updateEmp('SMITH', 4678); */ create or replace procedure pro_updateEmp(empName varchar2, newSal number) is --此处可以定义变量 begin --执行部分 update emp set sal = newSal where ename = empName; end; -------------------------------华丽的分割线------------------------------------------- //Java 程序调用存储过程 public class TestOracleProcedure { public static void main(String[] args) { try { //加载驱动 Class.forName("oracle.jdbc.driver.OracleDriver"); //获取连接 Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL", "dog", "dog"); //创建CallableStatement //注：procedure 名大小写要保持和数据库一致 CallableStatement cs = conn.prepareCall("{call PRO_UPDATEEMP(?, ?)}"); cs.setString(1, "SMITH"); cs.setInt(2, 100); //执行 cs.execute(); //关闭资源 cs.close(); conn.close(); } catch (Exception e) { e.printStackTrace(); } } }函数：用于返回特定的数据，当建立函数时，在函数头部必须包含return子句，而在函数体内必须包含return语句返回的数据。
--函数案例 --输入员工名，返回该员工的年薪 create or replace function fun_YearSal(EmpName varchar2) return number is YearSal number(7,2); begin --执行部分 select (sal+nvl(comm, 0))*12 into YearSal from emp where ename = EmpName; return YearSal; end; --如何调用function --定义变量abc接收返回结果 var abc number; call fun_YearSal('SMITH') into:abc;包：用于在逻辑上组合存储过程和函数，它由包规范和包体两部分组成。包的规范只包含了存储过程和函数的说明，但是没有存储过程和函数的实现代码。包体用于实现包规范中的存储过程和函数。
--包 案例 --创建包 pak_test，并声明该包有 存储过程 PRO_UPDATEEMP 和函数 FUN_YEARSAL create or replace package pak_test is procedure PRO_UPDATEEMP(empName varchar2, newSal number); function FUN_YEARSAL(EmpName varchar2) return number; end; --接下来要实现包体 create or replace package body pak_test is --实现包中的存储过程 procedure pro_updateEmp(empName varchar2, newSal number) is begin update emp set sal = newSal where ename = empName; end; --实现包中的函数 function fun_YearSal(EmpName varchar2) return number is YearSal number(7,2); begin select (sal+nvl(comm, 0))*12 into YearSal from emp where ename = EmpName; return YearSal; end; end; --如何调用包的存储过程和函数：当调用包的存储过程和函数时，在过程和函数前需要带有包名。如果要访问其他方案的包，还需要在包名前加方案名。 --包是pl/sql种非常重要的部分 call pak_test.updateEmp('SMITH', '2000');触发器：指隐含的执行的存储过程，一般不会主动调用。当定义触发器时，必须要指定触发的事件和操作。常用的触发时间包括insert, update, delete 语句，而触发操作实际就是一个pl/sql块。
触发器是非常有用的，可以维护数据库的安全和一致性。（PL/SQL第二篇中讲述）
定义并使用变量：
编写pl/sql程序时，可以定义变量和常量；在pl/sql程序中包括有：标量类型（scalar）、复合类型（composite）、参照类型（reference）、lob （large object）
a. 标量类型（scalar）
---常用类型
identifier [constant] datatype [not null] [:=| default expr]
identifier：名称 constant：常量。需要指定初始值，而且值不能改变。 datatype：数据类型 not null：不能为 空 :=：给变量或者常量指定初始值 default：用于指定初始值 expr：指定初始值的pl/sql表达式，可以是文本 值、其他变量、函数等。
案例：--定义一个变长字符串 v_ename varchar2(20)
--定义一个小数 范围-9999.99~9999.99 v_sal number(6,2)
--定义一个小数并给一个初始值5.4 （:= 是pl/sql的赋值号 ） v_sal2 number(6,2):=5.4
--定义一个日期类型的数据 v_date date;
--定义一个布尔变量， 不能为空，初始值为false v_valid boolean not null default false;
---%type 类型的标量，对于上面的常用标量，如果实际的变量长度超过了定义的长度，则会报错。为了降低pl/sql 程序的维护工作量，可以使用%type属性定义变量，这样它会按照数据库列来确定定义的变量的类型和长度。
标识符名 表名.列名%type
/* 案例：输入员工号，显示员工姓名、工资、个人所得税（税率0.03）, 说明变量的使用*/ declare --定义标量 c_tax_rate number(3,2):=0.03; v_ename varchar2(6); v_sal number(7,2); v_tax number(7,2); begin --执行 select ename, sal into v_ename, v_sal from emp where empno=&EmployeeNum; --计算所得税 v_tax:=v_sal*c_tax_rate; --输出 dbms_output.put_line('Ename: '||v_ename||' Salary: '||v_sal||' Tax: '||v_tax); exception when no_data_found then dbms_output.put_line('Employee number not exists!'); end; --上述案例使用 %type 来定义变量，使用较多 declare --定义标量 c_tax_rate number(3,2):=0.03; v_ename emp.ename%type; v_sal emp.sal%type; v_tax number(7,2); begin --执行 select ename, sal into v_ename, v_sal from emp where empno=&EmployeeNum; --计算所得税 v_tax:=v_sal*c_tax_rate; --输出 dbms_output.put_line('Ename: '||v_ename||' Salary: '||v_sal||' Tax: '||v_tax); exception when no_data_found then dbms_output.put_line('Employee number not exists!'); end;b. 复合类型（composite）：存放多个值的变量，主要包括：pl/sql 记录、pl/sql 表、嵌套表、varray 四种，下面将介 绍前两种比较常用的。
/* 复合类型--pl/sql记录 类似高级语言中的结构体，注意：当引用pl/sql记录成员时，必须要加记录变量作为前缀(记录变量名.记录成员) */ declare --定义一个pl/sql记录类型，名为 emp_record_type，包含 ename,salary,title 三个数据 type emp_record_type is record( ename emp.ename%type, salary emp.sal%type, title emp.job%type); --定义一个emp_record_type类型的变量，emp_record emp_record emp_record_type; begin select ename, sal, job into emp_record from emp where empno = 7369; dbms_output.put_line('Ename: '||emp_record.ename||' Salary: '||emp_record.salary||' Title: '||emp_record.title); end; /* 复合类型--pl/sql表 类似高级语言中的数组，不同的是，pl/sql表中的元素下标可以为负，并且下标没有限制 */ declare --定义一个pl/sql表类型，名为emp_table_type，用于存放 emp.name%type 类型的数据 -- index by binary_integer 表示下标是整数 type emp_table_type is table of emp.ename%type index by binary_integer; --定义一个emp_table_type 类型的变量 emp_table emp_table emp_table_type; begin select ename into emp_table(-1) from emp where empno = 7369; dbms_output.put_line('Ename: '|| emp_table(-1)); end; --问题：如果返回多行数据，该怎么接收呢?c. 参照类型（reference）：参照变量是指用于存放数值指针的变量。通过使用参照变量，可以是应用程序共享相同对 象，从而降低占用空间。在写pl/sql程序时，可以使用游标变量（ref cursor）和对象类型变量（ref obj_type）两种 参照变量类型。
/* 参照变量--ref cursor游标变量 定义游标时，不需要指定select语句，但使用游标时（open） 需要指定select 语句，这样游标就和select语句结合 */ --案例：输入部门号，显示该部门所有员工的姓名和工资 declare --定义游标类型 type emp_cursor_type is ref cursor; --定义游标变量 emp_cursor emp_cursor_type; --定义两个变量去接收结果 v_ename emp.ename%type; v_sal emp.sal%type; begin --执行 --将游标emp_cursor 和 select语句结合，让游标指向结果集 open emp_cursor for select ename, sal from emp where deptno = &DeptNum; --循环取出 loop --取出游标 fetch emp_cursor into v_ename, v_sal; --循环结束条件，emp_cursor为空 exit when emp_cursor%notfound; dbms_output.put_line('Ename: '||v_ename||' Salary: '||v_sal); end loop; end;pl/sql 控制结构
a. 条件分支语句
/* 案例 实例11-条件分支语句： 1.编写一个存储过程，输入员工名，如果该员工工资低于2000，就给该员工工资增加10% 2.（二重条件分支） 如果补助不是0，则增加100，否则补助为200 3.（多重条件分支） 如果该员工职位是PRESIDENT，工资加1000； 如果职位是 MANAGER，工资加500;其他情况工资加200 */ create or replace procedure update_sal(EmployeeName varchar2) is --定义 v_sal emp.sal%type; v_comm emp.comm%type; v_job emp.job%type; begin --执行 select sal, comm, job into v_sal, v_comm, v_job from emp where ename = EmployeeName; --判断 1 if v_sal<2000 then update emp set sal=sal*1.1 where ename = EmployeeName; end if; --判断 2 if v_comm <> 0 then update emp set comm=comm+100 where ename = EmployeeName; else update emp set comm=comm+200 where ename = EmployeeName; end if; --判断3 if v_job = 'PRESIDENT' then update emp set sal=sal + 1000 where ename = EmployeeName; elsif v_job = 'MANAGER' then update emp set sal=sal + 500 where ename = EmployeeName; else update emp set sal=sal + 200 where ename = EmployeeName; end if; exception when no_data_found then dbms_output.put_line('Employee number not exists!'); end;b. 循环语句：pl/sql中包含三种循环，loop、while、for
/* 案例 实例12-循环语句： loop 通过输入Username 向Users表中循环添加10条数据 */ --create table sql drop table users; create table users( userid number(10) primary key, username varchar2(30) not null, password varchar2(30)); create or replace procedure loop_add_user1(Username varchar2) is --定义 v_num number:=1; begin loop insert into users values(v_num, Username, 'loop'); --判断是否要退出循环 exit when v_num = 10; --num 自增 v_num:=v_num + 1; end loop; end; /* 案例 实例13-循环语句： while 通过输入Username 向Users表中循环添加10条数据，从11条记录开始添加 */ create or replace procedure loop_add_user2(Username varchar2) is --定义 v_num number:=11; begin while v_num <= 20 loop insert into users values(v_num, Username, 'whileLoop'); --判断是否要退出循环 exit when v_num = 10; --num 自增 v_num:=v_num + 1; end loop; end; /* 案例 实例14-循环语句： for 通过输入Username 向Users表中循环添加10条数据，从21条记录开始添加 */ create or replace procedure loop_add_user3(Username varchar2) is begin --从21开始循环，直到30结束.可以看到控制变量i在隐含中不停的增加 for i in reverse 21..30 loop insert into users values(i, Username, 'forLoop'); end loop; end;pl/sql 顺序控制语句 goto 、 null
/* 案例 实例15-顺序控制语句： goto 、 null 说明：goto end_loop,这里end_loop是定义的标签 <<end_loop>> null语句不会执行任何操作，并且会直接将控制传递到下一条语句，一般只是为了提高程序可读性 */ declare i int := 1; begin loop dbms_output.put_line('Output i=' || i); if i=10 then goto end_loop; else null; end if; i := i + 1; end loop; dbms_output.put_line('Cycle Over1'); <<end_loop>> dbms_output.put_line('Cycle Over2'); end;
扫一扫
239
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
