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 存储过程

create or replace procere sp_add_emp2( --创建名为sp_add_emp2存储过程
v_empno emp.empno%type, --------- 传入存储过程中的参数
v_ename emp.ename%type,
v_deptno dept.deptno%type,
v_dname dept.dname%type,

num out number ----------存储过程执行完毕后返回的值
)as
num1 number; ------自定义变量
num2 number;
begin
--查询dept表中的数据总共有多少行，赋值给num1，
--条件是字段deptno等于v_deptno(传进来的参数)
select count(*) into num1 from dept where deptno=v_deptno;
if(num1=0) then --如果没数据
--则给dept表的deptno 和dname列插入一条数据，值为v_deptno,v_dname
insert into dept(deptno,dname) values(v_deptno,v_dname);
end if; --If语句结束语
---同上。
select count(*) into num2 from emp where empno=v_empno;
if(num2=0)then
insert into emp(empno,ename,deptno) values(v_empno,v_ename,v_deptno);
else --如果查询到有数据则提示错误信息，员工ID重复，不插入数据
raise_application_error(-202021,'员工id 重复！！！');
end if;

num:=num1; --定义的输出参数等于num1。
commit; --结束存储过程。

‘叁’ 新手学习 Oracle存储过程怎么编写

示例参考:
建立存储过程
CREATE OR REPLACE PROCEDURE P_name--存储过程名字
(
i_var in int, --输入参数1
o_var OUT varchar2 --输出参数1
)
IS
v_STR VARCHAR2(200); --定义存储过程内部的局部变量
BEGIN
--下面是存储过程的主体实现部分
v_STR := i_var;
dbms_output.put_line(v_STR);
o_var := v_STR;
exception--错误异常处理部分
when others then
dbms_output.put_line(sqlerrm);--打印错误信息
END P_name

‘肆’ oracle存储过程

create table dept
(deptno number,
dname varchar2(20),
loc varchar2(20)
)
1.如果你是取出单个的，你可以定义一个或者多个变量，将取出的值赋给变量
例如：

create or replace procere dept_procere(a in varchar2)
is
v_no number;
v_a varchar2(20);
begin
select a.deptno,a.loc into v_no,v_a
from dept a
where a.dname=a;
dbms_output.put_line('学号为：'||v_no||);
end;
2.如果你取出的是一个结果集，你可以用游标(一个简单的例子)
create or replace procere dept_procere(a in varchar2,v_a out dept%rowtype)
is
--声明游标
cursor c_de(a in varchar2)
is select * from dept where dname=a;
begin
--打开游标，对其中找到的记录进行遍历
open c_de(a);
loop
fetch c_de into v_a;
exit when c_de%notfound;
end loop;
close c_de;
dbms_output.put_line('deptno:'||v_a.deptno);
dbms_output.put_line('dname:'||v_a.dname);
dbms_output.put_line('loc:'||v_a.loc);
end;

3.可注意：在创建存储过程的时候加上or replace，如果遇到重名的存储过程将替换掉！
4.定义变量后面记得加上分号
5.oracle中可以对它直接赋值，可不用set， v_a:='a';
（6.给变量赋值的时候注意它的数据类型 a要单引号）
7.如果你这存储过程是将参数赋值给一个你定义的变量的话，那这样你不觉得是多此一举么？一般将参数赋值给你定义的变量的话，可以进行数据类型转换！可在此存储过程中，没发现它的用处
8.你取出来的数据，要放在变量或是游标中，显示出来，不然你写出来的存储过程有什么用呢？所以 select ...into ...
9.我也是刚学oracle的，希望可以互相学习一下，呵呵！上面的如有错误希望可以告诉我一下啊

‘伍’ oracle存储过程怎么写循环

写循环的操作方法和步骤如下：

1、第一步，编写存储过程的整体结构，然后定义变量，见下图。

‘陆’ Oracle 中定义变量的区别

很明显的一个问题
v_forgid
number,
v_detailcode
varchar2
是存储过程的两个输入参数，调用该存储过程时赋值的两个参数变量
而as后面
v_fid
number;
v_detailval
number;
是存储过程自定的两个参数，作用v_fid
是接受输入参数的值，如v_fid
:=
v_forgid;。
便于变量赋值
where
a.forgid
=
v_fid
v_detailval
是将从表中查出的
a.detailval字段赋值给v_detailval，暂时存储如：
into
v_detailval
当然也可以不定义as后面两个自定义变量v_fid
也可以不定义，直接写where
a.forgid
=
v_forgid
这下你应该明白了吧，有空好好看看存储过程就知道咯！
希望能帮到你！

‘柒’ 请问一下Oracle存储过程中声明变量一定要declare吗

不需要加declare的,直接在"IS(或AS)"后面紧跟着定义这个存储过程的局部变量即可.

‘捌’ 怎样在oracle存储过程中定义数组

首先你需要定义一个数组类型，然后定义这个数组变量
declare
type a_type is table of number;
-- type a_type is array(10) of number;
-- 下面一种定义方式则指定了该数组的最大元素个数

a a_type := a_type(); -- 定义并初始化一个数组变量
begin
a.extend(3); -- 数组扩展到3个元素
a(1) := 1;
a(2) := 10;
a(3) := 100;
end;

另外数组还有一下方法和属性
first -- 第一个元素下标
last -- 最后一个元素下标
count -- 数组元素个数
prior(n) -- 下标 n 的前一个元素下标
next(n) -- 下标 n 后一个元素下标
extend(n) -- 添加 n 个数组元素，不带参数添加一个数组元素
delete(n) -- 删除数组中下标为 n 的元素，不带参数删除整个数组元素

‘玖’ Oracle 存储过程的动态变量如何使用

实现思路：
第一步：先定义一个字符串，这个字符串SQL是由动态变量和其他的sql语句成分拼接组成。
第二步：直接执行 execute immediate SQL;
备注：以上方法是oracle提供的动态执行方法语句，直接使用就可以。

VAR_DYNAMIC_SQL := 'INSERT INTO RP_COMFIRMED_EVENT_DETAIL( EVENT_ID,--ID TRANS_TIME,--交易时间 TRANS_TYPE,--交易类型 TRANS_PROVINCE,--开户省份 CCE_CSTNO,--客户号 CCE_CERTTYPE,--证件类型 CCE_CERTNO,--证件号 CCE_AMT,--交易金额 CCE_RISK_SCORE,--不处理的风险分值 CCE_PAYEE_ACC,--付款人账号 CCE_PAYEE_NAME,--付款人姓名 CCE_REC_NODE,--收款人开户行 CCE_REC_ACC,--收款人账号 CCE_REC_NAME,--收款人姓名 RULE_NAME,--规则名称 CCE_RULE_NOTE,--规则描述 SCORE_RANGE--处理后的风险分值 )select EVENT_ID, SYS_CREATION_DATE, GET_NAME_BY_CODE(GET_TOTAL_EVENT_TYPE(T1.EVENT_TYPE,T1.USER_DEFINED_EVENT_TYPE),''eventType'') AS TRANS_TYPE, -- get_province(IP_COUNTRY,IP_CITY) AS CCE_TRANSTYPE, get_vale_from_cd_p('||get_vale_from_cd1('13','5')||'),--开户省份为自定义字段 USER_ID, '||get_vale_from_cd1('4','4')||',--证件类型为自定义字段 '||get_vale_from_cd1('7','4')||',--证件号为自定义字段 要转换成动态SQL GET_AMT_BY_CURRENCY(AMOUNT_ORIGINAL,AMOUNT_CURRENCY),--通过汇率和币种转换 RISK_SCORE, USER_ACCT_NUMBER, '||get_vale_from_cd_g1(get_vale_from_cd_al)||',--付款人姓名 '||get_vale_from_cd1('11','5')||',--收款人开户行 自定义字段 需转换成动态SQL EXT_ACCT_NUMBER, EXT_ACCT_OWNER_NAME, NVL(POLICY_RULE_ID,''FALLBACK_RULE'') AS RULE_NAME, SUBSTR(GET_RULE_ORDER_ID(ORG_ID,POLICY_RULE_ID),INSTR(GET_RULE_ORDER_ID(ORG_ID,POLICY_RULE_ID),''-'')+1),--描述 GET_SCORE_RANGE(RISK_SCORE) AS RISK_SCORE_RANGE FROM EVENT_LOG_REALTIME T1))'; execute immediate VAR_DYNAMIC_SQL;

‘拾’ oracle 存储过程中select * from 变量 这么写这个怎么定义

用动态SQL。

CREATEORREPLACEPROCEDURETEST(I_TABLEINVARCHAR2)
IS
BEGIN
DECLARE
STRSQLVARCHAR2(4000);
P_VALUEVARCHAR2(4000);
BEGIN
STRSQL:='SELECTCOLFROM'||I_TABLE;--COL为字段名，保证能且只返回一行
_VALUE;
EXCEPTION
WHENOTHERSTHEN
--这里是异常处理
P_VALUE:='';
END;
END;
/

不明白可以追问。