Chapter -09 Creating Procedures 04
Exceptions Not Handled
Calling Path
Some Procedures Demo
可参考脚本:192.168.1.200
[oracle@DG1 scripts]$ pwd /u01/oracle/scripts [oracle@DG1 scripts]$ ll total 60 -rw-r--r-- 1 oracle oinstall 886 Apr 19 15:35 call_calculate_blance.sql -rw-r--r-- 1 oracle oinstall 220 Apr 19 14:26 s09_01.sql -rw-r--r-- 1 oracle oinstall 205 Apr 19 14:38 s9_2_call.sql -rw-r--r-- 1 oracle oinstall 247 Apr 19 14:36 s9_2.sql -rw-r--r-- 1 oracle oinstall 138 Apr 19 15:37 s9_3_call_01.sql -rw-r--r-- 1 oracle oinstall 185 Apr 19 14:46 s9_3_call.sql -rw-r--r-- 1 oracle oinstall 233 Apr 19 14:48 s9_3.sql -rw-r--r-- 1 oracle oinstall 256 Apr 19 17:15 s9_4.sql -rw-r--r-- 1 oracle oinstall 297 Apr 19 21:24 s9_5.sql -rw-r--r-- 1 oracle oinstall 222 Apr 19 21:27 s9_6.sql -rw-r--r-- 1 oracle oinstall 198 Apr 19 21:52 s9_7.sql -rw-r--r-- 1 oracle oinstall 615 Apr 19 22:30 s9_8.sql -rw-r--r-- 1 oracle oinstall 817 Apr 20 12:48 s9_9_01.sql -rw-r--r-- 1 oracle oinstall 688 Apr 20 13:03 s9_9_02.sql -rw-r--r-- 1 oracle oinstall 713 Apr 20 12:43 s9_9.sql
Removing Procedures:Using the DROP SQL Statement
Using the DROP statement:
DROP PROCEDURE raise_salary;
Viewing Procedure Information Using the Data Dictionary Views
SQL> desc user_source; Name Null? Type ----------------------------------------- -------- ---------------------------- NAME VARCHAR2(30) TYPE VARCHAR2(12) LINE NUMBER TEXT VARCHAR2(4000) SQL> SELECT text FROM user_source WHERE name = 'RAISE_SALARY' AND TYPE = 'PROCEDURE' ORDER BY line; TEXT -------------------------------------------------------------------------------- PROCEDURE raise_salary ( p_id IN emp.employee_id%TYPE, p_percent IN NUMBER ) IS BEGIN UPDATE emp SET salary = salary * (1 + p_percent/100) WHERE employee_id = p_id; END raise_salary; 11 rows selected.
Summary
In this lesson,you should have learned how to:
- Identify the benefits of modularized and layered subprogram design.
- Create and call procedures
- Use formal and actual parameters
- Use positional,named,or mixed notation for passing parameters.
- Identify the available parameter-passing modes.
- Handle exceptions in procedures
- Remove a procedure
- Display the procedure`s information.