oracle 创建存储过程,权限不足
在insert地方会编译出错:“权限不足”,请大神指点
错误:
[Warning] ORA-24344: 成功, 但出现编译错误
73/28 PL/SQL: ORA-01031: 权限不足
73/9 PL/SQL: SQL Statement ignored
102/28 PL/SQL: ORA-01031: 权限不足
102/9 PL/SQL: SQL Statement ignored
108/28 PL/SQL: ORA-01031: 权限不足
108/9 PL/SQL: SQL Statement ignored
127/17 PL/SQL: ORA-00904: "SUMDAY": 标识符无效
123/9 PL/SQL: SQL Statement ignored
(1: 0): Warning: compiled but with compilation errors
sql:
create or replace procedure ScoreToVoucher(ScoreExchThemeID int, CardTypeID int, EmployeeCode varchar2)
as
V_CardCount Number;/*卡的数量*/
V_MaxVoucherID varchar2(30);/*最大券号*/
V_MaxBillNumber varchar2(20);/*卡积分兑换表的最大单据号*/
V_EmployeeName varchar2(60);/*职员名称*/
begin
V_CardCount:=0;/*初始化卡的数量*/
select EmployeeName into V_EmployeeName from "000".tbEmployee;
delete from temp_Procedure__Card; /*删除临时卡表数据*/
delete from temp_Procedure_Voucher;/*删除临时表券数据*/
/*根据规则ID,卡类型,获取所有卡*/
insert into temp_Procedure__Card
select a.MemberCode,a.CardCode,a.AccountsCode,a.CurrentBalance,0 ExchMoney,b.ExchVParValue,'' BillNumber,0 Score
from "000".tbCard a,"000".tbScoreExchTheme b
where
a.CardState=2 /*卡状态*/
and a.IsBlankoutByChangeCard=0 /*不是废卡*/
and a.IsIntegral=1 /*积分卡*/
and a.typeID=CardTypeID /*卡类型*/
and a.PeriodOfValidity>to_char(sysdate,'YYYYMMDD')/*有效期*/
and b.ScoreExchThemeID=ScoreExchThemeID
and b.ExchMoneyForm=0/*兑换形式为“兑现金”*/
and b.IsSection=0/*分段积分不考虑*/
and case b.CardTypeID when -1 then a.typeID else b.CardTypeID end=a.typeID
and a.LevelID in (select CardLevelID from "000".tbScoreExchTheme_CardLevel where ScoreExchThemeID=ScoreExchThemeID)
and a.CurrentBalance>(select min(Score) from "000".tbScoreExchRuleToMoney where ScoreExchThemeID=ScoreExchThemeID);/*当前积分大于规则的最小积分*/
select count(*) into V_CardCount from temp_Procedure__Card;
if V_CardCount>0 then
/*循环规则,按照积分大小降序,从大到小*/
for extRule in (select * from "000".tbScoreExchRuleToMoney where ScoreExchThemeID=ScoreExchThemeID order by Score Desc) loop
/*更新兑换金额和*/
update temp_Procedure__Card set
ExchMoney=ExchMoney+(CurrentBalance-mod(CurrentBalance,extRule.Score))*extRule.ExchMoney/extRule.Score,
CurrentBalance=mod(CurrentBalance,extRule.Score),
Score=CurrentBalance-mod(CurrentBalance,extRule.Score)
where CurrentBalance>=extRule.Score ;
end loop;
delete from temp_Procedure__Card where ExchMoney<=0;/*删除没有兑换的*/
select max(BillNumber) into V_MaxBillNumber from "000".tb201407_ScoreExch where substr(billNumber,1,4)='9959';/*兑换信息最大单据号*/
update temp_Procedure__Card set BillNumber=LPad(V_MaxBillNumber + rownum,length(V_MaxBillNumber),'0');/*创建单据号*/
select Max(substr(VoucherID,5,12)) into V_MaxVoucherID from "000".tbVoucher;
/*插入券临时表*/
insert into temp_Procedure_Voucher
select * from (
WITH T1 AS(
SELECT BillNumber,CardCode,MemberCode,AccountsCode,ExchMoney,ParValue,SUM(ExchMoney/ParValue)OVER(ORDER BY CardCode,MemberCode,AccountsCode,ExchMoney,ParValue) RC
FROM temp_Procedure__Card
),T2 AS (