过程和函数

oracle220

   
过程和函数
子程序定义:
      q 命名的  PL/SQL  块,编译并存储在数据库中。
q子程序的各个部分:
q声明部分
q可执行部分
q异常处理部分(可选)
q子程序的分类:
q过程 - 执行某些操作
q函数 - 执行操作并返回值

过程:

  过程参数的三种模式:
      IN
用于接受调用程序的值
默认的参数模式
    OUT
      用于向调用程序返回值
    IN OUT
     用于接受调用程序的值,并向调用程序返回更新的值

下面是个简单的过程
    create  or replace procedure test_procedure
                          (row_count in number ,row_sign varchar2)--这里面的参数不能写多大(eg:  varchar2( 20)),只能写类型
   is
   row_sign_re  varchar2( 20);
   begin
     row_sign_re := row_sign ;
     for i in 1.. row_count
      loop
       row_sign_re := row_sign_re || '*';
       dbms_output.put_line (row_sign_re );
       end loop;
       end;

    create  or replace procedure test_procedure (row_count in number ,row_sign varchar2)
   is
   row_sign_re  varchar2( 20);
   begin
     row_sign_re := row_sign ;
     for i in 1.. row_count
      loop
        row_sign  :=  row_sign_re  ||  '*' ; --这里会报错,报错的原因在于in类型参数不能被二次赋值,如这行所示
       dbms_output.put_line (row_sign_re );
       end loop;
       end;

create or replace procedure pro2 (param1 out number)
as
base_num constant number := 10 ;
begin
  for i in 1 .. base_num
    loop
      param1 := i+ 1;--这里不会报错,在于out类型参数能被二次赋值,如这行所示
      end loop;
      end;


SQL> execute pro2(s);
BEGIN pro2(s); END;
           *
ERROR at line 1:
ORA-06550: line 1, column 12:
PLS-00201: identifier 'S' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
SQL>
这个放在SQLPLUS(命令行)中执行会报错。
但是放在PL/SQL块中可不会。说明OUT类型的过程只能放在PL/SQL代码块中执行
SQL> declare
  2  a number(20);
  3  begin
  4    pro2(a);
  5    dbms_output.put_line(a => a);
  6    end;
  7  /
11
PL/SQL procedure successfully completed

SQL> 

create  or  replace  procedure pro3  (num1  in  out  number  ,num2  in  out  number )
as swi_temp  number (  20 );
begin
  swi_temp  := num1 ;
  num1  := num2 ;
  num2  := swi_temp ;
   end  ;
 
   declare
  num1  number (  20 );
  num2  number (  20 );
   begin
    num1  :=  2 ;
    num2  :=  3 ;
    pro3 (num1  => num1 ,num2  => num2 );
    dbms_output.put_line ( 'num1 = '  ||to_char  (num1  ));
      dbms_output.put_line  ( 'num2 = '  ||to_char  (num2  ));
       end ;

对于用户过程是需要被赋予权限的
SQL>  execute sys.test_procedure(4,'*');
begin sys.test_procedure(4,'*'); end;
ORA-06550: 第 1 行, 第 7 列:
PLS-00201: 必须声明标识符 'SYS.TEST_PROCEDURE'
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored

SQL> grant execute on sys.test_procedure to hr;
Grant succeeded

SQL> 
SQL> set serverout on;
SQL>  execute sys.test_procedure(4,'*');
**
***
****
*****
PL/SQL procedure successfully completed

函数:

q定义函数的限制:
q函数只能接受 IN 参数,而不能接受 IN OUT 或 OUT 参数
q形参不能是 PL/SQL 类型,只能是数据库类型
q函数的返回类型也必须是数据库类型
q访问函数的两种方式:
q使用 PL/SQL 块
q使用 SQL 语句
要求:输入对应的学号,把他们在表中的名次输出出来?
SQL> select * from student;
    SNO SNAME                                    SAGE                 SCORE
------- ---------- --------------------------------------- ---------------------
      1 AA                                            21                    80
      2 BB                                            22                    90
      3 CC                                            23                   100

SQL>
SQL>
SQL>
SQL> create or replace function fun1(param1 in number)
  2  return number ;--不能有;号存在
  3  is
  4  score_cp number(20);
  5  rank_top number(10);
  6  begin
  7    select score into score_cp from student s where s.sno = param1;
  8    select count(*) into rank_top  from student s where s.score > score_cp;
  9    rank_top : = rank_top + 1;
 10    return rank_top;
 11    end;
 12  /
Warning: Function created with compilation errors

SQL> show error ;--输出报错的信息
Errors for FUNCTION SYS.FUN1:
LINE/COL ERROR
-------- ----------------------------------------------------------
3/1      PLS-00103: 出现符号 "IS"
9/12     PLS-00103: 出现符号 ":"在需要下列之一时:   := . ( @ % ; 

SQL> ed
SQL> /
Warning: Function created with compilation errors

SQL> show error ;
Errors for FUNCTION SYS.FUN1:
LINE/COL ERROR
-------- ------------------------
3/1      PLS-00103: 出现符号 "IS"

SQL> ed--修改报错的代码
SQL> /--执行修改后的代码块
Warning: Function created with compilation errors

SQL> show error ;
Errors for FUNCTION SYS.FUN1:
LINE/COL ERROR
-------- ------------------------
3/1      PLS-00103: 出现符号 "AS"

SQL> ed
SQL> /
Warning: Function created with compilation errors

SQL> show error ;
Errors for FUNCTION SYS.FUN1:
LINE/COL ERROR
-------- ------------------------
3/1      PLS-00103: 出现符号 "AS"

SQL> ED
SQL> /
Function created

SQL> SELECT FUN1(2) FROM DUAL;--函数的执行可以放在sqlplus中执行
   FUN1(2)
----------
         2

SQL>
SQL>
SQL>
SQL>   declare--也可以放在pl/sql代码块中执行
  2    rank_top number(10);
  3    begin
  4      rank_top := fun1(3);
  5      dbms_output.put_line(rank_top);
  6      end;
  7  /
1
PL/SQL procedure successfully completed

SQL> 

过 程

                     在过程中这里的return相当于高级语言中return;终止执行的意思。
                    但是在函数中必须要有返回值才可以。



自主事务:

q自主事务处理
q主事务处理启动独立事务处理
q然后主事务处理被暂停
q自主事务处理子程序内的 SQL 操作
q然后终止自主事务处理
q恢复主事务处理
qPRAGMA AUTONOMOUS_TRANSACTION
用于标记子程序为自主事务处理

q自主事务处理的特征:
q与主事务处理的状态无关
q提交或回滚操作不影响主事务处理
q自主事务处理的结果对其他事务是可见的
q能够启动其他自主事务处理

SQL> create or replace procedure pro2
  2  as
  3  stu_name varchar2(20);
  4    PRAGMA AUTONOMOUS_TRANSACTION;--主事务的操作不影响自主事务的操作,所以在下面的输出中stu_name = AA;
  5  begin
  6  select s.sname into stu_name  from student s where s.sno = 1;
  7    dbms_output.put_line(stu_name);
  8  rollback; --自主事务的回滚不会影响主事务的操作
  9  end;
 10  /
Procedure created
SQL>
SQL> create or replace procedure pro3
  2  as
  3  stu_name varchar2(20);
  4  begin
  5    update student s set s.sname = '张三' where s.sno = 1;
  6     pro2();
  7    select s.sname into stu_name from student s where s.sno = 1;
  8    dbms_output.put_line(stu_name);--张三被输出是因为自主事务的回滚不会影响主事务的操作
  9    end;
 10  /
Procedure created

SQL> execute pro3;
AA
张三
PL/SQL procedure successfully completed

SQL>   create or replace procedure pro2
  2  as
  3  stu_name varchar2(20);
  4    PRAGMA AUTONOMOUS_TRANSACTION;
  5  begin
  6     update student s set s.sname = '张三' where s.sno = 1;
  7  select s.sname into stu_name  from student s where s.sno = 1;
  8    dbms_output.put_line(stu_name);
  9     commit;--假如设置了自主事务了,那么就必须要有显示的commit/rowback
 10  end;
 11  /
Procedure created

SQL>
SQL>
SQL>
SQL> create or replace procedure pro3
  2  as
  3  stu_name varchar2(20);
  4  begin
  5      pro2();
  6    select s.sname into stu_name from student s where s.sno = 1;
  7    dbms_output.put_line(stu_name);
  8    end;
  9 
 10  /
Procedure created

SQL> execute pro3;
张三
张三
PL/SQL procedure successfully completed

SQL>
SQL>
SQL>
SQL>   create or replace procedure pro2
  2  as
  3  stu_name varchar2(20);
  4   PRAGMA AUTONOMOUS_TRANSACTION;
  5  begin
  6     update student s set s.sname = '张三' where s.sno = 1;
  7  select s.sname into stu_name  from student s where s.sno = 1;
  8    dbms_output.put_line(stu_name);
  9   需要添加  commit ; --假如设置了自主事务了,那么就必须要有显示的commit/rowback否则报下面的错误
 10  end;
 11  /
Procedure created

SQL>
SQL>
SQL>
SQL> create or replace procedure pro3
  2  as
  3  stu_name varchar2(20);
  4  begin
  5     pro2();
  7    select s.sname into stu_name from student s where s.sno = 1;
  8    dbms_output.put_line(stu_name);
  9   在此处添加  commit ; --不会影响自主事务的因为自主事务需要显示提交或回退,否则会自动回退并报错如下面错误
  9    end;
 10  /
Procedure created

SQL> execute pro3;
张三
begin pro3; end;
ORA-06519: 检测到活动的独立的事务处理, 已经回退
ORA-06512: 在 "SYS.PRO2", line 10
ORA-06512: 在 "SYS.PRO3", line 5
ORA-06512: 在 line 1

SQL> 


需要注意一点:
1. 在子程序中假如没有参数需要输入或输出那么()不能带。只能: create or replace procedure/function pro_fun_name ()
2. 参数只能是IN类型
相关文章
相关标签/搜索