-- 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;