存储过程是由一个或多个T-SQL语句组成的语句组,它可以接受输入参数和输出参数且能向调用程序返回多个值;可以调用其他过程;可以向调用程序返回结果状态值以标记执行成功或失败;

Oracle存储过程

一个简单的存储过程案例:

1
2
3
4
5
create or replace procedure myDemo01
as
begin
dbms_output.put_line('hello word, my name is stored procedure');
end;
  • create or replace procedure:关键字用来创建或覆盖一个原有的存储过程。

  • myDemo01:自定义的存储过程的名字。

  • as:关键字。注:

    • 在存储过程(PROCEDURE)和函数(FUNCTION)中没有区别;
    • 在视图(VIEW)中只能用AS不能用IS;
    • 在游标(CURSOR)中只能用IS不能用AS。
  • begin:关键字。

  • dbms_output.put_line(‘hello word’); 换行输出内容。

  • end 关键字。

ps:不区分大小写;以begin与end为核心体(类似于编程语言的{});每条语句以”;”结尾

1.sql的三大部分

声明部分、可执行部分与异常处理部分

1
2
3
4
5
6
declare 
-- 声明部分
i integer ;
begin
dbms_output.put_line("hello world");
end;
  1. || 表示拼接符号(sql server的拼接符号为+)
  2. 声明变量的方式为:变量名 变量类型(长度) eg: name varchar2(length);
  3. 变量赋值方式:
    3.1 直接赋值语句 :=(这是赋值符号,区别于其他语言) eg:name := ‘chd’;
    3.2 语句赋值 使用 select 值 into 变量 from dual;

2.变量类型

变量主要分为两大块:普通变量类型引用型变量

普通变量类型:前提你要了解数据库字段的类型和大小

  • 声明变量的方式为:变量名 变量类型(长度) eg: name varchar2(length);
  • 变量赋值方式:
    • 直接赋值语句 :=(这是赋值符号,区别于其他语言) eg:name := ‘chd’;
    • 语句赋值 使用 select 值 into 变量 from dual;
      普通变量类型的SQL查询

引用型变量:变量的类型和长度决定表中字段的类型和长度

  • 声明变量的方式: 变量名 表名.列名%TYPE (即定义的变量的数据类型借用某个表下的某一列的数据类型)
1
2
3
4
5
6
7
8
9
10
eg:name 表名.列名%TYPE (推荐使用,不必了解数据库架构) 
declare
v_name TW_DATAS.mny%TYPE;
age tw_datas.rate%TYPE;

begin

select mny, rate into v_name, age from TW_DATAS where ly_rate = 12;
dbms_output.put_line( ' hello java' || 'MNY: ' ||' '|| v_name || 'RATE: ' || age); --打印hello world
end;
  • 变量赋值方式:
    • 直接赋值语句 :=(这是赋值符号,区别于其他语言) eg:name := ‘chd’;
    • 语句赋值 使用 select 值 into 变量 from dual;
      普通变量类型的SQL查询

3. 流程控制

  1. 判断if…then…elsif…then…else…end if
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
BEGIN 
IF 条件 THEN 执行一;
ELSIF 条件二 THEN 执行二;
ELSE 执行否则;
END IF ;
END
-- 举例说明
declare
v_emp TW_DATAS%ROWTYPE;
v_count number;
begin
select count(distinct item_name) into v_count from Tw_Datas;
dbms_output.put_line(v_count);
if v_count >= 300 then
dbms_output.put_line(v_count + 1);
elsif 200 < v_count then
dbms_output.put_line(v_count - 100);
else
dbms_output.put_line(v_count);

end if;
end;
  1. 循环Loop
  • for循环

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    create or replace procedure mydemo09
    as
    begin

    for stu in (select * from students) loop
    if (stu.id<5) then
    dbms_output.put_line(stu.id);
    end if;
    end loop;
    commit;
    end;

    -- 调用存储过程
    begin
    mydemo09();
    end;
  • while循环

1
2
3
4
5
6
7
8
9
10
11
12
create or replace procedure test_while_loop as
n_count number := 0;
begin
while n_count < 10 loop
dbms_output.put_line(n_count);
n_count := n_count + 1;
end loop;
end;

begin
test_while_loop();
end;

4.游标cursor

基本概念

游标(Cursor)是处理数据的一种方法,为了查看或者处理结果集中的数据,游标提供了在结果集中一次一行或者多行前进或向后浏览数据的能力。

游标的分类

游标有两种类型:显式游标和隐式游标。

        一般我们用到的SELECT…INTO…查询语句,一次只能从数据库中提取一行数据,对于这种形式的查询和DML操作,系统都会使用一个隐式游标。但是如果要提取多行数据,就需要由程序员定义一个显式游标,并通过与游标有关的语句进行处理。显式游标对应一个返回结果为多行多列的SELECT语句。 游标一旦打开,数据就从数据库中传送到游标变量中,然后应用程序再从游标变量中分解出需要的数据,并进行处理。

        简单的DML操纵语句和单行查询语句会使用隐式游标,这些语句分别是插入insert语句,更新update语句,删除delete语句,单行select查询语句。

        当系统使用一个隐式游标时,可以通过隐式游标的属性来了解操作的状态和结果,进而控制程序的流程。隐式游标可以使用名字SQL来访问,但要注意,通过SQL游标名总是只能访问前一个DML操作或单行SELECT操作的游标属性。所以通常在刚刚执行完操作之后,立即使用SQL游标名来访问属性。

游标的属性有以下四种:

  1. SQL%ROWCOUNT 返回值为一个整型数字 代表DML语句成功执行的数据行数
      2. SQL%FOUND 布尔型 值为TRUE代表插入、删除、更新或单行查询操作成功
      3. SQL%NOTFOUND 布尔型 值为true表示插入、删除、更新或单行查询操作失败。
      4. SQL%ISOPEN 布尔型 DML执行过程中为真,结束后为假

游标的使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- 使用游标查询emp表中所有员工的姓名和工资
declare
-- 声明游标,c_emp为游标名
cursor c_emp is
-- 业务代码块
select name,salary from emp;
-- 声明变量用来接收游标中的元素
psnname emp.name%TYPE;
salary emp.salary%TYPE;

begin
--打开游标
open c_emp;
-- 遍历游标中的值
loop
--通过fetch语句获取游标中的值并赋值给变量
fetch c_emp into psnname,salary;
-- 通过%NOTFOUND判断是否有值,有值打印,没有则退出循环
exit when c_emp%NOTFOUND;
dbms_output.put_line( '姓名:' || psnname || ',薪水:' || salary);
end loop;

-- 关闭游标
close c_emp;
end;

5.Oracle存储过程(一)

存储过程中没有declare,declare用于在语句块中,存储过程可以理解为java的方法,可以多次调用的

1
2
3
4
5
6
7
8
9
10
11
12
语法:
create or replace procedure 过程名称[(参数列表)] is(as,两者互用,功能一样)
begin
...
end 过程名称;
创建的过程:
1.点击PL/SQL的文件-->新建--> 程序窗口--> Procedure--> ...-->
编写完进行编译
2.调用Procedure文件:新的函数窗口下
begin
直接调用已编译好的Procedure文件
end
  • 变量声明,赋值的存储过程

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    create or replace procedure mydemo
    as
    name varchar(10); -- 声明变量,注意varchar需要指定长度
    age int;
    begin
    name := 'Bob';
    age:= 18;
    --通过||符号达到连接字符串的功能
    dbms_output.put_line('name='||name||', age='||age);
    end;
  • 带有参数的存储过程

1
2
3
4
5
6
7
8
9
10
create or replace procedure myDemo03(name in varchar,age in int)
as
begin
dbms_output.put_line('name='||name||', age='||age);
end;

-- 调用存储过程
begin
myDemo03('xiaoming',18);
end;

注意参数 in
在调用存储过程时,如果存储过程没有参数,调用时括号()可以不带。
存储过程带参数需要注意参数的传递参数时的一致性,按顺序依次传递。

  • 实参形参问题
1
2
3
4
5
6
7
8
9
10
11
12
13
14
create or replace procedure myDemo04(name in varchar,age in int)
as
begin
dbms_output.put_line('name='||name||', age='||age);
end;

declare
name varchar(10);
age int;
begin
name:='xiaoming';
age:=18;
myDemo04(name=>name,age=>18);--此时不能myDemo04(name=>name,18),不能完成调用。
end;

注;在调用存储过程时,=>前面的变量为存储过程的形参且必须于存储过程中定义的一致,而=>后的参数为实际参数。当然也不可以不定义变量保存实参

  • in,out问题
1
2
3
4
5
6
7
8
9
10
11
12
13
14
create or replace procedure myDemo05(name out varchar,age in int)
as
begin
dbms_output.put_line('age='||age);
select 'xiaoming' into name from dual;
end;

declare
name varchar(10);
age int;
begin
myDemo05(name=>name,age=>10);
dbms_output.put_line('name='||name);
end;

PS:in代表输入,out用于输出,参数默认类型是in类型

  • 带输出参数的存储结果

带输出参数的存储过程一般是通过第三方程序来调用,这里举例Java调用存储过程:
https://www.jianshu.com/p/8ad29c4c8114

  • 异常问题,执行结果如下
1
2
3
4
5
6
7
8
9
10
11
12
13
create or replace procedure mydemo0006
as
age int;
begin
age:=10/0;
dbms_output.put_line(age);
exception when others then
dbms_output.put_line('error');
end;

begin
mydemo0006();
end;

6.Oracle存储过程(二)

两种循环+基本增删改查操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
create or replace procedure mydemo07(ids in int, username in varchar,userpass in varchar, userage in int)
as
begin
-- insert into students(id,username,userpass,userage)--增
-- values(ids,username,userpass,userage);
-- delete from students where id=ids;--删
--update students set userage=100 where id=ids;--改
commit;
end;

begin
mydemo07(10,'a','b','17');
end;
---------------------------
create or replace procedure mydemo08(ids in int, age out int)
as
begin
select userage into age from students where id=ids; --查
commit;
end;

declare
ids int;
age int;
begin
ids:=1;
myDemo08(ids=>ids,age=>age);
dbms_output.put_line('age='||age);
end;

for循环:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
create or replace procedure mydemo09
as
begin

for stu in (select * from students)
loop
if (stu.id<5) then
dbms_output.put_line(stu.id);
end if;
end loop;
commit;
end;

begin
mydemo09();
end;

7.存储过程补充点

  1. 存储过程中的Execute immediate

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    在存储过程的开头,begin之前,经常会看到这么一段语句

    procedure name

    is

    PRAGMA AUTONOMOUS_TRANSACTION;——————自治事物

    Begin
    -- 清空原表数据
           Execute immediate ’truncate table xxx’;

    End

    为什么要用Execute immediate ’truncate table xxx’;而不是直接在begin里写truncate 呢?

    原因:因为存储过程中是没有truncate这条语句的,直接在存储过程中写truncate是会报错的。
    存储过程中只能写delete之类的语句。Execute immediate后台执行该语句,令存储过程强行转了SQL。
    SQL命令下是允许执行truncate的。所以,如果没有存储过程的权限,而有SQL的权限,可以使用execute immediate来执行。
    详细使用案例:

    CREATE OR REPLACE PROCEDURE PRO_TEST() IS
    V_SQL VARCHAR2(2000) := '';
    V_NUM NUMBER;
    BEGIN
    --EXECUTE IMMEDIATE用法1:立刻执行
    V_SQL := 'create or replace view mytest as select employee_id, first_name from employees';
    EXECUTE IMMEDIATE V_SQL;
    --EXECUTE IMMEDIATE用法2:立刻执行,并赋值给某变量
    V_SQL := 'select count(1) from employees';
    EXECUTE IMMEDIATE V_SQL;
    INTO V_NUM;
    --EXECUTE IMMEDIATE用法3:带参数的sql
    V_SQL := 'select * from employees e where e.employee_id = :1 and first_name = :2';
    EXECUTE IMMEDIATE V_SQL;
    USING 200, 'Jennifer';
    END PRO_TEST;
  2. Oracle的转义字符

  • CHR(10)和 CHR(13)——在oracle都为换行
  • chr(32)——表示空格
  • ' ' 一对单引号表示的是字符串,两对单引号表示转义字符串的内容,比如
1
2
3
4
5
6
7
-- 在sql里
'name' => 字符串
''name'' => 'name'
举例说明:
' syear_name || substr(replace(speriod_name,''月'','''') + 100,2,2) as smonth,'

syear_name || substr(replace(speriod_name,'月','') + 100,2,2) as smonth,

8.实际案例

  1. 查询某个员工的(比如9527)的姓名与薪水,传入参数为id
1
2
3
4
5
6
7
8
9
10
11
create or replace procedure P_QUERYNAMEANDSAL(psncode in emp.empno%TYPE) is 
-- 声明变量并接收查询结果
username emp.ename%TYPE ;
sal emp.salary%TYPE ;
begin
-- 根据用户传递的员工1号查询姓名与薪水
select ename,sal into username,sal from emp
where empno = psncode;
-- 打印结果
dbms_output.put_line(username || ':' || sal);
end;

存储过程类似于Java中的方法,那我们该如何调用:
在测试窗口调用存储过程

1
2
3
4
5
6
7
8
9
10
11
declare
--声明变量id
id emp.id%TYPE
begin
-- 变量赋值
id := 7839;
-- 存储过程调用方式一
p_querynameandsal(id);
-- 存储结果调用方式二:直接赋值调用
p_querynameandsal(7839);
end;

参考文章


 评论

联系我 | Contact with me

Copyright © 2019-2020 谁知你知我,我知你知深。此恨经年深,比情度日久

博客内容遵循 署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 协议