13 plsql与存储过程、触发器.ppt

* * 变量作用范围： SQL var xm char(20) SQL Begin 2 select sname into :xm from student 3 where sno=95001; 4 dbms_output.put_line(:xm); 5 End; 6 / SQL exec dbms_output.put_line(:xm) * 1. DECLARE e_deptno emp.deptno%type; d_dname dept.dname%type; e_ename emp.ename%type; BEGIN SELECT dname INTO d_dname FROM emp join dept ON emp.deptno=dept.deptno WHERE emp.empno=7934; CASE WHEN d_dname=ACCOUNTING THEN d_dname:=财务部; WHEN d_dname =RESEARCH THEN d_dname:=开发部; WHEN d_dname =SALES THEN d_dname:=销售部; WHEN d_dname =OPERATIONS THEN d_dname:=项目部; ELSE d_dname:=没有这样的部门。; END CASE; DBMS_OUTPUT.PUT_LINE(d_dname); END; 2. DECLARE e_deptno emp.deptno%type; d_dname dept.dname%type; e_ename emp.ename%type; BEGIN SELECT CASE dname WHEN ACCOUNTING THEN 财务部 WHEN RESEARCH THEN 开发部 WHEN SALES THEN 销售部 WHEN OPERATIONS THEN 项目部 ELSE 没有这样的部门。 END CASE INTO d_dname FROM emp join dept ON emp.deptno=dept.deptno WHERE emp.empno=7934; DBMS_OUTPUT.PUT_LINE(d_dname); END; * * * CREATE OR REPLACE PROCEDURE gzupdate( pjob emp.job%TYPE, pbl emp.sal%TYPE) AS BEGIN update emp set sal=sal*(1+pbl/100.0) WHERE emp.job=pjob; END; exec gzupdate(CLERK,50) * CREATE OR REPLACE PROCEDURE tj( pcname ame%TYPE, rs90 OUT number, rs6089 out number, rs60 out number) AS cursor c is select grade from sc join course on o=o where cname=pcname; t sc.grade%type; BEGIN open c ; rs90:=0; rs6089:=0; rs60:=0; fetch c into t; while c%found loop case when (t=90) then rs90:=rs90+1; when (t60 ) then rs60:=rs60+1; else rs6089:=rs6089+1; end case; fetch c into t; end loop; close c ; dbms_output.put_line(90以上：||rs90|| 60~89：||rs6089|| 69以下：||rs60); END; / VARIABL rs9 number; VARIABL rs69 number; VARIABL rs6 number; EXECUTE tj(数据jg,:rs9, :rs69,:rs6); PRINT :rs9 :rs69 :rs6 ; * * create or replace procedure p2