--修改序列 stu_seq为 创建序列时的名字 不能修改start值（起始值） alter sequence stu_seq 参数 --删除序列 drop sequence stu_seq
视图
作用
–提供了另外一种级别的表安全性
–隐藏的数据的复杂性
–简化的用户的SQL命令
–隔离基表结构的改变
–通过重命名列，从另一个角度提供数据
创建视图
--创建视图 createview 视图名 assql语句； createview jiuyunst asselect ID,NAME,createtime from student;
视图执行删除
deletefrom 视图名 where name='?' deletefrom jiuyunst where NAME='李四';
删除视图
dropview 视图名
WITH CHECK OPTION
可以对数据进行增删改查 ，但是需要视图能够查询到的数据
只能对视图能够查询的数据进行增删改查
-- 创建视图是加上 WITH CHECK OPTION 只能删除视图里面有的的数据 createor REPLACE view jiuyunst asselect ID,NAME,createtime from student where ID>1WITHCHECK OPTION;
WITH READ ONLY
--创建视图上加上 HITH READ ONLY（只读） 只能执行查询 createor REPLACE view jiuyunst asselect ID,NAME,createtime from student where ID>1WITH READ ONLY;
索引
主键自带索引
作用
查询数据的更快 提高sql语句的执行性能
聚集索引
聚集索引类似词典的拼音查找 同音的在一块
非聚集索引
非聚集索引类似偏旁查找
创建索引
--创建索引 create index 索引名 on 表名（需要加索引的字段） create index stupass_index ON student (pass);
唯一索引
确保定义索引的列没有重复值
--唯一索引 createUNIQUE INDEX 索引名 on 表名（表需要索引字段） CREATEUNIQUE INDEX sname_index ON student(name);
删除索引
--删除索引 DROP INDEX 创建索引时的名字 DROP index loln_index;
PLSQL
1、基本语法 Declare begin exception end
2、记录类型 --类似对象
3、流程控制 条件 循环
4、游标 类似集合
5、异常处理 +事务
6、函数 储存过程
7、触发器
基本语法
//关键字 DECLARE ....BEGIN .....END; DECLARE 变量 BEGIN 赋值 输出 END;
--------------------- DECLARE v_id NUMBER(10); v_name VARCHAR(20); BEGIN v_id:=88; SELECT NAME INTO v_name FROM STUDENT WHERE ID=1; dbms_output.put_line(v_name); dbms_output.put_line('姓名：'||v_name); dbms_output.put_line('ID：'||v_id); END;
单独赋值
//变量名 ：=值 DECLARE v_id NUMBER(10) BEGIN v_id:=88 end;
定义变量
DECLARE 变量名 类型 ...... BEGIN ...... END; -------- DECLARE v_id NUMBER(10); v_name VARCHAR(20); BEGIN ....... END;
// 查询赋值 查询出单独一个赋值 //select name INTO 定义的变量 from 表名 where 条件 DECLARE v_id NUMBER(10); v_name VARCHAR(20); BEGIN v_id:=88; SELECT NAME INTO v_name FROM STUDENT WHERE ID=1; dbms_output.put_line(v_name); dbms_output.put_line('姓名：'||v_name); dbms_output.put_line('ID：'||v_id); END;
行变量语法
DECLARE 变量名 表名%rowtype; BEGIN select*into 变量名 from 表名 where 条件 //输出 dbms_output_put_line('ID:'||变量名.表字段) END; ------------------------ DECLARE st1 STUDENT%rowtype; BEGIN select*into st1 from STUDENT where ID=1; dbms_output.put_line('姓名：'||st1.NAME); dbms_output.put_line('ID：'||st1.ID); END;
表类型赋值
DECLARE v_id STUDENT.ID%TYPE; //变量名 表名.表字段%type v_name STUDENT.NAME%TYPE; v_pass STUDENT.PASS%TYPE; v_createtime STUDENT.CREATETIME%TYPE; BEGIN SELECT ID INTO v_id FROM STUDENT WHERE id=1; SELECT NAME INTO v_name FROM STUDENT WHERE id=1; SELECT PASS INTO v_pass FROM STUDENT WHERE id=1; SELECT CREATETIME INTO v_createtime FROM STUDENT WHERE id=1; //输出 dbms_output.put_line('ID:'||v_id||' '||'姓名：'||v_name||' '||'密码：'||v_pass||' '||'创建时间：'||v_createtime); END;
IF条件判断
--条件判断 DECLARE week NUMBER(10):=3; BEGIN IF week=1THEN dbms_output.put_line(week||'去上课！'); ELSE dbms_output.put_line(week||'休息！'); END IF; END;
多重条件判断
//语法 if 条件 THEN 操作1 ELSIF 条件 THEN 操作2 ELSIF 条件 THEN 操作3 ELSE 操作4 END IF;
--条件判断 DECLARE week NUMBER(10):=10; BEGIN IF week=1THEN dbms_output.put_line(week||'去上课！'); ELSIF week=2THEN dbms_output.put_line(week||'休息2！'); ELSIF week=3THEN dbms_output.put_line(week||'休息3！'); ELSIF week=4THEN dbms_output.put_line(week||'休息4！'); ELSIF week=5THEN dbms_output.put_line(week||'休息5！'); ELSE dbms_output.put_line(week||'唯一性！'); END IF; END;
SWITCH CASE判断
//语法 case 变量 when 条件或值 THEN 操作1 when 条件或值 THEN 操作2 when 条件或值 THEN 操作3 else//可以不写 操作4 endcase;
--Case判断 DECLARE week NUMBER(10):=4; BEGIN case week WHEN1THEN dbms_output.put_line(week||'CASE1！'); WHEN2THEN dbms_output.put_line(week||'CASE2！'); WHEN3THEN dbms_output.put_line(week||'CASE3！'); ELSE dbms_output.put_line(week||'CASE4！'); ENDcase; END;
loop循环
//语法 loop 语句; exit when 条件;//退出条件 end loop;
---------------------------------
--循环1-10 DECLARE num NUMBER(10):=0; BEGIN LOOP num:=num+1; dbms_output.put_line(num); exit WHEN num=10; END LOOP; END;
while循环
//语法 while 条件 loop 操作 end loop; -------------------
--while循环 DECLARE num NUMBER(10):=0; BEGIN while num<10 loop num:=num+1; dbms_output.put_line(num); end loop; END;
for循环
//语法 DECLARE BEGIN for 变量 in 条件 loop 操作 end loop; end; ----------------------------- --for循环 1-10 DECLARE
BEGIN for k in1..10 loop dbms_output.put_line(k); end loop; END;
for循环反转
//语法 for 变量 in REVERSE 条件 loop 操作 end loop;
-------------------------
-- for循环反转REVERSE DECLARE
BEGIN for k in REVERSE 1..10 loop dbms_output.put_line(k); end loop; END;
GOTO跳出循环
for 变量 in 条件 loop 操作 if 条件 then goto 变量2； end if; end loop; <<变量2>> 操作
--------------------------
--for循环 到5跳出 DECLARE
BEGIN for k in1..10 loop dbms_output.put_line(k); if k=5THEN GOTO label; END IF; end loop; <<label>> dbms_output.put_line('跳出循环！'); END;
日期转换
TO_CHAR(变量名,'yyyy-MM-dd HH24:mi:ss')
TO_CHAR(v_creatime,'yyyy-MM-dd HH24:mi:ss')
--------- DECLARE v_ID STUDENT.ID%TYPE; v_NAME STUDENT.NAME%TYPE; v_PASS STUDENT.PASS%TYPE; v_creatime STUDENT.CREATETIME%TYPE; BEGIN SELECT ID INTO v_ID from STUDENT where ID=1; SELECT NAME INTO v_NAME from STUDENT where ID=1; SELECT PASS INTO v_PASS from STUDENT where ID=1; SELECT CREATETIME INTO v_creatime from STUDENT where ID=1; dbms_output.put_line('ID：'||' '||v_ID); dbms_output.put_line('NAME：'||' '||v_NAME); dbms_output.put_line('PASS：'||' '||v_PASS); dbms_output.put_line('CREATETIME：'||' '||TO_CHAR(v_creatime,'yyyy-MM-dd HH24:mi:ss')); END;
DECLARE 变量名 表.字段%type; cursor 游标名 issql语句 //定义游标 BEGIN open 游标名； //打开游标 loop //loop循环 FETCH 游标名 into 变量； //复制变量 exit when 游标%notfound; //notfund:未找到 没有找到游标时退出循环 dbms_output.put_line(变量); //输出赋值后的变量 end loop; close 游标名； 关闭游标 end; -------
--loop游标 DECLARE v_name EMP.ENAME%TYPE; cursor v_cur isselect ENAME from EMP; BEGIN open v_cur; loop FETCH v_cur into v_name; exit when v_cur%notfound; dbms_output.put_line(v_name); end loop; close v_cur; end;
-------- --while游标 DECLARE CURSOR V_CUR isselect ename from emp; v_name EMP.ENAME%TYPE; BEGIN OPEN V_CUR; FETCH V_CUR INTO v_name; while V_CUR%FOUND LOOP dbms_output.put_line(v_name); FETCH V_CUR INTO v_name; END LOOP; close V_CUR; END;
多变量赋值游标
DECLARE 定义变量1 表.字段%type 定义变量2 表.字段%type cursor 游标名 issql语句 BEGIN OPEN 游标名 loop //循环游标 FETCH 游标名 into 定义变量1，定义变量2； //赋值游标 exit when 游标%notfound; //游标未找到 notfound :未找到 操作定义变量1 定义变量2 end loop; close 游标名； //关闭游标 end; -------------------- DECLARE v_id EMP.EMPNO%TYPE; v_name EMP.ENAME%TYPE; cursor v_cur isselect EMPNO,ENAME from EMP;//查询2个字段 BEGIN open v_cur; loop FETCH v_cur into v_id,v_name; exit when v_cur%notfound; dbms_output.put_line(v_id||' '|| v_name); end loop; close v_cur; end;
loop显示游标
--loop游标 DECLARE v_name EMP.ENAME%TYPE; cursor v_cur isselect ENAME from EMP; BEGIN open v_cur; loop FETCH v_cur into v_name; exit when v_cur%notfound; dbms_output.put_line(v_name); end loop; close v_cur; end;
for显示游标
DECLARE CURSOR 游标名 issql语句； BEGIN FOR 变量 in 游标名 loop 操作 输出游标：变量.字段 end loop; end; ---------------------------------------- -- for显式游标（指定字段） DECLARE cursor v_cur isselect EMPNO,ENAME from EMP; BEGIN for c in v_cur loop dbms_output.put_line(c.EMPNO||' '|| c.ENAME); END loop; end;
-- for 显式游标(整个表) DECLARE cursor v_cur isselect*from EMP; BEGIN for c in v_cur loop dbms_output.put_line(c.EMPNO||' '|| c.ENAME ||' '|| c.JOB); END loop; end;
游标更新行
--使用current of 时 sql语句需要加上 for update DECLARE 变量1 表.字段%type cursor 游标名 issql语句 forupdate BEGIN OPEN 游标名 //打开游标 loop FETCH 游标名 into 变量1 exit when 游标名%notfound; //游标未找到退出 sql更新语句 wherecurrentof 游标名 currentof：当前游标 end loop close 游标名；//关闭游标 -------------------- -- 使用游标更新行 --调整工资 DECLARE v_SAL EMP.SAL%TYPE; cursor v_cur isselect SAL from EMP forUPDATE; BEGIN open v_cur; loop FETCH v_cur into v_SAL; exit when v_cur%notfound; UPDATE emp set sal = v_SAL*1.1whereCURRENTof v_cur; end loop; close v_cur; end;
BEGIN SQL语句 IF sql%notfound then 操作 end if; end; --------- --隐式游标 BEGIN UPDATE EMP SET SAL=900WHERE EMPNO=7309; IF SQL%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE('没有找到！'); END if; END;
隐式游标 影响行数
BEGIN SQL语句 操作输出行 sql%rowcount; end; ------ --隐式游标 影响行数 BEGIN UPDATE EMP SET SAL=900WHERE ROWNUM<=5; DBMS_OUTPUT.PUT_LINE('行'||SQL%rowcount); END;
--普通游标 DECLARE v_sal EMP.SAL%type; cursor c_upsal isselect SAL from EMP forUPDATE; v_cny number(3,2); BEGIN open c_upsal; loop FETCH c_upsal into v_sal; exit when c_upsal%notfound; if v_sal<=1000THEN v_cny:=0.1; ELSIF v_sal>1000and v_sal<2000THEN v_cny:=0.05; ELSIF v_sal>2000THEN v_cny:=0.01; END if; UPDATE emp set sal=v_sal*v_cny whereCURRENTof c_upsal; end loop; close c_upsal; END;
--for游标 DECLARE cursor c_upsal isselect SAL from EMP forUPDATE; v_cny number(3,2); BEGIN for sa in c_upsal loop if sa.SAL<=1000THEN v_cny:=0.1; ELSIF sa.SAL>1000and sa.SAL<2000THEN v_cny:=0.05; ELSIF sa.SAL>2000THEN v_cny:=0.01; END if; UPDATE emp set sal=sa.SAL*v_cny whereCURRENTof c_upsal; END LOOP; END;
DECLARE BEGIN 操作 EXCEPTION when 异常名 then 操作 end; -------- DECLARE
BEGIN UPDATE LOLGAME set PRICE=PRICE-1000where id=3; EXCEPTION WHEN others then dbms_output.put_line('价格必须在0-800！'); END;
多个异常
DECLARE 变量 begin 操作 exception when 异常名 then 操作 when 异常名 then 操作 .... end; ------ DECLARE V_NAME LOLGAME.NICKNAME%TYPE; BEGIN SELECT NICKNAME INTO V_NAME FROM LOLGAME WHERE ID=100; DBMS_OUTPUT.PUT_LINE('NMAE:'||V_NAME); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('数据未找到！'); WHEN others THEN DBMS_OUTPUT.PUT_LINE('异常'); END;
自定义异常
DECLARE 定义异常名 exception 变量 类型 BEGIN 操作 .... exception when 定义的异常名 then 操作
-------- --自定义异常 DECLARE age_excption EXCEPTION; age INTEGER; BEGIN age:=130; if age >120THEN raise age_excption; ELSE dbms_output.put_line('年龄:'||age); END IF; EXCEPTION WHEN age_excption then dbms_output.put_line('年龄不能大于120！'); END;
转账
--A转1000到B DECLARE transfer EXCEPTION; Amoney LOLGAME.PRICE%TYPE; BEGIN select PRICE INTO Amoney from LOLGAME where ID=3; if Amoney<1000THEN raise transfer; ELSE UPDATE LOLGAME set PRICE=PRICE-1000where ID=3; update LOLGAME set PRICE=PRICE+1000where ID=2; dbms_output.put_line('转账成功！'); END IF; EXCEPTION WHEN transfer then dbms_output.put_line('金额不足 转账失败！'); END;
存储过程
定义储存过程
createprocedure 过程名 （变量 类型） as 定义变量 begin 操作 exception when 异常名 then 操作
--储存过程 CREATEPROCEDURE find_emp(noINTEGER) as --定义变量 v_name EMP.ENAME%TYPE; BEGIN select ENAME into v_name FROM EMP WHERE EMPNO=no; dbms_output.put_line('姓名：'||v_name); EXCEPTION when no_data_found THEN dbms_output.put_line('没有该人员！'); END;
IN OUT参数将初始值传递给子程序，并将更新的值返回给调用者。 它可以分配一个值，该值可以被读取。对应于IN OUT形式参数的实际参数必须是变量，而不是常量或表达式。正式参数必须分配一个值。实际参数(实参)通过值传递。
过程不能在sql语句中运行 函数可以
过程里可以有out in inout参数
函数只能有in参数
--输入输出变量 out输出 IN不用写 out类似返回值 CREATEPROCEDURE Ufunc(nmber INTEGER,nmber2 INTEGER,res OUTINTEGER) AS BEGIN res:=nmber+nmber2; END;
--测试输入输出 DECLARE v_res INTEGER; BEGIN Ufunc(1,3,v_res);//4 dbms_output.put_line('结果：'||v_res); END;
函数
createfunction 函数名 （参数）return 返回类型 as begin return 返回内容 end;
--函数 --定义函数 createFUNCTION hell(name VARCHAR)returnVARCHAR as BEGIN return'你好！'||name; END;
--执行函数 select hell('数据') from dual;
--执行函数 DECLARE v_na VARCHAR(50); BEGIN v_na:=hell('数据包'); dbms_output.put_line('结果：'||v_na); END;
案例转账过程
--转账过程 CREATEPROCEDURE trans3(user1 INTEGER,user2 INTEGER,money INTEGER) as transfer EXCEPTION; Amoney LOLGAME.PRICE%TYPE; tranmoer EXCEPTION; BEGIN select PRICE INTO Amoney from LOLGAME where ID=user1; IF Amoney < money THEN raise transfer; ELSIF money<=0THEN raise tranmoer; ELSE update LOLGAME set PRICE=PRICE-money where ID=user1; UPDATE LOLGAME SET PRICE=PRICE+money WHERE ID=user2; dbms_output.put_line('转账成功！'); END IF; EXCEPTION WHEN no_data_found THEN dbms_output.put_line('未找到该用户！'); when tranmoer THEN dbms_output.put_line('金额不能小于0！'); when transfer THEN dbms_output.put_line('金额不足 转账失败！'); END; --测试 call trans3(2,3,1);
