Shulou(Shulou.com)06/01 Report--

1. Storage procedure

What is a stored procedure?

A program fragment written in advance using oracle syntax with business functions, stored in oracle servers for a long time, for remote access by oracle clients (e.g., sqlplus) and program languages, similar to functions in Java.

Why use stored procedures?

(1) PLSQL must be run as a whole every time it is executed, before there is a result.

(2) PLSQL cannot be encapsulated and stored in Oracle Server for a long time.

PLSQL cannot be invoked by other applications, such as Java.

What is the relationship between stored procedures and PLSQL?

Stored procedures are an application of one aspect of PLSQL, which is the basis of stored procedures.

That is, stored procedures require PLSQL.

---------

Grammar:

create [or replace] procedure name [(parameter list)] asPLSQL program body;

Note: There is [begin…end;/] in the storage process, no declare

Create a non-parametric stored procedure hello, no return value, syntax: create or replace procedure name as PLSQL program

create or replace procedure helloasbegin dbms_output.put_line ('this is my first stored procedure'); end;/

delete stored procedure hello, syntax: drop procedure name

drop procedure hello;

Method of calling stored procedure 1, exec stored procedure name

exec hello;

Call stored procedure mode 2, PLSQL program

begin hello; end;/

Calling stored procedure mode 3, Java program

JDBC has an object called Callable Statement.

Create a parameter stored procedure raiseSalary(number), increase employee 7369 salary by 10%, demonstrate the usage of in, default in, case-insensitive

--define procedure create or replace procedure raiseSalary(pempno number)asbegin update emp set sal=sal*1.2 where empno=pempno;end;/--invoke procedure exec raiseSalary(7369);

Create a parametric stored procedure findEmpNameAndSalAndJob(ID), query the name, position, monthly salary of employee No. 7788, return multiple values, demonstrate the usage of out

--define procedure create or replace procedure findEmpNameAndSalAndJob(pempno in number,pename out varchar 2,pjob out varchar2,psal out number)asbegin select ename,job,sal into pename,pjob,psal from emp where empno=pempno;end;/--invoke procedure declare pename emp.ename%type; pjob emp.job%type; psal emp.sal%type;begin findEmpNameAndSalAndJob(7369, filename,pjob,psal); dbms_output.put_line ('employee number 7369's name is '|| pename ||'The position is'|| pjob ||'Monthly salary is'|| psal);end;/

Under what circumstances is exec invoked, and under what circumstances is PLSQL invoked?

exec Suitable for calling stored procedures No return value

plsql is suitable for calling stored procedures with return values, no matter how many

Use the storage process to write a function for calculating personal income tax

create or replace procedure get_rax(sal in number,rax out number)as -- sal denotes revenue-- bal denotes taxable revenue bal number;begin bal := sal - 3500; if bal

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.