大型数据库 | Oracle 12 c | demo

数据库DDL、DCL、DML语句

DDL，Data Definition Language，数据库定义语言　　
- 用于定义和管理数据库所有对象的语言
- 包括：CREATE，ALERT，DROP，TRUNCATE

DML，Data Manipulation Language，数据库操作语言
- SQL中处理数据等操作
- 包括：SELECT，INSERT，UPDATE，DELETE，CALL，EXPLAIN PLAN，LOCK

DCL，Data Control Language，数据库控制语言
- 用来授予或回收访问数据库和某种特权并控制数据库操纵事务发生的时间及效果，对数据库实行监控
- 包括：COMMIT，SAVEPOINT，ROLLBACK，SET TRANSACTION

https://blog.csdn.net/u011848397/article/details/88951979

5-PLSQL

数据类基本:

SET serveroutput ON --显示服务器输出信息 
DECLARE 
  SUBTYPE name IS CHAR(20); --定义了一个name类型
  SUBTYPE MESSAGE IS VARCHAR2(100); --定义了一个MESSAGE类型
  salutation name;--定义了一个name类型的变量 
  greetings MESSAGE;--定义了一个MESSAGE类型的变量
BEGIN 
  salutation := 'Reader'; 
  greetings := 'Welcome to the World of PL/SQL'; 
  dbms_output.put_line('Hello ' || salutation || greetings);
END;

数据类型type:

SET serveroutput ON 
DECLARE 
  type emp_type IS record   --声明record类型emp_type 
  (
     var_ename VARCHAR2(20), --定义字段/成员变量
     var_job  VARCHAR2(20), 
     var_sal NUMBER 
  );
  empinfo emp_type; --定义变量
BEGIN 
   SELECT ename, job, sal INTO empinfo FROM emp WHERE empno=7369; 
   /*输出雇员信息*/ 
   dbms_output.put_line('雇员 '||empinfo.var_ename||' 的职务是 '||empinfo.var_job|| ' 工资是 '||empinfo.var_sal);
END;

数据类型%rowtype:

DECLARE 
  rowVar_emp emp%rowtype;
BEGIN 
  SELECT * INTO rowVar_emp FROM emp WHERE empno=7369; 
  dbms_output.put_line('雇员'||rowVar_emp.ename||'的编号是'||rowVar_emp.empno||', 职务是'||rowVar_emp.job);
END;

DECLARE 
  v_emp emp%rowtype; 
BEGIN 
  select * into v_emp from emp where empno=&eno;  --&表示接受输入
  dbms_output.put_line('员工名字:'||v_emp.ename);
  dbms_output.put_line('员工工资:'||v_emp.sal);
  dbms_output.put_line('员工职位'||v_emp.job);
END;

循环while:

SET serveroutput ON ;
DECLARE 
    numbers integer default 1;
BEGIN 
    LOOP 
            dbms_output.put_line('hello');
            EXIT WHEN numbers>3;
            numbers:=numbers+1; 
    END LOOP;
END;

SET serveroutput ON
DECLARE 
    sum_i INT:= 0;
    i INT:= 0;
BEGIN 
      WHILE i<=99 LOOP 
          i:=i +1;
          sum_i:= sum_i+i;
      END LOOP;
      dbms_output.put_line('前100个自然数的和是：'||sum_i);
END;

循环for:

SET serveroutput ON
DECLARE 
        sum_i INT:= 0; --定义整数变量，存储整数和
BEGIN 
    FOR i IN reverse 1..100 LOOP
        IF mod(i,2)=0 THEN --判断是否为偶数
              sum_i := sum_i+i;
        END IF;
     END LOOP; 
    dbms_output.put_line('前100个自然数中偶数之和是：'||sum_i); 
END;

set serveroutput on 
declare 
    sum_i int:= 0;
begin 
    for i in 1..100 loop 
        if mod(i,2)=0 then 
           sum_i:=sum_i+i;
        end if;
   end loop; 
   dbms_output.put_line('前100个自然数中偶数之和是：'||sum_i);
end;

set serveroutput on ;
declare 
    v_num int:= &num;--接受用户输入
    v_pro int:=1;
begin 
    if v_num =0 then 
        v_pro:=1;
    else 
        for i in 1.. v_num loop 
            v_pro:= v_pro*i;
        end loop; 
    end if;
    dbms_output.put_line(v_pro);
END;

循环goto:

set serveroutput on ;
declare 
    v_i number:=0; 
    v_s number:=0; 
begin 
    <<label_1>> v_i := v_i+1;
    if v_i<=100 then 
        v_s:=v_s+v_i; 
        goto label_1;
    end if; 
    dbms_output.put_line(v_s);
end;

异常处理:

DECLARE 
    v_zero NUMBER:=0;
    v_result NUMBER; 
BEGIN 
    v_result:=100/v_zero; 
    EXCEPTION
        when ZERO_DIVIDE then
            dbms_output.put_line('除数为0');
END;

SET serveroutput ON ;
DECLARE
  var_empno NUMBER;       --定义变量，存储雇员编号
  var_ename VARCHAR2(50); --定义变量，存储雇员名称
BEGIN
  SELECT empno,ename INTO var_empno,var_ename FROM emp WHERE deptno=10;
  IF sql%found THEN
    dbms_output.put_line('雇员编号'||var_empno||'；雇员名称'||var_ename);
  END IF;
EXCEPTION               --捕获异常
WHEN too_many_rows THEN --若SELECT INTO语句的返回记录超过一行
  dbms_output.put_line('返回记录超过一行');
WHEN no_data_found THEN --若SELECT INTO语句的返回记录为0行
  dbms_output.put_line('无数据记录');
END;
-- 用户为scott

SET serveroutput ON
DECLARE
  e_overnum EXCEPTION;
  /*定义异常处理变量*/
  v_num   NUMBER;
  max_num NUMBER :=5;
  /*定义最大允许学生数变量*/
BEGIN
  SELECT COUNT(*) INTO v_num FROM emp;
  IF max_num<v_num THEN
    RAISE e_overnum;
  END IF;
EXCEPTION
WHEN e_overnum THEN
  dbms_output.put_line( '现在的员工数是：'|| v_num||' 而最大允数是' ||max_num );
END;

DECLARE
  null_exception EXCEPTION; --声明一个exception类型的异常变量
  dept_row dept%rowtype;    --声明rowtype类型的变量dept_row
BEGIN
  dept_row.deptno := 66; --给部门编号变量赋值
  dept_row.dname  := '公关部';
  /* 向dept表中插入一条记录*/
  INSERT
  INTO dept VALUES
    (
      dept_row.deptno,
      dept_row.dname,
      dept_row.loc
    );
  IF dept_row.loc IS NULL THEN
    raise null_exception;
  END IF;
EXCEPTION
WHEN null_exception THEN                  --当raise引发的异常是null_exception时
  dbms_output.put_line('loc字段的值不许为null'); --输出异常提示信息
  ROLLBACK;
END;

游标:

create table stu  ( s_id number(3), s_xm varchar2(30)  );
ALTER TABLE stu ADD CONSTRAINT pk_stu_id PRIMARY KEY(s_id);
INSERT INTO stu  (s_id, s_xm ) VALUES (1, 'Tom');
INSERT INTO stu  (s_id, s_xm ) VALUES (2, 'Jerry' );

DECLARE
  CURSOR cur_stu
  IS
    SELECT * FROM stu;   -- 步骤1: 声明游标
  v_stu cur_stu%ROWTYPE; -- 定义一个变量存放游标指示的内容
BEGIN
  OPEN cur_stu;  -- 步骤2: 打开游标
  LOOP
    FETCH cur_stu INTO v_stu; -- 步骤3: 提取数据
    EXIT
  WHEN cur_stu%notfound;
    dbms_output.put_line(v_stu.s_id ||' : '||v_stu.s_xm);
  END LOOP;
  CLOSE cur_stu;-- 步骤4: 关闭游标
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line(SQLCODE || ' : ' || sqlerrm);
  dbms_output.put_line(dbms_utility.format_error_backtrace);
END;

DECLARE
  v_name dept.dname%TYPE;
  v_loc dept.loc%TYPE;
  CURSOR c1(v_deptno NUMBER DEFAULT 2)
  is
    SELECT dname,loc FROM dept WHERE deptno<= v_deptno ;
BEGIN
  OPEN c1;
  loop
    FETCH c1  INTO v_dname, v_loc;
    EXIT
  WHEN c1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_dname||'---'||v_loc);
  END LOOP;
  close c1;
exception
  when others then
    rollback;
end;

set serveroutput on;
DECLARE
  v_count NUMBER;
BEGIN
  INSERT INTO stu
    (s_id, s_xm
    ) VALUES
    (3, 'Micky'
    );
  IF SQL%FOUND THEN
    dbms_output.put_line('插入成功!');
  END IF;
  UPDATE stu SET stu.s_xm= 'Donald' WHERE stu.s_id = 3;
  IF SQL%FOUND THEN
    dbms_output.put_line('更新成功!');
  end if;
  DELETE FROM stu t WHERE t.s_id = 3;
  IF SQL%FOUND THEN
    dbms_output.put_line('删除成功!');
  END IF;
  SELECT COUNT(*) INTO v_count FROM stu;
  IF SQL%FOUND THEN
    dbms_output.put_line('总记录为： '||v_count);
  END IF;
  IF sql%isopen THEN --对于隐式游标而言永远为FALSE
    dbms_output.put_line('隐式游标已打开');
  ELSE
    dbms_output.put_line('隐式游标未打开');
  END IF;
EXCEPTION
WHEN OTHERS THEN
  ROLLBACK;
END;

SET serveroutput ON;
DECLARE
    v_stu stu%ROWTYPE;
    TYPE cur_stu_type IS  REF  CURSOR  RETURN stu%ROWTYPE;
    cur_stu cur_stu_type;
BEGIN
    OPEN cur_stu FOR SELECT s_id, s_xm FROM stu;
    LOOP
      FETCH cur_stu INTO v_stu; --v_id, v_xm;
    EXIT
    WHEN cur_stu%NOTFOUND;
        dbms_output.put_line('序号：' || v_stu.s_id || chr(10) || '姓名：' || v_stu.s_xm);
    END LOOP;
    CLOSE cur_stu;
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line(SQLCODE || ' : ' || SQLERRM);
  dbms_output.put_line(dbms_utility.format_error_backtrace);
END;

存储过程:

--创建过程
CREATE OR REPLACE PROCEDURE EMP_COUNT AS
  V_TOTAL NUMBER(10);
BEGIN
  SELECT COUNT(*) INTO V_TOTAL FROM EMP;
  DBMS_OUTPUT.PUT_LINE('雇员总人数为：'||V_TOTAL);
END;

--执行过程
SET serveroutput ON
EXECUTE EMP_COUNT;

函数:

--创建函数
CREATE OR REPLACE   FUNCTION get_sal(empname IN VARCHAR2)
RETURN NUMBER  IS
    Result NUMBER;
BEGIN
    SELECT sal INTO Result FROM emp WHERE ename=empname;
    RETURN(Result);
END get_sal;

  --调用函数
BEGIN
    DBMS_OUTPUT.PUT_LINE('Scott的薪水为：'||get_sal('SCOTT'));
END;

触发器:

--创建触发器
CREATE OR REPLACE TRIGGER REM_DEPT AFTER
  INSERT ON DEPT FOR EACH ROW --对表的每一行触发器执行一次
    BEGIN DBMS_OUTPUT.PUT_LINE('您向DEPT表中插入了一条新数据！');
END;
--测试触发器
INSERT INTO DEPT  (DEPTNO,DNAME,LOC ) VALUES  (05,'HR','BEIJING' );
INSERT INTO DEPT  (DEPTNO,DNAME,LOC ) VALUES  (06,'MARKET','SHANGHAI' );
INSERT INTO DEPT  (DEPTNO,DNAME,LOC ) VALUES  (07,'COMPANY','LONDON' );

CREATE OR REPLACE TRIGGER my_trigger
BEFORE  INSERT OR  UPDATE OR  DELETE ON emp 
BEGIN 
  IF(TO_CHAR (SYSDATE,'day')  IN('星期六','星期日')) OR (TO_CHAR(SYSDATE,'HH24') NOT BETWEEN 8 AND 18) THEN 
      RAISE_APPLICATION_ERROR(-20001,'不是上班时间,不能修改emp表');
  END IF;
END;

CREATE TABLE dept_log(
  OPERATOR VARCHAR2(20),
  TIME DATE
);
Create Or Replace Trigger Tri_Dept 
Before  Insert Or Delete Or Update On Dept
Declare Var_Tag Varchar2(20);
Begin
    If Inserting Then Var_Tag:='插入';
    Elsif Updating Then Var_Tag:='修改';
    Elsif Deleting Then Var_Tag:='删除';
    End If;
    Insert Into Dept_Log Values (Var_Tag,Sysdate);
End Tri_Dept;

INSERT INTO dept VALUES(0,'HR','lao');
UPDATE dept SET loc='beijing' WHERE deptno = 0;
DELETE FROM dept WHERE deptno = 0;
select  * from dept_log;

程序包:

CREATE [OR REPLACE] PACKAGE package_name
IS
    变量、常量及数据类型定义; 游标声明； 函数、过程声明
END [package_name];
    包体部分： 
CREATE [OR REPLACE] PACKAGE BODY package_name 
AS
    游标、函数、过程的具体定义
END [package_name];

6-Oracle控制与安全

回滚事务savepoint：

--【示例1】HR模式中，演示使用保存点回滚事务。
SELECT * FROM dept; --4条记录
INSERT INTO dept VALUES (15, 'MARKET', 'BEIJING' );
SAVEPOINT sp01;
INSERT INTO dept VALUES (25, 'HR', 'SHANGHAI' );
SAVEPOINT sp02;
SELECT * FROM dept;--6条记录
DELETE FROM dept WHERE deptno = 15;
SELECT * FROM dept; --5条记录
ROLLBACK TO sp02;
SELECT * FROM dept; --6条记录
ROLLBACK TO sp01;
SELECT * FROM dept; --5条记录
ROLLBACK;
SELECT * FROM dept;--4条记录

锁：

锁的详细解析

posted @ 2020-10-22 15:09 东坡肉肉君阅读(191) 评论(0) 收藏举报

刷新页面返回顶部

东坡肉肉君

我自关山点酒，千秋皆入喉

大型数据库 | Oracle 12 c | demo

数据库DDL、DCL、DML语句

5-PLSQL

数据类基本:

数据类型type:

数据类型%rowtype:

循环while:

循环for:

循环goto:

异常处理:

游标:

存储过程:

函数:

触发器:

程序包:

6-Oracle控制与安全

回滚事务savepoint：

锁：

公告