程序包
1-pack_body_emp 、exec_package
SQL> CREATE OR REPLACE PACKAGE pack_emp IS2 FUNCTION fun_avg_sal(num_deptno NUMBER) RETURN NUMBER;34 PROCEDURE pro_regulate_sal(var_job VARCHAR2,num_proportion NUMBER);56 END pack_emp;7 /程序包已创建。
SQL> CREATE OR REPLACE PACKAGE BODY pack_emp IS2 FUNCTION fun_avg_sal(num_deptno NUMBER) RETURN NUMBER IS3 num_avg_sal NUMBER;4 BEGIN5 SELECT AVG(sal) INTO num_avg_sal6 FROM emp7 WHERE deptno=num_deptno;8 RETURN (num_avg_sal);9 EXCEPTION10 WHEN no_data_found THEN11dbms_output.put_line('该部门不存在雇员记录');12RETURN 0;13 END fun_avg_sal;1415 PROCEDURE pro_regulate_sal(var_job VARCHAR2,num_proportion NUMBER) IS16 BEGIN17 UPDATE emp SET sal=sal*(1+num_proportion)18 WHERE job=var_job;19 END pro_regulate_sal;20 END pack_emp;21 /程序包体已创建。
//执行程序包SQL> declare2num_deptno emp.deptno%type;3var_job emp.job%type;4num_avg_sal emp.sal%type;5num_proportion number;6 begin7num_deptno:=10;8num_avg_sal:=pack_emp.fun_avg_sal(num_deptno);9dbms_output.put_line(num_deptno||'号部门的平均工资是：'||num_avg_sal);1011var_job:='SALESMAN';12num_proportion:=0.1;13pack_emp.pro_regulate_sal(var_job,num_proportion);14 end;15 /
触发器
2-[触发器] 语句级触发器：
SQL> create table dept_log(operate_tag varchar2(10),operate_time date);表已创建。SQL> create or replace trigger tri_dept2 before insert or update or delete3 on dept4 declare5 var_tag varchar2(10);6 begin7 if inserting then8var_tag:='插入';9 elsif updating then10var_tag:='修改';11 elsif deleting then12var_tag:='删除';13 end if;14 insert into dept_log values(var_tag,sysdate);15 end tri_dept;16 /触发器已创建
SQL> insert into dept values(66,'咨询'，'长春');已创建 1 行。SQL> commit;提交完成。SQL> select * from dept_log;OPERATE_TA OPERATE_TIME---------- --------------插入 09-7月 -20
3-[触发器]tri_insert_good 行级触发器，自动创建主键值.
SQL> create table goods2 (3 id int primary key,4 good_name varchar2(50)5 );表已创建。SQL> create sequence seq_id;序列已创建。
SQL> create or replace trigger tri_insert_good2 before insert3 on goods4 for each row5 begin6 select seq_id.nextval7 into :new.id8 from dual;9 end;10 /触发器已创建
SQL> insert into goods(good_name)values('苹果');已创建 1 行。SQL> insert into goods(good_name)values('葡萄');已创建 1 行。SQL> select * from goods;ID GOOD_NAME---------- --------------------------------------------------1 苹果2 葡萄
函数
4-[函数]create_func 代码手敲并调用函数 get_agv_pay…最后删除函数
SQL> create or replace function get_avg_pay(num_deptno number) return number is --创建一个函数，计算某个部门的平均工资2 num_avg_pay number; --定义临时变量，保存某部门平均工资3 begin4 select avg(sal) into num_avg_pay from emp where deptno=num_deptno;--获取某部门平均工资5 return(round(num_avg_pay,2));--返回平均工资6 exception7 when no_data_found then8dbms_output.put_line('该部门编号不存在！');9return 0;10 end;11 /函数已创建。
//调用函数SQL> set serveroutput onSQL> declare2 avg_pay number;3 begin4 avg_pay:=get_avg_pay(10);--调用get_avg_pay()函数5 dbms_output.put_line(avg_pay);6 end;7 /2916.67PL/SQL 过程已成功完成。
//删除函数SQL> drop function get_avg_pay;函数已删除。
存储过程
5-[存储过程]—无参存储过程 create_procedure…pro_insertDept
SQL> create or replace procedure pro_insertDept is2 begin3 insert into dept values(77,'市场拓展部','jilin');4 commit;5 dbms_output.put_line('插入新纪录成功！');6 end pro_insertDept;7 /过程已创建。
6-show error 在sqlplus中可以显示编译错误么？
可以
SQL> create or replace procedure pro_insertDep1 is2 begin3 insert into dept values(77,'市场拓展部','jilin');4 commit;5 dbms_output.put_line('插入新纪录成功！');6 end pro_insertDept;7 /警告: 创建的过程带有编译错误。SQL> show errorPROCEDURE PRO_INSERTDEP1 出现错误:LINE/COL ERROR-------- -----------------------------------------------------------------6/5PLS-00113: END 标识符 'PRO_INSERTDEPT' 必须同 'PRO_INSERTDEP1'匹配 (在第 1 行, 第 11 列)
// 调用存储过程SQL> set serveroutput onSQL> begin2 pro_insertDept;3 end;4 /插入新纪录成功！PL/SQL 过程已成功完成。
7-[存储过程] in_pro…insert_dept代码调用.带入参存储过程.
SQL> create or replace procedure insert_dept(2 num_deptno in number,3 var_ename in varchar2,4 var_loc in varchar25 ) is6 begin7 insert into dept8 values(num_deptno,var_ename,var_loc);9 commit;10 end insert_dept;11 /过程已创建。
SQL> begin2 insert_dept(var_ename=>'采购部',var_loc=>'成都',num_deptno=>15);3 end;4 /PL/SQL 过程已成功完成。
SQL> select *from dept;DEPTNO DNAMELOC---------- -------------- -------------66 咨询 长春77 市场拓展部jilin15 采购部 成都10 ACCOUNTINGNEW YORK20 RESEARCH DALLAS30 SALESCHICAGO40 OPERATIONSBOSTON88 design beijing已选择8行。
8-[存储过程]pro_out…select_dept代码手敲并调用.带入参+返参存储过程.
SQL> create or replace procedure select_dept(2 num_deptno in number,3 var_dname out dept.dname%type,4 var_loc out dept.loc%type5 ) is6 begin7 select dname,loc8 into var_dname,var_loc9 from dept10 where deptno=num_deptno;11 exception12 when no_data_found then13dbms_output.put_line('该部门编号不存在！');14 end select_dept;15 /过程已创建。
SQL> set serveroutput onSQL> declare2 var_dname dept.dname%type;3 var_loc dept.loc%type;4 begin5select_dept(50,var_dname,var_loc);6dbms_output.put_line(var_dname||'位于:'||var_loc);7 end;8 /IT位于:HEFEIPL/SQL 过程已成功完成。
9-[存储过程]default_value… insert_dept 带默认值入参存储过程,插入数据。
SQL> create or replace procedure insert_dept(2 num_deptno in number,3 var_dname in varchar2 default '综合部',4 var_loc in varchar2 default '北京')is5 begin6 insert into dept values(num_deptno,var_dname,var_loc);7 end;8 /过程已创建。
SQL> begin2 insert_dept(90);3 end;4 /PL/SQL 过程已成功完成。
SQL> select * from dept;DEPTNO DNAMELOC---------- -------------- -------------66 咨询 长春77 市场拓展部jilin15 采购部 成都50 IT HEFEI10 ACCOUNTINGNEW YORK20 RESEARCH DALLAS30 SALESCHICAGO40 OPERATIONSBOSTON88 design beijing90 综合部 北京已选择10行。
