ITPub博客

首页 > 数据库 > Oracle > 【转】Oracle存储过程、游标语法

【转】Oracle存储过程、游标语法

Oracle 作者:hkogb 时间:2011-10-17 17:53:34 0 删除 编辑

1.基本结构 
CREATE OR REPLACE PROCEDURE 存储过程名字
(
    参数1 IN NUMBER,
    参数2 IN NUMBER
) {IS|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单步调试

 

关于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 and af.foundationid=fid;-- 有into,正确编译
  select af.keynode from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;-- 没有into,编译报错,提示:Compilation 
  Error: PLS-00428an 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 foundationid=fid;-- 正确运行
select af.keynode into kn from APPFOUNDATION af where af.appid=appid and af.foundationid=foundationid;-- 运行阶段报错,提示
ORA-01422:exact fetch returns more than requested number of rows
5.在存储过程中,关于出现null的问题
假设有一个表A,定义如下:
create table A(
id 
varchar2(50primary key not null,
vcount 
number(8not null,
bid 
varchar2(50not null -- 外键 
);
如果在存储过程中,使用如下语句:
select sum(vcount) into fcount from where bid='xxxxxx';
如果A表中不存在bid="xxxxxx"的记录,则fcount=null(即使fcount定义时设置了默认值,如:fcount number(8):=0依然无效,fcount还是会变成null),这样以后使用fcount时就可能有问题,所以在这里最好先判断一下:
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(
1foundationid);
                        cs.execute();
                        
return null;
                    }

                }
);

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

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 <= vs_ym_sn_end 
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 <= vs_ym_end 
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 

 
<!-- 正文结束 -->

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23824846/viewspace-1116965/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2010-04-30