oracle plsql常见操作练习

这里只是plsql练习的语句
后面有个附件，说明更加详细，这里只贴出按照附件学习的plsql代码

create table test_dept(dept_id number(11) primary key,dept_name varchar2(32));


DECLARE
Row_id UROWID;
info VARCHAR2(40);
BEGIN
INSERT INTO test_dept VALUES (100, 'design')
RETURNING rowid, dept_name||':'||to_char(dept_id)
INTO row_id, info;
DBMS_OUTPUT.PUT_LINE('ROWID:'||row_id);
DBMS_OUTPUT.PUT_LINE(info);
END;


DECLARE
Row_id UROWID;
info VARCHAR2(40);
BEGIN
update test_dept t set t.dept_name = 'product' where t.dept_name = 'design'
RETURNING rowid, dept_name||':'||to_char(dept_id)
INTO row_id, info;
DBMS_OUTPUT.PUT_LINE('ROWID:'||row_id);
DBMS_OUTPUT.PUT_LINE(info);
END;

DECLARE
Row_id UROWID;
info VARCHAR2(40);
BEGIN
delete test_dept t where t.dept_name = 'design'
RETURNING rowid, dept_name||':'||to_char(dept_id)
INTO row_id, info;
DBMS_OUTPUT.PUT_LINE('ROWID:'||row_id);
DBMS_OUTPUT.PUT_LINE(info);
END;


DECLARE
TYPE test_record IS RECORD(
Code VARCHAR2(10),
Name VARCHAR2(30) NOT NULL :='a book');
V_book test_record;
BEGIN
V_book.code :='123';
V_book.name :='C++ Programming';
DBMS_OUTPUT.PUT_LINE(v_book.code||v_book.name);
END;


DECLARE
-- 用 %TYPE 类型定义与表相配的字段
TYPE t_Record IS RECORD(
T_no test_dept.dept_id%TYPE,
T_name test_dept.dept_name%TYPE);
-- 声明接收数据的变量
v_dept t_Record;
BEGIN
SELECT dept_id, dept_name INTO v_dept FROM test_dept WHERE dept_id=1000;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_dept.t_no)||v_dept.t_name);
END;

DECLARE
v_dept_id test_dept.dept_id%TYPE :=&dept_id;
-- 用 %TYPE 类型定义与表相配的字段
TYPE t_Record IS RECORD(
T_no test_dept.dept_id%TYPE,
T_name test_dept.dept_name%TYPE);
-- 声明接收数据的变量
v_dept t_Record;
BEGIN
SELECT dept_id, dept_name INTO v_dept FROM test_dept WHERE dept_id=v_dept_id;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_dept.t_no)||v_dept.t_name);
END;

DECLARE
v_dept_id test_dept.dept_id%TYPE :=&dept_id;
rec test_dept%ROWTYPE;
BEGIN
SELECT * INTO rec FROM test_dept WHERE dept_id=v_dept_id;
DBMS_OUTPUT.PUT_LINE('部门编码:'||rec.dept_id||'部门名称:'||rec.dept_name);
END;


INSERT INTO test_dept VALUES (1001, 'humanresource');
INSERT INTO test_dept VALUES (1002, 'market');
INSERT INTO test_dept VALUES (1003, 'product');
INSERT INTO test_dept VALUES (1004, 'service');

DECLARE
TYPE dept_table_type IS TABLE OF
test_dept%ROWTYPE INDEX BY BINARY_INTEGER;
my_dept_table dept_table_type;
v_count number(2) :=4;
BEGIN
FOR id_incr IN 1 .. v_count LOOP
SELECT * INTO my_dept_table(id_incr) FROM test_dept WHERE dept_id=id_incr+1000;
END LOOP;
FOR id_incr IN my_dept_table.FIRST .. my_dept_table.LAST LOOP
DBMS_OUTPUT.PUT_LINE('Department number: '||my_dept_table(id_incr).dept_id);
DBMS_OUTPUT.PUT_LINE('Department name: '|| my_dept_table(id_incr).dept_name);
END LOOP;
END;

select * from test_dept;
alter table test_dept add salary number(11) default 100;

DECLARE
v_dept_name test_dept.dept_name%TYPE :=&dept_name;
v_salary test_dept.salary%TYPE;
BEGIN
SELECT salary INTO v_salary FROM test_dept WHERE dept_name=v_dept_name;
IF v_salary>=1500 THEN
UPDATE test_dept SET salary = salary+100 WHERE dept_name=v_dept_name;
DBMS_OUTPUT.PUT_LINE('编码为'||v_dept_name||'员工工资已更新!');
ELSE
DBMS_OUTPUT.PUT_LINE('编码为'||v_dept_name||'员工工资已经超过规定值!');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('数据库中没有编码为'||v_dept_name||'的员工');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('程序运行错误!请使用游标');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('发生其它错误!');
END;


DECLARE
v_dept_id test_dept.dept_id%TYPE :=&dept_id;
e_deptno_remaining EXCEPTION;
PRAGMA EXCEPTION_INIT(e_deptno_remaining,-00001);
/* -2292 是违反一致性约束的错误代码 */
BEGIN
insert into test_dept values(v_dept_id,'manager',5000);
EXCEPTION
WHEN e_deptno_remaining THEN
DBMS_OUTPUT.PUT_LINE('违反数据完整性约束!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('发生其它错误!');
END;


DECLARE
v_dept_name test_dept.dept_name%TYPE :=&dept_name;
no_result_exception EXCEPTION;
BEGIN
UPDATE test_dept SET salary=salary+500 WHERE dept_name=v_dept_name;
IF SQL%NOTFOUND THEN
RAISE no_result_exception;
END IF;
commit;
DBMS_OUTPUT.PUT_LINE('更新成功');
EXCEPTION
WHEN no_result_exception THEN
DBMS_OUTPUT.PUT_LINE('你的数据更新语句失败了!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('发生其它错误!');
END;


BEGIN
insert into test_dept values(2000,'buy',5000);
DBMS_OUTPUT.PUT_LINE('插入数据记录成功!');
insert into test_dept values(2000,'buy',5000);
DBMS_OUTPUT.PUT_LINE('插入数据记录成功!');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;

CREATE OR REPLACE FUNCTION test_get_salary(
p_dept_name VARCHAR2,
emp_count OUT NUMBER)
RETURN NUMBER IS
V_sum NUMBER;
BEGIN
SELECT SUM(salary), count(*) INTO V_sum, emp_count
FROM test_dept WHERE dept_name = p_dept_name;
RETURN v_sum;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('程序运行错误!请使用游标');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('发生其它错误!');
END test_get_salary;

drop function test_get_salary;

DECLARE
V_num NUMBER;
V_sum NUMBER;
BEGIN
V_sum :=test_get_salary('product', v_num);
DBMS_OUTPUT.PUT_LINE('product部门工资总和：'||v_sum||'，人数：'||v_num);
END;


DECLARE
V_num NUMBER;
V_sum NUMBER;
BEGIN
V_sum :=test_get_salary(emp_count => v_num, p_dept_name => 'product');
DBMS_OUTPUT.PUT_LINE('product部门工资总和：'||v_sum||'，人数：'||v_num);
END;


CREATE OR REPLACE PROCEDURE test_insert_dept_info IS
max_dept_id
BEGIN

INSERT INTO test_dept VALUES(3002,'department1',3000);
INSERT INTO test_dept VALUES(3002,'department1',3000);
END;


CREATE OR REPLACE PROCEDURE p_test_del_repeat_dept(
p_dept_name varchar2,
del_amount OUT NUMBER)
IS
v_max_dept_id number;
BEGIN
if p_dept_name<>'' THEN
SELECT max(dept_id), COUNT(*) INTO v_max_dept_id, del_amount
FROM test_dept WHERE dept_name = p_dept_name;
delete test_dept where dept_id = v_max_dept_id;
ELSE
SELECT max(dept_id), COUNT(*) INTO v_max_dept_id, del_amount
FROM test_dept WHERE dept_name = 'department1';
delete test_dept where dept_id = v_max_dept_id;
END IF;

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('发生其它错误!');
END p_test_del_repeat_dept;