在oracle plsql中创建存储过程 [英] create stored procedure in oracle plsql

问题描述

大家好，

我想在oracle中创建存储过程.

在那，我想更新表中是否存在记录，否则想在该表中插入该记录.

这是我写的存储过程.

我对oracle的了解很少(说实话，没有知识).


下面是我的代码，该代码的目的是根据用户在cognos report studio报表视图中所做的输入来插入/更新数据.

Hi all,

I want to create stored procedure in oracle.

In that,I want to update if record is present in the table else want to insert that record in that table.

Here is my stored proc which I write.

I have very little knowledge about oracle(to be honest no knowledge).


Below is my code and the purpose of this code is to insert/update the data based on the entry what ever the user makes in cognos report studio report view.

create or replace
PROCEDURE INSERTCOMMENTS 
(
  N_HATID NUMBER  
, N_IN_NUMBER NUMBER  
, N_POINTS VARCHAR2 DEFAULT 255 
, N_QETYPE VARCHAR2 DEFAULT 255 
) AS 
BEGIN

  IF((SELECT COUNT(*) FROM CCM_REPORT_USER.POINTS_QETYPE_COMMENTS 
    WHERE CCM_REPORT_USER.POINTS_QETYPE_COMMENTS.HATID = N_HATID)=0)
    THEN (INSERT INTO "CCM_REPORT_USER"."POINTS_QETYPE_COMMENTS" (HATID, IN_NUMBER, POINTS, QETYPE) 
    VALUES (N_HATID , N_IN_NUMBER , N_POINTS , N_QETYPE,));
    ELSE
    (UPDATE "CCM_REPORT_USER"."POINTS_QETYPE_COMMENTS" SET CCM_REPORT_USER.POINTS_QETYPE_COMMENTS.POINTS = N_POINTS 
    AND     CCM_REPORT_USER.POINTS_QETYPE_COMMENTS.QETYPE = N_QETYPE
    WHERE CCM_REPORT_USER.POINTS_QETYPE_COMMENTS.HATID = N_HATID) 
    AND     WHERE CCM_REPORT_USER.POINTS_QETYPE_COMMENTS.IN_NUMBER = N_IN_NUMBER)
    END IF;
 
END INSERTCOMMENTS;

非常感谢Advance

Thanks a lot in Advance

推荐答案

在这里，尝试一下，看看是否可行(我现在无处可测试Oracle存储的proc，所以我有点盲目.但是，大多数语法错误都应该得到纠正)

Here, try this one, see if it works (I have nowhere to test an Oracle stored proc right now, so I''m kinda blind. Still, most of the syntax errors should''ve been corrected)

create or replace PROCEDURE INSERTCOMMENTS (
    N_HATID IN NUMBER, 
    N_IN_NUMBER IN NUMBER,
    N_POINTS IN VARCHAR2 DEFAULT 255, 
    N_QETYPE IN VARCHAR2 DEFAULT 255)
IS
    count_comments NUMBER := 0;
BEGIN 
  SELECT COUNT(*) INTO count_comments
      FROM CCM_REPORT_USER.POINTS_QETYPE_COMMENTS 
      WHERE CCM_REPORT_USER.POINTS_QETYPE_COMMENTS.HATID = N_HATID;
  IF(count_comments = 0) THEN 
    INSERT INTO CCM_REPORT_USER.POINTS_QETYPE_COMMENTS (HATID, IN_NUMBER, POINTS, QETYPE)
      VALUES (N_HATID , N_IN_NUMBER , N_POINTS , N_QETYPE);
  ELSE 
    UPDATE CCM_REPORT_USER.POINTS_QETYPE_COMMENTS
      SET CCM_REPORT_USER.POINTS_QETYPE_COMMENTS.POINTS = N_POINTS,
        CCM_REPORT_USER.POINTS_QETYPE_COMMENTS.QETYPE = N_QETYPE
      WHERE CCM_REPORT_USER.POINTS_QETYPE_COMMENTS.HATID = N_HATID
        AND CCM_REPORT_USER.POINTS_QETYPE_COMMENTS.IN_NUMBER = N_IN_NUMBER;
  END IF; 
END;

嗨Straut
下面的代码被编译，但是当我尝试执行代码时，出现错误

Hi Straut
The below code gets Complied but when i try to execute the code it is gives me the Error saying

An Error was encountered performing the requested operation:

ORA-06550:line 10, column 3:
PLS-00306:wrong number or types of arguments in call to 'INSERTCOMMENTS_NEW'
ORA-06550:line 10, Column 3:
PL/SQL:Statement ignored
ORA-06512:at line 58

Vender Code 6550

创建或替换
PROCEDURE INSERTCOMMENTS_NEW(
N_HATID IN NUMBER，
N_POINTS IN VARCHAR2 DEFAULT 255，
N_QETYPE IN VARCHAR2默认值255，
p_cursor out SYS_REFCURSOR)
IS
COUNT_COMMENTS NUMBER:= 0；


开始
选择COUNT(*)INTO count_comments
来自CCM_REPORT_USER.POINTS_QETYPE_COMMENTS
在哪里CCM_REPORT_USER.POINTS_QETYPE_COMMENTS.HATID = N_HATID;
IF(count_comments = 0)然后
插入CCM_REPORT_USER.POINTS_QETYPE_COMMENTS(HATID，POINTS，QETYPE)
值(N_HATID，N_POINTS，N_QETYPE);
ELSE
更新CCM_REPORT_USER.POINTS_QETYPE_COMMENTS
SET CCM_REPORT_USER.POINTS_QETYPE_COMMENTS.POINTS = N_POINTS，
CCM_REPORT_USER.POINTS_QETYPE_COMMENTS.QETYPE = N_QETYPE
在哪里CCM_REPORT_USER.POINTS_QETYPE_COMMENTS.HATID = N_HATID;

万一;
从POINTS_QETYPE_COMMENTS打开SELECT HATID的p_cursor;
END;

请在此先感谢我

create or replace
PROCEDURE INSERTCOMMENTS_NEW (
N_HATID IN NUMBER,
N_POINTS IN VARCHAR2 DEFAULT 255,
N_QETYPE IN VARCHAR2 DEFAULT 255,
p_cursor out SYS_REFCURSOR)
IS
COUNT_COMMENTS NUMBER := 0;


BEGIN
SELECT COUNT(*) INTO count_comments
FROM CCM_REPORT_USER.POINTS_QETYPE_COMMENTS
WHERE CCM_REPORT_USER.POINTS_QETYPE_COMMENTS.HATID = N_HATID;
IF(count_comments = 0) THEN
INSERT INTO CCM_REPORT_USER.POINTS_QETYPE_COMMENTS (HATID, POINTS, QETYPE)
VALUES (N_HATID , N_POINTS , N_QETYPE);
ELSE
UPDATE CCM_REPORT_USER.POINTS_QETYPE_COMMENTS
SET CCM_REPORT_USER.POINTS_QETYPE_COMMENTS.POINTS = N_POINTS,
CCM_REPORT_USER.POINTS_QETYPE_COMMENTS.QETYPE = N_QETYPE
WHERE CCM_REPORT_USER.POINTS_QETYPE_COMMENTS.HATID = N_HATID;

END IF;
open p_cursor for SELECT HATID FROM POINTS_QETYPE_COMMENTS ;
END;

Kindly suggest me on this thanks a lot in advance

这篇关于在oracle plsql中创建存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！