oracle练习

最新推荐文章于 2024-09-25 11:06:23 发布

chunciqin6314

最新推荐文章于 2024-09-25 11:06:23 发布

阅读量161

点赞数

文章标签：数据库

原文链接：https://my.oschina.net/xiaoq6427/blog/224424

版权

2014-04-16

------要授予scott用户DBA的权限-------

ORDERS表字段为order_id, order_date, customer_id, ship_date, total;数据来自oe.orders，主键为order_id

CUSTOMER表字段为customer_id, cust_first_name, cust_last_name, date_of_birth, marital_status, cust_email，city_name数据来自oe.customers，主键为customer_id

ORDERS表与CUSTOMER为多对1关系，需要用建立主外键约束关系。

1.创建customer表并插入数据

create table customer as

select a.customer_id,a.cust_first_name,a.cust_last_name,a.date_of_birth,

a.marital_status,a.cust_email,a.cust_address.city city_name

from oe.customers a;

//添加主键

alter table customer add CONSTRAINT pk_cust primary key(customer_id);

2.创建orders表

create table orders(order_id number,

order_date TIMESTAMP ,customer_id number,

ship_date TIMESTAMP default sysdate,total number,

CONSTRAINT pk_orders PRIMARY key(order_id)，

CONSTRAINT fk_order_cust FOREIGN key(customer_id)

REFERENCES customer(customer_id)

);

3.插入数据

//向orders表插入数据，其中ship_date为order_date的日期的两个月后的日期。

insert into orders(order_id,order_date,ship_date,customer_id,total)

select order_id,order_date,add_months(order_date,2),customer_id,order_total

from oe.orders;

1.建立函数fun_valid_customer，根据输入的客户号，检查客户是否存在，如果客户存在，则返回TRUE，否则返回FALSE。

create or replace FUNCTION fun_vaild_customer(v_customer_id number)

return boolean

v_vaild number;

begin

select 1 into v_vaild from customer where customer_id=v_customer_id;

return true;

exception

when no_data_found then

return false;

end;

----------SELECT...INTO语句触发NO_DATA_FOUND---------------

2.建立函数fun_get_total，根据输入的订单号返回订单总价，然后调用该函数。当建立函数fun_get_total时，实现规则：

如果订单不存在，则显示自定义错误消息“ORA-20001:Please check correct order no.”

create or replace function fun_get_total(v_order_id number)

return number

v_total number;

begin

select total into v_total from orders where order_id=v_order_id;

return v_total;

exception

when no_data_found then

raise_application_error(-20001, 'Please check correct order number.');

end;

3.建立过程pro_add_order，根据输入的订单号，预定日期，客户号，交付日期和订单总价，为ORDERS表插入数据，然后调用过程。

当建立过程pro_add_order，实现规则：

使用fun_valid_customer检查客户号是否正确；如果正确，则插入数据，否则显示自定义错误消息“ORA-20001,Please check correct customer no.”

如果交付日期小于预定日期，则显示错误信息“ORA-20002:交付日期必须在预定日期之后。”

如果输入了已经存在的订单号，则显示自定义错误信息“ORA-20003：该订单已经存在。”

create or replace procedure pro_add_order(v_order_id number,v_order_date timestamp,

v_customer_id number,v_ship_date timestamp,v_total number)

begin

if FUN_VAILD_CUSTOMER(v_customer_id) then

insert into orders(order_id,order_date,customer_id,ship_date,total)

values(v_order_id,v_order_date,v_customer_id,v_ship_date,v_total);

else

raise_application_error(-20001, 'Please check correct customer number.');

end if;

if v_ship_date < v_order_date then

RAISE_APPLICATION_ERROR(-20002, 'the ship date must be after order date.');

end if;

exception

when dup_val_on_index then

RAISE_APPLICATION_ERROR(-20003, 'the order is already exist.');

end;

注意测试日期的格式：

V_ORDER_ID := 55;

V_ORDER_DATE := '17-AUG-07 05.34.12.234359000 AM';

V_CUSTOMER_ID := 150;

V_SHIP_DATE := '16-AUG-07 05.34.12.234359000 AM';

V_TOTAL := 55;

4.建立过程pro_delete_order，根据输入的订单号取消特定订单，然后调用该过程。实现规则：如果订单不存在，则显示错误信息“ORA-20001，请检查并输入正确的订单号。”

create or replace procedure pro_delete_order(v_order_id number)

begin

delete from orders where order_id=v_order_id;

if sql%notfound then

raise_application_error(-20001,

'Please check your order and input the right order number');

end if;

end;

——————————————————————————————————————————————————————————————————————

2014-04-17

习题1

建立用于操作ORDERS表的包pkg_orders，并调用该包的公用过程和函数。实现的规则：

1.定义私有函数fun_valid_cust，检查客户号是否在CUSTOMER表中存在；如果客户号存在，则返回TRUE，否则返回FALSE。

2.定义公用过程pro_add_order,根据输入的订单号，预定日期，客户编号，交付日期，订单总价为ORDERS表增加订单。如果订单存在，则显示自定义错误信息“ORA-20001:the order already exist,please check order no.”，如果客户号不存在，则显示自定义错误信息，“ORA-20002:the customer is not exist,please check customer no.”，如果交付日期小于预定日期，则显示自定义错误信息“ORA-20003，ship_date must be large than order_date .”

3.定义公用过程pro_update_shipdate，根据输入的订单号和交付日期，更新特定订单的交付日期。如果订单不存在，则显示自定义错误信息“ORA-20004，please input correct order no.”，如果交付日期小于预定日期，则显示自定义错误信息“ORA-20003，ship_date must be larger order_date .”

4.定义公用函数fun_get_info，根据输入的订单号返回客户名和订单总价。如果订单不存在，则显示自定义错误信息“ORA-20004，please input correct order no.”，

5.定义公用过程pro_delete_order，根据输入的订单号取消特定订单。如果订单不存在，则显示自定义错误信息“ORA-20004，please input correct order no.”

一.创建包头

create or replace package pkg_orders

procedure pro_add_order(v_order_id number,v_order_date timestamp,

v_customer_id number,v_ship_date timestamp,v_total number);

procedure pro_update_shipdate(v_order_id number,v_ship_date timestamp);

function fun_get_info(v_order_id number,v_total out number) return varchar2;

procedure pro_delete_order(v_order_id number);

end pkg_orders;

二.创建包体

create or replace package body pkg_orders

function fun_valid_cust(v_customer_id number)

return boolean

v_valid number;

begin

select 1 into v_valid from customer where customer_id=v_customer_id;

return true;

exception

when no_data_found then

return false;

end;

procedure pro_add_order(v_order_id number,v_order_date timestamp,

v_customer_id number,v_ship_date timestamp,v_total number)

begin

insert into orders(order_id,order_date,customer_id,ship_date,total)

values(v_order_id,v_order_date,v_customer_id,v_ship_date,v_total);

if not fun_valid_cust(v_customer_id) then

raise_application_error(-20002,'the customer is not exist,please check customer no.');

end if;

exception

when dup_val_on_index then

raise_application_error(-20001,'the order already exist,please check order no.');

if v_ship_date < v_order_date then

raise_application_error(-20003,'ship date must be larger than order date.');

end if;

end;

procedure pro_update_shipdate(v_order_id number,v_ship_date timestamp)

v_order_date orders.order_date%type;

begin

update orders set ship_date=v_ship_date where order_id=v_order_id;

if sql%notfound then

raise_application_error(-20004,'please input correct order no.');

end if;

if v_ship_date < v_order_date then

raise_application_error(-20003,'ship date must be larger than order date.');

end if;

end;

function fun_get_info(v_order_id number,v_total out number)

return varchar2

v_customer_name customer.cust_last_name%type;

begin

select b.cust_last_name,a.total into v_customer_name,v_total

from orders a join customer b on a.customer_id=b.customer_id

where a.order_id=v_order_id;

return v_customer_name;

end;

procedure pro_delete_order(v_order_id number)

begin

delete from orders where order_id=v_order_id;

if sql%notfound then

raise_application_error(-20004,'please input correct order no.');

end if;

end;

end pkg_orders;

**********************************************************

习题2

建立操作CUSTOMER表的包pkg_customer，然后调用其公用的过程和函数。实现规则：

1.定义重载过程pro_update_city，分别根据输入的客户号或客户名更新客户所在城市，如果客户号或者客户名不存在，如果客户号不存在，则显示自定义错误信息，“ORA-20002:the customer is not exist,please check customer no or customer name.”

2.定义重载函数fun_get_city,分别根据输入的客户号或客户名返回客户所在城市。如果客户号或者客户名不存在，如果客户号不存在，则显示自定义错误信息，“ORA-20002:the customer is not exist,please check customer no or customer name.”

一.创建包头

create or replace package pkg_customer

procedure pro_update_city(v_customer_id number,v_city_name varchar2);

procedure pro_update_city(v_cust_last_name varchar2,v_city_name varchar2);

function fun_get_city(v_customer_id number,v_city_name varchar2) return varchar2;

function fun_get_city(v_cust_last_name varchar2,v_city_name varchar2) return varchar2;

end pkg_customer;

二.创建包体

create or replace package body pkg_customer

procedure pro_update_city(v_customer_id number,v_city_name varchar2)

begin

update customer set city_name=v_city_name where customer_id=v_customer_id;