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;

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;

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;

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';

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;

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;