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

xiaoxiao2024-10-17 4

由于当时没有环境，然后装了个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;

---------------------------------------------- ---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;

最新回复(0)