engchina的专栏

CREATE OR REPLACE FUNCTION FUNC1 ( N IN NUMBER) RETURN NUMBER/**************************************************************/-- 普通のループ処理で、1からNまでの足し込みを行うファンクション/*************************************

SELECT * FROM TEST02; A B C---------- ---------- ----------1 EMP 79342 DEPT 10CREATE OR REPLACE PACKAGE PAC2IS/*******************************

SELECT * FROM TABLE(pac1.f1(5)) WHERE ROWNUM <= 2; COL1 COL2---------- ---------- 1 ABC1 2 ABC2CREATE OR REPLACE PACKAGE BODY pac1IS/***********************************

CREATE OR REPLACE PACKAGE pac1IS/**********************************************************//** レコード型の宣言 (テーブル・ファンクションの行の型) *//**********************************************************/ T

CREATE OR REPLACE VIEW V_GSALASSELECT D.DEPTNO, D.DNAME, SUM(E.SAL) AS GSALFROM EMP E, DEPT DWHERE E.DEPTNO = D.DEPTNOGROUP BY D.DEPTNO, D.DNAME/SELECT * FROM V_GSAL; DEPTNO DNAME

CREATE OR REPLACE VIEW V_TEST1ASSELECT EMPNO, ENAME, LOWER(ENAME) AS LOW_NAME FROM EMP;SELECT * FROM V_TEST1; EMPNO ENAME LOW_NAME---------- ---------- ---------- 7369 SMITH

CREATE OR REPLACE PACKAGE PAC1IS -- PROCAプロシージャの仕様の宣言 PROCEDURE PROCA ( PA IN NUMBER);END;/CREATE OR REPLACE PACKAGE BODY PAC1IS -- 変数 V1の宣言 V1 NUMBER; -- PROCBプロシージャの完全な定義 PROCEDURE PROCB ( PB IN NUM

CREATE PACKAGE PAC1IS PROCEDURE PROC1 ( P1 IN VARCHAR2);END;/CREATE PACKAGE BODY PAC1IS PROCEDURE PROC1 ( P1 IN VARCHAR2) ISBEGIN DBMS_OUTPUT.PUT_LINE(P1);END;END;SET SERVERO

SET serveroutput ONSET verify OFFDECLAREtype a_typeIS TABLE OF VARCHAR2(10) INDEX BY binary_integer; a a_type; I binary_integer :=1;BEGIN a(5) := 'five'; a(-3) := 'minus 3'; a(0) :=

SET serveroutput ONSET verify OFFDECLAREtype a_typeIS TABLE OF VARCHAR2(10) INDEX BY binary_integer; a a_type;BEGIN a(10) := 'ABC'; a(20) := 'DEF'; a(30) := 'GHI'; dbms_output.put_lin

SET serveroutput ONSET verify OFFDECLARE rec2 emp%rowtype;BEGIN SELECT * INTO rec2 FROM EMP WHERE employee_id = 100; dbms_output.put_line(REC2.employee_id || ' ' || REC2.first_name );END;/

SET serveroutput ONSET verify OFFBEGIN FOR rec1 IN (SELECT * FROM DEPT ) LOOP DBMS_OUTPUT.PUT_LINE(CHR(10) || '### 部門名 : ' || REC1.department_name || ' ####'); -- CHR(10)换行 FOR rec2

定义CursorSET serveroutput ONSET verify OFFDECLARE CURSOR cur_emp IS SELECT employee_id, upper(first_name) upname, salary*12 annsal FROM emp WHERE department_id = &

SET serveroutput ONSET verify OFFDECLARE CURSOR cur_emp IS SELECT employee_id, upper(first_name) upname, salary*12 annsal FROM emp WHERE department_id = &deptno;

SET serveroutput ONSET verify OFFDECLARE CURSOR cur_emp IS SELECT employee_id, first_name FROM emp WHERE department_id = &deptno; rec cur_emp%rowtype;BEGIN OPEN cur_

SET serveroutput ONSET verify ONDECLARE v_empno emp.employee_id%type := &emp_no; v_ename emp.first_name%type; v_deptno dept.department_id%type; v_dname dept.department_name%type;BEGIN SEL

CREATE GLOBAL TEMPORARY TABLE TEMP01( A NUMBER PRIMARY KEY, B VARCHAR2(10))/

CREATE OR REPLACE PACKAGE PAC1IS /* 同じ名前のプロシージャ３つ ただしパラメータの型や数が違う*/ PROCEDURE PROC1( P1 IN NUMBER); PROCEDURE PROC1( P1 IN VARCHAR2); PROCEDURE PROC1( P1 IN VARCHAR2,

CREATE OR REPLACE PACKAGE PAC1IS A NUMBER;END;/show err;/BEGIN PAC1.A := 10;END;/show err;/BEGIN DBMS_OUTPUT.PUT_LINE(PAC1.A);END;/show err;/outputPackage PAC1がコンパイルされましたエラー

SET VERIFY OFFSET SERVEROUTPUT ONDECLARE V_TABLE_NAME VARCHAR2(100); -- 表名 V_PK_NAME VARCHAR2(100); -- 主キー制約の名前 V_COLUMNS VARCHAR2(3000); -- 列名の取得(列1,列2,・・)BEGIN -- 表名の取得(キーボードから入力)

SET serveroutput ONDECLARE fh utl_file.file_type; v_line VARCHAR2(32767);BEGIN fh := utl_file.fopen('DATA_PUMP_DIR','test.txt','R'); LOOP utl_file.get_line(fh,v_line); dbms_output.pu

DECLARE fh utl_file.file_type;BEGIN fh := utl_file.fopen('DATA_PUMP_DIR','test.txt','W'); utl_file.put_line(fh,'hello'); utl_file.put_line(fh,'how are you'); utl_file.fclose(fh);END;/

CREATE OR REPLACE PROCEDURE get_name( p_id IN NUMBER, p_name OUT VARCHAR2)IS v_name emp.first_name%type;BEGIN EXECUTE immediate 'select first_name from emp where employee_id = :EMPID'

DECLAREtype cur_emp_typeIS ref CURSOR; cur_emp cur_emp_type; rec emp%rowtype; BEGIN OPEN cur_emp FOR 'select * from emp where department_id = 20'; LOOP FETCH cur_

DECLARE V_ENAME EMP.FIRST_NAME%TYPE;BEGIN EXECUTE IMMEDIATE 'SELECT FIRST_NAME FROM EMP WHERE EMPLOYEE_ID = 200' INTO V_ENAME; DBMS_OUTPUT.PUT_LINE('取得した社員名 = ' || V_ENAME);END;/PL/SQLプロ

select * from dept;SET serveroutput ONDECLARE v1 VARCHAR2(10) := 'DEPT'; v2 VARCHAR2(10) := '99'; v3 VARCHAR2(10) :='TEST'; v_stmt VARCHAR2(100);BEGIN v_stmt :='INSERT INTO '

ALTER TABLE DEPARTMENTS ADD ( GSAL NUMBER);DESC DEPT;UPDATE DEPTSET GSAL = ( SELECT SUM(SALARY) FROM EMP WHERE DEPARTMENT_ID = DEPT.DEPARTMENT_ID );SELECT

CREATE TABLE TEST02 ( A NUMBER, 登録日 DATE, 最終更新日 DATE ) ; /CREATE OR REPLACE TRIGGER TEST02_TRIG before INSERT OR UPDATE ON test02 FOR EACH row BEGIN IF inserting THEN :NEW.登録日 := SYSDATE;

CREATE TABLE TEST01 (ID NUMBER, NAME VARCHAR2(10) );CREATE TABLE AUDIT_TEST01 ( USERNAME VARCHAR2(10), OP_TIME VARCHAR2(30), OLD_ID NUMBER, NEW_ID NUMBER, OLD_NAME VARC

CREATE OR REPLACE TRIGGER 注文_在庫_TRIG AFTER INSERT ON 注文　FOR EACH ROW BEGIN UPDATE 在庫 SET 在庫数 = 在庫数 - :NEW.注文数 WHERE 製品名 = :NEW.製品名;END;DECLARE V_注文ID 注文.注文ID%TYPE;BEGIN PROC_順序番号('注文ID',V_

CREATE OR REPLACE TRIGGER TRIG_EMP_CHECK1 BEFORE INSERT OR DELETE OR UPDATE ON EMP DECLARE V_DATE VARCHAR2(5); BEGIN V_DATE := TO_CHAR(SYSDATE,'HH24:MI'); IF V_DATE BETWEEN '08:00' AND '15:00' THEN NU

CREATE OR REPLACE PACKAGE PAC1IS -- 最初にこのパッケージをコールした日時 --を格納する変数 FirstCall DATE;END;/show err;/CREATE OR REPLACE PACKAGE BODY PAC1IS -- IS の下は宣言部だがここでは宣言するものなし -- 以下のBEGINから実行部（初期化ブロッ

set serveroutput onset verify onCREATE OR REPLACE PACKAGE PAC1IS FK_ERROR EXCEPTION; PRAGMA EXCEPTION_INIT(FK_ERROR,-2292); -- ORA-2292 外部Key制约END;/show err;/DECLARE V_DEPTNO NUMBER :=

set serveroutput onCREATE OR REPLACE PACKAGE PAC1IS RITU CONSTANT NUMBER := 5; --PAC1パッケージに定数RITUを宣言END;/show err;/CREATE OR REPLACE FUNCTION FUNC_税額( P_KINGAKU IN NUMBER )RETURN NUMBERIS

CREATE OR REPLACE FUNCTION FUNC_順序番号 --←キーワードはFUNCTION ( P_発番単位 IN VARCHAR2) --←仮パラメータのモードは INだけ RETURN NUMBER --←RETURNするデータ型を宣言するIS V_発番済番号 順序.番号%TYPE; V_番号 NUMBER;BEGIN -- 指

SET serveroutput ONSET verify OFFDECLAREtype a_typeIS TABLE OF VARCHAR2(10) INDEX BY binary_integer; a a_type;BEGIN a(5) := 'five'; a(-3) := 'minus 3'; a(0) := 'zero'; a(7) := 'sev

CREATE OR REPLACE PROCEDURE proc_tax( p1 IN NUMBER, p2 OUT NUMBER)ISBEGIN p2 := p1*1.17;END;/SET serveroutput ONDECLARE v_input NUMBER(5); v_output NUMBER(5);BEGIN v_input :=1

CREATE TABLE OYA ( ID NUMBER CONSTRAINT OYA_PK PRIMARY KEY, C1 VARCHAR2(10)) /CREATE TABLE KO ( ID NUMBER CONSTRAINT KO_PK PRIMARY KEY, C1 VARCHAR2(10), OYA_ID NUMBER CONSTRAI

SELECT * FROM TEST86; COL1----------123a123123b1c23方法1SELECT * FROM TEST86 WHERE REGEXP_LIKE(COL1,'[^0-9]'); COL1----------a123123b1c23方法2SET SERVEROUTPUT ONDECLARE V_DUMMY

CREATE OR REPLACE PROCEDURE PROC85ISBEGIN DBMS_OUTPUT.PUT_LINE('こんにちは。今日は寒いですね');END;/SQL> show userユーザーは"SCOTT"です。SQL> @input.sqlwrap iname=input.sql oname=output.plbCREATE OR REP