oracle之plsql的变量小结

《oracle之plsql的变量小结》由会员分享，可在线阅读，更多相关《oracle之plsql的变量小结（11页珍藏版）》请在金锄头文库上搜索。

1、OracleOracle 之之 PLSQLPLSQL 的变量小结的变量小结Oracle 之 PL/SQL 的变量小结PLSQL 变量有四种类型，分别是： 标量类型（Scalar） 复合类型（Composite） 参照类型（Reference） LOB 类型（Large Object） 1、标量类型： 只能存放单个数值的变量 定义时，必须要指定标量的数据类型1.1 常用标量类型（1）VARCHAR2(n)定义可变长度的字符串n 指定字符串最大长度n 最大值是 32767 字节使用时必须指定长度当在 PLSQL 块钟使用该数据类型操纵 VARCHAR2 表列时，起数值长度不应超过 4000 字节（

2、2）CHAR(n)定义固定长度字符串n 指定字符串的最大长度n 最大值是 32767 自己使用时指定长度，若没指定，则使用默认值 1当在 PLSQL 块钟使用该数据类型操纵 CHAR2 表列时，起数值长度不应超过 2000 字节（3）NUMBER(p，s)定义固定长度的整数和浮点数p 表示精度，用于指定数字的总位数s 表示标度，用于指定小数点后的数字位数（4）DATE定义日期和数据数据（5）BOOLEAN定义布尔变量值为：TRUE、FALSE、NULL为 PLSQL 独有数据类型，表列不能采用该数据类型NULL 表示 missing、inapplicable 或者 unknown（6）LONG

3、 和 LONG ROWlong：数据类型用于定义变长字符串，类似于 VARCHAR2 数据类型，但其字符串的最大长度为 32760 字节long row：数据类型用于定义变长的二进制数据，其数据最大长度为 32760 字节（7）BINARY_INTEGER定义整数数值范围-2147483647 和 2174483647 之间（8）BINARY_FLOAT 和 BINARY_DOUBLEBINARY_FLOAT 定义单精度浮点数（oracle 10g 新增加）BINARY_DOUBLE 定义双精度浮点数（oracle 10g 新增加）（9）TIMESTAMP定义时间和日期数据（oracle 9i

4、 新增加）对其赋值方法与对 date 变量赋值方法完全相同当显示 TIMESTAMP 变量数据时，不仅会显示日期，而且还会显示时间和上下午标记1.2、定义标量类型语法：identifier CONSTANT datatype NOT NULL :=|DEFAULT expridentifier：指定变量或常量名称CONSTANT：指定是常量datatype：用于指定变量或常量的类型NOT NULL：非空，有它是必须初始化:= ：赋值符合DEFAULT：默认值expr：初始值1.3、标量定义规则每行声明一个变量变量的赋值使用：identifier := value;变量名称最多 30 个字符1.

5、4 使用%TYPE 属性安装数据库列或其他变量来确定新变量的类型和长度（具有动态性和绑定性）语法：identifier Table.column_name%TYPE;或 identifier other_variable_name%TYPE;2、复合变量指用于存放多个值的变量当定义复合变量时，必须要使用 PL/SQL 的复合数据类型PL/SQL 的复合数据类型包括：PL/SQL 记录PL/SQL 表（索引表）PL/SQL 嵌套表VARRAY关于这些复合数据类型，由相关章节给出3、参照变量指用于存放数值指针的变量可以通过参照变量来共享相同对象，从而降低占用空间两种参照变量：游标变量（REF CU

6、RSOR）对象类型变量（REF obj_type）关于这些参照变量，由相关章节给出4、LOB 变量用于存储大批量数据的变量分为两种：内部 LOB：包括 CLOB、BLOB、NCLOB，它们的数据被存储在数据库中，并且支持事务操作外部 LOB：BFILE，该类型数据被存储在 OS 文件中，并且不支持事务操作CLOB：存储大批量字符数据NCLOB：存储大批量字符数据，unicode 编码BLOB：存储大批量二进制数据BFILE：存储指向 OS 文件的指针5、非 PL/SQL 变量 5.1、替换变量（仅用于 SQL *Plus 或者用于原理和 SQL *Plus 相同的开发工具）：临时存储值利用它可

7、以达到创建通用脚本的目的利用它可以达到和用户交互，故在 SQL *Plus 中又称交互式命令替换变量的格式式在变量名称前加一个生命周期：单次引用中，不需要声明，如果替换字符或日期类型，最好用单引号扩起使用范围：where、order by、列表达式、表名、整个 SELECT 语句中（2）生命周期：整个会话（session 连接）,不需要声明（3）define ：“define 变量名=变量值”eg：DEFINE a = clark;生命周期：整个会话，预先声明，使用时用关闭调试命令（关掉替换过程）set verify(环境变量) on;打开调试命令(可以看到替换过程)5.2、SQL *PLUS

8、 环境变量（仅用于 SQL *Plus 或者用于原理和 SQL *Plus 相同的开发工具）：ECHO 显示回显HEADING OFF/ON是否显示列标题ARRAYSIZE20/n每一次从查询得到的返回量的大小FEEDBACKOFF/ON回馈，反馈信息LONG80/non/text LONG 类型LINESIZE 行的宽度SET LINESIZE n(最好是在 200 之内)PAGESIZE ：设置页的大小。SET PAGESIZE Nwrapoff/on 折行SET 修改SHOW 显示SQL *PLUS 中格式化显示的命令：COLUMNcolumn option可以设置字段或字段别名的格式C

9、OLUMN last_name HEADING employee|name |代表换行col 字段名 查看命令CLEAR：清除列的格式HEADING TEXT：设置列标题FORMAT FORMAT：格式化显示列的值，对字符和数字有效，对日期无效，eg：column salary justify left format 999,999.00，其中 justify left:左对齐；col manager_id format 999999999限制字符串的长度有 A+数字限制限制数字的长度有 9，有几为 9 就限制成几位。NOPRINT/PRINT NOPRINT：把一个字段从输出上屏蔽掉（返回但

10、不显示） 。col 字段名 noprint/print. NULL 如果有 NULL 值，显示什么。col name null on employee TTITLEtext/off/on设置报表的表头BTITLEtext/off/on设置报表的表尾做报表的时候要先想好 PAGESIZE 的大小。BREAK ON REPORT_ELEMENT压制重复值的显示，只能跟一个字段名才有效，eg：select department_id,last_namefrom employeeswhere rownum var vMonth varchar2(10);SQL begin2 :vMonth := 20

11、0901;3 end;4 /PL/SQL 过程已成功完成。SQL select :vMonth from dual;-oracle 绑定变量（bind variable）oracle 中，对于一个提交的 sql 语句,存在两种可选的解析过程, 一种叫做硬解析,一种叫做软解析.一个硬解析需要经解析,制定执行路径,优化访问计划等许多的步骤.硬解释不仅仅耗费大量的 cpu，更重要的是会占据重要的们闩（latch）资源，严重的影响系统的规模的扩大（即限制了系统的并发行） ， 而且引起的问题不能通过增加内存条和 cpu 的数量来解决。之所以这样是因为门闩是为了顺序访问以及修改一些内存区域而设置的，这些内

12、存区域是不能被同时修改。当一个 sql 语句提交后，oracle 会首先检查一下共享缓冲池（shared pool）里有没有与之完全相同的语句，如果有的话只须执行软分析即可，否则就得进行硬分析。而唯一使得 oracle 能够重复利用执行计划的方法就是采用绑定变量。绑定变量的实质就是用于替代 sql 语句中的常量的替代变量。绑定变量能够使得每次提交的 sql 语句都完全一样。含绑定变量的 sql 语句：SQL SELECT fname, lname, pcode FROM cust WHERE id = :cust_no;Sql*plus 中使用绑定变量：sql variable x numbe

13、r;sql exec :x := 123;sql SELECT fname, lname, pcode FROM cust WHERE id =:x;pl/sqlpl/sql 很多时候都会自动绑定变量而无需编程人员操心，即很多你写得 sql 语句都会自动利用绑定变量，如下例所示：create or replace procedure dsal(p_empno in number)asbeginupdate empset sal=sal*2where empno = p_empno;commit;end;/也许此时你会想要利用绑定变量来替代 p_empno,但是这是完全没有必要的，因为在 pl/sql 中，引用变量即是引用绑定变量。但是在 pl/sql 中动态 sql 并不是这样。在 vb，java 以及其他应用程序中都得显式地利用绑定变量。对于绑定变量的支持不仅仅限于 oracle,其他 RDBMS 向SQL也支持这一特性。但是并不是任何情况下都需要使用绑定变量， 下面是两种例外情况：对于隔相当一段时间才执行一次的语句，这是利用绑定变量的好处会被不能有效利用优化器而抵消数据仓库的情况下。