Oracle动态SQL和静态SQL比较

简介:

1.静态SQLSQL与动态SQL
Oracle编译PL/SQL程序块分为两个种:其一为前期联编(early binding),即SQL语句在程序编译期间就已经确定,大多数的编译情况属于这种类型;另外一种是后期联编(late binding),即SQL语句只有在运行阶段才能建立,例如当查询条件为用户输入时,那么Oracle的SQL引擎就无法在编译期对该程序语句进行确定,只能在用户输入一定的查询条件后才能提交给SQL引擎进行处理。通常,静态SQL采用前一种编译方式,而动态SQL采用后一种编译方式。

2动态SQL程序开发
理解了动态SQL编译的原理,也就掌握了其基本的开发思想。动态SQL既然是一种”不确定”的SQL,那其执行就有其相应的特点。Oracle中提供了Execute immediate语句来执行动态SQL,语法如下:

Excute immediate 动态SQL语句 using 绑定参数列表 returning into 输出参数列表;

对这一语句作如下说明:
1)动态SQL是指DDL和不确定的DML(即带参数的DML)
2)绑定参数列表为输入参数列表,即其类型为in类型,在运行时刻与动态SQL语句中的参数(实际上占位符,可以理解为函数里面的形式参数)进行绑定。
3)输出参数列表为动态SQL语句执行后返回的参数列表。
4)由于动态SQL是在运行时刻进行确定的,所以相对于静态而言,其更多的会损失一些系统性能来换取其灵活性。

 为了更好的说明其开发的过程,下面列举一个实例:
设数据库的他表,其数据为如下:

ID

NAME

SAL

10

scott

3000

20

tom

5000

30

jerry

4500

要求:
1.创建该表并输入相应的数据。
2.根据特定ID可以查询到其姓名和薪水的信息。
3.根据大于特定的薪水的查询相应的员工信息。
根据前面的要求,可以分别创建三个过程(均使用动态SQL)来实现:

过程一:(创建表并插入数据)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
18 : 16 : 27  SCOTT@ prod>create  or  replace  procedure p1  as
18 : 16 : 36    2   flag number; 
18 : 16 : 36    3   begin
18 : 16 : 36    4   select count(*)  into  flag  from  all_tables  where  table_name= 'T1' ;
18 : 16 : 36    5   if  (flag= 0 then 
18 : 16 : 36    6   execute immediate  'create table t1(id number,name varchar2(10),sal number)' ;
18 : 16 : 36    7   else
18 : 16 : 36    8   insert  into  t1 values ( 10 , 'scott' , 3000 );
18 : 16 : 36    9   insert  into  t1 values ( 20 , 'tom' , 5000 );
18 : 16 : 37   10   insert  into  t1 values ( 30 , 'jerry' , 4500 );
18 : 16 : 37   11   end  if ;
18 : 16 : 37   12   end p1;
18 : 16 : 38   13   /
Procedure created.
Elapsed:  00 : 00 : 00.20
18 : 16 : 40  SCOTT@ prod>exec p1;
PL/SQL procedure successfully completed.
Elapsed:  00 : 00 : 00.18
18 : 16 : 47  SCOTT@ prod>select *  from  t1;
         ID NAME              SAL
---------- ---------- ----------
         10  scott             3000
         20  tom               5000
         30  jerry             4500
Elapsed:  00 : 00 : 00.01
18 : 16 : 52  SCOTT@ prod>


过程二:(按id查询用户信息)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
18 : 40 : 24  SCOTT@ prod>create  or  replace  procedure p2 (p_id number)  as
18 : 40 : 26    2    v_name varchar2( 10 );
18 : 40 : 26    3    v_sal number;
18 : 40 : 26    4    begin
18 : 40 : 26    5    execute immediate  'select name,sal  from t1  where id=:1'  into  v_name,v_sal using p_id;
18 : 40 : 26    6    dbms_output.put_line(v_name || ' Salary is: ' ||to_char(v_sal));
18 : 40 : 26    7     exception
18 : 40 : 26    8    when others  then
18 : 40 : 26    9    dbms_output.put_line( 'No Data Found' );
18 : 40 : 26   10    end p2;
18 : 40 : 26   11    /
Procedure created.
Elapsed:  00 : 00 : 00.07
18 : 40 : 27  SCOTT@ prod>exec p2( 10 );
scott Salary is:  3000
PL/SQL procedure successfully completed.
Elapsed:  00 : 00 : 00.01
18 : 40 : 32  SCOTT@ prod>exec p2( 20 );
tom Salary is:  5000
PL/SQL procedure successfully completed.
Elapsed:  00 : 00 : 00.02
18 : 40 : 40  SCOTT@ prod>exec p2( 30 );
jerry Salary is:  4500
PL/SQL procedure successfully completed.
Elapsed:  00 : 00 : 00.02
18 : 40 : 45  SCOTT@ prod>


过程三:(查询薪水大于某个值的员工)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
18 : 48 : 59  SCOTT@ prod>create  or  replace  procedure p3(p_sal number)  as
18 : 50 : 55    2   r_t1 t1%rowtype;
18 : 50 : 55    3   type c_type is ref cursor;
18 : 50 : 56    4   c1 c_type;
18 : 50 : 56    5   begin
18 : 50 : 56    6   open c1  for  '
18 : 50 : 56    7   select *  from  t1
18 : 50 : 56    8   where  sal >: 1 '
18 : 50 : 56    9   using p_sal;
18 : 50 : 56   10   loop
18 : 50 : 56   11   fetch c1  into  r_t1;
18 : 50 : 56   12   exit when c1%notfound;
18 : 50 : 56   13   dbms_output.put_line( 'Salary higher ' ||to_char(p_sal)|| ' Name is:' );
18 : 50 : 56   14   dbms_output.put_line( 'ID is '  ||to_char(r_t1.id)|| ' Name is: ' ||r_t1.name);
18 : 50 : 56   15   end loop;
18 : 50 : 56   16   close c1;
18 : 50 : 56   17   end p3;
18 : 50 : 57   18   /
Procedure created.
Elapsed:  00 : 00 : 00.12
18 : 50 : 58  SCOTT@ prod>exec p3( 2000 );
Salary higher  2000  Name is:
ID is  10  Name is: scott
Salary higher  2000  Name is:
ID is  20  Name is: tom
Salary higher  2000  Name is:
ID is  30  Name is: jerry
PL/SQL procedure successfully completed.
Elapsed:  00 : 00 : 00.02
18 : 51 : 15  SCOTT@ prod>


注意:在过程二中的动态SQL语句使用了占位符“:1“,其实它相当于函数的形式参数,使用”:“作为前缀,然后使用using语句将p_id在运行时刻将:1给替换掉,这里p_id相当于函数里的实参。另外过程三中打开的游标为动态游标,它也属于动态SQL的范畴,其整个编译和开发的过程与execute immediate执行的过程很类似。










本文转自 客居天涯 51CTO博客,原文链接:http://blog.51cto.com/tiany/1422210,如需转载请自行联系原作者
目录
相关文章
|
23天前
|
SQL 监控 Oracle
Oracle SQL性能优化全面指南
在数据库管理领域,Oracle SQL性能优化是确保数据库高效运行和数据查询速度的关键
|
25天前
|
SQL 存储 Oracle
Oracle数据库SQL语句详解与应用指南
在数字化时代,数据库已成为各类企业和组织不可或缺的核心组件。Oracle数据库作为业界领先的数据库管理系统之一,广泛应用于各种业务场景。掌握Oracle数据库的SQL语句是数据库管理员、开发人员及运维人员的基本技能。本文将详细介绍Oracle数据库SQL语句的基本概念、语法、应用及最佳实践。一、Or
39 3
|
26天前
|
SQL Oracle 关系型数据库
Oracle SQL:了解执行计划和性能调优
Oracle SQL:了解执行计划和性能调优
43 1
|
4月前
|
SQL Oracle 关系型数据库
|
4月前
|
SQL API Python
`bandit`是一个Python静态代码分析工具,专注于查找常见的安全漏洞,如SQL注入、跨站脚本(XSS)等。
`bandit`是一个Python静态代码分析工具,专注于查找常见的安全漏洞,如SQL注入、跨站脚本(XSS)等。
|
4月前
|
SQL Oracle 关系型数据库
MySQL、SQL Server和Oracle数据库安装部署教程
数据库的安装部署教程因不同的数据库管理系统(DBMS)而异,以下将以MySQL、SQL Server和Oracle为例,分别概述其安装部署的基本步骤。请注意,由于软件版本和操作系统的不同,具体步骤可能会有所变化。
268 3
|
4月前
|
SQL Java 数据库连接
mybatis动态SQL常用语法总结
MyBatis 使用 OGNL 表达式语言处理动态SQL,如 `if` 标签进行条件判断,`choose`、`when`、`otherwise` 实现多条件选择,`where`、`set` 管理SQL关键字,`trim` 提供通用修剪功能,`foreach` 遍历集合数据。`sql` 和 `include` 用于代码重用,`selectKey` 处理插入后的返回值。参数传递支持匿名、具名、列表、Map、Java Bean和JSON方式。注意SQL转义及使用合适的jdbcType映射Java类型。
86 7
|
4月前
|
SQL 存储 Oracle
TDengine 3.3.2.0 发布:新增 UDT 及 Oracle、SQL Server 数据接入
**TDengine 3.3.2.0 发布摘要** - 开源与企业版均强化性能,提升WebSocket、stmt模式写入与查询效率,解决死锁,增强列显示。 - taos-explorer支持geometry和varbinary类型。 - 企业版引入UDT,允许自定义数据转换。 - 新增Oracle和SQL Server数据接入。 - 数据同步优化,支持压缩,提升元数据同步速度,错误信息细化,支持表名修改。 - 扩展跨平台支持,包括麒麟、Euler、Anolis OS等。
113 0
|
SQL Oracle 关系型数据库
Oracle SQL性能优化40条,值得收藏(一)
之前有发布一些Mysql的性能优化方法,有些小伙伴想了解一下Oracle的性能优化有哪些,特地去找了一些比较全和实用的文章,这篇就是其中一篇。对Oracle性能优化感兴趣的不妨收藏一些,以备不时之需。(部分内容存在错别字和技术性错误,有改动)
Oracle SQL性能优化40条,值得收藏(一)
|
SQL Oracle 关系型数据库
Oracle SQL性能优化40条,值得收藏(五)
之前有发布一些Mysql的性能优化方法,有些小伙伴想了解一下Oracle的性能优化有哪些,特地去找了一些比较全和实用的文章,这篇就是其中一篇。对Oracle性能优化感兴趣的不妨收藏一些,以备不时之需。(部分内容存在错别字和技术性错误,有改动)
Oracle SQL性能优化40条,值得收藏(五)