《Oracle过程.ppt》由会员分享，可在线阅读，更多相关《Oracle过程.ppt（32页珍藏版）》请在三一文库上搜索。
1、Oracle过程,以下例子采用sql*plus,2,第6章 PL/SQL结构和组成元素,6.1 PL/SQL结构 6.2 PL/SQL 变量类型 6.3 PL/SQL过程,开始,返回目录,3,6.1 PL/SQL结构,6.1.1 PL/SQL程序组成6.1.2 PL/SQL程序的运行6.1.3 PL/SQL结构特点6.1.4 PL/SQL块的嵌套结构6.1.5 PL/SQL块分类,4,6.1.1 PL/SQL程序组成,1.组成 由声明部分、执行部分、异常处理三部分组成。2.PL/SQL块的基本结构Declare /* 声明部分: 声明PL/SQL用到的变量,类型及光标 */ begin /*
2、执行部分: 过程及SQL语句,即程序的主要部分 */ Exception /* 执行异常部分: 错误处理,可选 */ End;,5,6.1.2 PL/SQL程序的运行,例1 一个简单的PL/SQL（无名块）程序Declare v_name varchar2(10); /* 声明部分*/ v_sql number(7,2); begin /*执行部分*/ v_name:=hello!; v_sql:=54321.23; dbms_output.put_line(v_name); dbms_output.put_line(v_sql); end; /* 本PL/SQL 程序结束 */程序运行 se
3、t serverout on /*设置系统输出参数*/ / /*运行当前缓冲区程序*/,6,PL/SQL块中可以包含子块； 子块可以位于 PL/SQL中的任何部分； 子块也即PL/SQL中的一条命令； 已定义的对象有一定的作用范围。,6.1.3 PL/SQL结构特点,7,6.1.4 PL/SQL块的嵌套结构,Declare /* 本部分可以定义变量、光标等。 */ Begin /* 执行部分 */ . . . . . . begin /* 子块开始，也是执行语句 */ . . . . . . EXCEPTION /* 执行异常处理部分 */ . . . . . . end; /* 子块结束 *
4、/ EXCEPTION /* 执行异常部分 */ . . . . . . end; /* 本PL/SQL 程序结束 */,8,6.1.5 PL/SQL块分类,PL/SQL块可分为4类无名块：动态构造，只能执行一次。命名块：加了标号的块。子程序：存储在数据库中的存储过程、函数及包等。当在数据库上建立好后可以在其它程序中调用它们。触发子：当数据库发生操作时，会触发一些事件，从而自动执行相应的程序。,9,6.2 PL/SQL 变量类型,6.2.1 标识符6.2.2 常用变量类型,10,6.2.1 标识符,标识符名不能超过30字符；第一个字符必须为字母；不分大小写；不能用-(减号);不能是SQL保留字
5、。,11,6.2.1 标识符,建议的命名方法：,12,6.2.2 常用变量类型,13,6.2.2 常用变量类型,例1.declare order_no number(3); cust_name varchar2(20); order_date date; emp_no integer := 25; /* 缺省为 25 */ pi constant number := 3.14159; /* 定义常量pi */ begin dbms_output.put_line(pi); end;,14,6.3 PL/SQL过程,6.3.1 建立PL/SQL过程的基本步骤6.3.2 修改存储过程6.3.3 删
6、除存储过程6.3.4 具有查询与输出的存储过程6.3.5 调用存储过程6.3.6 用存储过程实现统计功能6.3.7 用存储过程实现更新功能6.3.8 带输出参数的存储过程,15,6.3.1 建立PL/SQL过程的基本步骤,基本步骤：建立过程所用到的表、建立过程、执行。例 某过程中要对表counter操作。则建立该存储过程的步骤为：（1）建表counter 表结构（2）建表语句 CREATE TABLE counter (num1 integer,num2 integer, result integer);,16,（2）创建过程（插入数据）SQL create or replace proced
7、ure pl_1 /* 过程名pl_2 */ 2 as 3 begin 4 insert into counter(num1,num2,result)values(1,1,1); 5 end; 6 /（“/”表示执行命令 ）（3）执行过程 execute pl_1;（4）查看结果 select * from table counter;,17,方法一将更改过的带有相同过程名的过程从新输入执行即可。方法二用GET 过程名调出过程进行修改删除存储过程 drop procedure pl_1;,6.3.2 修改存储过程,18,删除存储过程 drop procedure 过程名例：删除存储过程pl_1
8、 drop procedure pl_1;,6.3.3 删除存储过程,19,对表查询应有对应的变量例：用存储过程查询counter表SQL declare /*无名过程 */ 2 n integer; /* 定义变量，最好用 n counter.num1%type */ 3 begin 4 select num1 into n from counter; /* 将查询到的num1值赋给n */ 5 dbms_output.put_line(n); 6 end; 7 /1PL/SQL 过程已成功完成。,6.3.4 具有查询与输出的存储过程,20,与上一页等价的存储过程create or repl
9、ace procedure pl_2 /* 过程名pl_2 */isn integer;begin select num1 into n from counter; dbms_output.put_line(n);end;,6.3.4 具有查询与输出的存储过程,21,带输入参数有调用过程功能的过程SQL create or replace procedure pl_3(n integer) is 2 m integer; 3 begin 4 m:=n; 5 if m1 then 6 pl_1; /* 调用过程pl_1 */ 7 else 8 pl_2; /* 调用过程pl_2 */ 9 end
10、 if; 10 end pl_3; 11 /过程已创建。,6.3.5 调用存储过程,SQL execute pl_3(2);PL/SQL 过程已成功完成。,22,统计 count表中num1=1的记录数。SQL create or replace procedure pl_4 2 as 3 q integer; 4 begin 5 select count(*) into q from counter where num1=1; 6 dbms_output.put_line(q); 7 end; 8 /过程已创建。SQL set serverout onSQL execute pl_4;2PL
11、/SQL 过程已成功完成。,6.3.6 用存储过程实现统计功能,23,带子查询的插入过程SQL create or replace procedure pl_5 is 2 a integer; b integer; c integer; 3 begin 4 insert into counter(num1,num2,result) select 5 sum(num1),sum(num2),sum(result) from counter ; 6 end pl_5; 7/过程已创建。SQL execute pl_5 ;PL/SQL 过程已成功完成。SQL select * from counte
12、r; NUM1 NUM2 RESULT- - - 1 1 1 1 1 1 2 2 2,6.3.6 用存储过程实现统计功能,24,更新counterSQL create or replace procedure xie_biao is 2 begin 3 UPDATE counter 4 SET num2 = 16, num1 = 40 5 WHERE result = 2; 6 commit; 7 end xie_biao; 8 /过程已创建。SQL execute xie_biao;PL/SQL 过程已成功完成。,6.3.7 用存储过程实现更新功能,25,create or replace
13、procedure pl_6(p_out out varchar2) as p varchar2(100); begin select 你好 into p from dual; p_out :=p; end; SQLset serverout on SQLvariable dd varchar2(100); SQLexecute pl_6(:dd); SQLselect :dd from dual; -就可以看到输出的数值,6.3.8 带输出参数的存储过程,26,SQL create or replace procedure pl_7(p_out out float) is 2 p float
14、; 3 begin 4 select avg(num1)into p from counter ; 5 p_out :=p; 6 end pl_7; 7 /过程已创建。SQL set serverout onSQL variable dd varchar2(100); SQL execute pl_7(:dd); /执行参数前一定要加冒号PL/SQL 过程已成功完成。SQL select :dd from dual; :DD-1.33333333333333333333333333333333333333注：dual是系统的一张表，这个表在每一个用户下都会有,27,28,动态存储过程,Crea
15、te or replace procedure move_insert( a in integer, b in integer, c integer -默认为in类型 ) as str_sql varchar2(500);begin str_sql:=insert into counter values(:1,:2,:3); execute immediate str_sql using a,b,c; end ;,29,综合：包，过程，函数,首先在ORACLE建立PACKAGE和PACKAGE BODY，将在这里面定义函数和存储过程返回结果集。 1：建立PACKAGE： CREATE OR R
16、EPLACE package pk_wt is type mytype is ref cursor; procedure p_wt(mycs out mytype); function f_get(str in varchar2) return varchar2; end; 说明：其实PACKAGE只是个声明罢了。我们在这里定义了一个存储过程返回结集和一个函数，返回字符串。,30,2：建立PACKAGE BODY： CREATE OR REPLACE package BODY pk_wt is procedure p_wt(mycs out mytype) is begin open mycs for select * from counter; end p_wt;,31,function f_get(str varchar2) return varchar2 is str_temp varchar2(100) := good luck!; begin str_temp := str_temp | str; return str_temp; end f_get; end pk_wt; 说明：这里建立PACKAGE BODY是具体的说明和使用，将采用什么方式实现。,32,O 了 ！,