1.存储过程
2.函数
3.包的创建及其应用
4.视图
5.数据库链
1.创建存储过程及其应用
1.1无参数存储过程
--存储过程(无参数)
create or replace procedure my_proc
is
uname varchar2(10);
begin
select name into uname from users where id=2;
dbms_output.put_line(uname);
end;
1.2存储过程,in
--存储过程(in)
--in类似于写入参数
create or replace procedure my_proc(uid in number)
is
begin
select * from users where id=uid;
end;
--调用
exec my_proc(2)
1.3存储过程,out
--存储过程(out)
--out类似于返回参数
create or replace procedure my_proc(uid out number)
is
uid number;
begin
select id into uid from users where id=2;
end;
--调用
declare
id number;
begin
my_proc(id);
dbms_output.putline(id);
end;
1.4存储过程,in out
--存储过程(in out)
--in out会将存储过程中最终改变的值,进行返回
create or replace procedure my_proc(num1 in out int ,num2 in out int)
is
temp int ;
begin
temp:=num1;
num1:=num2;
num2:=temp;
end;
--调用
declare
n1 int:=10;
n2 int:=20;
begin
dbms_output.put_line(n1||'=='||n2);
my_proc(n1,n2);
dbms_output.put_line(n1||'=='||n2);
end;
2.函数的创建及其应用,函数同样拥有in,out,in out
--函数创建
create or replace function my_fun
return varchar2
is
begin
return 'ss';
end;
---调用1,以表的形式显示
select my_fun as show from dual;
--调用 2
declare
str varchar2(20);
begin
str:=my_fun;
dbms_output.put_line(str);
end;
3.包的创建及其应用
--创建包头
create or replace package my_pack
is
procedure my_proc(uid in number);
function my_fun return varchar2;
end my_pack;
--创建包体
create or replace pack body my_pack
is
procedure my_proc(uid in number)
is
uname varchar2(20);
begin
select name into uname from users where id=uid;
dbms_output.put_line(uname);
end my_proc;
function my_fun
return varchar2
is
begin
return 'hello';
end my_fun;
end my_pack;
--调用
exec my_package.my_proc(2);
select my_package.my_fun from dual;
4.视图的创建及其应用
--视图创建
create view my_view as select * from users where id>25;
--调用
select * from my_view;
--删除
drop view my_view;
5.数据链的创建及其应用
--方法1(改文件)
create database link orcl_link
connect to accp identified by accp using 'ORCL';
--调用
select * from users@orcl_link;
--删除
drop database link orcl_link;
--方法2
create database link orcl_link
connect to accp identified by accp using '
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.4)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
';