oracle 存储过程 （ 例子+ 随笔）

最近写关于oracle 存储过程的代码，以前没有接触过，就把这几天写的代码贴出来：

 

使用工具PL/SQL Developer

 

1.创建

在已有的数据库中写的代码，

 

在Procedures文件夹中右键-->new

 

输入存储过程的名字，一般以P开头（代码习惯而已），然后输入参数，当然参数在文件里面写也可以；

 

2.编写sql语句

代码如下：//这段代码是可以运行的，已经测过

 没有参数的：

/*
author by zhanghw
200906
*/
CREATE OR REPLACE PROCEDURE P_temp_t1  AS
 	city_name varchar2(50);
 	dbname varchar2(20);
 	sqlstr1 varchar2(5000);
 	sqlstr2 varchar2(5000);
 	--sqlstr3 varchar2(5000);
 	p_starttime varchar2(30);--月开始时间
 	p_endtime varchar2(30);--月结束时间
 	mon varchar2(30);
 	t_type varchar(1);    	
 	xxbig varchar2(100);
 	xxdl_name varchar2(400);
 	xxxl_name varchar2(120);
 	total number(10) ;  	
 	
 	TYPE t_cursor IS REF CURSOR;  --游标
  	cur_city t_cursor;
  	cur_count t_cursor;
 	 	    
BEGIN
	mon :=to_char(add_months(to_date(to_char(sysdate,'yyyyMM'),'yyyyMM'),-1),'yyyyMM');--获取要查询的月
 	p_endtime :=to_char(last_day(to_date(to_char(sysdate,'yyyyMM'),'yyyyMM')),'yyyyMMDD')||'23:59:59';--月的最后时间
 	p_starttime :=mon||'01 00:00:00';--月的最后时间  
 	  	
 	/*获取城市及db名称*/
 	sqlstr1 :='SELECT city, dbname FROM re_complain_ini ORDER BY id' ;
 	
 	OPEN cur_city FOR sqlstr1 ; --循环城市，最外层循环
	LOOP
	FETCH cur_city INTO city_name , dbname ;
 	EXIT WHEN cur_city%NOTFOUND ;       
    BEGIN
 		
 		FOR num IN 0..3  LOOP --循环类型 0咨询 1 投诉 2 ……3 ……      				
 	
 				t_type := to_char(num);				
 				sqlstr2 := 'select b.xxbig,b.name,c.name,count(1) from complain_info2@' || dbname || ' a,km_situation_list@' || dbname ||' b,km_minitype_small@' || dbname ||' c where a.accept_date between to_date(' || '''' ||p_starttime || '''' || ',' || '''' || 'yyyy-MM-DD HH24:MI:SS' || '''' || ') and to_date(' || '''' || p_endtime || '''' || ',' || '''' ||'yyyy-MM-DD HH24:MI:SS' || '''' || ') and a.situation=b.id and a.sub_situation=c.id  and a.task_type='||t_type|| 'group by b.xxbig,b.name,c.name';
 			--	DBMS_OUTPUT.put_line(sqlstr2);				
 			OPEN cur_count FOR sqlstr2 ;--遍历查询的结果
 			LOOP
 			FETCH cur_count INTO xxbig,xxdl_name,xxxl_name,total ;
 			EXIT WHEN cur_count%NOTFOUND ;
 			BEGIN 
 				/*将上面查询的结果存入COMPLAIN_INFO_TEMP表中*/				
 			  insert into INFO_TEMP values(xxbig,xxdl_name,xxxl_name,total,city_name,t_type,mon,'','',seq_id.nextval);
 			  COMMIT ;
 			END ;			
 			END LOOP ;
 			CLOSE cur_count ;
 				
 		END LOOP;    
 	END ;
	END LOOP ; 
	COMMIT ; 	
	CLOSE  cur_city ;	
	
END P_temp_t1;

 

 

 有输入参数有返回值的：

 

create or replace procedure p_info
(
 starttime in  varchar
 endtime  in varchar,--  end time
 p_city    in varchar,-- city  ‘’所有
 type       in varchar   
 result out varchar-- result;
)
IS
city    varchar2(20);
c_city  varchar2(20);
end_time_key        varchar2(50);
ERR_MSG             varchar2(30);
amt                 number;
l_count             number;
dbname varchar2(50);
start_time      varchar2(50);
end_time        varchar2(50);
t_str            varchar2(3000);
sqlstr1          varchar2(3000);
sqlstr              varchar2(3000);
v_count             number;

……

 

3.编译或者叫做compile

 

点击左上角“Execute” 或者“F8”

 

在下方会告诉你语法是否有问题

 

4.测试

在存储过程的文件名上右击-->test

 

会自动生成测试文件，而且还带有debug功能

 

代码如下：

 

begin
  -- Call the procedure
  p_complain_info_temp_t1;
end;

 

 

附：

 

对于在存储过程中使用sql语句：

1. 直接书写

insert into INFO_TEMP values(xxbig,xxdl_name,xxxl_name,total,city_name,t_type,mon,'','',seq_id.nextval);
  COMMIT ;

 

2. 使用变量

sqlstr1 :='SELECT city, dbname FROM re_complain_ini ORDER BY id' ;
execute immediate sqlstr1;

 

调用函数给变量赋值：

1.直接使用

mon :=to_char(add_months(to_date(to_char(sysdate,'yyyyMM'),'yyyyMM'),-1),'yyyyMM');--获取要查询的月
 	p_endtime :=to_char(last_day(to_date(to_char(sysdate,'yyyyMM'),'yyyyMM')),'yyyyMMDD')||'23:59:59';--月的最后时间
 	p_starttime :=mon||'01 00:00:00';--月的最后时间  

 

说明： a.变量要在前面声明

           b. to_char()将其他类型强制转换为varchar2型，并可以为时间设置格式，to_date()将字符解析为时间类型，输入字符的长度要和格式长度一样，否则报错，last_day()获取上个月的最后一天，可以带有时间的

          c.sysdate 是获取当前系统时间

 

2.使用select

select to_char(SYSDATE-1/24*30/60,'yyyymmddhh24mi')    into STAND_TIMEKEY    from dual;

 说明：

       into 是关键字 赋值 ， from dual 也不能省略

 

注意：

1 赋值和其他操作 要放到begin中去做

2 begin中不能有declare

3 begin end ， loop end loop ，if end if 一定要相对应

4 '' 两个单引号表示一个 单引号，

|| 是字符连接

 

关于遍历结果集的问题

 

可以使用游标

1.一种这样使用

 

sqlstr1 varchar2(5000);

TYPE t_cursor IS REF CURSOR;  --游标
  	cur_city t_cursor;
  	cur_count t_cursor;

BEGIN


/*获取城市及db名称*/
 	sqlstr1 :='SELECT city, dbname FROM re_complain_ini ORDER BY id' ;
 	
 	OPEN cur_city FOR sqlstr1 ; --打开游标

LOOP
	FETCH cur_city INTO city_name , dbname ; 
 	EXIT WHEN cur_city%NOTFOUND ;       --退出条件
    BEGIN
           NULL;

   END;

END LOOP;

END ;

 

2.另一种 类似如下

来自http://blog.csdn.net/ronk/archive/2006/11/08/1374272.aspx的代码：

 

BEGIN 
             
     DECLARE 
             
            val INTEGER; --保存"上一次游标取出的userid"
            cur INTEGER; --保存"当前游标取出的userid"   

            CURSOR nextRecodeRow IS 
            SELECT * FROM v_process_attr3 WHERE dm=dm_input ORDER BY dm,userid,process_num;
            
--用%ROWTYPE属性声名的记录变量自动拥有对应于所引用镖的字段的字段名
            RecodeRow_v_process_attr v_process_attr3%ROWTYPE;

            BEGIN    
                 val := 0;
                 
                 DELETE FROM attr_list;
                 COMMIT;
                 
                 FOR RecodeRow_v_process_attr IN nextRecodeRow LOOP
                       cur := RecodeRow_v_process_attr.userid;
                       CASE val
                            WHEN cur THEN
                                BEGIN
                                     val := cur;
                                     UPDATE attr_list 
                                            SET 
                                                attr_list.attr_list = attr_list.attr_list || ',' || RecodeRow_v_process_attr.attribute_code || '=' ||RecodeRow_v_process_attr.attribute_value
                                            WHERE
                                                attr_list.userid = val 
                                                and 
                                                RecodeRow_v_process_attr.dm=dm_input;
                                     COMMIT;
                                END;
                            ELSE
                                BEGIN
                                     val := cur;

                                     INSERT INTO attr_list 
                                            VALUES 
                                            ( dm_input, 
                                            RecodeRow_v_process_attr.userid , 
                                            RecodeRow_v_process_attr.process_num,
                                            RecodeRow_v_process_attr.attribute_code || '=' || RecodeRow_v_process_attr.attribute_value, 
                                            RecodeRow_v_process_attr.timeout );
                                     COMMIT;
                                END;
                       END CASE;

                 END LOOP;
   
            END;



本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/ronk/archive/2006/11/08/1374272.aspx