第七章 PL/SQL开发
1．
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
TYPE name_table_type IS TABLE OF VARCHAR2(16)
INDEX BY BINARY_INTEGER;
v_table name_table_type;
BEGIN
SELECT job INTO v_table(1)
FROM emp WHERE ename = 'SCOTT';
SELECT job INTO v_table(2)
FROM emp WHERE ename = 'KING';
DBMS_OUTPUT.PUT_LINE('scott''s job is ' || v_table(1));
DBMS_OUTPUT.PUT_LINE('king''s job is ' || v_table(2));
END;
/
提示：
在上述代码中：
DBMS_OUTPUT.PUT_LINE('scott''s job is ' || v_table(1));
其中scott单词后的两个单引号，用来显示一个单引号。
2．
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
dept_record dept%ROWTYPE;
begin
SELECT dname, loc
INTO dept_record.dname, dept_record.loc
FROM dept
WHERE deptno = 10;
DBMS_OUTPUT.PUT_LINE('dept 10''s name is ' || dept_record.dname);
DBMS_OUTPUT.PUT_LINE('dept 10''s loc is ' || dept_record.loc);
END;
/
3.
SQL> SET SERVEROUTPUT ON
--创建过程
SQL> CREATE OR REPLACE PROCEDURE emp_add
(p_empno IN emp.empno%TYPE,
p_ename IN emp.ename%TYPE,
p_deptno IN emp.deptno%TYPE DEFAULT 10)
IS
BEGIN
INSERT INTO emp(empno, ename, deptno)
VALUES(p_empno, p_ename, p_deptno);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE || ', ' || SQLERRM);
END;
--执行过程
SQL> exec emp_add(1234, 'peter', 20);
--检查员工记录是否被插入
SQL> SELECT * FROM emp WHERE empno = 1234;
4．
SQL> SET SERVEROUTPUT ON
--创建PROCEDURE
SQL> CREATE OR REPLACE PROCEDURE emp_find(
p_name emp.ename%TYPE)
IS
v_job emp.job%TYPE;
v_sal emp.sal%TYPE;
BEGIN
SELECT job, sal INTO v_job, v_sal
FROM emp
WHERE ename = p_name;
DBMS_OUTPUT.PUT_LINE('员工' || p_name || '的职位是' || v_job || '，薪水是' || v_sal);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('没有员工叫' || p_name);
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('有多个员工叫'|| p_name);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (' 其它异常');
END;
/
--测试PROCEDURE
SQL> exec emp_find('PETER')
没有员工叫PETER
SQL> exec emp_find('SCOTT')
员工SCOTT的职位是ANALYST，薪水是3000
5．
SQL> DECLARE
v_empno emp.empno%TYPE := &eno;
BEGIN
DELETE FROM emp WHERE empno = v_empno;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || '条记录被删除');
COMMIT;
END;
/
输入 eno 的值: 1234
1条记录被删除
--检查记录是否被删除
SQL> SELECT * FROM emp WHERE empno = 1234;
未选定行
6．
SQL> DECLARE
v_empno emp.empno%TYPE := &empno;
v_ename emp.ename%TYPE;
v_dname dept.dname%TYPE;
v_workdate NUMBER;
v_sal emp.sal%TYPE;
e_sal_toolow EXCEPTION;
BEGIN
SELECT e.ename, d.dname, e.sal,
ROUND((sysdate - e.hiredate)/365)
INTO v_ename, v_dname, v_sal, v_workdate
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.empno = v_empno;
DBMS_OUTPUT.PUT_LINE(v_ename || '员工的所在部门是' || v_dname || ', 入职时间是' || v_workdate || '年, 薪水是' || v_sal);
IF v_sal < 3000 THEN
RAISE e_sal_toolow;
END IF;
EXCEPTION
WHEN e_sal_toolow THEN
DBMS_OUTPUT.PUT_LINE(v_ename || '先生在 ' || v_dname ||
'部门白吃了' || v_workdate || '年的咸盐');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE || ', ' || SQLERRM);
END;
/
输入 empno 的值: 7788
SCOTT员工的所在部门是RESEARCH, 入职时间是27年, 薪水是3000
SQL> /
输入 empno 的值: 7369
SMITH员工的所在部门是RESEARCH, 入职时间是29年, 薪水是800
SMITH先生在 RESEARCH部门白吃了29年的咸盐
7．
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
CURSOR emp_cursor(v_sal NUMBER) IS
SELECT empno, ename, sal
FROM emp
WHERE sal >= v_sal;
emp_record emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor(2000);--查询薪水大于等于2000元的员工
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11168/viewspace-1035531/，如需转载，请注明出处，否则将追究法律责任。
<%=items[i].content%>
<%if(items[i].items.items.length) { %><%=items[i].items.items[j].username%> 回复 <%=items[i].items.items[j].tousername%>： <%=items[i].items.items[j].content%>
转载于:http://blog.itpub.net/11168/viewspace-1035531/
到【灌水乐园】发言
