当前位置：文档之家 › PLSQL批量操作与动态SQL

PLSQL批量操作与动态SQL

  • 格式：doc
  • 大小：64.50 KB
  • 文档页数：5

1、批量绑定：批量绑定是指单条SQL操作能传递所有PLSQL表的数据。

通过批量绑定，可以极大地加快数据处理速度，提高应用程序性能。

批量绑定语句forall主要针对insert、update、delete等语句，forall绑定的表必须是PLSQL表，即单列多行的数据结构(类似于c语言中的数组)，如：索引表、嵌套表、变长数组，不能是记录表。

2、批量绑定语句forall的语法：(一次次的提取数据)语法一：(i作为PLSQL表的下标被引用)forall i in lower..upper sql_statement;解释：对PLSQL表中下标为i(取值范围为lower和upper之间，包括边界，i可以是其他满足命名规则的名字)的数据进行批量绑定。

例子：DECLARETYPE int_table_type IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;--声明索引表类型，其中的数--据类型为integer，索引表类型是PLSQL表类型的一种TYPE varchar2_table_type IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER; --声明索引表类型，--其中的数据类型为varchar2(20)name_table varchar2_table_type;--用声明的索引表类型声明变量，即声明一个PLSQL表id_table int_table_type; --用声明的索引表类型声明变量，即声明另一个PLSQL表BEGINFOR i IN1..100000 LOOPid_table(i) := i;--为PLSQL表赋值name_table(i) := 'aaa' || i;--为另一个PLSQL表赋值END LOOP;FORALL i IN1..100000 INSERT INTO intest VALUES(id_table(i),name_table(i));-- 将PLSQL表--id_table和name_table下标为i的数据插入到intest表中，这里i从1到100000END;/语法二：(用于下标不连续的情况)forall i in indices of collection [between lower and upper] sql_statement;解释：对PLSQL表中下标为i（i的取值范围为indices取得的collection(PLSQL表)的所有下标值，同时i在范围为lower到upper之间，包括边界）的数据进行批量绑定。

例子：DECLARETYPE int_table_type IS TABLE OF binary_integer INDEX BY BINARY_INTEGER;id_table int_table_type;BEGINFOR i IN1..10 LOOPid_table(i) := i;END LOOP;FORALL i IN INDICES OF id_table BETWEEN1AND5DELETE FROM intest WHERE iid = id_table(i); --对PLSQL表id_table下标为i(i取PLSQL表id_table的下标值，同时满足大于等于1小于等于5)的数据进行--批量绑定同时进行相关操作，这里表intest的iid等于id_table(1..5)的数据会被删END;/语法三：(用于处理重复数据)forall i in values of index_collection sql_statement;解释：对PLSQL表中下标为i(i的取值范围为index_collection表中的值)的数据进行批量绑定。

例子：DECLARETYPE int_table_type IS TABLE OF binary_integer INDEX BY BINARY_INTEGER;TYPE varchar2_table_type IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;name_table varchar2_table_type;id_table int_table_type;BEGINid_table(1):=5;id_table(5):=1;name_table(5):='aa5';name_table(1):='aa4';name_table(3):='aa4';FORALL i IN VALUES OF id_table insert into intest values(id_table(i),name_table(i));--对PLSQL表id_table和name_table下标为i(i取PLSQL表id_table中的值)的数据进行批量绑定，同时进行--操作。

这里会将id_table(1) 、id_table(5) 、name_table(1) 、name_table(5)插入到intest表中END;/3、批量提取(一次提取大批数据)：bulk collect用于取得批量数据，适用于select into、fetch into和DML语句(select、update、insert、delete等)。

语法：bulk collect into collection1[,collection2…]select into中使用批量提取的例子：DECLARETYPE name_table_type IS TABLE OF emp.ename%type INDEX BY BINARY_INTEGER;name_table name_table_type;BEGINSELECT ename BULK COLLECT INTO name_table FROM emp;FOR i _table.count LOOPdbms_output.put_line(name_table(i));END LOOP;END;/DML语句中使用批量提取的例子：DECLARETYPE varchar2_table_type IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;name_table varchar2_table_type;BEGINDELETE FROM intest WHERE iid < 100RETURNING iname BULK COLLECT INTO name_table;--将intest表中iid小于100的记录删除，同时将删除的iname批量提取到name_table中FOR i _table.count LOOPdbms_output.put_line(name_table(i));END LOOP;END;/fetch into中使用批量提取的例子：DECLARETYPE varchar2_table_type IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;CURSOR int_cursor IS SELECT iname FROM intest;name_table varchar2_table_type;BEGINOPEN int_cursor;FETCH int_cursor BULK COLLECT INTO name_table;CLOSE int_cursor;FOR i _table.count LOOPdbms_output.put_line(name_table(i));END LOOP;END;/4、动态SQL：创建过程的时候就已经编译好的sql叫静态sql，这种sql只能用于处理特定的操作，若要根据情况做出不同的操作，则必须使用动态SQL。

动态sql是在运行时建立的sql语句，若要根据不同情况生成相应的sql语句，就必须使用动态sql了。

使用动态sql的例子：CREATE OR REPLACE PROCEDURE grant_sys_priv(v_pri_name VARCHAR2,v_username VARCHAR2)ISsql_str VARCHAR2(200);BEGINsql_str := 'grant '|| v_pri_name ||' to '|| v_username;EXECUTE IMMEDIATE sql_str;END;/使用如上过程为scott用户授予create any view权限：exec grant_sys_priv('create any view','scott');参数被单引号引起来表示字符串类型。

5、绑定变量：使用绑定变量可以避免硬解析，提高性能，但绑定变量不能替换实际的数据库对象(表、视图、列等)。

绑定变量用:开头，使用using关键字或RETURNING INTO为绑定的变量赋值。

如果变量作为参数时，还是必须得用字符串连接，如：create or replace procedure drop_table(v_tablename in varchar2)isbeginexecute immediate'drop table '|| v_tablename;--不能写成execute immediate'drop table :tablename'using v_tablename;写成这样执行时会出错，--即执行exec drop_table('t');时出错end;/不使用绑定变量的例子：BEGINFOR i IN10..50 LOOPEXECUTE IMMEDIATE'select ename from scott.emp where deptno = '|| i;--这条语句不能看到查询结果END LOOP;END;/使用单个绑定变量的例子：BEGINFOR i IN10..50 LOOPEXECUTE IMMEDIATE'select ename from scott.emp where deptno = :deptno 'USING i;END LOOP;END;/使用多个绑定变量的例子：BEGINEXECUTE IMMEDIATE'update scott.emp set sal = (sal+:addsal) where deptno = :dno'USING&sal,&dno;END;/解释：:addsal和:dno是绑定变量，它们分别用&sal和&dno为其赋值，&代表在输入框中输值。

合集下载

plsql建表 基本语句

plsql建表 基本语句

plsql建表基本语句在PL/SQL中，创建表的基本语句是使用CREATE TABLE命令。

以下是创建表的基本语法：sqlCREATE TABLEtable_name (column1 datatype[constraint],column2 datatype[constraint],column3 datatype[constraint],...);其中，table_name是表的名称，column1, column2, column3等是表中的列名，datatype是列的数据类型，constraint是可选的约束条件。

以下是一个示例，展示如何在PL/SQL中创建一个简单的表：sqlCREATE TABLE employees (employee_id NUMBER PRIMARYKEY,first_name VARCHAR2(50),last_name VARCHAR2(50),hire_date DATE,salary NUMBER(8,2) CHECK(salary > 0));在上面的示例中，我们创建了一个名为employees的表，包含了五个列：employee_id、first_name、last_name、hire_date和salary。

每个列都有相应的数据类型，并且为employee_id列设置了主键约束，为salary列设置了检查约束，确保工资大于0。

请注意，PL/SQL通常用于Oracle数据库的存储过程和函数，而创建表的语句实际上是在SQL部分执行的。

在Oracle SQL Developer 等工具中，可以直接执行上述SQL语句来创建表。

如果你需要在PL/SQL块中执行DDL语句（如CREATE TABLE），你可以使用动态SQL （例如EXECUTE IMMEDIATE语句）来实现。

第五章上机课使用PLSQL

第五章上机课使用PLSQL

使用PLSQL目标：理解逻辑比较的定义能使用各种控制结构能编写错误处理程序第一部分：指导本阶段重点介绍编写程序以进行逻辑比较，使用各种控制结构以及编写错误处理代码的方法。

阶段一：使用条件控制需求说明：由于工程取得巨大成功，GITM公司决定向职员发放奖金。

奖金根据职员所在部门的代码来计算。

如果部门代码为“DP01”，奖金为2000元；部门代码为“DP02”，奖金为1700元；部门代码为“DP03”，奖金为1500元，请设计解决方案。

实现思路：条件控制根据指定的条件执行一连串语句。

单个条件可以使用IF-THEN语句来指定。

条件控制允许使用IF-THEN-ELSIF语句给出多个条件语句。

该公司想要根据职员所在的部门代码来更新职员的工资记录。

要计算工资，首先需要检查部门代码。

要检查部门代码，可以使用IF-THEN-ELSIF语句。

此程序声明V ARCHAR2类型的DPTCODE和EMP-CODE以及NUMBER类型的SALARY。

使用IF-THEN-ELSIF条件语句来验证职员所在的部门。

SELECT语句从SALARY-RECORDS表中检索DEPTCODE、EMPCODE和EMPSAL，并将值存储在变量DPTCODE、EMP-CODE和SALARY中。

IF语句比较DPTCODE中的值，然后更新SALARY-RECORDS表中的EMPSAL。

参考步骤：（1）启动“SQL Plus”。

（2）在SQL提示中输入“SET SERVEROUTPUT ON”。

（3）在SQL Plus中输入下列代码。

此代码提示用户输入部门代码。

代码将显示消息“PL/SQL过程已成功完成”。

使用SELECT语句可以检索更新以后的行。

（4）在SQL Plus中输入SELECT*FROM SALARY-RECORDS。

更新以后的表如图5.1所示。

阶段二：使用循环控制需求说明：请编写一个程序，用以接受用户输入的学生编号和课程。

在course-details表中详细信息。

Oracle_PLSQL教程-第十章

Oracle_PLSQL教程-第十章

翻译：陈拓 chentuo@2006 年 6 月16 日更多触发器概念Copyright © Oracle Corporation, 2001. All rights reserved.进度表： 时间 主题80 分钟 讲演40 分钟 练习120 分钟 总共目标完成本课程后, 您应当能够：• 创建附加的数据库触发器• 解释触发器管理规则• 实现触发器10­2 Copyright © Oracle Corporation, 2001. All rights reserved.本课目标在本课中，你将学习怎样创建更多地数据库触发器并学习规则触发器。

你还将学习许多应用程序触发器。

创建数据库触发器• 触发用户事件：– CREATE, ALTER 或 DROP– Logging on 或 off• 触发数据库或系统事件：– Shutting down 或 starting up 数据库– 一个指定的错误 (或任何错误) 出现10­3 Copyright © Oracle Corporation, 2001. All rights reserved.创建数据库触发器在编码触发器体之前，确定触发器的组建。

在系统事件上的触发器可以被定义在数据库或方案级别。

例如，一个数据库 shutdown 触发器 被定义在数据库级别。

在数据定义语言 (DDL) 语句上的触发器，或一个用户登录登出，即可以被 定义为数据库级别也可以被定义为方案级别。

在 DML 语句上的触发器被定义在指定的表或一个视图上。

一个定义在数据库级别的触发器对所有用户触发， 一个定义在方案或表级别的触发器只有当触 发级别包括该方案或表时才触发。

触发事件可能引起触发器触发：· 在数据库或方案中的一个对象上一个数据定义语句· 一个指定的用户 (或任何用户) 登录或登出· 一个数据库关闭或启动· 一个指定的或任何错误出现在 DDL 语句上创建触发器• 语法：CREATE [OR REPLACE] TRIGGER trigger_nametiming[ddl_event1 [OR ddl_event2 OR ...]]ON {DATABASE|SCHEMA}trigger_body10­4 Copyright © Oracle Corporation, 2001. All rights reserved.创建触发器语法DDL 事件 可能的值CREATE无论何时一个 CREATE 语句添加一个新的数据库对象到数据字典中时，引起 Oracle 服务器激发触发器ALTER无论何时一个 ALTER 语句修改一个在数据字典中的数据库对象时，引起 Oracle 服务器激发触发器DROP无论何时一个 DROP 语句删除一个在数据字典中的数据库对象时，引起Oracle 服务器激发触发器触发器体代表一个完整的 PL/SQL 块。

plsql_distinct用法_概述及解释说明

plsql_distinct用法_概述及解释说明

plsql distinct用法概述及解释说明1. 引言1.1 概述在数据库操作中，我们经常会需要对查询结果进行去重处理。

PL/SQL作为一种关系型数据库语言，提供了Distinct关键字来实现去重功能。

Distinct的用法和效果在数据库查询中起着重要的作用。

1.2 文章结构本文将从概述、语法和用法、应用示例等方面逐步介绍PL/SQL Distinct的相关知识和技术。

同时，还将解释说明Distinct在PL/SQL中的作用和效果，并提供一些实际案例分析以及常见使用场景和经验分享。

1.3 目的本文的目标是帮助读者全面了解PL/SQL Distinct的用法，包括基本概念、语法和用法，以及其在查询中的应用示例。

此外，我们也将解释说明Distinct在PL/SQL中的作用和效果，并提供一些性能优化注意事项和技巧。

最后，给出结论总结并展望未来可能发展方向和潜在挑战。

通过阅读本文，读者将能够掌握PL/SQL Distinct的基本概念、语法和用法，并能够灵活运用它来解决数据库查询中的数据重复问题。

同时，在设计PL/SQL程序时，可以更加深入地理解Distinct在性能优化方面的应用技巧，以提高查询效率。

2. PL/SQL Distinct用法:2.1 Distinct的基本概念:Distinct是PL/SQL中用于查询数据表中唯一值的关键字。

它可以从结果集中去除重复行，返回仅包含唯一值的结果集。

2.2 Distinct的语法和用法:在使用Distinct时，需要在SELECT语句中指定Distict关键字，并将需要去重的列名作为参数传递给它。

语法如下所示：```SELECT DISTINCT column1, column2, ...FROM table_nameWHERE condition;```其中，column1, column2等表示需要去重的列名，table_name表示要查询的表名，condition表示可选的查询条件。