17,377
社区成员
发帖
与我相关
我的任务
分享pl/sql中存储过程
在SQLSERVER中存储过程没问题,初次接触ORCAL,不清楚如何在PL/SQL中创建,修改,执行存储过程,请高人指点
...全文
请发表友善的回复…
发表回复
lixiaoyu0707 2012-04-06
- 打赏
- 举报
都是高手，学习。。。
啊彪123 2012-04-06
- 打赏
- 举报
[Quote=引用 13 楼 的回复:]
引用 11 楼 的回复:
引用 10 楼 的回复:
存储过程的问题解决了,还有新的问题,输入参数有可能全部为空,也可能全部不为空,这个在拼SQL语句时怎么拼,USING怎么写
SQL code
CREATE OR REPLACE PROCEDURE "CARD"."TRANS"
(
mcht in varchar,
sett in varchar,
startcardno ……
[/Quote]
你这sql看着就效率低下的，明显是为了完成任务而写的！
优化优化吧！
引用 11 楼 的回复:
引用 10 楼 的回复:
存储过程的问题解决了,还有新的问题,输入参数有可能全部为空,也可能全部不为空,这个在拼SQL语句时怎么拼,USING怎么写
SQL code
CREATE OR REPLACE PROCEDURE "CARD"."TRANS"
(
mcht in varchar,
sett in varchar,
startcardno ……
[/Quote]
你这sql看着就效率低下的，明显是为了完成任务而写的！
优化优化吧！
啊彪123 2012-04-06
- 打赏
- 举报
[Quote=引用 10 楼 的回复:]
存储过程的问题解决了,还有新的问题,输入参数有可能全部为空,也可能全部不为空,这个在拼SQL语句时怎么拼,USING怎么写
SQL code
CREATE OR REPLACE PROCEDURE "CARD"."TRANS"
(
mcht in varchar,
sett in varchar,
startcardno in varchar,
endcardno in ……
[/Quote]
在存储过程里判断参数，拼接sql。
存储过程的问题解决了,还有新的问题,输入参数有可能全部为空,也可能全部不为空,这个在拼SQL语句时怎么拼,USING怎么写
SQL code
CREATE OR REPLACE PROCEDURE "CARD"."TRANS"
(
mcht in varchar,
sett in varchar,
startcardno in varchar,
endcardno in ……
[/Quote]
在存储过程里判断参数，拼接sql。
啊彪123 2012-04-06
- 打赏
- 举报
[Quote=引用 8 楼 的回复:]
以下存储过程,编译错,为什么
SQL code
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;
……
[/Quote]
以下存储过程,编译错,为什么
SQL code
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;
……
[/Quote]
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;
/zhangfengyi 2012-04-06
- 打赏
- 举报
[Quote=引用 11 楼 的回复:]
引用 10 楼 的回复:
存储过程的问题解决了,还有新的问题,输入参数有可能全部为空,也可能全部不为空,这个在拼SQL语句时怎么拼,USING怎么写
SQL code
CREATE OR REPLACE PROCEDURE "CARD"."TRANS"
(
mcht in varchar,
sett in varchar,
startcardno in varchar,
e……
[/Quote]
可能是我没说明白, 现在这个存储过程是没问题, 我这里传入的参数是18个,在拼SQL语句时mcht(商户号),startdate(起始日期),enddate(结束日期)三个是必选项,其余都是可选项,那我在拼SQL语句时该怎样拼呢,如果是sqlserver直接拼好连接在where后面就可以了,pl/sql除了where以外,还有using,能不能也动态往using后面加变量呢,不知这样说明白没有.或者有什么更简单的方法拼出来,查询界面如图所示.
另外3楼那个是以前用C++写的,效率太低了,现在正在改成sql/sql存储过程.
引用 10 楼 的回复:
存储过程的问题解决了,还有新的问题,输入参数有可能全部为空,也可能全部不为空,这个在拼SQL语句时怎么拼,USING怎么写
SQL code
CREATE OR REPLACE PROCEDURE "CARD"."TRANS"
(
mcht in varchar,
sett in varchar,
startcardno in varchar,
e……
[/Quote]
可能是我没说明白, 现在这个存储过程是没问题, 我这里传入的参数是18个,在拼SQL语句时mcht(商户号),startdate(起始日期),enddate(结束日期)三个是必选项,其余都是可选项,那我在拼SQL语句时该怎样拼呢,如果是sqlserver直接拼好连接在where后面就可以了,pl/sql除了where以外,还有using,能不能也动态往using后面加变量呢,不知这样说明白没有.或者有什么更简单的方法拼出来,查询界面如图所示.
另外3楼那个是以前用C++写的,效率太低了,现在正在改成sql/sql存储过程.
mails2008 2012-04-05
- 打赏
- 举报
为什么已经有了存储过程还用java和c++等来实现存储过程呢
ssqtjffcu 2012-04-05
- 打赏
- 举报
[Quote=引用 10 楼 的回复:]
存储过程的问题解决了,还有新的问题,输入参数有可能全部为空,也可能全部不为空,这个在拼SQL语句时怎么拼,USING怎么写
SQL code
CREATE OR REPLACE PROCEDURE "CARD"."TRANS"
(
mcht in varchar,
sett in varchar,
startcardno in varchar,
endcardno in va……
[/Quote]
你现在这个过程没有问题啊，你是想在空的时候给个默认值还是怎么？
存储过程的问题解决了,还有新的问题,输入参数有可能全部为空,也可能全部不为空,这个在拼SQL语句时怎么拼,USING怎么写
SQL code
CREATE OR REPLACE PROCEDURE "CARD"."TRANS"
(
mcht in varchar,
sett in varchar,
startcardno in varchar,
endcardno in va……
[/Quote]
你现在这个过程没有问题啊，你是想在空的时候给个默认值还是怎么？
zhangfengyi 2012-04-05
- 打赏
- 举报
存储过程的问题解决了,还有新的问题,输入参数有可能全部为空,也可能全部不为空,这个在拼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;
/
江南小鱼 2012-03-29
- 打赏
- 举报
oracle存储过程输入参数是只读的，不能对其进行赋值
zhangfengyi 2012-03-27
- 打赏
- 举报
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)))
zhangfengyi 2012-03-27
- 打赏
- 举报
大家看下,这段代码是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)))
ICE-word 2012-03-27
- 打赏
- 举报
create or replace procedure P_GUOCHENG(名称 in out 类型, 名称 in out 类型, ...) is
begin
end P_GUOCHENG;
过程创建。 执行 可以在命令窗口。EXEC 过程名。
begin
end P_GUOCHENG;
过程创建。 执行 可以在命令窗口。EXEC 过程名。
zhangfengyi 2012-03-27
- 打赏
- 举报
以下存储过程,编译错,为什么
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;
/
wangpengpengwang 2012-03-27
- 打赏
- 举报
CREATE OR REPLACE PROCEDURE P_NAME ( 传入参数或输出参数) is or as
begin
null;--->>>执行程序
end;
begin
null;--->>>执行程序
end;
wangpengpengwang 2012-03-27
- 打赏
- 举报
CREATE OR REPLACE PROCEDURE P_NAME ( 传入参数或输出参数) is or as
begin
null;--->>>执行程序
end;
begin
null;--->>>执行程序
end;
回头是岸 2012-03-27
- 打赏
- 举报
对了 我也是oracle新手 我用的是oracle11G + PLSQL
回头是岸 2012-03-27
- 打赏
- 举报
CREATE OR REPLACE PROCDURE PROC_NAME
(
参数(入in、出out)
)
AS(OR IS)
参数
BEGIN
--为参数赋值
参数：= z值;
--SQL
SQL语句;
END;(PROC_NAME)
(
参数(入in、出out)
)
AS(OR IS)
参数
BEGIN
--为参数赋值
参数：= z值;
--SQL
SQL语句;
END;(PROC_NAME)
