oracle总结8

建立用于操作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 large order_date .”
3.定义公用过程pro_update_shipdate，根据输入的订单号和交付日期，更新特定订单的交付日期。如果订单不存在，则显示自定义错误信息“ORA-20004，please input correct order no.”，如果交付日期小于预定日期，则显示自定义错误信息“ORA-20003，ship_date must large order_date .”
4.定义公用函数fun_get_info，根据输入的订单号返回客户名和订单总价。如果订单不存在，则显示自定义错误信息“ORA-20004，please input correct order no.”，
5.定义公用过程pro_delete_order，根据输入的订单号取消特定订单。如果订单不存在，则显示自定义错误信息“ORA-20004，please input correct order no.”，
 
 
-----
表设计字段
 
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关系，需要用建立主外键约束关系。
 
======解锁oe
SQL> alter user oe account unlock;
 
 
SQL> alter user oe identified by oe;
 
 
SQL> grant dba to oe;
 
---table:orders
create table orders(order_id number primary key
, order_date date
, customer_id number
, ship_date date  
, total number);
 
 
---table: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;
 
---add primary and foreign key
alter table customer add constraint pk_customer primary key(customer_id);
 
alter table orders add constraint fk_orders foreign key(customer_id)
references customer(customer_id);
 
---向orders表中插入数据
INSERT INTO ORDERS
  (
    ORDER_ID,
    ORDER_DATE,
    CUSTOMER_ID,
    SHIP_DATE,
    TOTAL
  )
select b.order_id
,b.order_date
,b.customer_id  
,add_months(b.order_date,2)
,b.order_total
from oe.orders b;
 
---------------------------------------------
---package header:
 
create or replace package pkg_orders is
 
procedure pro_add_order(v_order_id number,
                        v_order_date date,
                        v_customer_id number,
                        v_ship_date date,
                        v_total number);
 
procedure pro_update_shipdate(v_order_id number,v_ship_date date);
 
function fun_get_info(v_order_id number) return varchar2;
 
 
procedure pro_delete_order(v_order_id number);
 
 
end pkg_orders;
 
---------------------------------------------
 
 
--- body:pkg_orders
create or replace package body pkg_orders is
 
function fun_valid_cust(v_customer_id number)  
return boolean is
v_tmp number;
begin
select 1 into v_tmp 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 date,
                        v_customer_id number,
                        v_ship_date date,
                        v_total number)
    is
    begin  
    if not fun_valid_cust(v_customer_id) then
    raise_application_error(-20002, 'the customer is not exist,please check customer no.');
    end if;
     
    if v_ship_date < v_order_date then  
    raise_application_error(-20003,'ship_date must large order_date');
    end if;
    insert into orders values(v_order_id
    ,v_order_date
    ,v_customer_id
    ,v_ship_date
    ,v_total);
    exception when dup_val_on_index then
    raise_application_error(-20001,'the order already exist,please check order no');
     
    end;
                         
 
procedure pro_update_shipdate(v_order_id number,v_ship_date date)
is  
v_order_date date;
 
begin  
update orders set ship_date=v_ship_date  
where order_id = v_order_id
returning order_date into v_order_date;
 
if sql%notfound then
raise_application_error(-20004,'please input correct order no');
end if;
 
if v_ship_date  < v_order_date then
rollback;
raise_application_error(-20003,'ship_date must large order_date');
end if;
 
end;
 
function fun_get_info(v_order_id number) return varchar2
 
is
v_info varchar2(100);
begin
select b.cust_last_name||' '||b.cust_first_name || ':' || a.total into v_info
from orders  a
join customer b on a.customer_id = b.customer_id
where a.order_id = v_order_id;
return v_info;
exception  
when no_data_found then  
 
raise_application_error(-20004,'please input correct order no');
 
end;
 
 
 
procedure pro_delete_order(v_order_id number)
is
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;
 
 
---------------------------------------------
 
 
 
 
=============
学生课程案例
 
t_student(student_id number not null primary key
,student_name varchar2(50)
,phone varchar2(15));
 
 
t_student_teacher(student_id number not null
,teacher_id number not null
,constraint pk_student_teacher   primary key(student_id,teacher_id));
 
alter table t_student_teacher add constraint fk_student1 foreign key(student_id) references t_student(student_id);
 
alter table t_student_teacher add constraint fk_teacher_id foreign key(teacher_id) references t_teacher(teacher_id);
 
t_teacher(teacher_id number not null primary key
,teacher_name varchar2(50)
,phone varchar2(15));
 
 
t_student_course(student_id number not null
,course_id number not null
,grade number(5,2)
,constraint  pk_student_course_id  primary key(student_id,course_id));
---编写外键约束
 
 
alter table t_student_course add constraint fk_student_courser_id foreign key(student_id) references t_student(student_id);
 
 
alter table t_student_course add constraint fk_student_courser2_id foreign key(course_id) references t_course(course_id );
 
t_course(course_id number not null primary key,
course_name varchar2(50));