本地动态SQL
来源:互联网 发布:115会员淘宝在哪买 编辑:程序博客网 时间:2024/06/04 01:08
一、本地动态SQL
本地动态SQL是使用EXECUTE IMMEDIATE语句来实现的。
1、本地动态SQL执行DDL语句:
需求:根据用户输入的表名及字段名等参数动态建表。
create or replace 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
str_sql:=’create table ’||table_name||’(’||field1||’ ’||datatype1||’,’||field2||’ ’||datatype2||’)’;
execute immediate str_sql; --动态执行DDL语句
exception
when others then
null;
end ;
以上是编译通过的存储过程代码。下面执行存储过程动态建表。
SQL> execute proc_test(’dinya_test’,’id’,’number(8) not null’,’name’,’varchar2(100)’);
PL/SQL procedure successfully completed
SQL> desc dinya_test;
Name Type Nullable Default Comments
---- ------------- -------- ------- --------
ID NUMBER(8)
NAME VARCHAR2(100) Y
SQL>
到这里,就实现了我们的需求,使用本地动态SQL根据用户输入的表名及字段名、字段类型等参数来实现动态执行DDL语句。
2、本地动态SQL执行DML语句。
需求:将用户输入的值插入到上例中建好的dinya_test表中。
create or replace procedure proc_insert
(
id in number, --输入序号
name in varchar2 --输入姓名
) as
str_sql varchar2(500);
begin
str_sql:=’insert into dinya_test values(:1,:2)’;
execute immediate str_sql using id,name; --动态执行插入操作
exception
when others then
null;
end ;
执行存储过程,插入数据到测试表中。
SQL> execute proc_insert(1,’dinya’);
PL/SQL procedure successfully completed
SQL> select * from dinya_test;
ID NAME
1 dinya
在上例中,本地动态SQL执行DML语句时使用了using子句,按顺序将输入的值绑定到变量,如果需要输出参数,可以在执行动态SQL的时候,使用RETURNING INTO 子句,如:
declare
p_id number:=1;
v_count number;
begin
v_string:=’select count(*) from table_name a where a.id=:id’;
execute immediate v_string into v_count using p_id;
end ;
更多的关于动态SQL中关于返回值及为输出输入绑定变量执行参数模式的问题,请读者自行做测试。
二、使用DBMS_SQL包
使用DBMS_SQL包实现动态SQL的步骤如下:A、先将要执行的SQL语句或一个语句块放到一个字符串变量中。B、使用DBMS_SQL包的parse过程来分析该字符串。C、使用DBMS_SQL包的bind_variable过程来绑定变量。D、使用DBMS_SQL包的execute函数来执行语句。
1、使用DBMS_SQL包执行DDL语句
需求:使用DBMS_SQL包根据用户输入的表名、字段名及字段类型建表。
create or replace 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||’(’||field_name1||’ ’||datatype1||’,’||field_name2||’ ’||datatype2||’)’;
dbms_sql.parse(v_cursor,v_string,dbms_sql.native); --分析语句
v_row:=dbms_sql.execute(v_cursor); --执行语句
dbms_sql.close_cursor(v_cursor); --关闭光标
exception
when others then
dbms_sql.close_cursor(v_cursor); --关闭光标
raise;
end;
以上过程编译通过后,执行过程创建表结构:
SQL> execute proc_dbms_sql(’dinya_test2’,’id’,’number(8) not null’,’name’,’varchar2(100)’);
PL/SQL procedure successfully completed
SQL> desc dinya_test2;
Name Type Nullable Default Comments
---- ------------- -------- ------- --------
ID NUMBER(8)
NAME VARCHAR2(100) Y
SQL>
declare
执行sql,并将结果放入变量中
v_str varchar2(1000) := 'select empno ,ename,deptno from emp where empno=1';
v_empno emp.empno%type;
v_ename emp.ename%type;
v_deptno emp.deptno%type;
begin
execute immediate v_str
into v_empno, v_ename, v_deptno;
dbms_output.put_line(v_ename);
end;
2、使用DBMS_SQL包执行DML语句
需求:使用DBMS_SQL包根据用户输入的值更新表中相对应的记录。
查看表中已有记录:
SQL> select * from dinya_test2;
ID NAME
1 Oracle
2 CSDN
3 ERP
SQL>
建存储过程,并编译通过:
create or replace procedure proc_dbms_sql_update
(
id number,
name varchar2
)as
v_cursor number; --定义光标
v_string varchar2(200); --字符串变量
v_row number; --行数
begin
v_cursor:=dbms_sql.open_cursor; --为处理打开光标
v_string:=’update dinya_test2 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’,name); --绑定变量
dbms_sql.bind_variable(v_cursor,’:p_id’,id); --绑定变量
v_row:=dbms_sql.execute(v_cursor); --执行动态SQL
dbms_sql.close_cursor(v_cursor); --关闭光标
exception
when others then
dbms_sql.close_cursor(v_cursor); --关闭光标
raise;
end;
执行过程,根据用户输入的参数更新表中的数据:
SQL> execute proc_dbms_sql_update(2,’csdn_dinya’);
PL/SQL procedure successfully completed
SQL> select * from dinya_test2;
ID NAME
1 Oracle
2 csdn_dinya
3 ERP
SQL>
执行过程后将第二条的name字段的数据更新为新值csdn_dinya。这样就完成了使用dbms_sql包来执行DML语句的功能。
使用DBMS_SQL中,如果要执行的动态语句不是查询语句,使用DBMS_SQL.Execute或DBMS_SQL.Variable_Value来执行,如果要执行动态语句是查询语句,则要使用DBMS_SQL.define_column定义输出变量,然后使用DBMS_SQL.Execute, DBMS_SQL.Fetch_Rows, DBMS_SQL.Column_Value及DBMS_SQL.Variable_Value来执行查询并得到结果。
总结说明:
在Oracle开发过程中,我们可以使用动态SQL来执行DDL语句、DML语句、事务控制语句及系统控制语句。但是需要注意的是,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)部分可以不要。
记得在学校自己写些程序的时候用到数据库,只要能完成功能根本从不去考虑什么性能问题,经过两个大型项目的开发才慢慢体会到数据库性能的优化的重要性很小的一个细节都可能大大影响系统的运行速度.下面是些常用的 sql语句优化知识
- 本地动态SQL
- 本地动态sql
- 第十一章 本地动态SQL
- 第十一章 本地动态SQL
- PLSQL中执行本地动态SQL
- 本地动态SQL执行DML语句
- PLSQL==》本地动态SQL
- PL/SQL编程学习之本地动态SQL
- JAVA_WEB Oracle 10g学习: 本地动态SQL
- 个人笔记基础必看游标、本地动态SQL、程序包实现动态SQL
- sql 动态添加服务器(在本地对服务器上的数据库操作)
- 本地动态SQL(Open for等)如何获取SQL的定义属性,包括栏位名称和数据类型
- 动态SQL
- 动态SQL
- 动态sql
- 动态SQL
- 动态SQL
- 动态sql
- Linux USB驱动框架分析(一)
- 转Protocol Buffer介绍
- SGU 106 The equation
- 关于双屏显示的一些问题解释
- 自己的mysql进行优化经历
- 本地动态SQL
- NYOJ-21 三个水杯【隐式图搜索】
- Code Sing Error: Provisioninge Profile can't be found. iOS开发
- 备忘,地图
- Google: Excellent Papers for 2011
- GB2312简体中文编码表
- 在C# WinForm中添加窗体大小变化和退出前确认功能,就要自行添加Resize、FormClosing事件
- win7笔记本,作为无线路由(实现手机连接笔记本)
- java也能写出点点算法-像C++一样去优化核心并发的代码例子1