Trigger: - A trigger is the code, which is executed for certain database events like DML operation on table. The user can write trigger on a table or a view trigger are used for implementing complex validation, complex security, auditing and replication of data. Before triggers are used for validation and security while after trigger … Continue reading Trigger: –
1 declare 2 a number(2); 3 b number(2); 4 c number(2); 5 function large(x in number,y in number) 6 return number is z number; 7 begin 8 if x>y then 9 Z:=x; 10 else 11 z:=y; 12 end if; 13 return z; 14 end; 15 begin … Continue reading An example of function subroutine?
1 declare 2 a number; 3 b number; 4 c number; 5 procedure sum(x IN number,y IN number,z OUT number) is 6 begin 7 z:=x+y; 8 end; 9 begin 10 a:=5; 11 b:=10; 12 sum(a,b,c); 13 dbms_output.put_line('Sum : '||to_char(c)); 14* end; SQL> / Sum : 15 … Continue reading An example of procedure subroutine?
Subroutine programs: - Subroutine programs are used to provide modularity and encapsulation in application. Through sub programs, we can have built application using small building blocks. These building blocks are easy to construct and easy to maintain. We can break a large program into manageable small module, which can easily we constructed. It is called … Continue reading Subroutine programs: –
SQL> declare 2 cursor c1 is 3 select * from batch; 4 z c1 %rowtype; 5 begin 6 open c1; 7 fetch c1 into z; 8 while (c1 %notfound) loop 9 dbms_output.put_line('Batch Code is : '||z.batchcode); 10 fetch c1 into z; 11 end loop; 12 close c1; … Continue reading An example of cursor?
Cursor: - Cursor is used in PL/SQL programming to define record pointer. It is used when we want to manipulate data of many rows. When SELECT_ _ _INTO or any DML statement is issued then oracle opens the cursor. There are two type of cursor used in PL/SQL programming. They are called implicit cursor and … Continue reading Cursor: –
Percent type: - The percent type used in PL/SQL for a column value when we declare PL/SQL variable to hold column value, we must insure that the variable is of current data type and precision. If it is not, a PL/SQL error will occur during execution. Rather than hard coding the data type and precision … Continue reading Percent type: –
Scalar data type: - A scalar data type holds a single value and has no internal components. Scalar data types can be classified into four different categories such as number, character, data and Boolean. Character and number data type have sub types that associates a base type to a constraint. For example, integer and positive … Continue reading Scalar data type: –
SQL> accept a prompt 'Enter A Number : ' Enter A Number : 5 SQL> declare 2 b integer; 3 c integer; 4 d integer; 5 begin 6 b:=&a; 7 c:=1; 8 for d in 1..b loop 9 c:=c*d; 10 end loop; 11 dbms_output.put_line('Factorial = '||c); 12 … Continue reading Write a program in PL/SQL to input a number calculate and print factorial of input number?
SQL> set serveroutput on SQL> accept x prompt 'Enter First Number : ' Enter First Number : 7 SQL> accept y prompt 'Enter Second Number : ' Enter Second Number : 5 SQL> accept z prompt 'Enter Third Number : ' Enter Third Number : 9 SQL> declare 2 a number(2); 3 b … Continue reading Write a program in PL/SQL to input three integers from keyboard and print a largest number out of three numbers?