脚本drop table tb_shop;
drop table tb_shoptype;
create table tb_shopType(
stid number(10) primary key,
typeName varchar2(10) not null
);
create table tb_shop(
sid number(10) primary key,
shopId varchar2(20) unique not null,
shopName varchar(20) not null,
price number(6,2) not null,
shopTypeId number(10) not null,
manufacturingDate date not null,
constraint ck_price check(price>0),
constraint fk_shopTypeId foreign key(shopTypeId) references tb_shoptype(stid)
);insert into Tb_Shoptype values(1,'a');
insert into Tb_Shoptype values(2,'b');
insert into Tb_Shoptype values(3,'c');
insert into Tb_Shoptype values(4,'d');
insert into Tb_Shoptype values(5,'e');
insert into Tb_Shoptype values(6,'f');
insert into Tb_Shoptype values(7,'g');
insert into Tb_Shoptype values(8,'k');
insert into Tb_Shoptype values(9,'l');
insert into Tb_Shoptype values(10,'n');
select * from tb_shoptype
commit;
insert into tb_shop values(1,'a1','温家宝',321,2,'11-1月-11');
insert into tb_shop values(2,'b2','胡景涛',123,3,'21-3月-11');
insert into tb_shop values(3,'c3','张三',434,4,'23-5月-10');
insert into tb_shop values(4,'d4','李岳',554,9,'22-2月-10');
insert into tb_shop values(5,'e5','张德',212,7,'12-1月-10');
insert into tb_shop values(6,'f6','东方不败',546,5,'4-3月-10');
insert into tb_shop values(7,'g7','李云龙',346,5,'16-3月-10');
insert into tb_shop values(8,'h8','楚云飞',653,3,'19-2月-10');
insert into tb_shop values(9,'i9','朱德',343,4,'5-10月-10');
insert into tb_shop values(10,'j10','毛爷爷',123,4,'9-12月-10');
insert into tb_shop values(11,'k11','周恩来',567,2,'6-11月-10');
insert into tb_shop values(12,'l12','花儿乐队',436,9,'4-11月-10');
insert into tb_shop values(13,'m13','刘烨',436,2,'11-10月-10');
insert into tb_shop values(14,'n14','谢娜',743,1,'11-3月-10');
insert into tb_shop values(15,'o15','思恋姐',432,2,'12-3月-10');
insert into tb_shop values(16,'p16','叶文',466,6,'6-5月-10');
insert into tb_shop values(17,'q17','俞灏明',567,7,'8-9月-10');
insert into tb_shop values(18,'r18','扯淡',132,6,'3-1月-11');
insert into tb_shop values(19,'s19','汉奸',44,6,'16-2月-11');
insert into tb_shop values(20,'t20','杀一个',342,2,'3-1月-11');
insert into tb_shop values(21,'u21','主公',34,6,'16-2月-11');
select * from tb_shop;函数
create or replace function fun_get_emp_info(
param_shopname varchar(20),
param_shoptype out varchar2(10)
)
return varchar2(20)
as
v_sjob tb_shop.shopid%type;
begin
select a.shopid,b.typename into param_sjob,param_shoptype from tb_shop a,tb_shoptype b
where a.shoptypeid=b.stid and upper(a.shopname)=upper(param_shopname);
return v_sjob;
exception
when no_data_found then
raise_application_error(-20000,'该员工不存在!');
end;调用方法
declare
v_shopname varchar(20):='张三';
v_shoptype varchar2(10);
v_sjob varchar2(20);
begin
v_sjob:=fun_get_emp_info(v_shopname,v_shoptype);
dbms_output.put_line('商品名称:'||v_shopname);
dbms_output.put_line('商品类型:'||v_shoptype);
dbms_output.put_line('商品编号:'||v_ejob);
end;
注:创建函数时就报错!
drop table tb_shoptype;
create table tb_shopType(
stid number(10) primary key,
typeName varchar2(10) not null
);
create table tb_shop(
sid number(10) primary key,
shopId varchar2(20) unique not null,
shopName varchar(20) not null,
price number(6,2) not null,
shopTypeId number(10) not null,
manufacturingDate date not null,
constraint ck_price check(price>0),
constraint fk_shopTypeId foreign key(shopTypeId) references tb_shoptype(stid)
);insert into Tb_Shoptype values(1,'a');
insert into Tb_Shoptype values(2,'b');
insert into Tb_Shoptype values(3,'c');
insert into Tb_Shoptype values(4,'d');
insert into Tb_Shoptype values(5,'e');
insert into Tb_Shoptype values(6,'f');
insert into Tb_Shoptype values(7,'g');
insert into Tb_Shoptype values(8,'k');
insert into Tb_Shoptype values(9,'l');
insert into Tb_Shoptype values(10,'n');
select * from tb_shoptype
commit;
insert into tb_shop values(1,'a1','温家宝',321,2,'11-1月-11');
insert into tb_shop values(2,'b2','胡景涛',123,3,'21-3月-11');
insert into tb_shop values(3,'c3','张三',434,4,'23-5月-10');
insert into tb_shop values(4,'d4','李岳',554,9,'22-2月-10');
insert into tb_shop values(5,'e5','张德',212,7,'12-1月-10');
insert into tb_shop values(6,'f6','东方不败',546,5,'4-3月-10');
insert into tb_shop values(7,'g7','李云龙',346,5,'16-3月-10');
insert into tb_shop values(8,'h8','楚云飞',653,3,'19-2月-10');
insert into tb_shop values(9,'i9','朱德',343,4,'5-10月-10');
insert into tb_shop values(10,'j10','毛爷爷',123,4,'9-12月-10');
insert into tb_shop values(11,'k11','周恩来',567,2,'6-11月-10');
insert into tb_shop values(12,'l12','花儿乐队',436,9,'4-11月-10');
insert into tb_shop values(13,'m13','刘烨',436,2,'11-10月-10');
insert into tb_shop values(14,'n14','谢娜',743,1,'11-3月-10');
insert into tb_shop values(15,'o15','思恋姐',432,2,'12-3月-10');
insert into tb_shop values(16,'p16','叶文',466,6,'6-5月-10');
insert into tb_shop values(17,'q17','俞灏明',567,7,'8-9月-10');
insert into tb_shop values(18,'r18','扯淡',132,6,'3-1月-11');
insert into tb_shop values(19,'s19','汉奸',44,6,'16-2月-11');
insert into tb_shop values(20,'t20','杀一个',342,2,'3-1月-11');
insert into tb_shop values(21,'u21','主公',34,6,'16-2月-11');
select * from tb_shop;函数
create or replace function fun_get_emp_info(
param_shopname varchar(20),
param_shoptype out varchar2(10)
)
return varchar2(20)
as
v_sjob tb_shop.shopid%type;
begin
select a.shopid,b.typename into param_sjob,param_shoptype from tb_shop a,tb_shoptype b
where a.shoptypeid=b.stid and upper(a.shopname)=upper(param_shopname);
return v_sjob;
exception
when no_data_found then
raise_application_error(-20000,'该员工不存在!');
end;调用方法
declare
v_shopname varchar(20):='张三';
v_shoptype varchar2(10);
v_sjob varchar2(20);
begin
v_sjob:=fun_get_emp_info(v_shopname,v_shoptype);
dbms_output.put_line('商品名称:'||v_shopname);
dbms_output.put_line('商品类型:'||v_shoptype);
dbms_output.put_line('商品编号:'||v_ejob);
end;
注:创建函数时就报错!
解决方案 »
- 图方便装了一个快捷版11G 现在像卸载 怎么办
- oracle如何设置动态的列名
- rman12004错误 ora-27001错误
- 删除和更新指定行,如limit(10,20)
- 应该如何使用insert into 语句在将tableAA的数据导入tableBB中! 源数据库为Oracle 目的数据库为 SQL Server
- 离线状态下无法启动监听,报错,请达人看看
- 如何从\4E2D\56FD\4EBA\6C11得到"中国人民"?
- SQL/PLUS里查看一个序列定义用什么?
- JDBC访问Oracle数据库的奇怪问题
- 大家都使用那个版本的oracle?
- 求PL/SQL设置表的主键和外键的步骤
- oracle 自定义函数里有个nvl()
create or replace function fun_get_emp_info(
param_shopname varchar2,
param_shoptype out varchar2
)
return varchar2
as
v_sjob tb_shop.shopid%type;
begin
select a.shopid,b.typename into v_sjob,param_shoptype
from tb_shop a,tb_shoptype b
where a.shoptypeid=b.stid and upper(a.shopname)=upper(param_shopname);
return v_sjob;
exception
when no_data_found then
dbms_output.put_line('该员工不存在!');
end;
where a.shoptypeid=b.stid and upper(a.shopname)=upper(param_shopname);
-- 函数不能带输出参数!
create or replace type empobj as object
( empno number(4),
ename varchar2(10),
sal number(7,2)
)
/create or replace type emptb is table of empobj
/create or replace function myemp(i_deptno number)
return emptb
is
Result emptb := emptb();
begin
result := emptb();
for i in (select empno, ename, sal from emp where deptno=i_deptno )
loop
result.extend;
result(result.count):=empobj(NULL,NULL,NULL);
result(result.count).empno := i.empno;
result(result.count).ename := i.ename;
result(result.count).sal := i.sal;
end loop;
return(result);
end;
/
select * from table(myemp(30));