Assignment 1: PL/SQL stored procedure P9
来源:互联网 发布:java 循环替换字符串 编辑:程序博客网 时间:2024/05/17 09:27
Assignment 1: PL/SQL stored procedure
Objective: To comprehend how to write, compile and execute a PL/SQL stored
procedure and display the required result to the SQL prompt.
Problem Description: The creation of PL/SQL stored procedure has been explained to
you in the classroom. This step by step guide will let you understand the procedure of
creation of a PL/SQL stored procedure.
Estimated time: 20 Minutes
Note: Before executing this block please set the SERVEROUTPUT option.
Type 'SET SERVEROUTPUT ON' at the SQL Prompt in SQL* Plus
Step 1: Consider the following employee table in your Oracle schema:
Create table employee (
empNo number,
empName varchar2(20),
empSalary number(8,2),
grade char(1)
);
Step 2: Write the following code in a notepad to create the stored procedure sp_Get_Grade
and save the file with name “getgrade.sql”.Note that the file extension should be .sql.
CREATE OR REPLACE PROCEDURE sp_Get_Grade( p_eNo IN EMPLOYEE.EmpNo%TYPE
:=0 , p_eGrade OUT EMPLOYEE.Grade%TYPE) IS
BEGIN
SELECT grade into p_eGrade FROM employee WHERE EmpNo = p_eNo;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_eGrade := 'Z';
WHEN OTHERS THEN
p_eGrade :='Z';
dbms_output.put_line('*** Error occurred ***');
dbms_output.put_line('SQLCODE: '||to_char(SQLCODE));
dbms_output.put_line('SQLERRM: '||SQLERRM);
END;
/
Step 4: Stored procedure is compiled and ready to use. Now create an anonymous PL/SQL
block as given below to call the stored procedure sp_Get_Grade.
DECLARE
v_employeeNo EMPLOYEE.EmpNo%TYPE;
v_employeeGrade EMPLOYEE.Grade%TYPE;
BEGIN
v_ employeeNo := 1;
sp_Get_Grade(v_employeeNo, v_employeeGrade);
IF v_employeeGrade = 'Z' THEN
dbms_output.put_line('Employee No Not Found');
ELSE
dbms_output.put_line('Employee Grade is '||v_employeeGrade);
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('*** Error occurred ***');
dbms_output.put_line('SQLCODE: '||to_char(SQLCODE));
dbms_output.put_line('SQLERRM: '||SQLERRM);
END;
/
You will get the following output (assuming the grade of employee no 1 is ‘A’):
Employee Grade is A
PL/SQL procedure successfully completed.
Assignment 2: Creating PL/SQL stored procedures
Objective: To learn how to write PL/SQL stored procedures and calling those objects
from anonymous blocks or from SQL prompt.
Problem Description: Create following tables and write PL/SQL codes for the
following requirements.
Note: Use provided sql file to create the tables and insert the records.
a) Login (This table stores the authentication information)
1. Create a sequence named “seq_transationid” that starts with 7000005 and increments
by 1.
Note: Sequence creation syntax:
CREATE SEQUENCE seqname START WITH initialvalue INCREMENT BY
incrementvalue;
Example: CREATE SEQUENCE seq1 START WITH 1 INCREMENT BY 1;
The next value can be generated using :
SELECT seq1.NEXTVAL from dual;
The current value can be displayed using:
SELECT seq1.CURRVAL from dual;
To assign to a PL/SQL variable inside a PL/SQL block:
SELECT seq1.NEXTVAL into v_productno from dual;
The sequence can be used in INSERT statement as follows:
INSERT INTO sample VALUES(seq1.NEXTVAL,…….);
2. Create a Stored Procedure for Account Transaction
The SP should accept AccountNo, Amount, Type of Transaction and Description and
insert them into the AccountTransaction table. The TransactionId should be generated
using the sequence that was created before. The TransactionDate should be the system
date. This insertion should happen only if the following conditions are met:
a. The AccountNo should exist.
b. The Amount should be a positive number.
c. The Type of transaction should be valid (‘D’ for Deposit or ‘W’ for
Withdraw).
d. If the Type is ‘W’, sufficient amount should be available for the
transaction. That is, the balance amount should be greater than or
equal to 500 after withdrawal.
The SP should also update CurrentBalance in the Account table. The SP should
return an integer as explained below
i) -1, if the AccountNo is invalid.
ii) -2, if the Amount is not a positive number.
iii) -3, if the fund is not sufficient
iv) -4, if the account is a fixed account
v) -5, if the type of transaction is other than ‘D’ or ‘W’
vi) <TransactionId>, if the transaction is successful.
vii) -6,other exceptions
Note: Calling a stored procedure from SQL prompt
Step 1. Declare host variables for Out parameters in SQL Prompt. E.g.
VARIABLE x Number
Step 2. Execute the procedure.
E.g. EXEC sp_transaction (2000,500,’D’,’Self’);
Step3. Print value of x in SQL Prompt. E.g. PRINT x;
Note: To display the current date
SELECT SYSDATE from dual;
To insert the current date into a table
INSERT INTO sample VALUES(SYSDATE,……);
To display the current date and time:
SELECT to_char(SYSDATE,'DD-MON-YYYY HH:MI:SS') FROM dual;
3. Create a PL/SQL Stored Procedure for FundTransfer.
The Stored Procedure should accept FromAccountNo , ToAccountNo, Description and
Amount and call the Account Transaction Stored Procedure ( “Creating PL/SQL stored
procedures” Assignment) for withdrawing amount from the FromAccountNo and
depositing amount to the ToAccountNo. The SP should return an integer as explained
below:
i) -1, if the AccountNo is invalid.
ii) -2, if the Amount is not a positive number.
iii) -3, if the fund is not sufficient
iv) -4, if the account is a fixed account
v) 0, if the fund transfer is successful.
i) -6,other exceptions
Write an anonymous PL/SQL block to invoke the stored procedure. The anonymous block
should display appropriate messages based on the return value from the procedure.
- Assignment 1: PL/SQL stored procedure P9
- 执行方法为PL/SQL Stored Procedure的并发程序
- Stored procedure & Sql Injection
- Sql server stored procedure operating summary 01
- Sql server database stored procedure reference 01
- SQL Server 2000 Stored Procedure Programming
- Overview of SQL Server Stored Procedure
- SQL存储过程(Stored Procedure)
- Stored Procedure
- My First PL/SQL Procedure
- My Second PL/SQL Procedure
- Problem - Slow PL/SQL procedure
- SQL Debug && Stored Procedure Debug ----- SQL Server Management Studio
- SQL Stored Procedure Wrapper & Typed DataSet Generator for .NET
- illustrates how to call a SQL Server stored procedure
- SQL Server 2005: Stored Procedure call activity statistics & execution time
- sql查询查所有存储过程(stored procedure)
- SQL Stored Procedure Wrapper & Typed DataSet Generator for .NET
- CSDN总编观察:创新与山寨——《程序员》2010第10期卷首语
- 惊喜
- Fedora 13 telnet配置
- 如何创建一个 Windows 服务应用程序的安装项目在 Visual C# 中
- 浅析Oracle存储过程触发器在数据同步中的应用
- Assignment 1: PL/SQL stored procedure P9
- SQL FOREIGN KEY 约束的建立与删除(转)
- GAIA page
- 关于错误:"table is mutating, trigger/function may not see it
- 关于UUID,GUID,OCMB
- 正则表达式
- 规划分析输出表格设计思路(2010_09)
- 计算机常用英文单词
- Boot time kernel options