カテゴリー別アーカイブ: 未分類

エッグタルト

[カスタードクリーム]
卵黄 2個
牛乳 100ml
生クリーム 100ml
グラニュー糖 100g
薄力粉 大さじ2

[生地]
薄力粉 140g
バター 85g
冷水 大さじ2
酢 小さじ1

[手順]
1. カスタードクリームを作る
2. 薄力粉とバターを混ぜる(スコーンの要領)
3. 冷水と酢を加えて生地を整える
4. 生地を型に敷き、底にフォークで穴を空ける
5. カスタードクリームを入れて、形を整える
6. 210度で20分焼く

ミルクババロア

牛乳              280ml
生クリーム    200ml
砂糖              30g
ゼラチン        5g
水                  20ml

1. 半分の量の牛乳と砂糖を混ぜてレンジで1分暖める
2. ゼラチンを溶かして混ぜる
3. 残りの牛乳を入れて混ぜる
4. ボールを氷水で冷やしながら、生クリームを少しずつ入れて混ぜる
5. 型に入れて冷蔵庫で冷やす

Oracle SQLのメモ

以前howmやらテキスト管理していたものをひとつにまとめた
整理はされていない

@@basic
create table (val1 number,val2 varchar(10)var3 date)

insert into NPI.TEST_TABLE(val1,val2) values(10,’val2′)
insert into NPI.TEST_TABLE values(10,’val2′,sysdate)

update NPI.TEST_TABLE set val2=’updated’,val3=sysdate where val1=10
//複数行を一気に更新
update TABLE1 TB1
set VAL1=
(
select TB2.VAL1
from TABLE2 TB2
where TB1.ID=TB2.ID
)
where TB1.VAL1 is NULL

delete from NPI.TEST_TABLE where val1=10

delte from TEST_TABLE where (val1,val2,val3…)IN
( select val1,val2,val3…
from …
)

alter table NPI.TEST_TABLE add(val4 number)
alter table NPI.TEST_TABLE modify(val4 date)
alter table NPI.TEST_TABLE drop(val4)
alter table NPI.TEST_TABLE RENAME COLUMN val3 to day

alter table NPI.TEST_TABLE add primary key (
val1,
val2,
val3,

)

–マテビューの更新
alter materialized view NPI.TEST_MV refresh complete

–アスタリスクと疑似列を併用するときはテーブル名をつける
select table_name.*,rownum form table_name

@@intermidiate
/*本日から月末までの日付を取得*/
select (to_date(to_date(sysdate),’yyyy/mm/dd’) + rownum – 1) serial_d
from all_catalog
where (to_date(sysdate) + rownum – 1) <= last_day(sysdate) 前レコードとの差分 鵜開始時刻-lag(完了時刻)over(order by 完了時刻))*1380 累計 sum(数値フィールド)over(order by 順序フィールド) 直線回帰の関数(オラクル) REGR_SLOPE(y,x) REGR_INTERCEPT(y,x) REGR_R2(y,x) @@各種一覧取得 @ソース一覧 all_source @テーブルの一覧 select * from DICT select * from USER_TABLES; @カラムのコメント一覧 ALL_COL_COMMENTS @インデックス一覧 select * from USER_INDEXES; @トリガー一覧 select * from USER_TRIGGERS; select * from user_objects where object_type = 'TRIGGER'; @実行sql一覧(dba) select * from v$sql where PARSING_SCHEMA_NAME='スキーマ名'; @オブジェクト一覧 select OBJECT_NAME, OBJECT_TYPE from USER_OBJECTS; @セッション一覧 select * from v$session @バインド変数 variable v1 number; //変数の設定 execute :v1 :=100; //変数に代入 select :v1 from all_catalog where rownum<5; //変数の使用 @@sqlplus & R in /User/h-ito/R/SQL
#日付j抽出はデフォルトで 01-JAN-12 となるので注意
(下の設定で文字コードをshift-jisにすると2012/01/01とできる)

#sqlplusの文字コードは.bash_profileに以下を記述(shift-jisの場合)
export NLS_LANG=Japanese_Japan.JA16SJIS

spool DAT/out.txt
@SQL/temp.sql
spool off


>read.table(“DAT/out.txt”)

@@output as csv
#!/bin/sh
sqlplus -s ANSWER/ANSWER@192.168.1.30:1521/ANSWER << EOSQL set echo off set linesize 1000 set heading off set underline off set feedback off set pagesize 0 set trimspool on set termout off #結果の出力 set colsep ',' spool /Users/h-ito/R/DAT/out.csv @$1 spool off quit EOSQL exit 1 @@plsql ###standard output in sqlplus set serveroutput on execute procedure_name ###create procedure or function### create or replace procedure NPI.TEST_PROC is num number:=10; begin DBMS_OUTPUT.PUT_LINE('num:' || num); end; ##insert create or replace procedure NPI.TEST_PROC is begin delete tablename1; insert into tablename1 ( select * from tablename2 ); end; create or replace function NPI.TEST_PROC(x number,y number) return number is return null; end; ###declare vaiables x number:=10; ###if if (x=10) then DBMS_OUTPUT.PUT_LINE('x=10'); else DBMS_OUTPUT.PUT_LINE('others'); end if; ###case case x when 1 then DBMS_OUTPUT.PUT_LINE('one'); when 2 then DBMS_OUTPUT.PUT_LINE('two'); when 3 then DBMS_OUTPUT.PUT_LINE('three'); else DBMS_OUTPUT.PUT_LINE('other'); end case; ###for for num in 0..5 loop DBMS_OUTPUT.PUT_LINE(num); end loop; ###while while (num<15) loop DBMS_OUTPUT.PUT_LINE(num); num:=num+1; end loop; ###cursor #inline for r in (select * from all_catalog where rownum<10) loop DBMS_OUTPUT.PUT_LINE(r.owner); end loop; #outline cursor cur is select * from all_catalog where rownum<10; begin for r in cur loop DBMS_OUTPUT.PUT_LINE(r.owner); end loop; #open and fetch cursor cur is select * from all_catalog where rownum<10; rcur cur%rowtype; begin open cur; loop fetch cur into rcur; exit when cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(rcur.owner); end loo; close cur; #Attribute cursor%FOUND cursor%NOTFOUND cursor%ISOPEN cursor%ROWCOUNT *文字列の切り出し left,right,midに相当*/ substr(文字列,開始位置,終了位置) --文字数で切りだす substrb(文字列,開始位置,終了位置) --バイトで切りだす /*文字列の連結*/ RPAD(文字列 , 指定桁数) 文字列の右端に指定桁数、空白(半角)を連結する RPAD(文字列 , 指定桁数 , 埋め込み文字) 文字列の右端に指定桁数、埋め込み文字を連結する LPAD(文字列 , 指定桁数) 文字列の左端に指定桁数、空白(半角)を連結する LPAD(文字列 , 指定桁数 , 埋め込み文字) 文字列の左端に指定桁数、埋め込み文字を連結する /*****make function*****/ CREATE OR REPLACE FUNCTION TEST_NPI.TESTFUNC1 (P1 in number) RETURN number IS val number; BEGIN case P1 when 1 then val:=10; when 2 then val:=20; else val:=30; end case; return val; END; /*SQLをbeginの中で扱う begin select get_val(dat) into val --intoで代入 from all_catalog; return val; --代入された値をreturn end; */ /*****use parameters in cursor*****/ CREATE OR REPLACE FUNCTION NPI.COR_SPEED(ZAIBAN in ANSWER.MS_ZAISITU.ZAIBAN%TYPE) RETURN number IS /********put parameters after name********/ cursor CUR(ZAIBAN ANSWER.MS_ZAISITU.ZAIBAN%TYPE) is select * from answer.MS_ZAISITU MZ where MZ.ZAIBAN = ZAIBAN; ZAI ANSWER.MS_ZAISITU%ROWTYPE; //a record set variable val1 number; val2 number; val3 number; BEGIN /********put parameters when open cursor*******/ open CUR(ZAIBAN); fetch CUR into ZAI; fetch CUR into val1, val2, val3; //must be fitted with ZAI return ZAI.ZAIBAN; close CUR; END ; / ###exception BEGIN SELECT ... SELECT ... SELECT ... ... EXCEPTION WHEN NO_DATA_FOUND THEN -- catches all 'no data found' errors #variables ACCESS_INTO_NULL Your program attempts to assign values to the attributes of an uninitialized (atomically null) object. CASE_NOT_FOUND None of the choices in the WHEN clauses of a CASE statement is selected, and there is no ELSE clause. COLLECTION_IS_NULL Your program attempts to apply collection methods other than EXISTS to an uninitialized (atomically null) nested table or varray, or the program attempts to assign values to the elements of an uninitialized nested table or varray. CURSOR_ALREADY_OPEN Your program attempts to open an already open cursor. A cursor must be closed before it can be reopened. A cursor FOR loop automatically opens the cursor to which it refers. So, your program cannot open that cursor inside the loop. DUP_VAL_ON_INDEX Your program attempts to store duplicate values in a database column that is constrained by a unique index. INVALID_CURSOR Your program attempts an illegal cursor operation such as closing an unopened cursor. INVALID_NUMBER In a SQL statement, the conversion of a character string into a number fails because the string does not represent a valid number. (In procedural statements, VALUE_ERROR is raised.) This exception is also raised when the LIMIT-clause expression in a bulk FETCH statement does not evaluate to a positive number. LOGIN_DENIED Your program attempts to log on to Oracle with an invalid username and/or password. NO_DATA_FOUND A SELECT INTO statement returns no rows, or your program references a deleted element in a nested table or an uninitialized element in an index-by table. SQL aggregate functions such as AVG and SUM always return a value or a null. So, a SELECT INTO statement that calls an aggregate function never raises NO_DATA_FOUND. The FETCH statement is expected to return no rows eventually, so when that happens, no exception is raised. NOT_LOGGED_ON Your program issues a database call without being connected to Oracle. PROGRAM_ERROR PL/SQL has an internal problem. ROWTYPE_MISMATCH The host cursor variable and PL/SQL cursor variable involved in an assignment have incompatible return types. For example, when an open host cursor variable is passed to a stored subprogram, the return types of the actual and formal parameters must be compatible. SELF_IS_NULL Your program attempts to call a MEMBER method on a null instance. That is, the built-in parameter SELF (which is always the first parameter passed to a MEMBER method) is null. STORAGE_ERROR PL/SQL runs out of memory or memory has been corrupted. SUBSCRIPT_BEYOND_COUNT Your program references a nested table or varray element using an index number larger than the number of elements in the collection. SUBSCRIPT_OUTSIDE_LIMIT Your program references a nested table or varray element using an index number (-1 for example) that is outside the legal range. SYS_INVALID_ROWID The conversion of a character string into a universal rowid fails because the character string does not represent a valid rowid. TIMEOUT_ON_RESOURCE A time-out occurs while Oracle is waiting for a resource. TOO_MANY_ROWS A SELECT INTO statement returns more than one row. VALUE_ERROR An arithmetic, conversion, truncation, or size-constraint error occurs. For example, when your program selects a column value into a character variable, if the value is longer than the declared length of the variable, PL/SQL aborts the assignment and raises VALUE_ERROR. In procedural statements, VALUE_ERROR is raised if the conversion of a character string into a number fails. (In SQL statements, INVALID_NUMBER is raised.) ZERO_DIVIDE Your program attempts to divide a number by zero.