ストアードプロシジャ作成スクリプト
0001: #! /bin/bash
0002: # ------------------------------------------------------------ #
0003: # CreateProcedureP.sh
0004: # ====================
0005: # Stored Procedure 作成スクリプトを起動
0006: #
0007: # CreateProcedureP.sh [options]
0008: # options :
0009: # [ -r | -d ] -d Procedure を削除する
0010: # -r Procedure を再作成する
0011: # [ encode={EUCJ_0208 | UTF8 } ] 標準 CHARACTER SET
0012: # 省略値は EUCJ_0208
0013: # [ dbpath=<dbpath> ] データベースのパス
0014: # [<hostname>]:<path>
0015: # 省略値は localhost:/DB/biords/birds.fdb
0016: # [ procedure=<procedure-name> ] プロシジャ名
0017: #
0018: #
0019: # $Id: CreateProcedureP.sh,v 1.4 ########## $
0020: # ------------------------------------------------------------ #
0021:
0022:
0023: # ------------------------------------------------------------ #
0024: # FUNCTION DROP PROCEDURE
0025: #
0026: # DropProcedure <dbpath-name> <procedure-name>
0027: # ------------------------------------------------------------ #
0028: function DropProcedure {
0029: dbpath=${1}
0030: procedure=${2}
0031: isql << EOF
0032: CONNECT "${dbpath}" USER "SYSDBA" PASSWORD "********";
0033: DROP PROCEDURE ${procedure};
0034: COMMIT;
0035: QUIT;
0036: EOF
0037: }
0038:
0039:
0040: # ------------------------------------------------------------ #
0041: # FUNCTION CREATE PROCEDURE PS_CHKINSERT
0042: #
0043: # CreatePS_CHKINSERT <dbpath-name> <encode>
0044: # ------------------------------------------------------------ #
0045: function CreatePS_CHKINSERT {
0046: dbpath=${1}
0047: encode=${2}
0048: echo '**** CREATE PS_CHKINSERT ****'
0049: isql << EOF
0050: CONNECT "${dbpath}" USER "SYSDBA" PASSWORD "********";
0051: /**************************************************************/
0052: /* PROCEDURE : PS_CHKINSERT */
0053: /* */
0054: /* テーブル SPECIES の行の存在を検査し */
0055: /* 存在していなければ */
0056: /* INSERT する RETVAL=1 */
0057: /* 存在していれば */
0058: /* パラメータ UPDT が 0 ならば */
0059: /* 何もしない RETVAL=0 */
0060: /* パラメータ UPDT が 1 ならば */
0061: /* UPDATE する RETVAL=0 */
0062: /* */
0063: /**************************************************************/
0064: SET NAMES ${encode};
0065: SET TERM !! ;
0066: CREATE PROCEDURE PS_CHKINSERT(
0067: UPDT INTEGER,
0068: GENUSNAME NCHAR VARYING(24),
0069: SPECIESNAME NCHAR VARYING(40),
0070: SPECIESJPN CHAR VARYING(24),
0071: SPECIESENG NCHAR VARYING(40),
0072: SPECIESENGCAP NCHAR VARYING(40)
0073: )
0074: RETURNS(
0075: RETVAL INTEGER
0076: )
0077: AS
0078: BEGIN
0079: IF ( EXISTS ( SELECT SPECIESNAME FROM SPECIES
0080: WHERE SPECIESNAME=:SPECIESNAME ) ) THEN
0081: BEGIN
0082: IF ( UPDT = 1 ) THEN
0083: UPDATE SPECIES SET
0084: GENUSNAME=:GENUSNAME,
0085: SPECIESJPN=:SPECIESJPN,
0086: SPECIESENG=:SPECIESENG,
0087: SPECIESENGCAP=:SPECIESENGCAP
0088: WHERE
0089: SPECIESNAME=:SPECIESNAME;
0090: RETVAL=0;
0091: END
0092: ELSE
0093: BEGIN
0094: INSERT INTO SPECIES(
0095: GENUSNAME, SPECIESNAME, SPECIESJPN, SPECIESENG, SPECIESENGCAP )
0096: VALUES (
0097: :GENUSNAME,:SPECIESNAME,:SPECIESJPN,:SPECIESENG,:SPECIESENGCAP );
0098: RETVAL=1;
0099: END
0100: END;
0101: !!
0102: SET TERM ; !!
0103: GRANT EXECUTE ON PROCEDURE PS_CHKINSERT TO PUBLIC;
0104: COMMIT;
0105: QUIT;
0106: EOF
0107: }
0108:
0109:
0110: # ------------------------------------------------------------ #
0111: # FUNCTION CREATE PROCEDURE PS_N_SGFO
0112: #
0113: # CreatePS_N_SGFO <dbpath-name> <encode>
0114: # ------------------------------------------------------------ #
0115: function CreatePS_N_SGFO {
0116: dbpath=${1}
0117: encode=${2}
0118: echo '**** CREATE PS_N_SGFO ****'
0119: isql << EOF
0120: CONNECT "${dbpath}" USER "SYSDBA" PASSWORD "********";
0121: /**************************************************************/
0122: /* PROCEDURE : PS_N_SGFO */
0123: /* */
0124: /* 種学名 SPECIESNAME をキーに */
0125: /* テーブル SPECIES,GENUS,FAMILY,ORDER を連結して単一行を返す */
0126: /* */
0127: /**************************************************************/
0128: SET NAMES ${encode};
0129: SET TERM !! ;
0130: CREATE PROCEDURE PS_N_SGFO(
0131: SPECIESNAMEIN NCHAR VARYING(40)
0132: )
0133: RETURNS(
0134: SEQNO INTEGER,
0135: ORDERNAME NCHAR VARYING(24),
0136: ORDERJPN CHAR VARYING(12),
0137: FAMILYNAME NCHAR VARYING(24),
0138: FAMILYJPN CHAR VARYING(20),
0139: GENUSNAME NCHAR VARYING(24),
0140: GENUSJPN CHAR VARYING(12),
0141: SPECIESNAME NCHAR VARYING(40),
0142: SPECIESJPN CHAR VARYING(24),
0143: SPECIESENG NCHAR VARYING(40)
0144: )
0145: AS
0146: BEGIN
0147: SELECT SEQNO, GENUSNAME, SPECIESNAME, SPECIESJPN, SPECIESENG
0148: FROM SPECIES
0149: WHERE SPECIESNAME=:SPECIESNAMEIN
0150: INTO :SEQNO,:GENUSNAME, :SPECIESNAME, :SPECIESJPN, :SPECIESENG;
0151: SELECT FAMILYNAME, GENUSJPN
0152: FROM GENUS
0153: WHERE GENUSNAME=:GENUSNAME
0154: INTO :FAMILYNAME, :GENUSJPN;
0155: SELECT ORDERNAME, FAMILYJPN
0156: FROM FAMILY
0157: WHERE FAMILYNAME=:FAMILYNAME
0158: INTO :ORDERNAME, :FAMILYJPN;
0159: SELECT ORDERJPN
0160: FROM ORDERTAB
0161: WHERE ORDERNAME=:ORDERNAME
0162: INTO :ORDERJPN;
0163: END;
0164: !!
0165: SET TERM ; !!
0166: GRANT EXECUTE ON PROCEDURE PS_N_SGFO TO PUBLIC;
0167: COMMIT;
0168: QUIT;
0169: EOF
0170: }
0171:
0172:
0173: # ------------------------------------------------------------ #
0174: # FUNCTION CREATE PROCEDURE PS_J_SGFO
0175: #
0176: # CreatePS_J_SGFO <dbpath-name> <encode>
0177: # ------------------------------------------------------------ #
0178: function CreatePS_J_SGFO {
0179: dbpath=${1}
0180: encode=${2}
0181: echo '**** CREATE PS_J_SGFO ****'
0182: isql << EOF
0183: CONNECT "${dbpath}" USER "SYSDBA" PASSWORD "********";
0184: /**************************************************************/
0185: /* PROCEDURE : PS_J_SGFO */
0186: /* */
0187: /* 種の和名 SPECIESJPN をキーに */
0188: /* テーブル SPECIES,GENUS,FAMILY,ORDER を連結して単一行を返す */
0189: /* */
0190: /**************************************************************/
0191: SET NAMES ${encode};
0192: SET TERM !! ;
0193: CREATE PROCEDURE PS_J_SGFO(
0194: SPECIESJPNIN CHAR VARYING(40)
0195: )
0196: RETURNS(
0197: SEQNO INTEGER,
0198: ORDERNAME NCHAR VARYING(24),
0199: ORDERJPN CHAR VARYING(12),
0200: FAMILYNAME NCHAR VARYING(24),
0201: FAMILYJPN CHAR VARYING(20),
0202: GENUSNAME NCHAR VARYING(24),
0203: GENUSJPN CHAR VARYING(12),
0204: SPECIESNAME NCHAR VARYING(40),
0205: SPECIESJPN CHAR VARYING(24),
0206: SPECIESENG NCHAR VARYING(40)
0207: )
0208: AS
0209: BEGIN
0210: SELECT SEQNO, GENUSNAME, SPECIESNAME, SPECIESJPN, SPECIESENG
0211: FROM SPECIES
0212: WHERE SPECIESJPN=:SPECIESJPNIN
0213: INTO :SEQNO,:GENUSNAME,:SPECIESNAME,:SPECIESJPN,:SPECIESENG;
0214: SELECT FAMILYNAME, GENUSJPN
0215: FROM GENUS
0216: WHERE GENUSNAME=:GENUSNAME
0217: INTO :FAMILYNAME,:GENUSJPN;
0218: SELECT ORDERNAME, FAMILYJPN
0219: FROM FAMILY
0220: WHERE FAMILYNAME=:FAMILYNAME
0221: INTO :ORDERNAME,:FAMILYJPN;
0222: SELECT ORDERJPN
0223: FROM ORDERTAB
0224: WHERE ORDERNAME=:ORDERNAME
0225: INTO :ORDERJPN;
0226: END;
0227: !!
0228: SET TERM ; !!
0229: GRANT EXECUTE ON PROCEDURE PS_J_SGFO TO PUBLIC;
0230: COMMIT;
0231: QUIT;
0232: EOF
0233: }
0234:
0235:
0236: # ------------------------------------------------------------ #
0237: # FUNCTION CREATE PROCEDURE PS_E_SGFO
0238: #
0239: # CreatePS_E_SGFO <dbpath-name> <encode>
0240: # ------------------------------------------------------------ #
0241: function CreatePS_E_SGFO {
0242: dbpath=${1}
0243: encode=${2}
0244: echo '**** CREATE PS_E_SGFO ****'
0245: isql << EOF
0246: CONNECT "${dbpath}" USER "SYSDBA" PASSWORD "********";
0247: /**************************************************************/
0248: /* PROCEDURE : PS_E_SGFO */
0249: /* */
0250: /* 種の英名 SPECIESENG をキーに */
0251: /* テーブル SPECIES, GENUS, FAMILY, ORDER を連結して返す */
0252: /* */
0253: /**************************************************************/
0254: SET NAMES ${encode};
0255: SET TERM !! ;
0256: CREATE PROCEDURE PS_E_SGFO(
0257: SPECIESENGIN NCHAR VARYING(40)
0258: )
0259: RETURNS(
0260: SEQNO INTEGER,
0261: ORDERNAME NCHAR VARYING(24),
0262: ORDERJPN CHAR VARYING(12),
0263: FAMILYNAME NCHAR VARYING(24),
0264: FAMILYJPN CHAR VARYING(20),
0265: GENUSNAME NCHAR VARYING(24),
0266: GENUSJPN CHAR VARYING(12),
0267: SPECIESNAME NCHAR VARYING(40),
0268: SPECIESJPN CHAR VARYING(24),
0269: SPECIESENG NCHAR VARYING(40)
0270: )
0271: AS
0272: BEGIN
0273: SELECT SEQNO, GENUSNAME, SPECIESNAME, SPECIESJPN, SPECIESENG
0274: FROM SPECIES
0275: WHERE UPPER(SPECIESENG)=:SPECIESENGIN
0276: INTO :SEQNO,:GENUSNAME,:SPECIESNAME,:SPECIESJPN,:SPECIESENG;
0277: SELECT FAMILYNAME, GENUSJPN
0278: FROM GENUS
0279: WHERE GENUSNAME=:GENUSNAME
0280: INTO :FAMILYNAME,:GENUSJPN;
0281: SELECT ORDERNAME, FAMILYJPN
0282: FROM FAMILY
0283: WHERE FAMILYNAME=:FAMILYNAME
0284: INTO :ORDERNAME,:FAMILYJPN;
0285: SELECT ORDERJPN
0286: FROM ORDERTAB
0287: WHERE ORDERNAME=:ORDERNAME
0288: INTO :ORDERJPN;
0289: END;
0290: !!
0291: SET TERM ; !!
0292: GRANT EXECUTE ON PROCEDURE PS_E_SGFO TO PUBLIC;
0293: COMMIT;
0294: QUIT;
0295: EOF
0296: }
0297:
0298:
0299: # ------------------------------------------------------------ #
0300: # FUNCTION CREATE PROCEDURE PS_ECAP_SGFO
0301: #
0302: # CreatePS_ECAP_SGFO <dbpath-name> <encode>
0303: # ------------------------------------------------------------ #
0304: function CreatePS_ECAP_SGFO {
0305: dbpath=${1}
0306: encode=${2}
0307: echo '**** CREATE PS_ECAP_SGFO ****'
0308: isql << EOF
0309: CONNECT "${dbpath}" USER "SYSDBA" PASSWORD "********";
0310: /**************************************************************/
0311: /* PROCEDURE : PS_ECAP_SGFO */
0312: /* */
0313: /* 種の英名の大文字項目 SPECIESENGCAP をキーに */
0314: /* テーブル SPECIES, GENUS, FAMILY, ORDER を連結して返す */
0315: /* */
0316: /**************************************************************/
0317: SET NAMES ${encode};
0318: SET TERM !! ;
0319: CREATE PROCEDURE PS_ECAP_SGFO(
0320: SPECIESENGCAPIN NCHAR VARYING(40)
0321: )
0322: RETURNS(
0323: SEQNO INTEGER,
0324: ORDERNAME NCHAR VARYING(24),
0325: ORDERJPN CHAR VARYING(12),
0326: FAMILYNAME NCHAR VARYING(24),
0327: FAMILYJPN CHAR VARYING(20),
0328: GENUSNAME NCHAR VARYING(24),
0329: GENUSJPN CHAR VARYING(12),
0330: SPECIESNAME NCHAR VARYING(40),
0331: SPECIESJPN CHAR VARYING(24),
0332: SPECIESENG NCHAR VARYING(40)
0333: )
0334: AS
0335: BEGIN
0336: SELECT SEQNO, GENUSNAME, SPECIESNAME, SPECIESJPN, SPECIESENG
0337: FROM SPECIES
0338: WHERE SPECIESENGCAP=:SPECIESENGCAPIN
0339: INTO :SEQNO,:GENUSNAME,:SPECIESNAME,:SPECIESJPN,:SPECIESENG;
0340: SELECT FAMILYNAME, GENUSJPN
0341: FROM GENUS
0342: WHERE GENUSNAME=:GENUSNAME
0343: INTO :FAMILYNAME,:GENUSJPN;
0344: SELECT ORDERNAME, FAMILYJPN
0345: FROM FAMILY
0346: WHERE FAMILYNAME=:FAMILYNAME
0347: INTO :ORDERNAME,:FAMILYJPN;
0348: SELECT ORDERJPN
0349: FROM ORDERTAB
0350: WHERE ORDERNAME=:ORDERNAME
0351: INTO :ORDERJPN;
0352: END;
0353: !!
0354: SET TERM ; !!
0355: GRANT EXECUTE ON PROCEDURE PS_ECAP_SGFO TO PUBLIC;
0356: COMMIT;
0357: QUIT;
0358: EOF
0359: }
0360:
0361:
0362: # ------------------------------------------------------------ #
0363: # FUNCTION CREATE PROCEDURE PS_NLIKE_SGFO
0364: #
0365: # CreatePS_NLIKE_SGFO <dbpath-name> <encode>
0366: # ------------------------------------------------------------ #
0367: function CreatePS_NLIKE_SGFO {
0368: dbpath=${1}
0369: encode=${2}
0370: echo '**** CREATE PS_NLIKE_SGFO ****'
0371: isql << EOF
0372: CONNECT "${dbpath}" USER "SYSDBA" PASSWORD "********";
0373: /**************************************************************/
0374: /* PROCEDURE : PS_NLIKE_SGFO */
0375: /* */
0376: /* 種の学名 SPECIESNAME を部分一致検索し */
0377: /* テーブル SPECIES,GENUS,FAMILY,ORDER を連結して複数行を返す */
0378: /* */
0379: /**************************************************************/
0380: SET NAMES ${encode};
0381: SET TERM !! ;
0382: CREATE PROCEDURE PS_NLIKE_SGFO(
0383: SPECIESNPART NCHAR VARYING(40)
0384: )
0385: RETURNS(
0386: SEQNO INTEGER,
0387: ORDERNAME NCHAR VARYING(24),
0388: ORDERJPN VARCHAR (12),
0389: FAMILYNAME NCHAR VARYING(24),
0390: FAMILYJPN VARCHAR (20),
0391: GENUSNAME NCHAR VARYING(24),
0392: GENUSJPN VARCHAR (12),
0393: SPECIESNAME NCHAR VARYING(40),
0394: SPECIESJPN VARCHAR (24),
0395: SPECIESENG NCHAR VARYING(40)
0396: )
0397: AS
0398: DECLARE VARIABLE SGENUSNAME NCHAR VARYING(24);
0399: DECLARE VARIABLE GFAMILYNAME NCHAR VARYING(24);
0400: DECLARE VARIABLE FORDERNAME NCHAR VARYING(24);
0401: BEGIN
0402: GENUSNAME='';
0403: FAMILYNAME='';
0404: ORDERNAME='';
0405: FOR
0406: SELECT SEQNO, GENUSNAME, SPECIESNAME, SPECIESJPN, SPECIESENG
0407: FROM SPECIES
0408: WHERE UPPER(SPECIESNAME) LIKE :SPECIESNPART AND SEQNO<9947
0409: INTO :SEQNO,:SGENUSNAME,:SPECIESNAME,:SPECIESJPN,:SPECIESENG
0410: DO
0411: BEGIN
0412: IF (SGENUSNAME<>GENUSNAME) THEN
0413: BEGIN
0414: SELECT GENUSNAME, FAMILYNAME, GENUSJPN
0415: FROM GENUS
0416: WHERE GENUSNAME=:SGENUSNAME
0417: INTO :GENUSNAME,:GFAMILYNAME,:GENUSJPN;
0418: IF (GFAMILYNAME<>FAMILYNAME) THEN
0419: BEGIN
0420: SELECT ORDERNAME, FAMILYNAME, FAMILYJPN
0421: FROM FAMILY
0422: WHERE FAMILYNAME=:GFAMILYNAME
0423: INTO :FORDERNAME, :FAMILYNAME, :FAMILYJPN;
0424: IF (FORDERNAME<>ORDERNAME) THEN
0425: SELECT ORDERNAME,ORDERJPN
0426: FROM ORDERTAB
0427: WHERE ORDERNAME=:FORDERNAME
0428: INTO :ORDERNAME,:ORDERJPN;
0429: END
0430: END
0431: SUSPEND;
0432: END
0433: END;
0434: !!
0435: SET TERM ; !!
0436: GRANT EXECUTE ON PROCEDURE PS_NLIKE_SGFO TO PUBLIC;
0437: COMMIT;
0438: QUIT;
0439: EOF
0440: }
0441:
0442:
0443: # ------------------------------------------------------------ #
0444: # FUNCTION CREATE PROCEDURE PS_JLIKE_SGFO
0445: #
0446: # CreatePS_JLIKE_SGFO <dbpath-name> <encode>
0447: # ------------------------------------------------------------ #
0448: function CreatePS_JLIKE_SGFO {
0449: dbpath=${1}
0450: encode=${2}
0451: echo '**** CREATE PS_JLIKE_SGFO ****'
0452: isql << EOF
0453: CONNECT "${dbpath}" USER "SYSDBA" PASSWORD "********";
0454: /**************************************************************/
0455: /* PROCEDURE : PS_JLIKE_SGFO */
0456: /* */
0457: /* 種の和名 SPECIESJPN を部分一致検索し */
0458: /* テーブル SPECIES,GENUS,FAMILY,ORDER を連結して単一行を返す */
0459: /* */
0460: /**************************************************************/
0461: SET NAMES ${encode};
0462: SET TERM !! ;
0463: CREATE PROCEDURE PS_JLIKE_SGFO(
0464: SPECIESJPART VARCHAR(20)
0465: )
0466: RETURNS(
0467: SEQNO INTEGER,
0468: ORDERNAME NCHAR VARYING(24),
0469: ORDERJPN VARCHAR (12),
0470: FAMILYNAME NCHAR VARYING(24),
0471: FAMILYJPN VARCHAR (20),
0472: GENUSNAME NCHAR VARYING(24),
0473: GENUSJPN VARCHAR (12),
0474: SPECIESNAME NCHAR VARYING(40),
0475: SPECIESJPN VARCHAR (24),
0476: SPECIESENG NCHAR VARYING(40)
0477: )
0478: AS
0479: DECLARE VARIABLE SGENUSNAME NCHAR VARYING(24);
0480: DECLARE VARIABLE GFAMILYNAME NCHAR VARYING(24);
0481: DECLARE VARIABLE FORDERNAME NCHAR VARYING(24);
0482: BEGIN
0483: GENUSNAME='';
0484: FAMILYNAME='';
0485: ORDERNAME='';
0486: FOR
0487: SELECT SEQNO,GENUSNAME, SPECIESNAME, SPECIESJPN, SPECIESENG
0488: FROM SPECIES
0489: WHERE SPECIESJPN LIKE :SPECIESJPART AND SEQNO<9947
0490: /*** ORDER BY SEQNO ***/
0491: INTO :SEQNO,:SGENUSNAME,:SPECIESNAME,:SPECIESJPN,:SPECIESENG
0492: DO
0493: BEGIN
0494: IF (SGENUSNAME<>GENUSNAME) THEN
0495: BEGIN
0496: SELECT GENUSNAME, FAMILYNAME, GENUSJPN
0497: FROM GENUS
0498: WHERE GENUSNAME=:SGENUSNAME
0499: INTO :GENUSNAME,:GFAMILYNAME,:GENUSJPN;
0500: IF (GFAMILYNAME<>FAMILYNAME) THEN
0501: BEGIN
0502: SELECT ORDERNAME, FAMILYNAME, FAMILYJPN
0503: FROM FAMILY
0504: WHERE FAMILYNAME=:GFAMILYNAME
0505: INTO :FORDERNAME, :FAMILYNAME, :FAMILYJPN;
0506: IF (FORDERNAME<>ORDERNAME) THEN
0507: SELECT ORDERNAME,ORDERJPN
0508: FROM ORDERTAB
0509: WHERE ORDERNAME=:FORDERNAME
0510: INTO :ORDERNAME,:ORDERJPN;
0511: END
0512: END
0513: SUSPEND;
0514: END
0515: END;
0516: !!
0517: SET TERM ; !!
0518: GRANT EXECUTE ON PROCEDURE PS_JLIKE_SGFO TO PUBLIC;
0519: COMMIT;
0520: QUIT;
0521: EOF
0522: }
0523:
0524:
0525: # ------------------------------------------------------------ #
0526: # FUNCTION CREATE PROCEDURE PS_ELIKE_SGFO
0527: #
0528: # CreatePS_ELIKE_SGFO <dbpath-name> <encode>
0529: # ------------------------------------------------------------ #
0530: function CreatePS_ELIKE_SGFO {
0531: dbpath=${1}
0532: encode=${2}
0533: echo '**** CREATE PS_ELIKE_SGFO ****'
0534: isql << EOF
0535: CONNECT "${dbpath}" USER "SYSDBA" PASSWORD "********";
0536: /**************************************************************/
0537: /* PROCEDURE : PS_ELIKE_SGFO */
0538: /* */
0539: /* 種の英名 SPECIESENG を部分一致検索し */
0540: /* テーブル SPECIES,GENUS,FAMILY,ORDER を連結して複数行を返す */
0541: /* */
0542: /**************************************************************/
0543: SET NAMES ${encode};
0544: SET TERM !! ;
0545: CREATE PROCEDURE PS_ELIKE_SGFO(
0546: SPECIESEPART NCHAR VARYING(40)
0547: )
0548: RETURNS(
0549: SEQNO INTEGER,
0550: ORDERNAME NCHAR VARYING(24),
0551: ORDERJPN VARCHAR (12),
0552: FAMILYNAME NCHAR VARYING(24),
0553: FAMILYJPN VARCHAR (20),
0554: GENUSNAME NCHAR VARYING(24),
0555: GENUSJPN VARCHAR (12),
0556: SPECIESNAME NCHAR VARYING(40),
0557: SPECIESJPN VARCHAR (24),
0558: SPECIESENG NCHAR VARYING(40)
0559: )
0560: AS
0561: DECLARE VARIABLE SGENUSNAME NCHAR VARYING(24);
0562: DECLARE VARIABLE GFAMILYNAME NCHAR VARYING(24);
0563: DECLARE VARIABLE FORDERNAME NCHAR VARYING(24);
0564: BEGIN
0565: GENUSNAME='';
0566: FAMILYNAME='';
0567: ORDERNAME='';
0568: FOR
0569: SELECT SEQNO, GENUSNAME, SPECIESNAME, SPECIESJPN, SPECIESENG
0570: FROM SPECIES
0571: WHERE UPPER(SPECIESENG) LIKE :SPECIESEPART AND SEQNO<9947
0572: INTO :SEQNO,:SGENUSNAME,:SPECIESNAME,:SPECIESJPN,:SPECIESENG
0573: DO
0574: BEGIN
0575: IF (SGENUSNAME<>GENUSNAME) THEN
0576: BEGIN
0577: SELECT GENUSNAME, FAMILYNAME, GENUSJPN
0578: FROM GENUS
0579: WHERE GENUSNAME=:SGENUSNAME
0580: INTO :GENUSNAME,:GFAMILYNAME,:GENUSJPN;
0581: IF (GFAMILYNAME<>FAMILYNAME) THEN
0582: BEGIN
0583: SELECT ORDERNAME, FAMILYNAME, FAMILYJPN
0584: FROM FAMILY
0585: WHERE FAMILYNAME=:GFAMILYNAME
0586: INTO :FORDERNAME, :FAMILYNAME, :FAMILYJPN;
0587: IF (FORDERNAME<>ORDERNAME) THEN
0588: SELECT ORDERNAME,ORDERJPN
0589: FROM ORDERTAB
0590: WHERE ORDERNAME=:FORDERNAME
0591: INTO :ORDERNAME,:ORDERJPN;
0592: END
0593: END
0594: SUSPEND;
0595: END
0596: END;
0597: !!
0598: SET TERM ; !!
0599: GRANT EXECUTE ON PROCEDURE PS_ELIKE_SGFO TO PUBLIC;
0600: COMMIT;
0601: QUIT;
0602: EOF
0603: }
0604:
0605:
0606: # ------------------------------------------------------------ #
0607: # FUNCTION CREATE PROCEDURE PG_CHKINSERT
0608: #
0609: # CreatePG_CHKINSERT <dbpath-name> <encode>
0610: # ------------------------------------------------------------ #
0611: function CreatePG_CHKINSERT {
0612: dbpath=${1}
0613: encode=${2}
0614: echo '**** CREATE PG_CHKINSERT ****'
0615: isql <<EOF
0616: CONNECT "${dbpath}" USER "SYSDBA" PASSWORD "********";
0617: /**************************************************************/
0618: /* PROCEDURE : PG_CHKINSERT */
0619: /* */
0620: /* テーブル GENUS の行の存在を検査し */
0621: /* 存在していなければ */
0622: /* INSERT する RETVAL=1 */
0623: /* 存在していれば */
0624: /* パラメータ UPDT が 0 ならば */
0625: /* 何もしない RETVAL=0 */
0626: /* パラメータ UPDT が 1 ならば */
0627: /* UPDATE する RETVAL=0 */
0628: /* */
0629: /**************************************************************/
0630: SET NAMES ${encode};
0631: SET TERM !! ;
0632: CREATE PROCEDURE PG_CHKINSERT(
0633: UPDT INTEGER,
0634: FAMILYNAME NCHAR VARYING(24),
0635: GENUSNAME NCHAR VARYING(24),
0636: GENUSJPN CHAR VARYING(12)
0637: )
0638: RETURNS(
0639: RETVAL INTEGER
0640: )
0641: AS
0642: BEGIN
0643: IF (EXISTS( SELECT GENUSNAME FROM GENUS
0644: WHERE GENUSNAME=:GENUSNAME ) )
0645: THEN
0646: BEGIN
0647: IF (UPDT=1) THEN
0648: UPDATE GENUS SET
0649: GENUSJPN=:GENUSJPN
0650: WHERE
0651: GENUSNAME=:GENUSNAME;
0652: RETVAL=0;
0653: END
0654: ELSE
0655: BEGIN
0656: INSERT INTO GENUS(
0657: FAMILYNAME, GENUSNAME, GENUSJPN )
0658: VALUES (
0659: :FAMILYNAME, :GENUSNAME, :GENUSJPN );
0660: RETVAL=1;
0661: END
0662: END;
0663: !!
0664: SET TERM ; !!
0665: GRANT EXECUTE ON PROCEDURE PG_CHKINSERT TO PUBLIC;
0666: COMMIT;
0667: QUIT;
0668: EOF
0669: }
0670:
0671:
0672: # ------------------------------------------------------------ #
0673: # FUNCTION CREATE PROCEDURE PG_LIST
0674: #
0675: # CreatePG_LIST <dbpath-name> <encode>
0676: # ------------------------------------------------------------ #
0677: function CreatePG_LIST {
0678: dbpath=${1}
0679: encode=${2}
0680: echo '**** CREATE PG_LIST ****'
0681: isql << EOF
0682: CONNECT "${dbpath}" USER "SYSDBA" PASSWORD "********";
0683: /*****************************************************************/
0684: /* PROCEDURE : PG_LIST */
0685: /* */
0686: /* 属の全リストを返す */
0687: /* */
0688: /*****************************************************************/
0689: SET NAMES ${encode};
0690: SET TERM !! ;
0691: CREATE PROCEDURE PG_LIST
0692: RETURNS(
0693: SEQNO INTEGER,
0694: ORDERNAME NCHAR VARYING(24),
0695: ORDERJPN CHAR VARYING(12),
0696: FAMILYNAME NCHAR VARYING(24),
0697: FAMILYJPN CHAR VARYING(20),
0698: GENUSNAME NCHAR VARYING(24),
0699: GENUSJPN CHAR VARYING(12)
0700: )
0701: AS
0702: BEGIN
0703: FOR
0704: SELECT ORDERNAME, ORDERJPN
0705: FROM ORDERTAB WHERE SEQNO<24
0706: ORDER BY SEQNO
0707: INTO :ORDERNAME, :ORDERJPN
0708: DO
0709: FOR
0710: SELECT FAMILYNAME, FAMILYJPN
0711: FROM FAMILY
0712: WHERE ORDERNAME=:ORDERNAME
0713: ORDER BY SEQNO
0714: INTO :FAMILYNAME, :FAMILYJPN
0715: DO
0716: FOR
0717: SELECT SEQNO, GENUSNAME, GENUSJPN
0718: FROM GENUS
0719: WHERE FAMILYNAME=:FAMILYNAME
0720: ORDER BY SEQNO
0721: INTO :SEQNO, :GENUSNAME, :GENUSJPN
0722: DO
0723: SUSPEND;
0724: END;
0725: !!
0726: SET TERM ; !!
0727: GRANT EXECUTE ON PROCEDURE PG_LIST TO PUBLIC;
0728: COMMIT;
0729: QUIT;
0730: EOF
0731: }
0732:
0733:
0734: # ------------------------------------------------------------ #
0735: # FUNCTION CREATE PROCEDURE PG_N_SGFO
0736: #
0737: # CreatePG_N_SGFO <dbpath-name> <encode>
0738: # ------------------------------------------------------------ #
0739: function CreatePG_N_SGFO {
0740: dbpath=${1}
0741: encode=${2}
0742: echo '**** CREATE PG_N_SGFO ****'
0743: isql << EOF
0744: CONNECT "${dbpath}" USER "SYSDBA" PASSWORD "********";
0745: /*****************************************************************/
0746: /* PROCEDURE : PG_N_SGFO */
0747: /* */
0748: /* 属の学名 GENUSNAME をキーに */
0749: /* テーブル SPECIES, GENUS, FAMILY, ORDER を連結して複数行を返す */
0750: /* */
0751: /*****************************************************************/
0752: SET NAMES ${encode};
0753: SET TERM !! ;
0754: CREATE PROCEDURE PG_N_SGFO(
0755: GENUSNAMEIN NCHAR VARYING(24)
0756: )
0757: RETURNS(
0758: SEQNO INTEGER,
0759: ORDERNAME NCHAR VARYING(24),
0760: ORDERJPN CHAR VARYING(12),
0761: FAMILYNAME NCHAR VARYING(24),
0762: FAMILYJPN CHAR VARYING(20),
0763: GENUSNAME NCHAR VARYING(24),
0764: GENUSJPN CHAR VARYING(12),
0765: SPECIESNAME NCHAR VARYING(40),
0766: SPECIESJPN CHAR VARYING(24),
0767: SPECIESENG NCHAR VARYING(40)
0768: )
0769: AS
0770: BEGIN
0771: SELECT GENUSNAME, GENUSJPN, FAMILYNAME
0772: FROM GENUS
0773: WHERE GENUSNAME=:GENUSNAMEIN
0774: INTO :GENUSNAME, :GENUSJPN, :FAMILYNAME;
0775: SELECT ORDERNAME, FAMILYJPN
0776: FROM FAMILY
0777: WHERE FAMILYNAME=:FAMILYNAME
0778: INTO :ORDERNAME, :FAMILYJPN;
0779: SELECT ORDERJPN
0780: FROM ORDERTAB
0781: WHERE ORDERNAME=:ORDERNAME
0782: INTO :ORDERJPN;
0783: FOR
0784: SELECT SEQNO, SPECIESNAME, SPECIESJPN, SPECIESENG
0785: FROM SPECIES
0786: WHERE GENUSNAME=:GENUSNAME
0787: ORDER BY SEQNO
0788: INTO :SEQNO, :SPECIESNAME, :SPECIESJPN, :SPECIESENG
0789: DO
0790: SUSPEND;
0791: END;
0792: !!
0793: SET TERM ; !!
0794: GRANT EXECUTE ON PROCEDURE PG_N_SGFO TO PUBLIC;
0795: COMMIT;
0796: QUIT;
0797: EOF
0798: }
0799:
0800: #-----------------------------------------------------------------------------
0801: # ------------------------------------------------------------ #
0802: # FUNCTION CREATE PROCEDURE PG_J_SGFO
0803: #
0804: # CreatePG_J_SGFO <dbpath-name> <encode>
0805: # ------------------------------------------------------------ #
0806: function CreatePG_J_SGFO {
0807: dbpath=${1}
0808: encode=${2}
0809: echo '**** CREATE PG_J_SGFO ****'
0810: isql << EOF
0811: CONNECT "${dbpath}" USER "SYSDBA" PASSWORD "********";
0812: /*****************************************************************/
0813: /* PROCEDURE : PG_J_SGFO */
0814: /* */
0815: /* 属の和名 GENUSJPN をキーに */
0816: /* テーブル SPECIES, GENUS, FAMILY, ORDER を連結して複数行を返す */
0817: /* */
0818: /*****************************************************************/
0819: SET NAMES ${encode};
0820: SET TERM !! ;
0821: CREATE PROCEDURE PG_J_SGFO(
0822: GENUSJPNIN CHAR VARYING(12)
0823: )
0824: RETURNS(
0825: SEQNO INTEGER,
0826: ORDERNAME NCHAR VARYING(24),
0827: ORDERJPN CHAR VARYING(12),
0828: FAMILYNAME NCHAR VARYING(24),
0829: FAMILYJPN CHAR VARYING(20),
0830: GENUSNAME NCHAR VARYING(24),
0831: GENUSJPN CHAR VARYING(12),
0832: SPECIESNAME NCHAR VARYING(40),
0833: SPECIESJPN CHAR VARYING(24),
0834: SPECIESENG NCHAR VARYING(40)
0835: )
0836: AS
0837: BEGIN
0838: SELECT GENUSNAME, GENUSJPN, FAMILYNAME
0839: FROM GENUS
0840: WHERE GENUSJPN=:GENUSJPNIN
0841: INTO :GENUSNAME, :GENUSJPN, :FAMILYNAME;
0842: SELECT ORDERNAME, FAMILYJPN
0843: FROM FAMILY
0844: WHERE FAMILYNAME=:FAMILYNAME
0845: INTO :ORDERNAME, :FAMILYJPN;
0846: SELECT ORDERJPN
0847: FROM ORDERTAB
0848: WHERE ORDERNAME=:ORDERNAME
0849: INTO :ORDERJPN;
0850: FOR
0851: SELECT SEQNO, SPECIESNAME, SPECIESJPN, SPECIESENG
0852: FROM SPECIES
0853: WHERE GENUSNAME=:GENUSNAME
0854: ORDER BY SEQNO
0855: INTO :SEQNO, :SPECIESNAME, :SPECIESJPN, :SPECIESENG
0856: DO
0857: SUSPEND;
0858: END;
0859: !!
0860: SET TERM ; !!
0861: GRANT EXECUTE ON PROCEDURE PG_J_SGFO TO PUBLIC;
0862: COMMIT;
0863: QUIT;
0864: EOF
0865: }
0866: #-----------------------------------------------------------------------------
0867:
0868: # ------------------------------------------------------------ #
0869: # FUNCTION CREATE PROCEDURE PF_CHKINSERT
0870: #
0871: # CreatePF_CHKINSERT <dbpath-name> <encode>
0872: # ------------------------------------------------------------ #
0873: function CreatePF_CHKINSERT {
0874: dbpath=${1}
0875: encode=${2}
0876: echo '**** CREATE PF_CHKINSERT ****'
0877: isql <<EOF
0878: CONNECT "${dbpath}" USER "SYSDBA" PASSWORD "********";
0879: /**************************************************************/
0880: /* PROCEDURE : PF_CHKINSERT */
0881: /* */
0882: /* テーブル FAMILY の行の存在を検査し */
0883: /* 存在していなければ */
0884: /* INSERT する RETVAL=1 */
0885: /* 存在していれば */
0886: /* パラメータ UPDT が 0 ならば */
0887: /* 何もしない RETVAL=0 */
0888: /* パラメータ UPDT が 1 ならば */
0889: /* UPDATE する RETVAL=0 */
0890: /* */
0891: /**************************************************************/
0892: SET NAMES ${encode};
0893: SET TERM !! ;
0894: CREATE PROCEDURE PF_CHKINSERT(
0895: UPDT INTEGER,
0896: ORDERNAME NCHAR VARYING(24),
0897: FAMILYNAME NCHAR VARYING(24),
0898: FAMILYJPN CHAR VARYING(20)
0899: )
0900: RETURNS(
0901: RETVAL INTEGER
0902: )
0903: AS
0904: BEGIN
0905: IF ( EXISTS( SELECT FAMILYNAME FROM FAMILY
0906: WHERE FAMILYNAME=:FAMILYNAME ) )
0907: THEN
0908: BEGIN
0909: IF (UPDT = 1) THEN
0910: UPDATE FAMILY SET
0911: ORDERNAME=:ORDERNAME,
0912: FAMILYJPN=:FAMILYJPN
0913: WHERE
0914: FAMILYNAME=:FAMILYNAME;
0915: RETVAL=0;
0916: END
0917: ELSE
0918: BEGIN
0919: INSERT INTO FAMILY(
0920: ORDERNAME, FAMILYNAME, FAMILYJPN )
0921: VALUES (
0922: :ORDERNAME, :FAMILYNAME, :FAMILYJPN );
0923: RETVAL=1;
0924: END
0925: END;
0926: !!
0927: SET TERM ; !!
0928: GRANT EXECUTE ON PROCEDURE PF_CHKINSERT TO PUBLIC;
0929: COMMIT;
0930: QUIT;
0931: EOF
0932: }
0933:
0934: # ------------------------------------------------------------ #
0935: # FUNCTION CREATE PROCEDURE PF_N_SGFO
0936: #
0937: # CreatePF_N_SGFO <dbpath-name> <encode>
0938: # ------------------------------------------------------------ #
0939: function CreatePF_N_SGFO {
0940: dbpath=${1}
0941: encode=${2}
0942: echo '**** CREATE PF_N_SGFO ****'
0943: isql <<EOF
0944: CONNECT "${dbpath}" USER "SYSDBA" PASSWORD "********";
0945: /*****************************************************************/
0946: /* PROCEDURE : PF_N_SGFO */
0947: /* */
0948: /* 科の学名 FAMILYNAME をキーに */
0949: /* テーブル SPECIES, GENUS, FAMILY, ORDER を連結して複数行を返す */
0950: /* */
0951: /*****************************************************************/
0952: SET NAMES ${encode};
0953: SET TERM !! ;
0954: CREATE PROCEDURE PF_N_SGFO(
0955: FAMILYNAMEIN NCHAR VARYING(24)
0956: )
0957: RETURNS(
0958: SEQNO INTEGER,
0959: ORDERNAME NCHAR VARYING(24),
0960: ORDERJPN CHAR VARYING(12),
0961: FAMILYNAME NCHAR VARYING(24),
0962: FAMILYJPN CHAR VARYING(20),
0963: GENUSNAME NCHAR VARYING(24),
0964: GENUSJPN CHAR VARYING(12),
0965: SPECIESNAME NCHAR VARYING(40),
0966: SPECIESJPN CHAR VARYING(24),
0967: SPECIESENG NCHAR VARYING(40)
0968: )
0969: AS
0970: BEGIN
0971: SELECT FAMILYNAME, FAMILYJPN, ORDERNAME
0972: FROM FAMILY
0973: WHERE FAMILYNAME=:FAMILYNAMEIN
0974: INTO :FAMILYNAME, :FAMILYJPN, :ORDERNAME;
0975: SELECT ORDERJPN
0976: FROM ORDERTAB
0977: WHERE ORDERNAME=:ORDERNAME
0978: INTO :ORDERJPN;
0979: FOR
0980: SELECT GENUSNAME, GENUSJPN
0981: FROM GENUS
0982: WHERE FAMILYNAME=:FAMILYNAME
0983: /*ORDER BY SEQNO*/
0984: INTO :GENUSNAME, :GENUSJPN
0985: DO
0986: FOR
0987: SELECT SEQNO, SPECIESNAME, SPECIESJPN, SPECIESENG
0988: FROM SPECIES
0989: WHERE GENUSNAME=:GENUSNAME
0990: /*ORDER BY SEQNO*/
0991: INTO :SEQNO,:SPECIESNAME,:SPECIESJPN,:SPECIESENG
0992: DO
0993: SUSPEND;
0994: END;
0995: !!
0996: SET TERM ; !!
0997: GRANT EXECUTE ON PROCEDURE PF_N_SGFO TO PUBLIC;
0998: COMMIT;
0999: QUIT;
1000: EOF
1001: }
1002:
1003: # ------------------------------------------------------------ #
1004: # FUNCTION CREATE PROCEDURE PF_J_SGFO
1005: #
1006: # CreatePF_J_SGFO <dbpath-name> <encode>
1007: # ------------------------------------------------------------ #
1008: function CreatePF_J_SGFO {
1009: dbpath=${1}
1010: encode=${2}
1011: echo '**** CREATE PF_J_SGFO ****'
1012: isql <<EOF
1013: CONNECT "${dbpath}" USER "SYSDBA" PASSWORD "********";
1014: /**************************************************************/
1015: /* PROCEDURE : PF_J_SGFO */
1016: /* */
1017: /* 科の和名 FAMILYJPN をキーに */
1018: /* SPECIES, GENUS, FAMILY, ORDER を連結して複数行を返す */
1019: /* */
1020: /**************************************************************/
1021: SET NAMES ${encode};
1022: SET TERM !! ;
1023: CREATE PROCEDURE PF_J_SGFO(
1024: FAMILYJPNIN CHAR VARYING(12)
1025: )
1026: RETURNS(
1027: SEQNO INTEGER,
1028: ORDERNAME NCHAR VARYING(24),
1029: ORDERJPN CHAR VARYING(12),
1030: FAMILYNAME NCHAR VARYING(24),
1031: FAMILYJPN CHAR VARYING(20),
1032: GENUSNAME NCHAR VARYING(24),
1033: GENUSJPN CHAR VARYING(12),
1034: SPECIESNAME NCHAR VARYING(40),
1035: SPECIESJPN CHAR VARYING(24),
1036: SPECIESENG NCHAR VARYING(40)
1037: )
1038: AS
1039: BEGIN
1040: SELECT FAMILYNAME, FAMILYJPN, ORDERNAME
1041: FROM FAMILY
1042: WHERE FAMILYJPN=:FAMILYJPNIN
1043: INTO :FAMILYNAME,:FAMILYJPN,:ORDERNAME;
1044: SELECT ORDERJPN
1045: FROM ORDERTAB
1046: WHERE ORDERNAME=:ORDERNAME
1047: INTO :ORDERJPN;
1048: FOR
1049: SELECT GENUSNAME, GENUSJPN
1050: FROM GENUS
1051: WHERE FAMILYNAME=:FAMILYNAME
1052: ORDER BY SEQNO
1053: INTO :GENUSNAME,:GENUSJPN
1054: DO
1055: FOR
1056: SELECT SEQNO, SPECIESNAME, SPECIESJPN, SPECIESENG
1057: FROM SPECIES
1058: WHERE GENUSNAME=:GENUSNAME
1059: ORDER BY SEQNO
1060: INTO :SEQNO,:SPECIESNAME,:SPECIESJPN,:SPECIESENG
1061: DO
1062: SUSPEND;
1063: END;
1064: !!
1065: SET TERM ; !!
1066: GRANT EXECUTE ON PROCEDURE PF_J_SGFO TO PUBLIC;
1067: COMMIT;
1068: QUIT;
1069: EOF
1070: }
1071:
1072: # ------------------------------------------------------------ #
1073: # FUNCTION CREATE PROCEDURE PO_CHKINSERT
1074: #
1075: # CreatePO_CHKINSERT <dbpath-name> <encode>
1076: # ------------------------------------------------------------ #
1077: function CreatePO_CHKINSERT {
1078: dbpath=${1}
1079: encode=${2}
1080: echo '**** CREATE PO_CHKINSERT ****'
1081: isql << EOF
1082: CONNECT "${dbpath}" USER "SYSDBA" PASSWORD "********";
1083: /**************************************************************/
1084: /* PROCEDURE : PO_CHKINSERT */
1085: /* */
1086: /* テーブル ORDERTAB の行の存在を検査し */
1087: /* 存在していなければ */
1088: /* INSERT する RETVAL=1 */
1089: /* 存在していれば */
1090: /* パラメータ UPDT が 0 ならば */
1091: /* 何もしない RETVAL=0 */
1092: /* パラメータ UPDT が 1 ならば */
1093: /* UPDATE する RETVAL=0 */
1094: /* */
1095: /**************************************************************/
1096: SET NAMES ${encode};
1097: SET TERM !! ;
1098: CREATE PROCEDURE PO_CHKINSERT(
1099: UPDT INTEGER,
1100: ORDERNAME NCHAR VARYING(24),
1101: ORDERJPN CHAR VARYING(12)
1102: )
1103: RETURNS(
1104: RETVAL INTEGER
1105: )
1106: AS
1107: BEGIN
1108: IF ( EXISTS( SELECT ORDERNAME FROM ORDERTAB
1109: WHERE ORDERNAME=:ORDERNAME ) )
1110: THEN
1111: BEGIN
1112: IF (UPDT = 1) THEN
1113: UPDATE ORDERTAB SET
1114: ORDERJPN=:ORDERJPN
1115: WHERE ORDERNAME=:ORDERNAME;
1116: RETVAL=0;
1117: END
1118: ELSE
1119: BEGIN
1120: INSERT INTO ORDERTAB(
1121: ORDERNAME, ORDERJPN )
1122: VALUES (
1123: :ORDERNAME, :ORDERJPN );
1124: RETVAL=1;
1125: END
1126: END;
1127: !!
1128: SET TERM ; !!
1129: GRANT EXECUTE ON PROCEDURE PO_CHKINSERT TO PUBLIC;
1130: COMMIT;
1131: QUIT;
1132: EOF
1133: }
1134:
1135:
1136: # ------------------------------------------------------------ #
1137: # FUNCTION CREATE PROCEDURE PO_N_SGFO
1138: #
1139: # CreatePO_N_SGFO <dbpath-name> <encode>
1140: # ------------------------------------------------------------ #
1141: function CreatePO_N_SGFO {
1142: dbpath=${1}
1143: encode=${2}
1144: echo '**** CREATE PO_N_SGFO ****'
1145: isql << EOF
1146: CONNECT "${dbpath}" USER "SYSDBA" PASSWORD "********";
1147: /*****************************************************************/
1148: /* PROCEDURE : PO_N_SGFO */
1149: /* */
1150: /* 目の学名 ORDERNAME をキーに */
1151: /* テーブル SPECIES, GENUS, FAMILY, ORDER を連結して複数行を返す */
1152: /* */
1153: /*****************************************************************/
1154: SET NAMES ${encode};
1155: SET TERM !! ;
1156: CREATE PROCEDURE PO_N_SGFO(
1157: ORDERNAMEIN NCHAR VARYING(24)
1158: )
1159: RETURNS(
1160: SEQNO INTEGER,
1161: ORDERNAME NCHAR VARYING(24),
1162: ORDERJPN CHAR VARYING(12),
1163: FAMILYNAME NCHAR VARYING(24),
1164: FAMILYJPN CHAR VARYING(20),
1165: GENUSNAME NCHAR VARYING(24),
1166: GENUSJPN CHAR VARYING(12),
1167: SPECIESNAME NCHAR VARYING(40),
1168: SPECIESJPN CHAR VARYING(24),
1169: SPECIESENG NCHAR VARYING(40)
1170: )
1171: AS
1172: BEGIN
1173: SELECT ORDERNAME, ORDERJPN
1174: FROM ORDERTAB
1175: WHERE ORDERNAME=:ORDERNAMEIN
1176: INTO :ORDERNAME, :ORDERJPN;
1177: FOR
1178: SELECT FAMILYNAME, FAMILYJPN
1179: FROM FAMILY
1180: WHERE ORDERNAME=:ORDERNAME
1181: ORDER BY SEQNO
1182: INTO :FAMILYNAME, :FAMILYJPN
1183: DO
1184: FOR
1185: SELECT GENUSNAME, GENUSJPN
1186: FROM GENUS
1187: WHERE FAMILYNAME=:FAMILYNAME
1188: ORDER BY SEQNO
1189: INTO :GENUSNAME, :GENUSJPN
1190: DO
1191: FOR
1192: SELECT SEQNO, SPECIESNAME, SPECIESJPN, SPECIESENG
1193: FROM SPECIES
1194: WHERE GENUSNAME=:GENUSNAME
1195: ORDER BY SEQNO
1196: INTO :SEQNO, :SPECIESNAME, :SPECIESJPN, :SPECIESENG
1197: DO
1198: SUSPEND;
1199: END;
1200: !!
1201: SET TERM ; !!
1202: GRANT EXECUTE ON PROCEDURE PO_N_SGFO TO PUBLIC;
1203: COMMIT;
1204: QUIT;
1205: EOF
1206: }
1207:
1208:
1209: # ------------------------------------------------------------ #
1210: # FUNCTION CREATE PROCEDURE PO_J_SGFO
1211: #
1212: # CreatePO_J_SGFO <dbpath-name> <encode>
1213: # ------------------------------------------------------------ #
1214: function CreatePO_J_SGFO {
1215: dbpath=${1}
1216: encode=${2}
1217: echo '**** CREATE PO_J_SGFO ****'
1218: isql << EOF
1219: CONNECT "${dbpath}" USER "SYSDBA" PASSWORD "********";
1220: /*****************************************************************/
1221: /* PROCEDURE : PO_J_SGFO */
1222: /* */
1223: /* 目の和名 ORDERJPN をキーに */
1224: /* テーブル SPECIES, GENUS, FAMILY, ORDER を連結して複数行を返す */
1225: /* */
1226: /*****************************************************************/
1227: SET NAMES ${encode};
1228: SET TERM !! ;
1229: CREATE PROCEDURE PO_J_SGFO(
1230: ORDERJPNIN CHAR VARYING(12)
1231: )
1232: RETURNS(
1233: SEQNO INTEGER,
1234: ORDERNAME NCHAR VARYING(24),
1235: ORDERJPN CHAR VARYING(12),
1236: FAMILYNAME NCHAR VARYING(24),
1237: FAMILYJPN CHAR VARYING(20),
1238: GENUSNAME NCHAR VARYING(24),
1239: GENUSJPN CHAR VARYING(12),
1240: SPECIESNAME NCHAR VARYING(40),
1241: SPECIESJPN CHAR VARYING(24),
1242: SPECIESENG NCHAR VARYING(40)
1243: )
1244: AS
1245: BEGIN
1246: SELECT ORDERNAME, ORDERJPN
1247: FROM ORDERTAB
1248: WHERE ORDERJPN=:ORDERJPNIN
1249: INTO :ORDERNAME, :ORDERJPN;
1250: FOR
1251: SELECT FAMILYNAME, FAMILYJPN
1252: FROM FAMILY
1253: WHERE ORDERNAME=:ORDERNAME
1254: ORDER BY SEQNO
1255: INTO :FAMILYNAME, :FAMILYJPN
1256: DO
1257: FOR
1258: SELECT GENUSNAME, GENUSJPN
1259: FROM GENUS
1260: WHERE FAMILYNAME=:FAMILYNAME
1261: ORDER BY SEQNO
1262: INTO :GENUSNAME, :GENUSJPN
1263: DO
1264: FOR
1265: SELECT SEQNO, SPECIESNAME, SPECIESJPN, SPECIESENG
1266: FROM SPECIES
1267: WHERE GENUSNAME=:GENUSNAME
1268: ORDER BY SEQNO
1269: INTO :SEQNO, :SPECIESNAME, :SPECIESJPN, :SPECIESENG
1270: DO
1271: SUSPEND;
1272: END;
1273: !!
1274: SET TERM ; !!
1275: GRANT EXECUTE ON PROCEDURE PO_J_SGFO TO PUBLIC;
1276: COMMIT;
1277: QUIT;
1278: EOF
1279: }
1280:
1281:
1282: # ------------------------------------------------------------ #
1283: # FUNCTION CREATE PROCEDURE PSGFO_CHKINSERT
1284: #
1285: # CreatePSGFO_CHKINSERT <dbpath-name> <encode>
1286: # ------------------------------------------------------------ #
1287: function CreatePSGFO_CHKINSERT {
1288: dbpath=${1}
1289: encode=${2}
1290: echo '**** CREATE PSGFO_CHKINSERT ****'
1291: isql << EOF
1292: CONNECT "${dbpath}" USER "SYSDBA" PASSWORD "********";
1293: /**************************************************************/
1294: /* PROCEDURE : PSGFO_CHKINSERT */
1295: /* */
1296: /* テーブル ORDERTAB, FAMILY, GENUS, SPECIES の */
1297: /* 各々について行の存在を検査し */
1298: /* 存在していなければ */
1299: /* INSERT する */
1300: /* 存在していれば */
1301: /* パラメータ UPDT が 0 ならば */
1302: /* 何もしない */
1303: /* パラメータ UPDT が 1 ならば */
1304: /* UPDATE する */
1305: /* 返却値 */
1306: /* 各テーブルに対応した変数 */
1307: /* INSORDER, INSFAMILY, INSGENUS, INSSPECIES に */
1308: /* INSERT した場合は 1 を */
1309: /* それ以外は 0 をセットする */
1310: /* */
1311: /**************************************************************/
1312: SET NAMES ${encode};
1313: SET TERM !! ;
1314: CREATE PROCEDURE PSGFO_CHKINSERT(
1315: UPDT INTEGER,
1316: ORDERNAME NCHAR VARYING(24),
1317: ORDERJPN CHAR VARYING(12),
1318: FAMILYNAME NCHAR VARYING(24),
1319: FAMILYJPN CHAR VARYING(20),
1320: GENUSNAME NCHAR VARYING(24),
1321: GENUSJPN CHAR VARYING(12),
1322: SPECIESNAME NCHAR VARYING(40),
1323: SPECIESJPN CHAR VARYING(24),
1324: SPECIESENG NCHAR VARYING(40),
1325: SPECIESENGCAP NCHAR VARYING(40)
1326: )
1327: RETURNS(
1328: INSORDER INTEGER,
1329: INSFAMILY INTEGER,
1330: INSGENUS INTEGER,
1331: INSSPECIES INTEGER
1332: )
1333: AS
1334: BEGIN
1335: IF ( EXISTS ( SELECT ORDERNAME FROM ORDERTAB
1336: WHERE ORDERNAME=:ORDERNAME ) ) THEN
1337: BEGIN
1338: IF (UPDT = 1) THEN
1339: UPDATE ORDERTAB SET
1340: ORDERJPN=:ORDERJPN
1341: WHERE ORDERNAME=:ORDERNAME;
1342: INSORDER=0;
1343: END
1344: ELSE
1345: BEGIN
1346: INSERT INTO ORDERTAB(
1347: ORDERNAME, ORDERJPN )
1348: VALUES (
1349: :ORDERNAME, :ORDERJPN );
1350: INSORDER=1;
1351: END
1352: IF ( EXISTS ( SELECT FAMILYNAME FROM FAMILY
1353: WHERE FAMILYNAME=:FAMILYNAME ) ) THEN
1354: BEGIN
1355: IF (UPDT = 1) THEN
1356: UPDATE FAMILY SET
1357: ORDERNAME=:ORDERNAME,
1358: FAMILYJPN=:FAMILYJPN
1359: WHERE FAMILYNAME=:FAMILYNAME;
1360: INSFAMILY=0;
1361: END
1362: ELSE
1363: BEGIN
1364: INSERT INTO FAMILY(
1365: ORDERNAME, FAMILYNAME, FAMILYJPN )
1366: VALUES (
1367: :ORDERNAME, :FAMILYNAME, :FAMILYJPN );
1368: INSFAMILY=1;
1369: END
1370: IF ( EXISTS ( SELECT GENUSNAME FROM GENUS
1371: WHERE GENUSNAME=:GENUSNAME ) ) THEN
1372: BEGIN
1373: IF (UPDT = 1) THEN
1374: UPDATE GENUS SET
1375: FAMILYNAME=:FAMILYNAME,
1376: GENUSJPN=:GENUSJPN
1377: WHERE GENUSNAME=:GENUSNAME;
1378: INSGENUS=0;
1379: END
1380: ELSE
1381: BEGIN
1382: INSERT INTO GENUS(
1383: FAMILYNAME, GENUSNAME, GENUSJPN )
1384: VALUES (
1385: :FAMILYNAME, :GENUSNAME, :GENUSJPN );
1386: INSGENUS=1;
1387: END
1388: IF ( EXISTS ( SELECT SPECIESNAME FROM SPECIES
1389: WHERE SPECIESNAME=:SPECIESNAME ) ) THEN
1390: BEGIN
1391: IF (UPDT = 1) THEN
1392: UPDATE SPECIES SET
1393: GENUSNAME=:GENUSNAME,
1394: SPECIESJPN=:SPECIESJPN,
1395: SPECIESENG=:SPECIESENG,
1396: SPECIESENGCAP=:SPECIESENGCAP
1397: WHERE SPECIESNAME=:SPECIESNAME;
1398: INSSPECIES=0;
1399: END
1400: ELSE
1401: BEGIN
1402: INSERT INTO SPECIES(
1403: GENUSNAME, SPECIESNAME, SPECIESJPN, SPECIESENG, SPECIESENGCAP )
1404: VALUES (
1405: :GENUSNAME, :SPECIESNAME, :SPECIESJPN, :SPECIESENG, :SPECIESENGCAP );
1406: INSSPECIES=1;
1407: END
1408: END;
1409: !!
1410: SET TERM ; !!
1411: GRANT EXECUTE ON PROCEDURE PSGFO_CHKINSERT TO PUBLIC;
1412: COMMIT;
1413: QUIT;
1414: EOF
1415: }
1416:
1417:
1418: # ------------------------------------------------------------ #
1419: # FUNCTION CREATE PROCEDURE PSGFO_1000
1420: #
1421: # CreatePSGFO_1000 <dbpath-name> <encode>
1422: # ------------------------------------------------------------ #
1423: function CreatePSGFO_1000 {
1424: dbpath=${1}
1425: encode=${2}
1426: echo '**** CREATE PSGFO_1000 ****'
1427: isql <<EOF
1428: CONNECT "${dbpath}" USER "SYSDBA" PASSWORD "********";
1429: /**************************************************************/
1430: /* PROCEDURE : PSGFO_1000 */
1431: /* テストデータ作成用 */
1432: /* 名前の後に '000'〜'999' の文字列を追加して1度の呼び出しで */
1433: /* CNT 行のデータを生成する */
1434: /* 5,000〜数万行/秒 の速度で INSERT が可能 */
1435: /* PROCEDURE 内でぐるぐる回すんで、perl から呼んでも C から */
1436: /* でも、あんま速度は変わらん */
1437: /* */
1438: /**************************************************************/
1439: SET NAMES ${encode};
1440: SET TERM !! ;
1441: CREATE PROCEDURE PSGFO_1000(
1442: SNUM INTEGER,
1443: ENUM INTEGER,
1444: ORDERNAME NCHAR VARYING(24),
1445: ORDERJPN CHAR VARYING(12),
1446: FAMILYNAME NCHAR VARYING(24),
1447: FAMILYJPN CHAR VARYING(20),
1448: GENUSNAME NCHAR VARYING(24),
1449: GENUSJPN CHAR VARYING(12),
1450: SPECIESNAME NCHAR VARYING(40),
1451: SPECIESJPN CHAR VARYING(24),
1452: SPECIESENG NCHAR VARYING(40),
1453: SPECIESENGCAP NCHAR VARYING(40)
1454: )
1455: AS
1456: DECLARE I INTEGER;
1457: DECLARE C1 INTEGER;
1458: DECLARE C2 INTEGER;
1459: DECLARE C3 INTEGER;
1460: DECLARE EXO INTEGER;
1461: DECLARE EXF INTEGER;
1462: DECLARE EXG INTEGER;
1463: BEGIN
1464: C1=SNUM/100;
1465: C2=(SNUM-C1*100)/10;
1466: C3=SNUM-C1*100-C2*10;
1467: IF ( EXISTS ( SELECT SPECIESNAME FROM SPECIES
1468: WHERE SPECIESNAME=:SPECIESNAME||:C1||:C2||:C3 ) ) THEN
1469: EXIT;
1470: IF ( EXISTS ( SELECT ORDERNAME FROM ORDERTAB
1471: WHERE ORDERNAME=:ORDERNAME||:C1||:C2||:C3 ) ) THEN
1472: EXO=1;
1473: ELSE
1474: EXO=0;
1475: IF ( EXISTS ( SELECT FAMILYNAME FROM FAMILY
1476: WHERE FAMILYNAME=:FAMILYNAME||:C1||:C2||:C3 ) ) THEN
1477: EXF=1;
1478: ELSE
1479: EXF=0;
1480: IF ( EXISTS ( SELECT GENUSNAME FROM GENUS
1481: WHERE GENUSNAME=:GENUSNAME||:C1||:C2||:C3 ) ) THEN
1482: EXG=1;
1483: ELSE
1484: EXG=0;
1485:
1486: I=SNUM;
1487: WHILE ( I <= ENUM ) DO
1488: BEGIN
1489: C1=I/100;
1490: C2=(I-C1*100)/10;
1491: C3=I-C1*100-C2*10;
1492: IF ( EXO = 0 ) THEN
1493: INSERT INTO ORDERTAB(
1494: ORDERNAME, ORDERJPN )
1495: VALUES (
1496: :ORDERNAME||:C1||:C2||:C3, :ORDERJPN||:C1||:C2||:C3 );
1497: IF ( EXF = 0 ) THEN
1498: INSERT INTO FAMILY(
1499: ORDERNAME, FAMILYNAME, FAMILYJPN )
1500: VALUES (
1501: :ORDERNAME||:C1||:C2||:C3, :FAMILYNAME||:C1||:C2||:C3,
1502: :FAMILYJPN||:C1||:C2||:C3 );
1503: IF ( EXG = 0 ) THEN
1504: INSERT INTO GENUS(
1505: FAMILYNAME, GENUSNAME, GENUSJPN )
1506: VALUES (
1507: :FAMILYNAME||:C1||:C2||:C3, :GENUSNAME||:C1||:C2||:C3,
1508: :GENUSJPN||:C1||:C2||:C3 );
1509: IF (I=77) THEN
1510: INSERT INTO SPECIES(
1511: GENUSNAME, SPECIESNAME, SPECIESJPN, SPECIESENG, SPECIESENGCAP )
1512: VALUES (
1513: :GENUSNAME||:C1||:C2||:C3, :SPECIESNAME||:C1||:C2||:C3,
1514: 'イスカンダル'||:SPECIESJPN, :SPECIESENG||:C1||:C2||:C3,
1515: :SPECIESENGCAP||:C1||:C2||:C3 );
1516: ELSE
1517: INSERT INTO SPECIES(
1518: GENUSNAME, SPECIESNAME, SPECIESJPN, SPECIESENG, SPECIESENGCAP )
1519: VALUES (
1520: :GENUSNAME||:C1||:C2||:C3, :SPECIESNAME||:C1||:C2||:C3,
1521: :SPECIESJPN||:C1||:C2||:C3, :SPECIESENG||:C1||:C2||:C3,
1522: :SPECIESENGCAP||:C1||:C2||:C3 );
1523: I=I+1;
1524: END
1525: END;
1526: !!
1527: SET TERM ; !!
1528: GRANT EXECUTE ON PROCEDURE PSGFO_1000 TO PUBLIC;
1529: COMMIT;
1530: QUIT;
1531: EOF
1532: }
1533:
1534: # ------------------------------------------------------------ #
1535: # FUNCTION CREATE PROCEDURE P_FETCH_ACCLOG
1536: #
1537: # CreateP_FETCH_ACCLOG <dbpath-name> <encode>
1538: # ------------------------------------------------------------ #
1539: function CreateP_FETCH_ACCLOG {
1540: dbpath=${1}
1541: encode=${2}
1542: echo '**** CREATE P_FETCH_ACCLOG ****'
1543: isql << EOF
1544: CONNECT "${dbpath}" USER "SYSDBA" PASSWORD "********";
1545: /**************************************************************/
1546: /* PROCEDURE : P_FETCH_ACCLOG */
1547: /* */
1548: /* */
1549: /**************************************************************/
1550: SET NAMES ${encode};
1551: SET TERM !! ;
1552: CREATE PROCEDURE P_FETCH_ACCLOG(
1553: FDATETIME TIMESTAMP,
1554: TDATETIME TIMESTAMP
1555: )
1556: RETURNS(
1557: SEQNO INTEGER,
1558: DATETIME TIMESTAMP,
1559: PID INTEGER,
1560: IPADDR NCHAR(15),
1561: ELAPSED DOUBLE PRECISION,
1562: SELCOUNT INTEGER,
1563: QUERY CHAR VARYING(128),
1564: SQLSTR CHAR VARYING(256)
1565: )
1566: AS
1567: BEGIN
1568: FOR
1569: SELECT SEQNO, DATETIME, PID, IPADDR, ELAPSED, SELCOUNT, QUERY, SQLSTR
1570: FROM ACCLOG
1571: WHERE DATETIME>:FDATETIME AND DATETIME<:TDATETIME
1572: INTO :SEQNO, :DATETIME, :PID, :IPADDR, :ELAPSED, :SELCOUNT, :QUERY, :SQLSTR
1573: DO
1574: SUSPEND;
1575: END;
1576: !!
1577: SET TERM ; !!
1578: GRANT EXECUTE ON PROCEDURE P_FETCH_ACCLOG TO PUBLIC;
1579: COMMIT;
1580: QUIT;
1581: EOF
1582: }
1583:
1584: # ------------------------------------------------------------ #
1585: # FUNCTION CREATE PROCEDURE P_INSERT_ACCLOG
1586: #
1587: # CreateP_FETCH_ACCLOG <dbpath-name> <encode>
1588: # ------------------------------------------------------------ #
1589: function CreateP_INSERT_ACCLOG {
1590: dbpath=${1}
1591: encode=${2}
1592: echo '**** CREATE P_INSERT_ACCLOG ****'
1593: isql << EOF
1594: CONNECT "${dbpath}" USER "SYSDBA" PASSWORD "********";
1595: /**************************************************************/
1596: /* PROCEDURE : P_INSERT_ACCLOG */
1597: /* */
1598: /* */
1599: /**************************************************************/
1600: SET NAMES ${encode};
1601: SET TERM !! ;
1602: CREATE PROCEDURE P_INSERT_ACCLOG(
1603: DATETIME TIMESTAMP,
1604: PID INTEGER,
1605: IPADDR NCHAR(15),
1606: ELAPSED DOUBLE PRECISION,
1607: SELCOUNT INTEGER,
1608: QUERY CHAR VARYING(128),
1609: SQLSTR CHAR VARYING(256)
1610: )
1611: AS
1612: BEGIN
1613: INSERT INTO ACCLOG(DATETIME, PID, IPADDR, ELAPSED, SELCOUNT, QUERY, SQLSTR)
1614: VALUES(:DATETIME, :PID, :IPADDR, :ELAPSED, :SELCOUNT, :QUERY, :SQLSTR);
1615: END;
1616: !!
1617: SET TERM ; !!
1618: GRANT EXECUTE ON PROCEDURE P_INSERT_ACCLOG TO PUBLIC;
1619: COMMIT;
1620: QUIT;
1621: EOF
1622: }
1623:
1624: # ------------------------------------------------------------ #
1625: # MAIN FUNCTION
1626: # ------------------------------------------------------------ #
1627: encode="EUCJ_0208"
1628: dbpath="localhost:/DB/birds/birds.fdb"
1629: while [ "${1}" != "" ]; do
1630: [ "${1:0:1}" == "-" ] && param=${1}
1631: [ "${1:0:7}" == "encode=" ] && encode=${1:7}
1632: [ "${1:0:7}" == "dbpath=" ] && dbpath=${1:7}
1633: [ "${1:0:10}" == "procedure=" ] && procedure=${1:10}
1634: shift
1635: done
1636:
1637: if [ "${param}" == "-d" ] || [ "${param}" == "-r" ]; then
1638: echo '**** DROP' ${procedure} '****'
1639: DropProcedure $dbpath $procedure
1640: [ "${param}" == "-d" ] && exit
1641: fi
1642:
1643: "Create"${procedure} ${dbpath} ${encode}
1644:
1645: exit
この HTML を検査する。( XHTML 1.0 Strict で書かれています )
Another HTML Lint Gateway ( Mirrored by htmllint.oosato.org )