-----创建序列
create sequence book_id
INCREMENT BY 1 -- 每次加几个
START WITH 001 -- 从1开始计数
NOMAXVALUE -- 不设置最大值
NOCYCLE -- 一直累加，不循环
CACHE 10;
------创建books表
create table books(
books_id varchar2(1000),
books_name varchar2(100),
price number,
qty number,
pub varchar2(200)
);
------修改books表的字段
alter table books modify(books_id number)
-------------往books表中插入数据
insert into books values(book_id.nextval,'中国文学1',39,12,'人民文学');
insert into books values(book_id.nextval,'中国文学2',30,32,'人民文学');
insert into books values(book_id.nextval,'中国文学3',59,22,'清华大学');
insert into books values(book_id.nextval,'中国文学4',33,52,'清华大学');
insert into books values(book_id.nextval,'中国文学5',99,62,'电子工业');
-----------跟新books中的信息
update books set price=100 where books_id=1
----------按出版社分组查询每个出版社金额的情况
select pub,sum(price*qty) from books group by pub
----------按出版社、书籍名称分组查询每个出版社金额的情况
select pub,books_name,sum(price*qty) from books group by pub,books_name
----------按出版社、书籍名称分组查询每个出版社金额的情况 >50
select pub,books_name,sum(price*qty) from books group by pub,books_name having sum(price)>50
----------查询相同出版社的记录数
select pub,count(pub) from books group by pub having count(pub) >1
-----标的内链接
select eid ,ename,six,name from e,d where a.id=d.id
select eid ,ename,six,name from e join d on a.id=d.id
-----做外连接
select eid ,ename,six,name from e join d on a.id=d.id(+)
----右外连接
select eid ,ename,six,name from e join d on a.id(+)=d.id
----无关子查询
select * from e where id in (select eid from d)
----相关子查询
select * from e where id in (select eid from d where id=d.id and id='003')
select * from e where id not in (select eid from d where id=d.id and id='003')
-----存在则显示
select * from e where exists(select id from d where id=d.id)
-----不存在则显示
select * from e where not exists(select id from d where id=d.id)
-----------------------PLSQL基本语法----------------------------------------------------------------------------------------------------------
set serveroutput on size 10000
declare
x varchar2(100);
begin
x:='This is ....';
DBMS_OUTPUT.PUT_LINE('x value is '||x);
end;
-----if elsif else
declare
a number;
b varchar2(10);
begin
a := 2;
if a = 1 then
b := 'A';
elsif a = 2 then
b := 'B';
else
b := 'C';
end if;
DBMS_OUTPUT.put_line(b);
end;
----------------case
declare
a number;
b varchar2(10);
begin
a := 2;
case
when a = 1 then
b := 'A';
when a = 2 then
b := 'B';
end case;
DBMS_OUTPUT.put_line(b);
end;
-------------------------PLSQL 循环--------------------------------------------
------loop
declare
x number;
begin
x := 1;
loop
x := x + 1;
if x > 3 then
exit;
end if;
DBMS_OUTPUT.put_line(x);
end loop;
DBMS_OUTPUT.put_line(x);
end;
--------------while
declare
x number;
begin
x := 1;
while x > 3 loop
x := +1;
DBMS_OUTPUT.put_line(x);
end loop;
DBMS_OUTPUT.put_line(x);
end;
-------for
begin
for x in 1 .. 10 loop------从小到大
DBMS_OUTPUT.put_line(x);
end loop;
DBMS_OUTPUT.put_line('end of for loop');
end;
begin
for x in reverse 1 .. 10 loop------从大到小
DBMS_OUTPUT.put_line(x);
end loop;
DBMS_OUTPUT.put_line('end of for loop');
end;
----------------做标签
declare
x number;
begin
x := 0;
<<repeat_loop>>
x := x + 1;
DBMS_OUTPUT.put_line(x);
if x < 3 then
goto repeat_loop;
end if;
end;
----------------exception 处理-------------------------------------
declare
test varchar2(100);
begin
select books_name into test from books where books_id = 1;
DBMS_OUTPUT.put_line(test);
exception
when no_data_found then
DBMS_OUTPUT.put_line('没有找到数据');
end;
-----------自定义异常
declare
test varchar2(100);
e exception;
begin
select books_name into test from books where books_id = 1;
if test <> '中国文学1' then
raise e;
end if;
DBMS_OUTPUT.put_line(test);
exception
when e then
DBMS_OUTPUT.put_line('不是需要的书籍名称');
end;
-----------------------记录的声明-------------------------------
declare
type myrecord is record(
bname varchar2(100),
bpub varchar2(100)
);
real_record myrecord;
begin
select books_name,pub into real_record from books where books_id=1;
DBMS_OUTPUT.put_line(real_record.bname || real_record.bpub);
end;
declare
type myrecord is record(
bname books.books_id%type, ---------------声明的字段和表中的字段类型一样
bpub varchar2(100));
real_record myrecord;
begin
select books_name, pub into real_record from books where books_id = 1;
DBMS_OUTPUT.put_line(real_record.bname || real_record.bpub);
end;
declare
myrecord books%rowtype;
begin
select * into myrecord from books where books_id = 1;
DBMS_OUTPUT.put_line(myrecord.books_name || myrecord.pub);
end;
-----------------------游标--------------------
----显示游标的使用方法
declare
cursor mycursor is
select * from books;
myrecord books%rowtype;
begin
open mycursor;
fetch mycursor
into myrecord;
while mycursor%found loop
DBMS_OUTPUT.put_line(myrecord.books_name || myrecord.pub);
fetch mycursor
into myrecord;
end loop;
close mycursor;
end;
------带参数的游标
declare
cursor mycursor(bookid number) is
select * from books where books.books_id = bookid;
myrecord books%rowtype;
begin
open mycursor(1);
fetch mycursor
into myrecord;
while mycursor%found loop
DBMS_OUTPUT.put_line(myrecord.books_name || myrecord.pub);
fetch mycursor
into myrecord;
end loop;
close mycursor;
end;
------使用for做游标的循环
declare
cursor mycursor(bookid number) is
select books_name from books where books.books_id=bookid;
begin
for cur in mycursor(1) loop
DBMS_OUTPUT.put_line(cur.books_name);
end loop;
end;
----is open
declare
bookname books.books_name%type;
cursor mycursor(booksid number) is
select books_name from books where books_id = booksid;
begin
if mycursor%isopen then
DBMS_OUTPUT.put_line('cursor is opened');
else
open mycursor(1);
end if;
fetch mycursor
into bookname;
close mycursor;
dbms_output.put_line(bookname);
end;
-------rowcount
declare
bookname books.books_name%type;
cursor mycursor is
select books_name from books;
begin
open mycursor;
loop
fetch mycursor
into bookname;
exit when mycursor%notfound or mycursor%notfound is null;
DBMS_OUTPUT.put_line(mycursor%rowcount);
end loop;
close mycursor;
end;
-----游标跟新数据
declare
cursor mycursor is
select books_name from books for update;
text varchar2(100);
begin
open mycursor;
fetch mycursor
into text;
while mycursor%found loop
update books
set books_name = books_name || '_t'
where current of mycursor;
fetch mycursor
into text;
end loop;
close mycursor;
end;
----------------隐式游标 不需要声明
begin
for cur in (select books_name from books) loop
DBMS_OUTPUT.put_line(cur.books_name);
end loop;
end;
--------------------存储过程------------------------------------------------------------------
create or replace procedure getBookNameById(bookid in number) is
bookname varchar2(100);
begin
select books_name into bookname from books where books_id = bookid;
--select books_name from books where books_id=1;
dbms_output.put_line(ookbname);
end;
end getBookNameById;
-----
begin调用过程
test.getBookNameById(1);
end;
----调用过程
execute test.getBookNameById(1);
-----显示某个存储过程的错误信息
show errors procedure getBookNameById;
分页：
SELECT * FROM
(
SELECT A*, ROWNUM RN
FROM (SELECT * FROM books) A
)
WHERE RN BETWEEN 1 AND 5