約 182,137 件
https://w.atwiki.jp/mtam0915/pages/13.html
■Excel関数 項番作成 =MAX($B$1 B11)+1 「$」列や行を固定できる ■SQLトレースの取得方法 <事前準備> 1.DBMS_MONITORパッケージをインストールする。 SQL conn sys/sys@xe as sysdba SQL @C \oraclexe\app\oracle\product\10.2.0\server\RDBMS\ADMIN\dbmsmntr.sql SQL grant execute on sys.dbms_monitor to test; SQL grant debug on sys.dbms_monitor to test; 2.execute.sqlでプログラム実行する前後に、トレースON/OFFするFUNCTIONを埋め込む。 BEGIN DBMS_MONITOR.SESSION_TRACE_ENABLE(); -- function call v_ret = pgName.; DBMS_MONITOR.SESSION_TRACE_DISABLE(); END; <トレース取得> 3.バッチファイルを叩く。(SR35B0101.batなど) 4.実行後、トレースファイルが生成される。 SQL SHOW PARAMETER USER_DUMP_DEST で出力先を確認 5.TKPROFユーティリティでトレースファイルを見易い形式に変換する。 C \oraclexe\app\oracle\admin\XE\udump TKPROF [トレースファイル] [変換後ファイル名] EXPLAIN=[ユーザID]/[パスワード] ■Oracleキャッシュクリア conn sys/sys as SYSDBA 共有プール(SHARED POOL)をクリアする ALTER SYSTEM FLUSH SHARED_POOL; データベース・バッファ・キャッシュのクリア ALTER SYSTEM FLUSH BUFFER_CACHE; ■オラクルでロックしているセッションを確認するSQL文 http //www.systeminfinity.co.jp/sub/tech/doc/ora8_rock_session/Rock_Session.html ■PGAメモリ使用量監視 alter session set nls_date_format= yyyy/mm/dd hh24 mi ss ; set lin 1000 set pages 1000 set trims on set tab off col PROGRAM for a20 col MODULE for a20 col ACTION for a20 col SQL_TEXT for a100 col pga_used_mem for 9999.0 col pga_alloc_mem for 9999.0 col pga_max_mem for 9999.0 select sysdate, s.sid, p.program, p.pga_used_mem/1024/1024 as pga_used_mem, p.pga_alloc_mem/1024/1024 as pga_alloc_mem, p.pga_max_mem/1024/1024 as pga_max_mem, s.module, s.action, q.sql_text from v$process p, v$session s, v$sql q where s.username= UserName and p.addr = s.paddr and s.sql_address = q.address order by p.pga_used_mem / ■UTL_FILE設定 UTL_FILE設定手順 各自の環境で読み換えて設定してください。 sqlplus起動 sqlplus /nolog sys権限で接続 conn sys/ password @xe as sysdba UTL_FILEのインストール @C \oraclexe\app\oracle\product\10.2.0\server\RDBMS\ADMIN\UTLFILE.sql ファイルディレクトリの設定 ALTER SYSTEM SET UTL_FILE_DIR= 任意のPath1 , 任意のPath2 SCOPE = SPFILE ; DBインスタンスの再起動 管理ツール - サービス「OracleServiceXE」を再起動 sqlplus起動 sqlplus /nolog sys権限で接続 conn sys/ password @xe as sysdba ファイルディレクトリの設定確認 SELECT NAME, VALUE FROM V$PARAMETER2 WHERE NAME= utl_file_dir ; ディレクトリオブジェクトの作成 DIRECTORY の作成には、CREATE DIRECTORY 権限 が必要。 CREATE DIRECTORY recv_area AS 任意のPath1 ;-- recv_areaは任意名称 CREATE DIRECTORY send_area AS 任意のPath2 ;-- send_areaは任意名称 ディレクトリへのアクセス権の設定 読み込み権限と書き込み権限は個別に行なう。 GRANT READ ON DIRECTORY recv_area TO user_name ; GRANT WRITE ON DIRECTORY send_area TO user_name ; 確認 SELECT * FROM ALL_DIRECTORIES ; 実行&デバッグ権限付与 GRANT EXECUTE ON SYS.UTL_FILE TO username ; GRANT DEBUG ON SYS.UTL_FILE TO username ;
https://w.atwiki.jp/ce00582/pages/2294.html
?php require("calldata.php"); $sql = "SELECT * FROM eli09"; $result = mysql_query($sql, $link); $n=1; while( $row = mysql_fetch_row( $result ) ){ $matrix[$n][1] = $row[0]; $matrix[$n][2] =$row[1]; $matrix[$n][3] =$row[2]; $matrix[$n][4] =$row[3]; $n=$n+1; } $maxn=$n; for ($n=1;$n $maxn;$n++){ $year=$matrix[$n][1]; $age=$matrix[$n][2]; $m4[$year][$age]=$matrix[$n][3]; $f4[$year][$age]=$matrix[$n][4]; } $sql = "SELECT * FROM eli10"; $result = mysql_query($sql, $link); $n=1; while( $row = mysql_fetch_row( $result ) ){ $matrix[$n][1] = $row[0]; $matrix[$n][2] =$row[1]; $matrix[$n][3] =$row[2]; $matrix[$n][4] =$row[3]; $n=$n+1; } $maxn=$n; for ($n=1;$n $maxn;$n++){ $year=$matrix[$n][1]; $age=$matrix[$n][2]; $m2[$year][$age]=$matrix[$n][3]; $f2[$year][$age]=$matrix[$n][4]; } $sql = "SELECT * FROM eli49"; $result = mysql_query($sql, $link); $n=1; while( $row = mysql_fetch_row( $result ) ){ $matrix[$n][1] = $row[0]; $matrix[$n][2] =$row[1]; $matrix[$n][3] =$row[2]; $matrix[$n][4] =$row[3]; $n=$n+1; } $maxn=$n; for ($n=1;$n $maxn;$n++){ $year=$matrix[$n][1]; $age=$matrix[$n][2]; $m3[$year][$age]=$matrix[$n][3]; $f3[$year][$age]=$matrix[$n][4]; } $sql = "SELECT * FROM eli50"; $result = mysql_query($sql, $link); $n=1; while( $row = mysql_fetch_row( $result ) ){ $matrix[$n][1] = $row[0]; $matrix[$n][2] =$row[1]; $matrix[$n][3] =$row[2]; $matrix[$n][4] =$row[3]; $n=$n+1; } $maxn=$n; for ($n=1;$n $maxn;$n++){ $year=$matrix[$n][1]; $age=$matrix[$n][2]; $m5[$year][$age]=$matrix[$n][3]; $f5[$year][$age]=$matrix[$n][4]; } $sql = "SELECT * FROM eli51"; $result = mysql_query($sql, $link); $n=1; while( $row = mysql_fetch_row( $result ) ){ $matrix[$n][1] = $row[0]; $matrix[$n][2] =$row[1]; $matrix[$n][3] =$row[2]; $matrix[$n][4] =$row[3]; $n=$n+1; } $maxn=$n; for ($n=1;$n $maxn;$n++){ $year=$matrix[$n][1]; $age=$matrix[$n][2]; $m1[$year][$age]=$matrix[$n][3]; $f1[$year][$age]=$matrix[$n][4]; } $sql = "SELECT * FROM eli48"; $result = mysql_query($sql, $link); $n=1; while( $row = mysql_fetch_row( $result ) ){ $matrix[$n][1] = $row[0]; $matrix[$n][2] =$row[1]; $n=$n+1; } $maxn=$n; for ($n=1;$n $maxn;$n++){ $year=$matrix[$n][1]; $kiso[$year]=$matrix[$n][2]; } for ($year=10;$year 100;$year++){ $px1=0; for ($age=20;$age 60;$age++){ $px1=$px1+0.8*($m1[$year][$age]+$f1[$year][$age]); $px1=$px1+$m2[$year][$age]+$f2[$year][$age]; $px1=$px1+$m3[$year][$age]+$f3[$year][$age]; $px1=$px1+$m4[$year][$age]+$f4[$year][$age]; $px1=$px1+$m5[$year][$age]+$f5[$year][$age]; } $px2=0; for ($age=20;$age 60;$age++){ $px2=$px2+$m2[$year][$age]+$f2[$year][$age]; $px2=$px2+$m3[$year][$age]+$f3[$year][$age]; } $kyo[$year]=$kiso[$year]*$px2/$px1; } $sql = "truncate eli52"; $exe= mysql_query($sql,$link); for ($year=10;$year 100;$year++){ $x=$kyo[$year]; $sql = "insert into eli52 values($year,$x)"; $exe= mysql_query($sql,$link); } $close_flag = mysql_close($link); print("52 基礎年金拠出金 計算終了"); ?
https://w.atwiki.jp/ce00582/pages/2243.html
?php require("calldata.php"); $sql = "SELECT * FROM eli16"; $result = mysql_query($sql, $link); $n=1; while( $row = mysql_fetch_row( $result ) ){ $matrix[$n][1] = $row[0]; $matrix[$n][2] =$row[1]; $matrix[$n][3] =$row[2]; $matrix[$n][4] =$row[3]; $n=$n+1; } $maxn=$n; for ($n=1; $n $maxn; $n++) { $byear =$matrix[$n][1]; $age =$matrix[$n][2]; $m2by[$byear][$age] = $matrix[$n][3]; $f2by[$byear][$age] = $matrix[$n][4]; } $sql = "SELECT * FROM data151"; $result = mysql_query($sql, $link); $n=1; while( $row = mysql_fetch_row( $result ) ){ $matrix[$n][1] = $row[0]; $matrix[$n][2] =$row[1]; $matrix[$n][3] =$row[2]; $matrix[$n][4] =$row[3]; $n=$n+1; } $maxn=$n; for ($n=1; $n $maxn; $n++) { $age =$matrix[$n][1]; $gamma[$age] = $matrix[$n][2]; $alpha[$age] = $matrix[$n][3]; $beta[$age] = $matrix[$n][4]; } $sql = "SELECT * FROM data152"; $result = mysql_query($sql, $link); $n=1; while( $row = mysql_fetch_row( $result ) ){ $matrix[$n][1] = $row[0]; $matrix[$n][2] =$row[1]; $matrix[$n][3] =$row[2]; $n=$n+1; } $maxn=$n; for ($n=1; $n $maxn; $n++) { $age =$matrix[$n][1]; $theta[$age] = $matrix[$n][2]; } $sql = "SELECT * FROM eli19"; $result = mysql_query($sql, $link); $n=1; while( $row = mysql_fetch_row( $result ) ){ $matrix[$n][1] = $row[0]; $matrix[$n][2] =$row[1]; $matrix[$n][3] =$row[2]; $n=$n+1; } $maxn=$n; for ($n=1; $n $maxn; $n++) { $age =$matrix[$n][1]; $car = $matrix[$n][2]; $mint[$age][$car]= $matrix[$n][3]; } $sql = "SELECT * FROM eli21"; $result = mysql_query($sql, $link); $n=1; while( $row = mysql_fetch_row( $result ) ){ $matrix[$n][1] = $row[0]; $matrix[$n][2] =$row[1]; $matrix[$n][3] =$row[2]; $n=$n+1; } $maxn=$n; for ($n=1; $n $maxn; $n++) { $age =$matrix[$n][1]; $car = $matrix[$n][2]; $mout[$age][$car]= $matrix[$n][3]; } For ($byear = 1949;$byear 1990;$byear++){ $stage =2008-$byear; For ($car=1;$car 50;$car++){ $mis[$stage][$car] = $mint[$stage][$car]; $mos[$stage][$car] = $mout[$stage][$car]; } For ($age = $stage+1;$age 65;$age++){ $z1=(1-$gamma[$age-1]) * $m2by[$byear][$age-1]; $z2=$m2by[$byear][$age] - $z1; If($z2 0)$z2 = 0; $z3=(1-$theta[$age])*$z2; $z4=$theta[$age]*$z2; $mis[$age][1]=$z3; $z5=0; For ($car=1;$car 50;$car++){ $z5=$z5+$mos[$age-1][$car]; } If($z5==0)$z5=100; $v[$age]=$z4/$z5; If($v[$age] 1)$v[$age] = 1; For ($car=2;$car 50;$car++){ $mis[$age][$car]=(1-$gamma[$age-1])*$mis[$age-1][$car-1]+$v[$age]*$mos[$age-1][$car-1]; } For ($car=1;$car 50;$car++){ $mos[$age][$car] = ($gamma[$age-1] - $alpha[$age-1] - $beta[$age-1]) * $mis[$age-1][$car] + (1- $v[$age]) * $mos[$age-1][$car]; } } $z6=0; For ($car=1;$car 25;$car++){ $z6=$z6+$mis[64][$car]+$mos[64][$car]; } $mnew[$byear] = $z6; } $sql = "truncate eli28"; $exe= mysql_query($sql,$link); for ($byear=1950;$byear 1990;$byear++){ $x=$mnew[$byear]; $sql = "insert into eli28 values($byear,$x)"; $exe= mysql_query($sql,$link); } $close_flag = mysql_close($link); print("28 男子通算年金新規裁定者2 計算終了"); ?
https://w.atwiki.jp/ce00582/pages/2329.html
?php require("calldata.php"); $sql = "SELECT * FROM plan"; $result = mysql_query($sql, $link); while( $row = mysql_fetch_row( $result ) ){ $plan = $row[0]; } $sql = "SELECT * FROM policy"; $result = mysql_query($sql, $link); $n=1; while( $row = mysql_fetch_row( $result ) ){ $matrix[$n][1] = $row[0]; $matrix[$n][2] =$row[1]; $matrix[$n][3] =$row[2]; $matrix[$n][4] =$row[3]; $n=$n+1; } $maxn=$n; for ($n=1;$n $maxn;$n++){ $num=$matrix[$n][1]; $px1[$num]=$matrix[$n][2]; $px2[$num]=$matrix[$n][3]; $px3[$num]=$matrix[$n][4]; } $p = $px1[$plan]; $g = $px2[$plan]; $r= $px3[$plan]; $sql = "SELECT * FROM eli40"; $result = mysql_query($sql, $link); $n=1; while( $row = mysql_fetch_row( $result ) ){ $matrix[$n][1] = $row[0]; $matrix[$n][2] =$row[1]; $matrix[$n][3] =$row[2]; $matrix[$n][4] =$row[3]; $n=$n+1; } $maxn=$n; for ($n=1; $n $maxn; $n++) { $year =$matrix[$n][1]; $age =$matrix[$n][2]; $mj[$year][$age] = $matrix[$n][3]; $fj[$year][$age] = $matrix[$n][4]; } $sql = "SELECT * FROM eli35"; $result = mysql_query($sql, $link); $n=1; while( $row = mysql_fetch_row( $result ) ){ $matrix[$n][1] = $row[0]; $matrix[$n][2] =$row[1]; $matrix[$n][3] =$row[2]; $n=$n+1; } $maxn=$n; for ($n=1; $n $maxn; $n++) { $byear =$matrix[$n][1]; $mtime[$byear] = $matrix[$n][2]; $ftime[$byear] = $matrix[$n][3]; } $sql = "SELECT * FROM eli90"; $result = mysql_query($sql, $link); $n=1; while( $row = mysql_fetch_row( $result ) ){ $matrix[$n][1] = $row[0]; $matrix[$n][2] =$row[1]; $matrix[$n][3] =$row[2]; $n=$n+1; } $maxn=$n; for ($n=1; $n $maxn; $n++) { $byear =$matrix[$n][1]; $mwage[$byear] = $matrix[$n][2]; $fwage[$byear] = $matrix[$n][3]; } $sql = "SELECT * FROM eli77"; $result = mysql_query($sql, $link); $n=1; while( $row = mysql_fetch_row( $result ) ){ $matrix[$n][1] = $row[0]; $matrix[$n][2] =$row[1]; $n=$n+1; } $maxn=$n; for ($n=1; $n $maxn; $n++) { $byear =$matrix[$n][1]; $jyo[$byear] = $matrix[$n][2]; } $sql = "SELECT * FROM eli92"; $result = mysql_query($sql, $link); $n=1; while( $row = mysql_fetch_row( $result ) ){ $matrix[$n][1] = $row[0]; $matrix[$n][2] =$row[1]; $matrix[$n][3] =$row[2]; $n=$n+1; } $maxn=$n; for ($n=1; $n $maxn; $n++) { $byear =$matrix[$n][1]; $age =$matrix[$n][2]; $pslide[$byear][$age] = $matrix[$n][3]; } for ($byear=1910;$byear 1945;$byear++){ $kg[$byear]=1; } for ($byear=1945;$byear 2035;$byear++){ $kg[$byear]=(1+$g)*$kg[$byear-1]; } for ($year=10;$year 100;$year++){ for ($age=60;$age 100;$age++){ $byear=$year-$age+2000; $syear=$byear; if ($syear 1950)$syear=1950; $e1=0; $e1=$e1+0.75*260*$kg[$byear]*$pslide[$byear][$age]*$mwage[$syear]*$mtime[$byear]*$mj[$year][$age]*$jyo[$byear]/1000; $e1=$e1+0.75*220*$kg[$byear]*$pslide[$byear][$age]*$fwage[$syear]*$ftime[$byear]*$fj[$year][$age]*$jyo[$byear]/1000; $iz[$year][$age]=$e1/10000; } } $sql = "truncate eli63"; $exe= mysql_query($sql,$link); for ($year=10;$year 100;$year++){ for ($age=60;$age 100;$age++){ $x=$iz[$year][$age]; $sql = "insert into eli63 values($year,$age,$x)"; $exe= mysql_query($sql,$link); } } $close_flag = mysql_close($link); print("63 世代遺族年金 計算終了"); ?
https://w.atwiki.jp/ce00582/pages/456.html
?php require("calldata.php"); $sql = "SELECT * FROM ordata13"; $result = mysql_query($sql, $link); $n=1; while( $row = mysql_fetch_row( $result ) ){ $matrix[$n][1] = $row[0]; $matrix[$n][2] =$row[1]; $matrix[$n][3] =$row[2]; $n=$n+1; } for ($n=1; $n 91; $n++) { $no=$matrix[$n][1]; $m=$matrix[$n][2]; $bra[$no][$m]=$matrix[$n][3]; } for ($age=15; $age 65; $age++) { for ($car=1; $car 46; $car++) { $no=floor($age/5)-2; $m=floor(($car-1)/5)+1; $mhi[$age][$car]=$bra[$no][$m]/250; } } $sql = "truncate pdata15"; $exe= mysql_query($sql,$link); for ($age=15; $age 65; $age++) { for ($car=1; $car 45; $car++) { $x=$mhi[$age][$car]; $sql = "insert into pdata15 values($age,$car,$x)"; $exe= mysql_query($sql,$link); } } $close_flag = mysql_close($link); ?
https://w.atwiki.jp/ce00582/pages/2306.html
?php require("calldata.php"); $sql = "SELECT * FROM data85"; $result = mysql_query($sql, $link); $n=1; while( $row = mysql_fetch_row( $result ) ){ $matrix[$n][1] = $row[0]; $matrix[$n][2] =$row[1]; $matrix[$n][3] =$row[2]; $n=$n+1; } $maxn=$n; for ($n=1; $n $maxn; $n++) { $age =$matrix[$n][1]; $car = $matrix[$n][2]; $wage[$age][$car]= $matrix[$n][3]; } For ($age = 15;$age 70;$age++){ For ($car = 1;$car 50;$car++){ $mwage[$age][$car]=$wage[$age][$car]/($car*240*10000); } } $sql = "truncate eli85"; $exe= mysql_query($sql,$link); For ($age = 15;$age 70;$age++){ For ($car = 1;$car 50;$car++){ $x=$mwage[$age][$car]; $sql = "insert into eli85 values($age,$car,$x)"; $exe= mysql_query($sql,$link); } } $close_flag = mysql_close($link); print("85 男子待期報酬 計算終了"); ?
https://w.atwiki.jp/ce00582/pages/458.html
?php require("calldata.php"); $sql = "SELECT * FROM ordata15"; $result = mysql_query($sql, $link); $n=1; while( $row = mysql_fetch_row( $result ) ){ $matrix[$n][1] = $row[0]; $matrix[$n][2] =$row[1]; $matrix[$n][3] =$row[2]; $n=$n+1; } for ($n=1; $n 91; $n++) { $no=$matrix[$n][1]; $m=$matrix[$n][2]; $bra[$no][$m]=$matrix[$n][3]; } for ($age=15; $age 65; $age++) { for ($car=1; $car 46; $car++) { $no=floor($age/5)-2; $m=floor(($car-1)/5)+1; $mhi[$age][$car]=$bra[$no][$m]/250; } } $sql = "truncate pdata15"; $exe= mysql_query($sql,$link); for ($age=15; $age 65; $age++) { for ($car=1; $car 45; $car++) { $x=$mhi[$age][$car]; $sql = "insert into pdata15 values($age,$car,$x)"; $exe= mysql_query($sql,$link); } } $close_flag = mysql_close($link); ?
https://w.atwiki.jp/nerikesi/pages/21.html
mysqlとApacheをコマンドプロンプトから起動する(windwos) mysql 起動 C \mysql\bin net start mysql 終了 C \mysql\bin net stop mysql Apache 起動 C \WINDOWS C \Apache\apache.exe -k start 終了 C \WINDOWS C \Apache\apache.exe -k stop
https://w.atwiki.jp/ce00582/pages/2231.html
?php require("calldata.php"); $sql = "SELECT * FROM data19"; $result = mysql_query($sql, $link); $n=1; while( $row = mysql_fetch_row( $result ) ){ $matrix[$n][1] = $row[0]; $matrix[$n][2] =$row[1]; $matrix[$n][3] =$row[2]; $n=$n+1; } $maxn=$n; for ($n=1; $n $maxn; $n++) { $age =$matrix[$n][1]; $car =$matrix[$n][2]; $mint[$age][$car] = $matrix[$n][3]; } for ($age = 15;$age 70;$age++){ for ($car = 1;$car 51;$car++){ $mint[$age][$car]=$mint[$age][$car]/10000; } } $sql = "truncate eli19"; $exe= mysql_query($sql,$link); for ($age = 15;$age 70;$age++){ for ($car = 1;$car 51;$car++){ $x=$mint[$age][$car]; $sql = "insert into eli19 values($age,$car,$x)"; $exe= mysql_query($sql,$link); } } $close_flag = mysql_close($link); print("19 男子被保険者 計算終了"); ?
https://w.atwiki.jp/ce00582/pages/459.html
?php require("calldata.php"); $sql = "SELECT * FROM ordata16"; $result = mysql_query($sql, $link); $n=1; while( $row = mysql_fetch_row( $result ) ){ $matrix[$n][1] = $row[0]; $matrix[$n][2] =$row[1]; $matrix[$n][3] =$row[2]; $n=$n+1; } for ($n=1; $n 91; $n++) { $no=$matrix[$n][1]; $m=$matrix[$n][2]; $bra[$no][$m]=$matrix[$n][3]; } for ($age=15; $age 65; $age++) { for ($car=1; $car 46; $car++) { $no=floor($age/5)-2; $m=floor(($car-1)/5)+1; $mhi[$age][$car]=$bra[$no][$m]/250; } } $sql = "truncate pdata16"; $exe= mysql_query($sql,$link); for ($age=15; $age 65; $age++) { for ($car=1; $car 45; $car++) { $x=$mhi[$age][$car]; $sql = "insert into pdata16 values($age,$car,$x)"; $exe= mysql_query($sql,$link); } } $close_flag = mysql_close($link); ?