分享
 
 
 

Oracle PL/SQL学习笔记(1)

王朝oracle·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

1 SQL*Plus环境

--------------------------------------

show all 显示所有SQL*Plus的命令名字

list or l 显示缓冲区的内容

list 4 显示当前SQL命令的第4行,并把第4行作为当前行。

change or c 用新的内容替换原来在一行中第一次出现内容,例如:SQL>c/(...)/('analyst')/

input or i 增加一行或多行

append or a 在一行后追加内容

del 删除当前行 删除SQL缓冲区中的当前行

run 显示并运行SQL缓冲区中的命令

/ 运行SQL缓冲区中的命令

edit 把SQL缓冲区中的命令写到操作系统下的文本文件,

并调用操作系统提供的编辑器执行修改。

set linesize 80 设置行的大小为80个字符宽度

set pagesize 100 设置页的大小为100行

ttitle 'smaple ttitle' 设置页头标

BTitle 'smaple btitle' 设置页脚标

spool /home/zxin10/myresult.log 设置sqlplus的输出到文件中

spool off(out) 停止输出文件

column column_name format a18 wrap heading ’smaple-column-title’

格式化列输出,每列宽度18个字符,超过18个自动换行,列标题换成'column-title'

CLEAR COLUMNS 将所有列的显示属性设为缺省值.

2 异常exception

--------------------------------------

no_data_found

表示select没有查询到满足条件的数据行。

others

表示Oracle预定义错误范围之外的任何错误,Oracle用这个"others"捕捉所有 未知的错误。可以使用sqlcode函数,sqlerrm函数在错误处理模块中显示错误 代码和错误信息。而且,others处理必须是一个块的最后一个错误处理,否则, others处理的优先级比较高,权利比较大,它会捕捉所有错误,包括预定义的Oracle 错误和非预定义的Oracle错误。

too_many_rows

Oracle的隐式游标,一次只能检索一行数据,使用隐式游标时,异常处理机制假如检测 到游标返回的数据是多行数据,它就抛出too_many_rows类型的异常。

dup_val_on_index

在某个索引上,出现重复值。

value_error

在某个目标字段中,放入的数据长度或者数据范围,超出目标字段定义的长度或者范围 ,如,把'8613905180088'这个字符串插入userid字段中,而userid定义为varchar2(10), 就会出现这种异常。

Exception Raised when ...

Access_INTO_NULL

Your program attempts to assign values to the attributes of an uninitialized

(atomically null) object.

CASE_NOT_FOUND

None of the choices in the WHEN clauses of a CASE statement is selected, and

there is no ELSE clause.

COLLECTION_IS_NULL

Your program attempts to apply collection methods other than EXISTS to an

uninitialized (atomically null) nested table or varray, or the program attempts

to assign values to the elements of an uninitialized nested table or varray.

CURSOR_ALREADY_OPEN

Your program attempts to open an already open cursor. A cursor must be closed

before it can be reopened. A cursor FOR loop automatically opens the cursor to

which it refers. So, your program cannot open that cursor inside the loop.

DUP_VAL_ON_INDEX

Your program attempts to store duplicate values in a database column that is

constrained by a unique index.

INVALID_CURSOR

Your program attempts an illegal cursor operation sUCh as closing an unopened cursor.

INVALID_NUMBER

In a SQL statement, the conversion of a character string into a number fails

because the string does not represent a valid number. (In procedural statements,

VALUE_ERROR is raised.) This exception is also raised when the LIMIT-clause

eXPression in a bulk FETCH statement does not evaluate to a positive number.

LOGIN_DENIED

Your program attempts to log on to Oracle with an invalid username and/or passWord.

NO_DATA_FOUND

A SELECT INTO statement returns no rows, or your program references a deleted element

in a nested table or an uninitialized element in an index-by table. SQL aggregate

functions such as AVG and SUM always return a value or a null. So, a SELECT INTO

statement that calls an aggregate function never raises NO_DATA_FOUND. The FETCH

statement is expected to return no rows eventually, so when that happens, no

exception is raised.

NOT_LOGGED_ON

Your program issues a database call without being connected to Oracle.

PROGRAM_ERROR

PL/SQL has an internal problem.

ROWTYPE_MISMATCH

The host cursor variable and PL/SQL cursor variable involved in an assignment have

incompatible return types. For example, when an open host cursor variable is passed

to a stored subprogram, the return types of the actual and formal parameters must

be compatible.

SELF_IS_NULL

Your program attempts to call a MEMBER method on a null instance. That is, the

built-in parameter SELF (which is always the first parameter passed to a MEMBER

method) is null.

STORAGE_ERROR

PL/SQL runs out of memory or memory has been corrupted.

SUBs cript_BEYOND_COUNT

Your program references a nested table or varray element using an index number

larger than the number of elements in the collection.

SUBs cript_OUTSIDE_LIMIT

Your program references a nested table or varray element using an index number

(-1 for example) that is outside the legal range.

SYS_INVALID_ROWID

The conversion of a character string into a universal rowid fails because the

character string does not represent a valid rowid.

TIMEOUT_ON_RESOURCE

A time-out occurs while Oracle is waiting for a resource.

TOO_MANY_ROWS

A SELECT INTO statement returns more than one row.

VALUE_ERROR

An arithmetic, conversion, truncation, or size-constraint error occurs. For example,

when your program selects a column value into a character variable, if the value is

longer than the declared length of the variable, PL/SQL aborts the assignment and raises

VALUE_ERROR. In procedural statements, VALUE_ERROR is raised if the conversion of a

character string into a number fails. (In SQL statements, INVALID_NUMBER is raised.)

ZERO_DIVIDE

Your program attempts to divide a number by zero.

3 连接查询

----------------------------------------

假设两个表s1cardinf,s1prefer

s1cardinf内容:

allindex

1

2

3

s1prefer内容:

allindex

2

3

4

select a.allindex,b.allindex

from s1cardinf a , s1prefer b

where a.allindex = b.allindex ;

结果:

allindex,allindex

2 2

3 3

select a.allindex,b.allindex

from s1cardinf a , s1prefer b

where a.allindex (+) = b.allindex ;

结果:

allindex,allindex

2 2

3 3

null 4

select a.allindex,b.allindex

from s1cardinf a , s1prefer b

where a.allindex = b.allindex (+) ;

结果:

allindex,allindex

1 null

2 2

3 3

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有