PLSQL IF ELSE в процедуре пакета
У меня есть пакет, который имеет 4 параметра. Все параметры будут получены из параллельного запроса. В соответствии с параметром типа транзакции пакет должен выполнить набор операторов. Я только начал с оракула. Ниже мой код
CREATE OR replace PACKAGE BODY
vat_reg_6
IS
PROCEDURE xx_po_vat_reg_proc_par(errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_startdate DATE,
p_enddate DATE,
p_legal_entity_id NUMBER,
p_trantype VARCHAR2)
IS
CURSOR po_cursor IS
--============================My Approach=================
IF p_trantype='AP'-- Should execute below block
SELECT al.tax_rate_code AS taxcode,
al.amount AS netamount,
al.amount AS taxamount,
ai.invoice_date AS reportingdate,
ai.invoice_num AS invoicenumber,
ai.invoice_date AS invoicedate,
ai.invoice_amount AS grossamount,
ai.invoice_num AS documentnumber ,
ai.invoice_date AS documentdate,
ai.vendor_id AS suplierid,
hz.tax_reference AS suppliervatnumber,
gl.segment1 AS companycode,
'AP' AS transactiontype
FROM apps.ap_invoice_lines_all al
inner join apps.ap_invoices_all ai
ON ai.invoice_id=al.invoice_id
inner join apps.hz_parties hz
ON ai.party_id=hz.party_id
inner join apps.ap_invoice_distributions_all dl
ON dl.invoice_id=al.invoice_id
inner join apps.gl_code_combinations gl
ON gl.code_combination_id=dl.dist_code_combination_id
WHERE ROWNUM<200
AND ai.invoice_date BETWEEN p_startdate AND p_enddate
AND ai.legal_entity_id=p_legal_entity_id;
ELSE -------------------
--===========This block=====================
BEGIN
/*apps.fnd_file.put_line (
apps.fnd_file.Output,
'Program Started
'
);*/
apps.fnd_file.put_line (apps.fnd_file.output,rpad('TaxCode',8)
|| rpad('NetAMount',15)
|| rpad('TaxAmount',15)
|| rpad('ReportingDate',20)
|| rpad('InvoiceNumber',20)
|| rpad('InvoiceDate',20)
|| rpad('GrossAmount',20)
|| rpad('DocumentNumber',20)
|| rpad('DocumentDate',20)
|| rpad('SuplierID',20)
|| rpad ('SupplierVATNumber',20)
|| rpad('CompanyCode',20)
|| rpad('TransactionType',20));
apps.fnd_file.put_line (apps.fnd_file.output, '--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------' );
/*FND_FILE.put_line(FND_FILE.output,'Starting processing:');*/
FOR po_rec IN po_cursor
LOOP
apps.fnd_file.put_line (apps.fnd_file.output, rpad(po_rec.taxcode,8)
|| rpad(po_rec.netamount,15)
|| rpad(po_rec.taxamount,15)
|| rpad(po_rec.reportingdate,20)
|| rpad(po_rec.invoicenumber,20)
|| rpad(po_rec.invoicedate,20)
|| rpad(po_rec.grossamount,20)
|| rpad(po_rec.documentnumber,20)
|| rpad(po_rec.documentdate,20)
|| rpad(po_rec.suplierid,20)
|| rpad (po_rec.suppliervatnumber,20)
|| rpad(po_rec.companycode,20)
|| rpad(po_rec.transactiontype,20));
/*APPS.FND_FILE.put_line(APPS.FND_FILE.output,
po_rec.TaxCode || po_rec.NetAMount ||
po_rec.TaxAmount || po_rec.ReportingDate||po_rec.InvoiceNumber||po_rec.GrossAmount||po_rec.DocumentNumber||po_rec.DocumentDate||po_rec.SuplierID||
po_rec.SupplierVATNumber||po_rec.CompanyCode||po_rec.TransactionType);*/
/*INSERT INTO APPS_RO.VAT_TEMP VALUES (po_rec.TaxCode,
po_rec.NetAMount,
po_rec.TaxAmount,
po_rec.ReportingDate,
po_rec.InvoiceNumber,
po_rec.InvoiceDate,
po_rec.GrossAmount,
po_rec.DocumentNumber,
po_rec.DocumentDate,
po_rec.SuplierID,
po_rec.SupplierVATNumber,
po_rec.CompanyCode,
po_rec.TransactionType);*/
END LOOP;
--FND_FILE.put_line(FND_FILE.output,'Done!');
COMMIT;
-- Return 0 for successful completion.
errbuf :='';
retcode := '0';
/*exception
when others then
errbuf := sqlerrm;
retcode := '2';*/
END xx_po_vat_reg_proc_par;
END vat_reg_6;
Будет ли мой подход работать? Пожалуйста, помогите мне сделать это!
3 ответа
Вы должны обрабатывать логику if else в ref курсоре
Внутри обычного курсора вы не можете делать if/else. Поэтому объявите курсор ref, и тогда ваша логика должна работать. Смотрите ссылку ниже.
Вы делаете ошибку, имея условное в вашем блоке декларации. Если вы хотя бы скомпилировали его, Oracle выдаст вам сообщение об ошибке.
Ваша фактическая реализация грязная и, честно говоря, не уверен, что вы делаете. Даже если вы переместите блок IF из объявления в исполняемый раздел, вы получите еще одну ошибку компиляции, потому что оператор SELECT должен быть извлечен в локальные переменные (чего вы еще не сделали), а затем может получить исключение времени выполнения, так как операторы SQL должен вернуть одну строку (и я подозреваю, что ваш оператор SQL не делает то, что должен).
Вот как это должно выглядеть, но без объяснения того, что вы пытаетесь сделать, это всего лишь выстрел в дарле
CREATE OR REPLACE PACKAGE BODY vat_reg_6 IS
PROCEDURE xx_po_vat_reg_proc_par(errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_startdate DATE,
p_enddate DATE,
p_legal_entity_id NUMBER,
p_trantype VARCHAR2) IS
CURSOR po_cursor IS
SELECT al.tax_rate_code AS taxcode,
al.amount AS netamount,
al.amount AS taxamount,
ai.invoice_date AS reportingdate,
ai.invoice_num AS invoicenumber,
ai.invoice_date AS invoicedate,
ai.invoice_amount AS grossamount,
ai.invoice_num AS documentnumber,
ai.invoice_date AS documentdate,
ai.vendor_id AS suplierid,
hz.tax_reference AS suppliervatnumber,
gl.segment1 AS companycode,
'AP' AS transactiontype
FROM apps.ap_invoice_lines_all al
INNER JOIN apps.ap_invoices_all ai
ON ai.invoice_id = al.invoice_id
INNER JOIN apps.hz_parties hz
ON ai.party_id = hz.party_id
INNER JOIN apps.ap_invoice_distributions_all dl
ON dl.invoice_id = al.invoice_id
INNER JOIN apps.gl_code_combinations gl
ON gl.code_combination_id = dl.dist_code_combination_id
WHERE ROWNUM < 200
AND ai.invoice_date BETWEEN p_startdate AND p_enddate
AND ai.legal_entity_id = p_legal_entity_id;
BEGIN
--============================My Approach=================
IF p_trantype = 'AP' THEN -- Should execute below block
SELECT al.tax_rate_code AS taxcode,
al.amount AS netamount,
al.amount AS taxamount,
ai.invoice_date AS reportingdate,
ai.invoice_num AS invoicenumber,
ai.invoice_date AS invoicedate,
ai.invoice_amount AS grossamount,
ai.invoice_num AS documentnumber,
ai.invoice_date AS documentdate,
ai.vendor_id AS suplierid,
hz.tax_reference AS suppliervatnumber,
gl.segment1 AS companycode,
'AP' AS transactiontype
-- INTO
FROM apps.ap_invoice_lines_all al
INNER JOIN apps.ap_invoices_all ai
ON ai.invoice_id = al.invoice_id
INNER JOIN apps.hz_parties hz
ON ai.party_id = hz.party_id
INNER JOIN apps.ap_invoice_distributions_all dl
ON dl.invoice_id = al.invoice_id
INNER JOIN apps.gl_code_combinations gl
ON gl.code_combination_id = dl.dist_code_combination_id
WHERE ROWNUM < 200
AND ai.invoice_date BETWEEN p_startdate AND p_enddate
AND ai.legal_entity_id = p_legal_entity_id;
ELSE
apps.fnd_file.put_line(
apps.fnd_file.output,
RPAD('TaxCode', 8)
|| RPAD('NetAMount', 15)
|| RPAD('TaxAmount', 15)
|| RPAD('ReportingDate', 20)
|| RPAD('InvoiceNumber', 20)
|| RPAD('InvoiceDate', 20)
|| RPAD('GrossAmount', 20)
|| RPAD('DocumentNumber', 20)
|| RPAD('DocumentDate', 20)
|| RPAD('SuplierID', 20)
|| RPAD('SupplierVATNumber', 20)
|| RPAD('CompanyCode', 20)
|| RPAD('TransactionType', 20));
apps.fnd_file.put_line(
apps.fnd_file.output,
'--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------');
/*FND_FILE.put_line(FND_FILE.output,'Starting processing:');*/
FOR po_rec IN po_cursor LOOP
apps.fnd_file.put_line(
apps.fnd_file.output,
RPAD(po_rec.taxcode, 8)
|| RPAD(po_rec.netamount, 15)
|| RPAD(po_rec.taxamount, 15)
|| RPAD(po_rec.reportingdate, 20)
|| RPAD(po_rec.invoicenumber, 20)
|| RPAD(po_rec.invoicedate, 20)
|| RPAD(po_rec.grossamount, 20)
|| RPAD(po_rec.documentnumber, 20)
|| RPAD(po_rec.documentdate, 20)
|| RPAD(po_rec.suplierid, 20)
|| RPAD(po_rec.suppliervatnumber, 20)
|| RPAD(po_rec.companycode, 20)
|| RPAD(po_rec.transactiontype, 20));
/*APPS.FND_FILE.put_line(APPS.FND_FILE.output,
po_rec.TaxCode || po_rec.NetAMount ||
po_rec.TaxAmount || po_rec.ReportingDate||po_rec.InvoiceNumber||po_rec.GrossAmount||po_rec.DocumentNumber||po_rec.DocumentDate||po_rec.SuplierID||
po_rec.SupplierVATNumber||po_rec.CompanyCode||po_rec.TransactionType);*/
/*INSERT INTO APPS_RO.VAT_TEMP VALUES (po_rec.TaxCode,
po_rec.NetAMount,
po_rec.TaxAmount,
po_rec.ReportingDate,
po_rec.InvoiceNumber,
po_rec.InvoiceDate,
po_rec.GrossAmount,
po_rec.DocumentNumber,
po_rec.DocumentDate,
po_rec.SuplierID,
po_rec.SupplierVATNumber,
po_rec.CompanyCode,
po_rec.TransactionType);*/
END LOOP;
--FND_FILE.put_line(FND_FILE.output,'Done!');
END IF;
COMMIT;
-- Return 0 for successful completion.
errbuf := '';
retcode := '0';
/*exception
when others then
errbuf := sqlerrm;
retcode := '2';*/
--FND_FILE.put_line(FND_FILE.output,'Done!');
COMMIT; -- Return 0 for successful completion.
errbuf := '';
retcode := '0';
/*exception
when others then
errbuf := sqlerrm;
retcode := '2';*/
END xx_po_vat_reg_proc_par;
END vat_reg_6;
Я думаю, что вы должны использовать динамические операторы SQL для этого подхода. лайк:
declare
sql_stmt varchar2(4000);
rec refcursor;
begin
IF p_trantype='AP'THEN -- Should execute below block
sql_stmt:= 'SELECT al.tax_rate_code AS taxcode,
al.amount AS netamount,
al.amount AS taxamount,
ai.invoice_date AS reportingdate,
ai.invoice_num AS invoicenumber,
ai.invoice_date AS invoicedate,
ai.invoice_amount AS grossamount,
ai.invoice_num AS documentnumber,
ai.invoice_date AS documentdate,
ai.vendor_id AS suplierid,
hz.tax_reference AS suppliervatnumber,
gl.segment1 AS companycode,
'AP' AS transactiontype
from...';
else
sql_stmt:=...
end if;
open rec for sql_stmt;
fetch rec into ...