Creation and use of 14.pl_sql--storeprocedures

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/49/52/wKioL1QS_2qwsnYBAAG6mVew9HQ945.jpg "style=" float: none; "title=" 1.png "alt=" Wkiol1qs_2qwsnybaag6mvew9hq945.jpg "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/49/50/wKiom1QS_1qzZ7WwAAEBN24jxss450.png "style=" float: none; "title=" 2.PNG "alt=" Wkiom1qs_1qzz7wwaaebn24jxss450.png "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/49/52/wKioL1QS_2yCHLhQAAGQdnXLguc375.jpg "style=" float: none; "title=" 3.png "alt=" Wkiol1qs_2ychlhqaagqdnxlguc375.jpg "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/49/50/wKiom1QS_1yQ3GdWAAHzFCYYxFk761.png "style=" float: none; "title=" 4.PNG "alt=" Wkiom1qs_1yq3gdwaahzfcyyxfk761.png "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/49/50/wKiom1QS_1yyN4cAAAIRp8o5iRg550.png "style=" float: none; "title=" 5.PNG "alt=" Wkiom1qs_1yyn4caaairp8o5irg550.png "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/49/50/wKiom1QS_12zr9lMAAJEXgMhI6c441.jpg "style=" float: none; "title=" 6.png "alt=" Wkiom1qs_12zr9lmaajexgmhi6c441.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/49/52/wKioL1QS_2_i2k4UAAHApPNLrs4745.jpg "style=" float: none; "title=" 7.png "alt=" Wkiol1qs_2_i2k4uaahappnlrs4745.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/49/52/wKioL1QS_3Dz37ckAAI6qdJvOQs550.jpg "style=" float: none; "title=" 8.png "alt=" Wkiol1qs_3dz37ckaai6qdjvoqs550.jpg "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/49/50/wKiom1QS_1_xWQruAAGkoTRRLLQ579.png "style=" float: none; "title=" 9.PNG "alt=" Wkiom1qs_1_xwqruaagkotrrllq579.png "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/49/50/wKiom1QS_2DBxU1PAAL7mAgu-9U645.png "style=" float: none; "title=" 10.PNG "alt=" Wkiom1qs_2dbxu1paal7magu-9u645.png "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/49/52/wKioL1QS_3LSbYCzAAHSziuswgU193.png "style=" float: none; "title=" 11.PNG "alt=" Wkiol1qs_3lsbyczaahsziuswgu193.png "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/49/50/wKiom1QS_2LgbPNWAAGn3AHf7Kg241.png "style=" float: none; "title=" 12.PNG "alt=" Wkiom1qs_2lgbpnwaagn3ahf7kg241.png "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/49/52/wKioL1QS_3OBIzz9AAEtDVc8wdc123.png "style=" float: none; "title=" 13.PNG "alt=" Wkiol1qs_3obizz9aaetdvc8wdc123.png "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/49/50/wKiom1QS_2PyuH90AAIpW2AO1ns673.png "style=" float: none; "title=" 14.PNG "alt=" Wkiom1qs_2pyuh90aaipw2ao1ns673.png "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/49/52/wKioL1QS_3ThUrZjAAHBETzfpmM855.png "style=" float: none; "title=" 15.PNG "alt=" Wkiol1qs_3thurzjaahbetzfpmm855.png "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/49/52/wKioL1QS_3XQWtRIAAIFJCmR8sI822.png "style=" float: none; "title=" 16.PNG "alt=" Wkiol1qs_3xqwtriaaifjcmr8si822.png "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/49/50/wKiom1QS_2XR3PwbAAH2C4L054I174.png "style=" float: none; "title=" 17.PNG "alt=" Wkiom1qs_2xr3pwbaah2c4l054i174.png "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/49/52/wKioL1QS_3eTPuonAAHSmmPD1n4351.png "style=" float: none; "title=" 18.PNG "alt=" Wkiol1qs_3etpuonaahsmmpd1n4351.png "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/49/50/wKiom1QS_2ejbV0KAAH_zh2ckIA744.png "style=" float: none; "title=" 19.PNG "alt=" Wkiom1qs_2ejbv0kaah_zh2ckia744.png "/>

================example 1===================

Sql> SELECT * from Emp_tmp

2;

employee_id salarycommission_pct

----------- ---------- --------------

301 2500 0

302 25.1

[Email protected] notes]$ vim S72.sql

CREATE OR REPLACE PROCEDURE raise_salary

(p_id Inemployees.employee_id%type,

P_percent Innumber)

Is

BEGIN

UPDATE emp_tmp

SET Salary =salary * (1 + p_percent/100)

WHERE employee_id= p_id;

COMMIT;

END raise_salary;

/

Sql> @notes/s72.sql

Procedure created.

Sql> BEGIN raise_salary (302, 100);

2 END;

3/

PL/SQL procedure successfully completed.

Sql> select * from Emp_tmp;

employee_id salarycommission_pct

----------- ---------- --------------

301 2500 0

302 50.1

=================example 2=====================

[Email protected] notes]$ vim S73.sql

CREATE OR REPLACE PROCEDURE query_emp

(p_id in Employees.employee_id%type,

P_name out Employees.last_name%type,

P_salary Outemployees.salary%type) is

BEGIN

SELECT last_name,salary into P_name, p_salary

From Employees

WHERE employee_id= p_id;

END query_emp;

/

[Email protected] notes]$ vim S73_1.sql

SET Serveroutput on

DECLARE

V_emp_nameemployees.last_name%type;

V_emp_sal Employees.salary%type;

BEGIN

Query_emp (171,v_emp_name, v_emp_sal);

Dbms_output. Put_Line (V_emp_name | | ' Earns ' | | To_char (v_emp_sal, ' $999,999.00 '));

END;

/

Sql> @notes/s73.sql

Procedure created.

Sql> @notes/s73_1.sql

Smith earns $7,400.00

PL/SQL procedure successfully completed.

Sql> Select Last_Name, salary from Employees whereemployee_id = 171;

Last_Name SALARY

------------------------- ----------

Smith 7400

==============example 3=================

[Email protected] notes]$ vim S74.sql

SET Serveroutput on

CREATE OR REPLACE PROCEDURE format_phone

(P_phone_no in OUTVARCHAR2) Is

BEGIN

P_phone_no: = ' (' | | SUBSTR (P_phone_no, 1, 3) | |

') ' | | SUBSTR (P_phone_no, 4, 3) | |

'-' | | SUBSTR (P_phone_no, 7);

END Format_phone;

/

[Email protected] notes]$ vim S74_1.sql

VARIABLE b_phone_no VARCHAR2 (15)

Execute:b_phone_no: = ' 8006330575 '

PRINT B_phone_no

EXECUTE Format_phone (: b_phone_no)

PRINT B_phone_no

Sql> @notes/s74.sql

Procedure created.

Sql> @notes/s74_1.sql

PL/SQL procedure successfully completed.

B_phone_no

--------------------------------

8006330575

PL/SQL procedure successfully completed.

B_phone_no

--------------------------------

(800) 633-0575

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/49/52/wKioL1QTAJ7SZyHeAAHF-NvIITE117.png "style=" float: none; "title=" 21.PNG "alt=" Wkiol1qtaj7szyheaahf-nviite117.png "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/49/51/wKiom1QTAI-h7lpkAAIznY_7zF8917.png "style=" float: none; "title=" 22.PNG "alt=" Wkiom1qtai-h7lpkaaizny_7zf8917.png "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/49/51/wKiom1QTAJDRSOMdAALzm2iaNqA866.png "style=" float: none; "title=" 23.PNG "alt=" Wkiom1qtajdrsomdaalzm2ianqa866.png "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/49/52/wKioL1QTAKLCsc74AAHMyG1z1v0633.png "style=" float: none; "title=" 24.PNG "alt=" Wkiol1qtaklcsc74aahmyg1z1v0633.png "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/49/51/wKiom1QTAJKQqy-dAAILz8OUIrs503.png "style=" float: none; "title=" 25.PNG "alt=" Wkiom1qtajkqqy-daailz8ouirs503.png "/>

This article is from the "Big sword without front of the great Qiao Not Work" blog, please make sure to keep this source http://wuyelan.blog.51cto.com/6118147/1551785

Creation and use of 14.pl_sql--storeprocedures