精通PLSL学习笔记.docVIP

精通PLSQL学习笔记 --建会员表 CREATE TABLE EMP ( ENO NUMBER primary key, ENAME VARCHAR2(200), DEPT VARCHAR2(200), PAY NUMBER(6,2) ) --建部门表 create table dept(deptno number not null, deptname varchar2(100 char), constraint dept_pk primary key (deptno) ) declare v_ename varchar2(200 char); begin select ename into v_ename from emp where eno=no; dbms_output.put_line(雇员名是：||v_ename); EXCEPTION when no_data_found then dbms_output.put_line(请输入正确的雇员号); end; --匿名块 declare avlpay number(6,2); begin select avg(pay) into avlpay from emp where eno=no; dbms_output.put_line(平均工资为：||avlpay); EXCEPTION when no_data_found then dbms_output.put_line(请输入正确的员工号); end; --命名块 outer declare v_pay number(6,2); v_eno number; begin inner begin select eno into v_eno from emp where ename=name; end;--inner select pay into v_pay from emp where eno=v_eno; dbms_output.put_line(vpay); end;--outer ----------------------------------------------------------------------------------------------过程 create or replace procedure upemp(e_name varchar2,e_pay number) is begin update emp set pay=e_pay where lower(ename)=lower(e_name); end; ----------------------------------------------------------------------------------------------函数 create or replace function annual_income(name varchar2) return number is aunual_salary number(6,2); begin select pay into aunual_salary from emp where ename=name; return aunual_salary; end; ----------------------------------------------------------------------------------------------包 --纯度级别：WNDS限制函数不能修改数据库数据；WNPS用于限制函数不能修改包变量；RNDS限制函数不能读取数据库数据；RNPS限制函数不能读取包变量 --语法：PRAGMA RESTRICT_REFERENCES(function_name,WNDS[,WNPS][,RNDS][,RNPS]) --包规范，只包含了过程和函数的说明 create package emp_pkg is procedure upemp(e_name varchar2,e_pay number); function annual_income(na