Oracle practices--pl/sql-based stored procedures

The stored procedure of PL/SQL Foundation

stored procedure: A procedure is a subroutine used to complete a specific task ( A collection of code )

/*

Sub-Program Advantages:1. Modular, decomposition of the program into a logical module;

2. reusability, which can be called by any number of programs;

3, maintainability, simplify maintenance operation;

4. Security: Make data more secure by setting permissions

*/

-- Stored Procedures 1 , Print HelloWorld

Create or replace procedure My_prois   --Used to declare some variables v_string varchar2: = ' helloworld! '; Begin Dbms_output.put_line (v_string); end;

There are three ways of calling:

Begin my_pro;--this way to invoke a stored procedure without arguments, the parentheses can omit My_pro (); end;

Call My_pro ();--without reference must also be enclosed in parentheses

Enter set Serveroutput on  execute My_pro () in command;

-- stored procedure 2 , with parameters, the default is input parameters

Create or Replace procedure My_pro2 (v_sal number  )--parameter, to make the type, varchar cannot specify the length is  v_sql varchar2 (n);  V_emp emp%rowtype;begin V_sql: = ' select * from emp where Sal =: s '; Execute immediate v_sql into v_emp using V_sal; Dbms_output.put_line (V_emp.ename); Exception when   No_data_found then     dbms_output.put_line (' not found! '); end; Select *from emp;call my_pro2 (820);

-- Stored Procedures 3 , with parameters, input (in) and Output (out) Parameters , Key Words: in Out, do not write by default is inch

Create or Replace procedure My_pro3 (v_sal number, v_name out emp.ename%type)--outis v_sql varchar2 (n); begin v_sql: = ' sel ECT ename from emp where Sal =: s '; Execute immediate v_sql into v_name using V_sal;end;

-- Stored Procedures 4 , enter the output type, in Out, Exchange two numbers

Create or replace procedure swap (v_num1 in out number, v_num2 in out number)--The in order cannot be reversed as v_temp number;begin v_temp : = V_NUM1; V_NUM1: = v_num2; V_NUM2: = V_temp;end;

-- calling a stored procedure 4

DECLARE v_num1 number: = ' &num1 '; V_NUM2 Number: = ' &num2 '; begin Dbms_output.put_line (' Pre-swap: NUM1 = ' | | v_num1| | ' -----num2 = ' | | V_NUM2); Swap (V_NUM1,V_NUM2); Dbms_output.put_line (' after exchange: NUM1 = ' | | v_num1| | ' -----num2 = ' | | V_NUM2); end;

-- Stored Procedure 5 to calculate the total number of pages and total records

Create or Replace procedure Countpageandrecords      (tableName varchar2, pageSize number,totalrecord out number, Totalpage out number) as V_sql varchar2 (n); begin V_sql: = ' SELECT count (*) from ' | | TableName; Execute immediate v_sql into Totalrecord; Totalpage: =ceil (totalrecord/pagesize);  --Calculation mode two if mod (totalrecord,pagesize) =0 then   totalpage: = totalrecord/pagesize; else   totalpage: =floor ( Totalrecord/pagesize) +1; End If;end;

-- Test Stored procedure 5

DECLARE TableName varchar2 (): = ' & table name: '; PageSize Number: = ' & paging unit: '; Totalrecord number; Totalpage number;begin countpageandrecords (tablename,pagesize,totalrecord,totalpage); Dbms_output.put_line (' Total number is: ' | | Totalrecord); Dbms_output.put_line (' Total pages: ' | | Totalpage); end;

Article Source: http://blog.csdn.net/ysjian_pingcx/article/details/25742459