ORDERS表字段为order_id, order_date, customer_id, ship_date, total;数据来自oe.orders，主键为order_id
CUSTOMERS表字段为customer_id, cust_first_name, cust_last_name, date_of_birth, marital_status, cust_email，city_name数据来自oe.customers，主键为customer_id
ORDERS表与CUSTOMERS为多对1关系，需要用建立朱外键约束关系。
分析：因为orders外键依赖于customers的主键所以建表和插入数据时以customers为先
//创建customers表
create table customers
(CUSTOMER_ID NUMBER(6) NOT NULL primary key ,
CUST_FIRST_NAME VARCHAR2(20) NOT NULL,
CUST_LAST_NAME VARCHAR2(20) NOT NULL ,
DATE_OF_BIRTH date ,
MARITAL_STATUS VARCHAR2(20),
CUST_EMAIL VARCHAR2(30),
city_name varchar2(50) default 'luoyang');
//创建orders表
create table orders
(order_id number(12) not null,
order_date date ,
customer_id number(6) not null ,
ship_date date not null ,
total number(8,2),
constraint pk_order_id primary key(order_id),
constraint fk_customer_id foreign key(customer_id) REFERENCES customers(customer_id));
grant dba to scott;
//向customers中插入数据
insert into customers(CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME ,DATE_OF_BIRTH, MARITAL_STATUS ,CUST_EMAIL) select CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME ,DATE_OF_BIRTH, MARITAL_STATUS ,CUST_EMAIL from oe.customers;
//向orders中插入数据
insert into orders(order_id,order_date ,customer_id ,ship_date,total)
select order_id,order_date ,customer_id ,add_months(order_date,2),order_total from oe.orders;
1.建立函数fun_valid_customer，根据输入的客户号，检查客户是否存在，如果客户存在，则返回TRUE，否则返回FALSE。
方法一：
create or replace function fun_valid_customer (v_customer_id number)
return varchar2
is
v_return varchar2(50);
v_CUST_FIRST_NAME varchar2(50);
begin
update customers set customer_id=v_customer_id where customer_id=v_customer_id;
if sql%found then
v_return:='true';
else
v_return:='false';
end if;
return v_return;
end;
方法二：
create or replace function fun_valid_customer (v_customer_id number)
return varchar2
is
v_return varchar2(50);
v_CUST_FIRST_NAME varchar2(50);
begin
select CUST_FIRST_NAME into v_CUST_FIRST_NAME from customers where customer_id=v_customer_id;
if sql%found then
v_return:='true';
return v_return;
end if;
exception
when no_data_found then
return 'false';
end;
方法三：
create or replace function fun_valid_customer (v_customer_id number)
return boolean
is
v_tmp number;
begin
select 1 into v_tmp from customers where customer_id=v_customer_id;
return true;
exception
when no_data_found then
return false;
end;
本例选择方法三
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
is
v_total number(8,2);
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 no.');
end;
3.建立过程pro_add_order，根据输入的订单号，预定日期，客户号，交付日期和订单总价，为ORDERS表插入数据，然后调用过程。当建立过程pro_add_order，实现规则：
使用pro_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 date ,v_customer_id number,v_ship_date date,v_total number)
is
v_count number;
begin
if not fun_valid_customer(v_customer_id) then
raise_application_error(-20001,'the customer not exist,Please check correct customer no.');
end if;
if v_ship_date < v_order_date then
raise_application_error(-20002,'ship_date must large order_date .');
end if;
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 );
exception
when dup_val_on_index then
raise_application_error(-20003,'the order already exist,please check order no.');
end;
4.建立过程pro_delete_order，根据输入的订单号取消特定订单，然后调用该过程。实现规则：如果定的不存在，则显示错误信息“ORA-20001，请检查并输入正确的订单号。”
create or replace 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(-20001,'Please check and enter the correct order id' );
end if;
commit;
end;
习题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 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.”，
############################################################################
建立包头
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,v_customer_name out varchar2) return number;
procedure pro_delete_order(v_order_id number);
end 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 customers 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_customer(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(order_id ,order_date ,customer_id,ship_date,total ) 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
select order_date into v_order_date from orders where order_id=v_order_id;
if v_ship_date < v_order_date then
raise_application_error(-20003,'ship_date must large order_date');
end if;
exception
when no_data_found then
raise_application_error(-20004,'please input correct order no.');
update orders set ship_date=v_ship_date where order_id=v_order_id;
end;
function fun_get_info(v_order_id number,v_customer_name out varchar2)
return number
is
v_total number(8,2);
begin
select b.CUST_FIRST_NAME ||' '|| b.CUST_LAST_NAME,a.total into v_customer_name,v_total from orders a join customers b on a.customer_id=b.customer_id where a.order_id=v_order_id ;
return v_total;
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;
commit;
end;
end pkg_orders;
##################################################################################################################
习题2
建立操作CUSTOMERS表的包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
is
procedure pro_update_city(v_cusno number,v_city varchar2);
procedure pro_update_city(v_cusname varchar2,v_city varchar2);
function fun_get_city(v_cusno number) return varchar2;
function fun_get_city(v_cusname varchar2) return varchar2;
end pkg_customer;
########建立包体
create or replace package body pkg_customer is
procedure pro_update_city(v_cusno number,v_city varchar2)
is
begin
update customers set city_name=v_city where customer_id=v_cusno;
if sql%notfound then
raise_application_error(-20002,'the customer is not exist,please check customer no.');
end if;
end;
procedure pro_update_city(v_cusname varchar2,v_city varchar2)
is
begin
update customers set city_name=v_city where upper(CUST_FIRST_NAME||' '||CUST_LAST_NAME) = upper(v_cusname) ;
if sql%notfound then
raise_application_error(-20002,'the customer is not exist,please check customer name.');
end if;
end;
function fun_get_city(v_cusno number)
return varchar2
is
v_city varchar2(50);
begin
select city_name into v_city from customers where customer_id=v_cusno;
return v_city;
exception
when no_data_found then
raise_application_error(-20002,'the customer is not exist,please check customer no.');
end;
function fun_get_city(v_cusname varchar2)
return varchar2
is
v_city varchar2(50);
begin
select city_name into v_city from customers where upper(CUST_FIRST_NAME||' '||CUST_LAST_NAME) = upper(v_cusname);
return v_city;
exception
when no_data_found then
raise_application_error(-20002,'the customer is not exist,please check customer name.');
end;
end pkg_customer;
##############################
在orders表中针对INSERT操作建立触发器tr_add_order,规则如下：
1.如果在星期六，星期天增加订单，显示错误信息“ORA-20001，只能在工作日增加订单”
2.如果在9:00-18:00之外的其他时间增加订单，则显示错误信息，“ORA-20002，只能在工作日增加订单”
create or replace trigger tr_add_order
before insert on orders
begin
if to_char(sysdate,'DY','nls_date_language=AMERICAN') IN ('SAT','SUN') THEN
RAISE_APPLICATION_ERROR(-20001,'只能在只能在周一～周五增加订单增加订单');
end if;
if to_char(sysdate,'HH24MI') not between 0900 and 1800 then
RAISE_APPLICATION_ERROR(-20002,'只能在9:00~18:00增加订单');
end if;
end;
#############################################
1.动态修改特定部门的工资，并返回修改后的雇员名和工资---scott---emp
//使用游标
create or replace procedure pro_change_sal(v_deptno emp.deptno%type,v_percent number)
is
type cursor_emp_type is ref cursor;
emp_record emp%rowtype;
emp_cursor cursor_emp_type;
v_sql varchar2(100);
begin
update emp set sal=sal*(1+v_percent/100) where deptno=v_deptno;
v_sql:='select * from emp where deptno=：v_deptno';
open emp_cursor for v_sql using v_deptno;
loop
fetch emp_cursor into emp_record;
exit when emp_cursor%notfound ;
dbms_output.put_line('ename: '|| emp_record.ename ||',salary: ' ||emp_record.sal);
end loop;
close emp_cursor;
end;
####################
//批量处理
set serveroutput on;
create or replace procedure pro_change_sal(v_deptno emp.deptno%type,v_percent number)
is
type sal_table_type is table of emp.sal%type index by binary_integer;
type ename_table_type is table of emp.ename%type index by binary_integer;
ename_table ename_table_type;
sal_table sal_table_type;
v_sql varchar2(100);
begin
update emp set sal=sal*(1+v_percent/100) where deptno=v_deptno;
v_sql:='select ename,sal from emp where deptno=:v_deptno';####必须使用:v_deptno加冒号
execute immediate v_sql bulk collect into ename_table,sal_table using v_deptno;
for i in 1..ename_table.count loop
dbms_output.put_line('ename: '|| ename_table(i) ||',salary: ' ||sal_table(i));
end loop;
end;
2.根据输入的多个订单号更新交付日期为当前日期，并返回每隔订单的对应的客户编号.
create or replace procedure pro_change_ship_date(id1 number,id2 number,id3 number)
is
type customer_id_table_type is table of number;
type ordre_id_table_type is table of number;
customer_id_table customer_id_table_type;
ordre_id_table ordre_id_table_type;
v_sql varchar2(100);
begin
update orders set ship_date=sysdate where order_id in (id1 ,id2 ,id3);
v_sql :='select customer_id, order_id from orders where order_id in ( :id1 ,:id2 ,:id3)';
execute immediate v_sql bulk collect into customer_id_table,ordre_id_table using id1 ,id2 ,id3;
for i in 1.. customer_id_table.count loop
dbms_output.put_line('ordre_id '||ordre_id_table(i)||' '||'customer_id '||customer_id_table(i));
end loop;
end;
exec pro_change_ship_date(2381,2354,null);
转载于:https://my.oschina.net/u/1458120/blog/225923
扫一扫
610
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
