pl/sql中存储过程

Question

在SQLSERVER中存储过程没问题,初次接触ORCAL,不清楚如何在PL/SQL中创建,修改,执行存储过程,请高人指点

Answer 1

 create or replace procedure P_GUOCHENG(名称 in out 类型, 名称 in out 类型, ...) is
   begin
     
   end P_GUOCHENG;
   过程创建。 执行 可以在命令窗口。EXEC 过程名。

Answer 2

大家看下,这段代码是C++写的,想改成ORCAL下的存储过程,怎么优化一下,更简洁maxrow=0;
                         EXEC SQL SELECT count(*) into :maxrow
                                               FROM (select DISTINCT TRAN_SLA_KEY,
                                                    TX_CODE,          
                                                            cardno,          
                                                            valid_date,      
                                                            sett_postdate,  
                                                            substr(TRAN_SLA_KEY,9,8), 
                                                            input_mode,      
                                                            merch_id,             
                                                            REQ_TXNAMT,          
                                                            integral,        
                                                            trace_no,        
                                                            sys_no,          
                                                            orig_traceno,    
                                                            rsp_code,        
                                                            compl_flag,
                                                            TRANDATE,
                                                            substr(TRAN_SLA_KEY,17,5),
                                                            ADDIDATA1,
                                                            MER_ORDERNO,
                                                            REJ_CODE
                                                      from translog a where 
                                                         ((addidata1 like '%'||:PerName||'%') or (:PerName = 'Z'))
                                                     and ((addidata1 like '%'||:PerTel||'%') or (:PerTel = 'Z'))
                                                     and ((addidata1 like '%'||:PerDate||'%') or (:PerDate = 'Z'))
                                                     and ((mer_orderno = :XMLpkgDATABuf.cardholder) or (:XMLpkgDATABuf.cardholder = 'Z'))
                                              /****  ((valid_date  = :XMLpkgDATABuf.valid_date) or (:XMLpkgDATABuf.valid_date = 'Z')) delete by GuWei 20110704 *****/
                                                     and ((EXISTS(select * from merchant where a.MERCH_ID=MERCH_ID and  EXISTS (select * from grppou where substr(merchant.GRP_NAME,1,8)=substr(MERGRP,1,8) and GRP_LEADER  = :XMLpkgDATABuf.merch_id)) or (merch_id  = :XMLpkgDATABuf.merch_id))
                                                          or (:XMLpkgDATABuf.merch_id = 'Z'))
                                                     and ((REQ_TXNAMT   = :XMLpkgDATABuf.amount) or (:XMLpkgDATABuf.amount = 1000000000))
                                                     and ((term_id  = :XMLpkgDATABuf.set_term) or (:XMLpkgDATABuf.set_term = 'Z'))
                                                     and ((trace_no  = :XMLpkgDATABuf.trace_no) or (:XMLpkgDATABuf.trace_no = 'Z'))
                                                     and ((orig_traceno  = :XMLpkgDATABuf.orig_traceno) or (:XMLpkgDATABuf.orig_traceno = 'Z'))
                                          /***  and ((sys_no  = :XMLpkgDATABuf.sys_no) or (:XMLpkgDATABuf.sys_no = 'Z')) delete by GuWei 20110704 ***/
                                                     and ((TRANDATE  = :tmp_makedate) or (:tmp_makedate = 'Z'))
                                                     and ((substr(TRAN_SLA_KEY,17,5)  = :XMLpkgDATABuf.instcode) or (:XMLpkgDATABuf.instcode = 'Z'))
                                                     and ((substr(TRAN_SLA_KEY,9,8)  = :XMLpkgDATABuf.term_id) or (:XMLpkgDATABuf.term_id = 'Z'))
                   and ((BATCHID  = :XMLpkgDATABuf.batchid) or (:XMLpkgDATABuf.batchid = 'Z'))
                                             and (((substr(cardno,5,5) = :XMLpkgDATABuf.MerOnCode) or (:XMLpkgDATABuf.MerOnCode = 'Z')) or ((substr(addidata1,5,5) = :XMLpkgDATABuf.MerOnCode) or (:XMLpkgDATABuf.MerOnCode = 'Z')))
                                                     and ((merch_id = :XMLpkgDATABuf.Rev_Id) or (:XMLpkgDATABuf.Rev_Id = 'Z'))
                                                     and ((((SUBSTR(cardno,1,18) >= :XMLpkgDATABuf.startno) or (:XMLpkgDATABuf.startno = 'Z'))
                   and ((SUBSTR(cardno,1,18) <= :XMLpkgDATABuf.endno) or (:XMLpkgDATABuf.endno = 'Z')))
                   or  (((SUBSTR(ADDIDATA1,1,18) <= :XMLpkgDATABuf.startno) and (SUBSTR(ADDIDATA1,20,18) >= :XMLpkgDATABuf.startno) or (:XMLpkgDATABuf.startno = 'Z'))
                   and ((SUBSTR(ADDIDATA1,1,18) <= :XMLpkgDATABuf.endno) and (SUBSTR(ADDIDATA1,20,18) >= :XMLpkgDATABuf.endno) or (:XMLpkgDATABuf.endno = 'Z'))))
                                                     and ((tx_code  = :XMLpkgDATABuf.TxCode) or (:XMLpkgDATABuf.TxCode = 'Z'))
                                                     and ((sett_postdate  >= to_char(:XMLpkgDATABuf.sett_postdate)))
                                                     and ((sett_postdate  <= to_char(:XMLpkgDATABuf.tran_dt)))
                                                                                                          

Answer 3

union 
                                                     select DISTINCT TRAN_SLA_KEY,
                                                    TX_CODE,          
                                                            cardno,          
                                                            valid_date,      
                                                            sett_postdate,   
                                                            substr(TRAN_SLA_KEY,9,8),
                                                            input_mode,      
                                                            merch_id,             
                                                            REQ_TXNAMT,          
                                                            integral,        
                                                            trace_no,        
                                                            sys_no,          
                                                            orig_traceno,    
                                                            rsp_code,        
                                                            compl_flag,
                                                            TRANDATE,
                                                            substr(TRAN_SLA_KEY,17,5),
                                                            ADDIDATA1,
                                                            MER_ORDERNO,
                                                            REJ_CODE
                                                      from partranslog a where 
                                                         ((addidata1 like '%'||:PerName||'%') or (:PerName = 'Z'))
                                                     and ((addidata1 like '%'||:PerTel||'%') or (:PerTel = 'Z'))
                                                     and ((addidata1 like '%'||:PerDate||'%') or (:PerDate = 'Z'))
                                                     and ((mer_orderno = :XMLpkgDATABuf.cardholder) or (:XMLpkgDATABuf.cardholder = 'Z'))
                                              /****  ((valid_date  = :XMLpkgDATABuf.valid_date) or (:XMLpkgDATABuf.valid_date = 'Z')) delete by GuWei 20110704 *****/
                                                     and ((EXISTS(select * from merchant where a.MERCH_ID=MERCH_ID and  EXISTS (select * from grppou where substr(merchant.GRP_NAME,1,8)=substr(MERGRP,1,8) and GRP_LEADER  = :XMLpkgDATABuf.merch_id)) or (merch_id  = :XMLpkgDATABuf.merch_id))
                                                          or (:XMLpkgDATABuf.merch_id = 'Z'))
                                                     and ((REQ_TXNAMT   = :XMLpkgDATABuf.amount) or (:XMLpkgDATABuf.amount = 1000000000))
                                                     and ((term_id  = :XMLpkgDATABuf.set_term) or (:XMLpkgDATABuf.set_term = 'Z'))
                                                     and ((trace_no  = :XMLpkgDATABuf.trace_no) or (:XMLpkgDATABuf.trace_no = 'Z'))
                                                     and ((orig_traceno  = :XMLpkgDATABuf.orig_traceno) or (:XMLpkgDATABuf.orig_traceno = 'Z'))
                                          /***  and ((sys_no  = :XMLpkgDATABuf.sys_no) or (:XMLpkgDATABuf.sys_no = 'Z')) delete by GuWei 20110704 ***/
                                                     and ((TRANDATE  = :tmp_makedate) or (:tmp_makedate = 'Z'))
                                                     and ((substr(TRAN_SLA_KEY,17,5)  = :XMLpkgDATABuf.instcode) or (:XMLpkgDATABuf.instcode = 'Z'))
                                                     and ((substr(TRAN_SLA_KEY,9,8)  = :XMLpkgDATABuf.term_id) or (:XMLpkgDATABuf.term_id = 'Z'))
                   and ((BATCHID  = :XMLpkgDATABuf.batchid) or (:XMLpkgDATABuf.batchid = 'Z'))
                                             and (((substr(cardno,5,5) = :XMLpkgDATABuf.MerOnCode) or (:XMLpkgDATABuf.MerOnCode = 'Z')) or ((substr(addidata1,5,5) = :XMLpkgDATABuf.MerOnCode) or (:XMLpkgDATABuf.MerOnCode = 'Z')))
                                                     and ((merch_id = :XMLpkgDATABuf.Rev_Id) or (:XMLpkgDATABuf.Rev_Id = 'Z'))
                                                     and ((((SUBSTR(cardno,1,18) >= :XMLpkgDATABuf.startno) or (:XMLpkgDATABuf.startno = 'Z'))
                   and ((SUBSTR(cardno,1,18) <= :XMLpkgDATABuf.endno) or (:XMLpkgDATABuf.endno = 'Z')))
                   or  (((SUBSTR(ADDIDATA1,1,18) <= :XMLpkgDATABuf.startno) and (SUBSTR(ADDIDATA1,20,18) >= :XMLpkgDATABuf.startno) or (:XMLpkgDATABuf.startno = 'Z'))
                   and ((SUBSTR(ADDIDATA1,1,18) <= :XMLpkgDATABuf.endno) and (SUBSTR(ADDIDATA1,20,18) >= :XMLpkgDATABuf.endno) or (:XMLpkgDATABuf.endno = 'Z'))))
                                                     and ((tx_code  = :XMLpkgDATABuf.TxCode) or (:XMLpkgDATABuf.TxCode = 'Z'))
                                                     and ((sett_postdate  >= to_char(:XMLpkgDATABuf.sett_postdate)))
                                                     and ((sett_postdate  <= to_char(:XMLpkgDATABuf.tran_dt)))
                                                     

Answer 4

CREATE OR REPLACE PROCDURE PROC_NAME
(
  参数(入in、出out)
)
AS(OR IS)
参数
BEGIN
   --为参数赋值
   参数：= z值;
   --SQL
   SQL语句;
END;(PROC_NAME)

Answer 5

对了 我也是oracle新手 我用的是oracle11G + PLSQL

Answer 6

CREATE OR REPLACE PROCEDURE P_NAME ( 传入参数或输出参数) is or as
begin
null;--->>>执行程序
end;

Answer 7

CREATE OR REPLACE PROCEDURE P_NAME ( 传入参数或输出参数) is or as
begin
null;--->>>执行程序
end;

Answer 8

以下存储过程,编译错,为什么create or replace procedure te(x in number) is
begin
   if x>0 then
     begin
       x:=0-x;
     end;
   end if;
   if x=0 then
     begin
       x:=1;
     end;
   end if;
dbms_output.put_line(x);
end te;
/

Answer 9

oracle存储过程输入参数是只读的，不能对其进行赋值

Answer 10

存储过程的问题解决了,还有新的问题,输入参数有可能全部为空,也可能全部不为空,这个在拼SQL语句时怎么拼,USING怎么写CREATE OR REPLACE PROCEDURE "CARD"."TRANS"
(
mcht in varchar,
sett in varchar,
startcardno in varchar,
endcardno in varchar,
tx_code in varchar,
banlace in varchar,
startdate in varchar,
enddate in varchar,
traceno in varchar,
batchno in varchar,
termid in varchar,
document in varchar,
cardhold in varchar,
lxname in varchar,
telphone in varchar,
gkdate in varchar,
pagesize in number,
currpage in number,
recordcount out number
)
as
strsql varchar(1000);
begin
  recordcount:=888;
  strsql:='select count(*) from partranslog where merch_id=:mcht and sett_postdate>=to_char(:startdate) and sett_postdate<=to_char(:enddate)';
  if length(startcardno)>0 and length(endcardno)>0 then
    strsql:=strsql||' and cardno>=:startcartno and cardno<=:endcardno';
    execute immediate strsql into recordcount using mcht,startdate,enddate,startcardno,endcardno,tx_code,banlace;
  else
    execute immediate strsql into recordcount using mcht,startdate,enddate;
  end if;
  update proce set name=recordcount;
end trans;
/

Answer 11

为什么已经有了存储过程还用java和c++等来实现存储过程呢

Answer 12

可能是我没说明白, 现在这个存储过程是没问题, 我这里传入的参数是18个,在拼SQL语句时mcht(商户号),startdate(起始日期),enddate(结束日期)三个是必选项,其余都是可选项,那我在拼SQL语句时该怎样拼呢,如果是sqlserver直接拼好连接在where后面就可以了,pl/sql除了where以外,还有using,能不能也动态往using后面加变量呢,不知这样说明白没有.或者有什么更简单的方法拼出来,查询界面如图所示.另外3楼那个是以前用C++写的,效率太低了,现在正在改成sql/sql存储过程.

Answer 13

create or replace procedure te(x in number) is
begin
   if x>0 then
     begin
       x:=0-x;
     end;
   end if;
   if x=0 then
     begin
       x:=1;
     end;
   end if;
   end;
dbms_output.put_line(x);
end te;
/

Answer 14

在存储过程里判断参数，拼接sql。

Answer 15

你这sql看着就效率低下的，明显是为了完成任务而写的！
优化优化吧！