下面的sql代码是我用游标进行统计的oracle 语句,本来这个是sql 用统计的,现在换库了,需要改成oracle的,我对oracle不熟,里面改的语法都是网上一边找资料一边改的,现在还有一些问题,小弟找不到原因。请各位大侠帮帮忙看看。
这个东西很急,在线等。
CREATE GLOBAL TEMPORARY TABLE Temp_Table
(
ITEM_CLASS varchar2(100),
QUANTITY NUMBER, ----
COSTS NUMBER,
UNITS_CODE varchar2(100),
INP_NO varchar2(100),
ITEM_NO NUMBER ,
ITEM_CODE varchar2(100),
ITEM_NAME varchar2(100),
GENERAL_NAME varchar2(100),
ITEM_SPEC varchar2(100),
UNITS varchar2(100),
AMOUNT NUMBER,
UNIT_PRICE NUMBER,
CHARGES NUMBER,
CLASS_ON_INP_RCPT varchar2(100),
DRUG_SIGN varchar2(100),
BILLING_DATE_TIME date,
INSURANCE_CODE varchar2(100),
INSURANCE_PAY NUMBER,
PERSON_STATUS varchar2(100),
CHARGE_TYPE varchar2(100),
PAY_IN NUMBER,
PAY_OUT NUMBER
)
ON COMMIT DELETE ROWS;declare temp_column varchar2;
CURSOR order_cursor_1 IS select distinct ITEM_CLASS from V_CONN_INP_BILL_DETAIL; --定义游标
begin
open order_cursor_1 --打开游标
LOOP
FETCH order_cursor_1 INTO temp_column;
EXIT WHEN order_cursor_1%NOTFOUND;
--FETCH NEXT FROM order_cursor_1 INTO @temp_column
--WHILE @@FETCH_STATUS = 0
--begin
insert into Temp_Table select B.ITEM_CLASS,B.QUANTITY,B.COSTS,B.UNITS_CODE,B.INP_NO,B.ITEM_NO,B.ITEM_CODE,B.ITEM_NAME,B.GENERAL_NAME,B.ITEM_SPEC,B.UNITS
,B.AMOUNT,B.UNIT_PRICE,B.CHARGES,B.CLASS_ON_INP_RCPT,B.DRUG_SIGN,B.BILLING_DATE_TIME,B.INSURANCE_CODE
,B.INSURANCE_PAY,P.CHARGE_TYPE,P.PERSON_STATUS,
--下面根据条件判断
(CASE P.CHARGE_TYPE
WHEN '01' THEN 0
ELSE
CASE
WHEN (p.PERSON_STATUS='06') THEN CHARGES
WHEN (p.PERSON_STATUS='07') THEN CHARGES
WHEN (p.PERSON_STATUS='08') THEN CHARGES
WHEN (p.PERSON_STATUS='09') THEN CHARGES
ELSE
B.INSURANCE_PAY * B.CHARGES
END
END) AS PAY_IN,
(CASE P.CHARGE_TYPE
WHEN '01' THEN CHARGES
ELSE
CASE
WHEN (p.PERSON_STATUS='06') THEN 0
WHEN (p.PERSON_STATUS='07') THEN 0
WHEN (p.PERSON_STATUS='08') THEN 0
WHEN (p.PERSON_STATUS='09') THEN 0
ELSE
(1 - B.INSURANCE_PAY) * B.CHARGES
END
END) AS PAY_OUT--B.INSURANCE_PAY * B.CHARGES as PAY_IN,(1 - B.INSURANCE_PAY) * B.CHARGES as PAY_OUT
from V_CONN_INP_BILL_DETAIL B,V_CONN_INP_PATS_VISIT P where B.ITEM_CLASS = temp_column AND B.INP_NO=P.SCOPE --SCOPE是住院号
insert into Temp_Table select'小计',0,0,'','',0,'','','','',''
,0,0,0,'','','','',0,'',''
,SUM(CASE P.CHARGE_TYPE
WHEN '01' THEN 0
ELSE
CASE
WHEN (p.PERSON_STATUS='06') THEN CHARGES
WHEN (p.PERSON_STATUS='07') THEN CHARGES
WHEN (p.PERSON_STATUS='08') THEN CHARGES
WHEN (p.PERSON_STATUS='09') THEN CHARGES
ELSE
B.INSURANCE_PAY * B.CHARGES
END
END),
SUM(CASE P.CHARGE_TYPE
WHEN '01' THEN CHARGES
ELSE
CASE
WHEN (p.PERSON_STATUS='06') THEN 0
WHEN (p.PERSON_STATUS='07') THEN 0
WHEN (p.PERSON_STATUS='08') THEN 0
WHEN (p.PERSON_STATUS='09') THEN 0
ELSE
(1 - B.INSURANCE_PAY) * B.CHARGES
END
END)
from V_CONN_INP_BILL_DETAIL B,V_CONN_INP_PATS_VISIT P
where ITEM_CLASS = temp_column AND B.INP_NO=P.SCOPE --这里就拼小计需要统计的列了,注意列要和上面的一样
-- FETCH NEXT FROM order_cursor_1 INTO @temp_column --让游标读取下一个
--end
END LOOP;
close order_cursor_1 --关闭游标
end;
insert into Temp_Table select '合计',0,0,'','',0,'','','','',''
,0,0,0,'','','','',0,'',''
,SUM(CASE P.CHARGE_TYPE
WHEN '01' THEN 0
ELSE
CASE
WHEN (p.PERSON_STATUS='06') THEN CHARGES
WHEN (p.PERSON_STATUS='07') THEN CHARGES
WHEN (p.PERSON_STATUS='08') THEN CHARGES
WHEN (p.PERSON_STATUS='09') THEN CHARGES
ELSE
B.INSURANCE_PAY * B.CHARGES
END
END),
SUM(CASE P.CHARGE_TYPE
WHEN '01' THEN CHARGES
ELSE
CASE
WHEN (p.PERSON_STATUS='06') THEN 0
WHEN (p.PERSON_STATUS='07') THEN 0
WHEN (p.PERSON_STATUS='08') THEN 0
WHEN (p.PERSON_STATUS='09') THEN 0
ELSE
(1 - B.INSURANCE_PAY) * B.CHARGES
END
END)
from V_CONN_INP_BILL_DETAIL B,V_CONN_INP_PATS_VISIT P
WHERE B.INP_NO=P.SCOPE -- 这里就拼总计 select * from Temp_Table
drop table Temp_Table
这个东西很急,在线等。
CREATE GLOBAL TEMPORARY TABLE Temp_Table
(
ITEM_CLASS varchar2(100),
QUANTITY NUMBER, ----
COSTS NUMBER,
UNITS_CODE varchar2(100),
INP_NO varchar2(100),
ITEM_NO NUMBER ,
ITEM_CODE varchar2(100),
ITEM_NAME varchar2(100),
GENERAL_NAME varchar2(100),
ITEM_SPEC varchar2(100),
UNITS varchar2(100),
AMOUNT NUMBER,
UNIT_PRICE NUMBER,
CHARGES NUMBER,
CLASS_ON_INP_RCPT varchar2(100),
DRUG_SIGN varchar2(100),
BILLING_DATE_TIME date,
INSURANCE_CODE varchar2(100),
INSURANCE_PAY NUMBER,
PERSON_STATUS varchar2(100),
CHARGE_TYPE varchar2(100),
PAY_IN NUMBER,
PAY_OUT NUMBER
)
ON COMMIT DELETE ROWS;declare temp_column varchar2;
CURSOR order_cursor_1 IS select distinct ITEM_CLASS from V_CONN_INP_BILL_DETAIL; --定义游标
begin
open order_cursor_1 --打开游标
LOOP
FETCH order_cursor_1 INTO temp_column;
EXIT WHEN order_cursor_1%NOTFOUND;
--FETCH NEXT FROM order_cursor_1 INTO @temp_column
--WHILE @@FETCH_STATUS = 0
--begin
insert into Temp_Table select B.ITEM_CLASS,B.QUANTITY,B.COSTS,B.UNITS_CODE,B.INP_NO,B.ITEM_NO,B.ITEM_CODE,B.ITEM_NAME,B.GENERAL_NAME,B.ITEM_SPEC,B.UNITS
,B.AMOUNT,B.UNIT_PRICE,B.CHARGES,B.CLASS_ON_INP_RCPT,B.DRUG_SIGN,B.BILLING_DATE_TIME,B.INSURANCE_CODE
,B.INSURANCE_PAY,P.CHARGE_TYPE,P.PERSON_STATUS,
--下面根据条件判断
(CASE P.CHARGE_TYPE
WHEN '01' THEN 0
ELSE
CASE
WHEN (p.PERSON_STATUS='06') THEN CHARGES
WHEN (p.PERSON_STATUS='07') THEN CHARGES
WHEN (p.PERSON_STATUS='08') THEN CHARGES
WHEN (p.PERSON_STATUS='09') THEN CHARGES
ELSE
B.INSURANCE_PAY * B.CHARGES
END
END) AS PAY_IN,
(CASE P.CHARGE_TYPE
WHEN '01' THEN CHARGES
ELSE
CASE
WHEN (p.PERSON_STATUS='06') THEN 0
WHEN (p.PERSON_STATUS='07') THEN 0
WHEN (p.PERSON_STATUS='08') THEN 0
WHEN (p.PERSON_STATUS='09') THEN 0
ELSE
(1 - B.INSURANCE_PAY) * B.CHARGES
END
END) AS PAY_OUT--B.INSURANCE_PAY * B.CHARGES as PAY_IN,(1 - B.INSURANCE_PAY) * B.CHARGES as PAY_OUT
from V_CONN_INP_BILL_DETAIL B,V_CONN_INP_PATS_VISIT P where B.ITEM_CLASS = temp_column AND B.INP_NO=P.SCOPE --SCOPE是住院号
insert into Temp_Table select'小计',0,0,'','',0,'','','','',''
,0,0,0,'','','','',0,'',''
,SUM(CASE P.CHARGE_TYPE
WHEN '01' THEN 0
ELSE
CASE
WHEN (p.PERSON_STATUS='06') THEN CHARGES
WHEN (p.PERSON_STATUS='07') THEN CHARGES
WHEN (p.PERSON_STATUS='08') THEN CHARGES
WHEN (p.PERSON_STATUS='09') THEN CHARGES
ELSE
B.INSURANCE_PAY * B.CHARGES
END
END),
SUM(CASE P.CHARGE_TYPE
WHEN '01' THEN CHARGES
ELSE
CASE
WHEN (p.PERSON_STATUS='06') THEN 0
WHEN (p.PERSON_STATUS='07') THEN 0
WHEN (p.PERSON_STATUS='08') THEN 0
WHEN (p.PERSON_STATUS='09') THEN 0
ELSE
(1 - B.INSURANCE_PAY) * B.CHARGES
END
END)
from V_CONN_INP_BILL_DETAIL B,V_CONN_INP_PATS_VISIT P
where ITEM_CLASS = temp_column AND B.INP_NO=P.SCOPE --这里就拼小计需要统计的列了,注意列要和上面的一样
-- FETCH NEXT FROM order_cursor_1 INTO @temp_column --让游标读取下一个
--end
END LOOP;
close order_cursor_1 --关闭游标
end;
insert into Temp_Table select '合计',0,0,'','',0,'','','','',''
,0,0,0,'','','','',0,'',''
,SUM(CASE P.CHARGE_TYPE
WHEN '01' THEN 0
ELSE
CASE
WHEN (p.PERSON_STATUS='06') THEN CHARGES
WHEN (p.PERSON_STATUS='07') THEN CHARGES
WHEN (p.PERSON_STATUS='08') THEN CHARGES
WHEN (p.PERSON_STATUS='09') THEN CHARGES
ELSE
B.INSURANCE_PAY * B.CHARGES
END
END),
SUM(CASE P.CHARGE_TYPE
WHEN '01' THEN CHARGES
ELSE
CASE
WHEN (p.PERSON_STATUS='06') THEN 0
WHEN (p.PERSON_STATUS='07') THEN 0
WHEN (p.PERSON_STATUS='08') THEN 0
WHEN (p.PERSON_STATUS='09') THEN 0
ELSE
(1 - B.INSURANCE_PAY) * B.CHARGES
END
END)
from V_CONN_INP_BILL_DETAIL B,V_CONN_INP_PATS_VISIT P
WHERE B.INP_NO=P.SCOPE -- 这里就拼总计 select * from Temp_Table
drop table Temp_Table
(CASE
WHEN P.CHARGE_TYPE='01' THEN 0
ELSE
CASE
WHEN p.PERSON_STATUS in ('06','07','08','09') THEN CHARGES
ELSE
B.INSURANCE_PAY * B.CHARGES
END
END) AS PAY_IN
[UNITS_CODE] [nvarchar](max) NULL,
[INP_NO] [nvarchar](max) NULL,
[ITEM_NO] [decimal](18, 0) NULL,
[ITEM_CLASS] [nvarchar](max) NULL,
[ITEM_CODE] [nvarchar](max) NULL,
[ITEM_NAME] [nvarchar](max) NULL,
[GENERAL_NAME] [nvarchar](max) NULL,
[ITEM_SPEC] [nvarchar](max) NULL,
[UNITS] [nvarchar](max) NULL,
[AMOUNT] [decimal](18, 4) NULL,
[QUANTITY] [decimal](18, 0) NULL,
[UNIT_PRICE] [decimal](18, 4) NULL,
[COSTS] [decimal](18, 2) NULL,
[CHARGES] [decimal](18, 2) NULL,
[CLASS_ON_INP_RCPT] [nvarchar](max) NULL,
[DRUG_SIGN] [nvarchar](max) NULL,
[BILLING_DATE_TIME] [datetime] NULL,
[INSURANCE_CODE] [nvarchar](max) NULL,
[INSURANCE_PAY] [decimal](18, 2) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[V_CONN_INP_PATS_VISIT](
[CHARGE_TYPE] [nvarchar](50) NULL,
[PERSON_STATUS] [nvarchar](50) NULL,
[SCOPE] [nvarchar](50) NULL
) ON
(
ITEM_CLASS varchar2(100),
QUANTITY NUMBER, ----
COSTS NUMBER,
UNITS_CODE varchar2(100),
INP_NO varchar2(100),
ITEM_NO NUMBER ,
ITEM_CODE varchar2(100),
ITEM_NAME varchar2(100),
GENERAL_NAME varchar2(100),
ITEM_SPEC varchar2(100),
UNITS varchar2(100),
AMOUNT NUMBER,
UNIT_PRICE NUMBER,
CHARGES NUMBER,
CLASS_ON_INP_RCPT varchar2(100),
DRUG_SIGN varchar2(100),
BILLING_DATE_TIME date,
INSURANCE_CODE varchar2(100),
INSURANCE_PAY NUMBER,
PERSON_STATUS varchar2(100),
CHARGE_TYPE varchar2(100),
PAY_IN NUMBER,
PAY_OUT NUMBER
)
ON COMMIT DELETE ROWS;declare temp_column varchar2(100);
temp_code varchar2(100);
CURSOR order_cursor_1 IS select distinct INP_NO,UNITS_CODE from SUPHISV3.V_CONN_INP_BILL_DETAIL; --定义游标
begin
open order_cursor_1; --打开游标 FETCH order_cursor_1 INTO temp_column,temp_code;
EXIT WHEN order_cursor_1%NOTFOUND;
insert into Temp_Table select B.ITEM_CLASS,B.QUANTITY,B.COSTS,B.UNITS_CODE,B.INP_NO,B.ITEM_NO,B.ITEM_CODE,B.ITEM_NAME,B.GENERAL_NAME,B.ITEM_SPEC,B.UNITS
,B.AMOUNT,B.UNIT_PRICE,B.CHARGES,B.CLASS_ON_INP_RCPT,B.DRUG_SIGN,B.BILLING_DATE_TIME,B.INSURANCE_CODE
,B.INSURANCE_PAY,P.CHARGE_TYPE,P.PERSON_STATUS,
--下面根据条件判断
(CASE P.CHARGE_TYPE
WHEN '01' THEN 0
ELSE
CASE
WHEN p.PERSON_STATUS in ('06','07','08','09') THEN CHARGES
ELSE
B.INSURANCE_PAY * B.CHARGES
END
END) AS PAY_IN,
(CASE P.CHARGE_TYPE
WHEN '01' THEN CHARGES
ELSE
CASE
WHEN p.PERSON_STATUS IN ('06','07','08','09') THEN 0
ELSE
(1 - B.INSURANCE_PAY) * B.CHARGES
END
END) AS PAY_OUT
from SUPHISV3.V_CONN_INP_BILL_DETAIL B, SUPHISV3.V_CONN_INP_PATS_VISIT P where B.INP_NO = temp_column AND B.UNITS_CODE=temp_code AND B.INP_NO=P.INP_NO ;
insert into Temp_Table select'小计',0,0,'','',0,'','','','',''
,0,0,0,'','','','',0,'',''
,SUM(CASE P.CHARGE_TYPE
WHEN '01' THEN 0
ELSE
CASE
WHEN p.PERSON_STATUS IN ('06','07','08','09') THEN CHARGES
ELSE
B.INSURANCE_PAY * B.CHARGES
END
END),
SUM(CASE P.CHARGE_TYPE
WHEN '01' THEN CHARGES
ELSE
CASE
WHEN p.PERSON_STATUS IN ('06','07','08','09') THEN 0
ELSE
(1 - B.INSURANCE_PAY) * B.CHARGES
END
END)
from SUPHISV3.V_CONN_INP_BILL_DETAIL B, SUPHISV3.V_CONN_INP_PATS_VISIT P
where B.INP_NO = temp_column AND B.UNITS_CODE=temp_code AND B.INP_NO=P.INP_NO ;
--这里就拼小计需要统计的列了,注意列要和上面的一样
close order_cursor_1; --关闭游标
end; insert into Temp_Table select '合计',0,0,'','',0,'','','','',''
,0,0,0,'','','','',0,'',''
,SUM(CASE P.CHARGE_TYPE
WHEN '01' THEN 0
ELSE
CASE
WHEN p.PERSON_STATUS IN ('06','07','08','09') THEN CHARGES
ELSE
B.INSURANCE_PAY * B.CHARGES
END
END),
SUM(CASE P.CHARGE_TYPE
WHEN '01' THEN CHARGES
ELSE
CASE
WHEN p.PERSON_STATUS IN('06','07','08','09') THEN 0
ELSE
(1 - B.INSURANCE_PAY) * B.CHARGES
END
END)
from SUPHISV3.V_CONN_INP_BILL_DETAIL B, SUPHISV3.V_CONN_INP_PATS_VISIT P
WHERE B.INP_NO=P.INP_NO ;-- 这里就拼总计 select * from Temp_Table;
drop table Temp_Table;
我现在这样改了,不提示语法错误,但是执行添加到临时表的时候很慢很慢,几十条数据等了十几分钟还不行,我想问问大家是不是 在用游标循环添加数据的时候有问题。?