📄 第一章 pl-sql一览 - pl-sql用户指南与参考 - whatiswhat.htm
字号:
<P>我们还可以用TABLE、VARRAY和RECORD这些复合类型来声明嵌套表、变长数组(缩写为varray)和记录。
</P>
<UL>
<LI>变量赋值 </LI></UL>
<P>我们可以用三种方式为变量赋值，第一种，直接使用赋值操作符":="： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap>tax := price * tax_rate;<BR>valid_id := <STRONG>FALSE</STRONG>;<BR>bonus := current_salary * 0.10;<BR>wages := gross_pay(emp_id,<BR> st_hrs,<BR> ot_hrs<BR> ) - deductions;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>第二种，利用数据库中查询的结果为变量赋值： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>SELECT</STRONG> sal * 0.10<BR> <STRONG>INTO</STRONG> bonus<BR> <STRONG>FROM</STRONG> emp<BR> <STRONG>WHERE</STRONG> empno = emp_id;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>第三种，把变量作为一个OUT或IN OUT模式的参数传递给子程序，然后由子程序为其赋值。如下例所示，IN
OUT参数可以为被调用的子程序传递初始值然后子程序将更新后的新值返回给调用程序： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> my_sal <STRONG>REAL</STRONG>(7,2);<BR> <STRONG>PROCEDURE</STRONG> adjust_salary (emp_id INT, salary <STRONG>IN</STRONG> <STRONG>OUT</STRONG> <STRONG>REAL</STRONG>) <STRONG>IS</STRONG> ...<BR><STRONG>BEGIN</STRONG><BR> <STRONG>SELECT</STRONG> <STRONG>AVG</STRONG>(sal)<BR> <STRONG>INTO</STRONG> my_sal<BR> <STRONG>FROM</STRONG> emp;<BR> adjust_salary(7788, my_sal); <EM>-- assigns a new value to my_sal</EM>
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<UL>
<LI>声明常量 </LI></UL>
<P>声明常量跟声明变量类似，但是要加一个CONSTANT关键字，并在声明时为其赋上初始值。下例中，我们声明一个名为credit_limit的常量：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap>credit_limit <STRONG>CONSTANT</STRONG> <STRONG>REAL</STRONG> := 5000.00;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P class=title2>3、游标</P>
<P>Oracle使用工作区(work
area)来执行SQL语句，并保存处理信息。PL/SQL可以让我们使用游标来为工作区命名，并访问存储的信息。游标的类型有两种：隐式和显式。
PL/SQL会为所有的SQL数据操作声明一个隐式的游标，包括只返回一条记录的查询操作。对于返回多条记录的查询，我们可以显式地声明一个游标来处理每一条记录。如下例：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> <STRONG>CURSOR</STRONG> c1 <STRONG>IS</STRONG><BR> <STRONG>SELECT</STRONG> empno, ename, job<BR> <STRONG>FROM</STRONG> emp<BR> <STRONG>WHERE</STRONG> deptno = 20;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>由多行查询返回的行集合称为结果集(result
set)。它的大小就是满足我们查询条件的行的个数。如下图所示，显式游标"指向"当前行的记录，这可以让我们的程序每次处理一条记录。
</P><IMG alt=""
src="第一章 PL-SQL一览 - PL-SQL用户指南与参考 - whatiswhat.files/o_1-2.gif"
name="">
<P>多行查询处理有些像文件处理。例如，一个COBOL程序打开一个文件，处理记录，然后关闭文件。同样，一个PL/SQL程序打开一个游标，处理查询出来的行，然后关闭游标。就像文件指针能标记打开文件中的当前位置一样，游标能标记出结构集的当前位置。
</P>
<P>我们可以使用OPEN，FETCH和CLOSE语句来控制游标，OPEN用于打开游标并使游标指向结果集的第一行，FETCH会检索当前行的信息并把游标指移向下一行，当最后一行也被处理完后，CLOSE就会关闭游标。
</P>
<P class=title2>4、游标FOR循环</P>
<P>在大多需要使用显式游标的情况下，我们都可以用一个简单的游标FOR循环来代替OPEN，FETCH和CLOSE语句。首先，游标FOR循环会隐式地声明一个代表当前行的循环索引(loop
index)变量。下一步，它会打开游标，反复从结果集中取得数据并放到循环索引的各个域(field)中。当所有行都被处理过以后，它就会关闭游标。下面的例子中，游标FOR循环隐式地声明了一个emp_rec记录：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> <STRONG>CURSOR</STRONG> c1 <STRONG>IS</STRONG><BR> <STRONG>SELECT</STRONG> ename, sal, hiredate, deptno<BR> <STRONG>FROM</STRONG> emp;<BR> ...<BR><STRONG>BEGIN</STRONG><BR> <STRONG>FOR</STRONG> emp_rec <STRONG>IN</STRONG> c1 <STRONG>LOOP</STRONG><BR> ...<BR> salary_total := salary_total + emp_rec.sal;<BR> <STRONG>END</STRONG> <STRONG>LOOP</STRONG>;<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>为了使用每一个查询到的行中的每一个字段，我们可以使用点标志(dot
notation)，它的作用就像一个域选择器。 </P>
<P class=title2>5、游标变量</P>
<P>游标变量的使用方式和游标类似，但更加灵活，因为它不绑定于某个特定的查询，所以可用于打开任何返回类型相兼容的查询语句。游标变量是真正的
PL/SQL变量，我们可以为它赋值，把它传递给子程序。如下例，我们把游标变量作为存储过程open_cv的一个参数传进去，程序执行时，可以根据
choice值的不同，灵活地打开不同的查询内容。 </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>PROCEDURE</STRONG> open_cv(generic_cv <STRONG>IN</STRONG> <STRONG>OUT</STRONG> genericcurtyp, choice <STRONG>NUMBER</STRONG>) <STRONG>IS</STRONG><BR><STRONG>BEGIN</STRONG><BR> <STRONG>IF</STRONG> choice = 1 <STRONG>THEN</STRONG><BR> <STRONG>OPEN</STRONG> generic_cv <STRONG>FOR</STRONG><BR> <STRONG>SELECT</STRONG> *<BR> <STRONG>FROM</STRONG> emp;<BR> <STRONG>ELSIF</STRONG> choice = 2 <STRONG>THEN</STRONG><BR> <STRONG>OPEN</STRONG> generic_cv <STRONG>FOR</STRONG><BR> <STRONG>SELECT</STRONG> *<BR> <STRONG>FROM</STRONG> dept;<BR> <STRONG>ELSIF</STRONG> choice = 3 <STRONG>THEN</STRONG><BR> <STRONG>OPEN</STRONG> generic_cv <STRONG>FOR</STRONG><BR> <STRONG>SELECT</STRONG> *<BR> <STRONG>FROM</STRONG> salgrade;<BR> <STRONG>END</STRONG> <STRONG>IF</STRONG>;<BR> ...<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P class=title2>6、属性</P>
<P>PL/SQL的变量和游标都有着让我们能够直接引用它们各自的数据类型或结构的属性。数据库字段和表也有类似的属性。"%"是一个属性的指示符。
</P>
<UL>
<LI>%TYPE </LI></UL>
<P>%TYPE可以提供一个变量或数据库字段的数据类型，这在声明存放数据库值的变量时是非常有用的。假设我们要声明一个存放表books中的字段my_title的字段的变量时，就可以这样使用%TYPE属性：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap>my_title books.title%<STRONG>TYPE</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>这样声明my_title有两个优点，第一，我们不必知道title具体类型；第二，如果我们改变了数据库中对该字段的数据类型定义的话，my_title的数据类型会在运行时做出相应的改变。
</P>
<UL>
<LI>%ROWTYPE </LI></UL>
<P>在PL/SQL中，记录用于将逻辑相关数据组织起来。一个记录是由许多相关域的组合。%ROWTYPE属性返回一个记录类型，其数据类型和数据表的数据结构相一致。这样的记录类型可以完全保存从数据表中查询(SELECT)或从游标/游标变量取出(FETCH)的行记录。
</P>
<P>行中的字段和记录中的域对应的名称和数据类型都完全一致。下面的例子中，我们声明一个dept_rec的记录。它的域名称和数据类型与表dept中的字段名称和数据类型就完全一样。
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> dept_rec dept%<STRONG>ROWTYPE</STRONG>; <EM>-- declare record variable</EM>
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>我们可以使用"."来引用记录中的域。 </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap>my_deptno := dept_rec.deptno;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>假设我们声明了一个用于检索雇员的名字、工资、雇用日期和职称的游标，我们就可以使用%ROWTYPE来声明一个类型相同的记录，如下例：
</P>
<BLOCKQUOTE>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -