当前位置:首页 > 开发 > 数据库 > 正文

PL/SQL动态SQL用法

发表于: 2012-11-29   作者:sunjing   来源:转载   浏览次数:
摘要: (好久以前根据网上的一篇文章修改的,不记得原文网址了。) 本文介绍了PL/SQL开发EXECUTE IMMEDIATE 和 DBMS_SQL两种动态SQL的用法。 以下为完整的package,可以直接拷贝到plsql中进行测试。 ----------------------------------------------------------------------------------

(好久以前根据网上的一篇文章修改的,不记得原文网址了。)

本文介绍了PL/SQL开发EXECUTE IMMEDIATE 和 DBMS_SQL两种动态SQL的用法。

以下为完整的package,可以直接拷贝到plsql中进行测试。

--------------------------------------------------------------------------------------------------------------------------

create or replace package SUNJC_TEST is
/*
* 动态SQL
* 在Oracle开发过程中,可以使用动态SQL来执行DDL语句、DML语句、事务控制语句及系统控制语句。
*/

/***************************************
* 使用EXECUTE IMMEDIATE动态SQL--DDL建表
***************************************/
procedure proc_test(table_name in varchar2, --表名
field1 in varchar2, --字段名
datatype1 in varchar2, --字段类型
field2 in varchar2, --字段名
datatype2 in varchar2 --字段类型
);

/***************************************
* 使用EXECUTE IMMEDIATE动态SQL--DDL建表
***************************************/
procedure proc_insert(p_id in number, --输入序号
p_name in varchar2 --输入姓名
);

/*************************************
* 使用DBMS_SQL包实现动态SQL--DDL建表
*************************************/
procedure proc_dbms_sql(table_name in varchar2, --表名
field_name1 in varchar2, --字段名
datatype1 in varchar2, --字段类型
field_name2 in varchar2, --字段名
datatype2 in varchar2 --字段类型
);

/***********************************
* 使用dbms_sql动态SQL,执行数据更新
************************************/
procedure proc_dbms_sql_update(p_id number, p_name varchar2);

/************************************
* 使用dbms_sql动态SQL,执行数据查询
************************************/
procedure proc_dbms_sql_query(p_id in varchar2);

end SUNJC_TEST;
/
create or replace package body SUNJC_TEST is

/***************************************
* 使用EXECUTE IMMEDIATE动态SQL--DDL建表
***************************************/
procedure proc_test(table_name in varchar2, --表名
field1 in varchar2, --字段名
datatype1 in varchar2, --字段类型
field2 in varchar2, --字段名
datatype2 in varchar2) --字段类型
as
str_sql varchar2(500);
begin
begin
str_sql := 'create table ' || table_name || '(' || field1 || ' ' ||
datatype1 || ',' || field2 || ' ' || datatype2 || ') ';
dbms_output.put_line(str_sql);
execute immediate str_sql; --动态执行DDL语句
exception
when others then
dbms_output.put_line(sqlerrm);
end;
end proc_test;

/***************************************
* 使用EXECUTE IMMEDIATE动态SQL--insert
***************************************/
procedure proc_insert(p_id in number, --输入序号
p_name in varchar2 --输入姓名
) as
str_sql varchar2(500);
v_count integer;
begin
str_sql := 'insert into dinya_test values(:1, :2)';
execute immediate str_sql
using p_id, p_name; --动态执行插入操作

str_sql := 'select count(*) from dinya_test a where a.id=:id and a.name=:name';
execute immediate str_sql
into v_count
using p_id, p_name;

dbms_output.put_line(v_count);

exception
when others then
null;
end proc_insert;

/****************************************************
* 使用DBMS_SQL包实现动态SQL--DDL建表
*A、先将要执行的SQL语句或一个语句块放到一个字符串变量中
*B、使用DBMS_SQL包的parse过程来分析该字符串
*C、使用DBMS_SQL包的bind_variable过程来绑定变量
*D、使用DBMS_SQL包的execute函数来执行语句
*****************************************************/
procedure proc_dbms_sql(table_name in varchar2, --表名
field_name1 in varchar2, --字段名
datatype1 in varchar2, --字段类型
field_name2 in varchar2, --字段名
datatype2 in varchar2 --字段类型
) as
v_cursor number; --定义游标
v_string varchar2(200); --定义字符串变量
v_row number; --行数
begin
v_cursor := dbms_sql.open_cursor; --为处理打开游标
v_string := 'create table ' || table_name || ' ' || chr(10) || '(' ||
field_name1 || ' ' || datatype1 || ',' || chr(10) ||
field_name2 || ' ' || datatype2 || ') ';

dbms_output.put_line(v_string);

dbms_sql.parse(v_cursor, v_string, dbms_sql.native); --分析语句
v_row := dbms_sql.execute(v_cursor); --执行语句
dbms_output.put_line(v_row);
dbms_sql.close_cursor(v_cursor); --关闭游标
exception
when others then
dbms_sql.close_cursor(v_cursor); --关闭游标
raise;
end proc_dbms_sql;
/*
注意:
PL/SQL块中使用动态SQL执行DDL语句的时候与别的不同,
在DDL中使用绑定变量是非法的(bind_variable(v_cursor,’:p_name’,name)),
分析后不需要执行DBMS_SQL.Bind_Variable,直接将输入的变量加到字符串中即可。
另外,DDL是在调用DBMS_SQL.PARSE时执行的,所以DBMS_SQL.EXECUTE也可以不用,
即在上例中的v_row:=dbms_sql.execute(v_cursor)部分可以不要
*/

/***********************************
* 使用dbms_sql动态SQL,执行数据更新
***********************************/
procedure proc_dbms_sql_update(p_id number, p_name varchar2) as
v_cursor number; --定义游标
v_string varchar2(200); --字符串变量
v_row number; --行数
begin
v_cursor := dbms_sql.open_cursor; --打开游标
v_string := 'update dinya_test a set a.name = :p_name where a.id = :p_id';
dbms_sql.parse(v_cursor, v_string, dbms_sql.native); --分析语句
dbms_sql.bind_variable(v_cursor, ':p_name', p_name); --绑定变量
dbms_sql.bind_variable(v_cursor, ':p_id', p_id); --绑定变量

v_row := dbms_sql.execute(v_cursor); --执行动态SQL

dbms_output.put_line(v_row);

dbms_sql.close_cursor(v_cursor); --关闭游标
exception
when others then
dbms_sql.close_cursor(v_cursor); --关闭游标
raise;
end proc_dbms_sql_update;

/************************************
* 使用dbms_sql动态SQL,执行数据查询
************************************/
procedure proc_dbms_sql_query(p_id in varchar2) as
v_cursor number; --定义游标
v_string varchar(200); --字符串变量
v_row number; --行数
v_id number;
v_name varchar(100);

begin
v_string := 'select id,name from dinya_test where id > :p_id';
v_cursor := dbms_sql.open_cursor; --打开游标
dbms_sql.parse(v_cursor, v_string, dbms_sql.native); --解析动态sql语句
dbms_sql.bind_variable(v_cursor, ':p_id', p_id); --绑定输入参数

--定义需要输出的变量(游标Id,顺序号,变量名称,变量长度)
dbms_sql.define_column(v_cursor, 1, v_id);
dbms_sql.define_column(v_cursor, 2, v_name, 100);

v_row := dbms_sql.execute(v_cursor); --执行动态sql语句

loop
exit when dbms_sql.fetch_rows(v_cursor) > 0; --在结果集中移动游标,如果未抵达末尾,返回1

dbms_sql.column_value(v_cursor, 1, v_id); --将当前行的查询结果写入上面定义的列中
dbms_sql.column_value(v_cursor, 2, v_name);

dbms_output.put_line(v_id || '-' || v_name); --输出结果
end loop;

dbms_sql.close_cursor(v_cursor); --关闭游标

exception
when others then
dbms_sql.close_cursor(v_cursor); --关闭游标
raise;
end proc_dbms_sql_query;

end SUNJC_TEST;
/


PL/SQL动态SQL用法

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
1.登录PL/SQL Developer 这里省略Oracle数据库和PL/SQL Developer的安装步骤,注意在安装PL/SQL Dev
1.PL/SQL 记录 简介:类似于高级语言中的结构,处理单行数据 可以使用记录自定义类型、记录变量、%Ro
使用PL/SQL 1、技术目标 理解 PL/SQL 功能和特点 了解数据类型及其用法 理解逻辑比较 理解控制结构
几个设置让UltraEdit更高效的使用PL/SQL(其他语言方法也是类似的) 1.PL/SQL的高亮 "%appdata%\IDMCo
几个设置让UltraEdit更高效的使用PL/SQL(其他语言方法也是类似的) 1.PL/SQL的高亮 "%appdata%\IDMCo
几个设置让UltraEdit更高效的使用PL/SQL(其他语言方法也是类似的) 1.PL/SQL的高亮 "%appdata%\IDMCo
NO1.A pl/sql block consists of four distinct section: 1.block header :the optional block iden
NO1. The general struture of execution section is : NO2.expresstion: PL/SQL exceptions consis
官网的链接: Overview of PL/SQL http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/overvie
导出表结构: Tools-->Export User Objects(导出用户对象) -->选择要导出的表(包括Sequence等
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号