create or replace procedure check_nav(tdate in hld.t_date%type)
begin
select a.port_code as 财务代码,
a.t_date as 日期,
a.summ as 持仓和利息,
b.p_totalnav as 总净值,
case
when a.summ - round(b.p_totalnav, 2) > 0.01 then
'失败'
else
'成功'
end as 是否成功
from (select port_code, t_date, sum(h_eval + h_ai) as summ
from hld
group by t_date, port_code) a
join (select port_code, t_date, p_totalnav from NAV) b
on a.port_code = b.port_code
and a.t_date = b.t_date
where a.t_date >= tdate;
end check_nav;编译报错:PROCEDURE XRISK.CHECK_NAV 编译错误错误:PLS-00103: 出现符号 "BEGIN"在需要下列之一时:
; is with authid as
cluster order using external deterministic parallel_enable
pipelined
行:2
文本:begin这是咋回事嘞?应该怎么写啊?
begin
select a.port_code as 财务代码,
a.t_date as 日期,
a.summ as 持仓和利息,
b.p_totalnav as 总净值,
case
when a.summ - round(b.p_totalnav, 2) > 0.01 then
'失败'
else
'成功'
end as 是否成功
from (select port_code, t_date, sum(h_eval + h_ai) as summ
from hld
group by t_date, port_code) a
join (select port_code, t_date, p_totalnav from NAV) b
on a.port_code = b.port_code
and a.t_date = b.t_date
where a.t_date >= tdate;
end check_nav;编译报错:PROCEDURE XRISK.CHECK_NAV 编译错误错误:PLS-00103: 出现符号 "BEGIN"在需要下列之一时:
; is with authid as
cluster order using external deterministic parallel_enable
pipelined
行:2
文本:begin这是咋回事嘞?应该怎么写啊?
FN_ID NUMBER(16) not null,
PORT_CODE VARCHAR2(30) not null,
P_PUBDATE CHAR(10),
T_DATE CHAR(10) not null,
P_TOTALNAV NUMBER(38,4),
P_UNITNAV NUMBER(32,8),
P_PROFIT_1W NUMBER(32,8),
P_YIELD_7D NUMBER(32,8),
P_CUMU_NAV NUMBER(32,16),
P_SIZE NUMBER(38,4),
IMP_DATE CHAR(10),
PIPE_ID NUMBER(16),
CURRENCY CHAR(3),
SPEC_FLAG NUMBER default 0,
F_MATURITY NUMBER,
F_VALUE_DEVIATION NUMBER(10,6),
F_REALIZEDGAIN NUMBER(32,16),
P_TOTAL_ASSET NUMBER(38,4),
P_SALE_PRICE NUMBER(32,16),
constraint PK_NAV primary key (FN_ID)
using index
pctfree 10
initrans 2
storage
(
initial 320K
minextents 1
maxextents unlimited
)
tablespace RISK
logging
)
pctfree 10
initrans 1
storage
(
initial 2048K
minextents 1
maxextents unlimited
)
tablespace RISK
logging
monitoring
noparallel
/*==============================================================*/
/* Table: HLD */
/*==============================================================*/
create table HLD (
H_ID NUMBER(16) default 0 not null,
PORT_CODE VARCHAR2(30) not null,
T_DATE CHAR(10) not null,
I_CODE VARCHAR2(30) not null,
A_TYPE VARCHAR2(20) not null,
M_TYPE VARCHAR2(20) not null,
ACCT_CODE VARCHAR2(50),
FACCTATTR VARCHAR2(100),
FBY VARCHAR2(100),
CURRENCY CHAR(3) not null,
H_TRADEABLE CHAR,
H_COUNT NUMBER(32,16),
H_COST NUMBER(32,16),
H_EVAL NUMBER(32,16),
H_EVAL_ADDED NUMBER(32,16),
H_AI NUMBER(32,16),
IMP_DATE CHAR(10),
PIPE_ID NUMBER,
PORT_CURRENCY CHAR(3),
H_PORT_COST NUMBER(32,16),
H_PORT_EVAL NUMBER(32,16),
H_PORT_EVAL_ADDED NUMBER(32,16),
FX_RATE NUMBER(20,15),
I_NAME VARCHAR2(100),
H_PORT_AI NUMBER(32,16),
FACCTCLASS VARCHAR2(30),
POSITION CHAR,
COMPANY_CODE VARCHAR2(50),
PFOLIO_UNIT_CD VARCHAR2(20),
FASSET_CLASS VARCHAR2(20),
H_DURATION_MOD NUMBER(32,16),
H_PAR_VALUE NUMBER(32,16),
H_NII NUMBER(32,16),
H_PORT_NII NUMBER(32,16),
H_REDEMPTION_YIELD NUMBER(32,16),
H_RECV_EVAL NUMBER(32,16),
H_PORT_RECV_EVAL NUMBER(32,16),
H_RECV_AI NUMBER(32,16),
H_PORT_RECV_AI NUMBER(32,16),
H_CASH_NII_RECV NUMBER(32,16),
H_PORT_CASH_NII_RECV NUMBER(32,16)
)
partition by hash
(PORT_CODE)
(
partition SYS_P549 tablespace RISK,
partition SYS_P550 tablespace RISK,
partition SYS_P551 tablespace RISK,
partition SYS_P552 tablespace RISK,
partition SYS_P553 tablespace RISK,
partition SYS_P554 tablespace RISK,
partition SYS_P555 tablespace RISK,
partition SYS_P556 tablespace RISK,
partition SYS_P557 tablespace RISK,
partition SYS_P558 tablespace RISK,
partition SYS_P559 tablespace RISK,
partition SYS_P560 tablespace RISK,
partition SYS_P561 tablespace RISK,
partition SYS_P562 tablespace RISK,
partition SYS_P563 tablespace RISK,
partition SYS_P564 tablespace RISK,
partition SYS_P565 tablespace RISK,
partition SYS_P566 tablespace RISK,
partition SYS_P567 tablespace RISK,
partition SYS_P568 tablespace RISK,
partition SYS_P569 tablespace RISK,
partition SYS_P570 tablespace RISK,
partition SYS_P571 tablespace RISK,
partition SYS_P572 tablespace RISK,
partition SYS_P573 tablespace RISK,
partition SYS_P574 tablespace RISK,
partition SYS_P575 tablespace RISK,
partition SYS_P576 tablespace RISK,
partition SYS_P577 tablespace RISK,
partition SYS_P578 tablespace RISK,
partition SYS_P579 tablespace RISK,
partition SYS_P580 tablespace RISK,
partition SYS_P581 tablespace RISK,
partition SYS_P582 tablespace RISK,
partition SYS_P583 tablespace RISK,
partition SYS_P584 tablespace RISK,
partition SYS_P585 tablespace RISK,
partition SYS_P586 tablespace RISK,
partition SYS_P587 tablespace RISK,
partition SYS_P588 tablespace RISK,
partition SYS_P589 tablespace RISK,
partition SYS_P590 tablespace RISK,
partition SYS_P591 tablespace RISK,
partition SYS_P592 tablespace RISK,
partition SYS_P593 tablespace RISK,
partition SYS_P594 tablespace RISK,
partition SYS_P595 tablespace RISK,
partition SYS_P596 tablespace RISK,
partition SYS_P597 tablespace RISK,
partition SYS_P598 tablespace RISK,
partition SYS_P599 tablespace RISK,
partition SYS_P600 tablespace RISK,
partition SYS_P601 tablespace RISK,
partition SYS_P602 tablespace RISK,
partition SYS_P603 tablespace RISK,
partition SYS_P604 tablespace RISK,
partition SYS_P605 tablespace RISK,
partition SYS_P606 tablespace RISK,
partition SYS_P607 tablespace RISK,
partition SYS_P608 tablespace RISK,
partition SYS_P609 tablespace RISK,
partition SYS_P610 tablespace RISK,
partition SYS_P611 tablespace RISK,
partition SYS_P612 tablespace RISK
)
monitoring
noparallel
create or replace procedure check_nav(tdate in hld.t_date%type)
--少了这个
is
begin
select a.port_code as 财务代码,
is
begin
select a.port_code as 财务代码,
a.t_date as 日期,
a.summ as 持仓和利息,
b.p_totalnav as 总净值,
case
when a.summ - round(b.p_totalnav, 2) > 0.01 then
'失败'
else
'成功'
end as 是否成功
from (select port_code, t_date, sum(h_eval + h_ai) as summ
from tcrp_hld
group by t_date, port_code) a
join (select port_code, t_date, p_totalnav from TCRP_NAV) b
on a.port_code = b.port_code
and a.t_date = b.t_date
where a.t_date >= tdate;
end check_nav;还是不行
PROCEDURE XRISK.CHECK_NAV 编译错误错误:PLS-00428: 在此 SELECT 语句中缺少 INTO 子句
行:4
文本:select a.port_code as 财务代码,
create or replace procedure check_nav(tdate in hld.t_date%type)
IS
vPortCode NAV.Port_Code%TYPE;
begin
select a.port_code as 财务代码, --这要查询出5列,前后矛盾
a.t_date as 日期,
a.summ as 持仓和利息,
b.p_totalnav as 总净值,
case
when a.summ - round(b.p_totalnav, 2) > 0.01 then
'失败'
else
'成功'
end as 是否成功 INTO vPortCode
from (select port_code, t_date, sum(h_eval + h_ai) as summ --这个结果集中一共才3列
from hld
group by t_date, port_code) a
join (select port_code, t_date, p_totalnav from NAV) b
on a.port_code = b.port_code
and a.t_date = b.t_date
where a.t_date >= tdate;
end check_nav;
你存储过程里面怎么能直接使用select 查询呢
你要存储过程返回数据集要用到引用游标和包。
如果你是要对查询出来的数据集逐行处理就需要用到游标,总之PLSQL块里不能存在select SQL查询语句
但可以使用select into 语句