📄 oracle pl-sql学习笔记.htm
字号:
<TD bgColor=#ffffff>
<TABLE cellSpacing=0 cellPadding=0 width="100%" border=0>
<TBODY>
<TR>
<TD vAlign=top width=618 bgColor=white>
<TABLE cellSpacing=6 cellPadding=0 width="100%"
bgColor=#ffffff border=0>
<TBODY>
<TR></TR></TBODY></TABLE><!--文章显示代码开始-->
<TABLE class=center_tdbgall
style="WORD-BREAK: break-all" cellSpacing=0
cellPadding=0 width=618 align=center border=0>
<TBODY>
<TR>
<TD colSpan=2></TD></TR>
<TR>
<TD class=main_Article_Title
style="WORD-BREAK: break-all" align=middle colSpan=2
height=90>Oracle PL/SQL学习笔记</TD></TR>
<TR>
<TD class=main_Article_other_Title
style="WORD-BREAK: break-all" align=middle colSpan=2
height=22>
<TABLE style="WORD-BREAK: break-all" cellSpacing=0
cellPadding=0 width="100%" border=0>
<TBODY>
<TR>
<TD align=middle><A
title=收藏的网页将被永久地保存到新浪ViVi收藏夹http://vivi.sina.com.cn
href="javascript:d=document;t=d.selection?(d.selection.type!='None'?d.selection.createRange().text:''):(d.getSelection?d.getSelection():'');void(vivi=window.open('http://vivi.sina.com.cn/collect/icollect.php?pid=http://smxf.gnway.net&title='+escape(d.title)+'&url='+escape(d.location.href)+'&desc='+escape(t),'vivi','scrollbars=no,width=480,height=480,left=75,top=20,status=no,resizable=yes'));vivi.focus();">收藏此文到<FONT
color=darkorchid>VI<FONT
color=#57c200>V</FONT>I</FONT></A>| <A
title=功能强大的网络收藏夹，一秒钟操作收藏本文
href="javascript:d=document;t=d.selection?(d.selection.type!='None'?d.selection.createRange().text:''):(d.getSelection?d.getSelection():'');void(keyit=window.open('http://www.365key.com/storeit.aspx?t='+escape(d.title)+'&u='+escape(d.location.href)+'&c='+escape(t),'keyit','scrollbars=no,width=475,height=575,left=75,top=20,status=no,resizable=yes'));keyit.focus();">收藏此文到<FONT
color=darkorchid>365K<FONT
color=#57c200>E</FONT>Y</FONT></A>| <A
title=功能强大的网络收藏夹，一秒钟操作收藏本文
href="javascript:u=location.href;t=document.title;void(open('http://www.YouNote.com/NoteIt.aspx?u='+escape(u)+'&t='+escape(t)+'&c='+escape(document.selection.createRange().text),'网络书签', 'toolbar=no,width=475,height=575,left=75,top=20,status=no,resizable=yes'));">收藏此文到<FONT
color=darkorchid>YOU<FONT
color=#57c200>NO</FONT>TE</FONT></A>
</TD></TR></TBODY></TABLE></TD></TR>
<TR></TR>
<TR align=middle>
<TD bgColor=#f3f3f3
colSpan=5>作者：未知 数据库来源：网络收集 点击数：
<SCRIPT language=javascript
src="Oracle PL-SQL学习笔记_files/GetHits.htm"></SCRIPT>
更新时间：2006-2-7</TD></TR>
<TR>
<TD class=main_tdbg_760 id=fontzoom
style="WORD-BREAK: break-all" vAlign=top colSpan=2
height=300>
<TABLE height=1 cellSpacing=0 cellPadding=0
width=618 border=0>
<TBODY>
<TR>
<TD>
<TABLE borderColor=#cccccc cellSpacing=0
cellPadding=0 width=486 align=center
bgColor=#ffffff border=0 heihgt="60px">
<TBODY>
<TR>
<TD>
<SCRIPT language=javascript
src="Oracle PL-SQL学习笔记_files/4.js"></SCRIPT>
</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
<TABLE cellSpacing=8 cellPadding=3 border=0>
<TBODY>
<TR>
<TD><!--插入广告JS代码-->
<TABLE class=zhi14
style="WIDTH: 620px; HEIGHT: 38px" height=38
cellSpacing=0 cellPadding=0 width=620
align=center border=0>
<TBODY>
<TR>
<TD width=648 height=20>
<P>1 SQL*Plus环境
<BR>--------------------------------------
<BR>show all 显示所有SQL*Plus的命令名字 <BR>list or l
显示缓冲区的内容 <BR>list 4 显示当前SQL命令的第4行，并把第4行作为当前行。
<BR>change or c
用新的内容替换原来在一行中第一次出现内容,例如:SQL>c/(...)/('analyst')/
<BR>input or i 增加一行或多行 <BR>append or a 在一行后追加内容
<BR>del 删除当前行 删除SQL缓冲区中的当前行 <BR>run
显示并运行SQL缓冲区中的命令 <BR>/ 运行SQL缓冲区中的命令 <BR>edit
把SQL缓冲区中的命令写到操作系统下的文本文件， <BR>并调用操作系统提供的编辑器执行修改。
<BR>set linesize 80 设置行的大小为80个字符宽度 <BR>set
pagesize 100 设置页的大小为100行 <BR>ttitle 'smaple
ttitle' 设置页头标 <BR>btitle 'smaple btitle' 设置页脚标
<BR>spool /home/zxin10/myresult.log
设置sqlplus的输出到文件中 <BR>spool off(out) 停止输出文件
<BR>column column_name format a18 wrap heading
’smaple-column-title’
<BR>格式化列输出,每列宽度18个字符,超过18个自动换行,列标题换成'column-title'
<BR>CLEAR COLUMNS 将所有列的显示属性设为缺省值. </P>
<P><BR>2 异常exception
<BR>--------------------------------------
<BR>no_data_found <BR>表示select没有查询到满足条件的数据行。
<BR>others
<BR>表示Oracle预定义错误范围之外的任何错误,Oracle用这个"others"捕捉所有
未知的错误。可以使用sqlcode函数,sqlerrm函数在错误处理模块中显示错误
代码和错误信息。而且,others处理必须是一个块的最后一个错误处理,否则,
others处理的优先级比较高,权利比较大,它会捕捉所有错误,包括预定义的Oracle
错误和非预定义的Oracle错误。 <BR>too_many_rows
<BR>Oracle的隐式游标,一次只能检索一行数据,使用隐式游标时,异常处理机制如果检测
到游标返回的数据是多行数据,它就抛出too_many_rows类型的异常。
<BR>dup_val_on_index <BR>在某个索引上,出现重复值。
<BR>value_error
<BR>在某个目标字段中,放入的数据长度或者数据范围,超出目标字段定义的长度或者范围
,如,把'8613905180088'这个字符串插入userid字段中,而userid定义为varchar2(10),
就会出现这种异常。 </P>
<P>Exception Raised when ...
<BR>ACCESS_INTO_NULL <BR>Your program attempts
to assign values to the attributes of an
uninitialized <BR>(atomically null) object. </P>
<P>CASE_NOT_FOUND <BR>None of the choices in the
WHEN clauses of a CASE statement is selected,
and <BR>there is no ELSE clause. </P>
<P>COLLECTION_IS_NULL <BR>Your program attempts
to apply collection methods other than EXISTS to
an <BR>uninitialized (atomically null) nested
table or varray, or the program attempts <BR>to
assign values to the elements of an
uninitialized nested table or varray. </P>
<P>CURSOR_ALREADY_OPEN <BR>Your program attempts
to open an already open cursor. A cursor must be
closed <BR>before it can be reopened. A cursor
FOR loop automatically opens the cursor to
<BR>which it refers. So, your program cannot
open that cursor inside the loop. </P>
<P>DUP_VAL_ON_INDEX <BR>Your program attempts to
store duplicate values in a database column that
is <BR>constrained by a unique index. </P>
<P>INVALID_CURSOR <BR>Your program attempts an
illegal cursor operation such as closing an
unopened cursor. </P>
<P>INVALID_NUMBER <BR>In a SQL statement, the
conversion of a character string into a number
fails <BR>because the string does not represent
a valid number. (In procedural statements,
<BR>VALUE_ERROR is raised.) This exception is
also raised when the LIMIT-clause <BR>expression
in a bulk FETCH statement does not evaluate to a
positive number. </P>
<P>LOGIN_DENIED <BR>Your program attempts to log
on to Oracle with an invalid username and/or
password. </P>
<P>NO_DATA_FOUND <BR>A SELECT INTO statement
returns no rows, or your program references a
deleted element <BR>in a nested table or an
uninitialized element in an index-by table. SQL
aggregate <BR>functions such as AVG and SUM
always return a value or a null. So, a SELECT
INTO <BR>statement that calls an aggregate
function never raises NO_DATA_FOUND. The FETCH
<BR>statement is expected to return no rows
eventually, so when that happens, no
<BR>exception is raised. </P>
<P>NOT_LOGGED_ON <BR>Your program issues a
database call without being connected to Oracle.
</P>
<P>PROGRAM_ERROR <BR>PL/SQL has an internal
problem. </P>
<P>ROWTYPE_MISMATCH <BR>The host cursor variable
and PL/SQL cursor variable involved in an
assignment have <BR>incompatible return types.
For example, when an open host cursor variable
is passed <BR>to a stored subprogram, the return
types of the actual and formal parameters must
<BR>be compatible. </P>
<P>SELF_IS_NULL <BR>Your program attempts to
call a MEMBER method on a null instance. That
is, the <BR>built-in parameter SELF (which is
always the first parameter passed to a MEMBER
<BR>method) is null. </P>
<P>STORAGE_ERROR <BR>PL/SQL runs out of memory
or memory has been corrupted. </P>
<P>SUBs cript_BEYOND_COUNT <BR>Your program
references a nested table or varray element
using an index number <BR>larger than the number
of elements in the collection. </P>
<P>SUBs cript_OUTSIDE_LIMIT <BR>Your program
references a nested table or varray element
using an index number <BR>(-1 for example) that
is outside the legal range. </P>
<P>SYS_INVALID_ROWID <BR>The conversion of a
character string into a universal rowid fails
because the <BR>character string does not
represent a valid rowid. </P>
<P>TIMEOUT_ON_RESOURCE <BR>A time-out occurs
while Oracle is waiting for a resource. </P>
<P>TOO_MANY_ROWS <BR>A SELECT INTO statement
returns more than one row. </P>
<P>VALUE_ERROR <BR>An arithmetic, conversion,
truncation, or size-constraint error occurs. For
example, <BR>when your program selects a column
value into a character variable, if the value is
<BR>longer than the declared length of the
variable, PL/SQL aborts the assignment and
raises <BR>VALUE_ERROR. In procedural
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -