SAK }‘ŠÙ

SAK Streets ƒƒCƒ“ƒXƒgƒŠ[ƒg }‘ŠÙEƒJƒtƒF ˆê—— SAK Streets ŒŸõ
| VB | SQL | Perl | PHP | ASP | JSP | C | PC | HP | FAQ | ƒtƒŠ[ƒ\ƒtƒg |

Oracle PL/SQL •Ò17 - ƒe[ƒuƒ‹\‘¢Žæ“¾ƒXƒgƒAƒhACreatePLSQLDynasetAASP

¡ƒe[ƒuƒ‹\‘¢Žæ“¾ƒXƒgƒAƒhƒvƒƒVƒWƒƒ

  EŽŸ‚Ì SAK ƒc[ƒ‹ƒpƒbƒP[ƒW‚ɂ́A
      ƒe[ƒuƒ‹\‘¢Žæ“¾
      ƒe[ƒuƒ‹€–Ú’è‹`î•ñŽæ“¾
    ŠÖ”‚ª‚ ‚éB
    ˆÈ‰ºA‚±‚̊֐”‚ð oo4o - CreatePLSQLDynaset ‚Ì‹@”\‚ðŽg—p‚µ‚āAASP ‚Å
    ƒf[ƒ^–â‚¢‡‚킹•\Ž¦‚·‚éB

/*
========================================================================
  SAK ƒc[ƒ‹ƒpƒbƒP[ƒWŽd—l  Copyright (C) 2002 Y.SAK
========================================================================
*/
create or replace package saktool is
  type usertbl is ref cursor; 
  procedure GetUserTable(tbl in varchar2, cu out usertbl);
  procedure GetUserTableItem(fld in varchar2, cu out usertbl);
end;
/

/*
========================================================================
  SAK ƒc[ƒ‹ƒpƒbƒP[ƒW–{‘Ì  Copyright (C) 2002 Y.SAK
========================================================================
*/
create or replace package body saktool is

  /*
  ======================================================================
    ƒe[ƒuƒ‹\‘¢Žæ“¾
  ======================================================================
  */
  procedure GetUserTable(tbl in varchar2, cu out usertbl) is
  begin 

  -- ƒe[ƒuƒ‹\‘¢ƒJ[ƒ\ƒ‹ƒI[ƒvƒ“
    open cu for select
      lower(table_name) as ƒe[ƒuƒ‹–¼,
      column_name as €–Ú–¼,
      lower(data_type) as €–Úƒ^ƒCƒv,
      nvl(data_precision, char_col_decl_length) as ’·‚³,
      data_scale as ¬”•”
    from user_tab_columns
    where lower(table_name) = tbl
    order by table_name, column_id;

  end; 

  /*
  ======================================================================
    ƒe[ƒuƒ‹€–Ú’è‹`î•ñŽæ“¾
  ======================================================================
  */
  procedure GetUserTableItem(fld in varchar2, cu out usertbl) is
  begin 

  -- ƒe[ƒuƒ‹€–Ú’è‹`î•ñƒJ[ƒ\ƒ‹ƒI[ƒvƒ“
    open cu for select
      lower(table_name) as ƒe[ƒuƒ‹–¼,
      column_name as €–Ú–¼,
      lower(data_type) as €–Úƒ^ƒCƒv,
      nvl(data_precision, char_col_decl_length) as ’·‚³,
      data_scale as ¬”•”
    from user_tab_columns
    where instr(lower(column_name), fld) > 0
    order by table_name, column_id;

  end; 

end;
/

¡ƒe[ƒuƒ‹\‘¢•\Ž¦ ASP

  Eƒpƒ‰ƒƒ^‚ðŽw’肹‚¸‚ɌĂяo‚·‚ƁuƒeƒXƒgmvƒe[ƒuƒ‹\‘¢‚ð•\Ž¦‚·‚é‚悤
    ‚É‚È‚Á‚Ä‚¢‚éB

  EŽÀÛ‚̌Ăяo‚µ‚́AŽŸ‚̂悤‚É‚·‚éB
    ®A\‘¢•\Ž¦ƒe[ƒuƒ‹–¼‚́A‰pŽš‚ð¬•¶Žš‚ÅŽw’è‚·‚邱‚ƁB

http://sak-design/w_test/dsptbl.asp?tbl=ƒeƒXƒgm
                                        |_ ƒ†[ƒUƒe[ƒuƒ‹–¼

<%@ Language=VBScript %>
<%
Option Explicit
'=======================================================================
'  dsptbl.asp ƒe[ƒuƒ‹\‘¢•\Ž¦ ASP  Copyright (C) 2002 Y.SAK
'=======================================================================

  Public Const ORADB_DEFAULT        = &H0
  Public Const ORADYN_DEFAULT       = &H0
  Public Const ORADYN_NO_AUTOBIND   = &H1
  Public Const ORADYN_NO_BLANKSTRIP = &H2
  Public Const ORADYN_READONLY      = &H4
  Public Const ORADYN_NOCACHE       = &H8
  Public Const ORADYN_ORAMODE       = &H10
  Public Const ORADYN_DBDEFAULT     = &H20
  Public Const ORADYN_NO_MOVEFIRST  = &H40
  Public Const ORADYN_DIRTY_WRITE   = &H80
  Public Const ORAPARM_INPUT        = 1
  Public Const ORAPARM_OUTPUT       = 2
  Public Const ORAPARM_BOTH         = 3
  Public Const ORATYPE_VARCHAR2     = 1
  Public Const ORATYPE_NUMBER       = 2
  Public Const ORATYPE_SINT         = 3
  Public Const ORATYPE_FLOAT        = 4
  Public Const ORATYPE_STRING       = 5
  Public Const ORATYPE_VARCHAR      = 9
  Public Const ORATYPE_DATE         = 12
  Public Const ORATYPE_UINT         = 68
  Public Const ORATYPE_CHAR         = 96
  Public Const ORATYPE_CHARZ        = 97
  Public Const ORATYPE_CURSOR       = 102
  dim tbl
  dim dbname
  dim cnuser
  dim OraSession
  dim OraDatabase
  dim rs
  dim i

'=======================================================================
'  €”õ
'=======================================================================

'** –â‚¢‡‚킹ƒe[ƒuƒ‹–¼
  tbl = trim(request.querystring("tbl"))
  if tbl = "" then tbl = "ƒeƒXƒgm"

'** ƒT[ƒrƒXAƒ†[ƒUÝ’è
  dbname = "sak"
  cnuser = "SAK/SAK"

'** oo4o Ú‘±
  Set OraSession = Server.CreateObject("OracleInProcServer.XOraSession")
  Set OraDatabase = OraSession.OpenDatabase(dbname, cnuser, ORADB_DEFAULT)

'** ƒpƒ‰ƒƒ^’ljÁ
  OraDatabase.Parameters.Add "ƒe[ƒuƒ‹", tbl, ORAPARM_INPUT
  OraDatabase.Parameters("ƒe[ƒuƒ‹").ServerType = ORATYPE_VARCHAR2

'** ƒŒƒR[ƒhƒZƒbƒgƒI[ƒvƒ“
  set rs = OraDatabase.CreatePLSQLDynaset("Begin saktool.GetUserTable " _
    & "(:ƒe[ƒuƒ‹, :cu); end;", "cu", ORADYN_DEFAULT)

'=======================================================================
'  ˆ—
'=======================================================================
%>
<HTML>
<HEAD>
<META http-equiv="Content-Type" content="text/html; charset=Shift_JIS">
</HEAD>
<BODY>
<CENTER>
<TABLE border=1 bordercolordark=White bordercolorlight=#a0a0a0 cellPadding=3 cellSpacing=0 rules=ALL>
<TBODY bgcolor=#f0f0f0 align="left">
<TR bgcolor=#d0d0d0 align="center">
<TD colspan="5">ƒe[ƒuƒ‹\‘¢ƒŠƒXƒg - <% = tbl %></TD>
</TR>
<TR bgcolor=#d0d0d0 align="center">
<%

'** Œ©o‚µ•\Ž¦
  for i = 0 to rs.Fields.Count - 1
    response.write("<TD>" & rs(i).name & "</TD>")
  next
  response.write("</TR>")

'** ƒf[ƒ^•\Ž¦
  do until rs.eof
    response.write("<TR>")
    for i = 0 to rs.Fields.Count - 1
      if isnull(rs(i)) then
        response.write("<TD>•‚Ž‚‚‚“‚G</TD>")  '© •‚Ž‚‚‚“‚G‚ÍŽÀÛ‚Í”¼Šp‹Lq
      else
        response.write("<TD>" & rs(i) & "</TD>")
      end if
    next
    response.write("</TR>")
    rs.movenext
  loop
%>
</TBODY>
</TABLE>
</CENTER>
</BODY>
<%
'=======================================================================
'  ŒãŽn––
'=======================================================================

'** ƒŒƒR[ƒhƒZƒbƒgƒNƒ[ƒY
  Set rs = Nothing

'** oo4o Ú‘±‰ðœ
  Set OraDatabase = Nothing
  Set OraSession = Nothing
%>
ƒe[ƒuƒ‹\‘¢ƒŠƒXƒg - ƒeƒXƒgm
ƒe[ƒuƒ‹–¼€–Ú–¼€–Úƒ^ƒCƒv’·‚³¬”•”
ƒeƒXƒgmƒL[char8 
ƒeƒXƒgmƒf[ƒ^1number90
ƒeƒXƒgmƒf[ƒ^2number90
ƒeƒXƒgmƒf[ƒ^3number90
¡ƒe[ƒuƒ‹€–Ú’è‹`î•ñ•\Ž¦ ASP

  Eƒpƒ‰ƒƒ^‚ðŽw’肹‚¸‚ɌĂяo‚·‚Ɓuƒf[ƒ^v€–Ú’è‹`‚ðŒŸõ•\Ž¦‚·‚é‚悤‚É
    ‚È‚Á‚Ä‚¢‚éB

  EŽÀÛ‚̌Ăяo‚µ‚́AŽŸ‚̂悤‚É‚·‚éB
    ®A’è‹`•\Ž¦€–Ú–¼‚́A‰pŽš‚ð¬•¶Žš‚ÅŽw’è‚·‚邱‚ƁB

http://sak-design/w_test/dspfld.asp?fld=ƒf[ƒ^
                                        |_ ƒ†[ƒUƒe[ƒuƒ‹€–ÚŒŸõ•¶Žš—ñ

<%@ Language=VBScript %>
<%
Option Explicit
'=======================================================================
'  dspfld.asp ƒe[ƒuƒ‹€–Ú’è‹`î•ñ•\Ž¦ ASP  Copyright (C) 2002 Y.SAK
'=======================================================================

  Public Const ORADB_DEFAULT        = &H0
  Public Const ORADYN_DEFAULT       = &H0
  Public Const ORADYN_NO_AUTOBIND   = &H1
  Public Const ORADYN_NO_BLANKSTRIP = &H2
  Public Const ORADYN_READONLY      = &H4
  Public Const ORADYN_NOCACHE       = &H8
  Public Const ORADYN_ORAMODE       = &H10
  Public Const ORADYN_DBDEFAULT     = &H20
  Public Const ORADYN_NO_MOVEFIRST  = &H40
  Public Const ORADYN_DIRTY_WRITE   = &H80
  Public Const ORAPARM_INPUT        = 1
  Public Const ORAPARM_OUTPUT       = 2
  Public Const ORAPARM_BOTH         = 3
  Public Const ORATYPE_VARCHAR2     = 1
  Public Const ORATYPE_NUMBER       = 2
  Public Const ORATYPE_SINT         = 3
  Public Const ORATYPE_FLOAT        = 4
  Public Const ORATYPE_STRING       = 5
  Public Const ORATYPE_VARCHAR      = 9
  Public Const ORATYPE_DATE         = 12
  Public Const ORATYPE_UINT         = 68
  Public Const ORATYPE_CHAR         = 96
  Public Const ORATYPE_CHARZ        = 97
  Public Const ORATYPE_CURSOR       = 102
  dim fld
  dim dbname
  dim cnuser
  dim OraSession
  dim OraDatabase
  dim rs
  dim i

'=======================================================================
'  €”õ
'=======================================================================

'** –â‚¢‡‚킹€–Ú–¼
  fld = trim(request.querystring("fld"))
  if fld = "" then fld = "ƒf[ƒ^"

'** ƒT[ƒrƒXAƒ†[ƒUÝ’è
  dbname = "sak"
  cnuser = "SAK/SAK"

'** oo4o Ú‘±
  Set OraSession = Server.CreateObject("OracleInProcServer.XOraSession")
  Set OraDatabase = OraSession.OpenDatabase(dbname, cnuser, ORADB_DEFAULT)

'** ƒpƒ‰ƒƒ^’ljÁ
  OraDatabase.Parameters.Add "€–Ú", fld, ORAPARM_INPUT
  OraDatabase.Parameters("€–Ú").ServerType = ORATYPE_VARCHAR2

'** ƒŒƒR[ƒhƒZƒbƒgƒI[ƒvƒ“
  set rs = OraDatabase.CreatePLSQLDynaset("Begin saktool.GetUserTableItem " _
    & "(:€–Ú, :cu); end;", "cu", ORADYN_DEFAULT)

'=======================================================================
'  ˆ—
'=======================================================================
%>
<HTML>
<HEAD>
<META http-equiv="Content-Type" content="text/html; charset=Shift_JIS">
</HEAD>
<BODY>
<CENTER>
<TABLE border=1 bordercolordark=White bordercolorlight=#a0a0a0 cellPadding=3 cellSpacing=0 rules=ALL>
<TBODY bgcolor=#f0f0f0 align="left">
<TR bgcolor=#d0d0d0 align="center">
<TD colspan="5">ƒe[ƒuƒ‹€–Ú’è‹`î•ñƒŠƒXƒg - <% = fld %></TD>
</TR>
<TR bgcolor=#d0d0d0 align="center">
<%

'** Œ©o‚µ•\Ž¦
  for i = 0 to rs.Fields.Count - 1
    response.write("<TD>" & rs(i).name & "</TD>")
  next
  response.write("</TR>")

'** ƒf[ƒ^•\Ž¦
  do until rs.eof
    response.write("<TR>")
    for i = 0 to rs.Fields.Count - 1
      if isnull(rs(i)) then
        response.write("<TD>•‚Ž‚‚‚“‚G</TD>")  '© •‚Ž‚‚‚“‚G‚ÍŽÀÛ‚Í”¼Šp‹Lq
      else
        response.write("<TD>" & rs(i) & "</TD>")
      end if
    next
    response.write("</TR>")
    rs.movenext
  loop
%>
</TBODY>
</TABLE>
</CENTER>
</BODY>
<%
'=======================================================================
'  ŒãŽn––
'=======================================================================

'** ƒŒƒR[ƒhƒZƒbƒgƒNƒ[ƒY
  Set rs = Nothing

'** oo4o Ú‘±‰ðœ
  Set OraDatabase = Nothing
  Set OraSession = Nothing
%>
ƒe[ƒuƒ‹€–Ú’è‹`î•ñƒŠƒXƒg - ƒf[ƒ^
ƒe[ƒuƒ‹–¼€–Ú–¼€–Úƒ^ƒCƒv’·‚³¬”•”
ƒR[ƒhmƒf[ƒ^varchar2200 
ƒeƒXƒgmƒf[ƒ^1number90
ƒeƒXƒgmƒf[ƒ^2number90
ƒeƒXƒgmƒf[ƒ^3number90
¡Oracle PL/SQL •ÒŽ‘—¿
¡SQL Šî‘bŽÀ’n•ÒŽ‘—¿
¡SQL Šî‘b•ÒŽ‘—¿
¡SQL ƒ`ƒ…[ƒjƒ“ƒO•ÒŽ‘—¿
¡PostgreSQL •ÒAJAVA ServletAJSP •ÒŽ‘—¿
¡MySQL •ÒŽ‘—¿
| VB | SQL | Perl | PHP | ASP | JSP | C | PC | HP | FAQ | ƒtƒŠ[ƒ\ƒtƒg |

SAK Streets ƒƒCƒ“ƒXƒgƒŠ[ƒg }‘ŠÙEƒJƒtƒF ˆê—— SAK Streets ŒŸõ



Copyright (C) 1999-2017 Y.SAK