(PL/SQL) basic operations

(PL/SQL) basic operations 1. function. To improve the system performance, the database storage module is required so that you can create a stored procedure (SQL window) from the basic application www.2cto.com 1) create or replace procedure update_staff as begin update staff set name = 'xy'; commit; end can be called in the SQL * plus environment in two ways, one is to use the EXECUTE (EXEC) command, the other is to use the CALL command, SQL> call update_staff (); Delete process SQL DROP procedure update_staff stored procedure is suitable for update operations, in particular, the advantages of a large amount of data update 2 Stored Procedure www.2cto.com ① improve database execution efficiency. Use SQL interfaces to update databases. If updates are complex and frequent, you need to connect to the database frequently. ② Improve security. Stored procedures are stored as objects in the database and can be assigned permissions. ③ Reusability 2 stored procedures with input parameters 1 create a stored procedure (SQL window) create or replace procedure update_staff (in_age in number) as begin declare newage number; begin newage: = in_age + 10; update staff set age = newage; commit; end update_staff; 2. execute the Stored Procedure (Command window) execute update_staff (10 ); 3 default value: only the in parameter can have the default value, such as create or replace procedure update_staff (in_name in varchar2, in_age in number default 20) when calling, you can only write execute update_sta Ff ('xy'); three stored procedures with output parameters 1 create a stored procedure (SQL window) create or replace procedure update_staff (in_age in number, out_age out number) as begin update staff set age = in_age; select age into out_age from student where num = 1; commit; end update_staff; no returned values are displayed in the stored procedure, however, the output parameters can output the stored procedure result (Command window) set serverout on; declare age number; begin update_staff (20, age); dbms. output. put_line (age); end; four-band Input and Output The most typical application is to exchange two values. 1. create a stored procedure (SQL window) create or replace procedure swap (param1 in out number, param2 in out number) as begin declare param number; begin param: = param1; param1: = param2; param2: = param; end swap; 2. output the stored procedure result (Command window) set serverout on; declare p1 number: = 25; p2 number: = 35; begin swap (p1, p2); dbms_output.put_line (p1); end; Five Parameter summaries ① input parameters: some callers pass this parameter to the stored procedure. No matter how the stored procedure calls this parameter, the value of this parameter cannot be changed. The parameter value is read-only. ② Output parameters: can be used as return values. It can be considered writable. ③ Input and output parameters: the parameters of this type are the most similar to those of java methods. input methods are readable and writable (except final identifiers ). ④ The Parameter order is summarized as follows: a parameter with a default value should be at the end of the parameter list, because sometimes you need to omit this parameter. Parameters without default values can follow "in-> out-> in out ".