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;