PL/SQL-stored procedures and plsql stored procedures

PL/SQL-stored procedures and plsql stored procedures

I. Stored Procedure

Stored Procedure is a set of SQL statements for specific functions. Compiled statements are stored in the database. You can specify the name of the stored procedure and provide parameters (if the stored procedure has parameters) to execute it. Oracle can store PL/SQL programs in databases and run them anywhere. Stored procedures are called PL/SQL subprograms. They are named PL/SQL fast and stored in databases. they exchange information with callers through input and output parameters. Oracle stored procedures do not return data.

Syntax:

Create or replace procudure stored by name (
Parameter Name input/output type parameter type,
Parameter Name input/output type parameter type
)
Is
Begin
Processing statement;
Exceeption;
Exception Handling statement;
End stores names;

There are three output types:

• IN defines an input parameter variable, which is used to pass a parameter to a stored procedure. The Stored Procedure cannot change the parameter value. This parameter can be a constant or a variable with a value.
• OUT defines an output parameter variable used to obtain data from the stored procedure. This parameter must be a variable and is not important if the variable has a value.
• In out defines an input and output parameter variable. It also has the functions of both. This parameter must be a variable and must have a value.

The output parameter type generally does not declare the length, because the width of the IN parameter is determined by the outside. For the OUT and in out parameters, the width is determined by the stored procedure. For parameters that do not describe the input/output type, the default value is IN.

Ii. Example

The structure of the person table in the following code is as follows:

Drop table person; create table person (id NUMBER (11) not null, username VARCHAR2 (255) NULL, age NUMBER (11) NULL, password VARCHAR2 (255) NULL, primary key (id) insert into person VALUES ('1', 'zhang san', '123', 'zhang123 '); insert into person VALUES ('2 ', 'Li si', '20', 'lisi123 '); insert into person VALUES ('3', 'wang wu', '20', 'wang123 '); insert into person VALUES ('4', 'zhao liu', '20', 'zhao123 ');

1. query one (in, out)

Create or replace procedure pro_person_getbyid (p_id in number, p_username out varchar2, p_age out number, p_password out varchar2) isbegin select username, age, password into p_username, p_age, p_password from person where id = p_id; end pro_person_getbyid; -- call the code -------------- declare v_id number; v_username varchar2 (255); v_age number; v_password varchar2 (255); begin v_id: = 1; pro_person_getbyid (v_id, v_username, v_age, v_password); dbms_output.put_line ('username: '| v_username | 'Age:' | v_age | 'password: '| v_password ); end;

2. query a (in, out) using rowtype

Create or replace procedure pro_person_getrow (p_id in number, p_row out person % rowtype, -- rowtype type variable p_count out number -- Mark whether a record is found) isbegin select * into p_row from person where id = p_id; p_count: = SQL % ROWCOUNT; exception when no_data_found then p_count: = 0; end pro_person_getrow; -- call -------------- declare v_id number: = 28; v_row person % rowtype; v_count number; begin pro_person_getrow (v_id, v_row, v_count); dbms_output.put_line (v_count); struct ('Id: '| v_row.id | 'username: '| v_row.username | 'Age:' | v_row.age | 'password: '| v_row.password); end;

3. Add records (in and out)

Create or replace procedure pro_person_insert (p_id number, p_username varchar2, p_age number, p_password varchar2, p_count out number -- add successful) isbegin insert into person (id, username, age, password) values (p_id, p_username, p_age, p_password); p_count: = SQL % ROWCOUNT; -- SQL % ROWCOUNT is the property of implicit cursor commit; exception when others then p_count: = 0; -- failed end pro_person_insert; -- call proceduredeclare v_id number: = 28; v_username varchar2 (255): = 'xiaoli'; v_age number: = 19; v_password varchar2 (255 ): = 'xiao123'; v_count number; begin pro_person_insert (p_id => v_id, p_username => v_username, p_age => v_age, p_password => v_password, p_count => v_count ); -- pro_person_insert (v_id, v_username, v_age, v_password, v_count); dbms_output.put_line ('Affected rows' | v_count); end;

4. Update (in and out)

Create or replace procedure pro_person_update (p_id number, p_age number, p_password varchar2, p_count out number) isbegin update person set age = p_age, password = p_password where id = p_id; p_count: = SQL % ROWCOUNT; commit; exception when no_data_found then p_count: = 0; when others then p_count: =-1; end pro_person_update; -- call ------------------- declare v_id number: = 28; v_age number: = 19; v_password varchar2 (255): = 'Password'; v_count number; begin pro_person_update (v_id, v_age, v_password, v_count ); dbms_output.put_line ('Affected rows' | v_count); end;

5. Delete (in, out)

Create or replace procedure pro_person_delete (p_id number, p_count out number) isbegin delete from person where id = p_id; p_count: = SQL % ROWCOUNT; commit; exception when no_data_found then p_count: = 0; when others then p_count: =-1; end pro_person_delete; -- call -------------- declare v_id number: = 28; v_count number; begin pro_person_delete (v_id, v_count ); dbms_output.put_line ('Affected rows' | v_count); end;

6. query all (in, out) using sys_refcursor

Create or replace procedure pro_person_findall2 (p_cursor out sys_refcursor -- the output parameter is of the package type) isbegin open p_cursor for select * from person; exception when others then evaluate ('error occurred when obtaining information '); end pro_person_findall2; ---- call includeclare c_cursor sys_refcursor; r_person person % rowtype; begin pro_person_findall2 (c_cursor); -- 2. open the cursor -- open c_cursor; -- open the cursor without display, because when the stored procedure is called, the returned cursor has already opened -- 3. extract the data loop fetch c_cursor into r_person; exit when c_cursor % notfound; -- when there is no data below, exit logging ('Id: '| r_person.id); dbms_output.put_line ('username:' | r_person.username); dbms_output.put_line ('Age: '| r_person.age); end loop; end;

7. query all (in, out) queries using custom types

-- Create or replace package pkg_const as type r_cursor is ref cursor; end pkg_const; -- create a stored procedure, create or replace procedure pro_person_findall (p_cursor out pkg_const.r_cursor -- the output parameter is of the package type) isbegin open p_cursor for select * from person; exception when others then condition ('error occurred when obtaining information '); end pro_person_findall; ---- call includeclare c_cursor pkg_const.r_cursor; r_person person % rowtype; begin pro_person_findall (c_cursor); -- 2. open the cursor -- open c_cursor; -- 3. extract the data loop fetch c_cursor into r_person; exit when c_cursor % notfound; -- exit dbms_output.put_line ('Id: '| r_person.id) when there is no data below ); dbms_output.put_line ('username: '| r_person.username); dbms_output.put_line ('Age:' | r_person.age); end loop; end;

Iii. Other statements in the Stored Procedure

View stored procedures

DESCRIBE stored procedure name;

Delete stored procedure

Drop procedure stored PROCEDURE name;

 

How Does oracle pl/SQL view statements through the stored procedure?

Right-click the table view and click view SQL in the lower right corner to view the table or edit it.

How can I call a stored procedure in PL/SQL Developer?

Check whether there are parameters in your Stored Procedure

Normally, find the name of the stored procedure you wrote in procedure in the list on the left. check whether there is a red cross on the top. If there is no Red Cross, the compilation is successful. If there is a Red Cross, there is still a problem. You need to change it.

Right-click the Stored Procedure (select the test button for the Chinese version and test for the English version), and enter the parameters below, and click the gear above.

Another way is to open an SQL window and write the following code:
Begin
Name of the stored procedure;
End;

Then run the gear. Note that two semicolons are indispensable.