ORACLE PL/SQL 记录(Record)学习笔记(二)
二、以形参的形式定义和使用记录、对象类型
在用作形式参数时,记录类型和对象类型有很多相同之处。在将它们作为游标、函数或过程的形式参数以前,事先都必须定义一个记录类型或者对象类型。
如下例所示:
记录
DECLARE
-- Define a record type.
TYPE individual_record IS RECORD
(individual_id INTEGER
,first_name VARCHAR2(30 CHAR)
,middle_initial VARCHAR2(1 CHAR)
,last_name VARCHAR2(30 CHAR));
-- Define a record type.
TYPE address_record IS RECORD
(address_id INTEGER
,individual_id INTEGER
,street_address1 VARCHAR2(30 CHAR)
,street_address2 VARCHAR2(30 CHAR)
,street_address3 VARCHAR2(30 CHAR)
,city VARCHAR2(20 CHAR)
,state VARCHAR2(20 CHAR)
,postal_code VARCHAR2(20 CHAR)
,country_code VARCHAR2(10 CHAR));
-- Define a record type of two user defined record type variables.
TYPE individual_address_record IS RECORD
(individual INDIVIDUAL_RECORD
,address ADDRESS_RECORD);
-- Define a variable of a user defined compound record type.
individual_address INDIVIDUAL_ADDRESS_RECORD;
-- Define a local procedure to manage addresses inserts.
PROCEDURE insert_address
(address_in ADDRESS_RECORD) IS
BEGIN
-- Insert the values into the target object.
INSERT
INTO addresses
VALUES
(address_in.address_id
,address_in.individual_id
,address_in.street_address1
,address_in.street_address2
,address_in.street_address3
,address_in.city
,address_in.state
,address_in.postal_code
,address_in.country_code);
END insert_address;
-- Define a local procedure to manage addresses inserts.
PROCEDURE insert_individual
(individual_in INDIVIDUAL_RECORD) IS
BEGIN
-- Insert the values into the table.
INSERT
INTO individuals
VALUES
(individual_in.individual_id
,individual_in.first_name
,individual_in.middle_initial
,individual_in.last_
相关文档:
2009-07-16 10:59
索引创建策略
1.导入数据后再创建索引
2.不需要为很小的表创建索引
3.对于取值范围很小的字段(比如性别字段)应当建立位图索引
4.限制表中的索引的数目
5.为索引设置合适的PCTFREE值
6.存储索引的表空间最好单独设定
创建不唯一索引
create index emp_ename on employees(ename)
table ......
declare
msg varchar(1000); --定义一个变量用于显示传出的信息
begin
PRO_TACCOUNT_RECOUNT ('B9D2B59A955541298D59EA8C35668CFD',msg); --执行存储过程
dbms_output.put_line(msg); --输出变量
end; ......
完全卸载Oracle方法:
软件环境:
1、Windows XP + Oracle 10g
2、Oracle安装路径为:d:\Oracle
1、如果数据库配置了自动存储管理(ASM),应该先删除聚集同步服务CSS(cluster Synchronization Services).删除CSS服务的方法是在DOS命令行中执行如下命令: localconfig delete
2、在“服务”窗口中停 ......
一、设置初始化参数 job_queue_processes
sql> alter system set job_queue_processes=n;(n>0)
job_queue_processes最大值为1000
查看job queue 后台进程
sql>select name,description from v$bgprocess;
二,dbms_job package 用法介绍
包含以下子过程:
......
SQL语言由命令、子句、运算和集合函数等构成。在SQL中,数据定义语言DDL(用来建立及定义数据表、字段以及索引等数据库结构)包含的命令有CREATE、DROP、ALTER;数据操纵语言DML(用来提供数据的查询、排序以及筛选数据等功能)包含的命令有SELECT、INSERT、UPDATE、DELETE。
一、SQL语句
(1)Select 查询语句
语 ......