Orcle基本语句（六）存储过程

-- Created on 2017/1/5 by ADMINISTRATOR 
DECLARE
  -- Local variables here
  i        INTEGER := 12;
  v_name   VARCHAR(15) := '张晓风';
  v_age    NUMBER(3) := 28;
  v_sex    VARCHAR(4) := '男';
  v_height NUMBER(3) := 150;
  v_age1   NUMBER(3);

BEGIN
  v_age1 := 3;
  -- Test statements here
  dbms_output.put_line('我叫' || v_name || ', ');
  dbms_output.put_line('今年' || v_age || ', ');
  dbms_output.put_line('性别' || v_sex || ', ');
  dbms_output.put_line('身高' || v_height || ', ');
END;

--给100号员工的工资加1
DECLARE
  v_sal1 number;
  v_sal2 number:=1;
BEGIN
  SELECT salary INTO v_sal1 FROM employees WHERE employee_id = 100;
  UPDATE employees SET salary = (v_sal1 + v_sal2) WHERE employee_id =100;
  COMMIT;
END;

SELECT ename, sal INTO v_ename, v_sal FROM emp WHERE empno = 7369;
--查询员工编号为7369号的员工信息，列包括：姓名，薪资
DECLARE
  v_empid NUMBER(5) := 7369;
  v_name  emp.ename%TYPE;
  v_sal   emp.sal%TYPE;
BEGIN
  SELECT ename, sal INTO v_name, v_sal FROM emp WHERE empno = v_empid;
  dbms_output.put_line('名字：' || v_name || '，薪资：' || v_sal);
END;
--向表new_dept插入数据，部门编号为50，部门名称chinasofti，地址：zz
CREATE TABLE new_dept AS SELECT * FROM dept;
DECLARE
    v_deptno new_dept.deptno%TYPE := 50;
    v_dname  new_dept.dname%TYPE := 'chinasofti';
    v_loc    new_dept.loc%TYPE := 'zz';
BEGIN
  INSERT INTO new_dept VALUES(v_deptno, v_dname, v_loc);
  COMMIT;
  END;
 /*录入一个数字，
  如果大于0小于100输出大于0小于100，
  如果大于0大于100输出大于0大于100
  如果小于0输出 小于0
*/
DECLARE
  v_test number(6):=&input;
BEGIN
  IF v_test >=0 AND v_test <=100 THEN
    dbms_output.put_line('输入的值在0和100之间');
  ELSIF v_test > 100 THEN
    dbms_output.put_line('输入的值大于100');
  ELSE 
    dbms_output.put_line('输入的值小于0');
  END IF;
  END;
-- --输入一个员工编号，判断员工工资等级（输出员工编号，姓名，工资，工资等级）
--如果员工不存在，那么提示"输入的员工不存在"
/*
>=15000 A
>=10000 B
>=5000  C
<5000   D
*/
DECLARE
  e        employees%ROWTYPE; --表对象
  v_level  CHAR(1); --工资级别
  v_count  BINARY_INTEGER; --存放员工是否存在0不存在1存在 
BEGIN
  e.employee_id := &input;
  SELECT COUNT(e.employee_id)
    INTO v_count
    FROM employees
   WHERE employee_id = e.employee_id;
  IF (v_count <= 0) THEN
    dbms_output.put_line('员工不存在');
  ELSE
    SELECT *
      INTO e
      FROM employees
     WHERE employee_id = e.employee_id;
    IF e.salary >= 15000 THEN
      v_level := 'A';
    ELSIF e.salary >= 10000 THEN
      v_level := 'B';
    ELSIF e.salary >= 5000 THEN
      v_level := 'c';
    ELSE
      v_level := 'D';
    END IF;
     dbms_output.put_line('员工编号:' || e.employee_id || ',姓名:' || e.last_name || ',工资:' || e.salary ||',工资等级：' ||  v_level);
  END IF;
END;

--********************--

declare  
  e employees%rowtype;--表对象
  v_level char(1);--工资级别
  v_count binary_integer;--存放员工是否存在0不存在1存在 
  
begin
      e.employee_id:=&input;        
      select count(employee_id)  into v_count from employees where employee_id= e.employee_id;
      if(v_count<=0)
             then 
                  dbms_output.put_line('员工不存在');                               
                  return;--退出
      end if;
      --接下来plsql块
      select * into e from employees where employee_id= e.employee_id;-->select employee_id,first_name...,deparment_id into  v_employee_id,v_first_name...,v_deparment_id frp from employees where employee_id= v_employee_id;
      dbms_output.put_line('----:' || e.salary);
      if(e.salary>=15000) then
         v_level:='A';
      elsif(e.salary>=10000) then
         v_level:='B';
      elsif(e.salary>=5000) then
         v_level:='C';
      else
         v_level:='D';
      end if;
      --输出员工编号，姓名，工资，工资等级
      dbms_output.put_line('员工编号:' || e.employee_id || ',姓名:' || e.last_name || ',工资:' || e.salary ||',工资等级：' ||  v_level);
  end;
--case语句
case 表达式值
  when 值1 THEN
    语句块1；
  when 值2 THEN
    语句块2；
 end case;
    
--输入ABCD，输入 A优秀 B良好  C一般  D差劲
DECLARE
  v_char VARCHAR2(1) := '&input';
BEGIN
    CASE v_char
    WHEN 'A' THEN
      dbms_output.put_line('优秀');
    WHEN 'B' THEN
      dbms_output.put_line('良好');
    WHEN 'C' THEN
      dbms_output.put_line('一般');
    WHEN 'D' THEN
      dbms_output.put_line('差劲');
  END CASE;
END;
--loop循环使用
DECLARE
  v_i BINARY_INTEGER := 0;
BEGIN
  LOOP
    IF v_i > 10 THEN
      EXIT;
    END IF;
    v_i := v_i + 1;
    dbms_output.put_line('第' || v_i || '次循环');
  END LOOP;
  dbms_output.put_line('循环结束');
END;

--while循环使用
DECLARE
  v_i BINARY_INTEGER := 0;
BEGIN
  WHILE v_i < 10 LOOP
    dbms_output.put_line('hello' || v_i);
    v_i := v_i + 1;
  END LOOP;
  dbms_output.put_line('over');
END;

--for循环正序
DECLARE
BEGIN
  FOR v_i IN 0 .. 10 LOOP
    dbms_output.put_line('hello' || v_i);
  END LOOP;
  dbms_output.put_line('over');
END;
--for循环倒序
DECLARE
BEGIN
  FOR v_i IN REVERSE 0 .. 10 LOOP
    dbms_output.put_line('hello' || v_i);
  END LOOP;
  dbms_output.put_line('over');
END;
--循环插入10行数据
CREATE TABLE books(
  author varchar2(20),
  book number(4)
);
--FOR循环
DECLARE
  v_name VARCHAR2(12) := 'Jhon';
BEGIN
  FOR v_i IN  1 .. 10 LOOP
    INSERT INTO books VALUES (v_name || v_i, v_i);
    COMMIT;
  END LOOP;
END;
--WHILE循环
DECLARE
  v_name VARCHAR2(12) := 'Jhon';
  v_i BINARY_INTEGER := 1;
BEGIN
  WHILE v_i <= 10 LOOP
    INSERT INTO books VALUES (v_name || v_i, v_i);
    COMMIT;
    v_i := v_i + 1;
  END LOOP;
END;
--loop循环
DECLARE
  v_name VARCHAR2(12) := 'Jhon';
  v_i    BINARY_INTEGER := 1;
BEGIN
  LOOP
    IF v_i > 10 THEN
      EXIT;
    END IF;
    INSERT INTO books VALUES (v_name || v_i, v_i);
    COMMIT;
    v_i := v_i + 1;
  END LOOP;
END;