动态SQL

范例：利用动态SQL在执行时创建一张数据表

CREATE OR REPLACE FUNCTION get_table_count_fun(p_table_name VARCHAR2) RETURN NUMBER AS

v_sql_statement VARCHAR2(200) ; -- 定义操作的SQL语句

v_count NUMBER ; -- 保存表中记录

BEGIN

SELECT COUNT(*) INTO v_count FROM user_tables WHERE table_name=UPPER(p_table_name) ;

IF v_count = 0 THEN -- 数据表不存在

v_sql_statement := 'CREATE TABLE ' || p_table_name ||

' ( id NUMBER ,

name VARCHAR2(30) NOT NULL ,

CONSTRAINT pk_id_' || p_table_name || ' PRIMARY KEY(id)) ' ; -- 创建数据表

EXECUTE IMMEDIATE v_sql_statement ; -- 执行动态SQL

END IF ;

v_sql_statement := 'SELECT COUNT(*) FROM ' || p_table_name ; -- 查询数据表记录

EXECUTE IMMEDIATE v_sql_statement INTO v_count ; -- 执行动态SQL并保存数据记录

RETURN v_count ;

END ;

范例：直接在程序中编写DDL或DML

CREATE OR REPLACE FUNCTION get_table_count_fun(p_table_name VARCHAR2) RETURN NUMBER AS

v_sql_statement VARCHAR2(200) ; -- 定义操作的SQL语句

v_count NUMBER ; -- 保存表中记录

BEGIN

SELECT COUNT(*) INTO v_count FROM user_tables WHERE table_name=UPPER(p_table_name) ;

IF v_count = 0 THEN -- 数据表不存在

-- 错误：无法直接使用DDL操作

CREATE TABLE p_table_name (

idNUMBER,

nameVARCHAR2(30)NOT NULL ,

CONSTRAINT id_pk PRIMARY KEY(id)) ;

END IF ;

-- 错误：查询数据表不存在

SELECT COUNT(*) INTO v_count FROM p_table_name ;

RETURN v_count ;

END ;

范例：编写PL/SQL块调用函数

BEGIN

DBMS_OUTPUT.put_line('数据表记录：' || get_table_count_fun('mldnjava')) ;

END ;

范例：为c##scott用户授权

CONN sys/change_on_install AS SYSDBA ;

GRANT CREATE ANY TABLE TO c##scott ;

CONN c##scott/tiger ;

范例：使用动态SQL创建表和PL/SQL块

DECLARE

v_sql_statement VARCHAR2(200) ;

v_count NUMBER ; -- 保存查找结果

BEGIN

SELECT COUNT(*) INTO v_count FROM user_tables WHERE table_name='MLDN_TAB' ;

IF v_count = 0 THEN -- 数据表不存在

v_sql_statement := 'CREATE TABLE mldn_tab(

id NUMBER PRIMARY KEY ,

url VARCHAR2(50) NOT NULL)' ; -- 定义动态SQL

EXECUTE IMMEDIATE v_sql_statement ;

ELSE -- 数据表存在

v_sql_statement := 'TRUNCATE TABLE mldn_tab' ;

EXECUTE IMMEDIATE v_sql_statement ;

END IF ;

v_sql_statement := 'BEGIN

FOR x IN 1 .. 10 LOOP

INSERT INTO mldn_tab(id,url) VALUES (x , ''www.mldnjava.cn - '' || x) ;

END LOOP ;

END ;' ;

EXECUTE IMMEDIATE v_sql_statement ;

COMMIT ; -- 提交事务

END ;

范例：查询mldn_tab数据表

SELECT * FROM mldn_tab ;

范例：使用绑定变量

DECLARE

v_sql_statement VARCHAR2(200) ;

v_deptno dept.deptno%TYPE := 60 ;

v_dname dept.dname%TYPE := 'MLDN' ;

v_loc dept.loc%TYPE := '北京' ;

BEGIN

v_sql_statement := 'INSERT INTO dept(deptno,dname,loc) VALUES (:dno , :dna , :dl)' ;

EXECUTE IMMEDIATE v_sql_statement USING v_deptno,v_dname,v_loc ;

COMMIT ;

END ;

范例：利用集合更新多条记录

DECLARE

v_sql_statement VARCHAR2(200) ;

TYPE deptno_nested IS TABLE OF dept.deptno%TYPE NOT NULL ;

TYPE dname_nested IS TABLE OF dept.dname%TYPE NOT NULL ;

v_deptno deptno_nested := deptno_nested(10,20,30,40) ;

v_dname dname_nested := dname_nested('财务部','研发部','销售部','操作部') ;

BEGIN

v_sql_statement := 'UPDATE dept SET dname=:dna WHERE deptno=:dno' ;

FOR x IN 1 .. v_deptno.COUNT LOOP

EXECUTE IMMEDIATE v_sql_statement USING v_dname(x),v_deptno(x) ;

END LOOP ;

COMMIT ;

END ;

范例：更新完成后查询dept表记录

SELECT * FROM dept ;

范例：查询数据

DECLARE

v_sql_statement VARCHAR2(200) ;

v_empno emp.empno%TYPE := 7369 ;

v_emprow emp%ROWTYPE ;

BEGIN

v_sql_statement := 'SELECT * FROM emp WHERE empno=:eno' ;

EXECUTE IMMEDIATE v_sql_statement INTO v_emprow USING v_empno ;

DBMS_OUTPUT.put_line('雇员编号：' || v_emprow.empno || '，姓名：' || v_emprow.ename || '，职位：' || v_emprow.job) ;

END ;

范例：在创建表时使用绑定变量

DECLARE

v_sql_statement VARCHAR2(200) ;

v_table_name VARCHAR2(200) := 'mldn' ;

v_id_column VARCHAR2(200) := 'id' ;

BEGIN

v_sql_statement := 'CREATE TABLE :tn (:ci NUMBER PRIMARY KEY)' ;

EXECUTE IMMEDIATE v_sql_statement USING v_table_name,v_id_column ;

END ;

范例：正确的代码

DECLARE

v_sql_statement VARCHAR2(200) ;

v_table_name VARCHAR2(200) := 'mldn' ;

v_id_column VARCHAR2(200) := 'id' ;

BEGIN

v_sql_statement := 'CREATE TABLE ' || v_table_name ||' (' || v_id_column ||' NUMBER PRIMARY KEY)' ;

EXECUTE IMMEDIATE v_sql_statement ;

END ;

范例：更新数据，取得更新后的结果

DECLARE

v_sql_statement VARCHAR2(200) ; -- 定义SQL操作语句

v_empno emp.empno%TYPE := 7369 ; -- 要更新的雇员编号

v_salary emp.sal%TYPE ; -- 保存更新后的sal内容

v_job emp.job%TYPE ; -- 保存更新后的job内容

BEGIN

v_sql_statement := 'UPDATE emp SET sal=sal*1.2,job=''开发'' ' ||

' WHERE empno=:eno RETURNING sal,job INTO :salary,:job' ;

EXECUTE IMMEDIATE v_sql_statement USING v_empno RETURNING INTO v_salary,v_job ;

DBMS_OUTPUT.put_line('调整后的工资：' || v_salary || '，新的职位：' || v_job) ;

END ;

范例：删除数据，取得删除前的结果

DECLARE

v_sql_statement VARCHAR2(200) ; -- 定义SQL操作语句

v_emprow emp%ROWTYPE ; -- 保存emp类型

v_empno emp.empno%TYPE := 7369 ; -- 删除的雇员编号

v_ename emp.ename%TYPE ; -- 删除的雇员姓名

v_sal emp.sal%TYPE ; -- 删除的雇员工资

BEGIN

v_sql_statement := 'DELETE FROM emp WHERE empno=:eno RETURNING ename,sal INTO :name,:sal' ;

EXECUTE IMMEDIATE v_sql_statement USING v_empno RETURNING INTO v_ename,v_sal ;

DBMS_OUTPUT.put_line('删除的雇员编号：' || v_empno || '，姓名：' || v_ename || '，工资：' || v_sal) ;

END ;

范例：编写部门增加过程

CREATE OR REPLACE PROCEDURE dept_insert_proc(

p_deptno IN OUT dept.deptno%TYPE , -- 此处可以将p_deptno的内容回传

p_dname dept.dname%TYPE, -- 默认为IN模式

p_loc dept.loc%TYPE) AS -- 默认为IN模式

BEGIN

SELECT MAX(deptno) INTO p_deptno FROM dept ; -- 取得最大的deptno内容

p_deptno := p_deptno + 1 ; -- 让最大值部门编号加1，此处不考虑超过2位数字情况

INSERT INTO dept(deptno,dname,loc) VALUES (p_deptno,p_dname,p_loc) ;

END ;

范例：编写PL/SQL块，调用过程

DECLARE

v_sql_statement VARCHAR2(200) ;

v_deptno dept.deptno%TYPE ;

v_dname dept.dname%TYPE := 'MLDN教学部' ;

v_loc dept.loc%TYPE := '北京' ;

BEGIN

v_sql_statement := 'BEGIN

dept_insert_proc(:dno , :dna , :dl) ;

END ;' ; -- 定义PL/SQL块

EXECUTE IMMEDIATE v_sql_statement USING IN OUT v_deptno , IN v_dname , v_loc ;

DBMS_OUTPUT.put_line('新增部门编号为：' || v_deptno) ;

END ;

范例：更新时使用BULK COLLECT语句

DECLARE

TYPE ename_index IS TABLE OF emp.ename%TYPE INDEX BY PLS_INTEGER ;

TYPE job_index IS TABLE OF emp.job%TYPE INDEX BY PLS_INTEGER ;

TYPE sal_index IS TABLE OF emp.sal%TYPE INDEX BY PLS_INTEGER ;

v_ename ename_index ;

v_job job_index ;

v_sal sal_index ;

v_sql_statement VARCHAR2(200) ; -- 定义动态SQL

v_deptno emp.deptno%TYPE := 10 ; -- 查询10部门

BEGIN

v_sql_statement := 'UPDATE emp SET sal=sal*1.2 WHERE deptno=:dno ' ||

' RETURNING ename,job,sal INTO :ena, :ej, :es' ; -- 此时返回多行更新结果

EXECUTE IMMEDIATE v_sql_statement USING v_deptno

RETURNING BULK COLLECT INTO v_ename,v_job,v_sal ;

FOR x IN 1 .. v_ename.COUNT LOOP

DBMS_OUTPUT.put_line('雇员姓名：' || v_ename(x) || '，职位：' || v_job(x) || '，工资：' || v_sal(x)) ;

END LOOP ;

END ;

范例：查询时使用BULK COLLECT

DECLARE

TYPE ename_index IS TABLE OF emp.ename%TYPE INDEX BY PLS_INTEGER ; -- 保存雇员姓名

TYPE job_index IS TABLE OF emp.job%TYPE INDEX BY PLS_INTEGER ; -- 保存雇员职位

TYPE sal_index IS TABLE OF emp.sal%TYPE INDEX BY PLS_INTEGER ; -- 保存雇员工资

v_ename ename_index ;

v_job job_index ;

v_sal sal_index ;

v_sql_statement VARCHAR2(200) ; -- 定义动态SQL

v_deptno emp.deptno%TYPE := 10 ; -- 查询10部门

BEGIN

v_sql_statement := 'SELECT ename,job,sal FROM emp WHERE deptno=:dno' ; -- 此时返回多行更新结果

EXECUTE IMMEDIATE v_sql_statement

BULK COLLECT INTO v_ename,v_job,v_sal

USING v_deptno ; -- 将多个结果一起返回

FOR x IN 1 .. v_ename.COUNT LOOP

DBMS_OUTPUT.put_line('雇员姓名：' || v_ename(x) || '，职位：' || v_job(x) || '，工资：' || v_sal(x)) ;

END LOOP ;

END ;

范例：通过FORALL设置多个参数

DECLARE

TYPE empno_nested IS TABLE OF emp.empno%TYPE ; -- 定义嵌套表

TYPE ename_index IS TABLE OF emp.ename%TYPE INDEX BY PLS_INTEGER ; -- 定义索引表

TYPE job_index IS TABLE OF emp.job%TYPE INDEX BY PLS_INTEGER ; -- 定义索引表

TYPE sal_index IS TABLE OF emp.sal%TYPE INDEX BY PLS_INTEGER ; -- 定义索引表

v_ename ename_index ; -- 保存删除后的姓名

v_job job_index ; -- 保存删除后的职位

v_sal sal_index ; -- 保存删除后的工资

v_empno empno_nested := empno_nested(7369,7566,7788) ; -- 定义要删除雇员编号

v_sql_statement VARCHAR2(200) ; -- 动态SQL

BEGIN

v_sql_statement := 'DELETE FROM emp WHERE empno=:eno ' || '

RETURNING ename,job,sal INTO :ena , :ej , :es' ; -- 删除数据SQL

FORALL x IN 1 .. v_empno.COUNT -- FORALL绑定多个变量

EXECUTE IMMEDIATE v_sql_statement USING v_empno(x)

RETURNING BULK COLLECT INTO v_ename,v_job,v_sal ;

FOR x IN 1 .. v_ename.COUNT LOOP

DBMS_OUTPUT.put_line('雇员姓名：' || v_ename(x) || '，职位：' || v_job(x) || '，工资：' || v_sal(x)) ;

END LOOP ;

END ;

范例：在游标中使用动态SQL

DECLARE

cur_emp SYS_REFCURSOR ; -- 定义游标变量

v_emprow emp%ROWTYPE ; -- 定义emp行类型

v_deptno emp.deptno%TYPE := 10 ; -- 定义要查询雇员的部门编号

BEGIN

OPEN cur_emp FOR 'SELECT * FROM emp WHERE deptno=:dno '

USING v_deptno ;

LOOP

FETCH cur_emp INTO v_emprow ; -- 取得游标数据

EXIT WHEN cur_emp%NOTFOUND ; -- 如果没有数据则退出

DBMS_OUTPUT.put_line('雇员姓名：' || v_emprow.ename || '，雇员职位：' || v_emprow.job) ;

END LOOP ;

CLOSE cur_emp ;

END ;

范例：利用FETCH保存查询结果

DECLARE

cur_emp SYS_REFCURSOR ; -- 定义游标变量

TYPE emp_index IS TABLE OF emp%ROWTYPE INDEX BY PLS_INTEGER ; -- 定义索引表

v_emprow emp_index ; -- 定义emp行类型

v_deptno emp.deptno%TYPE := 10 ; -- 定义要查询雇员的部门编号

BEGIN

OPEN cur_emp FOR 'SELECT * FROM emp WHERE deptno=:dno' USING v_deptno ;

FETCH cur_emp BULK COLLECT INTO v_emprow ;

CLOSE cur_emp ;

FOR x IN 1 .. v_emprow.COUNT LOOP

DBMS_OUTPUT.put_line('雇员编号：' || v_emprow(x).empno || '，姓名：' || v_emprow(x).ename || '，职位：' || v_emprow(x).job) ;

END LOOP ;

END ;

范例：查看DBMS_SQL包定义

SELECT *

FROM all_source

WHERE type='PACKAGE' AND name='DBMS_SQL' ;

范例：通过DBMS_SQL包查询数据

DECLARE

v_sql_statement VARCHAR2(200) ;

v_cid NUMBER ; -- 保存游标ID，以方便关闭

v_ename emp.ename%TYPE ;

v_job emp.job%TYPE ;

v_sal emp.sal%TYPE ;

v_stat NUMBER ;

v_deptno emp.deptno%TYPE := 10 ; -- 部门编号

BEGIN

v_cid := DBMS_SQL.open_cursor ; -- 打开游标

v_sql_statement := 'SELECT ename,job,sal FROM emp WHERE deptno=:dno' ;

DBMS_SQL.parse(v_cid , v_sql_statement ,DBMS_SQL.native) ;

DBMS_SQL.define_column(v_cid , 1 , v_ename, 10) ; -- 定义OUT模式变量

DBMS_SQL.define_column(v_cid , 2 , v_job, 9) ; -- 定义OUT模式变量

DBMS_SQL.define_column(v_cid , 3 , v_sal) ; -- 定义OUT模式变量

DBMS_SQL.bind_variable(v_cid, ':dno', v_deptno); --绑定变量

v_stat := DBMS_SQL.execute(v_cid) ; -- 执行游标，返回更新行数

LOOP

EXIT WHEN DBMS_SQL.fetch_rows(v_cid)=0 ;

DBMS_SQL.column_value(v_cid , 1 , v_ename) ;

DBMS_SQL.column_value(v_cid , 2 , v_job) ;

DBMS_SQL.column_value(v_cid , 3 , v_sal) ;

DBMS_OUTPUT.put_line('雇员姓名：' || v_ename || '，职位：' || v_job || '，薪金：' || v_sal) ;

END LOOP ;

DBMS_SQL.close_cursor(v_cid) ; -- 关闭游标

END ;

DECLARE

v_sql_statement VARCHAR2(200) ;

v_cid NUMBER ; -- 保存游标ID，以方便关闭

v_comm emp.comm%TYPE :=500 ;

v_empno emp.empno%TYPE := 7369 ;

v_stat NUMBER ;

BEGIN

v_cid := DBMS_SQL.open_cursor ; -- 打开游标

v_sql_statement := 'UPDATE emp SET comm=:ec WHERE empno=:eno' ;

DBMS_SQL.parse(v_cid , v_sql_statement ,DBMS_SQL.native) ;

DBMS_SQL.bind_variable(v_cid, ':ec', v_comm); --绑定变量

DBMS_SQL.bind_variable(v_cid, ':eno', v_empno); --绑定变量

v_stat := DBMS_SQL.execute(v_cid) ; -- 执行游标，返回更新行数

DBMS_OUTPUT.put_line('更新行数为：' || v_stat) ;

DBMS_SQL.close_cursor(v_cid) ; -- 关闭游标

END ;