ÀÀ¿ëÇÁ·Î±×·¥ °³¹ß ¾È³»¼­


SQL ÇÁ·Î½Ãµà¾î µð¹ö±ë

SQL ÇÁ·Î½Ãµà¾î¸¦ ÀÛ¼ºÇÑ ´ÙÀ½ CREATE PROCEDURE¹® ½ÇÇà¿¡ ¼³¸íµÈ ´ë·Î CREATE PROCEDURE¹®À» ¹ßÇàÇØ¾ß ÇÕ´Ï´Ù. ƯÁ¤ÇÑ °æ¿ì DB2´Â CREATE PROCEDURE¹®ÀÇ ÀÀ´äÀ¸·Î ¿À·ù¸¦ ¸®ÅÏÇÕ´Ï´Ù. ¿À·ù¸¦ Á¤Á¤ÇÏ´Â °Í¿¡ ´ëÇÑ ¼³¸í°ú Á¦¾ÈÀ» Æ÷ÇÔÇÏ¿© DB2¿¡¼­ ¸®ÅÏµÈ ¿À·ù¿¡ ´ëÇØ ´õ ¸¹Àº Á¤º¸¸¦ °Ë»öÇÏ·Á¸é CLP¿¡¼­ ´ÙÀ½ ¸í·ÉÀ» ¹ßÇàÇϽʽÿÀ.

   db2 "? error-code"

¿©±â¼­ error-code´Â ¿À·ù¿¡ ÀÇÇØ ¸®ÅÏµÈ SQLCODE ¶Ç´Â SQLSTATE¸¦ ³ªÅ¸³À´Ï´Ù. ¿¹¸¦ µé¾î, CREATE PROCEDURE¹®ÀÌ SQLCODE "SQL0469N"("¸Å°³º¯¼ö ¸ðµå°¡ À¯È¿ÇÏÁö ¾ÊÀ½")¿Í ÇÔ²² ¿À·ù¸¦ ¸®ÅÏÇÏ¸é ´ÙÀ½ ¸í·ÉÀ» ¹ßÇàÇϽʽÿÀ.

   db2 "? SQL0469"

DB2´Â ´ÙÀ½ ¸Þ½ÃÁö¸¦ ¸®ÅÏÇÕ´Ï´Ù.

   Explanation:  One of the following errors occurred:
 
   o   a parameter in an SQL procedure is declared as OUT and is
       used as input in the procedure body
 
   o   a parameter in an SQL procedure is declared as IN and is
       modified in the procedure body.
 
   User Response:  Change the attribute of the parameter to INOUT,
   or change the use of the parameter within the procedure.

ÀÏ´Ü ¸Þ½ÃÁö¸¦ Ç¥½ÃÇϸé "»ç¿ëÀÚ Á¶Ä¡" Àý¿¡ ÀÖ´Â Á¦¾È¿¡ µû¶ó SQL ÇÁ·Î½Ãµà¾î¸¦ ¼öÁ¤ÇϽʽÿÀ.

SQL ÇÁ·Î½Ãµà¾î ¿À·ù ¸Þ½ÃÁö Ç¥½Ã

SQL ÇÁ·Î½Ãµà¾î¿¡ ´ëÇØ CREATE PROCEDURE¹®À» ¹ßÇàÇϸé DB2´Â SQL ÇÁ·Î½Ãµà¾î º»¹®ÀÇ ±¸¹®À» ½ÂÀÎÇÏ¿© »çÀü ó¸® ÄÄÆÄÀÏÀ̳ª ÄÄÆÄÀÏ ´Ü°è¿¡¼­ SQL ÇÁ·Î½Ãµà¾î¸¦ ÀÛ¼ºÇÒ ¼ö ÀÖ½À´Ï´Ù. ÀÌ·± »óȲ¿¡¼­ DB2´Â º¸Åë ¿À·ù ¸Þ½ÃÁö°¡ µé¾î ÀÖ´Â ·Î±× ÆÄÀÏÀ» ÀÛ¼ºÇÕ´Ï´Ù. ÀÌ ·Î±× ÆÄÀϰú ±âŸ Áß´Ü ÆÄÀÏ¿¡ ´ëÇØ¼­´Â Áß°£ ÆÄÀÏÀ» »ç¿ëÇÑ SQL ÇÁ·Î½Ãµà¾î µð¹ö±ë¿¡ ¼³¸íµÇ¾î ÀÖ½À´Ï´Ù.

SQL ÇÁ·Î½Ãµà¾î¿¡ ´ëÇØ DB2¿Í C ÄÄÆÄÀÏ·¯¿¡¼­ »ý¼ºµÈ ¿À·ù ¸Þ½ÃÁö¸¦ °Ë»öÇÏ·Á¸é µ¥ÀÌÅͺ£À̽º ¼­¹ö¿¡¼­ ´ÙÀ½ µð·ºÅ丮¿¡ ¸Þ½ÃÁö ·Î±× ÆÄÀÏÀ» Ç¥½ÃÇϽʽÿÀ.

UNIX
$DB2PATH/function/routine/sqlproc/$DATABASE/$SCHEMA/tmp

¿©±â¼­ $DB2PATH´Â ÀνºÅϽº µð·ºÅ丮ÀÇ À§Ä¡¸¦ ³ªÅ¸³»°í $DATABASE´Â µ¥ÀÌÅͺ£À̽º À̸§À» ³ªÅ¸³»¸ç $SCHEMA´Â SQL ÇÁ·Î½Ãµà¾î¸¦ ÀÛ¼ºÇÒ ¶§ »ç¿ëµÇ´Â ½ºÅ°¸¶ À̸§À» ³ªÅ¸³À´Ï´Ù.

Windows NT
%DB2PATH%\function\routine\sqlproc\%DB%\%SCHEMA%\tmp

¿©±â¼­ %DB2PATH%´Â ÀνºÅϽº µð·ºÅ丮ÀÇ À§Ä¡¸¦ ³ªÅ¸³»°í %DB%´Â µ¥ÀÌÅͺ£À̽º À̸§À» ³ªÅ¸³»¸ç %SCHEMA%´Â SQL ÇÁ·Î½Ãµà¾î¸¦ ÀÛ¼ºÇÒ ¶§ »ç¿ëµÇ´Â ½ºÅ°¸¶ À̸§À» ³ªÅ¸³À´Ï´Ù.

ÀÀ¿ëÇÁ·Î±×·¥¿¡¼­ CALL¹®À» ¹ßÇàÇÏ¿© ´ÙÀ½ ±¸¹®À» ÅëÇØ db2udp!get_error_messages »ùÇà ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇÒ ¼öµµ ÀÖ½À´Ï´Ù.

   CALL db2udp!get_error_messages(schema-name, file-name, message-text)

¿©±â¼­ schema-nameÀº SQL ÇÁ·Î½Ãµà¾îÀÇ ½ºÅ°¸¶¸¦ ³ªÅ¸³»´Â ÀÔ·Â ¸Å°³º¯¼öÀ̰í file-nameÀº SQL ÇÁ·Î½Ãµà¾î¿¡ ´ëÇØ »ý¼ºµÈ ÆÄÀÏ À̸§À̸ç message-text´Â ¸Þ½ÃÁö ·Î±× ÆÄÀÏ·Î ¸Þ½ÃÁö ÅØ½ºÆ®¸¦ ¸®ÅÏÇÏ´Â Ãâ·Â ¸Å°³º¯¼öÀÔ´Ï´Ù.

¿¹¸¦ µé¾î, ´ÙÀ½ Java ÀÀ¿ëÇÁ·Î±×·¥À» »ç¿ëÇÏ¿© SQL ÇÁ·Î½Ãµà¾î¿¡ ´ëÇØ ¿À·ù ¸Þ½ÃÁö¸¦ Ç¥½ÃÇÒ ¼ö ÀÖ½À´Ï´Ù.

    public static String getErrorMessages(Connection  con,
      String procschema, String filename) throws Exception
    {
        String filecontents = null;
        // prepare the CALL statement
        CallableStatement stmt = null;
        try
        {
            String sql = "Call db2udp!get_error_messages(?, ?, ?) ";
            stmt = con.prepareCall (sql);
 
            // set all parameters (input and output)
            stmt.registerOutParameter( 3, java.sql.Types.LONGVARCHAR  );
            stmt.setString( 1, procschema );
            stmt.setString( 2, filename );
 
            // call the stored procedure
            boolean isrs = stmt.execute();
            filecontents = stmt.getString(3);
 
            System.out.println("SQL Procedure - getErrorMessages "
              + filecontents);
            return filecontents;
        }
        catch (Exception e) { throw e; }
        finally
        {
            if (stmt != null) stmt.close();
        }
    }

´ÙÀ½ C ÀÀ¿ëÇÁ·Î±×·¥À» »ç¿ëÇÏ¿© SQL ÇÁ·Î½Ãµà¾î¿¡ ´ëÇØ ¿À·ù ¸Þ½ÃÁö¸¦ Ç¥½ÃÇÒ ¼ö ÀÖ½À´Ï´Ù.

int getErrors(char inputSchema[9], char inputFilename[9],
                         char outputFilecontents[32000])
{
  EXEC SQL BEGIN DECLARE SECTION;
    char    procschema[100] = "";
    char    filename[100] = "";
    char    filecontents[32000] = "";
  EXEC SQL END DECLARE SECTION;
 
  strcpy (procschema, inputSchema);
  strcpy (filename, inputFilename);
 
  EXEC SQL CALL "db2udp!get_error_messages"
    (:procschema, :filename, :filecontents);
  if ( sqlca.sqlcode != 0 )
  {
    printf("Call failed. Code: %d\n", sqlca.sqlcode);
    return 1;
  }
  else
  {
    printf("\nSQL Procedure - getErrors:\n%s\n", filecontents);
  }
  strcpy (outputFilecontents, filecontents);
  return 0;
}
ÁÖ:DB2¿¡¼­ ÀÛ¼ºÇÏÁö ¸øÇÑ SQL ÇÁ·Î½Ãµà¾î¿¡ ´ëÇØ ¿À·ù ¸Þ½ÃÁö¸¦ Ç¥½ÃÇϱâ Àü¿¡ ÇÁ·Î½Ãµà¾î À̸§°ú SQL ÇÁ·Î½Ãµà¾îÀÇ »ý¼ºµÈ ÆÄÀÏ À̸§À» ¾Ë°í ÀÖ¾î¾ß ÇÕ´Ï´Ù. ÇÁ·Î½Ãµà¾î ½ºÅ°¸¶ À̸§ÀÌ CREATE PROCEDURE¹®ÀÇ ÀϺηΠ¹ßÇàµÇÁö ¾ÊÀ¸¸é DB2´Â CURRENT SCHEMA Ư¼ö ·¹Áö½ºÅÍÀÇ °ªÀ» »ç¿ëÇÕ´Ï´Ù. CURRENT SCHEMA Ư¼ö ·¹Áö½ºÅÍÀÇ °ªÀ» Ç¥½ÃÇÏ·Á¸é CLP¿¡¼­ ´ÙÀ½ ¸í·É¹®À» ¹ßÇàÇϽʽÿÀ.

   VALUES CURRENT SCHEMA

Áß°£ ÆÄÀÏÀ» »ç¿ëÇÑ SQL ÇÁ·Î½Ãµà¾î µð¹ö±ë

SQL ÇÁ·Î½Ãµà¾î¿¡ ´ëÇØ CREATE PROCEDURE¹®À» ¹ßÇàÇϰí DB2°¡ SQL ÇÁ·Î½Ãµà¾î º»¹®ÀÇ ±¸¹®À» ½ÂÀÎÇϸé DB2´Â ¿©·¯ °³ÀÇ Áß°£ ÆÄÀÏÀ» »ç¿ëÇÏ¿© SQL ÇÁ·Î½Ãµà¾î¸¦ ÀÛ¼ºÇÕ´Ï´Ù. DB2°¡ ¼º°øÀûÀ¸·Î SQL ÇÁ·Î½Ãµà¾î¸¦ ÀÛ¼ºÇÑ ´ÙÀ½ ÀÌ´Â º¸Åë Áß°£ ÆÄÀÏÀ» Á¦°ÅÇÏ¿© ½Ã½ºÅÛ ÀÚ¿øÀ» Àý¾àÇÕ´Ï´Ù. DB2°¡ CREATE PROCEDURE ±¸¹®À» ½ÂÀÎÇÏÁö¸¸ SQL ÇÁ·Î½Ãµà¾î¸¦ ÀÛ¼ºÇÏÁö ¸øÇϸé ÀÌ´Â CREATE PROCEDURE ÇÁ·Î¼¼½ºÀÇ »çÀü ó¸® ÄÄÆÄÀÏ, ¹ÙÀÎµå ±×¸®°í ÄÄÆÄÀÏ ´Ü°è¸¦ ÃßÀûÇÏ´Â ·Î±× ÆÄÀÏÀ» À¯ÁöÇÕ´Ï´Ù.

UNIX ½Ã½ºÅÛ¿¡¼­ DB2´Â instance/function/routine/sqlproc/dbAlias/schemaÀÇ ±âº» µð·ºÅ丮¸¦ »ç¿ëÇÏ¿© Áß°£ ÆÄÀÏÀ» º¸°üÇÕ´Ï´Ù. ¿©±â¼­ instance´Â DB2 ÀνºÅϽºÀÇ °æ·Î¸¦ ³ªÅ¸³»°í dbAlias´Â µ¥ÀÌÅͺ£À̽º º°¸íÀ» ³ªÅ¸³»¸ç schema´Â CREATE PROCEDURE¹®ÀÌ ¹ßÇàµÈ ½ºÅ°¸¶¸¦ ³ªÅ¸³À´Ï´Ù.

OS/2¿Í Windows 32-ºñÆ® ¿î¿µ üÁ¦¿¡¼­ DB2´Â instance\function\routine\sqlproc\dbAlias\schemaÀÇ ±âº» µð·ºÅ丮¸¦ »ç¿ëÇÏ¿© Áß°£ ÆÄÀÏÀ» º¸°üÇÕ´Ï´Ù. ¿©±â¼­ instance´Â DB2 ÀνºÅϽº¸¦ ³ªÅ¸³»°í dbAlias´Â µ¥ÀÌÅͺ£À̽º º°¸íÀ» ³ªÅ¸³»¸ç schema´Â CREATE PROCEDURE¹®ÀÌ ¹ßÇàµÈ ½ºÅ°¸¶¸¦ ³ªÅ¸³À´Ï´Ù.

SQL ÇÁ·Î½Ãµà¾î°¡ ¼º°øÀûÀ¸·Î ÀÛ¼ºµÇ¾úÁö¸¸ CALL¹®¿¡¼­ ¿¹»ó °á°ú¸¦ ¸®ÅÏÇÏÁö ¾ÊÀ¸¸é Áß°£ ÆÄÀÏÀ» °Ë»çÇÒ ¼ö ÀÖ½À´Ï´Ù. DB2°¡ Áß°£ ÆÄÀÏÀ» Á¦°ÅÇÏÁö ¸øÇÏ°Ô ¸·À¸·Á¸é ´ÙÀ½ ¸í·ÉÀ» »ç¿ëÇÏ¿© DB2_SQLROUTINE_KEEP_FILES DB2 ·¹Áö½ºÆ®¸® º¯¼ö¸¦ "¿¹"·Î ¼³Á¤ÇϽʽÿÀ.

   db2set DB2_SQLROUTINE_KEEP_FILES="yes"

DB2°¡ ·¹Áö½ºÆ®¸® º¯¼öÀÇ »õ °ªÀ» »ç¿ëÇϱâ Àü¿¡ µ¥ÀÌÅͺ£À̽º¸¦ ´Ù½Ã ½ÃÀÛÇØ¾ß ÇÕ´Ï´Ù.


[ ÆäÀÌÁöÀÇ ¸Ç À§ | ÀÌÀü ÆäÀÌÁö | ´ÙÀ½ ÆäÀÌÁö | ¸ñÂ÷ | »öÀÎ ]