動的 SQL の実行

  表作成



  
PROCEDURE 表作成
(
	PM_TABLE IN VARCHAR2
)
AUTHID CURRENT_USER
/**********************************************************/
/* 変数の定義 */
/**********************************************************/
AS
	WK_VALUE	VARCHAR2(2000) := 
	'CREATE TABLE ' || PM_TABLE || ' (' ||
	' id NUMBER ' ||
	' , value NUMBER ' ||
	')';

/**********************************************************/
/* 処理開始 */
/**********************************************************/
BEGIN
	DBMS_OUTPUT.PUT_LINE('表作成');
	DBMS_OUTPUT.PUT_LINE(PM_TABLE);
	DBMS_OUTPUT.PUT_LINE(WK_VALUE);
	EXECUTE IMMEDIATE WK_VALUE;

/**********************************************************/
/* 一番外側のブロックの例外処理 */
/**********************************************************/
EXCEPTION
	WHEN OTHERS THEN
		DBMS_OUTPUT.PUT_LINE('例外発生:'||SQLCODE||':'||SQLERRM);
END;
  


以下は、シーケンスを使用して、テーブルを作成しています

  
PROCEDURE 表作成
(
	PM_TABLE IN VARCHAR2
)
AUTHID CURRENT_USER
/**********************************************************/
/* 変数の定義 */
/**********************************************************/
AS
	WK_VALUE	VARCHAR2(2000); 
	WK_NEXTVAL NUMBER;

/**********************************************************/
/* 処理開始 */
/**********************************************************/
BEGIN
	DBMS_OUTPUT.PUT_LINE('表作成');
	DBMS_OUTPUT.PUT_LINE(PM_TABLE);
	select AUTO_INCREMENT.NEXTVAL into WK_NEXTVAL from dual; 
	WK_VALUE := 'CREATE TABLE "' || PM_TABLE || WK_NEXTVAL || '" (' ||
	' id NUMBER ' ||
	' , value NUMBER ' ||
	')';
	DBMS_OUTPUT.PUT_LINE(WK_VALUE);
	EXECUTE IMMEDIATE WK_VALUE;

/**********************************************************/
/* 一番外側のブロックの例外処理 */
/**********************************************************/
EXCEPTION
	WHEN OTHERS THEN
		DBMS_OUTPUT.PUT_LINE('例外発生:'||SQLCODE||':'||SQLERRM);
END;
  



  その他のパターン



:1 と :2 にそれぞれ、USING amount, column_value の値がバインドされる。
( :1 と :2 は数字を使っているが何でも良い )


SQL%ROWCOUNT は作用対象行数ですが、動的実行でも有効

  
sql_stmt := 'UPDATE employees SET salary = salary + :1 WHERE ' || v_column || ' = :2';

EXECUTE IMMEDIATE sql_stmt USING amount, column_value;
IF SQL%ROWCOUNT > 0 THEN
	DBMS_OUTPUT.PUT_LINE( v_column || '=' || column_value );
END IF;
  


プロシージャの動的実行


下のサンプルは、create_dept の1番目の引数が IN OUT で定義されているので、new_deptid に値が戻ってきます

  
plsql_block := 'BEGIN raise_emp_salary(:cvalue, :cname, :amt); END;';
EXECUTE IMMEDIATE plsql_block USING 110, 'DEPARTMENT_ID', 10;

plsql_block := 'BEGIN create_dept(:a, :b, :c, :d); END;';
EXECUTE IMMEDIATE plsql_block
   USING IN OUT new_deptid, new_dname, new_mgrid, new_locid;
  


いろいろな SQL

  
EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';

sql_stmt := 'INSERT INTO departments VALUES (:1, :2, :3, :4)';
EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, mgr_id, loc_id;

EXECUTE IMMEDIATE 'DELETE FROM departments WHERE ' || v_column || ' = :num'
      USING dept_id;
EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';
EXECUTE IMMEDIATE 'DROP TABLE bonus';
  


カーソルのFETCH

  
sql_stmt := 'SELECT * FROM employees WHERE job_id = :j';

OPEN emp_cv FOR sql_stmt USING v_job;

LOOP
	FETCH emp_cv INTO emp_rec;
	EXIT WHEN emp_cv%NOTFOUND;
END LOOP;

CLOSE emp_cv;
  



  RETURNING BULK COLLECT INTO句を使用した動的SQL

  
sql_stmt := 'UPDATE employees SET salary = salary + :1 
	RETURNING last_name INTO :2';
EXECUTE IMMEDIATE sql_stmt
	USING bonus_amt RETURNING BULK COLLECT INTO enames;
  










  infoboard   管理者用   
このエントリーをはてなブックマークに追加





フリーフォントWEBサービス
SQLの窓WEBサービス

SQLの窓フリーソフト

素材

一般WEBツールリンク

SQLの窓

フリーソフト

JSライブラリ