记录下自己学习的过程,备忘!
以下试验均在Oracle默认账户hr/hr下进行
1. SQL statement在PL/SQL中
(1).用select语句从数据库获取数据,语法:
select select_list(列名的列表)
into {variable_name,[variable_name]...| record_name}
from table
[where condition]
试验的运行环境:
[oracle@localhost sql]$ sqlplus hr/hr
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Aug 18 15:10:13 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> @pro4_1
PL/SQL procedure successfully completed.
SQL> setseroutput on
SP2-0734: unknown command beginning "setseroutp..." - rest of line ignored.
SQL> set serveroutput on
SQL> @pro4_1
tmp_name's inital name is Hello Harly!
My name is Ellen
PL/SQL procedure successfully completed.
SQL> l
1 declare
2 tmp_name employees.first_name%type := 'Hello Harly!';
3 begin
4 dbms_output.put_line(q'[tmp_name's inital name is ]'||tmp_name);
5 select first_name into tmp_name from employees where rownum<2;
6 dbms_output.put_line('My name is '||tmp_name);
7* end;
SQL>
(2).命名规范(naming convention)
a.Use a naming convention to avoid ambiguity in the where clause.
b.Avoid using database column names as identifiers.
c.The names of local variables and formal parameters take precedence over the
names of database tables.
d.The names of database table columns take precedence over the names of local
variables.
2. Merging Rows(合并行)
试验过程(先建立一张与employees结果一样的表):
SQL> create table test as select *from employees where rownum<10;
Table created.
SQL> select employee_id,first_name from test;
EMPLOYEE_ID FIRST_NAME
----------- --------------------
198 Donald
199 Douglas
200 Jennifer
201 Michael
202 Pat
203 Susan
204 Hermann
205 Shelley
206 William