mysql 触发器 函数 存储过程之(函数)

一、函数和存款和储蓄进程

因为从前从没有过写blog的习贯,在此以前的mysql知识也就没记录,正好今后又用到mysql的函数,所以就及时记录下呢!

一:前言:

澳门威斯尼人平台登录 1

mysql存款和储蓄进程与函数

a)通过函数和积累进程能够将业务逻辑保存在数据库,在必要的时候调用。譬喻学生在三个学期可以修的最大学科数、导师的最小授课数等,那么些判别具备相比复杂的逻辑,固然在数据库外也得以兑现那样的主宰,但用函数或存款和储蓄进度在数据库的输入来核准,能够与应用程序独立开来,便于维护。但认为将业务逻辑独立写在仓库储存进度也不断定就可以平价维护。

区别

触发器 是在insert update之后实践sql命令,无需任何参数和再次来到值

函数和存储进度看似,
区别:

  • 相似的话函数都以回来贰个值可能表对象,重临七个值最棒用存储进程
  • 存款和储蓄进度一般是用作一个单身的片段来试行( EXECUTE
    语句推行),而函数能够看做查询语句的三个部分来调用(SELECT调用),由于函数能够回去一个表对象,由此它能够在查询语句中位居FROM关键字的末端。
    SQL语句中不可用存款和储蓄进度,而得以应用函数。
  • 一般的话,存款和储蓄进度达成的功力要复杂一点,而函数的完毕的坚守针对性相比较强。存款和储蓄进程,功效强大,能够实施李包裹涵修改表等一多种数据库操作;客户定义函数无法用于实施一组修改全局数据库状态的操作。

一:存储进度

澳门威斯尼人平台登录 2

积存进程下载  demo

mysql> delimiter //  -- 这里//为修改默认分隔符;


mysql> CREATE  PROCEDURE simpleproc (OUT param1 INT)
    -> BEGIN
    ->   SELECT  COUNT(*) INTO param1 FROM t;
    -> END//
Query OK, 0 rows affected (0.00 sec)


mysql> delimiter ;      // -- 改回来这里的默认分隔符为;

那边调用试用call

mysql> CALL simpleproc(@a);  
mysql> select @a;

b)SQL标准规定的函数定义方法为:

翻开数据库中的存款和储蓄进程和函数
show procedure status; //存储过程
show function status;     //函数

1:创立储存进程

澳门威斯尼人平台登录 3

澳门威斯尼人平台登录 4澳门威斯尼人平台登录 5

  1 
  2 create procedure pro_insertDept is
  3 
  4 begin
  5     insert into dept values(77,'市场拓展部','家啊的发放');/*插入记录数据*/
  6  commit;
  7  dbms_output.put_line('插入新记录成功');
  8 end pro_insertDept;
  9 /
 10 

View Code

澳门威斯尼人平台登录 6

澳门威斯尼人平台登录 7

澳门威斯尼人平台登录 8澳门威斯尼人平台登录 9

  1 create or replace procedure pro_insertDept is
  2 
  3 begin
  4     insert into dept values(77,'市场拓展部','家啊的发放');/*插入记录数据*/
  5  commit;
  6  dbms_output.put_line('插入新记录成功');
  7 end pro_insertDept;
  8 /

View Code

澳门威斯尼人平台登录 10

澳门威斯尼人平台登录 11

澳门威斯尼人平台登录 12

从运维结果中能够看看,实践存款和储蓄进程是打响的;别的,代码中的“execute”命令也得以简写为
“exec”;但神迹需求在二个PL/SQL

程序快中调用某些储存进度

函数demo

参数未有输入输出参数

mysql> CREATE  FUNCTION hello (s CHAR(20))
mysql> RETURNS  CHAR(50) DETERMINISTIC
    -> RETURN  CONCAT(‘Hello, ‘,s,‘!‘);

 

那边调用试用 select,和一般性函数同样了

mysql> SELECT hello(‘world‘);
+----------------+
| hello(‘world‘) |
+----------------+
| Hello, world!  |
+----------------+

create function dept count(dept_name varchar

查看存款和储蓄进度或函数的创立代码
show create procedure proc_name;
show create function func_name;

2:存款和储蓄进程的参数

澳门威斯尼人平台登录 13

变量的运用

declare last_month_start DATE;
DECLARE my_sql  INT  DEFAULT 10 ;
set var_name = 2;

returns integer

翻开视图
SELECT * from information_schema.VIEWS   //视图

SELECT * from information_schema.TABLES   //表

2-1:in 形式参数

澳门威斯尼人平台登录 14

澳门威斯尼人平台登录 15澳门威斯尼人平台登录 16

  1 
  2 create or replace procedure insert_dept(
  3   num_deptno in number,/*定义in模式的变量,它储存部门编号*/
  4   var_ename in varchar2,/*定义in模式的变量,它储存部门名称*/
  5   var_loc in varchar2
  6 ) is
  7 begin
  8      insert into dept values(num_deptno,var_ename,var_loc); /*向dept表中插入记录*/
  9      commit; /*提交数据库*/
 10 end insert_dept;
 11 /
 12 
 13 

View Code

澳门威斯尼人平台登录 17

澳门威斯尼人平台登录 18

澳门威斯尼人平台登录 19

澳门威斯尼人平台登录 20澳门威斯尼人平台登录 21

  1 begin
  2  insert_dept(var_ename=>'asadad',var_loc=>'asdfasd',num_deptno=>15) ;
  3 end;
  4 /

View Code

澳门威斯尼人平台登录 22

澳门威斯尼人平台登录 23

澳门威斯尼人平台登录 24

澳门威斯尼人平台登录 25澳门威斯尼人平台登录 26

  1 begin
  2    insert_dept(28,'asdfa','fasdf');
  3 end;
  4 /
  5 

View Code

澳门威斯尼人平台登录 27

澳门威斯尼人平台登录 28

澳门威斯尼人平台登录 29

澳门威斯尼人平台登录 30澳门威斯尼人平台登录 31

  1 SQL> exec insert_dept(38,var_loc=>'adfasd',var_ename=>'adfasd');
  2 
  3 PL/SQL procedure successfully completed

View Code

澳门威斯尼人平台登录 32

流程序调控制

CREATE DEFINER=`root`@`localhost` PROCEDURE `student_insert`()
BEGIN
    set @x=0;
    ins :LOOP
        set @x = @x +1;
        if @x=100 then
            leave ins;
        end if;
        insert into student (stuname) values (CONCAT("name",@x));
    END LOOP ins;
END

CREATE DEFINER=`root`@`localhost` PROCEDURE `student_insert2`()
BEGIN
    set @x=100;
    ins :LOOP
        set @x = @x +1;
        if @x=120 then
            leave ins;
        elseif mod(@x,2) = 0 then
            iterate ins;
        end if;
        insert into student (stuname) values (CONCAT("name",@x));
    END LOOP ins;
END

CREATE DEFINER=`root`@`localhost` PROCEDURE `loop_demo`()
BEGIN
    set @x=1 ,@x1=1;
    repeat
        set @x = @x +1;
    until @x > 0 end repeat;

    while @x1 < 2 do
        set @x1=@x1+1;
    end while;
END

begin

删去自定义方法
drop function isRead

isRead是办法名

2-2: out 方式参数

澳门威斯尼人平台登录 33

澳门威斯尼人平台登录 34澳门威斯尼人平台登录 35

  1 create or replace procedure select_dept(
  2   num_deptno in number,/*定义 in 模式变量  要求输入部门编号*/
  3   var_dname out dept.dname%type,/*定义out模式变量,可以储存部门名称并输出*/
  4   var_loc out dept.loc%type
  5 )
  6 is
  7 
  8 begin
  9    select dname,loc into var_dname,var_loc from dept where deptno=num_deptno;
 10 exception
 11    when no_data_found then
 12        dbms_output.put_line('该部门编号不存在');
 13 
 14 end select_dept;
 15 /

View Code

澳门威斯尼人平台登录 36

澳门威斯尼人平台登录 37

澳门威斯尼人平台登录 38

澳门威斯尼人平台登录 39澳门威斯尼人平台登录 40

  1 set serveroutput on
  2 declare
  3     var_dname dept.dname%type;/*声明变量,对应过程中的out模式的var_dname */
  4     var_loc dept.loc%type;/*声明变量,对应过程这哦功能的out模式的var_loc*/
  5 begin
  6     select_dept(99,var_dname ,var_loc );/*传入部门编号然后输出部门名称和位置信息*/
  7     dbms_output.put_line(var_dname||'位于:'||var_loc); /*输出部门信息*/
  8 end;
  9 /

View Code

澳门威斯尼人平台登录 41

在上边代码中,把注解的八个变量传入熬存款和储蓄进程中,当存款和储蓄进度实施时,个中的out参数会被赋值,当存款和储蓄进程实行完成,out参数的值会在调用处重返,那样定义的多少个变量就足以拿走out参数被给予的值,最终这七个值就能够在仓库储存进程外随便使用了。

澳门威斯尼人平台登录 42

澳门威斯尼人平台登录 43澳门威斯尼人平台登录 44

  1 variable var_dname varchar2(50);
  2 variable var_loc varchar2(50);
  3 exec select_dept(15,:var_dname,:var_loc );
  4 

View Code

澳门威斯尼人平台登录 45

澳门威斯尼人平台登录 46

澳门威斯尼人平台登录 47

澳门威斯尼人平台登录 48

澳门威斯尼人平台登录 49

游标的施用
CREATE DEFINER=`root`@`localhost` PROCEDURE `cursor_demo`()
BEGIN
    declare i_stuid int;
    declare i_stuname varchar(20);
    declare cur_stu cursor for select stuid,stuname from student;
    declare exit handler for not found close cur_stu;

    set @x1 = 0;
    set @x2 = 0;

    open cur_stu;

    repeat
        fetch cur_stu into i_stuid,i_stuname;
        select i_stuid,i_stuname;
    until 0 end repeat;

    close cur_stu;
END


CREATE DEFINER=`root`@`localhost` PROCEDURE `cursor_demo3`()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE a CHAR(16);
  DECLARE b,c INT;
  DECLARE cur1 CURSOR FOR SELECT stuname,stuid FROM `student`;
  DECLARE cur2 CURSOR FOR SELECT subid FROM `subject`;
  DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000‘ SET done = 1;

  OPEN cur1;
  OPEN cur2;

  REPEAT
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;
    IF NOT done THEN
       SELECT a,b,c;
    END IF;
  UNTIL done END REPEAT;

  CLOSE cur1;
  CLOSE cur2;
END

declare d_count integer;

友善写的贰个demo

目标是查询本身的房子在某年有些月份是不是曾经交过电费,重临结果0/1,
写法有一点点类似js的function

delimiter $$
CREATE FUNCTION isRead(in_meterTime INT,in_year Varchar(255),in_roomId INT) RETURNS int(1)  
BEGIN  
DECLARE i INT ; 
    select count(1) INTO i from (
        select distinct id from t_room where id in (select room_table_id from t_electric_meter where dfsjd_code =  in_meterTime and year = in_year and deleted = 0 )
    )t where id = in_roomId;
RETURN i;
END$$

2-3:in  out 方式参数

澳门威斯尼人平台登录 50

  1 create or replace procedure pro_square(
  2  num in out number ,/*计算它的平方或者是平方根,这是一个‘int  out ’参数*/
  3  flag in boolean
  4 )is
  5 i int :=2;
  6 begin
  7   if flag then /*true*/
  8     num:=power(num,i);
  9   else
 10     num :=sqrt(num);
 11 
 12   end if;
 13 
 14 end pro_square;
 15 /

澳门威斯尼人平台登录 51

澳门威斯尼人平台登录 52

澳门威斯尼人平台登录 53

  1 
  2 declare
  3   var_number number;/*储存要进行运算的值和运算后的结果*/
  4   var_temp number;/*储存要进行运算的值*/
  5   boo_flag boolean;/*平方或者 平方根的逻辑标记*/
  6 begin
  7   var_temp:=3;/*变量赋值*/
  8   var_number:=var_temp;
  9   boo_flag:=false;/*false 表示计算平方根 true 表示计算平方*/
 10   pro_square(var_number,boo_flag);
 11   if boo_flag then
 12      dbms_output.put_line(var_temp ||'的平方是:'||var_number);/*输出计算结果*/
 13   else
 14       dbms_output.put_line(var_temp ||'的平方根是:'||var_number);/*输出计算结果*/
 15    end if;
 16 end;
 17 /
 18 

澳门威斯尼人平台登录 54

澳门威斯尼人平台登录 55

触发器

总得创建在真实表之上,适合部分初叶化数据

CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
  a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  b4 INT DEFAULT 0
);

DELIMITER |

CREATE TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
    DELETE FROM test3 WHERE a3 = NEW.a1;  
    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
  END
|

DELIMITER ;

INSERT INTO test3 (a3) VALUES 
  (NULL), (NULL), (NULL), (NULL), (NULL), 
  (NULL), (NULL), (NULL), (NULL), (NULL);

INSERT INTO test4 (a4) VALUES 
  (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);

 

select count into d_count

说明

“DELIMITE途乐$$”语句的职能是将MYSQL的竣事符设置为$$,因为MYSQL默许的言语结束符为分号;所以那样设置才不会争论

  • 1、定义变量

DECLARE var_name[,varname]…date_type[DEFAULT VALUE];

var_name为一些变量的称谓
。DEFAUL电视ALUE子句给变量提供三个私下认可值。值除了能够被声称为二个常数外,还足以被钦赐为一个表明式。
若是未有DEFAULT子句,起初值为NULL

DECLARE MYPARAM INT DEFAULT 100;
  • 2、为变量赋值

概念变量之后,为变量赋值能够改换变量的暗中同意值,MYSQL中使用SET语句为变量赋值

SET var_name=expr[,var_name=expr]…

声明3个变量,分别为var1,var2和var3

DECLARE var1,var2,var3 INT;
SET var1=10,var2=20;
SET var3=var1+var2;

MYSQL中还足以因而SELECT…INTO为一个或多少个变量赋值(小编的例子中就是用的那么些)
eg:

DECLARE NAME CHAR(50);
DECLARE id DECIMAL(8,2);
SELECT id,NAME INTO id ,NAME FROM t3 WHERE id=2;

2-4:in 参数的私下认可值

澳门威斯尼人平台登录 56

  1 create or replace procedure insert_dept1(
  2   num_deptno in number,/*定义储存部门编号的in参数*/
  3   var_dname in varchar2 default '综合部',/*定义储存部门名称的in 参数 并初始化默认值*/
  4   var_loc in varchar2 default '北京'
  5 ) is
  6 
  7 begin
  8    insert into dept values (num_deptno,var_dname,var_loc) ;
  9 
 10 end insert_dept1;
 11 /
 12 

澳门威斯尼人平台登录 57

澳门威斯尼人平台登录 58

澳门威斯尼人平台登录 59

  1 declare
  2    row_dept dept%rowtype ;/*定义行变量,与dept表的一行类型相同*/
  3 begin
  4    insert_dept1(57,var_loc => '太原');/*调用 insert_dept存储过程,插入参数*/
  5    commit;
  6     select *  into row_dept from dept where deptno =57 ;/*查询新插入的记录*/
  7     dbms_output.put_line('部门名称是:'||row_dept.dname||',位置是:'||row_dept.loc);
  8 end;
  9 /

澳门威斯尼人平台登录 60

澳门威斯尼人平台登录 61

查看索引
show index from table

 

from instructor

3:存款和储蓄进程示例:

 

where instructor.dept_name= dept_name

3-1:中规中矩的 loop  while 循环。常态是拼接游标的格局循环数据

澳门威斯尼人平台登录 62

  1 create or replace procedure proc_yszxtz_aduit (
  2   yszxtz_ids in varchar2  /* 预算执行调整表id,其值为: '1,2,3,4,5,6,7,8'  */
  3 ) is
  4 
  5 rowvar_yszxtz ys_zxtz%rowtype    ; /*声明了:预算执行调整主表 单条数据对象 */
  6 ---  预算执行调整主表的id的 游标 
  7 var_sql varchar2(4000);
  8 cursor yszxtz_cursor is   select   *   into  rowvar_yszxtz from ys_zxtz z where  1=1       ;
  9 begin
 10     dbms_output.put_line('----===========预算执行调整审批:审结后业务处理开始-----====== ');
 11 /*进行:入参*/
 12 if yszxtz_ids is   null then
 13      dbms_output.put_line(' 预算执行调整主表的id值不允许 null');     /*则输出异常提示信息*/
 14      rollback; /*回滚插入的数据记录*/
 15 end if;
 16 
 17 --循环开始
 18 
 19   /*判断 split_cursor 是否已经打开*/
 20   if not  yszxtz_cursor%isopen  then
 21      open yszxtz_cursor   ;  /*打开游标 */
 22   end if;
 23   fetch yszxtz_cursor   into  rowvar_yszxtz;/*先让指针指向结果集中的第一行,并将值保存到emp_row 中*/
 24   while yszxtz_cursor%found loop
 25     dbms_output.put_line('预算执行调整的id:' || rowvar_yszxtz.id  );
 26 
 27 
 28 
 29   fetch yszxtz_cursor    into  rowvar_yszxtz;/*先让指针指向结果集中的第一行,并将值保存到emp_row 中*/
 30    /*退出循环的条件*/
 31   exit when yszxtz_cursor%notfound or yszxtz_cursor%notfound is null;
 32 
 33    dbms_output.put_line('---------------------------------------------预算执行调整的 业务逻辑处理结束---------------------------------------------' );
 34   end loop;
 35 close yszxtz_cursor;/*关闭游标*/
 36 dbms_output.put_line('---------------------------------------------预算执行调整的 业务逻辑处理结束:关闭游标---------------------------------------------' );
 37 
 38 end proc_yszxtz_aduit;
 39 

return d count;

3-2:动态拼接 游标的sql语句和参数条件的办法 进行 loop循环

澳门威斯尼人平台登录 63

  1 create or replace procedure proc_yszxtz_aduit_1 (
  2   yszxtz_ids in varchar2  /* 预算执行调整表id,其值为: 1,2,3,4,5,6,7,8 */
  3 )
  4 is
  5 type ref_cursor_type is ref cursor;  --定义一个动态游标
  6 yszxtz_cursor ref_cursor_type;
  7 rowvar_yszxtz ys_zxtz%rowtype    ; /*声明:预算执行调整主表 单条数据对象 */
  8 rowvar_adjustMx  view_yszxtzms_adjustmx%rowtype;/*声明:view_yszxtzms_adjustmx 视图 行数据对象*/
  9 var_gkadjustid number(20);/*声明:获取 gk_adjust 表主键 对象*/
 10 
 11 begin
 12     dbms_output.put_line('============================================预算执行调整审批:审结后业务处理开始============================================ ');
 13 /*进行:入参*/
 14 if yszxtz_ids is   null then
 15      dbms_output.put_line(' 预算执行调整主表的id值不允许 null');     /*则输出异常提示信息*/
 16      rollback; /*回滚插入的数据记录*/
 17 end if;
 18 
 19 --循环开始
 20    --打开游标     
 21   open yszxtz_cursor for 'select  *  from ys_zxtz z where  1=1 and  z.id in ('||   yszxtz_ids ||' )' ;
 22     dbms_output.put_line('============================================ 预算执行调整的 业务逻辑处理 开始:执行的sql为: '|| 'select  *  from ys_zxtz z where  1=1 and  z.id in ('||   yszxtz_ids ||' )============================================'  );     /*则输出异常提示信息*/
 23   fetch yszxtz_cursor   into  rowvar_yszxtz;/*先让指针指向结果集中的第一行,并将值保存到emp_row 中*/
 24   while yszxtz_cursor%found loop
 25     var_gkadjustid := nextid('GK_ADJUST_SEQ');/*获取预算执行调整主表的id值*/
 26    dbms_output.put_line('============================================预算执行调整的 业务逻辑处理 开始:    其id:' || rowvar_yszxtz.id ||';gk_adjust 表主键值:var_gkadjustid='||var_gkadjustid||'===========================================' );
 27 
 28 
 29   fetch yszxtz_cursor    into  rowvar_yszxtz;/*先让指针指向结果集中的第一行,并将值保存到emp_row 中*/
 30    /*退出循环的条件*/
 31   exit when yszxtz_cursor%notfound or yszxtz_cursor%notfound is null;
 32    dbms_output.put_line('============================================预算执行调整的 业务逻辑处理     结束:其id:' || rowvar_yszxtz.id ||'=====================================================================================' );
 33   end loop;
 34 close yszxtz_cursor;/*关闭游标*/
 35 dbms_output.put_line('============================================预算执行调整的 业务逻辑处理结束:关闭游标============================================' );
 36 
 37 end proc_yszxtz_aduit_1;
 38 

end

3-3:使用常德、包体的花样

  1 --包头 
  2 create or replace package mypackage as
  3  type empcursor is ref cursor; --声明一个光标类型
  4  procedure queryEmpList(dno in number,empList out empcursor);
  5 end;
  6 
  7 --创建包体 
  8 create or replace package body mypackage as
  9  procedure queryEmpList(dno in number,empList out empcursor) as
 10    begin
 11      --打开光标
 12      open empList for select * from emp where deptno=dno;
 13    end;
 14 end;
 15 

函数定义好后,能够在查询语句中调用,就疑似内置函数一样:

3-4: 使用存款和储蓄进度,再次回到游标的款型

  1 --定义一个返回程序集的引用游标 
  2 CREATE OR REPLACE PACKAGE BAF_QUERY_TABLE AS
  3   TYPE P_CURSOR IS ref CURSOR;
  4 END BAF_QUERY_TABLE;
  5 
  6 --创建存储过程,并返回游标的形式返回程序集 
  7 create or replace procedure getList(p_eno number, p_out_cursor out BAF_QUERY_TABLE.P_CURSOR) is
  8 begin
  9   --没有给定员工ID则返回所有员工信息
 10   if p_eno is null then
 11       open p_out_cursor for select * from emp;
 12   else
 13     --返回指定ID的员工信息
 14       open p_out_cursor for select * from emp where empno = p_eno;
 15   end if;
 16 
 17 end getList;
 18 
 19 
 20 --以上创建的包还可以给存储函数使用
 21 create or replace function sp_ListEmp return BAF_QUERY_TABLE.P_CURSOR
 22 as
 23     l_cursor    BAF_QUERY_TABLE.P_CURSOR;
 24 begin
 25     open l_cursor for select ename, empno from emp order by ename;
 26     return l_cursor;
 27 end;

select dept name, budget

3-5:使用sys_refcursor类型

  1 create or replace procedure rsCursor(p_eno number,rs_cursor out SYS_REFCURSOR)
  2 AS
  3 BEGIN
  4  --没有给定员工ID则返回所有员工信息
  5   if p_eno is null then
  6       OPEN rs_cursor for select * from emp;
  7   else
  8     --返回指定ID的员工信息
  9       OPEN rs_cursor for select * from emp where deptno = p_eno ;
 10   end if;
 11 
 12 END;

java中调用

  1 Connection conn = null;
  2         //sql语句 (一定要写上包名)
  3         String sql = "{call mypackage.queryEmpList(?,?)}";
  4 
  5         try {
  6             //获取数据库的连接
  7             conn = JDBCUtil.getConnection();
  8             //通过连接创建statment
  9             CallableStatement call = conn.prepareCall(sql);
 10 
 11             //对于IN参数需要赋值
 12             call.setInt(1,10);
 13 
 14             //对于OUT参数需要先申明
 15             call.registerOutParameter(2,OracleTypes.CURSOR);
 16 
 17             //执行调用
 18             call.execute();
 19 
 20             //取出该部门中所有员工信息(注意这里)
 21             ResultSet rs = ((OracleCallableStatement)call).getCursor(2);
 22 
 23             while(rs.next()){
 24                 //可以取出sql语句中查询的所有字段(这里只取几个演示下)
 25                 int empno = rs.getInt("empno");
 26                 String ename = rs.getString("ename");
 27                 double sal = rs.getDouble("sal");
 28                 System.out.println("==================================================");
 29                 System.out.println("empno:"+empno+"\t ename:"+ename+"\t sal:"+sal);
 30                 System.out.println("==================================================");
 31             }

from instructor

3-6:存款和储蓄过程的常用才具

只顾:本段内容出自:《oracle
存款和储蓄进程,游标、格外管理,会集使用入门详解》

在进展pl/sql编制程序时打交道最多的正是积累进程了。存款和储蓄进程的结构是非常的简易的,我们在此间除了读书存款和储蓄进程的基本结构外,还也许会学习编写存款和储蓄进度时有关的局地实用的文化。如:游标的拍卖,格外的拍卖,集结的挑选等等

1.积攒进程结构

1.1 第贰个存款和储蓄进度

  1 create or replace procedure proc1(
  2   p_para1 varchar2,
  3   p_para2 out varchar2,
  4   p_para3 in out varchar2
  5 )as
  6  v_name varchar2(20);
  7 begin
  8   v_name := '张三丰';
  9   p_para3 := v_name;
 10   dbms_output.put_line('p_para3:'||p_para3);
 11 end;

上面就是三个最简易的存放进程。三个囤积进度差十分的少分为这么多少个部分:
成立语句:create or replace procedure 存款和储蓄进程名
如果未有or
replace语句,则单独是新建贰个囤积进度。借使系统存在该存款和储蓄进度,则会报错。Create
or replace procedure
尽管系统中尚无此存款和储蓄进度就新建贰个,假诺系统中有此存款和储蓄进度则把本来删除掉,重新创造二个囤积进程。
积累进度名定义:满含仓库储存进度名和参数列表。参数名和参数类型。参数名无法重复,
参数字传送递格局:IN, OUT, IN OUT
IN 表示输入参数,按值传递格局。
OUT
表示输出参数,能够通晓为按援用传递格局。能够视作存款和储蓄进度的出口结果,供外界调用者使用。
IN OUT 就能够作输入参数,也可作输出参数。
参数的数据类型只须求指明类型名就可以,无需钦赐宽度。
参数的宽度由外界调用者决定。
进程能够有参数,也得以未有参数
变量评释块:紧跟着的as (is
)关键字,能够明白为pl/sql的declare关键字,用于注脚变量。
变量表明块用于注明该存款和储蓄进度要求动用的变量,它的成效域为该存款和储蓄进程。其余这里注解的变量必需内定宽度。服从PL/SQL的变量注解规范。
进度语句块:从begin
关键字开头为经过的语句块。存款和储蓄进度的切实可行逻辑在这里来完毕。
极度管理块:关键字为exception ,为管理语句发生的极度。该有的为可选
得了块:由end关键字结果。

1.2 存款和储蓄进度的参数字传送递格局

存款和储蓄进度的参数字传送递有三种情势:IN,OUT,IN OUT .
IN
按值传递,而且它不允许在蕴藏进度中被再度赋值。如若存款和储蓄进程的参数未有一点名存参数字传送递类型,默以为IN

  1 create or replace procedure proc1(
  2   p_para1 varchar2,
  3   p_para2 out varchar2,
  4   p_para3 in out varchar2
  5 )as
  6  v_name varchar2(20);
  7 begin
  8   p_para1 :='aaa';
  9   p_para2 :='bbb';
 10   v_name := '张三丰';
 11   p_para3 := v_name;
 12   dbms_output.put_line('p_para3:'||p_para3);
 13   null;
 14 end;
 15 
 16 Warning: Procedure created with compilation errors
 17 
 18 SQL> show error;
 19 Errors for PROCEDURE LIFEMAN.PROC1:
 20 
 21 LINE/COL ERROR
 22 -------- ----------------------------------------------------------------------   
 23 8/3      PLS-00363: expression 'P_PARA1' cannot be used as an assignment target
 24 8/3      PL/SQL: Statement ignored

那一点与其余高等语言都不相同。它一定于java在参数后面加上final关键字。
OUT
参数:作为出口参数,须求小心,当贰个参数被钦命为OUT类型时,就算在调用存储进度从前对该参数进行了赋值,在积累进度中该参数的值照旧是null.

  1 create or replace procedure proc1(
  2   p_para1 varchar2,
  3   p_para2 out varchar2,
  4   p_para3 in out varchar2
  5 )as
  6  v_name varchar2(20);
  7 begin
  8   v_name := '张三丰';
  9   p_para3 := v_name;
 10   dbms_output.put_line('p_para1:'||p_para1);
 11   dbms_output.put_line('p_para2:'||p_para2);
 12   dbms_output.put_line('p_para3:'||p_para3);
 13 end;
 14 
 15 SQL> var p1 varchar2(10);
 16 SQL> var p2 varchar2(10);
 17 SQL> var p3 varchar2(10);
 18 SQL> exec :p1 :='aaaa';
 19 SQL> exec :p2 :='bbbb';
 20 SQL> exec :p3 :='cccc';
 21 SQL> exec proc1(:p1,:p2,:p3);
 22 p_para1:aaaa
 23 p_para2:
 24 p_para3:张三丰
 25 SQL> exec dbms_output.put_line(:p2);
 26 
 27 
 28 PL/SQL procedure successfully completed
 29 p2
 30 ---------  
 31 

INOUT 是的确的按援用传递参数。就可以作为传播参数也能够看做传播参数。

  1 1.3 存储过程参数宽度
  2 create or replace procedure proc1(
  3   p_para1 varchar2,
  4   p_para2 out varchar2,
  5   p_para3 in out varchar2
  6 )as
  7  v_name varchar2(2);
  8 begin
  9   v_name := p_para1;
 10 end;
 11 
 12 SQL> var p1 varchar2(10);
 13 SQL> var p2 varchar2(20);
 14 SQL> var p3 varchar2(30);
 15 SQL> exec :p1 :='aaaaaa';
 16 SQL> exec proc1(:p1,:p2,:p3);
 17 
 18 
 19 ORA-06502: PL/SQL: numeric or value error: character string buffer too small
 20 ORA-06512: at "LIFEMAN.PROC1", line 8
 21 ORA-06512: at line 1

率先,大家要领会,我们鞭长莫及在存款和储蓄进度的定义中钦定期存款款和储蓄参数的小幅度,也就导致了我们心余力绌在积攒进度中决定传入变量的宽窄。那几个幅度是全然由外界传入时间调控制的。
我们再来看看OUT类型的参数的增长幅度

  1 create or replace procedure proc1(
  2   p_para1 varchar2,
  3   p_para2 out varchar2,
  4   p_para3 in out varchar2
  5 )as
  6  v_name varchar2(2);
  7 begin
  8   p_para2 :='aaaaaaaaaaaaaaaaaaaa';
  9 end;
 10 SQL> var p1 varchar2(1);
 11 SQL> var p2 varchar2(1);
 12 SQL> var p3 varchar2(1);
 13 SQL> exec :p2 :='a';
 14 SQL> exec proc1(:p1,:p2,:p3);

在该进度中,p_para2被授予了十八个字符a.
而在表面的调用进程中,p2那么些参数仅仅被定义为varchar2(1).
而把p2作为参数调用那些历程,却并从未报错。並且它的真实值就是二十一个a

  1 SQL> select dump(:p2) from dual;
  2 DUMP(:P2)
  3 ---------------------------------------------------------------------------   
  4 Typ=1 Len=20: 97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97
  5 p2
  6 ---------   
  7 aaaaaaaaaaaaaaaaaaaa
  8 
  9     再来看看IN OUT参数的宽度
 10 create or replace procedure proc1(
 11   p_para1 varchar2,
 12   p_para2 out varchar2,
 13   p_para3 in out varchar2
 14 )as
 15  v_name varchar2(2);
 16 begin
 17   p_para3 :='aaaaaaaaaaaaaaaaaaaa';
 18 end;
 19 
 20 SQL> var p1 varchar2(1);
 21 SQL> var p2 varchar2(1);
 22 SQL> var p3 varchar2(1);
 23 SQL> exec proc1(:p1,:p2,:p3);

实行那么些历程,仍旧精确实行。
看得出,对于IN参数,其上涨的幅度是由外界调整。
对此OUT 和IN OUT 参数,其上升的幅度是由存款和储蓄进度里面调整。
之所以,在写存款和储蓄进程时,对参数的宽度举办认证是卓殊有不可缺少的,最明智的方法就是参数的数据类型使用%type。那样两方就落成了同样。

1.3 参数的暗许值

积攒进度的参数能够安装默许值

  1 create or replace procedure procdefault(p1 varchar2,
  2                                         p2 varchar2 default 'mark')
  3 as
  4 begin
  5   dbms_output.put_line(p2);
  6 end;
  7 
  8 SQL> set serveroutput on;
  9 SQL> exec procdefault('a');

mark
能够经过default
关键字为存款和储蓄进度的参数钦命暗许值。在对存款和储蓄进程调用时,就能够省略私下认可值。
供给留心的是:私下认可值仅仅扶助IN传输类型的参数。OUT 和 IN
OUT不能够内定暗许值
对此有默许值的参数不是排在最终的状态。

  1 create or replace procedure procdefault2(p1 varchar2 default 'remark',
  2                                         p2 varchar2 )
  3 as
  4 begin
  5   dbms_output.put_line(p1);
  6 end;

首先个参数有暗中同意值,第一个参数未有。就算大家想利用第贰个参数的默许值时
exec procdefault2(‘aa’);
那样是会报错的。
那怎么变吗?能够钦定参数的值。

  1 SQL> exec procdefault2(p2 =>'aa');

emark
诸有此类就OK了,钦命aa传给参数p2

2. 存款和储蓄进度之中块

2.1 内部块

我们精晓了仓库储存进程的布局,语句块由begin开首,以end甘休。那些块是能够嵌套。在语句块中可以嵌套任何以下的块。

  1 Declare … begin … exception … end;
  2 create or replace procedure innerBlock(p1 varchar2)
  3 as
  4   o1 varchar2(10) := 'out1';
  5 begin
  6   dbms_output.put_line(o1);
  7   declare
  8     inner1 varchar2(20);
  9   begin
 10     inner1 :='inner1';
 11     dbms_output.put_line(inner1);
 12 
 13     declare
 14       inner2 varchar2(20);
 15     begin
 16       inner2 := 'inner2';
 17       dbms_output.put_line(inner2);
 18     end;
 19   exception
 20     when others then
 21       null;
 22   end;
 23 end;

亟需专心变量的成效域。

3.囤积进程的常用本领

3.1 哪一类集结?

咱俩在选取存储进度的时候平常须求管理记录集,也正是多条数据记录。分为单列多行和多列多行,那几个品种都足以称作群集类型。我们在此间举办相比较这个集中类型,以便于在编制程序时做出正确的选项。
索引表,也叫做pl/sql表,不能够储存于数据库中,成分的个数未有限定,下标可感觉负值。

  1 type t_table is table of varchar2(20) index by binary_integer;
  2  v_student t_table;

varchar2(20)表示存放成分的数据类型,binary_integer代表元素下标的数据类型。
嵌套表,索引表未有 index
by子句正是嵌套表,它能够贮存于数据中,成分个数Infiniti,下标从1发端,并且供给伊始化

  1 type t_nestTable is table of varchar2(20);
  2 v_class t_nestTable ;

仅是那般证明是不能够运用的,必得对嵌套表实行开首化,对嵌套表展开先河化可以行使它的构造函数

  1 v_class :=t_nestTable('a','b','c');

  1 type t_array is varray (20) of varchar2(20);

长数组,变长数组与高等语言的数组类型极其相像,下标以1伊始,成分个数有限。

  1 type t_array is varray (20) of varchar2(20);

varray(20)就定义了变长数组的最大意素个数是21个
变长数组与嵌套表同样,也能够是数码表列的数据类型。
况兼,变长数组的采纳也供给事先初阶化。
类别 可存款和储蓄于数据库 成分个数 是或不是需伊始化 伊始下标值
澳门威斯尼人平台登录,索引表 否 无限 不需
嵌套表 可 无限 需 1
可变数组 可 有限(自定义) 需 1
总来说之,即使单单是在积存进程中作为集结变量使用,索引表是最佳的选料。

3.2 选取何种游标?

显示游标分为:普通游标,参数化游标和游标变量三种。
下边以三个进度来张开求证

  1 create or replace procedure proccursor(p varchar2)
  2 as
  3 v_rownum number(10) := 1;
  4 cursor c_postype is select pos_type from pos_type_tbl where rownum =1;
  5 cursor c_postype1 is select pos_type from pos_type_tbl where rownum = v_rownum;
  6 cursor c_postype2(p_rownum number) is select pos_type from pos_type_tbl where rownum = p_rownum;
  7 type t_postype is ref cursor ;
  8 c_postype3 t_postype;
  9 v_postype varchar2(20);
 10 begin
 11   open c_postype;
 12   fetch c_postype into v_postype;
 13   dbms_output.put_line(v_postype);
 14   close c_postype;
 15   open c_postype1;
 16   fetch c_postype1 into v_postype;
 17   dbms_output.put_line(v_postype);
 18   close c_postype1;
 19   open c_postype2(1);
 20   fetch c_postype2 into v_postype;
 21   dbms_output.put_line(v_postype);
 22   close c_postype2;
 23   open c_postype3 for select pos_type from pos_type_tbl where rownum =1;
 24   fetch c_postype3 into v_postype;
 25   dbms_output.put_line(v_postype);
 26   close c_postype3;
 27 end;
 28 

cursor c_postype is select pos_type from pos_type_tbl where rownum
=1
这一句是概念了三个最平日的游标,把全体查询已经写死,调用时不可以作其余更动。
cursor c_postype1 is select pos_type from pos_type_tbl where
rownum = v_rownum;
这一句并从未写死,查询参数由变量v_rownum来决定。必要注意的是v_rownum必得在那一个游标定义从前扬言。
cursor c_postype2(p_rownum number) is select pos_type from
pos_type_tbl where rownum = p_rownum;
这一条语句与第二条效果与利益相似,都以足以为游标完成动态的查询。可是它越来越压缩了参数的效能域范围。然而可读性收缩了多数。
type t_postype is ref cursor ;
c_postype3 t_postype;
先定义了贰个引用游标类型,然后再评释了多少个游标变量。
open c_postype3 for select pos_type from pos_type_tbl where rownum
=1;
下一场再用open for
来开采三个询问。需求留神的是它可以每每用到,用来开垦分裂的查询。
从动态性来讲,游标变量是最棒用的,可是阅读性也是最差的。
注意,游标的概念只好用使爱慕字IS,它与AS不通用。

3.3 游标循环最棒攻略

咱俩在拓宽PL/SQL编制程序时,日常需求循环读取结果集的多少。实行逐行处理,那几个历程就必要对游标进行巡回。对游标举办巡回的艺术有三种,大家在此一一深入分析。

  1 create or replace procedure proccycle(p varchar2)
  2 as
  3 cursor c_postype is select pos_type, description from pos_type_tbl where rownum < 6;
  4 v_postype varchar2(20);
  5 v_description varchar2(50);
  6 begin
  7 open c_postype;
  8   if c_postype%found then
  9     dbms_output.put_line('found true');
 10   elsif c_postype%found = false then
 11     dbms_output.put_line('found false');
 12   else
 13     dbms_output.put_line('found null');
 14   end if;
 15   loop
 16    fetch c_postype into v_postype,v_description ;
 17    exit when c_postype%notfound;
 18    dbms_output.put_line('postype:'||v_postype||',description:'||v_description);
 19   end loop;
 20   close c_postype;
 21 dbms_output.put_line('---loop end---');
 22   open c_postype;
 23     fetch c_postype into v_postype,v_description;
 24     while c_postype%found loop
 25       dbms_output.put_line('postype:'||v_postype||',description:'||v_description);
 26       fetch c_postype into v_postype,v_description ;
 27     end loop;
 28 
 29   close c_postype;
 30 dbms_output.put_line('---while end---');
 31   for v_pos in c_postype loop
 32     v_postype := v_pos.pos_type;
 33     v_description := v_pos.description;
 34     dbms_output.put_line('postype:'||v_postype||',description:'||v_description);
 35   end loop;
 36   dbms_output.put_line('---for end---');
 37 end;

动用游标在此以前需求开打游标,open cursor,循环完后再关闭游标close
cursor.
那是选拔游标应该慎记于心的规律。
地方的历程演示了游标循环的三种办法。
在批评循环方法在此以前,大家先看看%found和%notfound这一个游标的本性。

  1 open c_postype;
  2  if c_postype%found then
  3    dbms_output.put_line('found true');
  4  elsif c_postype%found = false then
  5    dbms_output.put_line('found false');
  6  else
  7    dbms_output.put_line('found null');
  8  end if;

在开垦贰个游标之后,立刻检查它的%found或%notfound属性,它拿走的结果即不是true亦不是false.而是null.必需执行一条fetch语句后,那些属性才有值。

第一种选择loop 循环 

  1 loop
  2    fetch c_postype into v_postype,v_description ;
  3    exit when c_postype%notfound;
  4    ……
  5 end loop

此间要求细心,exit
when语句一定要紧跟在fetch之后。必防止多余的多寡管理。
拍卖逻辑须要跟在exit when之后。这或多或少索要多加小心。
循环甘休后要记得关闭游标。

第三种采纳while循环。

  1 fetch c_postype into v_postype,v_description;
  2 while c_postype%found loop
  3    ……
  4       fetch c_postype into v_postype,v_description ;
  5 end loop;

大家精通了二个游标张开后,必需实施贰次fetch语句,游标的属性才会起效果。所以使用while
循环时,就供给在循环此前开展二次fetch动作。
并且数量管理动作必需放在循环体内的fetch方法从前。循环体内的fetch方法要放在最终。否则就能够多管理叁回。那一点也要特别的当心。
总的说来,使用while来循环管理游标是最复杂的章程。

第三种 for循环 

  1 for v_pos in c_postype loop
  2    v_postype := v_pos.pos_type;
  3    v_description := v_pos.description;
  4    …
  5  end loop;

.

可知for循环是相比较轻巧实用的艺术。
率先,它会自行open和close游标。消除了你忘记张开或关闭游标的干扰。
其余,自动定义了叁个笔录类型及注解该品种的变量,并自行fetch数据到这几个变量中。
笔者们须求小心v_pos
那些变量没有要求要在循环外进行宣示,不供给要为其钦点数据类型。
它应有是一个记下类型,具体的构造是由游标决定的。
本条变量的功效域仅仅是在循环体内。
把v_pos看作三个记下变量就足以了,假若要博取某二个值就如调用记录同一就可以了。
如v_pos.pos_type
有鉴于此,for循环是用来循环游标的最棒办法。高效,简洁,安全。
但缺憾的是,平日看到的却是第一种方法。所以从今现在得改造那么些习于旧贯了。

3.4 select into不可乎视的主题材料

小编们知道在pl/sql中要想从数据表中向变量赋值,需求利用select into
子句。
而是它会拉动来部分标题,假若查询未有记录时,会抛出no_data_found异常。
设若有多条记下时,会抛出too_many_rows异常。
那一个是比较糟糕的。一旦抛出了老大,就能够让进程中断。特别是no_data_found这种特别,未有严重到要让程序中断的程度,能够完全交由由程序开展管理。

  1 create or replace procedure procexception(p varchar2)
  2 as
  3   v_postype varchar2(20);
  4 begin
  5    select pos_type into v_postype from pos_type_tbl where 1=0;
  6     dbms_output.put_line(v_postype);
  7 end;

实行那么些进度

  1 SQL> exec procexception('a');
  2 报错
  3 ORA-01403: no data found
  4 ORA-06512: at "LIFEMAN.PROCEXCEPTION", line 6
  5 ORA-06512: at line 1
  6 

管理这一个有八个措施
1. 向来抬Gott别管理。

  1 create or replace procedure procexception(p varchar2)
  2 as
  3   v_postype varchar2(20);
  4 
  5 begin
  6    select pos_type into v_postype from pos_type_tbl where 1=0;
  7     dbms_output.put_line(v_postype);
  8 exception
  9   when no_data_found then
 10     dbms_output.put_line('没找到数据');
 11 end;

如此做换汤不换药,程序依旧被中止。或者这么不是大家所想要的。

  1. select into做为三个单身的块,在那些块中开展特别管理

    1 create or replace procedure procexception(p varchar2)
    2 as
    3 v_postype varchar2(20);
    4
    5 begin
    6 begin
    7 select pos_type into v_postype from pos_type_tbl where 1=0;
    8 dbms_output.put_line(v_postype);
    9 exception
    10 when no_data_found then
    11 v_postype := ”;
    12 end;
    13 dbms_output.put_line(v_postype);
    14 end;

那是一种相比较好的管理形式了。不会因为那一个那些而孳生程序中断。
3.施用游标

  1 create or replace procedure procexception(p varchar2)
  2 as
  3   v_postype varchar2(20);
  4   cursor c_postype is select pos_type  from pos_type_tbl where 1=0;
  5 begin
  6   open c_postype;
  7     fetch c_postype into v_postype;
  8   close c_postype;
  9   dbms_output.put_line(v_postype);
 10 end;

如此那般就全盘的制止了no_data_found卓殊。完全交由程序猿来举办支配了。
第三种情状是too_many_rows 至极的标题。
Too_many_rows 这么些主题素材比起no_data_found要复杂一些。
给三个变量赋值时,不过查询结果有五个记录。
管理这种难题也会有两种情景:
1.
多条数据是足以承受的,也正是说从结果聚焦随便取三个值就行。这种景象应当很极端了啊,假诺现身这种意况,也验证了程序的严俊性存在难点。
2.
多条数据是不得以被接受的,在这种地方自然是程序的逻辑出了难题,也说是说原本根本就不会想到它会生出多条记下。
对此第一种景况,就非得选用游标来拍卖,而对此第三种状态就无法不运用当中块来拍卖,重新抛出极其。
多条数据足以承受,随意取一条,那个跟no_data_found的管理形式同样,使用游标。
本身那边仅说第两种状态,不可承受多条数据,不过不要忘了拍卖no_data_found哦。那就无法应用游标了,必得运用当中块。

  1 create or replace procedure procexception2(p varchar2)
  2 as
  3   v_postype varchar2(20);
  4 
  5 begin
  6   begin
  7     select pos_type into v_postype from pos_type_tbl where rownum < 5;
  8   exception
  9     when no_data_found then
 10       v_postype :=null;
 11     when too_many_rows then
 12       raise_application_error(-20000,'对v_postype赋值时,找到多条数据');
 13   end;
 14  dbms_output.put_line(v_postype);
 15 end;

必要小心的是迟早要拉长对no_data_found的管理,对出现多条记下的情景则继续抛出特别,让上一层来处理。
简单来讲对于select into的语句供给小心那三种情景了。需求安妥管理啊。
3.5 在存款和储蓄进度中回到结果集
大家利用存款和储蓄进程都以重临值都以十足的,有的时候大家供给从进度中回到一个集结。即多条数据。那有二种缓和方案。相比轻便的做法是写不经常表,可是这种做法不灵敏。何况爱戴麻烦。我们得以行使嵌套表来完结.未有叁个相会类型能够与java的jdbc类型相称。那便是目的与关周到据库的对抗吧。数据库的对象并不可见统统调换为编制程序语言的指标,还必需利用关周全据库的管理格局。

  1 create or replace package procpkg is
  2    type refcursor is ref cursor;
  3    procedure procrefcursor(p varchar2, p_ref_postypeList  out refcursor);
  4 end procpkg;
  5 
  6 create or replace package body procpkg is
  7   procedure procrefcursor(p varchar2, p_ref_postypeList out  refcursor)
  8   is
  9     v_posTypeList PosTypeTable;
 10   begin
 11     v_posTypeList :=PosTypeTable();--初始化嵌套表   
 12     v_posTypeList.extend;
 13     v_posTypeList(1) := PosType('A001','客户资料变更');
 14     v_posTypeList.extend;
 15     v_posTypeList(2) := PosType('A002','团体资料变更');
 16     v_posTypeList.extend;
 17     v_posTypeList(3) := PosType('A003','受益人变更');
 18     v_posTypeList.extend;
 19     v_posTypeList(4) := PosType('A004','续期交费方式变更');
 20     open p_ref_postypeList for  select * from table(cast (v_posTypeList as PosTypeTable));
 21   end;
 22 end procpkg;

在湖州中定义了二个游标变量,并把它看作存款和储蓄进程的参数类型。
在存款和储蓄进程中定义了叁个嵌套表变量,对数据写进嵌套表中,然后把嵌套表进行类型调换为table,游标变量从这么些嵌套表中打开询问。外界程序调用这么些游标。
据此那个进度供给定义多个种类。

  1 create or replace type PosType as Object (
  2   posType varchar2(20),
  3   description varchar2(50)
  4 );

create or replace type PosTypeTable is table of PosType;
亟需注意,这四个门类不可能定义在黄冈中,必得独立定义,那样java层工夫采取。
在外表通过pl/sql来调用这一个历程特别简单。

  1 set serveroutput on;
  2 declare
  3   type refcursor is ref cursor;
  4   v_ref_postype refcursor;
  5   v_postype varchar2(20);
  6   v_desc varchar2(50);
  7 begin
  8   procpkg.procrefcursor('a',v_ref_postype);
  9   loop
 10     fetch  v_ref_postype into v_postype,v_desc;
 11     exit when v_ref_postype%notfound;
 12     dbms_output.put_line('posType:'|| v_postype || ';description:' || v_desc);
 13   end loop;
 14 end;

瞩目:对于游标变量,无法运用for循环来管理。因为for循环会隐式的实施open动作。而因而open
for来张开的游标%isopen是为true的。相当于暗中认可打开的。Open三个已经open的游标是破绽百出的。所以不能够使用for循环来管理游标变量。
大家重视探讨的是什么通过jdbc调用来拍卖那几个输出参数。

  1 conn = this.getDataSource().getConnection();
  2 CallableStatement call = conn.prepareCall("{call procpkg.procrefcursor(?,?)}");
  3 call.setString(1, null);
  4 call.registerOutParameter(2, OracleTypes.CURSOR);
  5 call.execute();
  6 ResultSet rsResult = (ResultSet) call.getObject(2);
  7 while (rsResult.next()) {
  8   String posType = rsResult.getString("posType");
  9   String description = rsResult.getString("description");
 10   ......
 11 }

那正是jdbc的管理办法。
Ibatis管理格局:
1.参数配置

  1 <parameterMap id="PosTypeMAP" class="java.util.Map">
  2  <parameter property="p" jdbcType="VARCHAR" javaType="java.lang.String" />
  3  <parameter property="p_ref_postypeList" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT" typeHandler="com.palic.elis.pos.dayprocset.integration.dao.impl.CursorHandlerCallBack" />
  4 </parameterMap>
  5 
  6 2.调用过程
  7   <procedure id ="procrefcursor" parameterMap ="PosTypeMAP">
  8       {call procpkg.procrefcursor(?,?)}
  9   </procedure>
 10 
 11 3.定义自己的处理器
 12   public class CursorHandlerCallBack implements TypeHandler{
 13     public Object getResult(CallableStatement cs, int index) throws SQLException {
 14         ResultSet rs = (ResultSet)cs.getObject(index);
 15         List result = new ArrayList();
 16         while(rs.next()) {
 17             String postype =rs.getString(1);
 18             String description = rs.getString(2);
 19             CodeTableItemDTO posTypeItem = new CodeTableItemDTO();
 20             posTypeItem.setCode(postype);
 21             posTypeItem.setDescription(description);
 22             result.add(posTypeItem);
 23         }
 24         return result;
 25     }
 26 
 27 
 28 
 29 4. dao方法
 30     public List procPostype() {
 31         String p = "";
 32         Map para = new HashMap();
 33         para.put("p",p);
 34         para.put("p_ref_postypeList",null);
 35          this.getSqlMapClientTemplate().queryForList("pos_dayprocset.procrefcursor",  para);
 36          return (List)para.get("p_ref_postypeList");
 37     }

以此跟jdbc的点子丰富的相似.
大家运用的是ibatis的2.0本子,相比较费心。
万一是运用2.2之上版本就特别简单的。
因为能够在parameterMap中定义多少个resultMap.那样就无须要和煦定义管理器了。
能够从分析2.0和2.0的dtd文件知道。
上面的二种艺术都以可怜的繁杂,假若单独是内需回到多个结出集,那就完全能够使用函数来兑现了。
.

  1 create or replace package procpkg is
  2    type refcursor is ref cursor;
  3    procedure procrefcursor(p varchar2, p_ref_postypeList  out refcursor);
  4    function procpostype(p varchar2) return PosTypeTable;
  5 end procpkg;
  6 
  7 create or replace package body procpkg is
  8   procedure procrefcursor(p varchar2, p_ref_postypeList out  refcursor)
  9   is
 10     v_posTypeList PosTypeTable;
 11   begin
 12     v_posTypeList :=PosTypeTable();--初始化嵌套表
 13     v_posTypeList.extend;
 14     v_posTypeList(1) := PosType('A001','客户资料变更');
 15     v_posTypeList.extend;
 16     v_posTypeList(2) := PosType('A002','团体资料变更');
 17     v_posTypeList.extend;
 18     v_posTypeList(3) := PosType('A003','受益人变更');
 19     v_posTypeList.extend;
 20     v_posTypeList(4) := PosType('A004','续期交费方式变更');
 21     open p_ref_postypeList for  select * from table(cast (v_posTypeList as PosTypeTable));
 22   end;
 23 
 24   function procpostype(p varchar2) return PosTypeTable
 25   as
 26    v_posTypeList PosTypeTable;
 27   begin
 28       v_posTypeList :=PosTypeTable();--初始化嵌套表
 29     v_posTypeList.extend;
 30     v_posTypeList(1) := PosType('A001','客户资料变更');
 31     v_posTypeList.extend;
 32     v_posTypeList(2) := PosType('A002','团体资料变更');
 33     v_posTypeList.extend;
 34     v_posTypeList(3) := PosType('A003','受益人变更');
 35     v_posTypeList.extend;
 36     v_posTypeList(4) := PosType('A004','续期交费方式变更');
 37     return  v_posTypeList;
 38   end;
 39 end procpkg;

ibatis配置

  1 <resultMap id="posTypeResultMap" class="com.palic.elis.pos.common.dto.CodeTableItemDTO">
  2    <result property="code" column="posType"/>
  3    <result property="description" column="description"/>
  4  </resultMap>
  5 
  6   <select id="procPostype" resultMap="posTypeResultMap">
  7     select * from table(cast (procpkg.procpostype(#value#) as PosTypeTable))
  8   </select>

Dao的写法跟日常查询同一

  1 public List queryPostype() {
  2   return this.getSqlMapClientTemplate().queryForList("pos_dayprocset.procPostype", null);
  3 }

有几点必要小心,这里不能够接纳索引表,而是嵌套表。
除此以外正是把嵌套表强制调换为普通表。

where dept count(dept name) > 12;

4:Oracle动态游标达成动态SQL循环遍历,和静态游标的可比。

        注: 本文 来源与:《   Oracle动态游标完结动态SQL循环遍历,和静态游标的相比较。 

动态游标能够遍历动态的表,

格式:

  1 TYPE 游标类型 IS REF CURSOR;  --定义一个动态游标
  2 游标名  游标类型;

万一查询的表的数额差异的,动态变化的,那时候能够用动态游标。

内需注意的是,动态游标的概念,

在平凡存款和储蓄进度中:内需放在 is 前面包车型客车率先行

动态游标通过:open 游标 for 字符串,方式利用,遍历。

  1 create or replace procedure P_TEST_SQL
  2 is
  3 TYPE ref_cursor_type IS REF CURSOR;  --定义一个动态游标     
  4 tablename varchar2(200) default 'ess_client';
  5 v_sql varchar2(1000);
  6 mobile varchar2(15);
  7 usrs ref_cursor_type;
  8 begin
  9   --使用连接符拼接成一条完整SQL     
 10   v_sql := 'select usrmsisdn from '||tablename||' where rownum < 11';
 11   --打开游标     
 12   open usrs for v_sql ;
 13   loop
 14       fetch usrs into mobile;
 15       exit when usrs%notfound;
 16       insert into tmp(usrmsisdn) values(mobile);
 17   end loop;
 18   close usrs;
 19   commit;
 20 end P_TEST_SQL;

上面是一个团结做过的二个实例,代码只保留了可参照他事他说加以考察部分。

  1 create or replace procedure DDGZ1--订单跟踪
  2 (
  3        P_flag varchar,--订单类型
  4        P_operate varchar,--操作类型
  5        P_BH varchar --传入的编号ID
  6 )
  7 is
  8       TYPE ref_cursor_type IS REF CURSOR;
  9        v_cur_CKD  ref_cursor_type;
 10 
 11 begin
 12    open v_cur_CKD for 'select v_ERP,v_DRP,v_jxsmc,v_jxs,v_DWMC,v_czr,v_jlsj from tmp_DDGZ1';
 13    fetch v_cur_CKD into v_ERP,v_DRP,v_jxsmc,v_jxs,v_DWMC,v_czr,v_jzsj;
 14    while v_cur_CKD%found
 15      loop
 16      insert into DRPDDGZJL2 (JLID,DRP,ERP,JXS,JXSMC,JLSJ,GZJL,CZR,BHID) values(SYS_GUID(),v_drp,v_erp,v_jxs,v_jxsmc,v_jzsj,v_DWMC||'受托代销成品库接收订单,组织发货',v_CZR,'出库单'||P_BH);
 17      fetch v_cur_CKD into v_ERP,v_DRP,v_jxsmc,v_jxs,v_DWMC,v_czr,v_jzsj;
 18      end loop;
 19      close v_cur_CKD;
 20  end;

而对此海口包体的储存进程。平日是之类格局:

在曲靖中:

  1 CREATE OR REPLACE PACKAGE BIA_FYJSDCX
  2 
  3 is  --发运结算单查询
  4   type T_CURSOR is ref cursor;
  5   procedure ERP_HY_FYJSDCX
  6   (
  7     Re_CURSOR out T_CURSOR
  8   );
  9 end BIA_FYJSDCX;

包体中:

  1 procedure ERP_HY_FYJSDCX
  2   (
  3     Re_CURSOR out T_CURSOR
  4   )
  5 begin
  6      v_sql:='select * from T_FYJSDCX4';
  7       Open Re_CURSOR For v_sql;
  8 
  9 end;

c)函数还是能重临表,称为表函数(table functions),这一定于带参数的视图

3-7:oracle存款和储蓄进度十三分捕获

        注: 本段内容来自于:《  oracle存款和储蓄进程卓绝捕获 

oracle存储进度足够捕获学习,施行及示范进度:

累积进度:

  1 CREATE OR REPLACE PROCEDURE sp_test_2
  2 (
  3    param1 in int,     --输入参数
  4    param2 in int,
  5    out_return out varchar2 --返回结果
  6 )
  7 is
  8    --全局变量
  9    val int;
 10    errorException exception; --申明异常
 11    errorCode number; --异常代号
 12    errorMsg varchar2(1000); --异常信息
 13    flag varchar2(10);
 14 begin
 15      flag := 'true';
 16      out_return := 'flag=' || flag || ',errorCode=' || errorCode || ',errorMsg=' || errorMsg;
 17      val := param1/param2;
 18      --/*
 19      exception
 20          when errorException then
 21               errorCode := SQLCODE;
 22               errorMsg := SUBSTR(SQLERRM, 1, 200);
 23               flag := 'false';
 24               out_return := 'flag=' || flag || ',errorCode=' || errorCode || ',errorMsg=' || errorMsg;
 25          when others then
 26                errorCode := SQLCODE;
 27                errorMsg := SUBSTR(SQLERRM, 1, 200);
 28                flag := 'false';
 29                out_return := 'flag=' || flag || ',errorCode=' || errorCode || ',errorMsg=' || errorMsg;
 30      --dbms_output.put_line(errorCode || ',' || errorMsg); 
 31      --*/
 32 end sp_test_2;

示范存款和储蓄进度:

  1 DECLARE
  2    out_return varchar2(1000);
  3    val int; --全局变量  
  4    errorException exception; --申明异常  
  5    errorCode number; --异常编码  
  6    errorMsg varchar2(1000); --异常信息  
  7    flag varchar2(10);
  8 begin
  9      flag := 'true';
 10      out_return := 'flag=' || flag || ',errorCode=' || errorCode || ',errorMsg=' || errorMsg;
 11      val := 1/0;
 12      exception  --异常捕捉,不要把有需要的代码放在异常捕捉后面,有异常才会执行异常代码下所有代码,没有异常不会执行  
 13          when errorException then
 14               errorCode := SQLCODE;
 15               errorMsg := SUBSTR(SQLERRM, 1, 200);
 16               flag := 'false';
 17               out_return := 'flag=' || flag || ',errorCode=' || errorCode || ',errorMsg=' || errorMsg;
 18          when others then
 19                errorCode := SQLCODE;
 20                errorMsg := SUBSTR(SQLERRM, 1, 200);
 21                flag := 'false';
 22                out_return := 'flag=' || flag || ',errorCode=' || errorCode || ',errorMsg=' || errorMsg;
 23 
 24      dbms_output.put_line(out_return);
 25 end;

sqlplus中进行存款和储蓄进程:

  1 DECLARE
  2      out_return varchar2(1000);
  3 begin
  4      sp_test_2(1,0,out_return);
  5      dbms_output.put_line(out_return); --打印结果  
  6 end;

实践存款和储蓄进度 1除以0 结果:

澳门威斯尼人平台登录 64

create function instructors of (dept_name varchar

oracle 之定义数组类型

注:本文来源:《oracle
之定义数组类型

oracle 数组类型,未有现有的项目,可是能够协和随意定义,很方便。

Oracle 数组能够分成定长数组和可变长的数组两类。以投注重是一维数组介绍:

returns table ( ID varchar , name varchar , dept_name varchar , salary
numeric

1:定长数组:

  1 /*定长字符数组,数组大小为10*/
  2 declare
  3 type v_arr is varray(10) of varchar2(30);
  4 my_arr v_arr;
  5 my_arr:=v_arr('1','2','3');
  6 begin
  7   for i in 1..my_arr.count
  8   loop
  9       dbms_output_line(my_arr(i));
 10   end loop;
 11 end;
 12 

return table

2:变长数组:

  1 /*可变长字符数组,元素大小30,索引标号integer类型自增长*/
  2 declare
  3 type v_table is table of varchar2(30) index by binary_integer;
  4 --类型可以是前面的类型定义,index by binary_integer子句代表以符号整数为索引,   
  5 --这样访问表类型变量中的数据方法就是“表变量名(索引符号整数)”。   
  6 my_table v_table;
  7 begin
  8       for i in 1..20
  9      loop
 10           my_table(i):=i;
 11           dbms_output.put_line(my_table(i));
 12       end loop;
 13 end;
 14 

(select ID, name, dept_name, salary

Oracle存款和储蓄进度 数组集结的选用

注:本文来源《 Oracle存款和储蓄过程数组聚积的选拔 

from instructor

1 说明

1.1 RECORD

概念记录数据类型。它相仿于C语言中的结构数据类型(STRUCTURE),PL/SQL提供了将多少个有关的、分离的、基本数据类型的变量组成三个完好无缺的章程,即RECORubiconD复合数据类型。在选取记录数据类型变量时,须求在宣称部分先定义记录的构成、记录的变量,然后在施行部分引用该记录变量自个儿或内部的分子。

概念记录数据类型的语法如下:

  1 TYPE RECORD_NAME IS RECORD(
  2 
  3 V1 DATA_TYPE1 [NOT NULL][:=DEFAULT_VALUE],
  4 
  5 V2 DATA_TYPE2 [NOT NULL][:=DEFAULT_VALUE],
  6 
  7 VN DATA_TYPEN [NOT NULL][:=DEFAULT_VALUE]);
  8 
1.2 VARRAY

数组是具备同样数据类型的一组成员的集合。每一个成员都有二个独一的下标,它取决于成员在数组中的地点。在PL/SQL中,数组数据类型是VACRUISERRAY(variable
array,就可以变数组)。

概念VA福睿斯RAY数据类型的语法如下:

  1 TYPE VARRAY_NAMEIS VARRAY(SIZE) OF ELEMENT_TYPE [NOT NULL];

其中,varray_name是VA瑞虎RAY数据类型的称号,size是正整数,表示能够容纳的分子的最大数量,各样成员的数据类型是element_typeo默许时,成员能够取空值,不然供给选择NOT
NULL加以限制。

1.3 TABLE

概念记录表(或索引表)数据类型。它与记录类型一般,但它是对记录类型的扩展。它能够拍卖多行记录,类似于C语言中的二维数组,使得能够在PL/SQL中效仿数据库中的表。

概念记录表类型的语法如下:

  1 TYPE TABLE NAME IS TABLE OF ELEMENT_TYPE [NOT NULL]
  2 
  3 INDEX BY [BINARY_INTEGER|PLS_INTEGER|VARRAY2];
  4 

第一字INDEX BY表示创设一个主键索引,以便援用记录表变量中的特定行。

BINARY_INTEGER的说明

如语句:TYPE NUMBERS  IS TABLE OF NUMBER INDEX BY
BINARY_INTEGE本田CR-V;其成效是,加了”INDEX BYBINA途胜Y_INTEGE凯雷德”后,NUMBEHavalS类型的下标就是自增加,NUMBEENCORES类型在插入成分时,无需起初化,无需每回EXTEND扩张贰个空中。

而若无那句话“INDEXBY
BINALX570Y_INTEGER”,这就得要体现对开头化,且每插入三个要素到NUMBETiguanS类型的TABLE中时,都急需先EXTEND。

where instructor.dept_name = instructor of.dept_name);

2 举例

恍如的成效也足以应用存款和储蓄进程:

2.1 成立表结构以及数据筹划

  1 --组织机构结构表  
  2 CREATE TABLE SF_ORG
  3 (
  4 ORG_ID INT NOT NULL, --组织机构主键ID  
  5 ORG_NAME VARCHAR2(50),--组织机构名称  
  6 PARENT_ID INT--组织机构的父级  
  7 )
  8 
  9 --一级组织机构  
 10 INSERT INTO SF_ORG(ORG_ID, ORG_NAME, PARENT_ID) VALUES(1, '一级部门1',0);
 11 
 12 --二级部门  
 13 
 14 INSERT INTO SF_ORG(ORG_ID, ORG_NAME, PARENT_ID) VALUES(2, '二级部门2',1);
 15 INSERT INTO SF_ORG(ORG_ID, ORG_NAME, PARENT_ID) VALUES(3, '二级部门3',1);
 16 INSERT INTO SF_ORG(ORG_ID, ORG_NAME, PARENT_ID) VALUES(4, '二级部门4',1);

create procedure dept_count_proc(in dept_name varchar, out d_count
integer) begin select count into d_count

2.2 RECOKoleosD的行使举个例子

先定义三个只与SF_O福特ExplorerG表中某几个列的数据类型同样的记录数据类型TYPE_ORG_RECO科雷傲D,然后声爱他美(Aptamil)个该数据类型的笔录变量V_ORG_RECOLacrosseD,最终用替换变量&O本田UR-VG_ID接受输入的雇员编码,查询并出示该雇员的这几列中的信息。注意,在运用RECOKoleosD数据类型的变量时要用“.”运算符钦命记录变量名限定词。

一个记下类型的变量只可以保留从数据库中查询出的一行记录,假诺查询出了多行记录,就能现出错误。

  1 DECLARE
  2   TYPE TYPE_ORG_RECORD IS RECORD(
  3   V_ORG_NAME SF_ORG.ORG_NAME%TYPE,
  4   V_PARENT_ID SF_ORG.PARENT_ID%TYPE);
  5   V_ORG_RECORD TYPE_ORG_RECORD;
  6 BEGIN
  7   SELECT ORG_NAME,PARENT_ID INTO V_ORG_RECORD
  8   FROM SF_ORG SO
  9   WHERE SO.ORG_ID=&ORG_ID;
 10   DBMS_OUTPUT.PUT_LINE('部门名称:' || V_ORG_RECORD.V_ORG_NAME);
 11   DBMS_OUTPUT.PUT_LINE('上级部门编码:' || TO_CHAR(V_ORG_RECORD.V_PARENT_ID));
 12 END;

from instructor

2.3 VA魅影RAY的行使比如

先定义八个能保留5个VARCHALAND2(25)数据类型的分子的VA奥迪Q5RAY数据类型OCRUISERG_VARRAY_TYPE,然后声Bellamy(Bellamy)个该数据类型的VAPRADORAY变量V_ORG_VA索罗德RAY,最终用与OPAJEROG_VARRAY_TYPE数据类型同名的构造函数语法给V_ORG_VA陆风X8RAY变量赋予初值并出示赋值结果。

专心,在援用数组中的成员时.须要在一对括号中选用各种下标,下标从1起首实际不是从0起先。

  1 DECLARE
  2   TYPE ORG_VARRAY_TYPE IS VARRAY(5) OF VARCHAR2(25);
  3   V_ORG_VARRAY ORG_VARRAY_TYPE;
  4 BEGIN
  5   V_ORG_VARRAY := ORG_VARRAY_TYPE('1','2','3','4','5');
  6   DBMS_OUTPUT.PUT_LINE('输出1:' || V_ORG_VARRAY(1) || '、'|| V_ORG_VARRAY(2) || '、'|| V_ORG_VARRAY(3) || '、'|| V_ORG_VARRAY(4));
  7   DBMS_OUTPUT.PUT_LINE('输出2:' || V_ORG_VARRAY(5));
  8   V_ORG_VARRAY(5) := '5001';
  9   DBMS_OUTPUT.PUT_LINE('输出3:' || V_ORG_VARRAY(5));
 10 END;

where instructor.dept_name= dept_count proc.dept_name

2.4 TABLE使用譬如

end

2.4.1 存款和储蓄单列多行

本条和VATiggoRAY类似。可是赋值方式有些有一点差异,无法应用同名的构造函数实行赋值。具体的如下:

  1 DECLARE
  2   TYPE ORG_TABLE_TYPE IS TABLE OF VARCHAR2(25)
  3   INDEX BY BINARY_INTEGER;
  4   V_ORG_TABLE ORG_TABLE_TYPE;
  5 BEGIN
  6   V_ORG_TABLE(1) := '1';
  7   V_ORG_TABLE(2) := '2';
  8   V_ORG_TABLE(3) := '3';
  9   V_ORG_TABLE(4) := '4';
 10   V_ORG_TABLE(5) := '5';
 11   DBMS_OUTPUT.PUT_LINE('输出1:' || V_ORG_TABLE(1) || '、'|| V_ORG_TABLE(2) || '、'|| V_ORG_TABLE(3) || '、'|| V_ORG_TABLE(4));
 12   DBMS_OUTPUT.PUT_LINE('输出2:' || V_ORG_TABLE(5));
 13 END;

in和out表示数据的输入输出。存款和储蓄进程还足以重载。

2.4.2 存款和储蓄多列多行和ROWTYPE结合使用

采取bulkcollect能够将查询结果三回性地加载到collections中。并非通过cursor一条一条地管理。

  1 DECLARE
  2    TYPE T_TYPE IS TABLE OF SF_ORG%ROWTYPE;
  3    V_TYPE  T_TYPE;
  4  BEGIN
  5     SELECT ORG_ID,ORG_NAME,PARENT_ID BULK COLLECT INTO V_TYPE
  6     FROM SF_ORG
  7     WHERE SF_ORG.ORG_ID <= 3;
  8 
  9     FOR V_INDEX IN V_TYPE.FIRST .. V_TYPE.LAST LOOP
 10         DBMS_OUTPUT.PUT_LINE(V_TYPE(V_INDEX).C1 || ' ' || V_TYPE(V_INDEX).C2);
 11     END LOOP;
 12  END;

d)存储进程和函数的区分:

2.4.3 存款和储蓄多列多行和RECO汉兰达D结合使用

行使bulkcollect能够将查询结果叁遍性地加载到collections中。并非透过cursor一条一条地处理。

  1 DECLARE
  2    TYPE TEST_EMP IS RECORD
  3    (
  4     C1 SF_ORG.ORG_NAME%TYPE,
  5     C2 SF_ORG.PARENT_ID%TYPE
  6    );
  7    TYPE T_TYPE IS TABLE OF TEST_EMP;
  8    V_TYPE  T_TYPE;
  9  BEGIN
 10     SELECT ORG_NAME,  PARENT_ID BULK COLLECT INTO V_TYPE
 11     FROM SF_ORG
 12     WHERE SF_ORG.ORG_ID <= 3;
 13 
 14     FOR V_INDEX IN V_TYPE.FIRST .. V_TYPE.LAST LOOP
 15         DBMS_OUTPUT.PUT_LINE(V_TYPE(V_INDEX).C1 || ' ' || V_TYPE(V_INDEX).C2);
 16     END LOOP;
 17  END;

函数只可以通过return语句重回单个值或然表对象。而存款和储蓄进程不允许实践return,然而经过out参数再次来到三个值;

Oracle存款和储蓄进程自定义数组定义与行使

近来为同盟社项目数据库通过存款和储蓄进程做归档,要求用到自定义数组

百度结果中非常多写的都不是很明显,可变长数组定义好后,怎么样运用。

在此做个记录:

定义:

type id_array is table of number(15) index by binary_integer;

acb_ids   id_array;

本条定义方式适用在package,假设是纯粹在仓库储存进程中自定义类型请百度 。

使用:

acb_ids(acb_ids.count+1) := c_account_books_cbs_rec.acb_id;

网络广大实例是那样的:acb_ids(acb_ids.count) :=
c_account_books_cbs_rec.acb_id;这样写是不曾用的,因为那年acb_ids是空的,那么acb_ids.count也是没用的,acb_ids在选取时回会报”未找到任何数据”,由此要求acb_ids.count+1。

c_account_books_cbs_rec为游标遍历中的多个对象,上述代码意思是将游标数据中每行记录的ID放到自定义数组中,方便存款和储蓄进程的值重回大概游标遍历外程序体中选用。

for x in 1 .. acb_ids.count loop
        do something;
end loop;

函数是能够放置在sql中利用的,能够在select中调用,而存储进程特别;

储存进程中定义参数类型为数组

注意:本文来源:《仓库储存进程中定义参数类型为数组》

函数限制非常多,比如无法用一时表,只可以用表变量.还恐怕有局地函数都不可用等等.而存款和储蓄进程的限定相对就相当少;

1:存款和储蓄进度

  1 Procedure Update_Batch_Id(p_entity_id in Number,
  2                             p_vdr_id    In fnd_table_of_number) is
  3   begin
  4 
  5     For i In 1 .. p_vdr_id.COUNT Loop
  6       update cux_table_header cvs
  7          set cvs.attribute10 = p_entity_id
  8        where cvs.header_id = p_vdr_id(i);
  9     End Loop;
 10   end;

相似的话,存款和储蓄进程落成的意义要复杂一点,而函数的贯彻的法力针对性比较强。

2:JAVA中调用

  1 List list = new ArrayList();
  2 ...
  3 list.add(row.getHeaderId());
  4 ...
  5 
  6 
  7  OracleCallableStatement statement = null;
  8         OracleConnection oracleConnection = (OracleConnection)tsn.getJdbcConnection();
  9 int size = list.size();
 10 if (size>0)
 11         {
 12            Number[] vdrIdArray = (Number[])list.toArray(new Number[size]);
 13             ARRAY vdrArray=null;
 14 try {
 15                           ArrayDescriptor tableOfNumber =
 16                               oracle.sql.ArrayDescriptor.createDescriptor("FND_TABLE_OF_NUMBER",
 17                                                                           oracleConnection);
 18                          vdrArray = new ARRAY(tableOfNumber, oracleConnection, vdrIdArray);
 19                        String sql =
 20                            "BEGIN cux_XXXXXXX_pkg.Update_Batch_Id(:1,:2);end;";
 21                        statement = (OracleCallableStatement)oracleConnection.prepareCall(sql);
 22 
 23                        statement.setObject(1, batchid);
 24                        statement.setARRAY(2, vdrArray);
 25                        statement.execute();
 26                       }catch (Exception ex) {
 27                                String[][] stra2 = { { "123456wewee", ex.getMessage() }, };
 28                                LogUtil.of(stra2, this).print(pageContext);
 29                             ex.printStackTrace();
 30                             System.out.println(ex.getMessage());
 31                            }
 32 }

——————————————————————————————————————————————————————————————————————————————————————————

二、SQL的语法结构

二:函数

澳门威斯尼人平台登录 65

a)SQL也像Java、C等语言同样辅助if、for等语法结构,用declare评释变量、用set赋值,但一段SQL要写在begin…end之间,使用begin
atomic…end的话,内部的语句构成几个工作。

1:创设函数

澳门威斯尼人平台登录 66澳门威斯尼人平台登录 67

  1 create or replace function get_avg_pay(num_deptno number )return number is /*创建一个函数,该函数实现计算某个部门的平均工资,传入部门编号参数*/
  2   num_avg_pay number; /*保存平均工资的内部变量*/
  3 begin
  4    select avg(sal) into num_avg_pay from emp where deptno=num_deptno;/*某个部门的平均工资*/
  5    return (round(num_avg_pay));
  6 exception
  7    when no_data_found then  /*如果此部门编号不存在*/
  8       dbms_output.put_line('该部门编号不存在');
  9     return(0); /*返回平均工资0*/
 10 end;
 11 /

澳门威斯尼人平台登录 68

b)while和repeat

2:调用函数

澳门威斯尼人平台登录 69

  1 set serveroutput on
  2 declare
  3   avg_pay number ;/*定义变量,储存函数返回值*/
  4 begin
  5   avg_pay :=get_avg_pay(10);
  6   dbms_output.put_line('平均工资是:'||avg_pay);
  7 end;
  8 /
  9 

澳门威斯尼人平台登录 70

while boolean expression do

3:删除函数

澳门威斯尼人平台登录 71

  1 drop function get_avg_pay;
  2 
  3 

澳门威斯尼人平台登录 72

sequence of statements;

三:触发器

澳门威斯尼人平台登录 73

end while

1:触发器钙素

澳门威斯尼人平台登录 74澳门威斯尼人平台登录 75

澳门威斯尼人平台登录 76

repeat

2:语句级触发器

澳门威斯尼人平台登录 77澳门威斯尼人平台登录 78

  1 
  2 /*在scott模式下 创建dept_log数据表,并在其中定义连个字段,分别用来
  3 储存操作种类信息和操作日期*/
  4 create table dept_log
  5 (
  6    operate_tag varchar2(10),/*定义字段,储存操作种类信息*/
  7    operate_time date /*定义字段,储存操作日期*/
  8 );
  9 
 10 
 11 

澳门威斯尼人平台登录 79

澳门威斯尼人平台登录 80

  1 
  2 /*创建一个触发器 tri_dept,该触发器在 insert、update、delete 事件下
  3 都可以被触发,并且操作的数据对象是dept,要求在触发器执行时输出对dept表所做的具体操作*/
  4 create or replace trigger tri_dept
  5 before insert or update or delete
  6 on dept /*创建触发器,当dept表发生插入、修改、删除等操作时引起的触发器执行*/
  7 declare
  8    var_tag varchar2(10);
  9 begin
 10    if inserting then /*当触发器事件是 insert 时*/
 11           var_tag:='插入';
 12     elsif updating then /*当触发器事件是 update 时*/
 13            var_tag:='修改';
 14     elsif deleting then  /*当触发事件是delete时*/
 15            var_tag:='删除';
 16     end if;
 17 insert into dept_log values(var_tag,sysdate);
 18 end tri_dept;
 19 /

澳门威斯尼人平台登录 81

澳门威斯尼人平台登录 82

澳门威斯尼人平台登录 83

  1 insert into  dept values(66,'adfasdf','fsdafd');
  2 update dept set loc='w235f' where deptno=66;
  3 delete from dept where deptno=66;
  4 

澳门威斯尼人平台登录 84

澳门威斯尼人平台登录 85

澳门威斯尼人平台登录 86

sequence of statements;

3:行等第触发器

澳门威斯尼人平台登录 87

  1 /*在scott模式下,创建一个用于储存商品种类的数据表,其中包括商品序号和商品名称*/
  2 create table goods(
  3   id int primary key,
  4   good_name varchar2(50)
  5 );

澳门威斯尼人平台登录 88

澳门威斯尼人平台登录 89

  1 
  2 /*使用create sequence语句创建一个序列,命名为seq_id */
  3 create sequence seq_id;
  4 

澳门威斯尼人平台登录 90

澳门威斯尼人平台登录 91

  1 /*创建一个行级别触发器,该触发器在数据表goods插入数据时被触发,并且在该触发器的主体中实现设置
  2 goods表的id列的值。*/
  3 create or replace trigger tri_insert_good
  4 before insert
  5   on goods /*关于goods 数据表,在向其插入新记录之前,引发该触发器的运行*/
  6   for each row /*创建行触发器*/
  7 begin
  8    select  seq_id.nextval into :new.id from dual; /*从序列号中生成 一个新的数值,赋值给当前插入的行的id*/
  9 end;
 10 /
 11 

澳门威斯尼人平台登录 92

澳门威斯尼人平台登录 93

  1 
  2 /*向goods表中插入两条记录,其中一条记录不指定id列的值,由序列seq_id来产生;另一条记录指定id的值*/
  3 insert into goods(good_name) values('asdfasdf');
  4 insert into goods(id,good_name) values(9,'asdtet');
  5 
  6 

澳门威斯尼人平台登录 94

澳门威斯尼人平台登录 95

澳门威斯尼人平台登录 96

澳门威斯尼人平台登录 97澳门威斯尼人平台登录 98

until boolean expression

4:替换触发器

澳门威斯尼人平台登录 99

  1 With the Partitioning, OLAP, Data Mining and Real Application Testing options
  2 [oracle@localhost ~]$ sqlplus / as sysdba;
  3 
  4 SQL*Plus: Release 11.2.0.3.0 Production on Sun Jan 7 16:38:32 2018
  5 
  6 Copyright (c) 1982, 2011, Oracle.  All rights reserved.
  7 
  8 
  9 Connected to:
 10 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
 11 With the Partitioning, OLAP, Data Mining and Real Application Testing options
 12 
 13 SYS@orcl> alter user system identified by oracle;
 14 
 15 User altered.
 16 
 17 SYS@orcl> conn system/oracle;
 18 Connected.
 19 SYSTEM@orcl> grant create view to scott;
 20 
 21 Grant succeeded.
 22 
 23 SYSTEM@orcl> conn scott/scott;
 24 Connected.
 25 SQL> create view view_emp_dept
 26   2  as
 27   3  select empno,ename,dept.deptno,dept.dname,job,hiredate from emp,dept where emp.deptno=dept.deptno;
 28 
 29 View created
 30 

澳门威斯尼人平台登录 100

澳门威斯尼人平台登录 101

  1 
  2 create or replace trigger tri_insert_view
  3   instead of insert
  4   on view_emp_dept    /*创建一个关于 view_emp_dept视图的替换触发器*/
  5   for each row  /*行级别视图*/
  6 declare
  7    row_dept dept%rowtype;
  8 begin
  9   select * into  row_dept from dept where deptno =:new.deptno;/*查询指定部门编号的记录行*/
 10  if sql%notfound then
 11      insert into dept(deptno,dname) values(:new.deptno,:new.dname);/*向dept表中插入数据*/
 12   end if;
 13   insert into emp(empno,ename,deptno,job,hiredate)
 14   values(:new.empno,:new.ename,:new.deptno,:new.job,:new.hiredate);/*向emp表中插入数据*/
 15 end tri_insert_view;
 16 /
 17 

澳门威斯尼人平台登录 102

澳门威斯尼人平台登录 103

澳门威斯尼人平台登录 104

澳门威斯尼人平台登录 105

end repeat

5:顾客事件触发器

澳门威斯尼人平台登录 106

澳门威斯尼人平台登录 107

  1 
  2 /*
  3  常见用户事件:
  4  create 、alter 、drop、analyze、comment、grant、revoke、rename、truncate、uspend、logon、logoff
  5  使用create table 语句创建一个日志信息表,该表保存的日志信息包括数据对象、数据对象类型、操作行为、操作用户 等操作日期等。
  6 */
  7 create table ddl_oper_log(
  8   db_obj_name varchar2(20),/*数据对象名称*/
  9   db_obj_type varchar2(20),/*数据对象类型*/
 10   oper_action varchar2(20),/*操作行为*/
 11   oper_user varchar2(20),/*操作用户*/
 12   oper_date   date /*操作日期*/
 13 );

澳门威斯尼人平台登录 108

澳门威斯尼人平台登录 109

  1 create or replace trigger tri_ddl_oper
  2 
  3 /*关于scott用户的ddl 操作,(这里包括 create alter  drop )
  4 创建一个触发器,然后讲DDL操作的相关信息插入到 ddl_oper_log 日志表中*/
  5        before create or alter or drop
  6        on scott.schema /*在scott模式下,在创建、修改、删除数据库对象之前将引发触发器运行*/
  7 begin
  8       insert into ddl_oper_log values(
  9         ora_dict_obj_name,/*操作的数据对象名称*/
 10         ora_dict_obj_type ,/*操作的数据对象类型 */
 11         ora_sysevent,/*系统事件名称*/
 12         ora_login_user,/*登录用户*/
 13         sysdate  );
 14 
 15 end;
 16 /

澳门威斯尼人平台登录 110

澳门威斯尼人平台登录 111

澳门威斯尼人平台登录 112

澳门威斯尼人平台登录 113澳门威斯尼人平台登录 114

  1 SQL> create table tb_test(id number);
  2 
  3 Table created
  4 
  5 SQL> create view view_test as select empno,ename from emp;
  6 
  7 View created
  8 
  9 SQL> alter table tb_test add (name varchar2(10));
 10 
 11 Table altered
 12 
 13 SQL> drop view view_test;
 14 
 15 View dropped
 16 
 17 SQL> select * from ddl_oper_log;
 18 
 19 DB_OBJ_NAME          DB_OBJ_TYPE          OPER_ACTION          OPER_USER            OPER_DATE
 20 -------------------- -------------------- -------------------- -------------------- -----------
 21 TB_TEST              TABLE                CREATE               SCOTT                2018/1/7 21
 22 VIEW_TEST            VIEW                 CREATE               SCOTT                2018/1/7 21
 23 TB_TEST              TABLE                ALTER                SCOTT                2018/1/7 21
 24 VIEW_TEST            VIEW                 DROP                 SCOTT                2018/1/7 21
 25 

View Code

澳门威斯尼人平台登录 115

澳门威斯尼人平台登录 116

c)for

四:程序包

澳门威斯尼人平台登录 117

declare n integer default 0;

1:程序包的行业内部

澳门威斯尼人平台登录 118

澳门威斯尼人平台登录 119澳门威斯尼人平台登录 120

  1 
  2 /*创建一个程序包的规范,首先在该程序包中声明一个可以获取指定部门的平均工资的函数,
  3 然后在声明一个可以实现按照指定比例上调职务的工资的储存过程*/
  4 create or replace package pack_emp is
  5   function fun_avg_sal(num_deptno number) return number;/*获取指定部门的平均工资*/
  6   procedure pro_regulate_sal(var_job varchar2,num_proportion number) ;/*按照指定比例上调指定职务的工资*/
  7 end pack_emp;
  8 /

View Code

澳门威斯尼人平台登录 121

for r as

2:程序包主体

澳门威斯尼人平台登录 122

澳门威斯尼人平台登录 123

澳门威斯尼人平台登录 124

澳门威斯尼人平台登录 125

  1 create or replace package body pack_emp is
  2   function fun_avg_sal(num_deptno number) return number is  --引入“规范”中的函数
  3     num_avg_sal number;--定义内部变量
  4   begin
  5     select avg(sal)
  6     into num_avg_sal
  7     from emp
  8     where deptno = num_deptno;--计算某个部门的平均工资
  9     return(num_avg_sal);--返回平均工资
 10   exception
 11     when no_data_found then--若未发现记录
 12       dbms_output.put_line('该部门编号不存在雇员记录');
 13     return 0;--返回0
 14   end fun_avg_sal;
 15 
 16   procedure pro_regulate_sal(var_job varchar2,num_proportion number) is--引入“规范”中的存储过程
 17   begin
 18     update emp
 19     set sal = sal*(1+num_proportion)
 20     where job = var_job;--为指定的职务调整工资
 21   end pro_regulate_sal;
 22 end pack_emp;
 23 /
 24 
 25 
 26 
 27 
 28 
 29 
 30 
 31 
 32 
 33 
 34 
 35 
 36 
 37 

澳门威斯尼人平台登录 126

  1 set serveroutput on
  2 declare
  3   num_deptno emp.deptno%type;--定义部门编号变量
  4   var_job emp.job%type;--定义职务变量
  5   num_avg_sal emp.sal%type;--定义工资变量
  6   num_proportion number;--定义工资调整比例变量
  7 begin
  8   num_deptno:=10;--设置部门编号为10
  9   num_avg_sal:=pack_emp.fun_avg_sal(num_deptno);--计算部门编号为10的平均工资
 10   dbms_output.put_line(num_deptno||'号部门的平均工资是:'||num_avg_sal);--输出平均工资
 11 
 12   var_job:='SALESMAN';--设置职务名称
 13   num_proportion:=0.1;--设置调整比例
 14   pack_emp.pro_regulate_sal(var_job,num_proportion);--调整指定部门的工资
 15 end;
 16 /
 17 

澳门威斯尼人平台登录 127


select budget from department where dept name = ‘Music‘

do

set n = n− r.budget

end for

d)if

if boolean expression

then statement or compound statement

elseif boolean expression

then statement or compound statement else statement or compound
statement

end if

三、触发器Trigger

a)触发器包罗三个成分:被触发的火候、被触发后举行的动作。

在数据库自带的一致性约束机制无法满足工作需要时,能够用触发器来限制;也足以达成监督、报警、自动化等急需。

b)触发器的创设

create trigger timeslot_check1 after insert on section

referencing new row as nrow

for each row

when (nrow.time slot_id not in (

select time slot_id

from time_slot))

begin

rollback

end;

为在section表insert时创制的触发器,referencing new row as
nrow会将被插入的行保存到nrow一时变量,然后使用for each row来遍历。

除了插入操作,删除的触发器写法为:

create trigger timeslot_check2 after delete on timeslot

referencing old row as orow

for each row

when (orow.time slot_id not in (

select time slot_id

from time_slot)

and orow.time slot_id in (

select time slot_id from section)) begin

rollback

end;

暂且保存的是删除前的旧行,那么update时新行、旧行都要求:

create trigger credits_earned after update of takes on

referencing new row as nrow

referencing old row as orow

for each row

when …

begin atomic

end;

唯有takes.grade被更新时才会被触发

c)除了用after定义动作发生后的触发器,还足以选用before在动作爆发前触发;除了针对行的触发器(for
each row),还会有针对表的触发器,对应的语法有;refenencing old/new table
as、for each statement

d)触发器就算能够用来消除大多主题素材,但要是有顶替格局,便不引入使用触发器,因为触发器的百无一是只好在运作时意识,并且几个触发器的关联会促成维护的紧Baba。

读书材质:Database System Concepts, by 亚伯拉罕 Silberschatz, Henry
F.Korth, S.Sudarshan

发表评论

电子邮件地址不会被公开。 必填项已用*标注