パッケージの作成
あとで編集
・パッケージヘッダの定義
--パッケージ定義
CREATE OR REPLACE PACKAGE BOOKS_LEARNING AS
PROCEDURE HIGH_PRICE(
RET_PRICE_SUM OUT NUMBER,
RET_CD OUT NUMBER
);
PROCEDURE INSERT_WORK(
RET_CD OUT NUMBER
);
PROCEDURE MARGE_BOOK(
RET_CD OUT NUMBER
);
END;
パッケージボディの定義
--パッケージボディ定義
CREATE OR REPLACE PACKAGE BODY BOOKS_LEARNING AS
PROCEDURE HIGH_PRICE(
RET_PRICE_SUM OUT NUMBER,
RET_CD OUT NUMBER
) IS
CURSOR CUR001 IS
SELECT
URIMEI.BOOKID AS BOOKID,
URIMEI.SLIPNO AS SLIPNO,
URIMEI.PRICE AS PRICE
FROM
URIMEIDTL_TBL URIMEI
INNER JOIN
BOOK_MST BOOK
ON
URIMEI.SLIPNO = BOOK.CMNSLIPNO
AND URIMEI.BOOKID = BOOK.BOOKID
-- AND BOOK.BOOKID IN ('001014')
ORDER BY
URIMEI.BOOKID, URIMEI.SLIPNO;
PRICE_MAX NUMBER:=0;
BEGIN
-- DBMS_OUTPUT.PUT_LINE('TEST');
FOR URIMEI IN CUR001
LOOP
DBMS_OUTPUT.PUT_LINE('URIMEI : ' || URIMEI.BOOKID || ' ' || URIMEI.PRICE);
INSERT INTO LEARNING_WORK
VALUES (URIMEI.SLIPNO, URIMEI.PRICE, URIMEI.BOOKID,SYSDATE,'');
END LOOP;
END;
PROCEDURE INSERT_WORK(
RET_CD OUT NUMBER
) IS
PROCEDURE MARGE_BOOK(
RET_CD OUT NUMBER
) IS
CURSOR CUR002 IS
SELECT
URIMEI.SLIPNO AS SLIPNO,
URIMEI.BOOKNM AS BOOKNM,
URIMEI.PRICE AS PRICE
FROM
URIMEIDTL_TBL URIMEI
INNER JOIN
BOOK_MST BOOK
ON
URIMEI.SLIPNO = BOOK.CMNSLIPNO
AND
URIMEI.BOOKID = BOOK.BOOKID
ORDER BY
URIMEI.BOOKID, URIMEI.SLIPNO;
PRICE_MAX NUMBER:=0;
BEGIN
/*
FOR URIMEI IN CUR002
LOOP
INSERT INTO LEARNING_WORK
VALUES (URIMEI.SLIPNO, URIMEI.PRICE, URIMEI.BOOKNM, SYSDATE, SYSDATE );
END LOOP;
COMMIT;
*/
MERGE INTO LEARNING_WORK LW
USING
(
SELECT
UT.SLIPNO,UT.PRICE,UT.BOOKNM
FROM
URIMEIDTL_TBL UT
INNER JOIN
BOOK_MST BM
ON
UT.SLIPNO = BM.CMNSLIPNO
AND UT.BOOKID = BM.BOOKID
) UM
ON (UM.SLIPNO= LW.SLIPNO)
WHEN MATCHED THEN
UPDATE SET
PRICE = UM.PRICE,
BOOKNM = UM.BOOKNM,
UPD_DATE = SYSDATE
WHEN NOT MATCHED THEN
INSERT
( SLIPNO, PRICE, BOOKNM, INS_DATE, UPD_DATE )
VALUES
( UM.SLIPNO,
UM.PRICE,
UM.BOOKNM,
SYSDATE,
SYSDATE
);
END;
END;
・パッケージの実行
--パッケージ実行
DECLARE
RET_CD NUMBER:=0;
BEGIN
/*
BOOKS_LEARNING.HIGH_PRICE(RET_PRICE_SUM,RET_CD) ;
DBMS_OUTPUT.PUT_LINE(RET_PRICE_SUM) ;
DBMS_OUTPUT.PUT_LINE(RET_CD ) ;
*/
BOOKS_LEARNING.INSERT_WORK(RET_CD);
--BOOKS_LEARNING.MARGE_BOOK(RET_CD);
END;
【Webアプリ】 iTextによるPDF出力
あとで編集
・PDF作成サーブレット (データ取得部分は省略)
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
ByteArrayOutputStream bout = null;
PdfWriter writer = null;
Document doc = null;
BookDAO dao = null;
int lineCursor = 1;
int shoukei = 0;
int soukei = 0;
try {
dao = new BookDAO();
ArrayList
bout = new ByteArrayOutputStream();
doc = new Document(PageSize.A4, 50, 50, 50, 50);
writer = PdfWriter.getInstance(doc, bout);
Rectangle pageSize = doc.getPageSize();
writer.setBoxSize("art",
new Rectangle(36,50,
pageSize.getWidth() - 50,
pageSize.getHeight() - 36)
);
// ヘッダー・フッター設定
writer.setPageEvent(new PdfPageEventHelper() {
//ヘッダーのフォント
Font font_header = new Font(BaseFont.createFont(
"HeiseiKakuGo-W5", "UniJIS-UCS2-H",
BaseFont.NOT_EMBEDDED), 10, Font.ITALIC);
//フッターのフォント
Font font_page = new Font(BaseFont.createFont(
"HeiseiKakuGo-W5", "UniJIS-UCS2-H",
BaseFont.NOT_EMBEDDED), 9, Font.NORMAL);
@Override
public void onEndPage(PdfWriter writer, Document document) {
Rectangle rect = writer.getBoxSize("art");
//ヘッダー表示
Phrase header = new Phrase("書籍マスター一覧", font_header);
ColumnText.showTextAligned(writer.getDirectContent(),
Element.ALIGN_LEFT, header, rect.getLeft(),
rect.getTop(), 0);
SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy'年'MM'月'dd'日'");
Phrase headerDate = new Phrase(sdf1.format(new Date()), font_header);
ColumnText.showTextAligned(writer.getDirectContent(),
Element.ALIGN_RIGHT, headerDate, rect.getRight(),
rect.getTop(), 0);
//フッター(ページ)表示
Phrase page = new Phrase(String.format("- %d -",
writer.getPageNumber()), font_page);
ColumnText.showTextAligned(writer.getDirectContent(),
Element.ALIGN_CENTER, page,
(rect.getLeft() + rect.getRight()) / 2,
rect.getBottom(), 0);
}
});
doc.open();
/* 文字 */
BaseFont bf;
Font font;
// bf = BaseFont.createFont("HeiseiKakuGo-W5","UniJIS-UCS2-H",BaseFont.EMBEDDED);
// font = new Font(bf, 10);
bf = BaseFont.createFont("KozMinPro-Regular","UniJIS-UCS2-H",BaseFont.EMBEDDED);
font = new Font(bf, 10);
// 出力するPDFに説明を付与
doc.addAuthor("ウニウニコンピュータ");
doc.addSubject("書籍マスタ一覧");
// テーブルオブジェクトの作成
PdfPTable table = new PdfPTable(3); // 3列のテーブル
float[] columnWidths = {3, 14, 4}; // 列の幅
table.setWidths(columnWidths);
for( BookBean item : list ) {
//以下、すべてのif文を通すこと。else if でくくってはいけない
if(lineCursor % 52 == 1) {
// ヘッダ
PdfPCell hcell1 = new PdfPCell(new Paragraph("書籍番号", font));
PdfPCell hcell2 = new PdfPCell(new Paragraph("書籍名", font));
PdfPCell hcell3 = new PdfPCell(new Paragraph("金額", font));
hcell1.setHorizontalAlignment(Element.ALIGN_CENTER);
hcell2.setHorizontalAlignment(Element.ALIGN_CENTER);
hcell3.setHorizontalAlignment(Element.ALIGN_CENTER);
table.addCell(hcell1);
table.addCell(hcell2);
table.addCell(hcell3);
lineCursor++;
}
if(lineCursor % 52 != 0 && lineCursor % 52 != 1) {
//通常の行
PdfPCell cell1 = new PdfPCell(new Paragraph(item.getBookid(), font));
PdfPCell cell2 = new PdfPCell(new Paragraph(item.getBooknm(), font));
int tmpvalue = Integer.parseInt(item.getBookprice());
PdfPCell cell3 = new PdfPCell(new Paragraph(
String.format("%1$,3d", tmpvalue), font)
);
cell3.setHorizontalAlignment(Element.ALIGN_RIGHT);
table.addCell(cell1);
table.addCell(cell2);
table.addCell(cell3);
lineCursor++;
shoukei += Integer.parseInt(item.getBookprice());
soukei += Integer.parseInt(item.getBookprice());
}
if(lineCursor % 52 == 0) {
// 小計フッタ
Paragraph s_footer1 = new Paragraph("小計", font);
s_footer1.setAlignment(Element.ALIGN_CENTER);
PdfPCell fscell1 = new PdfPCell(s_footer1);
PdfPCell fscell2 = new PdfPCell(new Paragraph(String.format("%1$,3d", shoukei), font));
fscell1.setColspan(2);
fscell2.setHorizontalAlignment(Element.ALIGN_RIGHT);
table.addCell(fscell1);
table.addCell(fscell2);
shoukei = 0;
lineCursor++;
}
}
// 小計フッタ(ラスト)
Paragraph s_footer1 = new Paragraph("小計", font);
s_footer1.setAlignment(Element.ALIGN_CENTER);
PdfPCell fscell1 = new PdfPCell(s_footer1);
PdfPCell fscell2 = new PdfPCell(new Paragraph(String.format("%1$,3d", shoukei), font));
fscell1.setColspan(2);
fscell2.setHorizontalAlignment(Element.ALIGN_RIGHT);
table.addCell(fscell1);
table.addCell(fscell2);
// 総計フッタ
Paragraph footer1 = new Paragraph("総計", font);
footer1.setAlignment(Element.ALIGN_CENTER);
PdfPCell fcell1 = new PdfPCell(footer1);
PdfPCell fcell2 = new PdfPCell(new Paragraph(String.format("%1$,3d", soukei), font));
fcell1.setColspan(2);
fcell2.setHorizontalAlignment(Element.ALIGN_RIGHT);
table.addCell(fcell1);
table.addCell(fcell2);
// ドキュメントにテーブルを追加
Paragraph para1 = new Paragraph();
para1.add(table);
doc.add(para1);
} catch (NamingException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (DocumentException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
doc.close();
try {
dao.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
response.setContentType("application/pdf");
response.setContentLength(bout.size());
OutputStream out = response.getOutputStream();
out.write(bout.toByteArray());
out.close();
JavaでOracleのストアドプロシージャを実行
ソース抜粋
Connection con = null;
CallableStatement cs = null;
Class.forName ("oracle.jdbc.driver.OracleDriver");
try {
con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.20.22:1521:kokusei", "watanabe", "watanabe");
cs = con.prepareCall("call BOOKS_LEARNING.CALC_BOOKS_PRICE(?, ?, ?)");
cs.setString(1, "000000");
cs.registerOutParameter(2, java.sql.Types.INTEGER);
cs.registerOutParameter(3, java.sql.Types.INTEGER);
cs.execute();
sum = cs.getInt(2);
result = cs.getInt(3);
System.out.println(id + ":" + sum + "\t" + result);
cs.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}