create or replace procedure proc_RatePhoto
(
procCompanyID in varchar2,
procBeginTime in date,
procEndTime in date
)
is
str varchar2(4000);
BEGIN
str:='CREATE temporary tablespace ls
(
companyID in varchar(200),
companyName in varchar(500),
All_Count in number default(0),
hege_Count in default(0)
)
On Commit Preserve Rows';
execute immediate str;
insert into ls (companyID,All_Count)
(select companyID,count(QualifiedFlag) as All_Count
from (select companyID,QualifiedFlag from View_PhotoInfo where UsedStop='0'and ControlTime between procBeginTime and procEndTime)
group by companyID);
insert into ls (companyID,hege_Count)
(select companyID,count(companyID) as hege_Count
from (select companyID,QualifiedFlag from View_PhotoInfo where UsedStop='0'and ControlTime between procBeginTime and procEndTime)
where QualifiedFlag='0'
group by companyID);
--更新单位名称。
update ls set ls.companyName=(selecT Data_CompanyInfo.CompanyName from Data_CompanyInfo where Data_CompanyInfo.CompanyID=ls.CompanyID)
where ls.CompanyID in(selecT CompanyID from Data_CompanyInfo);
--- UPDATE A SET A.NAME=(SELECT B.NAME FROM B WHERE B.ID=A.ID) WHERE A.ID IN (SELECT ID FROM B)；
---得到合格率
select companyID,CompanyName,sum(All_Count) as All_Count,sum(hege_Count) as hege_Count
round( convert(float,sum(hege_Count))convert(float,sum(All_Count)),4)*100) as hegelv
from ls where companyID like '%' || procCompanyID|| '%'
group by companyID,CompanyName;
--drop table temp1
END;
------解决方案--------------------------------------------------------
- SQL code
BEGIN str:='CREATE temporary tablespace ls( companyID in varchar(200), companyName in varchar(500), All_Count in number default(0), hege_Count in default(0))On Commit Preserve Rows';execute immediate str;commit; --------这里应该要显示提交。insert into ls (companyID,All_Count) (select companyID,count(QualifiedFlag) as All_Countfrom (select companyID,QualifiedFlag from View_PhotoInfo where UsedStop='0'and ControlTime between procBeginTime and procEndTime)group by companyID);
------解决方案--------------------------------------------------------
一堆的错误.语法错误自己调试下吧.
只说下oracle和sql server临时表的区别,oracle临时表不是在过程里临时创建的.而是在外面创建,过程里只是使用临时表.
在Oracle中，可以创建以下两种临时表：
1。会话特有的临时表
CREATE GLOBAL TEMPORARY <TABLE_NAME> ( <column specification> )
ON COMMIT PRESERVE ROWS；
2。事务特有的临时表
CREATE GLOBAL TEMPORARY <TABLE_NAME> ( <column specification> )
ON COMMIT DELETE ROWS；
------解决方案--------------------------------------------------------
还有一个比较大的问题就是plsql不支持单纯的select语句,select语句必须有变量接收其返回值.或者使用cursor,open cursorname for select 语句