首页 > 代码库 > Orcle基本语句(六)

Orcle基本语句(六)

  1 -- Created on 2017/1/5 by ADMINISTRATOR 
  2 DECLARE
  3   -- Local variables here
  4   i        INTEGER := 12;
  5   v_name   VARCHAR(15) := 张晓风;
  6   v_age    NUMBER(3) := 28;
  7   v_sex    VARCHAR(4) := ;
  8   v_height NUMBER(3) := 150;
  9   v_age1   NUMBER(3);
 10 
 11 BEGIN
 12   v_age1 := 3;
 13   -- Test statements here
 14   dbms_output.put_line(我叫 || v_name || , );
 15   dbms_output.put_line(今年 || v_age || , );
 16   dbms_output.put_line(性别 || v_sex || , );
 17   dbms_output.put_line(身高 || v_height || , );
 18 END;
 19 
 20 --给100号员工的工资加1
 21 DECLARE
 22   v_sal1 number;
 23   v_sal2 number:=1;
 24 BEGIN
 25   SELECT salary INTO v_sal1 FROM employees WHERE employee_id = 100;
 26   UPDATE employees SET salary = (v_sal1 + v_sal2) WHERE employee_id =100;
 27   COMMIT;
 28 END;
 29 
 30 SELECT ename, sal INTO v_ename, v_sal FROM emp WHERE empno = 7369;
 31 --查询员工编号为7369号的员工信息,列包括:姓名,薪资
 32 DECLARE
 33   v_empid NUMBER(5) := 7369;
 34   v_name  emp.ename%TYPE;
 35   v_sal   emp.sal%TYPE;
 36 BEGIN
 37   SELECT ename, sal INTO v_name, v_sal FROM emp WHERE empno = v_empid;
 38   dbms_output.put_line(名字: || v_name || ,薪资: || v_sal);
 39 END;
 40 --向表new_dept插入数据,部门编号为50,部门名称chinasofti,地址:zz
 41 CREATE TABLE new_dept AS SELECT * FROM dept;
 42 DECLARE
 43     v_deptno new_dept.deptno%TYPE := 50;
 44     v_dname  new_dept.dname%TYPE := chinasofti;
 45     v_loc    new_dept.loc%TYPE := zz;
 46 BEGIN
 47   INSERT INTO new_dept VALUES(v_deptno, v_dname, v_loc);
 48   COMMIT;
 49   END;
 50  /*录入一个数字,
 51   如果大于0小于100输出大于0小于100,
 52   如果大于0大于100输出大于0大于100
 53   如果小于0输出 小于0
 54 */
 55 DECLARE
 56   v_test number(6):=&input;
 57 BEGIN
 58   IF v_test >=0 AND v_test <=100 THEN
 59     dbms_output.put_line(输入的值在0和100之间);
 60   ELSIF v_test > 100 THEN
 61     dbms_output.put_line(输入的值大于100);
 62   ELSE 
 63     dbms_output.put_line(输入的值小于0);
 64   END IF;
 65   END;
 66 -- --输入一个员工编号,判断员工工资等级(输出员工编号,姓名,工资,工资等级)
 67 --如果员工不存在,那么提示"输入的员工不存在"
 68 /*
 69 >=15000 A
 70 >=10000 B
 71 >=5000  C
 72 <5000   D
 73 */
 74 DECLARE
 75   e        employees%ROWTYPE; --表对象
 76   v_level  CHAR(1); --工资级别
 77   v_count  BINARY_INTEGER; --存放员工是否存在0不存在1存在 
 78 BEGIN
 79   e.employee_id := &input;
 80   SELECT COUNT(e.employee_id)
 81     INTO v_count
 82     FROM employees
 83    WHERE employee_id = e.employee_id;
 84   IF (v_count <= 0) THEN
 85     dbms_output.put_line(员工不存在);
 86   ELSE
 87     SELECT *
 88       INTO e
 89       FROM employees
 90      WHERE employee_id = e.employee_id;
 91     IF e.salary >= 15000 THEN
 92       v_level := A;
 93     ELSIF e.salary >= 10000 THEN
 94       v_level := B;
 95     ELSIF e.salary >= 5000 THEN
 96       v_level := c;
 97     ELSE
 98       v_level := D;
 99     END IF;
100      dbms_output.put_line(员工编号: || e.employee_id || ,姓名: || e.last_name || ,工资: || e.salary ||,工资等级: ||  v_level);
101   END IF;
102 END;
103 
104 --********************--
105 
106 declare  
107   e employees%rowtype;--表对象
108   v_level char(1);--工资级别
109   v_count binary_integer;--存放员工是否存在0不存在1存在 
110   
111 begin
112       e.employee_id:=&input;        
113       select count(employee_id)  into v_count from employees where employee_id= e.employee_id;
114       if(v_count<=0)
115              then 
116                   dbms_output.put_line(员工不存在);                               
117                   return;--退出
118       end if;
119       --接下来plsql块
120       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;
121       dbms_output.put_line(----: || e.salary);
122       if(e.salary>=15000) then
123          v_level:=A;
124       elsif(e.salary>=10000) then
125          v_level:=B;
126       elsif(e.salary>=5000) then
127          v_level:=C;
128       else
129          v_level:=D;
130       end if;
131       --输出员工编号,姓名,工资,工资等级
132       dbms_output.put_line(员工编号: || e.employee_id || ,姓名: || e.last_name || ,工资: || e.salary ||,工资等级: ||  v_level);
133   end;
134 --case语句
135 case 表达式值
136   when 值1 THEN
137     语句块1;
138   when 值2 THEN
139     语句块2;
140  end case;
141     
142 --输入ABCD,输入 A优秀 B良好  C一般  D差劲
143 DECLARE
144   v_char VARCHAR2(1) := &input;
145 BEGIN
146     CASE v_char
147     WHEN A THEN
148       dbms_output.put_line(优秀);
149     WHEN B THEN
150       dbms_output.put_line(良好);
151     WHEN C THEN
152       dbms_output.put_line(一般);
153     WHEN D THEN
154       dbms_output.put_line(差劲);
155   END CASE;
156 END;
157 --loop循环使用
158 DECLARE
159   v_i BINARY_INTEGER := 0;
160 BEGIN
161   LOOP
162     IF v_i > 10 THEN
163       EXIT;
164     END IF;
165     v_i := v_i + 1;
166     dbms_output.put_line( || v_i || 次循环);
167   END LOOP;
168   dbms_output.put_line(循环结束);
169 END;
170 
171 --while循环使用
172 DECLARE
173   v_i BINARY_INTEGER := 0;
174 BEGIN
175   WHILE v_i < 10 LOOP
176     dbms_output.put_line(hello || v_i);
177     v_i := v_i + 1;
178   END LOOP;
179   dbms_output.put_line(over);
180 END;
181 
182 --for循环正序
183 DECLARE
184 BEGIN
185   FOR v_i IN 0 .. 10 LOOP
186     dbms_output.put_line(hello || v_i);
187   END LOOP;
188   dbms_output.put_line(over);
189 END;
190 --for循环倒序
191 DECLARE
192 BEGIN
193   FOR v_i IN REVERSE 0 .. 10 LOOP
194     dbms_output.put_line(hello || v_i);
195   END LOOP;
196   dbms_output.put_line(over);
197 END;
198 --循环插入10行数据
199 CREATE TABLE books(
200   author varchar2(20),
201   book number(4)
202 );
203 --FOR循环
204 DECLARE
205   v_name VARCHAR2(12) := Jhon;
206 BEGIN
207   FOR v_i IN  1 .. 10 LOOP
208     INSERT INTO books VALUES (v_name || v_i, v_i);
209     COMMIT;
210   END LOOP;
211 END;
212 --WHILE循环
213 DECLARE
214   v_name VARCHAR2(12) := Jhon;
215   v_i BINARY_INTEGER := 1;
216 BEGIN
217   WHILE v_i <= 10 LOOP
218     INSERT INTO books VALUES (v_name || v_i, v_i);
219     COMMIT;
220     v_i := v_i + 1;
221   END LOOP;
222 END;
223 --loop循环
224 DECLARE
225   v_name VARCHAR2(12) := Jhon;
226   v_i    BINARY_INTEGER := 1;
227 BEGIN
228   LOOP
229     IF v_i > 10 THEN
230       EXIT;
231     END IF;
232     INSERT INTO books VALUES (v_name || v_i, v_i);
233     COMMIT;
234     v_i := v_i + 1;
235   END LOOP;
236 END;

 

Orcle基本语句(六)