SELECT Statements in PL/SQL
必须使用INTO,只能选出一条记录
Naming Conventions
避免变量名与列名相同,如相同,优先选择列名。在where语句中会导致歧义。
DECLARE
hire_date employees.hire_date%TYPE;
sysdate hire_date%TYPE;
employee_id employees.employee_id%TYPE := 176;
BEGIN
SELECT hire_date, sysdate
INTO hire_date, sysdate
FROM employees
WHERE employee_id = employee_id;
END;
/
SQL Cursor
• A cursor is a pointer to the private memory area
allocated by the Oracle server.
• There are two types of cursors:
– Implicit: Created and managed internally by the
Oracle server to process SQL statements
– Explicit: Explicitly declared by the programmer
三种SQL Cursor Attributes
SQL%FOUND
SQL%NOTFOUND
SQL%ROWCOUNT
IF Statements
IF condition THEN
statements;
[ELSIF condition THEN
statements;]
[ELSE
statements;]
END IF;
CASE Expressions
CASE selector
WHEN expression1 THEN result1
WHEN expression2 THEN result2
...
WHEN expressionN THEN resultN
[ELSE resultN+1]
END;
/
TRUE AND NULL èNULL
TRUE OR NULL èTRUE
FALSE AND NULL èFALSE
FALSE OR NULL èNULL
NOT NULL èNULL
Basic Loops
LOOP
statement1;
. . .
EXIT [WHEN condition];
END LOOP;
WHILE Loops
WHILE condition LOOP
statement1;
statement2;
. . .
END LOOP;
FOR Loops(counter无需定义)
FOR counter IN [REVERSE]
lower_bound..upper_bound LOOP
statement1;
statement2;
. . .
END LOOP;
Guidelines for Loops
Basic loop:循环体中的语句至少需要被执行一次
While loop:必须将条件判断放在每次循环之前
For loop:预先知道迭代次数
Nested Loops and Labels
循环可欠套使用,不同层的循环可使用labels来表示
<>
END LOOP outer_loop;
Creating a PL/SQL Record
TYPE type_name IS RECORD
(field_declaration[, field_declaration]…);
identifier type_name;
field_declaration:
field_name {field_type | variable%TYPE
| table.column%TYPE | table%ROWTYPE}
[[NOT NULL] {:= | DEFAULT} expr]
%ROWTYPE Attribute(无须另外定义record type)
DECLARE
identifier reference%ROWTYPE;
Updating a Row in a Table by Using a Record
SET SERVEROUTPUT ON
SET VERIFY OFF
DEFINE employee_number = 124
DECLARE
emp_rec retired_emps%ROWTYPE;
BEGIN
SELECT * INTO emp_rec FROM retired_emps;
emp_rec.leavedate:=SYSDATE;
UPDATE retired_emps SET ROW = emp_rec WHERE
empno=&employee_number;
END;
/
SELECT * FROM retired_emps;
Creating an INDEX BY Table
TYPE type_name IS TABLE OF
{column_type | variable%TYPE
| table.column%TYPE} [NOT NULL]
| table%ROWTYPE
[INDEX BY PLS_INTEGER | BINARY_INTEGER
| VARCHAR2()];
identifier type_name;
注意点:1.主键为interger型或string型(PLS_INTEGER or BINARY_INTEGER)
2.column为一个标量或一个record型
3.表的记录数受主键的数据类型的范围限制,可为负数
4.主键和column都不可以被命名
Using INDEX BY Table Methods
EXISTS
COUNT
FIRST and LAST
PRIOR
NEXT
DELETE
Nested Tables
TYPE type_name IS TABLE OF
{column_type | variable%TYPE
| table.column%TYPE} [NOT NULL]
| table.%ROWTYPE
说明:计数列为正数,从1开始引用。属于数据库的数据类型的一种
可使用constructor直接赋值
TYPE location_type IS TABLE OF locations.city%TYPE;
offices location_type;
offices := location_type('Bombay', 'Tokyo','Singapore','Oxford');
VARRAY
TYPE location_type IS VARRAY(3) OF locations.city%TYPE;
offices location_type;
说明:声明时指定了最大值,可使用constructor直接赋值
[@more@]来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8558093/viewspace-1014379/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/8558093/viewspace-1014379/