Simple Application of Stored Procedures

Simple Application of Stored Procedures

Stored Procedure: executes a task that contains a series of pl SQL statements stored in the database and becomes an object of the database. The efficiency is relatively high, but when you create a stored procedure, it will make a judgment and compile.

==========================================

SQL> CREATE OR REPLACE PROCEDURE xs_proc
2 IS
3 BEGIN
4 NULL;
5 END;
6/
Procedure created.

SQL> EXECUTE xs_proc;
PL/SQL procedure successfully completed.

Or execute

SQL> BEGIN
2 xs_proc;
3 END;
4/

PL/SQL procedure successfully completed.

======================================

SQL> CREATE OR REPLACE PROCEDURE xs_proc
2 IS
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE ('hello ');
5 END;
6/

SQL> EXECUTE xs_proc;

Hello will be displayed only when it is set to ON
SQL> SET SERVEROUTPUT ON

SQL> EXECUTE xs_proc;

========================================================== ============

Create table xue_sheng (id integer, xing_ming varchar (25), yu_wen number, shu_xue number );

Insert into xue_sheng VALUES (1, 'zhansan', 80, 90 );

Insert into xue_sheng VALUES (2, 'lisi );

========================================================== ============

SQL> CREATE OR REPLACE PROCEDURE xs_proc (temp_id IN integer)
2 IS
3 name varchar2 (25 );
4 BEGIN
5 select xing_ming into name from xue_sheng where id = temp_id;
6 DBMS_OUTPUT.PUT_LINE (name );
7 END;
8/

SQL> execute xs_proc (1 );
ZhanSan

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

When you enter the Student name, the total score (Chinese + mathematics) is displayed.

Create or replace procedure xs_proc (temp_name IN varchar2)
IS
Num_1 number;
Num_2 number;
BEGIN
Select yu_wen, shu_xue into num_1, num_2 from xue_sheng where xing_ming = temp_name;
DBMS_OUTPUT.PUT_LINE (num_1 + num_2 );
END;
/

SQL> EXECUTE xs_proc ('zhansan ');
170

========================================================== ================

SQL> CREATE OR REPLACE PROCEDURE xs_proc (temp_name IN varchar2, temp_num OUT number)
2 IS
3 num_1 number;
4 num_2 number;
5 BEGIN
6 select yu_wen, shu_xue into num_1, num_2 from xue_sheng where xing_ming = temp_name;
7 temp_num: = num_1 + num_2;
8 END;
9/

Procedure created.

SQL> DECLARE
2 tname varchar2 (25 );
3 tnum number;
4 BEGIN
5 tname: = 'zhansan ';
6 xs_proc (tname, tnum );
7 DBMS_OUTPUT.PUT_LINE (tnum );
8 END;
9/
170

PL/SQL procedure successfully completed.

========================================================== =====

1. view the process status

SELECT object_name, status FROM USER_OBJECTS WHERE object_type = 'Procedure ';

2. recompile the process

Alter procedure xs_proc COMPILE;

3. view the source code of the process.

SELECT * FROM USER_SOURCE where type = 'processed ';

4. delete a stored procedure

Drop procedure xs_proc;