Chapter -09 Creating Procedures 02
Using the IN、OUT、IN OUT Parameter Mode:Example
Demo 01:IN Keywords
CREATE OR REPLACE 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; / Procedure created. SQL> begin 2 raise_salary(197,10); 3 end; 4 / PL/SQL procedure successfully completed. SQL> select employee_id,salary from EMP; EMPLOYEE_ID SALARY ----------- ---------- 198 2600 199 2600 200 4400 201 13000 202 6000 203 6500 204 10000 205 12008 206 8300 100 24000 101 17000 EMPLOYEE_ID SALARY ----------- ---------- 102 17000 103 9000 104 6000 105 4800 106 4800 107 4200 108 12008 109 9000 110 8200 111 7700 112 7800 EMPLOYEE_ID SALARY ----------- ---------- 113 6900 114 11000 115 3100 116 2900 117 2800 118 2600 119 2500 120 8000 121 8200 122 7900 123 6500 EMPLOYEE_ID SALARY ----------- ---------- 124 5800 125 3200 126 2700 127 2400 128 2200 129 3300 130 2800 131 2500 132 2100 133 3300 134 2900 EMPLOYEE_ID SALARY ----------- ---------- 135 2400 136 2200 137 3600 138 3200 139 2700 140 2500 141 3500 142 3100 143 2600 144 2500 145 14000 EMPLOYEE_ID SALARY ----------- ---------- 146 13500 147 12000 148 11000 149 10500 150 10000 151 9500 152 9000 153 8000 154 7500 155 7000 156 10000 EMPLOYEE_ID SALARY ----------- ---------- 157 9500 158 9000 159 8000 160 7500 161 7000 162 10500 163 9500 164 7200 165 6800 166 6400 167 6200 EMPLOYEE_ID SALARY ----------- ---------- 168 11500 169 10000 170 9600 171 7400 172 7300 173 6100 174 11000 175 8800 176 8600 177 8400 178 7000 EMPLOYEE_ID SALARY ----------- ---------- 179 6200 180 3200 181 3100 182 2500 183 2800 184 4200 185 4100 186 3400 187 3000 188 3800 189 3600 EMPLOYEE_ID SALARY ----------- ---------- 190 2900 191 2500 192 4000 193 3900 194 3200 195 2800 196 3100 197 3300 107 rows selected.
Demo 01:IN Keywords WITH COMMIT
CREATE OR REPLACE 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; COMMIT; END raise_salary; / Procedure created. SQL> begin 2 raise_salary(197,10); 3 end; 4 / PL/SQL procedure successfully completed. SQL> select employee_id,salary from EMP; EMPLOYEE_ID SALARY ----------- ---------- 198 2600 199 2600 200 4400 201 13000 202 6000 203 6500 204 10000 205 12008 206 8300 100 24000 101 17000 EMPLOYEE_ID SALARY ----------- ---------- 102 17000 103 9000 104 6000 105 4800 106 4800 107 4200 108 12008 109 9000 110 8200 111 7700 112 7800 EMPLOYEE_ID SALARY ----------- ---------- 113 6900 114 11000 115 3100 116 2900 117 2800 118 2600 119 2500 120 8000 121 8200 122 7900 123 6500 EMPLOYEE_ID SALARY ----------- ---------- 124 5800 125 3200 126 2700 127 2400 128 2200 129 3300 130 2800 131 2500 132 2100 133 3300 134 2900 EMPLOYEE_ID SALARY ----------- ---------- 135 2400 136 2200 137 3600 138 3200 139 2700 140 2500 141 3500 142 3100 143 2600 144 2500 145 14000 EMPLOYEE_ID SALARY ----------- ---------- 146 13500 147 12000 148 11000 149 10500 150 10000 151 9500 152 9000 153 8000 154 7500 155 7000 156 10000 EMPLOYEE_ID SALARY ----------- ---------- 157 9500 158 9000 159 8000 160 7500 161 7000 162 10500 163 9500 164 7200 165 6800 166 6400 167 6200 EMPLOYEE_ID SALARY ----------- ---------- 168 11500 169 10000 170 9600 171 7400 172 7300 173 6100 174 11000 175 8800 176 8600 177 8400 178 7000 EMPLOYEE_ID SALARY ----------- ---------- 179 6200 180 3200 181 3100 182 2500 183 2800 184 4200 185 4100 186 3400 187 3000 188 3800 189 3600 EMPLOYEE_ID SALARY ----------- ---------- 190 2900 191 2500 192 4000 193 3900 194 3200 195 2800 196 3100 197 3300 107 rows selected.
DEMO 02:OUT Define
CREATE OR REPLACE PROCEDURE query_emp ( p_id IN emp.employee_id%TYPE, p_name OUT emp.last_name%TYPE, p_salary OUT emp.salary%TYPE ) IS BEGIN SELECT last_name,salary INTO p_name,p_salary FROM emp WHERE employee_id = p_id; END query_emp; /
DEMO 02:OUT CALL
DECLARE v_emp_name emp.last_name%TYPE; v_emp_sal emp.salary%TYPE; BEGIN query_emp(197,v_emp_name,v_emp_sal); DBMS_OUTPUT.PUT_LINE(v_emp_name || ' earns ' || TO_CHAR(v_emp_sal,'$999,999.00')); END; /
DEMO 03:IN OUT Define
CREATE OR REPLACE PROCEDURE format_phone ( p_phone_no IN OUT VARCHAR2 ) IS BEGIN p_phone_no := '(' || SUBSTR(p_phone_no,1,3) || ')' || SUBSTR(p_phone_no,4,4) || '-' || SUBSTR(p_phone_no,8); END format_phone; /
DEMO 03:IN OUT CALL
DECLARE v_formated_phone_no VARCHAR2(16) :='01082375914'; BEGIN format_phone(v_formated_phone_no); DBMS_OUTPUT.PUT_LINE('The formated phone no is ' || v_formated_phone_no); END; /
DEMO 03:IN OUT CALL WITH VARIABLE
VARIABLE b_phone_no VARCHAR2(16) EXECUTE :b_phone_no := '01082375915' PRINT b_phone_no EXECUTE format_phone(:b_phone_no) PRINT b_phone_no --plsql block --call SQL> @s9_3_call_01 PL/SQL procedure successfully completed. B_PHONE_NO -------------------------------------------------------------------------------- 01082375915 PL/SQL procedure successfully completed. B_PHONE_NO -------------------------------------------------------------------------------- (010)8237-5915
Viewing OUT Parameters:Using SQL*PLUS Host Variables
1、Use SQL*Plus host variables.
2、Execute QUERY_EMP using host variable.
3、Print the host variables.
Parameter Passing Methods
The PL/SQL compiler has two ways of passing an actural parameter to a subprogram:
- by reference:The compiler passes the subprogram a pointer to the actual parameter.The actual and formal parameters refer to the same memory location.
- by value:The compiler assigns the value of the actual parameter to the corresponding formal parameter.The actual and fromal parameter refer to different memory locations.
IN->by reference(按照正常思维，容易混淆)
OUT 、IN OUT ->by value(按照正常思维，容易混淆)
Available Notations for Passing Actual Parameters
- When calling a subprogram,you can write the actual parameters using the following notations:
- -Postional:Lists the actual parameters in the same order as the formal parameters
- -Named:Lists the actual parameters in arbitrary order and uses the association operator(=>) to associate a named formal parameter with its acutal parameter
- -Mixed:Lists some of the acutal parameters as positional and some as named
- Prior to Oracle Database 11g,only the postional notation is supported in calls from SQL
- Starting in Oracle Database 11g,named and mixed notation can be used for sepecifying arguments in calls to PL/SQL subroutines from SQL statements.
