e,lastname from student where id < 1001;
begin
for stus in t_student loop
fname := t_student.firstname;
lname := t_student.lastname;
dbms_output.put_line('姓名：'||fname||''||lname);
end loop;
end;
-- 存储过程
create proc | procedure pro_name
[{@参数数据类型} [=默认值] [output],
{@参数数据类型} [=默认值] [output],
....
]
as
SQL_statements
-- 创建无参存储过程
create or replace procedure showInfo
as
select * from student
begin
showInfo('Jock'); -- 执行存储过程
end;
-- 创建带参存储过程
create or replace procedure showInfo (Major in varchar2) as // 声明一个输入参数
select * from student where major = Major;
begin
showInfo('Jock'); -- 执行存储过程
end;
drop showInfo -- 删除存储过程
-- 函数语法
create [or replace] function 名称
[(参数1 [{in|out|in out} 类型 参数[{in|out|in out} 类型...]]) return 返回类型 {is | as}]
function _body;
-- 定义函数
create or replace function getCount(Major in varchar2)
return number as f_count number; // 声明返回类型
begin
select count(*) into f_count from students where major = 'Magor'
return f_count; // 返回return语句
end;
-- 使用函数
declare
v_count number;
begin
v_count := getCount('Music');
dbms_output.put_line(v_count);
end;
drop function getCount -- 删除函数
-- 创建包头
create or replace package emp_package as
-- 声明一个存储过程
procedure my_proc(
lend_nun varchar2;
lend_name varchar2;
ledn_sex varchar2;
major varchar2;
);
end emp_package;
-- 创建包体
create or replace package body emp_package as
-- 存储过程的实现
procedure my_proc(
lend_num varchar2;
lend_name varchar2;
lend_sex varchar2;
major varchar2;
) is
begin
insert into emp(lnum,lname,lsex,major) values(lend_num,lend_name,lend_sex,major);
end my_proc;
end emp_package;
-- 调用包
package_name.type_name;
begin
emp_package.my_proc('1001','Jock','male','music');
end;
-- 定义视图
create or replace view v_student as select * from student;
select * from v_student; // 查询视图
drop view v_student; // 删除视图
-- 序列
create sequence seq_name
[increment by n]
[start with n]
[maxvalue n | nomaxvalue] // nomaxvalue：为升序指定最大值为1027，降序最大为-1
[minvalue n | mominvalue] // nominvalue：为升序指定最小值为1，降序最小为-1026
-- 修改序列
alter sequence seq_name
[increment by n]
[maxvalue n | nomaxvalue]
[minvalue n | mominvalue]
-- 删除序列
drop sequence seq_name;
create sequence seq_Id
minvalue 1
maxvalue 1000
start with 1
increment by 1
cache 20;
-- 数据库链
create [public] datebase link link_name
connect to username identified by password
using 'servername / serverurl';
select * from tablename@link_name;
create database link link_goods
connect to scott identified scott
using '(description = (address_list = (address = (protocol = tcp)(host = 10.0.0.34)(port = 1521)))(connect_data = (service_name = Orcl)))';
select * from goods@link_goods;
-- 索引
create [unique] index [schema.]index_name on table_name(col_name)
[tablespace ts]
[storage s]
[pctfree pf]
[nosort ns]
schema: 表示Oracle模式，缺省默认当前账户
tablespace: 索引存储表空间
storage：存储参数
pctfree：索引数据块空闲空间的百分比
nosort：不排序(存储时已经按照升序排序，无需再排序)
create unique index i_id on student(id);
-- 修改索引
alter [unique] index index_name
[initrans n]
[maxtrans n]
rebuild
[storage]
initrans：一个块内同时访问的初始事务的入口数，n为十进制整数
maxtrans：一个块内同时访问的最大事务入口数，n为十进制整数
rebuild：根据原来的索引结构重新建立索引，即重新对表进行全表扫描以后创建索引数据
storage: 存储数据，与create index相同
alter index i_id rebuild storage(initial 1M next 512k)
--删除索引
drop index schema.index_name;
|