-
Copyrights 2010 - Online Training Oracle Applications. All Rights Reserved.
|
PL/ SQL:
The following are the advantages of PL/SQL 2) We can have user definied error messages by using the concept of exception handling. 3) We can perform related actions by using the concept of triggers. 4) Pl/SQL helps in reducing the network traffic. PL/SQL Block structure: declare A Pl/SQL block contains 3 sections. 1) Declare section 1) Declare section: 2) Executable Section: The actual task which should be done is written in the executable section. All the lines between Begin and exception keywords is called as Executable section. 3) Exception Section: Ex1: Write a PL/SQL block to display 'Hello World'. For this program, we do not need any local variables. Before the running this program, we need to make the environment variable serveroutput to ON. To command to make the serveroutput to ON] SQL> Set serveroutput on Begin Pl/SQL procedure successfully completed. Ex 2: For this program, we need 3 variables, so we need declare section. Syntax to declare variable: <variable> <datatype>(size); Declare begin dbms_output.put_line ( ' The sum is ...'||c); Pl/SQL procedure successfully completed. In the above program, there are two important points to learn. i) := is assignment operator, which is used to assign value from the right hand side to the variable in the left hand side. ii) || (pipe) is concatenation operator. We can initilize at the time of declaration. In the abvoe program, we have hard coded the value 10 and 20 in the program. Instead of hard coding the value, we can accept the values from the user. Ex 3: Write a program to accept two values from the user and display its sum. Declare begin a := &a; dbms_output.put_line('The sum is ...'||c); Enter a value for B:30 The sum is ...70 Pl/SQL procedure successfully completed. Note: & operator is used to accept value from the user. Ex 4: Note: To increment the salary (change the value) in a table, we need to use update command. Declare begin update emp set sal = sal+1000 end; Procedure successfully completed. To make the above update command permanent, we can use commit after update command in PL/SQL block. ex: Declare begin update emp set sal = sal+1000 commit; end; Writing a select stmt in a PL/SQL Block: Write a pl/SQL block which accepts empno and display ename and salary. As ename and sal are the values present in the emp table, to get those values we need to write a select stmt. Note: Every select stmt in a PL/SQL block should have into clause. Declare l_empno := &empno; select ename,sal into l_ename, l_sal from emp dbms_output.put_line(l_ename||'....'||l_sal); Note: Using %TYPE attribute: Instead of hardcoding the datatype and size for local variable, we can use %TYPE attribute. Ex: l_ename emp.ename%TYPE; --- The datatype of ename column The above program, i use %TYPE attribute to declare local variables. Declare begin l_empno := &empno; select ename,sal into l_ename, l_sal from emp dbms_output.put_line(l_ename||'....'||l_sal); Using %ROWTYPE Attribute: Ex: Write a PL/SQL Block which accepts an empno and display ename, sal, hiredate and job. declare l_empno := &empno; dbms_output.put_line(l_row.ename); end; Note: we cannot print a ROWTYPE variable, we can print a value of a ROWTYPE variable.
|