OraclePLSQL编程精简.pdf

PL/SQL 编程-1-PL/SQLPL/SQLPL/SQLPL/SQL 编程编程pl/sql（procedural language/sql）是 Oracle 在标准的 sql 语言上的扩展。pl/sql 不仅允许嵌入式 sql语言，还可以定义变量和常量，允许使用条件语句和循环语句，允许使用例外处理各种错误。这样使得他的功能变的更强大。缺点是移植性不好。编写一个存储过程，向表中添加数据。1.create table mytest(name varchar2(30),passwd varchar2(30);2.create or replace procedure xxc_pro1 isbegininsert into mytest values(小红,m123);end;3.调用过程exec过程名（参数 1,参数 2）或 call 过程名参数 1,参数 2）1exec xxc_pro1;或者是2call xxc_pro1;pl/sql 可以做什么？块：包括过程、函数、触发器、包。编写规范：1.注释-：单行注释eg：select*from emp where empno=7788;-取得员工信息/*/多行注释2.表示符号（变量）的命名规范：1当定义变量时，建议用 v_作为前缀:v_ename2当定义常量时，建议用 c_作为前缀：c_rate3当定义游标时，建议用_cursor 作为后缀：emp_cursor4当定义例外时，建议用 e_作为前缀：e_error块（block）是 pl/sql 的今本程序单元，编写 pl/sql 程序实际上就是在编写 pl/sql 块；pl/sql 块由三部分组成：定义部分，执行部分，例外处理部分。declare-可选部分/*定义部分：定义常量，变量，游标，例外，复杂数据类型*/begin-必选部分/*执行部分：要执行的 pl/sql 语句和 sql 语句*/exception-可选部分/*例外处理部分：处理运行的各种错误*/实例 1：只包含执行部分的 pl/sql 块SQL set serveroutput on-打开输出SQL begin2dbms_output.put_line(hello);3end;4/说明：dbms_output 是 oracle 提供的包，该包包含一些过程，put_line 就是其中之一。实例 2：包含定义部分和执行部分SQL declare2v_ename varchar2(5);3begin4select ename into v_ename from emp where empno=&no;PL/SQL 编程-2-5dbms_output.put_line(雇员名|v_ename);6end;7/说明：&：从控制台输入变量，会弹出一个对话框。实例 3.同时输出雇员名和工资SQL declare2v_ename varchar2(20);3v_sal number(10,2);4begin5select ename,sal into v_ename,v_sal from emp where empno=&no;6dbms_output.put_line(雇员名：|v_ename|工资：|v_sal);7end;8/包含定义，执行，和例外处理的 pl/sql 块。实例 4.当输入的员工号不存在时SQL declare2v_ename varchar2(20);3v_sal number(10,2);4begin5select ename,sal into v_ename,v_sal from emp where empno=&no;6dbms_output.put_line(雇员名：|v_ename|工资：|v_sal);7exception-异常处理部分8when no_data_found then9dbms_output.put_line(请输入正确的员工号！);10end;11/以上为块的基础，下面来介绍块的各个组成：过程，函数，触发器，包。过程过程过程用于执行特定的操作，当执行过程的时候，可以指定输入参数（in），也可以指定输出参数（out）。通过在过程中使用输入参数，可以讲数据输入到执行部分，通过使用输出参数，可以将执行部分的数据输出到应用环境，在pl/sql中可以使用create procedure命令来创建过程。编写一个存储过程，可以输入雇员名和新工资来改变员工工资。-案例create or replace procedure xxc_pro3(newname in varchar2,newsal in number)isbeginupdate emp set sal=newsal where ename=newname;end;调用exec xxc_pro3(SCOTT,2900);-in表示是输入参数，可以不写，默认是in，但out必须写。在java程序中调用存储过程来修改工资；/演示java程序调用oracle中的存储过程packagepackagepackagepackage TestOraPro;/根据不同的包，包名不同importimportimportimport java.sql.*;publicpublicpublicpublic classclassclassclass TestOraPro PL/SQL 编程-3-/*paramparamparamparam args*/publicpublicpublicpublic staticstaticstaticstatic voidvoidvoidvoid main(String args)/TODOTODOTODOTODO Auto-generated method stubtrytrytrytry/加载驱动Class.forName(oracle.jdbc.driver.OracleDriver);/得到连接1521为端口号Connection ct=DriverManager.getConnection(jdbc:oracle:thin:127.0.0.1:1521:CUIXIAO2,scott,xxc);/创建callablestatementCallableStatement cs=ct.prepareCall(call xxc_pro3(?,?);/给？赋值cs.setString(1,SMITH);cs.setInt(2,2600);/执行cs.execute();catchcatchcatchcatch(Exception e)e.printStackTrace();finallyfinallyfinallyfinally/关闭资源cs.close();ct.close();以上为过程基础，后会详细讲。函数函数函数用于返回特定的数据，当建立函数时，在函数头部必须包含return子句，而在函数体内必须包含return语句来返回数据，可以使用create function来建立函数。案例1.返回工人年工资。-函数案例create or replace function xxc_fun1(newname varchar2)return number is yearSal number(10,2);beginselect sal*12+nvl(comm,0)*12 into yearSal from emp where ename=newname;return yearSal;end;在sqlplus中调用函数sqlvar income number;sqlcall xxc_fun1(SCOTT)into:income;PL/SQL 编程-4-sqlprint income;在java中调用函数select xxc_fun1(SCOTT)from dual;这样可以通过rs.setInt(1)得到返回结果。包包包用于在逻辑上组合过程和函数，它由包规范和包体两部分组成。1.我们可以用create(or replace)package命令来建包；实例：create package xxc_package isProcedure update_sal(name varchar2,newsal number);Function annual_sal(name varchar2)return number;End;包规只包含过程和函数的说明，但是没有函数和过程的实现代码。包体用于实现包规范中的过程和函数。2.建立包体可以用create package body命令。create package body xxc_package isProcedure update_sal(name varchar2,newsal number)isbeginupdate emp set sal=newsal where ename=name;end;function annual_sal(name varchar2)return number isannual_salary number;beginselect sal*12+nvl(comm,0)*12 into annual_salary from empwhere ename=name;return;end;end;3.如何调用包中的过程和函数。在过程和函数前加上包名。exec xxc_package.update_sal(SCOTT,120);触发器触发器触发器是指隐含的存储过程，当定义触发器是，必须指定触发事件和触发的操作，常用的触发操作有insert、update和delete。触发器操作实际上是一个pl/sql块，可以使用create trigger命令来创建触发器。因为触发器内容很多，会在后面详细介绍，触发器是非常有用的，可以用来维护数据库的安全和一致性。在编写pl/sql块时可以定义的变量和常量；1.标量类型(scalar);2.复合类型(composite);3.参照类型(reference);4.lob(large ofject);标量类型标量类型(scalar)(scalar)(scalar)(scalar)常用类型常用类型语法格式（即前边最常见的格式）变量名 constant datatype not null:=/default exprexpr：指定初始值的pl/sql表达式，可以是文本，其他变量，函数等。定义标量案例PL/SQL 编程-5-1定义一个变长字符串V_ename varchar2(10);2定义一个小数，范围在-9999.999999.99v_sal number(6,2);3定义一个小数并赋初值:=是pl/sql的赋值符号；v_sal number(6,2):=12.3;4定义一个日期类型数v_birthday date;5定义一个布尔类型变量，不能为空，默认是false;v_valid boolen not null default false;下面以输入员工号，显示员工姓名，工资，个人所得税（税率为0.03）为例，说明变量的使用。declarec_tax_rate number(3,2):=0.03;v_ename varchar2(5);v_sal number(10,2);v_tax_sal number(10,2);beginselect ename,sal into v_ename,v_sal from emp where empno=&no;v_tax_sal:=v_sal*c_tax_rate;dbms_output.put_line(员工名：|v_ename|工资：|v_sal|缴税:|v_tax_sal);end;对于上边的pl/sql块，有一个问题。如果员工的名字长于5个字符，就会出现错误，为了降低pl/sql的维护工作量，可以使用%type属性来定义变量，这样它会按照数据库列来确定你定义的变量的类型和长度。变量名表名.列名%type对于上例的改动v_ename emp.ename%type;v_sal emp.sal%type;复合类型（composite）-介绍用于存放多个值的变量，主要包括这几种：pl/sql记录；pl/sql表；嵌套表；varray；其中 用的多，用的少。1.pl/sql1.pl/sql1.pl/sql1.pl/sql的记录。的记录。类似于高级语言的结构体，当引用pl/sql记录成员的时候，必须要加记录变量作为前缀（记录变量.记录成员）。如：declaretype emp_record_type is record(name emp.ename%type,salary emp.sal%type,title emp.job%type);xxc_record emp_record_type;beginselect ename,sal,job into xxc_record from emp where empno=&no;dbms_output.put_line(员工名：|xxc_record.name|工资：|xxc_record.salary);e e e end;nd;nd;nd;2.pl/sql2.pl/sql2.pl/sql2.pl/sql表表PL/SQL 编程-6-相当于高级语言中的数组，需要注意的是高级语言中的数组的下标不能为负，但表的下标没有限制，可以为负。例如：declare-binary_integer表示下标是整数type xxc_table_type is table of emp.ename%type index by binary_integer;xxc_table xxc_table_type;begin-下标0可以是任何整数，-1，-2,23都可以，但输出的时候也要是同一个数，不然会出错。select ename into xxc_table(0)from emp where empno=7788;dbms_output.put_line(员工名：|xxc_table(0);end;在这个实例中，如果把where子句去掉，就会出错，因为你只请求了一个数据，而返回了多行数据。解决办法是使用参照变量。参照变量参照变量参照变量是指用于存放数据指针的变量，通过使用参照变量可以使得应用程序共享相同的对象，从而降低占用的空间。在编写pl/sql程序时，可以使用游标变量(ref cursor)和对象类型变量(ref obj_type)这两种参照变量，用得多的是游标变量。游标变量游标变量定义游标是，不需要指定select语句，但当使用游标(open)时，需要指定select语句，这样一个游标就与一个游标就与一个select语句结合了。实例：编写一个pl/sql块，输入部门号，显示所有员工名和工资。declaretype xxc_emp_cursor is ref cursor;test_cursor xxc_emp_cursor;v_ename emp.ename%type;v_sal emp.sal%type;beginopen test_cursor for select ename,sal from emp where deptno=&no;loopfetch test_cursor into v_ename,v_sal;exit when test_cursor%notfound;dbms_output.put_line(名字：|v_ename|工资：|v_sal);end loop;close test_cursor;end;在 的基础上，如果员工的工资低于2000，加100declaretype xxc_emp_cursor is ref cursor;test_cursor xxc_emp_cursor;v_ename emp.ename%type;v_sal emp.sal%type;beginopen test_cursor for select ename,sal from emp where deptno=&no;loopfetch test_cursor into v_ename,v_sal;exit when test_cursor%notfound;if v_sal2000 thenupdate emp set sal=sal+100 where ename=v_ename;v_sal=v_sal+100;PL/SQL 编程-7-end if;dbms_output.put_line(名字：|v_ename|工资：|v_sal);end loop;close test_cursor;end;Pl/sqlPl/sqlPl/sqlPl/sql分支控制语句分支控制语句1.1.1.1.条件分支语句条件分支语句ifthenifthenelseiftheneslifthenelse当if语句结束时要有end if。不等于号是，不是!=，if job=MANAGER,是单引号，不是双引号。2.2.2.2.循环语句循环语句-loop-loop-loop-loop是pl/sql中最简单的循环语句，这种循环以loop开始，以end loop结束，此循环至少会被执行一次。例：编写一个循环过程，可输入用户名，并循环添加10个用户到user表中，用户编号从1开始。Sqlcreate table user(userid number(10),username varchar2(20);Sqlcreate procedure xxc_insert_user1(name varchar2)isdeclarev_Num number:=1;-不用指定大小吗？beginloopinsert into user values(v_num,name);exit when v_num=10;v_num:=v_num+1;-Oracle中没有+符号end loop;end;-while-while-while-while循环循环当while条件为真时，执行循环体。以whileloop开头，以end loop结尾。例：上题，用户号从11开始，添加10名用户。create procedure xxc_insert_user2(name varchar2)isdeclarev_Num number:=11;-不用指定大小吗？beginwhile v_num=20 loopinsert into user values(v_num,name);v_num:=v_num+1;end loop;end;-for-for-for-for循环循环循环基本结构如下：beginfor i in reverse 1.10 loopinsert into users(I,小红);end loop;end;PL/SQL 编程-8-顺序控制语句-goto,nullGoto语句会增加程序的复杂性，降低可读性，一般不使用，但有时会很方便。基本语法goto lable，lable是定义好了的标签。例：declarei int:=1;beginloopdbms_output.put_line(i=|i);if i=10 thengoto end_loop;end if;end loop;-此为标签，注意格式dbms_output.put_line(循环结束);end;null语句不执行任何操作，好处是可以提高程序的可读性。例：if sal2000 thenupdate emp set sal=sal+100 where ename=name;elsenull;编写分页过程编写分页过程从易到难，先写简单的过程。写无返回值的过程，并被Java程序调用，前边已写过，不再重写。现在写一个有返回值的过程，并用Java程序调用。例：输入员工号，输出员工姓名。Create procedure xxc_pro4(empid in number,empname out varchar2)isBeginSelect ename into empname from emp where empno=empid;End;Java程序调用。importimportimportimport java.sql.*;publicpublicpublicpublic classclassclassclass TestOraPro1/*paramparamparamparam args*/publicpublicpublicpublic staticstaticstaticstatic voidvoidvoidvoid main(String args)/TODOTODOTODOTODO Auto-generated method stubtrytrytrytry/加载驱动Class.forName(oracle.jdbc.driver.OracleDriver);/得到连接1521为端口号Connection ct=DriverManager.getConnection(jdbc:oracle:thin:127.0.0.1:1521:CUIXIAO2,scott,xxc);PL/SQL 编程-9-/创建callablestatementCallableStatement cs=ct.prepareCall(call xxc_pro4(?,?);/给第一个？赋值cs.setInt(1,7788);/给第二个？赋值cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);/执行cs.execute();/取出返回值，要注意？的顺序，第几个？是返回值就写几。String name=cs.getString(2);System.out.println(“7788的名字是”+name);catchcatchcatchcatch(Exception e)e.printStackTrace();finallyfinallyfinallyfinally/关闭资源cs.close();ct.close();案例扩展：输入一个员工号，输出该员工的名字，工资和岗位。create procedure xxc_pro5(empid in number,empname out varchar2,empsal out number,empjobout varchar2)isbeginselect ename,sal,job into empname,empsal,empjob from emp where empno=empid;end;Java调用importimportimportimport java.sql.*;publicpublicpublicpublic classclassclassclass TestOraPro1/*paramparamparamparam args*/publicpublicpublicpublic staticstaticstaticstatic voidvoidvoidvoid main(String args)/TODOTODOTODOTODO Auto-generated method stubtrytrytrytry/加载驱动Class.forName(oracle.jdbc.driver.OracleDriver);/得到连接1521为端口号Connection ct=DriverManager.getConnection(jdbc:oracle:thin:127.0.0.1:1521:CUIXIAO2,scott,xxc);/创建callablestatementCallableStatement cs=ct.prepareCall(call xxc_pro4(?,?,?,?);PL/SQL 编程-10-/给第一个？赋值cs.setInt(1,7788);/给后3个？赋值cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);cs.registerOutParameter(3,oracle.jdbc.OracleTypes.DOUBLE);cs.registerOutParameter(4,oracle.jdbc.OracleTypes.VARCHAR);/执行cs.execute();/取出返回值，要注意？的顺序，第几个？是返回值就写几。String name=cs.getString(2);String job=cs.getString(4);System.out.println(“7788的名字是”+name+”工作是”+job);catchcatchcatchcatch(Exception e)e.printStackTrace();finallyfinallyfinallyfinally/关闭资源cs.close();ct.close();带有返回值的存储过程（结果集是个列表）。案例：编写个存储过程，输入部门号，输出该部门的所有员工的信息。分析如下：由于Oracle的存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是个集合，所以不能用一般参数，必须要用package。所以分为两部分。创建一个包，在包中定义一个test_package，是个游标；create package test_package as type test_cursor is ref cursor;End test_package;2创建存储过程create procedure xxc_pro6(xxcno in number,xxc_cursor out test_package.test_cursor)isbeginopen xxc_cursor for select*from emp where deptno=xxcno;end;在Java中调用此存储过程importimportimportimport java.sql.*;publicpublicpublicpublic classclassclassclass TestOraPro1/*paramparamparamparam args*/publicpublicpublicpublic staticstaticstaticstatic voidvoidvoidvoid main(String args)/TODOTODOTODOTODO Auto-generated method stubtrytrytrytryPL/SQL 编程-11-/加载驱动Class.forName(oracle.jdbc.driver.OracleDriver);/得到连接1521为oracle端口号Connection ct=DriverManager.getConnection(jdbc:oracle:thin:127.0.0.1:1521:CUIXIAO2,scott,xxc);/创建callablestatementCallableStatement cs=ct.prepareCall(call xxc_pro6(?,?);/给第一个？赋值cs.setInt(1,10);/给第二个？赋值cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);/执行cs.execute();/得到结果集ResultSet rs=(ResustSet)cs.getObject(2);WhileWhileWhileWhile(rs.next()System.out.println(rs.getInt(1)+”+rs.getString(2)+”+rs.getString(3);catchcatchcatchcatch(Exception e)e.printStackTrace();finallyfinallyfinallyfinally/关闭资源cs.close();ct.close();开始编写分页过程。开始编写分页过程。例：请编写一个存储过程，要求可以输入表名、每页显示记录数、当前页，返回总记录数、总页数和返回的结果集。现在先回顾一下分页的语句，这是个模板。select*from(select t1.*,rownum rn from(select*from emp)t1 where rownum=6;创建一个包，在包中定义一个test_package2，是个游标；create package test_package2 as type test_cursor2 is ref cursor;End test_package2;创建存储过程；create procedure fenye is(tablename in varchar2,-表名rowsize in number,-每页记录数pagenow in number,-当前页recordnum out number,-总记录数pagenum out number,-总页数set_cursor out test_package2.test_cursor2-结果集的游标)isv_sql varchar2(1000);PL/SQL 编程-12-v_begin nuber:=(pagenow-1)*rowsize+1;v_end nuber:=pagenow*rowsize;begin-v_sql是连接字符串，有点绕，看仔细v_sql:=SELECT*FROM(SELECT T.*,ROWNUM rn FROM(SELECT*FROM|TABLENAME|)T WHERE ROWNUM=|V_BEGIN;-打开游标，把游标和sql语句结合起来open set_cursor for v_sql;-计算recordnum和pagenumv_sql:=SELECT COUNT(*)FROM|TABLENAME;execute immediate v_sql into recordnum;if mod(recordnum,rowsize)=0 thenpagenum:=recordnum/rowsize;elsepagenum:=recordnum/rowsize+1;end if;close set_cursor;end;用Java程序调用：importimportimportimport java.sql.*;publicpublicpublicpublic classclassclassclass TestOraPro1/*paramparamparamparam args*/publicpublicpublicpublic staticstaticstaticstatic voidvoidvoidvoid main(String args)/TODOTODOTODOTODO Auto-generated method stubtrytrytrytry/加载驱动Class.forName(oracle.jdbc.driver.OracleDriver);/得到连接1521为端口号Connection ct=DriverManager.getConnection(jdbc:oracle:thin:127.0.0.1:1521:CUIXIAO2,scott,xxc);/创建callablestatementCallableStatement cs=ct.prepareCall(call fenye(?,?,?,?,?,?);/给？赋值cs.setString(1,”emp”);cs.setInt(2,5);cs.setInt(3,1);/注册总记录数cs.registerOutParameter(4,oracle.jdbc.OracleTypes.INTEGER);/注册总页数PL/SQL 编程-13-cs.registerOutParameter(5,oracle.jdbc.OracleTypes.INTEGER);/注册结果集cs.registerOutParameter(6,oracle.jdbc.OracleTypes.CURSOR);/执行cs.execute();/取出返回值，要注意？的顺序，第几个？是返回值就写几。int rnum=cs.getInt(4);int pnum=cs.getInt(5);ResultSet rs=(ResultSet)cs.getObject(6);System.out.println(”记录数是：”+rnum+”总页数是:”+pnum);while(rs.next()System.out.println(”编号：”+rs.getInt(1)+”姓名：”+rs.getString(2);catchcatchcatchcatch(Exception e)e.printStackTrace();finallyfinallyfinallyfinally/关闭资源cs.close();ct.close();例外处理例外处理例外处理分为预定义例外，其他预定义例外和自定义例外。案例：当输入的雇员编号不存在时declarev_ename emp.ename%type;beginselect ename into v_name from emp where empno=&no;dbms_output.putline(名字是：|v_name);exceptionwhen no_data_found thendbms_output.putline(编号不存在！);end;no_data_foundno_data_foundno_data_foundno_data_found就是一个预定义例外，在执行select语句时会出异常，但在执行update等语句时不会触发该例外。预定义例外预定义例外是由pl/sql所提供的系统例外。当pl/sql违反Oracle的规定时就会隐含触发一个内部例外。常用的例外：1.case_no_found 当编写pl/sql块中编写case语句时，如果在when子句中没有包含必须的条件分支，就会触发case_no_found例外。例：create or replace procedure xxc_pro7(no number)isv_sal emp.sal%type;PL/SQL 编程-14-beginselect sal into v_sal from emp where empno=no;casewhen v_sal1000 thenupdate emp set sal=sal+200 where empno=no;when 1000=v_sal2000 thenupdate emp set sal=sal+300 where empno=no;end case;exceptionwhen case_no_found thendbms_output.putline(case中没有和|v_sal|相匹配的条件);end;2.2.2.2.cursor_already_opencursor_already_opencursor_already_opencursor_already_open例外：例外：declarecursor emp_cursor is select ename,sal from emp;beginopen emp_cursor;for emp_record1 in emp_cursor loopdbms_output.putline(emp_cursor.ename);end loop;exceptionwhen cursor_already_open thendbms_output.putline(游标已打开！);end;3.dup_val_on_index3.dup_val_on_index3.dup_val_on_index3.dup_val_on_index例外例外在唯一索