2009年3月12日
打开Net Manager，选中服务名称，检查服务标识栏里的服务名输入是否正确。该服务名必须与服务器端监听器配置的全局数据库名一致。
方法二： 在TOAD登陆时使用全局名称：
zhouzhenhua:1521/javafan
人品不错，挺让人放心的，不会像有的男的看起来很花花的，还有就是没有明显的缺点，这可是很重要的
我现在看到的男的歪瓜裂枣的太多了
PL/SQL集合方法是用于操纵集合变量的内置函数或过程。
语法：
collection_name.method_name[(parameter)]
注意：集合方法extend和trim不适用于PL/SQL表。
1. exists
2. count
3. limit
返回VARRAY变量所允许的最大元素个数。嵌套表和PL/SQL表的元素个数无限制，所以调用该方法时返回NULL。
4. first和last
第一个元素的下标，最后一个元素的下标
ename_table(ename_table.first)
ename_table(ename_table.last)
5. prior和next
前一个元素的下标，后一个元素的下标
ename_table(ename_table.prior(1))
ename_table(ename_table.next(1))
6.extend
该方法为PL/SQL集合变量增加元素，只适用于嵌套表和VARRAY。
注意：当使用EXTEND增加元素时，不能为未初始化的集合变量增加元素。
EXTEND方法有三种调用方法：
- EXTEND:添加一个null元素。
- EXTEND(n):添加n个null元素
- EXTEND(n,i):添加n个元素，元素值与第i个元素相同。
7. trim
该方法用于从集合变量尾部删除元素，并且只适用于嵌套表和VARRAY。
TRIM共有两种调用方法：
- TRIM: 从集合变量尾部删除一个元素。
- TRIM(n): 从集合变量尾部删除n个元素。
8. delete
用于删除特定元素，只适用于嵌套表和PL/SQL表。
DELETE有三种调用方法：
- DELETE: 删除所有的元素
- DELETE(n): 删除第n个元素。
- DELETE(m,n):删除m~n之间所有的元素。
第一，PL/SQL表
它只能作为PL/SQL类型，不能作为表列类型。
下标可以为负值，并且元素个数无限制。
下标类型key_type: 包括BINARY_INTEGER、PLS_INTEGER、VARCHAR2。
语法：
TYPE type_name IS TABLE OF element_type
[NOT NULL] INDEX BY key_type;
identifier type_name;
例如：
DECLARE
TYPE category_table_type IS TABLE OF VARCHAR2 (40)
INDEX BY VARCHAR2 (40);
category_table category_table_type;
BEGIN
category_table ('长沙') := '长沙，我爱你！';
DBMS_OUTPUT.put_line (category_table ('长沙'));
END;
第二，嵌套表
可以作为表列的数据类型使用。
当使用嵌套表元素时，需要使用构造方法初始化嵌套表变量。
下标从1开始计算。
语法：
TYPE type_name IS TABLE OF element_type;
identifier type_name;
例子：
DECLARE
TYPE category_table_type IS TABLE OF tb_out_service.servicename%TYPE;
category_table category_table_type;
BEGIN
category_table := category_table_type ('全省', '长沙', '常德');
SELECT servicename
INTO category_table (3)
FROM tb_out_service
WHERE serviceid = '&serviceid';
DBMS_OUTPUT.put_line (category_table (1));
DBMS_OUTPUT.put_line (category_table (2));
DBMS_OUTPUT.put_line (category_table (3));
END;
第三，变长数组(VARRAY)
需要使用构造方法初始化VARRAY变量。
语法：
TYPE type_name is VARRAY(size_limit) OF element_type [NOT NULL];
identifier type_name;
第一，定义PL/SQL记录
1. 自定义PL/SQL记录
2. 使用%ROWTYPE属性定义记录变量
举例：
-----------------------------------------------
DECLARE
--自定义记录类型
TYPE emp_record_type IS RECORD (
serviceid tb_out_service%TYPE,
sericename tb_out_service%TYPE
);
emp_record emp_record_type;
--使用%ROWTYPE属性定义记录变量
category_record tb_out_service%ROWTYPE;
BEGIN
NULL;
END;
第二，使用PL/SQL记录
DECLARE
--使用%ROWTYPE属性定义记录变量
category_r tb_out_service%ROWTYPE;
BEGIN
...
UPDATE tb_out_service
SET ROW = category_r --注意ROW关键字
WHERE serviceid = category_r.serviceid;
...
INSERT INTO tb_out_service
VALUES category_r; --在values子句中使用记录成员播入数据。
...
END;
commit
rollback
savepoint a1
隐含游标：专门处理SELECT INTO、INSERT、UPDATE以及DELETE语句。
显示游标：用于处理多行的SELECT语句。
当在PL/SQL块中执行INSERT、UPDATE及DELETE语句时，为了取得DML语句作用的结果，需要使用SQL游标属性。
第一，游标的4种属性
1. SQL%ISOPEN
该属性永远都是false，没有实际意义。
2. SQL%FOUND
语句是否操作成功。当SQL语句有作用行时为TRUE.
3. SQL%NOTFOUND
LOOP
FETCH emp_cursor INTO v_name, v_deptno;
EXIT WHEN emp_cursor%NOTFOUND;
...
END LOOP;
4. SQL%ROWCOUNT
返回SQL语句所作用的总计行数。
该属性用于返回已提取的实际行数。
第二，显示游标专用于处理SELECT语句返回的多行数据。
CURSOR cursor_name IS select_statement;
open cursor_name;
FETCH cursor_name INTO var1[, var2, ...];
CLOSE cursor_name;
例子：
DECLARE
CURSOR category_cursor
IS
SELECT *
FROM tb_out_service t
WHERE t.servicename LIKE '长沙%';
TYPE category_table_type IS TABLE OF tb_out_service%ROWTYPE
INDEX BY BINARY_INTEGER;
category_table category_table_type;
i INT;
BEGIN
OPEN category_cursor;
LOOP
i := category_cursor%ROWCOUNT + 1;
FETCH category_cursor
INTO category_table (i);
EXIT WHEN category_cursor%NOTFOUND;
DBMS_OUTPUT.put_line ( RPAD (category_table (i).serviceid, 30)
|| ' '
|| category_table (i).servicename
);
END LOOP;
CLOSE category_cursor;
END;
第三，游标FOR循环
语法：
FOR record_name IN cursor_name LOOP
statement;
...
END LOOP;
注意：当使用游标FOR循环时，既可以在定义部分定义游标，也可以直接在FOR循环中使用SELECT语句。
第四，参数游标
CURSOR cursor_name(parameter_name datatype) is select_statement;
OPEN cursor_name(parameter_value)
注意：
定义参数只能指定数据类型，不能指定长度。
另外，应该在游标的select语句的where子句中引用游标参数，否则失去了定义参数游标的意义。
---------------------------------------------------------------------------------------------------------------------
DECLARE
CURSOR category_cursor (NAME VARCHAR2)
IS
SELECT *
FROM tb_out_service t
WHERE t.servicename LIKE NAME || '%';
BEGIN
FOR category_record IN category_cursor ('吉首')
LOOP
DBMS_OUTPUT.put_line ( RPAD (category_record.serviceid, 30)
|| ' '
|| category_record.servicename
);
END LOOP;
END;
第五，更新或删除游标行
语法：
CURSOR cursor_name IS select_statement
FOR UPDATE [OF column_reference] [NOWAIT];
UPDATE table_name SET column=.. WHERE CURRENT OF cursor_name;
DELETE FROM table_name WHERE CURRENT OF cursor_name;
注意：
1. OF子句的格式：column_reference为table_name.column_name
2. WHERE CURRENT OF cursor_name, 更新或者删除游标所在行的数据。
3. 在使用游标更新或删除数据时，定义游标必须带有FOR UPDATE子句，并且在更新或者删除游标时必须带有WHERE CURRENT OF 子句。
当查询语句涉及到多张表时，如果不带有OF子句，会在多张表上同时加锁，如果只在特定的表上加锁，需要带有OF子句。
DECLARE
CURSOR category_cursor (NAME VARCHAR2)
IS
SELECT *
FROM tb_out_service t
WHERE t.servicename LIKE NAME || '%'
FOR UPDATE OF t.querystr;
v_name CONSTANT VARCHAR2 (20) := '长沙';
BEGIN
FOR category_record IN category_cursor (v_name)
LOOP
IF INSTR (category_record.servicename, v_name || '——') <> 1
THEN
DBMS_OUTPUT.put_line ( 'delete: '
|| RPAD (category_record.serviceid, 30)
|| ' '
|| category_record.servicename
);
DELETE FROM tb_out_service
WHERE CURRENT OF category_cursor;
ELSE
DBMS_OUTPUT.put_line ( 'upate: '
|| RPAD (category_record.serviceid, 30)
|| ' '
|| category_record.servicename
);
UPDATE tb_out_service t
SET t.querystr = v_name || '——' || t.servicename
WHERE CURRENT OF category_cursor;
END IF;
END LOOP;
END;
第六， 游标变量
游标变量是基于REF CURSOR类型所定义的变量，它实际上是指向内存地址的指针。显式游标只能定义静态游标，而游标变量可以在打开时指定其所对应的SELECT语句，从而实现动态游标。
TYPE ref_type_name IS REF CURSOR [RETURN return_type];
cursor_variable ref_type_name;
OPEN cursor_name FOR select_statement;
FETCH cursor_variable INTO var1[, var2, ...];
CLOSE cursor_variable;
注意：
不能在远程子程序中使用游标变量。
当指定子查询时，不能带有FOR UPDATE子句。
当指定RETURN子句时，返回类型必须使用PL/SQL记录类型。
例子：
DECLARE
TYPE category_cursor_type IS REF CURSOR
RETURN tb_out_service%ROWTYPE;
category_cursor category_cursor_type;
category_record tb_out_service%ROWTYPE;
v_name CONSTANT VARCHAR2 (40) := '长沙';
BEGIN
OPEN category_cursor FOR
SELECT *
FROM tb_out_service t
WHERE t.servicename LIKE v_name || '%';
LOOP
FETCH category_cursor
INTO category_record;
EXIT WHEN category_cursor%NOTFOUND;
DBMS_OUTPUT.put_line ( RPAD (category_record.serviceid, 30)
|| ' '
|| category_record.servicename
);
END LOOP;
END;
编写PL/SQL时，可以直接使用大多数的单行SQL函数，这些单行函数包括数字函数、字符函数、转换函数及日期函数。
注意：某些SQL函数只能在SQL语句中引用，而不能直接在PL/SQL语句中引用，这些SQL函数包括GREATEST、LEAST、DECODE及所有的分组函数（如SUM）。
约束用于确保数据库数满足业务规则。
约束包括：NOT NULL，UNIQUE，PRIMARY KEY，FOREIGN KEY以及CHECK等5种类型。
建立主键约束和唯一约束时，Oralce会基于约束列自动建立唯一索引；主键约束不允许为NULL，唯一约束允许为NULL。
一张表只能建立一个主键约束。
建表约束：NOT NULL只能在列级定义；其它4种既可以在列级定义，也可以在表级定义。复合主键约束只能在表级定义。
维护约束：增加NOT NULL约束时必须使用MODIFY子句，而增加其它约束时需要使用ADD子句。
第一， 定义约束
---------------------------------------------
语法：
CREATE TABLE [SCHEMA.]table_name(
column_name datatype [DEFAULT expr] [column_constraint],
...
[table_constraint][, ...]
);
例子：
CREATE TABLE tt_user_info
(
ID VARCHAR2(20 BYTE),
NAME VARCHAR2(20 BYTE) NOT NULL,
category_id VARCHAR2(20 BYTE) REFERENCES tb_out_service(serviceid),
remark VARCHAR2(1000)
);
ALTER TABLE tt_user_info ADD (
CHECK ( LENGTH(NAME)>2),
PRIMARY KEY (ID),
UNIQUE (NAME)
);
说明：
1. NOT NULL，非空约束
not null
2. UNIQUE，唯一约束
UNIQUE (COL_NAME)
3. PRIMARY KEY，主键约束
primary key (col_name1 [, col_name2])
4. FOREIGN KEY，外键约束
它有三种类型：
references primary_table(primary_col)
on delete cascade
on delete set null
5. CHECK，检查约束
check (money > 1000)
第二， 维护约束
----------------------------------------
1. 增加约束
NOT NULL使用ALTER MODIFY子句，其它的使用ALTER ADD子句
-------------------------------
CREATE TABLE tt_user(NAME VARCHAR2(20));
ALTER TABLE tt_user MODIFY user_name NOT NULL;
ALTER TABLE tt_user ADD CONSTRAINT constraint_name UNIQUE(NAME);
ALTER TABLE tt_user ADD CONSTRAINT constraint_name PRIMARY KEY(NAME);
ALTER TABLE tt_user ADD parentid VARCHAR2(20)
CONSTRAINT constraint_name
REFERENCES tb_out_service(serviceid);
2. 修改约束名
ALTER TABLE table_name RENAME CONSTRAINT old_constraint_name TO new_constraint_name
3. 删除约束
ALTER TABLE table_name DROP CONSTRAINT constraint_name
4. 禁止约束
ALTER TABLE table_name DISABLE CONSTRAINT constraint_name [CASCADE];
5.激动约束
ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;
第三. 显示约束信息
所有约束信息
SELECT *
FROM user_constraints
用户约束所对应的表列
SELECT *
FROM user_cons_columns;
临时表分为两种，它们只在事务或者会话内有效。
------------------------------------
A. 事务临时表(缺省)：
CREATE GLOBAL TEMPORARY TABLE table_name(col_name INT);
CREATE GLOBAL TEMPORARY TABLE table_name(col_name INT) ON COMMIT DELETE ROWS;
------------------------------------
B. 会话临时表：
CREATE GLOBAL TEMPORARY TABLE table_name(col_name INT) ON COMMIT PRESERVE ROWS;
A. 删除数据
-----------------------------------------
方法一：
delete(DML)
只是删除数据，并没有释放空间
操作可以回退
例如：
DELETE FROM table_name;
方法二：
truncate table(DDL)
删除数据，释放表空间，操作不能回退
例如：
TRUNCATE TABLE table_name;
B. 删除库表
------------------------------------------
DROP TABLE table_name [CASCADE CONSTRAINTS] [purge]
CASCADE CONSTRAINTS： 表示是否级联删除外键约束
C. 恢复删除的库表（10g）
FLASHBACK TABLE table_name TO BEFORE DROP;