sql: Oracle simple example table

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193

--Oracle 9i 实例数据脚本地址:$oracle_home/rdbms/admin/utlsampl.sql   CREATE TABLE DEPT        (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,     DNAME VARCHAR2(14) ,     LOC VARCHAR2(13) ) ; CREATE TABLE EMP        (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,     ENAME VARCHAR2(10),     JOB VARCHAR2(9),     MGR NUMBER(4),     HIREDATE DATE,     SAL NUMBER(7,2),     COMM NUMBER(7,2),     DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT); INSERT INTO DEPT VALUES     (10,'ACCOUNTING','NEW YORK'); INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS'); INSERT INTO DEPT VALUES     (30,'SALES','CHICAGO'); INSERT INTO DEPT VALUES     (40,'OPERATIONS','BOSTON'); INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20); INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30); INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30); INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20); INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30); INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30); INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10); INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-7-87','dd-mm-rr')-85,3000,NULL,20); INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10); INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30); INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,to_date('13-7-87', 'dd-mm-rr')-51,1100,NULL,20); INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30); INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20); INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10); CREATE TABLE BONUS     (     ENAME VARCHAR2(10)    ,     JOB VARCHAR2(9)  ,     SAL NUMBER,     COMM NUMBER     ) ; CREATE TABLE SALGRADE       ( GRADE NUMBER,     LOSAL NUMBER,     HISAL NUMBER ); INSERT INTO SALGRADE VALUES (1,700,1200); INSERT INTO SALGRADE VALUES (2,1201,1400); INSERT INTO SALGRADE VALUES (3,1401,2000); INSERT INTO SALGRADE VALUES (4,2001,3000); INSERT INTO SALGRADE VALUES (5,3001,9999); COMMIT; EXIT       select rowid,emp.* from emp; SELECT emp.empno,dept.deptno from emp,dept where emp.deptno=dept.deptno and dept.deptno>=20;   SELECT * FROM EMP;     DECLARE  v_EmpName VARCHAR2(50); BEGIN  SELECT EName Into v_EmpName FROM Scott.EMP WHERE empNo=&EmpNo;  DBMS_OUTPUT.PUT_LINE('当前查询的员工编号为：'||&EmpNo||'员工名称为：'||v_EmpName); END;     --开启屏幕输出显示 --SET SERVEROUTPUT ON; --显示当前日期与时间 BEGIN   DBMS_OUTPUT.PUT_LINE('现在的日期时间：');   --显示信息不换行   DBMS_OUTPUT.PUT('今天是：');   --显示信息并换行   DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE,'DAY'));   DBMS_OUTPUT.PUT('现在时间是： ');   DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'));  END;     --分支结构示例，演示为员工加薪 DECLARE  --定义加薪比率常量  c_Manager CONSTANT NUMBER:=0.15;  c_SalesMan CONSTANT NUMBER:=0.12;  c_Clerk CONSTANT NUMBER:=0.10;  --定义工种变量  v_Job VARCHAR(100);  empNo1 NUMBER(4):=7369; BEGIN  --查询指定员工编码的员工信息  SELECT job INTO v_Job FROM emp WHERE empno=empNo1; --执行分支判断  IF v_Job='CLERK' THEN    UPDATE emp SET SAL=SAL*(1+c_Clerk) WHERE empno=empNo1;  ELSIF v_Job='SALESMAN' THEN    UPDATE emp SET SAL=SAL*(1+c_SalesMan) WHERE empno=empNo1;    ELSIF v_Job='MANAGER' THEN    UPDATE emp SET SAL=SAL*(1+c_Manager) WHERE empno=empNo1;    END IF;  --显示完成信息  DBMS_OUTPUT.PUT_LINE('已经为员工'||empNo1||'成功加薪!');   EXCEPTION  --处理PL/SQL预定义异常  WHEN NO_DATA_FOUND THEN    DBMS_OUTPUT.PUT_LINE('没有找到员工数据'); END;   --循环结构示例，演示循环为所有员工加薪 DECLARE  --定义加薪比率常量  c_Manager CONSTANT NUMBER:=0.15;  c_SalesMan CONSTANT NUMBER:=0.12;  c_Clerk CONSTANT NUMBER:=0.10;  v_Job VARCHAR(100);                         --定义职位变量  v_EmpNo VARCHAR(20);                        --定义员工编号变量  v_Ename VARCHAR(60);                        --定义员工名称变量  CURSOR c_Emp IS SELECT job,empno,ename from emp FOR UPDATE; BEGIN  OPEN c_Emp;   --打开游标  LOOP          --循环游标    FETCH c_Emp INTO v_Job,v_EmpNo,v_Ename;   --提取游标数据    EXIT WHEN c_Emp%NOTFOUND;                 --如果无数据可提取退出游标  IF v_Job='CLERK' THEN                       --如果为职员，加薪10%    UPDATE emp SET SAL=SAL*(1+c_Clerk) WHERE CURRENT OF c_Emp;  ELSIF v_Job='SALESMAN' THEN                 --如果为销售职员，加薪12%    UPDATE emp SET SAL=SAL*(1+c_SalesMan) WHERE CURRENT OF c_Emp;  ELSIF v_Job='MANAGER' THEN                  --如果为经理，加薪15%    UPDATE emp SET SAL=SAL*(1+c_Manager) WHERE CURRENT OF c_Emp;   END IF;  --显示完成信息  DBMS_OUTPUT.PUT_LINE('已经为员工'||v_EmpNo||':'||v_Ename||'成功加薪!');  END LOOP;  CLOSE c_Emp;                --关闭游标  EXCEPTION  WHEN NO_DATA_FOUND THEN     --处理PL/SQL预定义异常    DBMS_OUTPUT.PUT_LINE('没有找到员工数据'); END;      CREATE OR REPLACE FUNCTION GetAddSalaryRatio(p_Job VARCHAR2) RETURN NUMBER AS   v_Result NUMBER(7,2); BEGIN  IF p_Job='CLERK' THEN                       --如果为职员，加薪10%    v_Result:=0.10;  ELSIF p_Job='SALESMAN' THEN                 --如果为销售职员，加薪12%    v_Result:=0.12;     ELSIF p_Job='MANAGER' THEN                  --如果为经理，加薪15%    v_Result:=0.15;  END IF;  RETURN v_Result;   END;   CREATE OR REPLACE FUNCTION GetAddSalaryRatioCASE(p_Job VARCHAR2) RETURN NUMBER AS   v_Result NUMBER(7,2); BEGIN    CASE p_Job                     --使用CASE WHEN语句进行条件判断   WHEN 'CLERK' THEN              --职员     v_Result:=0.10;   WHEN 'SALESMAN' THEN           --销售     v_Result:=0.12;   WHEN 'MANAGER' THEN            --经理     v_Result:=0.15;   END CASE;  RETURN v_Result;                --返回值 END;       BEGIN   DBMS_OUTPUT.PUT_LINE(GetAddSalaryRatio('bc')); END;