Oracle PL/SQL的大量操作

--PLSQL
--声明变量 语法:DECLARE var_name [CONSTANT] type [not null] [:=value]
--DECLARE varchar2(30) :='jaca';
--程序块开始BEGIN END
--EXCEPTION 当异常出现的时候
--输出语句 DBMS_OUTPUT.PUT_LINE
--准备创建一张表
--使用||符号连接字符串输出 &num接收用户的输入
--使用'&num'转换用户的输入;
--等于号= 大于号>= 小于号<= 
--提示快捷键 f6

create table chap8_user(
username varchar2(25) not null,
age number not null

)
SELECT * FROM chap8_user

--使用变量插入一条数
DECLARE 
  v_name varchar2(30) :='Jack';
  v_age  number:=6;
BEGIN
  insert into chap8_user (
  username,age
  ) values (v_name,v_age);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line('插入数据失败');
END;    
  
--变量命名要求 # _ $ 
--运算符：算术，关系，逻辑 其他
--流程控制：条件结构 
--IF condition THEN
--Statement
--ELSE
--Statement
--end if;


--示例：根据两个变量值，使用if-else判断大小,
--如果两个数字都有值的话，跟逻辑结果一样
--如果两个数字有一个是null,则结果是v_num1>v_num2 结果null 执行else后语句

DECLARE 
   v_num1 number:=15;
   v_num2 number;
   v_result varchar2(20);
BEGIN
   IF v_num1>v_num2
   then 
     v_result:='num1 is big';
   else
     v_result:='num2 is big';
    
   end if ;
   DBMS_OUTPUT.put_line('结果是'||v_result);
end;
     
   
--多重选择可是用 case 变量名 when 等值 then 执行相应的输出 end case
DECLARE 
   v_num number:=&num;
   
BEGIN 
   case v_num 
     when 1 then DBMS_OUTPUT.put_LINE('你输入的结果是1');
     when 2 then DBMS_OUTPUT.put_LINE('你输入的结果是2');
     when 3 then DBMS_OUTPUT.put_LINE('你输入的结果是3');
     else DBMS_OUTPUT.put_LINE('你输入的是其他数字');
   end case;
end;    

--课本示例
DECLARE
  grade varchar2(4):='A';
  remark varchar2(20);
BEGIN
  CASE grade
       WHEN 'A' THEN remark:='is Excellent';
       WHEN 'B' THEN remark:='is Good';
       WHEN 'C' THEN remark:='is Normal';
       WHEN 'D' THEN remark:='is Bad';
       ELSE remark:='big Problem';
  END CASE;
       DBMS_OUTPUT.put_LINE(remark);
END;


--循环 LOOP END LOOP

--课本示例,变量从1开始，到10结束
DECLARE 
    v_num number:=1;
BEGIN 
LOOP 
    v_num:=v_num+1;
    DBMS_OUTPUT.put_LINE('当前的变量值是：'||to_char(v_num));
    if(v_num=10)then
    exit;
    end if;
    
END LOOP;

END;

--接受用户输入的字符串
DECLARE 
    v_str varchar2(10):='&txt';
BEGIN
    DBMS_OUTPUT.put_line('用户输入的顺序是：'||v_str);
END;

--WHILE循环s
DECLARE 
    v_num number:=1;
BEGIN 
WHILE v_num<12 LOOP 
  v_num:=v_num+1;
  DBMS_OUTPUT.put_LINE(v_num);
END LOOP;

END;

--FOR循环 FOR 变量 in 开始位置 .. 结束的位置 LOOP
DECLARE 
   total integer:=0;
BEGIN
   FOR i IN 1..19 LOOP
     total:=total+1;
     DBMS_OUTPUT.PUT_LINE(total);
   END LOOP;
  
END; 


--由于DDL是不能在PL/SQL执行，
--需要使用执行动态的SQL
BEGIN 
  
  EXECUTE IMMEDIATE 'create table temp_table'
  ||'(id integer,name varchar2(20))';
END;  

--执行PL/SQL语句块
DECLARE 
plsql varchar2(200);
BEGIN
  plsql:='DECLARE systime varchar2(20);'
  ||'BEGIN 
  select to_char(sysdate,''dd-mm-yyyy day'') into systime from dual;
  DBMS_OUTPUT.PUT_LINE(''当前时间是:''||systime);
  END;';
  EXECUTE IMMEDIATE plsql;
END;

--SELECT * from temp_table;
--绑定变量,使用占位符的形式来绑定内容
--占位符号从1开始
DECLARE 
   plsql varchar2(200);
   t_name varchar2(20):='John';
   t_id integer :=6;
BEGIN
   plsql:='insert into temp_table values(:1,:2)';
   EXECUTE IMMEDIATE plsql using t_id,t_name;
   END;


--PL/SQL的异常处理，抛出自定义异常 声明异常的类型为EXCEPTION 抛出异常使用raise
--EXCEPTION捕获异常
DECLARE
   temp_ex exception;
   t_num integer;
BEGIN
   select count(id) into t_num from
   temp_table where id='6';
   if t_num>=1 THEN
     raise temp_ex;
     end if;
   DBMS_OUTPUT.put_line('该用户不存在');
   EXCEPTION 
     WHEN temp_ex THEN
       DBMS_OUTPUT.put_line('该用户已存在')  ;
END;

--显示指定和使用游标 %FOUND %ISOPEN %NOTFOUND %ROWCOUNT
--%FOUND 用户判断是否能获取一条记录，对已经关闭或打开前的游标操作容易出错
--%ROWCOUNT 用于判断结果集的数量

--操作步奏：1.声明游标 2.打开游标 3.提取游标 4.关闭游标
--示例：使用游标读取记录
select * from temp_table;
DECLARE 
 fname varchar2(20);
 lname number(20);
 
 CURSOR c_student is 
 select id,name from temp_table
 where id =5;
 
 BEGIN 
   --先打开
   OPEN c_student;
   --判断是否读取到记录
   if c_student%NOTFOUND THEN
      DBMS_OUTPUT.put_line('没有找到相应的记录'); 
   else
   --从游标中读取记录
   fetch c_student into lname,fname;
     DBMS_OUTPUT.put_line(fname||'年龄'||lname);
     end if;
     close c_student;
end;      


--使用循环来执行数据的读取

DECLARE 
   fname varchar2(20);
   fage number;
   cursor c_student is 
   select id ,name from temp_table ;
BEGIN
   FOR c_student_temp in c_student LOOP
     fname:=c_student_temp.name;
     fage:=c_student_temp.id;
     DBMS_OUTPUT.put_line('name:'||fname||'   age:'||fage);
     END LOOP;

END;


--补充示例，%TYPE声明变量类型
DECLARE
   v_name temp_table.name%TYPE;
   v_age temp_table.id%TYPE;
   v_name_search temp_table.id%TYPE;
   CURSOR c_student is 
   select id,name  from temp_table where id=v_name_search;
   
BEGIN
   v_name_search :=5;
   --先打开游标
   OPEN c_student;
   
   FETCH c_student into v_age,v_name;
   --关闭游标
   close c_student;
     DBMS_OUTPUT.put_line(v_age||v_name);
     
     
END;
   
--补充示例二 带参数的游标    
   
DECLARE
   v_name temp_table.name%TYPE;
   v_age temp_table.id%TYPE;
   v_name_search temp_table.id%TYPE;
   --传入参数
   CURSOR c_student (v_name_search temp_table.id%TYPE) is 
   select id,name  from temp_table where id=v_name_search;   
BEGIN
   v_name_search :=5;
   --先打开游标
   OPEN c_student(v_name_search);   
   FETCH c_student into v_age,v_name;
   --关闭游标
   close c_student;
   DBMS_OUTPUT.put_line(v_age||v_name);    
END;

--使用存储过程 存储过程是sql语句的打包

CREATE OR REPLACE PROCEDURE showInfo
as

--DECLARE

 CURSOR c_student is 
 select id ,name from temp_table ;
begin 
  for c_student_temp in c_student loop
    
   DBMS_OUTPUT.put_line('输出id123'||c_student_temp.id||'输出姓名'||c_student_temp.name);
  end loop;
  
end ;


--调用存储过程,就像调用方法一样。
begin
  showInfo ;
end; 
--删除存储过程

DROP PROCEDURE showInfo;


--执行带参数的存储过程
CREATE OR REPLACE PROCEDURE showInfo (inputid in NUMBER)
as

--DECLARE

 CURSOR c_student is 
 select id ,name from temp_table where id=inputid;
begin 
  for c_student_temp in c_student loop    
   DBMS_OUTPUT.put_line('输出id123'||c_student_temp.id||'输出姓名'||c_student_temp.name);
  end loop;  
end ;

--insert into temp_table (id,name) values (6,'内容');
--执行带参数的存储过程
DECLARE 
  inputid number:=4;
begin
   showInfo(inputid);
end;
 

--使用函数来遍历结果
CREATE OR REPLACE FUNCTION fn_showInfo(inputid in number)
--声明返回值类型
RETURN number as f_reId number;
BEGIN
 select id INTO f_reId  from temp_table where id=inputid;
 return f_reId;
END;

--调用带参函数
DECLARE 
  inputId number :=5;
  returnValue number;
begin 
  returnValue:=fn_showInfo(inputId);
  DBMS_OUTPUT.put_line('返回的值是：'||returnValue);
end;


--过程和函数的区别
--名称不一样
--函数在使用的过程中要使用参数，但是过程不需要使用参数
--函数的调用可以出现表达式当中，但过程需要单独的调用过程

--包的创建和使用
--包是包装指定的sql语句,可以给包添加权限。
--包要分开两部分声明，一个是头部，一个是body 
--注意，给包传递参数varchar2的时候不需要设置长度。如
--procedure my_proc(
--    inputid number,
--    inputname varchar2 
--);

CREATE OR REPLACE PACKAGE emp_package as
procedure my_proc(inputid in number,inputname in varchar2); 
END emp_package;  
 
CREATE OR REPLACE PACKAGE body emp_package as
procedure my_proc(inputid in number ,inputname in varchar2  ); 
  is
BEGIN
  insert into temp_table (id,name) values (inputid,inputname);
END my_proc;

end emp_package;
  
--包的调用
DECLARE 
  inputid number:=7;
  inputName varchar2(20):='张三疯';  
begin
   emp_package.my_proc(7,'张三疯');
end;
select * from temp_table;


--创建包头--
create or replace package emp_package as
--声明存储过程，用于插入一条记录--
procedure my_proc(
    inputid number,
    inputname varchar2
);
end emp_package;




--创建包体--
create or replace package body emp_package as

--存储过程的实现--
procedure my_proc(
   inputid number,
    inputname varchar2
) is
begin
insert into temp_table (id,name) values (inputid,inputname);
end my_proc;
end emp_package;

select * from temp_table; 
begin 
emp_package.my_proc(9,'9');

end;

--视图、序列，前面已经讲过，不作为重点
--创建视图
create or replace view vw_student
as select * from temp_table;
--查询视图，跟普通表的查询是一样的
select * from vw_student;

--删除视图
drop view vw_student;


--序列
CREATE SEQUENCE seq_id
minvalue 1
maxvalue 1000
start with 1
increment by 1
cache 20;

--使用序列插入
insert into temp_table (id,name) values
(seq_id.nextval,'张三');


--数据库链，用于远程连接数据库的 使用了解

CREATE database link link_office
connect to admin identified by admin 
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = wenyemin.eicp.net)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = USERDATA)
)
)';

select * from books;
--使用数据库链查询指定的表。
select * from books@link_office







/*============================================================
                       示例 创建触发器
  ============================================================*/
--需求：当职工表emp 被删除一条记录时，把被删除记录写到删除日志表中去

drop table emp;

create table emp  (

empno number primary key,
empname nvarchar2(50),
salary number(6,2),
job    nvarchar2(50),
deptno number
)


drop table del_emp;
CREATE TABLE del_emp AS SELECT * FROM EMP WHERE 1=2; 

--创建触发器
CREATE OR REPLACE TRIGGER tr_del_emp 
   BEFORE DELETE --指定触发时机为删除操作前触发
   ON emp 
   FOR EACH ROW   --说明创建的是行级触发器 
   WHEN (old.deptno <> 2)  --触发限制：deptno的新值不等于40，触发器就会执行
BEGIN
   --将修改前数据插入到日志记录表 del_emp ,以供监督使用。
   INSERT INTO del_emp(deptno , empno, empname , job , salary  )
       VALUES( :old.deptno, :old.empno, :old.empname , :old.job,:old.salary);
END;

--删除emp表中编号7900记录
DELETE emp WHERE empno=1--30号部门

--查看表，在del_emp表中会看见7900记录，emp中记录也被删除。
select * from emp;
select * from del_emp;