过程和函数

1enny

浏览: 70556 次
性别:
来自: 深圳

最近访客更多访客>>

文朝君123

春秋战国灞桥游

yhq1212

渭水飞熊

博主相关

博客

微博

相册

留言

关于我

文章分类

全部博客 (144)

社区版块

存档分类

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>




createorreplaceprocedurepro3(num1inoutnumber,num2inoutnumber)

asswi_tempnumber(20);
begin
 swi_temp:=num1;
 num1:=num2;
 num2:=swi_temp;

end;

declare
 num1number(20);
 num2number(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);
 4PRAGMA 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);
 4PRAGMA 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);
 9commit;--假如设置了自主事务了，那么就必须要有显示的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
 5pro2();
 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类型

分享到：

游标 | PLSQL基础

2015-02-25 09:43
浏览 361
评论(0)
查看更多

发表评论

您还没有登录,请您登录后再发表评论

最近访客更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论

过程和函数

评论

发表评论

相关推荐

最近访客 更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论

过程和函数

评论

发表评论

相关推荐

最近访客更多访客>>