plsql编程学习文档

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

2. 过程，函数，触发器是用pl/sql编写的。过程，函数，触发器可以在java

程序中调用，pl/sql是非常强大的数据库过程语言 3. 如何查看错误信息：show errors

如何调用该过程：

（1） exec 过程名（参数值1，参数值2，……） （2） call 过程名（参数值1，参数值2，……） 4. 编写规范:

(1) 单行注释 --,多行注释 /*...*/来划分

(2) 标志符号的命名规范: 当定义变量时，建议用v_作为前缀v_sal; 当定

义常量时，建议用c_作为前缀c_rate; 当定义游标时，建议用_cursor

作为后缀emp_cursor; 当定义例外时，建议用e_作为前缀e_error

5. 块(block)是pl/sql的基本程序单元，编写pl/sql程序实际上就是编写

pl/sql块，要完成相对简单的应用功能，可能只需要编写一个pl/sql块，但是如果想要实现复杂的功能，可能需要在一个pl/sql块中嵌套其它的pl/sql块

6. pl/sql块由三个部分构成：定义部分，执行部分，例外处理部分

declare

/*定义部分——定义常量、变量、游标、例外、复杂数据类型*/ begin

/*执行部分——要执行的pl/sql语句和sql语句*/ exception

/*例外处理部分——处理运行的各种错误*/ end;

定义部分是从declare开始的，该部分是可选的； 执行部分是从begin开始的，该部分是必须的； 例外处理部分是从exception开始的，该部分是可选的 例子：declare

v_name varchar2(20); begin

select names into v_name from test where id=&id; dbms_output.put_line('姓名：'||v_name); exception

when no_data_found then

dbms_output.put_line('没有此id');

end;

7. dbms_output是oracle所提供的包(类似java的开发包)，该包包含一些过

程，put_line就是dbms_output包的一个过程 8. set serveroutput on --打开输出选项 9. & 表示要接收从控制台输入的变量

10. 过程用于执行特定的操作，当建立过程时，既可以指定输入参数(in，定义

时，只需指定类型，不用指定大小)，也可以指定输出参数(out)， 通过在过程中使用输入参数，可以将数据传递到执行部分；通过使用输出参数，可以将执行部分的数据传递到应用环境。在sqlplus中可以使用create procedure命令来建立过程 11. Oracle存储过程案例：

create or replace procedure sp_pro3(spId number,newName varchar2) is begin

update test set names=newName where id=spId;

commit; end;

12. java程序去调用Oracle的存储过程案例：

Class.forName(\); Connection ct =

DriverManager.getConnection(\,\,\);

//创建一个CallableStatement

CallableStatement cs = ct.prepareCall(\

cs.setInt(1, 10); cs.setString(2, \ cs.execute();

java程序调用Oracle的有返回值的存储过程：

Class.forName(\);

Connection ct =

DriverManager.getConnection(\l\,\,\);

//创建一个CallableStatement CallableStatement cs = ct.prepareCall(\sp_pro5(?,?,?)\); cs.setInt(1, 100);

//给第二、三个？赋值 cs.registerOutParameter(2, cs.registerOutParameter(3,

oracle.jdbc.OracleTypes.VARCHAR); oracle.jdbc.OracleTypes.VARCHAR);

//执行

cs.execute();

//取返回值，要注意？顺序

String name = cs.getString(2);

String publihHouse = cs.getString(3);

System.out.println(\编号为100的书名是：\ + name);

System.out.println(\编号为100的出版社是：\ + publihHouse); cs.close(); ct.close();

java程序调用Oracle有返回结果集的存储过程：

----建立一个包，在该包中，我定义类型test_cursor,是个游标。 如下 create or replace package testpackage as TYPE test_cursor is ref cursor; end testpackage; ----建立存储过程。如下

create or replace procedure sp_pro6(id in number,p_cursor out testpackage.test_cursor) is begin

open p_cursor for

select * from book where bookid = id; end

----java调用

Class.forName(\

Connection

ct

=

DriverManager.getConnection(\

//′′?¨ò???CallableStatement

CallableStatement cs = ct.prepareCall(\cs.setInt(1, 100); //??μú?t?￠èy??￡??3?μ

cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR); //?′DD

cs.execute(); //μ?μ??á1??ˉ

ResultSet rs = (ResultSet)cs.getObject(2); while(rs.next()) {

System.out.println(\2)

+\3));

}

cs.close(); ct.close();

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

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

15. 包用于在逻辑上组合过程和函数，它由包规范和包体两部分组成 16. 创建包案例：

create package sp_package is

procedure update_test(newName varchar2, newid number) ; end;

17. 创建包体案例：

create or replace package body sp_package is

procedure update_test(newName varchar2, newid number)

is

begin

update test set names = newName where id = newid; end; end;

18. 当调用包的过程或是函数时，在过程和函数前需要带有包名，如果要访问其

它方案的包，还需要在包名前加方案名。

19. 调用包的过程或是函数案例：call sp_package. update_test ('SCOTT', 10); 20. 定义并使用变量,复合类型

? 标量类型(scalar) ? 复合类型(composite) ? 参照类型(reference) ? lob(large object)

21. pl/sql中定义变量和常量的语法如：identifier [constant] datatype [not

null] [:=| default expr]

identifier : 名称

constant ：指定常量。需要指定它的初始值，且其值是不能改变的 datatype ：数据类型

not null ：指定变量值不能为null

:= 给变量或是常量指定初始值 default 用于指定初始值

expr ：指定初始值的pl/sql表达式，可以是文本值、其它变量、函数等 22. 在定义好变量后，就可以使用这些变量。这里需要说明的是pl/sql块为变

量赋值不同于其它的编程语言，需要在等号前面加冒号(:=)

23. 标量(scalar)——使用%type类型。使用%type属性定义变量，这样它会按

照数据库列来确定你定义的变量的类型和长度。使用格式 ： 标识符名 表名.列名%type

24. 由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替

代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用pagkage了 25. Oracle分页：

create or replace procedure fenye

(tableName in varchar2,

Pagesize in number,--一页显示记录数 pageNow in number,

myrows out number,--总记录数 myPageCount out number,--总页数

p_cursor out testpackage.test_cursor--返回的记录集 ) is --定义部分

--定义sql语句 字符串 v_sql varchar2(1000); --定义两个整数

v_begin number:=(pageNow-1)*Pagesize+1; v_end number:=pageNow*Pagesize; begin

--执行部分

v_sql:='select * from (select t1.*, rownum rn from (select * from '||tableName||') t1 where rownum<='||v_end||')

where rn>='||v_begin; --把游标和sql关联

open p_cursor for v_sql; --计算myrows和myPageCount

--组织一个sql语句

v_sql:='select count(*) from '||tableName; --执行sql,并把返回的值，赋给myrows；

execute immediate v_sql into myrows;

--计算myPageCount

if mod(myrows,Pagesize)=0 then myPageCount:=myrows/Pagesize; else

myPageCount:=myrows/Pagesize+1; end if; end;

26. 预定义例外是由pl/sql所提供的系统例外。当pl/sql应用程序违反了

oracle 规定的限制时，则会隐含的触发一个内部例外。pl/sql为开发人员提供了二十多个预定义例外

case_not_found :

在开发pl/sql块中编写case语句时，如果在when子句中没有包含必须的条件分支，就会触发case_not_found的例外

cursor_already_open :

当重新打开已经打开的游标时，会隐含的触发例外cursor_already_open dup_val_on_index :

在唯一索引所对应的列上插入重复的值时，会隐含的触发例外dup_val_on_index例外 invalid_cursor :

当试图在不合法的游标上执行操作时，会触发该例外 invalid_number :

当输入的数据有误时，会触发该例外 too_many_rows :

当执行select into语句时，如果返回超过了一行，则会触发该例外

27. 预定义例外和自定义例外都是与oracle错误相关的，并且出现的oracle错

误会隐含的触发相应的例外；而自定义例外与oracle错误没有任何关联，它是由开发人员为特定情况所定义的例外 28. 自定义例外案例：

create or replace procedure ex_test(spNo number)

is

--定义一个例外 myex exception; begin

--更新用户sal

update emp set sal=sal+1000 where empno=spNo; --sql%notfound这是表示没有update --raise myex;触发myex

if sql%notfound then

raise myex; end if; exception

when myex then

dbms_output.put_line('没有更新任何用户')； end;

29. 视图是一个虚拟表，其内容由查询定义，同真实的表一样，视图包含一系列

带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。（视图不是真实存在磁盘上的） 30. 创建或修改视图：create or replace view 视图名 as select 语句 [with read only] 31.

32. 复合变量(composite)，用于存放多个值的变量。主要包括这几种： pl/sql

记录 ，pl/sql表，嵌套表 ，varray

33. 复合类型——pl/sql记录：类似于高级语言中的结构体，需要注意的是，当

引用pl/sql记录成员时，必须要加记录变量作为前缀(记录变量.记录成员) 34. pl/sql记录案例：

declare

--定义一个pl/sql记录类型emp_record_type,类型包含3个数据name,salary,title。说白了，就是一个类型可以存放3个数据，主要是为了好管理

type emp_record_type is record(

name emp.ename%type, salary emp.sal%type, title emp.job%type);

--定义了一个sp_record变量，这个变量的类型是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); end;

35. 复合类型-pl/sql表：相当于高级语言中的数组，但是需要注意的是在高级

语言中数组的下标不能为负数，而pl/sql是可以为负数的，并且表元素的 下标没有限制 36. pl/sql表案例：

declare

--定义了一个pl/sql表类型sp_table_type，该类型是用于存emp.ename%type

--index by binary_integer 表示下标是整数

type sp_table_type is table of emp.ename%type index by binary_integer;

--定义了一个sp_table变量，这个变量的类型是sp_table_type sp_table sp_table_type; begin

select ename into sp_table(-1) from emp where empno = 7788; dbms_output.put_line('员工名:' || sp_table(-1));

end;

37. select ename into sp_table(-1) from emp,( sp_table_type 是pl/sql表

类型)实际返回的行数超出请求的行数？解决方法是：使用参照变量

38. 参照变量是指用于存放数值指针的变量。通过使用参照变量，可以使得应用程序共享相同对象，从而降低占用的空间。在编写pl/sql程序时，可以使用游标变量(ref cursor)和对象类型变量(ref obj_type)两种参照变量类型 39. 使用游标时，当定义游标时不需要指定相应的select语句，但是当使用游

标时（open时）需要指定select语句，这样一个游标就与一个select语句结合了

40. 游标与Select语句案例如下：

请使用pl/sql编写一个块，可以输入部门号，并显示该部门所有员工姓名和他的工资。

在基础上，如果某个员工的工资低于200元，就添加100元。

declare

--定义游标类型sp_emp_cursor

type sp_emp_cursor is ref cursor; --定义一个游标变量

test_cursor sp_emp_cursor; --定义变量

v_ename emp.ename%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%notfound;

dbms_output.put_line('名字：'||v_ename||' 工资：'||v_sal); end loop; end;

41. 条件分支语句：pl/sql中提供了三种条件分支语句if—then，if – then –

else，if – then – elsif – then 42. 条件分支语句案例：

编写一个过程，可以输入一个雇员编号，如果该雇员的职位是PRESIDENT就

给他的工资增加1000，如果该雇员的职位是MANAGER就给他的工资增加500，其它职位的雇员工资增加200

create or replace procedure sp_pro6(spNo number) is

--定义

v_job emp.job%type; begin

--执行

select job into v_job from emp where empno=spNo; if v_job='PRESIDENT' then

update emp set sal=sal+1000 where empno=spNo; elsif v_job='MANAGER' then

update emp set sal=sal+500 where empno=spNo; else

update emp set sal=sal+200 where empno=spNo;

end if; end;

43. 循环语句 –loop：是pl/sql中最简单的循环语句，这种循环语句以loop开头，以end loop结尾，这种循环至少会被执行一次

44. 循环语句 –while循环：基本循环至少要执行循环体一次，而对于while

循环来说，只有条件为true时，才会执行循环体语句，while循环以while...loop开始，以end loop结束 45. 循环语句 –for循环：

基本for循环的基本结构如下 begin

for i in reverse 1..10 loop

insert into users values (i, 'shunping'); end loop; end;

我们可以看到控制变量i，在隐含中就在不停地增加

46. goto语句：基本语法如下 goto lable，其中lable是已经定义好的标号名 47. goto语句案例：

declare

i int := 1;

begin loop

dbms_output.put_line('输出i=' || i); if i = 1{} then goto ends_loop; end if; i := i + 1; end loop;

<>

dbms_output.put_line('循环结束'); end;

48. null语句不会执行任何操作，并且会直接将控制传递到下一条语句。使用

null语句的主要好处是可以提高pl/sql的可读性 49.

50. 触发器是指隐含的执行的存储过程。当定义触发器时，必须要指定触发的事

件和触发的操作，常用的触发事件包括insert,update,delete语句，而触发操作实际就是一个pl/sql块。可以使用create trigger来建立触发器 51.

<>

dbms_output.put_line('循环结束'); end;

48. null语句不会执行任何操作，并且会直接将控制传递到下一条语句。使用

null语句的主要好处是可以提高pl/sql的可读性 49.

50. 触发器是指隐含的执行的存储过程。当定义触发器时，必须要指定触发的事

件和触发的操作，常用的触发事件包括insert,update,delete语句，而触发操作实际就是一个pl/sql块。可以使用create trigger来建立触发器 51.

本文来源：https://www.bwwdw.com/article/w3mh.html