`
liubin2010
  • 浏览: 307833 次
  • 性别: Icon_minigender_1
  • 来自: 西安
社区版块
存档分类
最新评论

oracle 存储过程的基本语法

阅读更多

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

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 - 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  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 ( 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),这样以后使用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(
1 , foundationid);
                        cs.execute();
                        
return   null ;
                    }

                }
);

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

posted @ 2008-11-13 13:25 赖文华(SWPU小赖)  阅读(123) 评论(3)   编辑  收藏  网摘
oracle 存储过程的基本语法  - 张睿 - 博客

发表评论
  回复   引用   查看     
#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+'<br>'+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<br>Anne 
2 Chr blue 
3 AAA Wayne 

(3 行受影响) 

*/

分享到:
评论

相关推荐

    Oracle存储过程基本语法

    Oracle 存储过程基本语法 Oracle 存储过程是一种可以在 Oracle 数据库中创建和执行的程序单元,它可以完成多种操作,如数据处理、数据报表、数据统计等。下面是 Oracle 存储过程的基本语法。 创建存储过程 CREATE...

    Oracle存储过程基本语法.docx

    Oracle存储过程基本语法 Oracle存储过程基本语法是指在Oracle数据库中创建和管理存储过程的基本语法规则和结构。存储过程是数据库中的一种程序单元,可以执行特定的数据库操作和逻辑处理任务。 CREATE OR REPLACE ...

    oracle存储过程基本语法

    ### Oracle存储过程基本语法详解 #### 一、存储过程简介 在Oracle数据库中,存储过程是一种重要的编程单元,它可以被创建并保存在数据库内。存储过程由一系列SQL语句及可选的PL/SQL控制流语句组成,用于执行特定的...

    Oracle存储过程基本语法及示例

    ### Oracle存储过程基本语法及示例 在Oracle数据库中,存储过程是一种强大的工具,用于封装一组SQL语句或PL/SQL代码块,以便在数据库服务器上执行特定的任务。存储过程可以提高应用程序性能、确保数据完整性并简化...

    oracle存储过程基本语法.txt

    根据提供的文件信息,我们可以深入探讨Oracle存储过程的基本语法与关键概念。存储过程是数据库中预编译的一组SQL语句及过程化结构的集合体,它可以在数据库服务器上执行,以此来提高性能并减少网络流量。下面将详细...

    关于oracle存储过程的基本语法

    ### Oracle存储过程基础语法详解及注意事项 #### 一、Oracle存储过程概述 Oracle存储过程是一种在Oracle数据库中存储的一段可执行的SQL代码或PL/SQL代码,它可以帮助开发人员实现复杂的业务逻辑处理,提高应用程序...

    Oracle存储过程的基本语法

    以下是Oracle存储过程中涉及的一些基本语法元素的详细解释: 1. **定义存储过程**: 使用`CREATE OR REPLACE PROCEDURE`语句来创建或替换一个存储过程。例如: ```sql CREATE OR REPLACE PROCEDURE proc_name ( ...

    Oracle存储过程基本语法格式

    本文将详细解释Oracle存储过程的基本语法格式及其主要组成部分。 首先,创建存储过程的基本结构如下: ```sql CREATE OR REPLACE PROCEDURE 存储过程名字( 参数 1 IN NUMBER, -- 输入参数,类型为NUMBER 参数 2 ...

    (完整word版)Oracle存储过程基本语法.doc

    以下是对Oracle存储过程基本语法的详细说明: 1. **创建存储过程**: 创建存储过程使用`CREATE OR REPLACE PROCEDURE`语句。例如: ```sql CREATE OR REPLACE PROCEDURE 存储过程名 IS BEGIN NULL; END; ``...

    oracle存储过程语法

    Oracle存储过程语法 Oracle存储过程语法是指在Oracle数据库中创建和管理存储过程的语法规则。存储过程是一种可以重复使用的数据库对象,旨在将多个SQL语句组合成一个单元,以便实现复杂的业务逻辑。 创建存储过程...

    oracle存储过程学习经典入门

    Oracle 存储过程学习目录是 Oracle 存储过程学习的基础知识,了解 Oracle 存储过程的基本语法、基础知识和一些常见问题的解决方法是非常重要的。本文将从 Oracle 存储过程的基础知识开始,逐步深入到 Oracle 存储...

    ORACLE创建过程的语法

    ORACLE创建过程的语法

    Oracle_存储过程的基本语法

    ### Oracle存储过程的基本语法 #### 3.1.1 基本结构 在Oracle数据库中,存储过程是一种可重复使用的数据库对象,用于封装一系列SQL命令或其他PL/SQL语句,以便于执行复杂的业务逻辑。存储过程的基本结构如下: ``...

    oracle 存储过程的基本语法.doc

    以下是对Oracle存储过程基本语法的详细解释: 1. **创建存储过程**: `CREATE OR REPLACE PROCEDURE 存储过程名字(参数列表) IS/AS` 这是创建存储过程的基本结构。`CREATE OR REPLACE`允许你更新已存在的存储过程...

Global site tag (gtag.js) - Google Analytics