oracle_PL/SQL基础

1 
Create tablespace indx 
Datafile 'd:\indx01.dbf'  size 20m;
 
2、
CREATE TABLE authors(
 author_id VARCHAR2(15) CONSTRAINT A_PK PRIMARY KEY
   USING INDEX TABLESPACE indx,
 author_fname VARCHAR2(20) NOT NULL,
 author_lname VARCHAR2(40) NOT NULL,
 phone CHAR(20) NOT NULL,
 addr VARCHAR2(50),
 city VARCHAR2(20),
 state CHAR(10),
 zip CHAR(10)
 );


insert into authors(author_id,author_fname,author_lname,phone)
values('810001','小王','王','4205567');


insert into authors(author_id,author_fname,author_lname,phone)
values('810002','小刘','刘','4205568');


SET SERVEROUTPUT ON   --程序执行时可以输出结果值  显示 putline




--定义一个pl/sql程序块


DECLARE
 V_AUTHORFNAME VARCHAR2(20);
 V_AUTHORLNAME VARCHAR2(40);
BEGIN
 SELECT AUTHOR_FNAME,AUTHOR_LNAME
 INTO V_AUTHORFNAME,V_AUTHORLNAME
 FROM AUTHORS
 WHERE AUTHOR_ID ='810001';
 DBMS_OUTPUT.PUT_LINE(V_AUTHORLNAME || V_AUTHORFNAME);
END;
/


variable_name  指出声明的变量或者常量名


constant 说明声明的是常量


not null非空


default 为变量或者常量赋值




赋值 
1.直接初始化
DECLARE
 V_AUTHORFNAME VARCHAR2(20) := 'db1001';
 V_AUTHORLNAME VARCHAR2(40) default '哈哈哈'


2.执行时为单个变量赋值


DECLARE
 V_AUTHORFNAME VARCHAR2(20);
BEGIN
 V_AUTHORLNAME VARCHAR2(40) :=  '哈哈哈'
3.执行部分时用select fetch语句同时为多个变量赋值 称为 集体赋值
DECLARE
 V_AUTHORFNAME VARCHAR2(20);
 V_AUTHORLNAME VARCHAR2(40);
BEGIN
 SELECT AUTHOR_FNAME,AUTHOR_LNAME
 INTO V_AUTHORFNAME,V_AUTHORLNAME
 FROM AUTHORS
 WHERE AUTHOR_ID ='810001';






使用select语句




//创建books表
CREATE TABLE books(
bookid NUMBER(6) ,
booknum VARCHAR2(6),
bookname VARCHAR2(60),
author VARCHAR(50),
publish VARCHAR2(50),
bookprice NUMBER(8,2),
category CHAR(10),
booktime DATE DEFAULT SYSDATE
)


INSERT INTO books(booknum,bookname,author,bookprice) 
VALUES('DB1006','ORACLE','admin',30);


INSERT INTO books(booknum,bookname,author,bookprice) 
VALUES('DB1003','JAVA','admin2',37.8);


INSERT INTO books(booknum,bookname,author,bookprice) 
VALUES('DB1002','LINUX','admin3',32.31);




DECLARE
 V_BOOKNAME books.bookname%TYPE;
 V_AUTHOR books.author%TYPE;
BEGIN
 SELECT BOOKNAME,AUTHOR 
 INTO V_BOOKNAME,V_AUTHOR
 FROM BOOKS
 WHERE BOOKNUM ='DB1007';
 DBMS_OUTPUT.PUT_LINE(V_BOOKNAME || ' ' || V_AUTHOR);
END;
/
---------------------------------------------------
创建序列books_seq 初始值为1 递步增长1最大值为100000
CREATE SEQUENCE books_seq
START WITH 1
INCREMENT BY 1
MAXVALUE 100000
CACHE 10
NOCYCLE;


------------------调用insert 
DECLARE
 V_BOOKNUM VARCHAR2(6) := 'DB1005';
BEGIN
 INSERT INTO BOOKS(BOOKID,BOOKNUM,BOOKNAME)
 VALUES(BOOKS_SEQ.NEXTVAL,V_BOOKNUM,'数据库基础教程');
 UPDATE BOOKS SET BOOKPRICE =55.2
WHERE BOOKNUM = V_BOOKNUM;
 DELETE FROM BOOKS
WHERE BOOKNUM = V_BOOKNUM;
END;
/


事务控制
--回滚delete操作  后提交
DECLARE
 V_BOOKNUM VARCHAR2(6) := 'DB1005';
BEGIN
 INSERT INTO BOOKS(BOOKID,BOOKNUM,BOOKNAME)
 VALUES(BOOKS_SEQ.NEXTVAL,V_BOOKNUM,'数据库基础教程');
 SAVEPOINT SP1;
 DELETE FROM BOOKS
WHERE BOOKNUM = V_BOOKNUM;
 ROLLBACK TO SAVEPOINT SP1;
 UPDATE BOOKS SET BOOKPRICE =55.2
WHERE BOOKNUM = V_BOOKNUM;
 COMMIT;
END;
/


------------------------------------PL/SQL控制结构
CREATE TABLE orders(
order_id VARCHAR2(20),
order_date DATE DEFAULT SYSDATE,
qty INTEGER,
payterms VARCHAR2(12),
book_id NUMBER(6)
);


//插入数据orders
INSERT INTO orders(order_id,book_id) VALUES(1,10);


用if语句统计orders表的销售量
DECLARE
 V_NUM INTEGER;
 V_BOOKNUM VARCHAR2(6) := 'DB1004';
 V_STR VARCHAR2(60);
BEGIN
 SELECT SUM(QTY) INTO V_NUM
 FROM ORDERS
 WHERE BOOK_ID = V_BOOKNUM;
 IF V_NUM >= 100000 THEN
    V_STR := V_booknum
END;




------------------------------------PL/SQL控制结构
CREATE TABLE orders(
order_id VARCHAR2(20),
order_date DATE DEFAULT SYSDATE,
qty INTEGER,
payterms VARCHAR2(12),
book_id VARCHAR2(6)
);
--插入数据
insert into orders(order_id,order_date,qty,book_id)
values(1001,to_date('2017-05-01','YYYY-MM-DD'),3000,'DB1004');


insert into orders(order_id,order_date,qty,book_id)
values(1002,to_date('2017-05-20','YYYY-MM-DD'),5000,'DB1004');


insert into orders(order_id,order_date,qty,book_id)
values(1002,to_date('2017-05-20','YYYY-MM-DD'),5000,'DB1007');


select * from orders;


SET SERVEROUTPUT ON  -- 控制输出  一定要写


----ELSIF  (ELSEIF) if结构
DECLARE
V_NUM INTEGER;
V_BOOKNUM VARCHAR2(6) := 'DB1004';
V_STR VARCHAR2(60);
BEGIN
SELECT SUM(QTY) INTO V_NUM
FROM ORDERS
WHERE BOOK_ID =V_BOOKNUM;
IF V_NUM >= 100000 THEN
  V_STR := V_BOOKNUM || '的销量为：' || V_NUM || '册，畅销的书';
ELSIF V_NUM >= 50000 THEN
  V_STR := V_BOOKNUM || '的销量为：' || V_NUM || '册，比较畅销的书！';
ELSIF V_NUM >= 5000 THEN
  V_STR := V_BOOKNUM || '的销量为：' || V_NUM || '册，销售一般的书！';
ELSE 
  V_STR := V_BOOKNUM || '的销量为：' || V_NUM || '册，滞销的书！';
END IF;
DBMS_OUTPUT.PUT_LINE(V_STR);
END;
/


--case结构1


DECLARE
V_NUM INTEGER;
V_BOOKNUM VARCHAR2(6) := 'DB1004';
V_STR VARCHAR2(60);
BEGIN
SELECT SUM(QTY) INTO V_NUM
FROM ORDERS
WHERE BOOK_ID =V_BOOKNUM;
CASE
WHEN V_NUM >= 100000 THEN
  V_STR := V_BOOKNUM || '的销量为：' || V_NUM || '册，畅销的书';
WHEN V_NUM >= 50000 THEN
  V_STR := V_BOOKNUM || '的销量为：' || V_NUM || '册，比较畅销的书！';
WHEN V_NUM >= 5000 THEN
  V_STR := V_BOOKNUM || '的销量为：' || V_NUM || '册，销售一般的书！';
ELSE 
  V_STR := V_BOOKNUM || '的销量为：' || V_NUM || '册，滞销的书！';
END CASE;
DBMS_OUTPUT.PUT_LINE(V_STR);
END;
/


--case结构2
a可以输入  也可以提前定义  用define a = 滞销


DECLARE
V_SALSTATUS VARCHAR2(10) := '&a';
V_STR VARCHAR2(60);
BEGIN
V_STR := V_SALSTATUS || '书的售出册数大于等于';
CASE V_SALSTATUS
WHEN '畅销'THEN V_STR := V_STR || 100000;
WHEN '比较畅销'THEN V_STR := V_STR || 50000;
WHEN '销售一般'THEN V_STR := V_STR || 5000;
WHEN '滞销'THEN V_STR := V_SALSTATUS || '书的出售数量小于5000';
ELSE V_STR  := '此状态不对应任何售出的数量等级';
END CASE;
DBMS_OUTPUT.PUT_LINE(V_STR);
END;
/


--loop循环


DECLARE
V_I INTEGER := 1;
V_SUM INTEGER :=0;
BEGIN
LOOP
V_SUM := V_SUM + V_I * V_I;
V_I := V_I +1;
EXIT WHEN V_I > 10;
END LOOP;
DBMS_OUTPUT.PUT_LINE('10以内的正整数平方和等于' || V_SUM);
END;
/


--WHILE 循环




DECLARE
V_I INTEGER := 1;
V_SUM INTEGER :=0;
BEGIN
WHILE
V_I <= 10 LOOP
V_SUM := V_SUM + V_I * V_I;
V_I := V_I +1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('10以内的正整数平方和等于' || V_SUM);
END;
/


--FOR循环




DECLARE
V_SUM INTEGER :=0;
BEGIN
FOR V_I IN 1..10 LOOP
V_SUM := V_SUM + V_I * V_I;
END LOOP;
DBMS_OUTPUT.PUT_LINE('10以内的正整数平方和等于' || V_SUM);
END;
/


--goto语句


DECLARE
V_I INTEGER := 1;
V_SUM INTEGER :=0;
BEGIN
LOOP
V_SUM := V_SUM + V_I * V_I;
V_I := V_I +1;
IF V_I >10 THEN
GOTO endofloop;
END IF;
END LOOP;
<<endofloop>>
DBMS_OUTPUT.PUT_LINE('10以内的正整数平方和等于' || V_SUM);
END;
/




集合与记录


联合数组
DECLARE
TYPE BOOKNAME_ARRAY IS TABLE OF BOOKS_PUB.BOOKS.BOOKNAME%TYPE 
INDEX BY BINARY_INTEGER;
V_BOOKNAME1 BOOKNAME_ARRAY;
V_BOOKNAME2 BOOKNAME_ARRAY;
BEGIN
V_BOOKNAME1(-10) := '数据结构c语言版';
V_BOOKNAME1(1) := '数据库原理';
V_BOOKNAME1(10)  := 'oracle10g入门与提高';
FOR i IN 20..25 LOOP
V_BOOKNAME1(i) := '高等数学'||TO_CHAR(i);
END LOOP;
V_BOOKNAME2 := V_BOOKNAME1;
DBMS_OUTPUT.PUT_LINE('V_BOOKNAME1(-10):' || V_BOOKNAME1(-10));
DBMS_OUTPUT.PUT_LINE('V_BOOKNAME1(1):'   || V_BOOKNAME1(1));
DBMS_OUTPUT.PUT_LINE('V_BOOKNAME1(20):'  || V_BOOKNAME1(20));
END;
/