为一个小孩写的oracle数据库简单SQL语句

本文主要是介绍为一个小孩写的oracle数据库简单SQL语句，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

由于当时没有环境，然后装了个ORACLE12.1版本程序，安装过程中碰到以下问题：

oracle12c安装失败【INS-30131】执行安装程序验证所需要的初始设置失败

使用以下方法解决：

针对客户端安装，在cmd中执行命令：
前面加实际路径setup.exe -ignorePrereq -J"-Doracle.install.client.validate.clientSupportedOSCheck=false" 
针对服务端安装，在cmd中执行命令：
前面加实际路径setup.exe -ignorePrereq -J"-Doracle.install.db.validate.supportedOSCheck=false"

这个来自于：

https://blog.csdn.net/killvoon/article/details/51821928

所有的SQL语句如下：

/*=======================以下需要以DBA的身份重新建立一个用户，以新用户去执行，================*/
/*==========否则触发器和存储过程是不会以DBA的角色创建=======================================*/

drop table vcd_borrow_record cascade constraints;

drop table vcd_borrow_type cascade constraints;

drop table vcd_info cascade constraints;

drop table vcd_sale_record cascade constraints;

drop table vcd_type cascade constraints;

/*==============================================================*/
/* Table: vcd_borrow_record                                   */
/*==============================================================*/
create table vcd_borrow_record 
(
   ID                   NUMBER(20)           default NULL not null,
   vcd_id             NUMBER(20),
   record_date        DATE,
   ctype               INT                  default NULL,
   vcdnum             INT,
   constraint PK_VCD_BORROW_RECORD primary key (ID)
);

comment on table vcd_borrow_record is
'vcd借还记录表';

comment on column vcd_borrow_record.ctype is
'0：借；1还';

/*==============================================================*/
/* Table: vcd_borrow_type                                     */
/*==============================================================*/
create table vcd_borrow_type 
(
   ID                   INT                  default NULL not null,
   typename           varchar(50),
   constraint PK_VCD_BORROW_TYPE primary key (ID)
);

comment on table vcd_borrow_type is
'vcd借还类型表';

/*==============================================================*/
/* Table: vcd_info                                            */
/*==============================================================*/
create table vcd_info 
(
   ID                   NUMBER(20)           default NULL not null,
   ctype               int,
   vcdname            varchar(255),
   price              DEC(12, 2),
   saleprice          DEC(12, 2)           default NULL,
   vcdnum             NUMBER(20),
   constraint PK_VCD_INFO primary key (ID)
);

comment on table vcd_info is
'vcd信息表';

/*==============================================================*/
/* Table: vcd_sale_record                                     */
/*==============================================================*/
create table vcd_sale_record 
(
   ID                   NUMBER(20)           default NULL not null,
   vcd_id             NUMBER(20),
   record_date        DATE,
   salenum            INT,
   saleprice          DEC(12, 2),
   constraint PK_VCD_SALE_RECORD primary key (ID)
);

comment on table vcd_sale_record is
'vcd销售记录表';

/*==============================================================*/
/* Table: vcd_type                                            */
/*==============================================================*/
create table vcd_type 
(
   ID                   INT                  default NULL not null,
   typename           varchar(50),
   constraint PK_VCD_TYPE primary key (ID)
);

comment on table vcd_type is
'vcd分类表';

/*=======================以下是触发器，挨个执行=======================================*/

create sequence vcd_info_seq
increment by 1 
start with 1
nomaxvalue
nominvalue
nocache;

create or replace trigger tr_vcd_info
before insert on "vcd_info"
for each row
begin
select vcd_info_seq.nextval into :new.id from dual;
end;

create sequence vcd_borrow_seq
increment by 1 
start with 1
nomaxvalue
nominvalue
nocache;

create or replace trigger tr_vcd_borrow
before insert on "vcd_borrow_record"
for each row
begin
select vcd_borrow_seq.nextval into :new.id from dual;
end;

create sequence vcd_sale_seq
increment by 1 
start with 1
nomaxvalue
nominvalue
nocache;

create or replace trigger tr_vcd_sale
before insert on "vcd_sale_record"
for each row
begin
select vcd_sale_seq.nextval into :new.id from dual;
end;

/*=======================以下是触发器，挨个执行=======================================*/

CREATE OR REPLACE TRIGGER Trigger_borrow 
BEFORE INSERT ON vcd_borrow_record  
FOR EACH ROW 
WHEN(NEW.ctype=0) 
DECLARE
v_temp INT;
BEGIN
  SELECT COUNT(*) INTO v_temp FROM vcd_info WHERE id = :NEW.vcd_id;
 if v_temp >0 then
   update vcd_info set vcdnum=vcdnum-1 where id= :NEW.vcd_id;
  else
    raise_application_error(-20001,'error');
  end if;
END;

CREATE OR REPLACE TRIGGER Trigger_send
BEFORE INSERT  ON vcd_borrow_record 
FOR EACH ROW 
WHEN(NEW.ctype=1) 
DECLARE 
v_temp INT;
BEGIN 
 
   update vcd_info set vcdnum=vcdnum+1 where id= :NEW.vcd_id;

END;

CREATE OR REPLACE TRIGGER Trigger_sale 
BEFORE INSERT  ON vcd_sale_record 
FOR EACH ROW 
DECLARE
v_temp INT;
BEGIN
  SELECT COUNT(*) INTO v_temp FROM vcd_info WHERE id= :NEW.vcd_id;
 if v_temp >0 then
   update vcd_info set vcdnum=vcdnum-1 where id= :NEW.vcd_id;
else
raise_application_error(-20001,'error');
end if;
END;

/*=======================以下是存储过程，挨个执行=======================================*/

create or replace procedure p_sale_num(vcd_id_in    in number, --某个VCD序号
                                      dt_in_beg     in date, --开始日期
                                      dt_in_end     in date, --结束日期
                                      sale_o_num    out int --成功/销售数量
                                      )
as
i_count      int; --数
begin
SELECT sum(salenum) INTO i_count FROM vcd_sale_record WHERE vcd_id= vcd_id_in and record_date between dt_in_beg and dt_in_end ;
sale_o_num := i_count ;
end;

--借的统计数据
create or replace procedure p_borrow_num(vcd_id_in    in number, --某个VCD序号
                                      dt_in_beg     in date, --开始日期
                                      dt_in_end     in date, --结束日期
                                      o_num    out int --数量
                                      )
as
i_count      int; --数
begin
SELECT sum(vcdnum) INTO i_count FROM vcd_borrow_record WHERE vcd_id= vcd_id_in and ctype=0 and record_date between dt_in_beg and dt_in_end ;
o_num := i_count ;
end;

--还的统计数据
create or replace procedure p_send_num(vcd_id_in    in number, --某个VCD序号
                                      dt_in_beg     in date, --开始日期
                                      dt_in_end     in date, --结束日期
                                      o_num    out int --数量
                                      )
as
i_count      int; --数
begin
SELECT sum(vcdnum) INTO i_count FROM vcd_borrow_record WHERE vcd_id= vcd_id_in and ctype=1 and record_date between dt_in_beg and dt_in_end ;
o_num := i_count ;
end;

/*=======================以下是视图，挨个执行=======================================*/

CREATE OR REPLACE VIEW v_sum_vcd_as_type AS
SELECT  vt.typename,sum(vi.vcdnum) as vcdtotal from vcd_info vi, vcd_type vt where vi.ctype=vt.id
 group by vt.typename;
 
 
 create or replace function f_total_by_type(f_type in number) return int
as
total_count int;
begin
select count(1) into  total_count  from vcd_info where ctype=f_type;
return total_count;
end;

--f_type的值是：0或1
create or replace function f_total_borrow(f_type in number) return int
as
total_count int;
begin
select count(1) into  total_count  from vcd_borrow_record where ctype=f_type;
return total_count;
end;

declare
total_count int;
begin
total_count:=f_total_by_type(10);
DBMS_OUTPUT.PUT_LINE('总数：'||total_count);
end;

--时间段内销售记录数
create or replace function f_total_sale(
              dt_in_beg     in date, --开始日期
              dt_in_end     in date --结束日期
                  ) return int
as
total_count int;
begin
select count(1) into  total_count  FROM vcd_sale_record WHERE record_date between dt_in_beg and dt_in_end ;
return total_count;
end;

--时间段内销售金额数
create or replace function f_total_sale_price(vcd_id_in    in number, --某个VCD序号
              dt_in_beg     in date, --开始日期
              dt_in_end     in date --结束日期
                  ) return number
as
total_count number;
begin
select sum(salenum*saleprice) into  total_count  FROM vcd_sale_record WHERE vcd_id= vcd_id_in and record_date between dt_in_beg and dt_in_end ;
return total_count;
end;

INSERT INTO vcd_type(id,typename) VALUES(1,'教学');
INSERT INTO vcd_type(id,typename) VALUES(2,'影视');
INSERT INTO vcd_type(id,typename) VALUES(3,'娱乐');

INSERT INTO vcd_info(ctype,vcdname,price,saleprice,vcdnum) VALUES(1,'计算机教学',20,20,200);
INSERT INTO vcd_info(ctype,vcdname,price,saleprice,vcdnum) VALUES(1,'数据结构教学',25,30,250);
INSERT INTO vcd_info(ctype,vcdname,price,saleprice,vcdnum) VALUES(2,'魔游纪2：烈风峡谷',15,20,300);

SELECT * FROM vcd_info

create or replace procedure p_sale_pricenum(vcd_id_in    in number, --某个VCD序号
                                      dt_in_beg     in date, --开始日期
                                      dt_in_end     in date, --结束日期
                                      sale_o_num    out number --成功/销售数量
                                      )
as
i_count      number; --数
begin
SELECT sum(salenum*saleprice) INTO i_count FROM vcd_sale_record WHERE vcd_id= vcd_id_in and record_date between dt_in_beg and dt_in_end ;
sale_o_num := i_count ;
end;

create or replace procedure p_sale_price(vcd_id_in    in number, --某个VCD序号
                                      dt_in_beg     in date, --开始日期
                                      dt_in_end     in date, --结束日期
                                      sale_o_num    out number --成功/销售数量
                                      )
as
i_count      number; --数
begin
SELECT sum(saleprice) INTO i_count FROM vcd_sale_record WHERE vcd_id= vcd_id_in and record_date between dt_in_beg and dt_in_end ;
sale_o_num := i_count ;
end;

--时间段内销售金额数
create or replace function f_total_sale_price_num(vcd_id_in    in number, --某个VCD序号
              dt_in_beg     in date, --开始日期
              dt_in_end     in date --结束日期
                  ) return number
as
total_count number;
begin
select sum(salenum) into  total_count  FROM vcd_sale_record WHERE vcd_id= vcd_id_in and record_date between dt_in_beg and dt_in_end ;
return total_count;
end;

--f_type的值是：0或1
create or replace function f_total_borrow(f_type in number) return int
as
total_count int;
begin
select count(1) into  total_count  from vcd_borrow_record where ctype=f_type;
return total_count;
end;

----------------------------------------------
---1--p_send_num
declare 
  i_count  int;
begin
  p_send_num(1,to_date('2019-01-16 14:22:20','yyyy-mm-dd hh24:mi:ss'),to_date('2019-02-16 14:22:20','yyyy-mm-dd hh24:mi:ss'),i_count);
  dbms_output.put_line('返回数据:' || i_count);
end;
--2--p_sale_pricenum
declare 
  i_count  number;
begin
  p_sale_pricenum(1,to_date('2019-01-16 14:22:20','yyyy-mm-dd hh24:mi:ss'),to_date('2019-02-16 14:22:20','yyyy-mm-dd hh24:mi:ss'),i_count);
  dbms_output.put_line('返回数据:' || i_count);
end;
--3--p_sale_price
declare 
  i_count  number;
begin
  p_sale_price(1,to_date('2019-01-16 14:22:20','yyyy-mm-dd hh24:mi:ss'),to_date('2019-02-16 14:22:20','yyyy-mm-dd hh24:mi:ss'),i_count);
  dbms_output.put_line('返回数据:' || i_count);
end;
--4--p_borrow_num
declare 
  i_count  int;
begin
  p_borrow_num(1,to_date('2019-01-16 14:22:20','yyyy-mm-dd hh24:mi:ss'),to_date('2019-02-16 14:22:20','yyyy-mm-dd hh24:mi:ss'),i_count);
  dbms_output.put_line('返回数据:' || i_count);
end;
--5--p_send_num
declare 
  i_count  int;
begin
  p_send_num(1,to_date('2019-01-16 14:22:20','yyyy-mm-dd hh24:mi:ss'),to_date('2019-02-16 14:22:20','yyyy-mm-dd hh24:mi:ss'),i_count);
  dbms_output.put_line('返回数据:' || i_count);
end;

--1--f_total_sale
declare 
  i_count  int;
begin
  i_count :=f_total_sale(to_date('2019-01-16 14:22:20','yyyy-mm-dd hh24:mi:ss'),to_date('2019-02-16 14:22:20','yyyy-mm-dd hh24:mi:ss'));
  dbms_output.put_line('返回数据:' || i_count);
end;

--2--f_total_sale_price
declare 
  i_count  number;
begin
  i_count :=f_total_sale_price(1,to_date('2019-01-16 14:22:20','yyyy-mm-dd hh24:mi:ss'),to_date('2019-02-16 14:22:20','yyyy-mm-dd hh24:mi:ss'));
  dbms_output.put_line('返回数据:' || i_count);
end;

--3--f_total_by_type
declare 
  i_count  int;
begin
  i_count :=f_total_by_type(1);
  dbms_output.put_line('返回数据:' || i_count);
end;

--4--f_total_sale_price_num
declare 
  i_count  number;
begin
  i_count :=f_total_sale_price_num(1,to_date('2019-01-16 14:22:20','yyyy-mm-dd hh24:mi:ss'),to_date('2019-02-16 14:22:20','yyyy-mm-dd hh24:mi:ss'));
  dbms_output.put_line('返回数据:' || i_count);
end;

--5--f_total_borrow
declare 
  i_count  int;
begin
  i_count :=f_total_borrow(1);
  dbms_output.put_line('返回数据:' || i_count);
end;
 

这篇关于为一个小孩写的oracle数据库简单SQL语句的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---