oracle存储过程语法详解,在oracle中创建存储过程的语法

oracle存储过程语法详解,在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 PROCEDURE 存储过程名字
　　(
　　参数 IN NUMBER
　　参数 IN NUMBER

　　) IS
　　变量 INTEGER := ;
　　变量 DATE;
　　BEGIN
　　END 存储过程名字
　　 SELECT INTO STATEMENT
　　将select查询的结果存入到变量中 可以同时将多个列存储多个变量中 必须有一条
　　记录 否则抛出异常(如果没有记录抛出NO_DATA_FOUND)
　　例子
　　BEGIN
　　SELECT col col into 变量 变量 FROM typestruct where xxx;
　　EXCEPTION
　　WHEN NO_DATA_FOUND THEN
　　xxxx;
　　END;
　　
　　 IF 判断
　　IF V_TEST= THEN
　　BEGIN
　　do something
　　END;
　　END IF;
　　 while 循环
　　WHILE V_TEST= LOOP
　　BEGIN
　　XXXX
　　END;
　　END LOOP;
　　 变量赋值
　　V_TEST := ;
　　 用for in 使用cursor
　　
　　IS
　　CURSOR cur IS SELECT * FROM xxx;
　　BEGIN
　　FOR cur_result in cur LOOP
　　BEGIN
　　V_SUM :=cur_result 列名 +cur_result 列名
　　END;
　　END LOOP;
　　END;
　　 带参数的cursor
　　CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;
　　OPEN C_USER(变量值);
　　LOOP
　　FETCH C_USER INTO V_NAME;
　　EXIT FETCH C_USER%NOTFOUND;
　　do something
　　END LOOP;
　　CLOSE C_USER;
　　 用pl/sql developer debug
　　连接数据库后建立一个Test WINDOW
　　在窗口输入调用SP的代码 F 开始debug CTRL+N单步调试
　　 关于oracle存储过程的若干问题备忘
　　 在oracle中 数据表别名不能加as 如
　　select a appname from appinfo a; 正确
　　select a appname from appinfo as a; 错误
　　也许 是怕和oracle中的存储过程中的关键字as冲突的问题吧
　　 在存储过程中 select某一字段时 后面必须紧跟into 如果select整个记录 利用游标的话就另当别论了
　　select af keynode into kn from APPFOUNDATION af where af appid=aid and af foundationid=fid; 有into 正确编译
　　select af keynode from APPFOUNDATION af where af appid=aid and af foundationid=fid; 没有into 编译报错 提示 Compilation
　　Error: PLS : an INTO clause is expected in this SELECT statement
　　 在利用select into 语法时 必须先确保数据库中有该条记录 否则会报出 no data found 异常
　　可以在该语法之前 先利用select count(*) from 查看数据库中是否存在该记录 如果存在 再利用select into
　　 在存储过程中 别名不能和字段名称相同 否则虽然编译可以通过 但在运行阶段会报错
　　select keynode into kn from APPFOUNDATION where appid=aid and foundationid=fid; 正确运行
　　select af keynode into kn from APPFOUNDATION af where af appid=appid and af foundationid=foundationid; 运行阶段报错 提示
　　ORA :exact fetch returns more than requested number of rows
　　 在存储过程中 关于出现null的问题
　　假设有一个表A 定义如下
　　create table A(
　　id varchar ( ) primary key not null
　　vcount number( ) not null
　　bid varchar ( ) not null 外键
　　);
　　如果在存储过程中 使用如下语句
　　select sum(vcount) into fcount from A where bid= xxxxxx ;
　　如果A表中不存在bid= xxxxxx 的记录 则fcount=null(即使fcount定义时设置了默认值 如 fcount number( ):= 依然无效 fcount还是会变成null) 这样以后使用fcount时就可能有问题 所以在这里最好先判断一下
　　if fcount is null then
　　fcount:= ;
　　end if;
　　这样就一切ok了
　　 Hibernate调用oracle存储过程
　　this pnumberManager getHibernateTemplate() execute(
　　new HibernateCallback() {
　　public Object doInHibernate(Session session)
　　throws HibernateException SQLException {
　　CallableStatement cs = session
　　nnection()
　　 prepareCall( {call modifyapppnumber_remain(?)} );
　　cs setString( foundationid);
　　cs execute();
　　return null;
　　}
lishixinzhi/Article/program/Oracle/201311/16725

oracle中函数和存储过程的区别和联系

在oracle中，函数和存储过程是经常使用到的，他们的语法中有很多相似的地方，但也有自己的特点。刚学完函数和存储过程，下面来和大家分享一下自己总结的关于函数和存储过程的区别。
一、存储过程
1.定义
　　存储过程是存储在数据库中提供所有用户程序调用的子程序，定义存储过程的关键字为procedure。
2.创建存储过程
　　create [or replace] procedure 存储过程名
　　[(参数1 类型，参数2 out 类型……)]　　　
　　as
　　　　变量名　　类型；
　　begin
　　　　程序代码体
　　end;
　　示例一：无参无返
create or replace procedure p1
--or replace代表创建该存储过程时，若存储名存在，则替换原存储过程，重新创建
--无参数列表时，不需要写()
as
begin
dbms_output.put_line('hello world');
end;
--执行存储过程方式1
set serveroutput on;
begin
p1();
end;
--执行存储过程方式2
set serveroutput on;
execute p1();
　　示例二：有参有返
create or replace procedure p2
(name in varchar2,age int,msg out varchar2)
--参数列表中，声明变量类型时切记不能写大小，只写类型名即可，例如参数列表中的name变量的声明
--参数列表中，输入参数用in表示，输出参数用out表示，不写时默认为输入参数。
------------输入参数不能携带值出去，输出参数不能携带值进来，当既想携带值进来，又想携带值出去，可以用in out
as
begin
msg:='姓名'||name||',年龄'||age;
--赋值时除了可以使用：=，还可以用into来实现
--上面子句等价于select '姓名'||name||',年龄'||age into msg from dual;
end;
--执行存储过程
set serveroutput on;
declare
msg varchar2(100);
begin
p2('张三',23,msg);
dbms_output.put_line(msg);
end;
　　示例三：参数列表中有in out参数
create or replace procedure p3
(msg in out varchar2)
--当既想携带值进来，又想携带值出去，可以用in out
as
begin
dbms_output.put_line(msg); --输出的为携带进来的值
msg:='我是从存储过程中携带出来的值';
end;
--执行存储过程
set serveroutput on;
declare
msg varchar2(100):='我是从携带进去的值';
begin
p3(msg);
dbms_output.put_line(msg);
end;
　　示例四：存储过程中定义参数
create or replace procedure p4
as
--存储过程中定义的参数列表
name varchar(50);
begin
name := 'hello world';
dbms_output.put_line(name);
end;
---执行存储过程
set serveroutput on;
execute p4();
总结：1.创建存储过程的关键字为procedure。
　　 2.传参列表中的参数可以用in，out，in out修饰，参数类型一定不能写大小。列表中可以有多个输入输出参数。
　　 3.存储过程中定义的参数列表不需要用declare声明，声明参数类型时需要写大小的一定要带上大小。
　　　 4.as可以用is替换。
　　 5.调用带输出参数的过程必须要声明变量来接收输出参数值。
　　 6.执行存储过程有两种方式，一种是使用execute，另一种是用begin和end包住。
　　 存储过程虽然有很多优点，但是它却不能使用return返回值。当需要使用return返回值时，我们可以使用函数。
二、存储函数
1.函数与存储过程的结构类似，但是函数必须有一个return子句，用于返回函数值。
create or replace function f1
return varchar--必须有返回值，且声明返回值类型时不需要加大小
as
msg varchar(50);
begin
msg := 'hello world';
return msg;
end;
--执行函数方式1
select f1() from dual;
--执行函数方式2
set serveroutput on;
begin
dbms_output.put_line(f1());
end;

三、存储过程与存储函数的区别和联系
　　相同点：1.创建语法结构相似，都可以携带多个传入参数和传出参数。
　　　　 2.都是一次编译，多次执行。
　　不同点：1.存储过程定义关键字用procedure，函数定义用function。
　　　　　　2.存储过程中不能用return返回值，但函数中可以，而且函数中必须有return子句。
　　　　　　3.执行方式略有不同，存储过程的执行方式有两种（1.使用execute2.使用begin和end），函数除了存储过程的两种方式外，还可以当做表达式使用，例如放在select中（select f1() form dual;）。
总结：如果只有一个返回值，用存储函数，否则，一般用存储过程。

oracle中的存储过程怎么写

Oracle存储过程写法实例
总结项目中写的存储过程例子：
Oracle存储过程基本语法 存储过程
　　1 CREATE OR REPLACE PROCEDURE 存储过程名
　　2 IS/AS
　　3 BEGIN
　　4 NULL;
　　5 EXCEIPTION;
　　6 END;
1、创建存储过程，后面可用is或者as：
create or replace procedure PRO_COMPLAIN_TEMPLATE as
2、定义变量，此处用到了%TYPE和%ROWTYPE，参考 /database/201211/168564.html ，另外定义了一个游标，TEM_INSTANCE TEMPLATE_CUR%ROWTYPE这个类型定义必须要在游标定义之后：
NEED_DO_FOR_ZL INTEGER;
CURRENT_MAX_ID MEMO_TEMPLET.TEMPLET_ID%TYPE;
CURSOR TEMPLATE_CUR IS SELECT TEMPLET_NAME, TEMPLET_CONTENT FROM SYS_COMPLAINT_TEMPLET;
TEM_INSTANCE TEMPLATE_CUR%ROWTYPE;
3、begin开始块：
begin
4、该插入语句使用了DBLINK，还有使用DBMS_OUTPUT.put_line('打印信息')进行信息输出：
insert into MEMO_TEMPLET (TEMPLET_ID, TEMPLET_CONTENT, TEMPLET_TYPE, TEMPLET_MEMO) (SELECT * FROM MEMO_TEMPLET@COMPANY);
SELECT COUNT(*) INTO NEED_DO_FOR_ZL FROM SYS_COMPLAINT_TEMPLET;
IF(NEED_DO_FOR_ZL > 0) THEN
DBMS_OUTPUT.put_line('打印信息');
SELECT MAX(TEMPLET_ID)+1 INTO CURRENT_MAX_ID FROM MEMO_TEMPLET;
DBMS_OUTPUT.put_line('MEMO_TEMPLET最大ID' || CURRENT_MAX_ID);
FOR TEM_INSTANCE IN TEMPLATE_CUR LOOP
INSERT INTO MEMO_TEMPLET (TEMPLET_ID, TEMPLET_CONTENT,
TEMPLET_TYPE, TEMPLET_MEMO) VALUES (CURRENT_MAX_ID,
TEM_INSTANCE.TEMPLET_CONTENT, '7', NULL);
CURRENT_MAX_ID := CURRENT_MAX_ID + 1;
DBMS_OUTPUT.put_line(TEM_INSTANCE.TEMPLET_CONTENT);
END LOOP;
END IF;
COMMIT;
www.2cto.com
4、exception块，使用WHEN OTHERS THEN，其中用raise可显示错误信息：
exception
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('sqlcode : ' ||sqlcode);
raise;
ROLLBACK;
IF TEMPLATE_CUR%ISOPEN THEN
CLOSE TEMPLATE_CUR;
END IF;
DBMS_OUTPUT.put_line('打印信息');
end;
注：关于游标的使用，如果像本例中使用for循环去遍历游标的话则不需要显式的去用open/close
cursor打开和关闭游标，此处会自己处理，如果使用fetch
into的话就需要显式开关游标，另外任意执行一个update操作，用隐式游标sql的属性%found,%notfound,%rowcount,%isopen观察update语句的执行情况，也可以使用来判断游标状态，如IF
TEMPLATE_CUR%ISOPEN THEN。

Oracle 的存储过程怎么写。？

create produce(
SQL语句
)；
就这么简单，或者你可以用可视化工具，如PLSQL，找到左边树的“存储过程”右键点击创建即可。
oracle 存储过程的基本语法
1.基本结构
CREATE OR REPLACE PROCEDURE 存储过程名字
(
参数1 IN NUMBER,
参数2 IN NUMBER
) IS
变量1 INTEGER :=0;
变量2 DATE;
BEGIN
END 存储过程名字
2.SELECT INTO STATEMENT
将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条
记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)
例子：
BEGIN
SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xxxx;
END;
...
3.IF 判断
IF V_TEST=1 THEN
BEGIN
do something
END;
END IF;
4.while 循环
WHILE V_TEST=1 LOOP
BEGIN
XXXX
END;
END LOOP;
5.变量赋值
V_TEST := 123;
6.用for in 使用cursor
...
IS
CURSOR cur IS SELECT * FROM xxx;
BEGIN
FOR cur_result in cur LOOP
BEGIN
V_SUM :=cur_result.列名1+cur_result.列名2
END;
END LOOP;
END;
7.带参数的cursor
CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;
OPEN C_USER(变量值);
LOOP
FETCH C_USER INTO V_NAME;
EXIT FETCH C_USER%NOTFOUND;
do something
END LOOP;
CLOSE C_USER;
8.用pl/sql developer debug
连接数据库后建立一个Test WINDOW
在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试

oracle 存储过程中的语句

execute immediate 说明动态执行，效率性能提升
创建的是临时表
ON COMMIT DELETE ROWS 说明临时表是事务指定，每次提交后ORACLE将截断表（删除全部行）
在ORACLE的PL/SQL里：
EXECUTE IMMEDIATE 代替了以前Oracle8i中DBMS_SQL package包.
它解析并马上执行动态的SQL语句或非运行时创建的PL/SQL块.动态创建和执行SQL语句性能超前，EXECUTE IMMEDIATE的目标在于减小企业费用并获得较高的性能，较之以前它相当容易编码.尽管DBMS_SQL仍然可用，但是推荐使用EXECUTE IMMEDIATE,因为它获的收益在包之上。
-- 使用技巧
1. EXECUTE IMMEDIATE将不会提交一个DML事务执行，应该显式提交
如果通过EXECUTE IMMEDIATE处理DML命令，
那么在完成以前需要显式提交或者作为EXECUTE IMMEDIATE自己的一部分.
如果通过EXECUTE IMMEDIATE处理DDL命令,它提交所有以前改变的数据
2. 不支持返回多行的查询,这种交互将用临时表来存储记录(参照例子如下)或者用REF cursors.
3. 当执行SQL语句时，不要用分号，当执行PL/SQL块时，在其尾部用分号.
4. 在Oracle手册中，未详细覆盖这些功能。
下面的例子展示了所有用到Execute immediate的可能方面.希望能给你带来方便.
5. 对于Forms开发者,当在PL/SQL 8.0.6.3.版本中，Forms 6i不能使用此功能.
EXECUTE IMMEDIATE -- 用法例子
1. 在PL/SQL运行DDL语句
begin
execute immediate 'set role all';
end;
2. 给动态语句传值(USING 子句)
declare
l_depnam varchar2(20) := 'testing';
l_loc varchar2(10) := 'Dubai';
begin
execute immediate 'insert into dept values (:1, :2, :3)'
using 50, l_depnam, l_loc;
commit;
end;
3. 从动态语句检索值(INTO子句)
declare
l_cnt varchar2(20);
begin
execute immediate 'select count(1) from emp'
into l_cnt;
dbms_output.put_line(l_cnt);
end;
4. 动态调用例程.例程中用到的绑定变量参数必须指定参数类型.
黓认为IN类型,其它类型必须显式指定
declare
l_routin varchar2(100) := 'gen2161.get_rowcnt';
l_tblnam varchar2(20) := 'emp';
l_cnt number;
l_status varchar2(200);
begin
execute immediate 'begin ' || l_routin || '(:2, :3, :4); end;'
using in l_tblnam, out l_cnt, in out l_status;
if l_status != 'OK' then
dbms_output.put_line('error');
end if;
end;
5. 将返回值传递到PL/SQL记录类型;同样也可用%rowtype变量
declare
type empdtlrec is record (empno number(4),
ename varchar2(20),
deptno number(2));
empdtl empdtlrec;
begin
execute immediate 'select empno, ename, deptno ' ||
'from emp where empno = 7934'
into empdtl;
end;
6. 传递并检索值.INTO子句用在USING子句前
declare
l_dept pls_integer := 20;
l_nam varchar2(20);
l_loc varchar2(20);
begin
execute immediate 'select dname, loc from dept where deptno = :1'
into l_nam, l_loc
using l_dept ;
end;
7. 多行查询选项.对此选项用insert语句填充临时表，
用临时表进行进一步的处理,也可以用REF cursors纠正此缺憾.
declare
l_sal pls_integer := 2000;
begin
execute immediate 'insert into temp(empno, ename) ' ||
' select empno, ename from emp ' ||
' where sal > :1'
using l_sal;
commit;
end;
对于处理动态语句,EXECUTE IMMEDIATE 比以前可能用到的更容易并且更高效.
当意图执行动态语句时，适当地处理异常更加重要.应该关注于捕获所有可能的异常
解答如下：
1、CREATE GLOBAL TEMPORARY TABLE T_TEMP 语句是创建一个临时表，oracle中创建临时表有两种，一种是事务级的，一种是会话级的，当创建临时表语句后面有ON COMMIT delete ROWS时表示该临时表是事务级的，ON COMMIT PRESERVE ROWS 时表示该临时表是会话级的。
2、execute immediate 是动态执行sql语句。
上面语句的意思是动态执行一条创建事务级临时表的ddl语句。

在oracle中创建存储过程的语法

应该没有错误，你的param1跟userId的数据类型一致吗
这是创建存储过程的模板：
存储过程模板：
CREATE [OR REPLACE] PROCEDURE
[schema.]procedure_name[(argument [in|out|inout] type…)]
IS | AS
[本地变量声明]
BEGIN
执行语句部分
[EXCEPTION]
错误处理部分
END[procedure_name];
是as 不是is
还有就是oracle中，存储过程中直接select语句是不行的，没用，执行这个过程是没有任何返回集的
create or replace procedure sp_get_users_byId(param1 in varchar2,pr2 out sys_refcursor) is
begin
open pr2 for select * from scott.tbUsers where userId=param1;
end sp_get_users_byId;
过程里是不能直接查询的,好像是没有输出~~你用
create or replace procedure sp_get_users_byId(param1 in varchar2)
is
s varchar2(2000);
begin
s:='create table ls_table as (
select * from scott.tbUsers where userId=param1)';
execute immediate s;
end sp_get_users_byId;
这个看看可以不~~就是把你查询的结果建一个叫ls_table的表，你再查询这个表。
create or replace test(param1 in varchar2)
Authid Current_User as
begin
execute immediate 'select * from scott.tbUsers
where userid = ' || param1
end;
如果你要取返回值，必须定义游标，如果用游标返回取到的值，必须定义包，在包里写存储过程。
你也可以用楼上的方法把数据写进另一张表，不过要记得每次调用时还把表删掉。
create or replace procedure sp_get_users_byId(param1 in varchar2)
is
s varchar2(2000);
begin
s:='drop table ls_table';
execute immediate s;
s:='create table ls_table as (
select * from scott.tbUsers where userId=param1)';
execute immediate s;
end sp_get_users_byId;

oracle存储过程并且怎么写

存储过程(procedure)：是一个命名了的语句块，可以有0个或多个参数
语法：

create or replace procedure HelloWorld
as
begin
dbms_output.put_line('HelloWorld');
end;

调用存储过程
* 命令调用 exec helloworld
* 语句块调用
begin
helloworld;
end;
/

DELETE:

create or replace procedure del_emp01
is
begin
delete from emp01 where empno=7369;
end;

exec del_emp01;

create or replace procedure del_emp01(v_empno in emp01.empno%type)
is
begin
delete from emp01 wherer empno=v_empno;
end;

exec del_emp01(7521);

INSERT

create or replace procedure ins_emp01
(v_empno emp01.empno%type,v_ename emp01.ename%type)
as
begin
insert into emp01(empno,ename) values(v_empno,v_ename);
end;

exec ins_emp01(1000,'李四');

UPDATE

create or replace procedure upd_emp01
(v_empno emp01.empno%type,v_ename emp01.ename%type)
as
begin
update emp01 set ename=v_ename where empno=v_empno;
end;

exec upd_emp01(1000,'张三');

SELECT
create or replace procedure sel_emp01
(v_empno emp01.empno%type,v_emp01_data out emp01%rowtype)
as
begin
select * into v_emp01_data from emp where empno=v_empno;
end;

带有输出参数的存储过程不能使用命令直接调用

只能由语句块或程序调用(JAVA) ****************************************************************
declare
v_emp_data emp01%rowtype;
begin
sel_emp01(7499,v_emp_data);
dbms_output.put_line(v_emp_data.ename||' '||v_emp_data.sal);
end;
调用：
declare
v_emp_data emp01%rowtype;
begin
sel_emp01(7499,v_emp_data);
dbms_output.put_line(v_emp_data.ename||' '||v_emp_data.sal);
end;
使用scott用户登录
统计某个部门的员工的工资总和，员工的人数，平均工资，创建存储过程
create or replace procedure deptcount
(v_deptno emp.deptno%type,v_sal_sum out number,v_recordes out number,v_avg_sal out number,errorMsg out varchar2)
is
begin
select sum(sal) into v_sal_sum from emp group by deptno having deptno=v_deptno;
select count(*) into v_recordes from emp group by deptno having deptno=v_deptno;
select avg(sal) into v_avg_sal from emp group by deptno having deptno=v_deptno;
exception
when no_data_found then
errorMsg:='没有该部门';
end;
调用：
declare
v_sal_sum number;
v_recordes number;
v_avg_sal number(8,2);
v_errormsg varchar2(20);
begin
deptcount(90,v_sal_sum,v_recordes,v_avg_sal,v_errormsg);
dbms_output.put_line(v_sal_sum||' '||v_recordes||' '||v_avg_sal);
dbms_output.put_line(v_errormsg);
end;

//输出参数
create or replace procedure my_pro(v_num in number,v_result out number)
is
v_temp number;
begin
v_temp:=0;
for i in 1..v_num
loop
v_temp:=v_temp+i;
end loop;
v_result:=v_temp;
end;
declare
v_recieve number;
begin
my_pro(100,v_recieve);
dbms_output.put_line(v_recieve);
end;
//既是输入参数又是输出参数
create or replace procedure my_pro1(v_i in out number)
is
v_j number;
begin
v_j:=30;
v_i:=v_i*v_j;
end;

declare
v_t number;
begin
v_t:=20;
my_pro1(v_t);
dbms_output.put_line(v_t);
end;

oracle 存储过程 begin end

我理解就是用begin end界定一个代码块，跟C语言中的{}是同一个意思。
比如：
CREATE Procedure()
as
begin
。。。
/*
if ()
begin
...
end
else
begin
...
end
*/
end
而这里省略号位置如果又出现了While循环或者IF ELSE判断，当然就又需要界定范围了，你不能在里面用{}，只能嵌套begin end。
我怎么才看见这个贴呢。
Oracle存储过程基础知识
商业规则和业务逻辑可以通过程序存储在Oracle中，这个程序就是存储过程。
存储过程是SQL, PL/SQL, Java 语句的组合，它使你能将执行商业规则的代码从你的应用程序中移动到数据库。这样的结果就是，代码存储一次但是能够被多个程序使用。
要创建一个过程对象（procedural object），必须有 CREATE PROCEDURE 系统权限。如果这个过程对象需要被其他的用户schema 使用，那么你必须有 CREATE ANY PROCEDURE 权限。执行 procedure 的时候，可能需要excute权限。或者EXCUTE ANY PROCEDURE 权限。如果单独赋予权限，如下例所示：
grant execute on MY_PROCEDURE to Jelly
调用一个存储过程的例子：
execute MY_PROCEDURE( 'ONE PARAMETER');
存储过程(PROCEDURE)和函数(FUNCTION)的区别。
function有返回值，并且可以直接在Query中引用function和或者使用function的返回值。
本质上没有区别,都是 PL/SQL 程序,都可以有返回值。最根本的区别是： 存储过程是命令, 而函数是表达式的一部分。比如：
select max(NAME) FROM
但是不能 exec max(NAME) 如果此时max是函数。
PACKAGE是function，procedure，variables 和sql 语句的组合。package允许多个procedure使用同一个变量和游标。
创建 procedure的语法：
CREATE [ OR REPLACE ] PROCEDURE [ schema.]procedure
[(argument [IN | OUT | IN OUT ] [NO COPY] datatype
[, argument [IN | OUT | IN OUT ] [NO COPY] datatype]...
)]
[ authid { current_user | definer }]
{ is | as } { pl/sql_subprogram_body |
language { java name 'String' | c [ name, name] library lib_name
}]
Sql 代码：
CREATE PROCEDURE sam.credit (acc_no IN NUMBER, amount IN NUMBER) AS
BEGIN
UPDATE accounts
SET balance = balance + amount
WHERE account_id = acc_no;
END;
可以使用 create or replace procedure 语句， 这个语句的用处在于，你之前赋予的excute权限都将被保留。
IN, OUT, IN OUT用来修饰参数。
IN 表示这个变量必须被调用者赋值然后传入到PROCEDURE进行处理。
OUT 表示PRCEDURE 通过这个变量将值传回给调用者。
IN OUT 则是这两种的组合。
authid代表两种权限：
定义者权限（difiner right 默认），执行者权限（invoker right）。
定义者权限说明这个procedure中涉及的表，视图等对象所需要的权限只要定义者拥有权限的话就可以访问。
执行者权限则需要调用这个 procedure的用户拥有相关表和对象的权限。
Oracle存储过程的基本语法
1. 基本结构
CREATE OR REPLACE PROCEDURE 存储过程名字
(
参数1 IN NUMBER,
参数2 IN NUMBER
) AS
变量1 INTEGER :=0;
变量2 DATE;
BEGIN
END 存储过程名字
2. SELECT INTO STATEMENT
将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条
记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)
例子：
BEGIN
SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xxxx;
END;
...
3. IF 判断
IF V_TEST=1 THEN
BEGIN
do something
END;
END IF;
4. while 循环
WHILE V_TEST=1 LOOP
BEGIN
XXXX
END;
END LOOP;
5. 变量赋值
V_TEST := 123;
6. 用for in 使用cursor
...
IS
CURSOR cur IS SELECT * FROM xxx;
BEGIN
FOR cur_result in cur LOOP
BEGIN
V_SUM :=cur_result.列名1+cur_result.列名2
END;
END LOOP;
END;
7. 带参数的cursor
CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;
OPEN C_USER(变量值);
LOOP
FETCH C_USER INTO V_NAME;
EXIT FETCH C_USER%NOTFOUND;
do something
END LOOP;
CLOSE C_USER;
8. 用pl/sql developer debug
连接数据库后建立一个Test WINDOW
在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试
9. Pl/Sql中执行存储过程
在sql*plus中：
declare
--必要的变量声明，视你的过程而定
begin
execute yourprocudure(parameter1,parameter2,...);
end
/
在SQL/PLUS中调用存储过程，显示结果：
SQL>set serveoutput on --打开输出
SQL>var info1 number; --输出1
SQL>var info2 number; --输出2
SQL>declare
var1 varchar2(20); --输入1
var2 varchar2(20); --输入2
var3 varchar2(20); --输入2
BEGIN
pro(var1,var2,var3,:info1,:info2);
END;
/
SQL>print info1;
SQL>print info2;
注：在EXECUTE IMMEDIATE STR语句是SQLPLUS中动态执行语句，它在执行中会自动提交，类似于DP中FORMS_DDL语句，在此语句中str是不能换行的,只能通过连接字符"||"，或着在在换行时加上"-"连接字符。