oracle PL/SQL的介绍

发布时间：2020-12-12 13:17:47 所属栏目：百科来源：网络整理

导读：转自：http://blog.sina.com.cn/s/blog_4c302f060101i4o1.html 一 PL/SQL的介绍 1 PL/SQL是什么？ PL/SQL(procedural language/SQL)是Oracle在标准的sql语言上的扩展。pl/sql不仅允许嵌入sql语言，还可以定义变量和常量，允许使用条件语句和循环语句，允许使

转自：http://blog.sina.com.cn/s/blog_4c302f060101i4o1.html

一 PL/SQL的介绍 1 PL/SQL是什么？ PL/SQL(procedural language/SQL)是Oracle在标准的sql语言上的扩展。pl/sql不仅允许嵌入sql语言，还可以定义变量和常量，允许使用条件语句和循环语句，允许使用例外处理各种错误，这样使得它的功能变的更加强大。（1）过程，函数，触发器是PL/SQL编写的（2）过程，函数，触发器是在Oracle中的（3）PL/SQL是非常强大的数据库过程化语言（4）过程，函数可以在java程序中调用 2 学习的必要性 （1）提供应用程序的运行性能传统的操作数据库的方法是：sql语句写到java程序中，java连接Oracle，传递给数据库，Oracle翻译SQL语句，编译成自己能识别的代码，然后才执行。 PL/SQL:直接在Oracle中写过程，由java调用过程，而此过程已经编译成可执行代码。这样节省了时间。（2）模块化的设计思想[分页的过程，订单的过程，转账的过程。。。] （3）减少网络传输量传统的方法：数据库和java中的SQL语句不在同一机器上，要经过网络传输过程：直接调用（4）提高安全性过程中封装了用户名、密码、表名、字段名等信息。 3 pl/sql的缺点 移植性不好 ?换数据库了就不能用了 4 pl/sql开发工具 （1）sqlplus开发工具 ?开始——运行——sqlplusw——用户名、口令（2）pl/sql developer 开发工具 ?文件——新建命令窗口 5 例子： eg1：编写一个存储过程，该过程可以向某表中添加记录——用sqlplus开发工具（1）创建一张简单表：create table mytest(name varchar2(30),passwd varchar2(30)); （2）创建过程： create or replace procedure sp_pro1 is --replace：代表如果原来有sp_pro1将被替换 begin--执行部分 insert into mytest values(‘李叶‘,‘m123‘); end; 回车? /?回车（3）如何查看错误信息如果编译有错，则可以用 show error显示错误。如果没错，则提示过程已创建。（4）如何调用该过程 ①第一种方式：exec 过程名（参数值1，参数值2。。。）; ②第二种方式：call 过程名（参数值1，参数值2。。。）; eg2:编写一个存储过程，该过程可以删除某表记录——用pl/sql developer开发工具 create or replace procedure sp_pro2 is? begin delete from mytest where name=‘韩顺平‘; end; / exec sp_pro2; ? 二 PL/SQL的基础 ????开发人员使用pl/sql编写应用程序模块时，不仅需要掌握sql语句的编写方法，还要掌握pl/sql语句及语法规则。pl/sql编程可以使用变量和逻辑控制语句，从而可以编写非常有用的功能模块。比如：分页存储过程模块、订单处理存储过程模块、转账存储过程模块。。而且如果使用pl/sql编程，我们可以轻松地完成非常复杂的查询要求。 1 pl/sql可以做什么？ （1）简单分类块（编程的基础单元）过程（存储过程）、函数、触发器和包（2）编写规范 ①注释单行注释 -- 多行注释 ②表示符号的命名规范 1）当定义变量时，建议用 v_作为前缀v_sal 2）当定义常量时，建议用 c_作为前缀c_rate 3）当定义游标时，建议用 _cursor作为后缀emp_cursor 4）当定义例外时，建议用 e_作为前缀e_error 2 pl/sql块介绍 （1）介绍 ????块（block）是pl/sql的基本程序单元，编写pl/sql程序实际上就是编写pl/sql块。要完成相对简单的应用功能，可能只需要编写一个pl/sql块；但是如果要想完成复杂的功能，可能需要在一个pl/sql块中嵌套其它的pl/sql块。（2）块结构示意图 pl/sql块由三个部分构成：定义部分、执行部分、例外处理部分。 declare begin exception end; 块结构和java程序的比较 java程序结构： public static void main(String[] args) { int a=1;//定义部分 try{ a++;//执行部分 } catch(Exception e){ //捕获异常 } } ? 3 例子? eg1?只包括执行部分的pl/sql块 set serveroutput on --打开输出选项 begin ????dbms_output.put_line(‘hello‘); end; 相关说明：dbms_output是Oracle所提供的包（类似java的开发包），该包包含一些过程，put_line就是dbms_output包的一个过程。 ? eg2 ?包括定义部分和执行部分 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); end; 相关说明：(1) select ename into v_ename from... 把查出的ename赋值给v_ename ?(2)& 表示要接收从控制台输入的变量 ?(3)|| 字符串连接符 ? eg3?包含定义部分、执行部分和例外处理部分 ????为了避免pl/sql程序的运行错误，提高pl/sql的健壮性，应该对可能的错误进行处理，这个很有必要： ① 比如在eg2中，如果输入了不存在的雇员号，应当做例外处理 ②有时出现异常，希望用另外的逻辑处理相关说明：Oracle事先预定义了一些例外，no_data_found就是找不到数据的例外。 declare ????v_ename varchar2(5);--定义字符串变量 ????v_sal number(7,v_sal from emp where empno=&no; ????dbms_output.put_line(‘雇员名:‘||v_ename||‘ 工资:‘||v_sal); --异常处理 exception when no_data_found then? dbms_output.put_line(‘朋友你的编号输入有误！‘); end; ? 4 过程 ????过程用于执行特定的操作，当建立过程时，既可以指定输入参数（in），也可以指定输出参数（out），通过在过程中使用输入参数，可以将数据传递到执行部分；通过使用输出参数，可以将执行部分的数据传递到应用环境。在sqlplus中可以使用create procedure命令来建立过程。 ? 5 实例 ①请考虑编写一个过程，可以输入雇员名，新工资可修改雇员的工资 create procedure?sp_prop3(sp_Name varhcar2,newSal number)? is begin --执行部分根据用户名修改工资 update emp set sal=newSal where ename=sp_Name; end; ② 如何调用过程有两种方法 exec sp_pro3(‘SCOTT‘,4768); 或者call。。。 ③ 如何在java中调用一个存储过程，C C++程序都可以调用启动eclipse——file new java project——project命名——new package 命名 com.sp——new class 命名 TestOraclePro package com.sp; import java.sql.*; public class TestOraclePro{ public static void main(String[] args) { try{ //1.加载驱动 Class.forName("Oracle.jdbc.driver.OracleDriver"); //2. 得到连接 Connection ct=DriverManager.getConnection("jdbc:Oracle:thin:@127.0.0.1","SCOTT","m123"); //3 创建CallableStatement? CallableStatement cs=ct.prepareCall("{call sp_pro3(?,?)}"); //4 给？赋值 cs.setString(1,"SMITH"); cs.setInt(2,10); //5 执行 cs.execute(); //关闭 cs.close(); ct.close(); } catch(Exception e){ e.printStackTrace(); } } } ? 加入驱动引入一个jar包 ?property——jave build path——libraries——add external jars 问题：如何使用过程返回值？？ ? 6 函数 函数用于返回特定的数据，当建立函数时，在函数的头部必须包含return子句，而在函数体内必须包含return语句返回的数据。我们可以使用create function来建立函数，实例： --输入雇员的姓名，返回该雇员的年薪 create function?sp_fun2(spName varchar2)? return?number? is?yearSal number(7,2); begin --执行部分 select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=spName; return yearSal; end; 调用 var abc number; call sp_fun2(‘SCOTT‘) into:abc; ? 7 包 包用于逻辑上组合过程和函数，它是由包规范和包体组成。 ① 我们可以使用create package命令来创建包：实例 --创建一个包sp_package --声明该包有一个过程 --声明该包有一个函数 create package sp_package? is ????procedure update_sal(name varchar2,newsal number); ????function annual_income(name varchar2) return number; end; / 执行 --包被创建包的规范只包含了过程和函数的声明，但是没有过程和函数的实现代码。包体用于实现包规范中的过程和函数。 ② 建立包体可以使用create package body命令 ? --给包 sp_package 实现包体 create or replace package body sp_package is procedure update_sal(name varchar2,newsal number) is begin undate emp set sal=newsal where ename=name; end; function annual_income(name varchar2) return number is annual_sal number; begin? select sal*12+nvl(comm,0)*12 into annual_sal from emp where ename=name; return annual_sal; end;? end; / 包体被创建? ③ 如何调用包的过程或函数当调用包的过程或是函数时，在过程和函数前需要带有包名，如果要访问其它方案的包，还需要在包名前加方案名如：call sp_package.update_sal(‘SCOTT‘,1500); ? 8 触发器 触发器是指隐含的执行的存储过程。当定义触发器时，必须要指定触发的事件和触发的操作，常用的触发事件包括insert,update,delete语句，而触发操作实际就是一个pl/sql块。可以使用create trigger来建立触发器。 ? 9 定义并使用变量 在编写pl/sql程序时，可以定义变量和常量；在pl/sql程序中包括： ①标量类型（scalar） ②复合类型（composite） ③参照类型（reference） ④lob（large Object）（1）标量（scalar）——常用类型在编写pl/sql块时，如果要使用变量，需在定义部分定义变量。 pl/sql中定义变量和常量的语法如下： identifier [constant] datatype [not null] [:=|default expr] identifier：名称 constant：指定常量。需要指定它的初始值，且其值是不能改变的 datatype：数据类型 not null：指定变量值不能为null := 给变量或是常量指定初始值 default：用于指定初始值 expr：指定初始值的pl/sql表达式，可以是文本值、其它变量、函数等例子： ①定义一个变长字符串 v_ename varchar2(10); ②定义一个小数范围-9999.99~9999.99 v_sal number(6,2); ③定义一个小数并给一个初始值5.4 v_sal2 number(6,2):=5.4 ④定义一个日期类型的数据 v_hiredate date; ⑤定义一个布尔变量，不能为空，初始值为false v_valid boolean not null default false; ? 使用标量 ????在定义好标量之后，就可以使用这些变量。这里需要说明的是pl/sql块为变量赋值不同于其它的编程语言，需要在等号前加冒号（:=）例子下面以输入员工号，显示员工姓名、工资、个人所得税（税率为0.03）为例，说明变量的使用，看看如何编写。 declare c_taxrate constant number(3,2):=0.03; v_name varchar2(5); v_sal number(7,2); v_tax_sal number(7,2) begin select ename,sal into v_name,v_sal from emp where empno=&no; --计算所得税 v_tax_sal:=sal*c_taxrate; --输出 dbms_optput.put_line(‘姓名是:‘||v_name||‘工资：‘||v_sal||‘交税:‘||v_tax_sal); （2）标量（scalar）——使用 %type类型对于上面的pl/sql块有一个问题：就是如果员工的姓名超过了5个字符的话，就会有错误，为了降低pl/sql程序的维护工作量，可以使用%type属性定义变量，这样它就会按照数据库列来确定你定义的变量的类型和长度。标识符名表名.列名%type; ??v_name emp.ename%type; ?v_name的类型和emp.ename的类型长度一致（3）复合变量（composite）——介绍 ①pl/sql记录 ????类似于高级语言中的结构体，需要注意的是当引用pl/sql记录成员时，必须要加记录变量作为前缀（记录变量.记录成员）如下： declare --定义一个pl/sql记录类型，名字emp_record_type,类型包含3个数据分别是name,salary,title。 type emp_record_type is record( name emp.ename%type, salary emp.sal%type, title emp.job%type ); --定义了一个变量，该变量的类型是emp_record_type sp_record emp_record_type; begin select ename,sal,job into sp_record from emp where empno=7788; dbms_output.put_line(‘员工名:‘||sp_record.name||‘工资是:‘||sp_record.salary); ? ②pl/sql表 ???相当于高级语言中的数组，但是需要注意的是在高级语言中数组的下表不能为负数，而pl/sql是可以为负数的，并且表元素的下表没有限制。实例如下 declare --定义了一个pl/sql表类型，sp_table_type,该类型用于存放emp.ename%type type sp_table_type is table of emp.ename%type index by binary_integer;--下表为整数，可以为负 sp_table sp_table_type;--定义了一个sp_table_type类型的变量sp_table begin ? select ename into sp_table(0) from emp where empno=7788;--如果把where去掉，则应该使用参照变量 dbms_output.put_line(‘员工名：‘||sp_table(0));? end; ③嵌套表 ④varray ? （4）参照变量 ????参照变量是指用于存放数值指针的变量，通过使用参照变量，可以使得应用程序共享相同对象，从而降低占用的空间。在编写pl/sql程序时，可以使用游标变量（ref cursor）和对象类型变量（ref obj_type）两种参照变量类型。参照变量——ref cursor游标变量使用游标时，当定义游标时不需要指定相应的select语句，但是当使用游标时（open时）需要指定select语句，这样一个游标就和一个select语句结合了。实例： ①请使用pl/sql编写一个块，可以输入部门号，并显示该部门所有员工姓名和他的工资 declare --定义游标类型 type sp_emp_cursor is ref cursor; --定义一个游标变量 test_cursor sp_emp_cursor; --定义两个变量 v_ename emp.name%type; v_sal emp.sal%type; --执行 begin --把test_cursor和一个select结合 open test_cursor for select ename,sal from emp where deptno=&no?; --循环取出 loop fetch test_cursor into v_ename,v_sal; --判断是否test_cursor为空 exit when test_cursor not found; dbms_output.put_line(‘名字：‘||v_ename||‘ 工资：‘||v_sal); end loop; end; ②在①的基础上，如果某个员工的工资低于200元，就增加100元 type sp_emp_cursor is ref cursor; --定义一个游标变量 test_cursor sp_emp_cursor; --定义两个变量 v_ename emp.name%type; v_sal emp.sal%type; --执行 begin --把test_cursor和一个select结合 open test_cursor for select ename,sal from emp where deptno=&no ; --循环取出 loop fetch test_cursor into v_ename,v_sal; --判断工资是否小于200 决定是否更新 if v_sal<200 then update emp set sal=sal+100 ; --判断是否test_cursor为空 exit when test_cursor not found; dbms_output.put_line(‘名字：‘||v_ename||‘ 工资：‘||v_sal); end loop; end;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!