服务器之家：专注于VPS、云服务器配置技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Oracle - Oracle中PL/SQL的用法总结

Oracle中PL/SQL的用法总结

2022-09-16 17:36springsnow Oracle

本文详细讲解了Oracle中PL/SQL的用法，文中通过示例代码介绍的非常详细。对大家的学习或工作具有一定的参考借鉴价值，需要的朋友可以参考下

一、PLSQL基本结构

1、基本数据类型变量

1. 基本数据类型

  • Number 数字型
  • Int 整数型
  • Pls_integer 整数型，产生溢出时出现错误
  • Binary_integer 整数型，表示带符号的整数
  • Char 定长字符型，最大255个字符
  • Varchar2 变长字符型，最大2000个字符
  • Long 变长字符型，最长2GB
  • Date 日期型
  • Boolean 布尔型(TRUE、FALSE、NULL三者取一)

在PL/SQL中使用的数据类型和Oracle数据库中使用的数据类型，有的含义是完全一致的，有的是有不同的含义的。

2. 基本数据类型变量的定义方法

变量名 类型标识符[not null]:=值;

?
1
2
3
4
5
6
7
8
9
declare
 
     age number(3):=26; --长度为3，初始值为26
 
begin
 
     commit;
 
end;

其中，定义常量的语法格式：

常量名 constant 类型标识符 [not null]:=值;

?
1
2
3
4
5
6
7
8
9
declare
 
     pi constant number(9):=3.1415926;--为pi的数字型常量，长度为9，初始值为3.1415926
 
begin
 
     commit;
 
end;

2、表达式

变量、常量经常需要组成各种表达式来进行运算，下面介绍在PL/SQL中常见表达式的运算规则。

1. 数值表达式

 PL/SQL程序中的数值表达式是由数值型常数、变量、函数和算术运算符组成的， 
可以使用的算术运算符包括+(加法)、-(减法)、*(乘法)、/(除法)和**(乘方)等。 
命令窗口中执行下列PL/SQL程序，该程序定义了名为result的整数型变量，计算的是10+3*4-20+5**2的值，理论结果应该是27。 

?
1
2
3
4
5
6
7
set serveroutput on
 Declare
    result integer;
 begin
    result:=10+3*4-20+5**2;
    dbms_output.put_line('运算结果是：'||to_char(result));
 end;

dbms_output.put_line函数输出只能是字符串，因此利用to_char函数将数值型结果转换为字符型。

2. 字符表达式

字符表达式由字符型常数、变量、函数和字符运算符组成，唯一可以使用的字符运算符就是连接运算符“||”。

3. 关系表达式

关系表达式由字符表达式或数值表达式与关系运算符组成，可以使用的关系运算符包括以下9种。 
< 小于 
> 大于 
= 等于(不是赋值运算符:=) 
like 类似于 
in 在……之中 
<= 小于等于 
>= 大于等于 
!= 不等于 或<> 
between 在……之间 
关系型表达式运算符两边的表达式的数据类型必须一致。

4. 逻辑表达式

逻辑表达式由逻辑常数、变量、函数和逻辑运算符组成，常见的逻辑运算符包括以下3种。 
NOT：逻辑非 
OR：逻辑或 
AND：逻辑与 
运算的优先次序为NOT、AND和OR。

3、PLSQL函数

PL/SQL程序中提供了很多函数供扩展功能，除了标准SQL语言的函数可以使用外，最常见的数据类型转换函数有以下3个。 
To_char：将其他类型数据转换为字符型。 
To_date：将其他类型数据转换为日期型。 
To_number：将其他类型数据转换为数值型。 
继续追加中..

4、系统输出打印

利用pl/sql在数据库服务器端打印一句话： 
set serveroutput on--设置数据库输出，默认为关闭，每次重新打开窗口需要重新设置。

?
1
2
3
BEGIN
     DBMS_OUTPUT.PUT_LINE('Hello PL/SQL');
END;

pl/sql程序中对大小写不敏感(打印声明的变量)

?
1
2
3
4
5
6
7
8
set serveroutput on
DECLARE
   v_char varchar2(20):='a';
   v_char1 varchar2(20):='b';
BEGIN
   DBMS_OUTPUT.PUT_LINE(v_char);
   DBMS_OUTPUT.PUT_LINE(v_char1);
 END;

pl语句块是pl/sql里最小的编程块，其中可以再嵌套begin end

?
1
2
3
4
5
begin
  dbms_output.put_line('Hello World');
  dbms_output.put_line('2*3='||(2*3));
  dbms_output.put_line('what''s');
 end;

5、PL/SQL中的变量声明

所有变量必须在declare中声明，程序中不允许声明。 
没有初始化的变量默认值为null，屏幕上null是看不见的，命名习惯：PL/SQL中变量一般以v_开头 
(等同于存储过程中as和begin区域的变量定义习惯)。 
注意number也能存小数，最长38位，所以以后建议整数都用binary_integer存。 
long是字符类型，boolean类型不能打印。 
标准变量类型：数字，字符，时间，布尔。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
declare
     v_number1 number;
     v_number2 number(3,2) ;
     v_number3 binary_integer :=1;
     v_name varchar2(20) :='kettas';
     v_date date :=sysdate;
     v_long long :='ni hao';
     v_b boolean := true;
begin
     if (v_number1 is null) then
     dbms_output.put_line( 'hello');
     end if;
     dbms_output.put_line(v_number1);
     dbms_output.put_line(v_number2);
     dbms_output.put_line(v_number3);
     dbms_output.put_line(v_name);
     dbms_output.put_line(v_date);
     dbms_output.put_line(v_long);
   --dbms_output.put_line(v_b); --执行该句ORACLE提示“调用'PUT_LINE' 时参数个数或类型错误”
end;

备注： 
关于声明number(4,3)中括号中的两个数字的意义，前面的数字叫精度，后面的叫刻度。 
刻度： 
当刻度为正数的时候，表示四舍五入到小数点后面的位数 
当刻度为负数的时候，表示四舍五入到小数点前面的位数 
精度： 
从数字的最前面不为零开始到刻度精确到的位置 
v_Number number(4,3):=123.12312 
1、按刻度进行四舍五入得到123.123 
2、确定刻度精确到的位置123123处，精度为6位(.符号不算) 
2、根据精度进行判断6位(>4)精度上限值 --报错不能存储 
number(3,-3):=44445 
1、根据刻度－3进行四舍五入得到44000 
2、小数点向前移动3位44.此位置为刻度精确到的位置 
3、根据精度进行判断2位(<3)精度上限值 --不报错可存储结果为44000

?
1
2
3
4
5
6
7
8
9
10
11
DECLARE
  v_Number number(4,3):=123.12312;--实际精度6位大于上限精度值4位，提示“ORA-06502:PL/SQL: 数字或值错误 :  数值精度太高”
BEGIN
  DBMS_OUTPUT.PUT_LINE(v_Number);
END;
 
DECLARE
  v_Number number(7,3):=4555; --实际精度7位等于上限精度值，可以存储
BEGIN
  DBMS_OUTPUT.PUT_LINE(v_Number);
 END;

二、变量赋值方式

oracle中变量赋值方式是值拷贝而非引用

?
1
2
3
4
5
6
7
8
9
10
declare
    v_number1 number:=100;
    v_number2 number;
begin
    v_number2:=v_number1;
    v_number1:=200;
    dbms_output.put_line(v_number1); --200
    dbms_output.put_line(v_number2); --100
 
end;

三、PLSQL复合类型

1、记录类型record

record类型最常用，声明的时候可以加not null，但必须给初始值，如果record类型一致可以相互赋值，如果类型不同，里面的字段恰好相同，不能互相赋值。引用记录型变量的方法是“记录变量名.基本类型变量名”。

?
1
2
3
4
5
6
7
8
9
10
11
12
declare
    type t_first is record(
         idnumber(3),
         namevarchar2(20)
    );
    v_first t_first;
begin
      v_first.id:=1;
      v_first.name:='cheng';
      dbms_output.put_line(v_first.id);
      dbms_output.put_line(v_first.name);
 end;

record类型变量间赋值

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
declare
    type t_first is record(
      id number,
      name varchar2(20)  
    );
    v_first t_first;
    v_second t_first;
begin
    v_first.id:=1;
    v_first.name:='susu';
    
    v_second:=v_first;--相互赋值
    
    v_first.id:=2;
    v_first.name:='kettas';
    dbms_output.put_line(v_first.id);
    dbms_output.put_line(v_first.name);
    dbms_output.put_line(v_second.id);
    dbms_output.put_line(v_second.name);
 end;

2、表类型变量table

语法如下： 
type 表类型 is table of 类型 index by binary_integer; 
表变量名 表类型; 
类型可以是前面的类型定义，index by binary_integer子句代表以符号整数为索引，这样访问表类型变量中的数据方法就是“表变量名(索引符号整数)”。table类型，相当于Java中的Map容器，就是一个可变长的数组，key(符号整数索引)必须是整数，可以是负数，value(类型)可以是标量，也可以是record类型。可以不按顺序赋值，但必须先赋值后使用。 

1. 定义一维表类型变量

?
1
2
3
4
5
6
7
8
9
declare
     type t_tb is table of varchar2(20)index by binary_integer;
     v_tb t_tb;
 begin
   v_tb(100):='hello';
   v_tb(98):='world';
   dbms_output.put_line(v_tb(100));
   dbms_output.put_line(v_tb(98));
 end;

类型为record的表类型变量

?
1
2
3
4
5
6
7
8
9
10
11
declare
     type t_rd is record(id number,namevarchar2(20));
     type t_tb is table of t_rd index bybinary_integer;
     v_tb2 t_tb;
 begin
     v_tb2(100).id:=1;
     v_tb2(100).name:='hello';
     --dbms_output.put_line(v_tb2(100).id);
    --dbms_output.put_line(v_tb2(100).name);
    dbms_output.put_line(v_tb2(100).id||'    '||v_tb2(100).name);
 end;

2. 定义多维表类型变量

该程序定义了名为tabletype1的多维表类型，相当于多维数组，table1是多维表类型变量，将数据表tempuser.testtable中recordnumber为60的记录提取出来 
存放在table1中并显示。

?
1
2
3
4
5
6
7
declare
   type tabletype1 is table of testtable%rowtypeindex by binary_integer;
   table1 tabletype1;
 begin
    select * into table1(60) fromtempuser.testtable where recordnumber=60;
   dbms_output.put_line(table1(60).recordnumber||table1(60).currentdate);
 end;

备注：在定义好的表类型变量里，可以使用count、delete、first、last、next、exists和prior等属性进行操作，使用方法为“表变量名.属性”，返回的是数字。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
set serveroutput on
declare
     type tabletype1 is table ofvarchar2(9) index by binary_integer;
     table1 tabletype1;
begin
     table1(1):='成都市';
     table1(2):='北京市';
     table1(3):='青岛市';
     dbms_output.put_line('总记录数：'||to_char(table1.count));
     dbms_output.put_line('第一条记录：'||table1.first);
     dbms_output.put_line('最后条记录：'||table1.last);
     dbms_output.put_line('第二条的前一条记录：'||table1.prior(2));
     dbms_output.put_line('第二条的后一条记录：'||table1.next(2));
 end;

四、 %type和%rowtype

使用%type定义变量，为了让PL/SQL中变量的类型和数据表中的字段的数据类型一致，Oracle 9i提供了%type定义方法。这样当数据表的字段类型修改后，PL/SQL程序中相应变量的类型也自动修改。

?
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
38
39
40
41
42
43
44
create table student(
    id number,
    name varchar2(20),
    age number(3,0)
 );
 insert into student(id,name,age) values(1,'susu',23);
--查找一个字段的变量
declare
    v_name varchar2(20);
    v_name2 student.name%type;
 begin
    select name into v_name2 from studentwhere rownum=1;
    dbms_output.put_line(v_name2);
 end;
--查找多个字段的变量
declare
     v_id student.id%type;
     v_name student.name%type;
     v_age student.age%type;
 begin
   select id,name,age into v_id,v_name,v_age fromstudent where rownum=1;
   dbms_output.put_line(v_id||' '||v_name||'  '||v_age);
 end;
--查找一个类型的变量，推荐用*
declare
    v_student student%rowtype;
 begin
    select * into v_student from student whererownum=1;
    dbms_output.put_line(v_student.id||' '||v_student.name||'  '||v_student.age);
 end;
--也可以按字段查找，但是字段顺序必须一样，不推荐这样做
declare
    v_student student%rowtype;
 begin
  select id,name,age into v_student from student whererownum=1;
  dbms_output.put_line(v_student.id||' '||v_student.name||'  '||v_student.age);
 end;
 declare
    v_student student%rowtype;
 begin
  select id,name,age intov_student.id,v_student.name,v_student.age from student where id=1;
  --select * intov_student.id,v_student.name,v_student.age from student where id=1;
  dbms_output.put_line();
 end;

备注：insert，update，delete，select都可以，createtable，droptable不行。DPL，DML，和流程控制语句可以在pl/sql里用，但DDL语句不行。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
declare
    v_name student.name%type:='wang';
 begin
    insert into student(id,name,age)values(2,v_name,26);
 end;
 
begin
    insert into student(id,name,age)values(5,'hehe',25);
 end;
 declare
    v_name student.name%type:='hexian';
 begin
    update student set name=v_name where id=1;
 end;
 begin
    update student set name='qinaide' whereid=2;
 end;

五、PLSQL变量的可见空间

变量的作用域和可见性，变量的作用域为变量申明开始到当前语句块结束。

当外部过程和内嵌过程定义了相同名字的变量的时候，在内嵌过程中如果直接写这个变量名是没有办法访问外部过程的变量的，可以通过给外部过程定义一个名字<<outername>>,通过outername变量名来访问外部过程的变量(待测试..)。

?
1
2
3
4
5
6
7
8
9
10
11
12
declare
         v_i1binary_integer:=1;
begin
      declare
         v_i2binary_integer:=2;
      begin
        dbms_output.put_line(v_i1);
        dbms_output.put_line(v_i2);
      end;
   dbms_output.put_line(v_i1);
--dbms_output.put_line(v_i2);  解开后执行Oracle会提示“必须说明标识符'V_I2'”
end;

六、PLSQL流程控制

if判断

?
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
declare
    v_b boolean:=true;
begin if v_b then
      dbms_output.put_line('ok');
   end if;
 end;
if else判断
declare
     v_b boolean:=true;
 begin
  if v_b then
     dbms_output.put_line('ok');
  else
     dbms_output.put_line('false');
  end if;
end;
 if elsif else判断
declare
     v_name varchar2(20):='cheng';
 begin
  if v_name='0701' then
     dbms_output.put_line('0701');
  elsif v_name='cheng' then
     dbms_output.put_line('cheng');
  else
     dbms_output.put_line('false');
  end if;
 end;

loop循环

注意推出exit是推出循环，而不是推出整个代码块

?
1
2
3
4
5
6
7
8
9
10
11
12
declare
    v_i binary_integer:=0;
 begin
  loop
      if v_i>10 then
         exit;
      end if;
      v_i:=v_i+1;
      dbms_output.put_line('hehe');
  end loop;
      dbms_output.put_line('over');
 end;

loop简化写法

?
1
2
3
4
5
6
7
8
9
10
declare
    v_i binary_integer :=0;
 begin
  loop
      exit when v_i>10;
      v_i :=v_i+1;
      dbms_output.put_line('hehe');
  end loop;
      dbms_output.put_line('over');
 end;

while循环

?
1
2
3
4
5
6
7
8
9
declare
    v_i binary_integer:=0;
 begin
    while v_i<10 loop
         dbms_output.put_line('hello'||v_i );
         v_i:=v_i+1;
    end loop;
    dbms_output.put_line('over');
 end;

for循环

注意不需要声明变量

?
1
2
3
4
5
6
begin
  for v_i in 0..10 loop
     dbms_output.put_line('hello'||v_i);
  end loop;
      dbms_output.put_line('over');
end;

七、PLSQL异常处理

1、声明异常

异常名 EXCEPTION；

2、抛出异常

RAISE 异常名

3、处理异常

抛出异常后的逻辑代码不会被继续执行 
异常的定义使用

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
begin
       dbms_output.put_line(1/0);
 exception
         when othersthen
            dbms_output.put_line('error');
 end;
 declare
        e_myException exception;
 begin
        dbms_output.put_line('hello');
         raisee_myException; --raise抛出异常,用此关键字，抛出后转到自定义的e_myException ，执行其里面的putline函数后，再跳到end处，结束PL/SQL块，raise接下面的2句不会继续执行。
        dbms_output.put_line('world');
        dbms_output.put_line(1/0);
 exception
         whene_myException then
            dbms_output.put_line(sqlcode); --当前会话执行状态，错误编码
            dbms_output.put_line(sqlerrm); --当前错误信息
            dbms_output.put_line('my error');
         when othersthen
            dbms_output.put_line('error');
 end;

八、PLSQL游标和goto语句

备注：下面提到的游标为静态cursor，包括显示和隐式。 
游标，从declare、open、fetch、close是一个完整的生命旅程。当然了一个这样的游标是可以被多次open进行使用的，显式cursor是静态cursor，她的作用域是全局的，但也必须明白，静态cursor也只有pl/sql代码才可以使用它。静态游标变量是在定义时就必须指定SQL语句。 
cursor 游标(结果集)用于提取多行数据，定义后不会有数据，使用后才有。一旦游标被打开，就无法再次打开(可以先关闭，再打开)。

?
1
2
3
4
5
6
declare
       cursor c_studentis  select * from book;
 begin
       open c_student;
       close c_student;
 end;

第二种游标的定义方式，用变量控制结果集的数量。

?
1
2
3
4
5
6
7
8
declare
       v_id binary_integer;
       cursor c_student isselect * from book where id>v_id;
 begin
       v_id:=10;
       open c_student;
       close c_student;
 end;

第三种游标的定义方式，带参数的游标，用的最多。

?
1
2
3
4
5
6
declare
       cursor c_student(v_idbinary_integer) is select * from book where id>v_id;
 begin
       open c_student(10);
       close c_student;
 end;

游标的使用，一定别忘了关游标。

?
1
2
3
4
5
6
7
8
9
declare
       v_student book%rowtype;
       cursor c_student(v_idbinary_integer) is select * from book where id>v_id;
 begin
       open c_student(10);
       fetch c_student intov_student;
       close c_student;
       dbms_output.put_line(v_student.name);
 end;

如何遍历游标fetch

游标的属性 %found，%notfound,%isopen,%rowcount。

  • %found:若前面的fetch语句返回一行数据，则%found返回true，如果对未打开的游标使用则报ORA-1001异常。
  • %notfound，与%found行为相反。
  • %isopen，判断游标是否打开。
  • %rowcount:当前游标的指针位移量，到目前位置游标所检索的数据行的个数，若未打开就引用，返回ORA-1001。

注： 
no_data_found和%notfound的用法是有区别的，小结如下 
1)SELECT. . . INTO 语句触发 no_data_found； 
2)当一个显式光标(静态和动态)的 where 子句未找到时触发 %notfound； 
3)当UPDATE或DELETE语句的where 子句未找到时触发 sql%notfound； 
4)在光标的提取(Fetch)循环中要用 %notfound 或%found 来确定循环的退出条件，不要用no_data_found。 
下面是几个实例：

?
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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
create table BOOK
 (
 ID       VARCHAR2(10) not null,
 BOOKNAME VARCHAR2(10) not null,
 PRICE    VARCHAR2(10) not null,
 CID      VARCHAR2(10) not null
 );
--insert
 create or replace procedure say_hello(
 i_name in varchar2,
o_result_msg out varchar2
 )
  as
  v_price varchar2(100);
  e_myException exception;
 begin
     insert into book(id,bookname,price)values (1,2,3);
     o_result_msg := 'success';
  exception
     when others then
         rollback;
         o_result_msg := substr(sqlerrm, 1, 200);
  end;
--update or delete
 create or replace procedure say_hello(
 i_name in varchar2,
 o_result_msg out varchar2
 )
  as
  v_price varchar2(100);
  e_myException exception;
 begin
     update book set price = '55' wherebookname = i_name;
     delete from book where bookname =i_name;
     if sql%notfound then
        raisee_myException;
     end if;
     /*
 if sql%rowcount = 0 then--写法2
        raisee_myException;
     end if;
 */
     o_result_msg := 'success';
  exception
     when e_myException then
         rollback;
         o_result_msg := 'update or delete dail';
  end;
--select
 create or replace procedure say_hello(
 i_name in varchar2,
 o_result_msg out varchar2
 )
  as
  v_price varchar2(100);
  e_myException exception;
 begin
     select price into v_price from bookwhere bookname = i_name;
     o_result_msg := 'success';
  exception
     when no_data_found then
         rollback;
         o_result_msg := 'select into dail';
  end;

loop方式遍历游标

?
1
2
3
4
5
6
7
8
9
10
11
12
declare
       v_bookname varchar2(100);
       cursor c_book(i_idnumber) is select bookname from book where id = i_id;
 begin
     Open  c_book(i_id);
     Loop
         Fetch c_bookinto v_bookname;
         exit whenc_student%notfound;
          update book set price = '33' where bookname = v_bookname;
     End Loop;
     Close c_book;
 end;

或

?
1
2
3
4
5
6
7
8
9
10
11
12
13
declare
       v_bookname varchar2(100);
       cursor c_book(i_idnumber) is select bookname from book where id = i_id;
 begin
     Open  c_book(i_id);
       Fetch c_book intov_bookname;
       While c_book%Found
       Loop
          update book set price = '33' where bookname = v_bookname;
       Fetch  c_book intov_bookname;
       End Loop;
     Close c_book;
 end;

while循环遍历游标，注意，第一次游标刚打开就fetch，%found为null，进不去循环 
解决方法：while nvl(c_student%found,true) loop

?
1
2
3
4
5
6
7
8
9
10
11
declare
      v_bookname  varchar2(100);
      cursor c_book(i_id number) isselect bookname from book where id = i_id;
 begin
      Open  c_book(i_id);
      while nvl(c_book%found,true)--或这种写法：while c_book%found is null or c_book%foundloop   
          Fetchc_book into v_bookname;
          updatebook set price = '33' where bookname = v_bookname;
      End Loop;
      Close c_book;
 end;

for循环遍历，最简单，用的最多，不需要 声明v_student,Open和Close游标和fetch操作(不用打开游标和关闭游标，实现遍历游标最高效方式)

?
1
2
3
4
5
6
7
8
declare
      cursor c_book(i_id number) isselect bookname from book where id = i_id;
begin
      for cur in c_book(i_id) --直接将入参i_id传入cursor即可
      loop
          updatebook set price = '53' where bookname = cur.bookname;
      end loop;
 end;

goto例子，一般不推荐使用goto，会使程序结构变乱

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
declare
      i number:=0;
 begin
      if i=0 then
          gotohello;
      end if;
      <<hello>>
      begin
         dbms_output.put_line('hello');
          gotoover;
      end;
      <<world>>
      begin
         dbms_output.put_line('world');
          gotoover;
      end;
      <<over>>
         dbms_output.put_line('over');
  end;

九、Oracle存储过程

在谈存储过程书写中的一些规则时，先看一下执行它的规则，在命令窗口执行存储过程sp_get_product_prompt

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
set serveroutput on
  var ret1 varchar2(200);
  var ret2 varchar2(200);
  exec sp_get_product_prompt(83,:ret1,:ret2); --或execute
  print ret1;
  print ret2;
  或
  set serveroutput on
  declare
     ret1 varchar2(200);
     ret2 varchar2(200);
  begin
     sp_get_product_prompt(83,ret1,ret2);
     dbms_output.put_line(ret1);
     dbms_output.put_line(ret2);
  end;

存储过程入参，不论类型，缺省情况下值都为null,入参和出参不能有长度,其中关键字as可以替换成is,存储过程中变量声明在as和begin之间，同时，存储过程中可以再调用其它的存储过程，如果要保证存储过程之间的事务处理不受影响，可以定义为自治事务。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
create or replace procedure say_hello(
    v_name in varchar2,
    v_flag number,
    o_ret out number
  )
  as
  begin
    if v_name is null and v_flag is null then --v_name和v_flag都等于null
       o_ret := 10;
    else
        o_ret := 100;
    end if;
  end;

对于入参为null情况下给予缺省值

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
create or replace procedure say_hello(
    i_name in varchar2,
    i_flag number,
    o_ret out number
  )
  as
    v_name  varchar2(100);
  begin
    if i_name is null then
       v_name := '0';
    else
       v_name := i_name;
    end if;
    insert into phone(..,wname..,)values(..,v_name,..); 
  
  end;

或直接在insert语句中调用nvl函数赋缺省值

?
1
insert intophone(..,wname..,) values(..,nvl(v_name,' '),..); ----如果将' '写成'',则insert进来的v_name值还是为''等价于null值

带一个参数的存储过程 
输入参数in，输入参数不能进行:=赋值，但可以将它赋给as后面定义的变量； 
输入参数in，可以作为变量进行条件判断； 
默认不写就是in； 
存储过程没有重载，这个有参的say_hello会替代已经存在的无参say_hello。

?
1
2
3
4
5
6
create or replace procedure say_hello(v_name in varchar2)
  as
  begin
     --v_name:='a'; --存储过程入参v_name不能做为赋值目标
    dbms_output.put_line('hello '||v_name);
  end;

存储过程输入参数作为变量进行条件判断

?
1
2
3
4
5
6
7
8
9
10
11
12
13
create or replace procedure say_hello(
     i_opFlag in number
  )
  as
     v_name varchar2(100);
  begin
     if i_opFlag = 1 then
v_name :='0';
     else
v_name :='haha';
     end if;
     dbms_output.put_line('hello'||v_name);
  end;

利用存储过程中定义的变量对入参的空值处理：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
create or replace procedure say_hello(
     i_name in varchar2
  )
  as
     v_name varchar2(100);
  begin
     if i_name is null then
v_name :='0';
     else
v_name :=i_name;--将入赋值给定义变量
     end if;
     dbms_output.put_line('hello'||v_name);
  end;

多个参数的存储过程

?
1
2
3
4
5
6
7
create or replace procedure say_hello(
     v_first_name in varchar2,
     v_last_name in varchar2)
  as
  begin
     dbms_output.put_line('hello'||v_first_name||'.'||v_last_name);
  end;

out输出参数

用于利用存储过程给一个或多个变量赋值，类似于返回值

?
1
2
3
4
5
6
7
create or replaceprocedure say_hello(
     v_name in varchar2,
     v_content out varchar2
  )
  begin
     v_content:='hello'||v_name;
  end;

调用：

?
1
2
3
4
5
6
7
declare
     v_con varchar2(200);
     v_in varchar2(20):='wang';
  begin
     say_hello(v_in,v_con);
     dbms_output.put_line(v_con);
  end;

in out参数，既赋值又取值

?
1
2
3
4
5
create or replace procedure say_hello(v_name in outvarchar2)
  as
  begin
     v_name:='hi '||v_name;
  end;

调用：

?
1
2
3
4
5
6
declare
     v_inout varchar2(20):='wangsu';
  begin
     say_hello(v_inout);
     dbms_output.put_line(v_inout);
  end;

对存储过程入参赋缺省值

?
1
2
3
4
5
6
7
8
create or replace procedure say_hello(
     v_name varchar2 default 'susu',
     v_content varchar2 default 'hello'
  )
  as
  begin
     dbms_output.put_line(v_name||''||v_content);
  end;

调用：(用指明形参名的方式调用更好)

?
1
2
3
begin
     say_hello();
  end;

或

?
1
2
3
begin
     say_hello('cheng');
  end;

或

?
1
2
3
begin
  say_hello(v_name=>'cheng');
 end;

十、PLSQL中的function

FUNCTION和PROCEDURE的区别

1、函数有返回值，过程没有 
2、函数调用在一个表达式中，过程则是作为pl/sql程序的一个语句 
过程和函数都以编译后的形式存放在数据库中，函数可以没有参数也可以有多个参数并有一个返回值。

过程有零个或多个参数，没有返回值。

函数和过程都可以通过参数列表接收或返回零个或多个值，函数和过程的主要区别不在于返回值，而在于他们的调用方式，过程是作为一个独立执行语句调用的，函数以合法的表达式的方式调用。

?
1
2
3
4
5
6
create or replace function func(v_name in varchar2)
return varchar2
is
begin
    return(v_name||' hello');
 end;

调用：

?
1
2
3
4
5
6
declare
    v_name varchar2(20);
begin
    v_name:=func('cheng');
    dbms_output.put_line(v_name);
 end;

带out参数的函数

?
1
2
3
4
5
6
7
8
9
10
create or replace function func(
    v_name in varchar2,
    v_content out varchar2
)
return varchar2
is
begin
    v_content:=v_name||' hello';
    return v_content;
 end;

调用：

?
1
2
3
4
5
6
7
8
declare
    v_name varchar2(20);
    v_name1 varchar2(20);
begin
    v_name1:=func('susu',v_name);--返回v_name值
    dbms_output.put_line(v_name1);--打印func结果
    dbms_output.put_line(v_name);--打印v_name结果
end;

带in out 参数的函数

?
1
2
3
4
5
6
7
8
create or replace function func(
    v_name in out varchar2)
return varchar2
is
begin
    v_name:=v_name||' hello';
    return 'cheng';
 end;

调用：

?
1
2
3
4
5
6
7
8
declare
    v_inout varchar2(20):='world';
    v_ret varchar2(20);
begin
    v_ret:=func(v_inout);--返回调用v_inout值(作为出参)
    dbms_output.put_line(v_ret);--打印func结果   
    dbms_output.put_line(v_inout);--返回v_name结果
end;

到此这篇关于Oracle中PL/SQL用法的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持服务器之家。

原文链接：https://www.cnblogs.com/springsnow/p/11497340.html

  • Oracle
  • PL/SQL

    • 延伸 · 阅读

    精彩推荐
    最近更新
    编辑推荐
    阅读排行
    热门标签
    1033 　 12154 　 1455 　 10231 　 12514 　 54013 　 1000 　 外连接 　 监控账户 　 子程序 　 参数模式 　 字符替换 　 更改数据库名 　 RANK 　 LAG 　 sys 　 数值函数 　 ORCLE 　 赋权 　 SP2-0027 　 oracle存储过程 　 oracle函数 　 ORACLE11g 　 关联其他表 　 tsql 　 行列转 　 IMMEDIATE 　 创建用户 　 Oracle DBA 　 Oracle游标 　
    © 2019-2022 服务器之家(www.tuohang.net) 版权所有关于我们联系我们版权申明网站地图