Oracle笔记

Oracle笔记二

序列sequence

是Oracle提供的用于产生一系列唯一数字的数据对象
可用于主键自增

CREATE SEQUENCE SEQ_TEST–创建序列名称
INCREMENT BY N – 递增的序列值(步长)是n，如果n是正数，就递增，如果是负数就递减
START WITH N – 开始的值
MAXVALUE N – 最大值
MINVALUE N – 最小值
CYCLE|NOCYCLE – 是否循环
CACHE N|NOCACHE – 是否缓存
CURRVAL – 获取序列的当前值
NEXTVAL – 获取序列的下一个值

如果没有循环，当超过最大或最小值时出错
一个序列所有表共用
起始指针在外边，第一次需要next才能curr
如果步长为负，需要定义最大值

CREATE SEQUENCE SEQ_TEST 
INCREMENT BY 1
START WITH 1;

SELECT SEQ_TEST.NEXTVAL FROM DUAL;
SELECT SEQ_TEST.CURRVAL FROM DUAL;

PL/SQL

DECLARE (可选，声明各种变量和游标)
BEGIN （必要的，从此开始执行）
EXCEPTION （可选，抓取到异常后执行）
END;（必要的，结束执行）

如果要在sqlplus里面执行，
先开启打印 SET SERVEROUTPUT ON
最后面加 / 表示执行

BEGIN
	DBMS_OUTPUT.PUT_LINE('HELLO, WORLD!');--打印语句
END;

变量声明

1. 不能使用保留字，如from select等
2. 第一个字符必须是字母
3. 最多包含30个字符
4. 不要与数据库表或者列同名
5. 每一行只能声明一个变量

DECLARE
	-- V_NAME VARCHAR2(20); -- 定义变量
	V_NAME VARCHAR2(20):='ZHANGSAN'; -- 定义变量的同时赋值
	V_NUM NUMBER := 0;--:
  V_DATE DATE := SYSDATE;
  V_USER_NAME T_USER.USER_NAME%TYPE; -- 用表内字段类型声明变量类型
	V_USER_NAME2 V_USER_NAME%TYPE; -- 用变量类型属性声明另外一个变量类型
BEGIN
	-- V_NAME:='ZHANGSAN';
  V_NUM := 5/1;
	DBMS_OUTPUT.PUT_LINE(TO_CHAR(V_DATE,'YYYY-MM-DD HH24:MI:SS'));
	DBMS_OUTPUT.PUT_LINE(V_USER_NAME || V_NAME); -- 拼字符串，使用 ||
	
EXCEPTION--如果有异常就执行，不写的话抛异常
WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE('ERROR');
END;

IF

DECLARE
	V_NUM NUMBER :=1;
BEGIN
	IF V_NUM>1 THEN
		DBMS_OUTPUT.PUT_LINE('OK');
  ELSE
		DBMS_OUTPUT.PUT_LINE('NOT OK');
	END IF;--必须写END IF，不然会将后边的语句当做IF后的语句

	IF V_NUM>1 THEN
		DBMS_OUTPUT.PUT_LINE('TTT');
	ELSIF V_NUM<1 THEN--注意写法
		DBMS_OUTPUT.PUT_LINE('TTT2');
	ELSE
		DBMS_OUTPUT.PUT_LINE('TTT3');
	END IF;
END;

循环

LOOP 循环语句 EXIT WHEN 条件 END LOOP;

DECLARE
	V_NUM NUMBER:=8;
BEGIN
	LOOP
		INSERT INTO T_TEST7(ID) VALUES(V_NUM);
		-- DBMS_OUTPUT.PUT_LINE(V_NUM);
		EXIT WHEN V_NUM=0;
		V_NUM:=V_NUM-1;
		-- EXIT WHEN V_NUM=0;
	END LOOP;
END;

WHILE 条件 LOOP END LOOP;

DECLARE
	V_NUM NUMBER:=0;
BEGIN
	WHILE V_NUM<10 LOOP
		DBMS_OUTPUT.PUT_LINE(V_NUM);
    V_NUM:=V_NUM+1;
	END LOOP;
END;

FOR 变量 IN [REVERSE] 开始值 … 结束值 LOOP END LOOP;

BEGIN
	FOR i_count IN REVERSE 5..10 LOOP
		DBMS_OUTPUT.PUT_LINE(i_count);
	END LOOP;
END;
-- i_count 是循环控制变量，由oracle隐含定义

INTO

DECLARE
	V_ID T_USER.USER_ID%TYPE;
	V_NAME T_USER.USER_NAME%TYPE;
BEGIN
-- SELECT INTO 查询结果必须是一条记录，多条或者没有数据都会报错
	SELECT USER_ID,USER_NAME INTO V_ID,V_NAME FROM T_USER WHERE USER_ID=2; 
	DBMS_OUTPUT.PUT_LINE(V_ID || V_NAME);
END;

如果select出来多个字段，就要用多个变量来接住，这时候就要用到游标

游标(CURSOR)

用来处理使用select语句从数据库中检索到的多行记录的工具
简单来说：一个可以遍历的结果集

CURSOR 游标名称 IS select语句

DECLARE 
	V_ID T_USER.USER_ID%TYPE;
	V_NAME T_USER.USER_NAME%TYPE;
	-- 定义一个游标
	CURSOR CUR_USER IS SELECT USER_ID,USER_NAME FROM T_USER ;
BEGIN
	-- 打开游标
	OPEN CUR_USER;
	-- 从游标中提取数据
	FETCH CUR_USER INTO V_ID,V_NAME;
	-- 判断游标里面是否有数据 
	-- %NOTFOUND， 没有数据
	IF CUR_USER%NOTFOUND THEN
		DBMS_OUTPUT.PUT_LINE('NO DATA');
	ELSE
		DBMS_OUTPUT.PUT_LINE(V_ID || ',' || V_NAME);
	END IF;
	--关闭游标
	CLOSE CUR_USER;
END;

第一种

DECLARE 
	V_ID T_USER.USER_ID%TYPE;
	V_NAME T_USER.USER_NAME%TYPE;
	CURSOR CUR_USER IS SELECT USER_ID,USER_NAME FROM T_USER;
BEGIN
	OPEN CUR_USER;
	LOOP
		FETCH CUR_USER INTO V_ID,V_NAME;
		EXIT WHEN CUR_USER%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(V_ID || ',' || V_NAME);
	END LOOP;
	CLOSE CUR_USER;
END;

第二种

  DECLARE 
    	V_ID T_USER.USER_ID%TYPE;
    	V_NAME T_USER.USER_NAME%TYPE;
    	CURSOR CUR_USER IS SELECT USER_ID,USER_NAME FROM T_USER;
    BEGIN
    	-- I_USER 代表一行记录，里面可能有多个列
    	FOR I_USER IN CUR_USER LOOP
    		V_ID:=I_USER.USER_ID;
    		V_NAME:=I_USER.USER_NAME;
    		DBMS_OUTPUT.PUT_LINE(V_ID ||','|| V_NAME);
    	END LOOP;
    END;
    -- 使用for循环时，自动打开游标，无需使用open语句
    -- pl/sql会自动对变量进行隐式声明
    -- 当循环结束后，游标会自动关闭
    --推荐使用

复制表

CREATE TABLE T_USER3 AS SELECT * FROM T_USER WHERE 1=2;
--加上一个不成立的条件就可以只复制表结构而不赋值数据(默认连同数据一起复制)

DECLARE
	CURSOR CUR_USER IS SELECT USER_ID,USER_NAME FROM T_USER;
BEGIN
	FOR I_USER IN CUR_USER LOOP
		INSERT INTO T_USER3(USER_ID,USER_NAME,HIREDATE) VALUES(I_USER.USER_ID,I_USER.USER_NAME,SYSDATE);
	END LOOP;
END;

视图(VIEW)

一个视图实际上就是封装了一条复杂的查询语句

CREATE VIEW 视图名称 AS 查询语句

CREATE VIEW VIEW_EMP AS
SELECT * FROM T_EMP WHERE DEPTNO=10 AND JOB='MANAGER' 
OR DEPTNO=20 AND JOB='CLERK'
OR JOB NOT IN('MANAGER','CLERK') AND SAL>=2000;
-- 视图不会存储任何数据
-- 跟table一样使用

创建VIEW需要授权

GRANT CREATE VIEW TO NAME;