SQL：3.1变量-20181223

DECLARE
   v_empname VARCHAR2(20);
   v_deptname VARCHAR2(20);
   v_hiredate DATE NOT NULL := SYSDATE;
   v_empno INT NOT NULL DEFAULT 7639;
BEGIN
   NULL;
END;


DECLARE
   v_counter INTEGER;
BEGIN
   v_counter := 90;
   v_counter := v_counter+1;
   DBMS_OUTPUT.put_line('未赋值的变脸示例结果：'||v_counter);
END;


DECLARE
   v_salary NUMBER(7,2);
   v_rate NUMBER(7,2) := 0.12;
   v_base_salary NUMBER(7,2) := 1200;
BEGIN
   v_salary := v_base_salary*(1+v_rate);
   DBMS_OUTPUT.put_line('员工的薪资为：'||v_salary);
END;


DECLARE
   v_string VARCHAR2(200);
   v_hire_date DATE;
   v_bool BOOLEAN;
BEGIN
   v_bool := TRUE;
   v_hire_date := to_date('2018-12-23','yyyy-mm-dd');
   v_hire_date := SYSDATE;
   v_hire_date := date'2018-12-23';
   v_string := 'This is a string';
END;


DECLARE
   v_empno emp.empno%TYPE;
   v_ename emp.ename%TYPE;
   v_hiredate emp.hiredate%TYPE;
BEGIN
   SELECT empno, ename, hiredate INTO v_empno, v_ename, v_hiredate FROM emp WHERE empno = &empno;

   DBMS_OUTPUT.put_line('员工编号为：'||v_empno);
   DBMS_OUTPUT.put_line('员工名称为：'||v_ename);
   DBMS_OUTPUT.put_line('雇佣日期为：'||v_hiredate);
END;


DECLARE
   v_empno emp.empno%TYPE;
   v_empno2 v_empno%TYPE;
   v_salary NUMBER(7,3) NOT NULL := 1350.5;
   v_othersalary v_salary%TYPE := 1500;
BEGIN
   NULL;
END;


DECLARE
   v_emp emp%ROWTYPE;
BEGIN
   SELECT * INTO v_emp FROM emp WHERE empno = &empno;
   DBMS_OUTPUT.put_line(v_emp.empno || CHR(10) || v_emp.ename);
END;


DECLARE
   v_emp emp%ROWTYPE;
BEGIN
   v_emp.empno := 8001;
   v_emp.ename := '张三丰';
   v_emp.job := '掌门';
   v_emp.mgr := 7902;
   v_emp.hiredate := date'2018-12-23';
   v_emp.sal := 8000;
   v_emp.deptno := 20;
   INSERT INTO emp VALUES v_emp;
   COMMIT;
END;


DECLARE
   CURSOR emp_cursor IS SELECT empno, ename, job, sal, hiredate FROM emp;
   v_emp emp_cursor%ROWTYPE;
BEGIN 
   OPEN emp_cursor;
   LOOP
      FETCH emp_cursor INTO v_emp;
   EXIT WHEN emp_cursor%NOTFOUND;
   DBMS_OUTPUT.put_line( v_emp.empno
                         || ''
                         || v_emp.ename
                         || ''
                         || v_emp.job
                         || ''
                         || v_emp.sal
                         || ''
                         || TO_CHAR(v_emp.hiredate, 'YYYY-MM-DD'));
   END LOOP;
   CLOSE emp_cursor;
END;


DECLARE
   c_salary_rate CONSTANT NUMBER(7,2) := 0.25;
   v_salary NUMBER(7,2);
BEGIN
   SELECT sal * (1+c_salary_rate) INTO v_salary FROM emp WHERE empno = &empno;
   DBMS_OUTPUT.put_line('加薪后的薪资为：'|| v_salary);
END;