存储过程基本语法2

oracle 存储过程的基本语法

2009-02-19 17:44:46| 分类： 标签： |字号大中小 订阅

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 T

YPEID=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存储过程的若干问题备忘

1.在oracle中，数据表别名不能加as，如：

select a.appname

from appinfo a;-- 正确

select a.appname from appinfo as a;-- 错误

也许，是怕和oracle中的存储过程中的关键字as冲突的问题吧

2.在存储过程中，select某一字段时，后面必须紧跟into

，如果select整个记录，利用游

标的话就另当别论了。

select af.keynode into kn from APPFOUNDATION af where af.appid=aid an

d af.foundationid=fid;-- 有into，正确编译

select af.keynode from APPFOUNDATION af where af.appid=aid and af.fou

ndationid=fid;-- 没有into，编译报错，提示：

Compilation

Error: PLS-00428: an INTO clause is expected in this SELECT statement

3.在利用select...into...语法时，必须先确保数据库中有该条记录，否则会报出"no data

found"异常。

可以在该语法之前，先利用select count(*) from 查看数据库中是否存在该记录，如

果存在，再利用select...into...

4.在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会报错 select

keynode into kn from APPFOUNDATION where appid

=aid and foundat

ionid=fid;-- 正确运行

select af.keynode

into kn from APPFOUNDATION af where af.appid=appid an

d

af.foundationid=foundationid;-- 运行阶段报错，提示

ORA-01422:exact fetch returns

more than requested number of rows

5.在存储过程中，关于出现null的问题

假设有一个表A，定义如下：

create table A(

id varchar2(50) primary key

not null, vcount

number(8)

not null,

bid varchar2(50) not null -- 外键

);

如果在存储过程中，使用如下语句：

select sum(vcount) into fcount from A where bid='xxxxxx';

如果A表中不存在bid="xxxxxx"的记录，则fcount=null(即使fcount定义时设置了默认值，如：fcount number(8):=0依然无效，fcount还是会变成null)，这样以后使用f

count时就可能有问题，所以在这里最好先判断一下：

if fcount is null then

fcount:=0;

end if;

这样就一切ok了。

6.Hibernate调用oracle存储过程

this.pnumberManager.getHibernateTemplate().execute(

new HibernateCallback() {

public Object doInHibernate(Session session)

throws HibernateException, SQLException {

CallableStatement cs = session

.connection()

.prepareCall("{call modifyapppnumber_remain(?)}");

cs.setString(1, foundationid);

cs.execute();

return null;

}

});

原文：http://www.cnblogs.com/happyday56/archive/2007/07/05/806830.htm

l

posted @ 2008-11-13 13:25 赖文华(SWPU小赖) 阅读(123) 评论(3) 编辑 收藏 网摘

发表评论

回复 引用 查看

#1楼 [楼主]2008-11-13 13:33 | 赖文华(SWPU小赖)

ORACLE存储过程实例

--创建存储过程

CREATE OR REPLACE PROCEDURE xxxxxxxxxxx_p

(

--参数IN表示输入参数，OUT表示输入参数，类型可以使用任意Oracle中的合法类型。

is_ym IN CHAR

)

AS

--定义变量

vs_msg VARCHAR2(4000); --错误信息变量

vs_ym_beg CHAR(6); --起始月份

vs_ym_end CHAR(6); --终止月份

vs_ym_sn_beg CHAR(6); --同期起始月份

vs_ym_sn_end CHAR(6); --同期终止月份

--定义游标(简单的说就是一个可以遍历的结果集)

CURSOR cur_1 IS

SELECT area_code,CMCODE,SUM(rmb_amt)/10000 rmb_amt_sn,SUM(usd_amt)/10000

usd_amt_sn

FROM BGD_AREA_CM_M_BASE_T

WHERE ym >= vs_ym_sn_beg

AND ym

GROUP BY area_code,CMCODE;

BEGIN

--用输入参数给变量赋初值，用到了Oralce的SUBSTR TO_CHAR ADD_MONTHS TO_DATE 等很常

用的函数。

vs_ym_beg := SUBSTR(is_ym,1,6);

vs_ym_end := SUBSTR(is_ym,7,6);

vs_ym_sn_beg := TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_beg,'yyyymm'),

-12),'yyyymm');

vs_ym_sn_end := TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_end,'yyyymm'),

-12),'yyyymm');

--先删除表中特定条件的数据。

DELETE FROM xxxxxxxxxxx_T WHERE ym = is_ym;

--然后用内置的DBMS_OUTPUT对象的put_line方法打印出影响的记录行数，其中用到一个系统变量

SQL%rowcount

DBMS_OUTPUT.put_line('del上月记录='||SQL%rowcount||'条');

INSERT INTO xxxxxxxxxxx_T(area_code,ym,CMCODE,rmb_amt,usd_amt)

SELECT area_code,is_ym,CMCODE,SUM(rmb_amt)/10000,SUM(usd_amt)/10000

FROM BGD_AREA_CM_M_BASE_T

WHERE ym >= vs_ym_beg

AND ym

GROUP BY area_code,CMCODE;

DBMS_OUTPUT.put_line('ins当月记录='||SQL%rowcount||'条');

--遍历游标处理后更新到表。遍历游标有几种方法，用for语句是其中比较直观的一种。

FOR rec IN cur_1 LOOP

UPDATE xxxxxxxxxxx_T

SET rmb_amt_sn = rec.rmb_amt_sn,usd_amt_sn = rec.usd_amt_sn

WHERE area_code = rec.area_code

AND CMCODE = rec.CMCODE

AND ym = is_ym;

END LOOP;

COMMIT;

--错误处理部分。OTHERS表示除了声明外的任意错误。SQLERRM是系统内置变量保存了当前错误的详

细信息。

EXCEPTION

WHEN OTHERS THEN

vs_msg := 'ERROR IN xxxxxxxxxxx_p('||is_ym||'):'||SUBSTR(SQLERRM,1,500);

ROLLBACK;

--把当前错误记录进日志表。

INSERT INTO LOG_INFO(proc_name,error_info,op_date)

VALUES('xxxxxxxxxxx_p',vs_msg,SYSDATE);

COMMIT;

RETURN;

END;

回复 引用 查看

#2楼 [楼主]2008-11-13 13:40 | 赖文华(SWPU小赖)

刘贤同学写的一个例子，很不错，所以就收藏一下：）

sql 代码

create or replace

PROCEDURE p_insert_t

(v_x in NUMBER)

IS

temp_id number;

BEGIN

FOR i IN 1..v_x LOOP

select SEQ_B_USER_ID.nextval into temp_id from dual;

insert into buser (id, username, nickname, status, createdtime) values(temp_id,

to_char(temp_id), to_char(temp_id), 0, sysdate);

END LOOP;

END;

--execute

创建sequence的方法（备忘）：

sql 代码

CREATE SEQUENCE SEQ_B_USER_ID INCREMENT BY 1 START WITH 1;

复制一个表的结构：

sql 代码

create table test as select * from bookmarkcategory where 1=2;

随机数：

sql 代码

select dbms_random.value(1,20) from dual;

游标的使用例子：

sql 代码

SET SERVERIUTPUT ON

DECLARE

CURSOR c_emp IS select title from bookmarkcategory where username='130';

r_emp c_emp%ROWTYPE;

BEGIN

OPEN c_emp;

-- LOOP

FETCH c_emp INTO r_emp;

--EXIT WHEN c_emp%NOTFOUND;

--DBMS_OUT.PUT.PUT_LINE('Salary of Employee is'|| r_emp.title);

--END LOOP;

update bookmarkcategory set title = r_emp.title where id=121;

CLOSE c_emp;

END;

回复 引用 查看

#3楼 [楼主]2008-11-13 16:54 | 赖文华(SWPU小赖)

---------------------------------

-- Author: liangCK 小梁

-- Date : 2008-11-13 10:07:06

---------------------------------

--> 生成测试数据: [A表]

IF OBJECT_ID('[A表]') IS NOT NULL DROP TABLE [A表]

CREATE TABLE [A表] (id INT,ProgramName VARCHAR(3))

INSERT INTO [A表]

SELECT 1,'End' UNION ALL

SELECT 2,'Chr' UNION ALL

SELECT 3,'AAA'

--> 生成测试数据: [B表]

IF OBJECT_ID('[B表]') IS NOT NULL DROP TABLE [B表]

CREATE TABLE [B表] (id INT,Person VARCHAR(5))

INSERT INTO [B表]

SELECT 1,'Ken' UNION ALL

SELECT 1,'Anne' UNION ALL

SELECT 2,'blue' UNION ALL

SELECT 3,'Wayne'

--SQL查询如下:

GO

CREATE FUNCTION dbo.fn_Persons(@id INT)

RETURNS VARCHAR(50)

AS

BEGIN

DECLARE @re VARCHAR(50);

SET @re='';

SELECT @re=@re+'
'+Person

FROM [B表]

WHERE id=@id;

RETURN STUFF(@re,1,4,'');

END

GO

SELECT id,ProgramName,dbo.fn_Persons(id) AS Persons

FROM [A表]

GO

DROP TABLE [A表],[B表]

DROP FUNCTION dbo.fn_Persons

/*

id ProgramName Persons

----------- ----------- --------------------------------------------------

1 End Ken
Anne

2 Chr blue

3 AAA Wayne

(3 行受影响)