Oracle practices--pl/sql-based stored procedures

Source: Internet
Author: User

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
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.