oracle有参数的存储过程

‘壹’ oracle存储过程基本语法

一：存储过程

创建存储过程，需要有CREATE PROCEDURE或CREATE ANY PROCEDURE的系统权限   

删除存储过程，是过程的创建者或者拥有DROP ANY PROCEDURE系统权限 

修改存储过程，则只能是过程的创建者或者拥有ALTER ANY PROCEDURE系统权限的人

执行(或调用)存储过程，是过程的创建者或是拥有EXECUTE ANY PROCEDURE系统权限的人或是被拥有者授予EXECUTE权限的人

1：语法

 1）创建

CREATE[ORREPLACE]PROCEDURE存储过程名[(参数[IN|OUT|INOUT] 数据类型...)]

{AS|IS}

[说明部分]

BEGIN

可执行部分

[EXCEPTION

错误处理部分]

END[过程名];

 说明：

   OR REPLACE 表示如果存在就覆盖存储过程

   参数有三种形式：IN、OUT和IN OUT。则默认为IN。

  关键字AS也可以写成IS，后跟过程的说明部分，可以在此定义过程的局部变量。

2）删除存储过程：

DROP PROCEDURE 存储过程名；

3）修改存储过程：

      ALTER PROCEDURE 存储过程名 COMPILE； 

4）执行存储过程

      EXECUTE 模式名.存储过程名[(参数...)]; 

     或

       BEGIN 

模式名.存储过程名[(参数...)];

END;

另外：参数可以是变量、常量或表达式

 要其它用户执行存储过程须要给其它用户授权

GRANT EXECUTE ON 存储过程名 TO 用户名

 2：参数说明

IN 定义一个输入参数变量，用于传递参数给存储过程

OUT 定义一个输出参数变量，用于从存储过程获取数据

IN OUT 定义一个输入、输出参数变量，兼有以上两者的功能

 1）参数名 IN 数据类型 DEFAULT 值；

          定义一个输入参数变量，用于传递参数给存储过程。

          可以是常量、有值变量或表达式

          DEFAULT 关键字为可选项，用来设定参数的默认值。如果在调用存储过程时不指明参数，则参数变量取默认值

          在存储过程中，输入变量接收主程序传递的值，但不能对其进行赋值。

2）参数名 OUT 数据类型；

          定义一个输出参数变量，用于从存储过程获取数据，即变量从存储过程中返回值给主程序。

          在调用存储过程时，主程序的实际参数只能是一个变量，而不能是常量或表达式。

          在存储过程中，参数变量只能被赋值而不能将其用于赋值，而且必须给输出变量至少赋值一次。

3）参数名 IN OUT 数据类型 DEFAULT 值； 

          定义一个输入、输出参数变量，兼有以上两者的功能。

          在调用存储过程时，主程序的实际参数只能是一个变量，而不能是常量或表达式。

          DEFAULT 关键字为可选项，用来设定参数的默认值。

          在存储过程中，变量接收主程序传递的值，同时可以参加赋值运算，也可以对其进行赋值。在存储过程中必须给变量至少赋值一次。

补充：如果省略IN、OUT或IN OUT，则默认模式是IN。 

          调用它时参数个数与位置可以不一致，用以下形式调用：

EXECUTE CHANGE_SALARY(P_RAISE=>80,P_EMPNO=>7788);  //=>运算符左侧是参数名，右侧是参数表达式

 二：函数

创建函数，需要有CREATE PROCEDURE或CREATE ANY PROCEDURE的系统权限

删除函数，需要是函数的创建者或者是拥有DROP ANY PROCEDURE系统权限的人

修改函数，需要是函数的创建者或者拥有ALTER ANY PROCEDURE系统权限的人

执行函数，需要是函数的创建者或拥有EXECUTE ANY PROCEDURE系统权限的人

1：语法

1）创建：

CREATE[ORREPLACE]FUNCTION函数名[(参数[IN] 数据类型...)]

RETURN数据类型

{AS|IS}

[说明部分]

BEGIN

可执行部分

RETURN(表达式)

[EXCEPTION

    错误处理部分]

END[函数名];

 说明：

     参数是可选的，但只能是IN类型(IN关键字可以省略)。

     在定义部分的RETURN 数据类型，用来表示函数的数据类型，也就是返回值的类型，不可省略。

     在可执行部分的RETURN(表达式)，用来生成函数的返回值，其表达式的类型应该和定义部分说明的函数返回值的数据类型一致。在函数的执行部分可以有多个RETURN语句，但只有一个RETURN语句会被执行，一旦执行了RETURN语句，则函数结束并返回调用环境。 

2）删除

      DROP FUNCTION 函数名；

3）修改

      ALTER PROCEDURE 函数名 COMPILE；

4）执行

      变量名:=函数名(...) 

三：对存储过程 和 函数的查看（可以通过对数据字典【USER_SOURCE】的访问来查询存储过程或函数的有关信息）

 1：查询某个存储过程序内容

select TEXT from user_source WHERE NAME='存储过程名';

 2：查看数据字殿

DESCRIBE USER_SOURCE ; //命令行中

 3：查看存储过程的参数

DESCRIBE say_hello; //后面是过程名

 4：查看发生编辑错误

SHOW ERRORS ;

 5：查询一个存储过程或函数是否是有效状态(即编译成功)

SELECT STATUS FROM USER_OBJECTS WHERE OBJECT_NAME='过程名';//注意大小写

VALID表示该存储过程有效(即通过编译)，INVALID表示存储过程无效或需要重新编译。它的状态会改变，这与它依赖外部表（表删除修改等操作）有关系

6：查看存储过程与表的依赖关系

SELECT REFERENCED_NAME,REFERENCED_TYPE FROM USER_DEPENDENCIES WHERE NAME='SAY_HELLO';

 说明

NAME为实体名，TYPE为实体类型，REFERENCED_OWNER为涉及到的实体拥有者账户，REFERENCED_NAME为涉及到的实体名，REFERENCED_TYPE 为涉及到的实体类型。

问题：

如果一个用户A被授予执行属于用户B的一个存储过程的权限，在用户B的存储过程中，访问到用户C的表，用户B被授予访问用户C的表的权限，但用户A没有被授予访问用户C表的权限，那么用户A调用用户B的存储过程是失败的还是成功的呢？答案是成功的。

‘贰’ ORACLE中如何为存储过程传递参数

第一种：只读。参数是只读的，不能修改，即调用时传递进来的是常量，或者变量（但变量不能在存储过程中修改正薯）。通常select及DML类型的存储过程传递的是in类型的参数。

第二种：只写。忽略调用语句传递的任何参数，并在函数（过程）内部给这些参数赋值，因此是只写的。（这种情况是在函数或过程内部给参数重新赋值，但重新赋值后的参举御者数是无法被外部调用的（好像游标类型的参数除外））

CREATE OR REPLACE PROCEDURE "SCOTT"."SWAP" (firstValue out
number, secondValue out number) is
temp number;
begin
temp := firstValue;
firstValue := secondValue;
secondValue := temp;
end swap;

外部调用：

set serveroutput on;
declare
firstVal number;
secondVal number;
begin
firstVal := 10;
secondVal := 20;
scott.swap(firstVal,secondVal);
dbms_output.put_line('first is ' || firstVal);
dbms_output.put_line('second is ' || secondVal);
end;

无法在外部访问到firstValue与secondValue的值。此时打印出的结果为：

first is
second is
第三种：读或写。这可以完全控制参数，读取传递的参数的值。可以再函数（过程）内部修改参数的值，在退出函数（过程）后，这些参数被赋给在函数内部写入的值，这样就可以返回多个值。（即入口参数写入值后，可以传递到函数（过程）的外部，供外部调用的时候使用）

ps:函数中的返回值为如下几种：

char; varchar2; number; integer; date; boolean; table; record

sql> CREATE OR REPLACE PROCEDURE HelloWorld2 (

2 p_user_name IN VARCHAR2,

3 p_out_val OUT VARCHAR2,

4 p_inout_val IN OUT VARCHAR2

5 ) AS

6 BEGIN

7 dbms_output.put_line('Hello ' || p_user_name || p_inout_val || '!');

8 p_out_val := 'A';

9 p_inout_val := 'B';

10 END HelloWorld2;

11 /

Procere created.

SQL> DECLARE

2 p_outval VARCHAR2(10);

3 p_inoutval VARCHAR2(10) := '~Hi~';

4 BEGIN

5 HelloWorld2('Edward', p_outval, p_inoutval);

6

7 dbms_output.put_line('p_outval=' || p_outval);

8 拆橡dbms_output.put_line('p_inoutval=' || p_inoutval);

9 END;

10 /

Hello Edward~Hi~!

p_outval=A

p_inoutval=B

PL/SQL procere successfully completed.

‘叁’ oracle中怎么执行带有输出参数的存储过程，在程序中我知道怎么调用，

下面说明在SQL
DEVELOPER中的做法，右击存储结构的文件名，在菜单中选择RUN，会跳出一个叫RUN
PL/SQL的页面，在下方写着PL/SQL
BLOCK
的地方修改输入参数
spno
=>spno
修改为spno
=>真实的值
具体可以参看HELP中的Run/Debug/Profile
PL/SQL

‘肆’ Oracle存储过程及举例（几种参数情况的存储

create table TESTTABLE
(
id1 VARCHAR2(12),
name VARCHAR2(32)
)
select t.id1,t.name from TESTTABLE t
insert into TESTTABLE (ID1, NAME)
values ('1', 'zhangsan');

insert into TESTTABLE (ID1, NAME)
values ('2', 'lisi');

insert into TESTTABLE (ID1, NAME)
values ('3', 'wangwu');

insert into TESTTABLE (ID1, NAME)
values ('4', 'xiaoliu');

insert into TESTTABLE (ID1, NAME)
values ('5', 'laowu');
---创建存储过程
create or replace procere test_count
as
v_total number(1);
begin
select count(*) into v_total from TESTTABLE;
DBMS_OUTPUT.put_line('总人数：'||v_total);
end;
--准备
--线对scott解锁：alter user scott account unlock;
--应为存储过程是在scott用户下。还要给scott赋予密码
---alter user scott identified by tiger;
---去命令下执行
EXECUTE test_count;
----在ql/spl中的sql中执行
begin
-- Call the procere
test_count;
end;

create or replace procere TEST_LIST
AS
---是用游标
CURSOR test_cursor IS select t.id1,t.name from TESTTABLE t;
begin
for Test_record IN test_cursor loop---遍历游标，在打印出来
DBMS_OUTPUT.put_line(Test_record.id1||Test_record.name);
END LOOP;
test_count;--同时执行另外一个存储过程（TEST_LIST中包含存储过程test_count）
end;
-----执行存储过程TEST_LIST
begin
TEST_LIST;
END;
---存储过程的参数
---IN 定义一个输入参数变量，用于传递参数给存储过程
--OUT 定义一个输出参数变量，用于从存储过程获取数据
---IN OUT 定义一个输入、输出参数变量，兼有以上两者的功能
--这三种参数只能说明类型，不需要说明具体长度 比如 varchar2(12)，defaul 可以不写，但是作为一个程序员最好还是写上。

---创建有参数的存储过程
create or replace procere test_param(p_id1 in VARCHAR2 default '0')
as v_name varchar2(32);
begin
select t.name into v_name from TESTTABLE t where t.id1=p_id1;
DBMS_OUTPUT.put_line('name：'||v_name);
end;
----执行存储过程
begin
test_param('1');
end;

default '0'

---创建有参数的存储过程
create or replace procere test_paramout(v_name OUT VARCHAR2 )
as
begin
select name into v_name from TESTTABLE where id1='1';
DBMS_OUTPUT.put_line('name：'||v_name);
end;
----执行存储过程
DECLARE
v_name VARCHAR2(32);
BEGIN
test_paramout(v_name);
DBMS_OUTPUT.PUT_LINE('name：'||v_name);
END;
-------IN OUT
---创建存储过程
create or replace procere test_paramINOUT(p_phonenumber in out varchar2)
as
begin
p_phonenumber:='0571-'||p_phonenumber;
end;

----
DECLARE
p_phonenumber VARCHAR2(32);
BEGIN
p_phonenumber:='26731092';
test_paramINOUT(p_phonenumber);
DBMS_OUTPUT.PUT_LINE('新的电话号码：'||p_phonenumber);
END;
-----sql命令下，查询当前用户的存储过程或函数的源代码，
-----可以通过对USER_SOURCE数据字典视图的查询得到。USER_SOURCE的结构如下：

SQL> DESCRIBE USER_SOURCE ;
Name Type Nullable Default Comments

---- -------------- -------- -------
-------------------------------------------------------------------------------------------------------------
NAME VARCHAR2(30) Y Name of the object

TYPE VARCHAR2(12) Y Type of the object: "TYPE", "TYPE BODY", "PROCEDURE", "FUNCTION",
"PACKAGE", "PACKAGE BODY" or "JAVA SOURCE"
LINE NUMBER Y Line number of this line of
source

TEXT VARCHAR2(4000) Y Source text

SQL>
---查询出存储过程的定义语句
select text from user_source WHERE NAME='TEST_COUNT';
----查询存储过程test_paramINOUT的参数
SQL> DESCRIBE test_paramINOUT;
Parameter Type Mode Default?
------------- -------- ------ --------
P_PHONENUMBER VARCHAR2 IN OUT
SQL>
---查看当前的存储过程的状态是否正确，
---VALID为正确，INVALID表示存储过程无效或需要重新编译
SELECT STATUS FROM USER_OBJECTS WHERE OBJECT_NAME='TEST_COUNT';
-----如果要检查存储过程或函数的依赖性，可以通过查询数据字典USER_DENPENDENCIES来确定，该表结构如下：
SQL> DESCRIBE USER_DEPENDENCIES;
Name Type Nullable Default Comments
-------------------- ------------- -------- ------- ----------------------------------------------------------
NAME VARCHAR2(30) Name of the object
TYPE VARCHAR2(17) Y Type of the object
REFERENCED_OWNER VARCHAR2(30) Y Owner of referenced object (remote owner if remote object)
REFERENCED_NAME VARCHAR2(64) Y Name of referenced object
REFERENCED_TYPE VARCHAR2(17) Y Type of referenced object
REFERENCED_LINK_NAME VARCHAR2(128) Y Name of dblink if this is a remote object
SCHEMAID NUMBER Y
DEPENDENCY_TYPE VARCHAR2(4) Y
SQL>
---查询存储过程TEST_COUNT的依赖关系
SELECT REFERENCED_NAME,REFERENCED_TYPE FROM USER_DEPENDENCIES WHERE NAME='TEST_COUNT';

‘伍’ oracle 中用什么命令执行一个带参数的存储过程

具体如下：

1、第一步，创建一个新的存储过程，见下图，转到下面的步骤。

‘陆’ Oracle存储过程的带参存储过程

模式描述IN参数（默认模式）（输入参数）用来从调用环境中向存储过程传递值，不能给IN参数赋值，给此参数传递的值可以是常量、有值的变量、表达式等。OUT参数（输出参数）用来从过程中返回值给调用者，不能将此参数的值赋给另一个变量，不能是常量或表达式。在过程体内，必须给OUT参数赋值。INOUT参数（输入输出参数）既可以从调用者向过程中传递值，执行过程后还可返回可能改变了的值给调用者。 模式描述IN参数（默认模式）（输入参数）用来从调用环境中向存储过程传递值，不能给IN参数赋值，给此参数传递的值可以是常量、有值的变量、表达式等。OUT参数（输出参数）用来从过程中返回值给调用者，不能将此参数的值赋给另一个变量，不能是常量或表达式。在过程体内，必须给OUT参数赋值。INOUT参数（输入输出参数）既可以从调用者向过程中传递值，执行过程后还可返回可能改变了的值给调用者。模式
描述
用来从调用环境中向存储过程传递值，不能给IN参数赋值，
给此参数传递的值可以是常量、有值的变量、表达式等。
用来从过程中返回值给调用者，不能将此参数的值赋给另
一个变量，不能是常量或表达式。在过程体内，必须给
OUT参数赋值。
既可以从调用者向过程中传递值，执行过程后还可返回可能
改变了的值给调用者。 如果形式参数是IN模式的参数，实际参数可以是一个具体的值或一个有值的变量；如果形式参数是OUT模式的参数，实际参数必须是一个变量，当调用过程后，此变量就被赋值了。可以输出此变量的值来测试过程执行的结果。如果形式参数是INOUT模式的参数，则实际参数必须是一个预先已经赋值的变量。执行完过程后，该变量被重新赋值，可以输出此变量的值来测试过程执行结果。

‘柒’ 关于调用带输出参数的oracle存储过程

可以通过以下方式来进行带参数存储过程执行。

SQL>createorreplaceproceresp_pro10
2(spnoinnumber,spnameoutvarchar2)is
3begin
4select'ename'intospnamefromal;
5end;
6/

过程已创建。

SQL>setserveroutputon
SQL>DECLARE
2spnonumber;
3spnamevarchar2(10);
4BEGIN
5sp_pro10(spno,spname);
6
7dbms_output.put_line(spname);
8END;
9/
ename

PL/SQL过程已成功完成。

‘捌’ oracle带参（传入，传出)的存储过程怎么执行

1、新建存储过程，Create or ReplaceProcere CHK_SYS_EMP 检查系统工液陆族号。

‘玖’ oracle 中用什么命令执行一个带参数的存储过程

具体如下：

1、第一步，创建一个新的存储过程，见下图，转到下面的步骤。

‘拾’ oracle带输入参数的存储过程

createorreplaceproceresp_test(iv_yinvarchar2,iv_minvarchar2)
as
v_countinteger;
begin
selectcount(*)intov_countfromtable_testwhereyear=iv_yandmonth=iv_m;
ifv_count>0then
Raise_Application_Error(-20001,'数据已存在！');
else
insertintotable_testvalues(iv_y,iv_m);
commit;
endif;
end;