Oracle----存储过程

存储过程

基本结构

　　Oracle存储过程包含三部分：过程声明，执行过程部分，存储过程异常（可写可不写，要增强脚本的容错性和调试的方便性那就写上异常处理）

无参存储过程

• 这里的as和is一样任选一个，在这里没有区别，其中demo是存储过程名称。

CREATE OR REPLACE PROCEDURE demo AS/IS
	变量2 DATE;
	变量3 NUMBER;
BEGIN
	--要处理的业务逻辑
END　

有参存储过程

下面脚本中，

• 第1行：param1 是参数，类型和student表id字段的类型一样。
• 第3行：声明变量name，类型是student表name字段的类型（同上）。
• 第4行：声明变量age，类型数数字，初始化为20

CREATE OR REPLACE PROCEDURE 存储过程名称(param1 student.id%TYPE)
AS/IS
name student.name%TYPE;
age number :=20;
BEGIN
  --业务处理.....
END

带参数的存储过程并且进行赋值

下面脚本中：

• 其中参数IN表示输入参数，是参数的默认模式。
• OUT表示返回值参数，类型可以使用任意Oracle中的合法类型。
• OUT模式定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递回调用他的过程
• IN OUT表示该参数可以向该过程中传递值，也可以将某个值传出去
• 第7行：查询语句，把参数s_age作为过滤条件，INTO关键字，把查到的结果赋给total变量。
• 第8行：输出查询结果，在数据库中“||”用来连接字符串
• 第9—11行：做异常处理

CREATE OR REPLACE PROCEDURE 存储过程名称(
       s_no in varchar,
       s_name out varchar,
       s_age number) AS
total NUMBER := 0;
BEGIN
  SELECT COUNT(1) INTO total FROM student s WHERE s.age=s_age;
  dbms_output.put_line('符合该年龄的学生有'||total||'人');
  EXCEPTION
    WHEN too_many_rows THEN
    DBMS_OUTPUT.PUT_LINE('返回值多于1行');
END;

　　

存储过程语法

这里s，m，n是变量，类型是number；

分类	运算符	含义	示例表达式
算术运算符	+	加	s := 2 + 2;
	-	减	s := 3 – 1;
	*	乘	s := 2 * 3;
	/	除	s := 6 / 2;
	mod(,)	取模，取余	m : = mod(3,2)
	**	乘方	10**2 =100
关系运算符	=	等于	s = 2
	<>或!=或~=	不等于	s != 2
	<	小于	s < 3
	>	大于	s > 0
	<=	小于等于	s <= 9
	>=	大于等于	s >= 1
比较运算符	LIKE	满足匹配为true	‘li’ like ‘%i’返回true
	BETWEEN	是否处于一个范围中	2 between 1 and 3 返回true
	IN	是否处于一个集合中	‘x’ in (‘x’,’y’) 返回true
	IS NULL	判断变量是否为空	若：n:=3,n is null,返回false
逻辑运算符	AND	逻辑与	s=3 and c is null
	OR	逻辑或	s=3 or c is null
	NOT	逻辑非	not c is null
其他	:=	赋值	s := 0;
	..	范围	1..9，即1至9范围
	||	字符串连接	‘hello’||’world’

存储过程语法

IF..END IF

IF s_sex=1 THEN
  dbms_output.put_line('这个学生是男生');
END IF

IF..ELSE..END IF

IF s_sex=1 THEN
  dbms_output.put_line('这个学生是男生');
ELSE
  dbms_output.put_line('这个学生是女生');
END IF

循环语句

LOOP
  IF 表达式 THEN
    EXIT;
  END IF
END LOOP;

while循环

WHILE 表达式 LOOP
  dbms_output.put_line('haha');
END LOOP;

for循环

FOR a in 10 .. 20 LOOP
  dbms_output.put_line('value of a: ' || a);
END LOOP;

常用的游标属性

属性	描述
%FOUND	如果DML语句执行后影响有数据被更新或DQL查到了结果，返回true。否则，返回false。
%NOTFOUND	如果DML语句执行后影响有数据被更新或DQL查到了结果，返回false。否则，返回true。
%ISOPEN	游标打开时返回true，反之，返回false。
%ROWCOUNT	返回DML执行后影响的行数。

使用游标

#声明游标定义游标的名称和相关的SELECT语句：
CURSOR cur_cdd IS SELECT s_id, s_name FROM student;
#打开游标游标分配内存，使得它准备取的SQL语句转换成它返回的行：
OPEN cur_cdd;
#抓取游标中的数据，可用LIMIT关键字来限制条数，如果没有默认每次抓取一条：
FETCH cur_cdd INTO id, name ;
#关闭游标来释放分配的内存:
CLOSE cur_cdd;

pl/sql处理存储过程

新建存储过程：右键procedures，点击new，弹出PROCEDURE框，再点击OK，如下图：

在下面的编辑区，编写存储过程脚本

这里我们编写一个demo_cdd存储过程，要求输出“hello world”，如下图：

右键刚才新建的存储过程名称，点击“Test”，在点击执行按钮

pl/sql调试存储过程

首先，当前这个用户得有能调试存储过程的权限，如果没有的话，以数据库管理员身份给你这个用户授权：

GRANT DEBUG ANY PROCEDURE,DEBUG CONNECT SESSION TO username;

(1).右键一个存储过程名称，点击测试（TEST），如下图：

这里我用的pl/sql是12.0.4版本的，下面截图中与低版本的pl/sql按钮位置都相同，只是图标不一样。

 

 (2).点击两次step into按钮，进入语句调试，如下图：

 (3).每点击一次step into按钮，会想下执行一条语句，也可以查看变量和表达式的值，如下图：

• 查看变量值：在查看变量区域，在Variable列手动输入变量i，在Value列点击下，该变量的值就显示出来了。

 

实战

　　有表student(s_no, s_name, s_age, s_grade)，其中s_no-学号，也是主键，是从1开始向上排的（例如：第一个学生学号是1，第二个是2，一次类推）；s_name-学生姓名；s_age-学生年龄；s_grade-年级；这张表的数据量有几千万甚至上亿。一个学年结束了，我要让这些学生全部升一年级，即，让s_grade字段加1。

这条sql，写出来如下：

update student set s_grade=s_grade+1

分析：

　　如果我们直接运行运行这条sql，因数据量太大会把数据库undo表空间撑爆，从而发生异常。那我们来写个存储过程，进行批量更新，我们每10万条提交一次。

CREATE OR REPLACE PROCEDURE process_student is
total NUMBER := 0;
i NUMBER := 0;
BEGIN
  SELECT COUNT(1) INTO total FROM student;
  WHILE i<=total LOOP
    UPDATE student SET grade=grade+1 WHERE s_no=i;
    i := i + 1;
    IF i >= 100000 THEN
      COMMIT;
    END IF;
  END LOOP;
  dbms_output.put_line('finished!');
END;

直接执行存储过程

　　如果需要将下面脚本转成存储过程存放到数据库中，直接将 declare 删除。就行

删除所有用户的表/视图/等

declare
    v_name all_tables.table_name%type;
    cursor mycur is select table_name from all_tables where owner='TICM1';
begin
    open mycur;
    loop
        fetch mycur into v_name;
        exit when mycur%NOTFOUND OR mycur%NOTFOUND IS NULL;
        execute immediate 'drop table '|| v_name;
    end loop;
    close mycur;
end;

declare
    v_name all_views.view_name%type;
    cursor mycur is select view_name from all_views where owner='ticm1';
begin
    open mycur;
    loop
        fetch mycur into v_name;
        exit when mycur%NOTFOUND OR mycur%NOTFOUND IS NULL;
        execute immediate 'drop view '|| v_name ;
    end loop;
    close mycur;
end;

declare
    v_name ALL_SEQUENCES.SEQUENCE_NAME%type;
    cursor mycur is select SEQUENCE_NAME from ALL_SEQUENCES where SEQUENCE_OWNER='TICM1';
begin
    open mycur;
    loop
        fetch mycur into v_name;
        exit when mycur%NOTFOUND OR mycur%NOTFOUND IS NULL;
        execute immediate 'drop SEQUENCE  '|| v_name ;
    end loop;
    close mycur;
end;


declare
    v_name ALL_PROCEDURES.PROCEDURE_NAME%type;
    cursor mycur is select OBJECT_NAME from ALL_PROCEDURES where OWNER='TICM1';
begin
    open mycur;
    loop
        fetch mycur into v_name;
        exit when mycur%NOTFOUND OR mycur%NOTFOUND IS NULL;
        execute immediate 'drop PROCEDURE  '|| v_name ;
    end loop;
    close mycur;
end;

将上面脚本变成存储过程

create PROCEDURE test1
AS
    v_name ALL_PROCEDURES.PROCEDURE_NAME%type;
    cursor mycur is select OBJECT_NAME from ALL_PROCEDURES where OWNER='TICM1';
BEGIN
    open mycur;
    dbms_output.put_line('open：');
    loop
        fetch mycur into v_name;
         dbms_output.put_line('fetch：');
        IF mycur%NOTFOUND OR mycur%NOTFOUND IS NULL THEN
            dbms_output.put_line('退出：');
            exit;
        ELSE
          execute immediate 'drop PROCEDURE  '|| v_name;
        END IF;
    end loop;
    close mycur;
END;