Page 1 :
PL/SQL Datatypes:, 1.CHAR:CHAR data type used to store character data within a predefined, length., 2.VARCHAR2: VARCHAR2 data type used to store variable strings data within, a predefined length., 3.DATE:DATE data type to store valid date-time format with a fixed, length., 4.Number:NUMBER data type used to store numeric data.It contains letters,, numbers, and special characters., Storage Range: Precision range(p): 1 to 38 and Scale range(s) : -84 to, 127, 5.LONG:Variable-length character string with maximum size of 32,760 bytes, 6.RAW:The RAW data type used to store binary data such as images,, graphics, etc., 7.LONG RAW:LONG RAW data type same as LONG type used to store variable, string data within a predefined length, 8.BLOB:Used to store large binary objects in the database., 9.CLOB:Used to store large blocks of character data in the database., 10.BFile:Used to store large binary objects in operating system files, outside the database., 11.%Type:This data type is used to store value unknown data type column, in a table. The column is identified by %type data type., 12.%RowType:This data type is used to store values unknown data type in, all columns in a table. All columns are identified by %RowType datatype., =========================================================================, ======================, PL/SQSL variables:, Variable Initialization, =======================, 1)Declaring variable in declaration section and assigning value in begin, block., SET SERVEROUTPUT ON;, DECLARE, Test_var1 NUMBER; declaration, BEGIN, Test_var1:= 10;, Assiging value, DBMS_OUTPUT.PUT_LINE (Test_var1);, END;, /, -----------------------------------------------------------------------------2)Declaring variable in declaration section and assigning value by direct, way., declaration with assigning value in declare block, SET SERVEROUTPUT ON;, DECLARE, var_test1 VARCHAR2(30) := '&enter_a_string';, BEGIN, DBMS_OUTPUT.PUT_LINE(var_test1);, END;, /, -----------------------------------------------------------------------------3)by fetching value from the column of a row of a table and assigning, that value to the variable., SET SERVEROUTPUT ON;, DECLARE
Page 4 :
ELSIF v_Place = 'Mumbai' THEN, DBMS_OUTPUT.PUT_LINE('City of dreams');, ELSE, DBMS_OUTPUT.PUT_LINE('Part of INDIA');, END IF;, DBMS_OUTPUT.PUT_LINE('Thank you');, END;, /, =========================================================================, ======================, *************************************B)Iterative, control*************************************, 1)Loop or simple loop, display the number from 1 to 100:EXIT WHEN, SET SERVEROUTPUT ON;, DECLARE, i NUMBER := 1;, BEGIN, LOOP, INSERT INTO T2 VALUES(i);, i:=i+1;, EXIT WHEN i>100;, END LOOP;, END;, /, -----------------------------------------------------------------------------SET SERVEROUTPUT ON;, DECLARE, i number:=0;, BEGIN, LOOP, i:=i+1;, DBMS_OUTPUT.PUT_LINE(i);, IF(i>=5)THEN, EXIT;, END IF;, END LOOP;, END;, /, -----------------------------------------------------------------------------2)While loop, Display 1 to 100, SET SERVEROUTPUT ON;, DECLARE, i INTEGER := 1;, BEGIN, WHILE i <= 100 LOOP, DBMS_OUTPUT.PUT_LINE(i);, i := i+1;, END LOOP;, END;, /, ------------------------------------------------------------------------------
Page 7 :
NULL;, END IF;, =========================================================================, ======================, Exception Handling:, create table School(Roll_no int , Name varchar(20), Marks int);, insert into School values(1, 'Suraj',100);, insert into School values(2, 'Praveen',97);, insert into School values(3, 'Jessie', 99);, -----------------------------------------------------------------------------NO_DATA_FOUND: It is raised WHEN a SELECT INTO statement returns no rows, SET SERVEROUTPUT ON;, DECLARE, temp varchar(20);, BEGIN, SELECT Roll_no into temp from School where Name='ram';, exception, WHEN NO_DATA_FOUND THEN, dbms_output.put_line('No such student!');, end;, /, -----------------------------------------------------------------------------TOO_MANY_ROWS:It is raised WHEN a SELECT INTO statement returns more than, one row., SET SERVEROUTPUT ON;, DECLARE, temp varchar(20);, BEGIN, -- raises an exception as SELECT, -- into trying to return too many rows, SELECT Name into temp from School;, dbms_output.put_line(temp);, EXCEPTION, WHEN TOO_MANY_ROWS THEN, dbms_output.put_line('error trying to SELECT too many rows');, end;, /, -----------------------------------------------------------------------------VALUE_ERROR:This error is raised WHEN a statement is executed that, resulted in an arithmetic, numeric, string, conversion, or constraint, error. This error mainly results from programmer error or invalid data, input., SET SERVEROUTPUT ON;, DECLARE, temp number;, BEGIN, SELECT Name into temp from School where Name='Suraj';, dbms_output.put_line('the Name is '||temp);
Page 8 :
EXCEPTION, WHEN VALUE_ERROR THEN, dbms_output.put_line('Error');, dbms_output.put_line('Change data type of temp from number to, varchar(20)');, END;, /, -----------------------------------------------------------------------------ZERO_DIVIDE = raises exception WHEN dividing with zero, SET SERVEROUTPUT ON;, DECLARE, a int:=19;, b int:=0;, answer int;, BEGIN, answer:=a/b;, dbms_output.put_line('the result after division is'||answer);, exception, WHEN ZERO_DIVIDE THEN, dbms_output.put_line('dividing by zero please check the values, again');, dbms_output.put_line('the value of a is '||a);, dbms_output.put_line('the value of b is '||b);, END;, /, -----------------------------------------------------------------------------SET SERVEROUTPUT ON;, DECLARE, a int:=&a;, b int:=&b;, answer int;, BEGIN, answer:=a/b;, dbms_output.put_line('the result after division is'||answer);, exception, WHEN ZERO_DIVIDE THEN, dbms_output.put_line('dividing by zero please check the values, again');, dbms_output.put_line('the value of a is '||a);, dbms_output.put_line('the value of b is '||b);, END;, /, -----------------------------------------------------------------------------User defined exception:, Divide non-negative integer x by y such that the result is greater than, or equal to 1., From the given question we can conclude that there exist two exceptions, 1.Division be zero., 2.If result is greater than or equal to 1 means y is less than or equal, to x.
Page 10 :
*************************, CREATE [OR REPLACE ] TRIGGER trigger_name, {BEFORE | AFTER | INSTEAD OF }, {INSERT [OR] | UPDATE [OR] | DELETE}, [OF col_name], ON table_name, [REFERENCING OLD AS o NEW AS n], [FOR EACH ROW], WHEN (condition), DECLARE, Declaration-statements, BEGIN, Executable-statements, EXCEPTION, Exception-handling-statements, END;, *************************, Here,, CREATE [OR REPLACE] TRIGGER trigger_name: It creates or replaces an, existing trigger with the trigger_name., {BEFORE | AFTER | INSTEAD OF} : This specifies when the trigger would be, executed. The INSTEAD OF clause is used for creating trigger on a view., {INSERT [OR] | UPDATE [OR] | DELETE}: This specifies the DML operation., [OF col_name]: This specifies the column name that would be updated., [ON table_name]: This specifies the name of the table associated with the, trigger., [REFERENCING OLD AS o NEW AS n]: This allows you to refer new and old, values for various DML statements, like INSERT, UPDATE, and DELETE., [FOR EACH ROW]: This specifies a row level trigger, i.e., the trigger, would be executed for each row being affected. Otherwise the trigger will, execute just once when the SQL statement is executed, which is called a, table level trigger., WHEN (condition): This provides a condition for rows for which the, trigger would fire. This clause is valid only for row level triggers., OLD and NEW references are used for record level triggers these are not, available for table level triggers., If you want to query the table in the same trigger, then you should use, the AFTER keyword, because triggers can query the table or change it, again only after the initial changes are applied and the table is back in, a consistent state., -----------------------------------------------------------------------------Creating Triggers:, Create Table customers_test(ID Number(10),Name Varchar2(50),Salary, Number(30));, CREATE OR REPLACE TRIGGER display_salary_changes, BEFORE DELETE OR INSERT OR UPDATE ON customers_test, FOR EACH ROW
Page 11 :
WHEN (NEW.ID > 0), DECLARE, sal_diff number;, BEGIN, sal_diff := :NEW.salary - :OLD.salary;, dbms_output.put_line('Old salary: ' || :OLD.salary);, dbms_output.put_line('New salary: ' || :NEW.salary);, dbms_output.put_line('Salary difference: ' || sal_diff);, END;, /, Insert into customers_test values(4,'Sayali',23000);, Insert into customers_test values(5,'Om',33000);, Insert into customers_test values(6,'Shyam',63000);, commit;, Update customers_test set Salary=70000 where ID=3;, Commit;, DELETE FROM customers_test where ID=1;, Commit;, -----------------------------------------------------------------------------Row-level triggers:, 1)Modifying :OLD & :NEW values, A BEFORE row-level trigger can modify the new column values, but an AFTER, row-level trigger cannot., 2)Correlation names, OLD and NEW are the default correlation names. But you can override them, using the REFERENCING clause., When you reference OLD and NEW in the trigger body, you must precede them, with a colon (:) because OLD and NEW are external variable references., 3)Performance consideration, A row-level trigger fires each time a row is affected by a triggering, event. For example, if you update 1000 rows in a table, the trigger will, fire 1000 times, which potentially cause a performance issue., To specify a condition of when to fire the trigger, you can use the WHEN, clause. For example, the following trigger only fires when you update, credit for a customer with the new credit is greater than 10,000:, -----------------------------------------------------------------------------CREATE OR REPLACE TRIGGER customers_update_credit_trg, BEFORE UPDATE OF credit_limit, ON customers, FOR EACH ROW, WHEN (NEW.credit_limit > 0), BEGIN, -- check the credit limit, IF :NEW.credit_limit >= 2 * :OLD.credit_limit THEN, raise_application_error(-20101,'The new credit ' ||, :NEW.credit_limit ||, ' cannot increase to more than double, the current credit ', || :OLD.credit_limit);, ELSE
Page 12 :
dbms_output.put_line('credit limit is fine');, END IF;, END;, /, SELECT customer_id,credit_limit, FROM customers, WHERE customer_id = 11;, UPDATE customers, SET credit_limit = 100, WHERE customer_id = 11;, -----------------------------------------------------------------------------create table DBTRIGGER1 (ID NUMBER(4),NAME VARCHAR2(50),Marks Number(6));, create or replace TRIGGER TR1_DBTRIGGER1 AFTER INSERT OR DELETE OR UPDATE, ON DBTRIGGER, FOR EACH ROW, DECLARE, updating_key_fields EXCEPTION;, BEGIN, IF INSERTING THEN, INSERT INTO DBMSTEST.DBTRIGGER1 VALUES (, :NEW.ID,, :NEW.NAME,, :NEW.MARKS);, END IF;, EXCEPTION, WHEN updating_key_fields THEN, raise_application_error(-20300, 'ActiveDB Error: cannot, update key fields of source table.');, END TR1_DBTRIGGER1;, /, Insert into DBTRIGGER values(11,'Dinesh',99);, commit;, Select * from DBTRIGGER, Select * from DBTRIGGER1, -----------------------------------------------------------------------------Statement-level triggers:, A statement-level trigger executes once for each transaction., For example, if you update 1000 rows in a table, then a statement-level, trigger on that table would only be executed once., Suppose, you want to restrict users to update credit of customers from, 28th to 31st of every month so that you can close the financial month., CREATE OR REPLACE TRIGGER customers_credit_trg, BEFORE UPDATE OF credit_limit, ON customers, DECLARE, l_day_of_month NUMBER;
Page 13 :
BEGIN, -- determine the transaction type, l_day_of_month := EXTRACT(DAY FROM sysdate);, IF l_day_of_month BETWEEN 5 AND 10 THEN, raise_application_error(-20100,'Cannot update customer credit, from 5th to 10th');, END IF;, END;, /, UPDATE, customers, SET, credit_limit = credit_limit * 110;, ALTER TRIGGER customers_audit_trg DISABLE;, ALTER TABLE customers DISABLE ALL TRIGGERS;, =========================================================================, ============================, Cursors:, When an SQL statement is processed, Oracle creates a memory area known as, context area. A cursor is a pointer to this context area. This context, area is used to store the data retrieved from the database, and, manipulate this data. In PL/SQL, the context area is controlled by, Cursor. A cursor contains information on a select statement and the rows, of data accessed by it., A cursor is used to referred to a program to fetch and process the rows, returned by the SQL statement, one at a time.A cursor can hold more than, one row, but can process only one row at a time. The set of rows the, cursor holds is called the active set., Implicit Cursors:, Implicit cursors are automatically created by Oracle whenever an SQL, statement is executed, when there is no explicit cursor for the, statement. Programmers cannot control the implicit cursors and the, information in it., Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an, implicit cursor is associated with this statement. For INSERT operations,, the cursor holds the data that needs to be inserted. For UPDATE and, DELETE operations, the cursor identifies the rows that would be affected., , Oracle provides some attributes known as Implicit cursor's attributes to, check the status of DML operations. Some of them are: %FOUND, %NOTFOUND,, %ROWCOUNT and %ISOPEN., %FOUND, Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or, more rows or a SELECT INTO statement returned one or more rows., Otherwise, it returns FALSE., %NOTFOUND
Page 14 :
The logical opposite of %FOUND. It returns TRUE if an INSERT, UPDATE, or, DELETE statement affected no rows, or a SELECT INTO statement returned no, rows. Otherwise, it returns FALSE., %ISOPEN, Always returns FALSE for implicit cursors, because Oracle closes the SQL, cursor automatically after executing its associated SQL statement., %ROWCOUNT, Returns the number of rows affected by an INSERT, UPDATE, or DELETE, statement, or returned by a SELECT INTO statement., ---------------------------------------------------------------------------------SET SERVEROUTPUT ON;, DECLARE, total_rows number(2);, BEGIN, UPDATE CUSTOMERS_Cursor, SET salary = salary + 500;, IF sql%notfound THEN, dbms_output.put_line('no customers selected');, ELSIF sql%found THEN, total_rows := sql%rowcount;, dbms_output.put_line( total_rows || ' customers selected ');, END IF;, END;, /, -------------------------------------------------------------------------------SET SERVEROUTPUT ON;, DECLARE, total_rows number(2);, BEGIN, UPDATE CUSTOMERS_Cursor, SET salary = salary + 500 where ID=10;, IF sql%notfound THEN, dbms_output.put_line('no customers selected');, ELSIF sql%found THEN, total_rows := sql%rowcount;, dbms_output.put_line( total_rows || ' customers selected ');, END IF;, END;, /, -------------------------------------------------------------------------------Explicit Cursors:, Explicit cursors are programmer-defined cursors for gaining more control, over the context area. An explicit cursor should be defined in the, declaration section of the PL/SQL Block. It is created on a SELECT, Statement which returns more than one row., For example: When you execute the SQL statements like INSERT, UPDATE,, DELETE then the cursor attributes tell whether any rows are affected and, how many have been affected. If you run a SELECT INTO statement in PL/SQL, block, the implicit cursor attribute can be used to find out whether any
Page 16 :
END LOOP;, Dbms_output.put_line('Total rows fetched is '||empt_cur%ROWCOUNT);, CLOSE empt_cur;, END;, /, --------------------------------------------------------------------------Cursor FOR LOOP:, The cursor FOR LOOP statement is the extension of the normal FOR LOOP, statement.The normal FOR LOOP executes the body of a loop once for every, integer value in a specified range. Similarly, the cursor FOR LOOP, executes the body of the loop once for each row returned by the query, associated with the cursor., A nice feature of the cursor FOR LOOP statement is that it allows you to, fetch every row from a cursor without manually managing the execution, cycle i.e., OPEN, FETCH, and CLOSE., Cursor FOR LOOP implicitly declares its loop index as a record that, represents a row fetched from the database.Next it opens a cursor, repeatedly fetches rows of values from the result set into fields in the, records in th records then closes the cursor when all rows have been, processed., SET SERVEROUTPUT ON;, DECLARE, CURSOR cur_product, IS, SELECT, product_name, list_price, FROM, products, ORDER BY, list_price DESC;, BEGIN, FOR r_product IN cur_product, LOOP, dbms_output.put_line( r_product.product_name || ': $' ||, r_product.list_price );, END LOOP;, END;, /, --------------------------------------------------------------------------Parameterized Cursor:PL/SQL Parameterized cursor pass the parameters into, a cursor and use them in to query.PL/SQL Parameterized cursor define only, datatype of parameter and not need to define it's length.Default values, is assigned to the Cursor parameters. and scope of the parameters are, locally.Parameterized cursors are also saying static cursors that can, passed parameter value when cursor are opened., 1.Cursur having paramater, 2.It contains steps of explicit cursor, 3.Cursor For Loop, set serveroutput on, DECLARE, cursor Cur_emp(n number) is select * from EMPS where EMPID = n;, temps EMPS%rowtype;, BEGIN, OPEN Cur_emp(4);
Page 17 :
FOR temps IN Cur_emp(4) LOOP, dbms_output.put_line('EMPID:, '||temps.EMPID);, dbms_output.put_line('EMP_Name: '||temps.empname);, dbms_output.put_line('phoneno: '||temps.phoneno);, dbms_output.put_line('EMP_Salary:'||temps.salary);, END Loop;, CLOSE Cur_emp;, END;, /, --------------------------------------------------------------------------Stored Procedure:A PL/SQL procedure is a reusable unit that encapsulates, specific business logic of the application. Technically speaking, a, PL/SQL procedure is a named block stored as a schema object in the Oracle, Database.A procedure begins with a header that specifies its name and an, optional parameter list.Each parameter can be in either IN, OUT, or INOUT, mode. The parameter mode specifies whether a parameter can be read from, or write to., IN:This parameter is used for giving input to the subprograms.It is a, read-only variable inside the subprograms. Their values cannot be changed, inside the subprogram.In the calling statement, these parameters can be a, variable or a literal value or an expression, for example, it could be, the arithmetic expression like ‘5*8’ or ‘a/b’ where ‘a’ and ‘b’ are, variables.By default, the parameters are of IN type., OUT:This parameter is used for getting output from the subprograms.It is, a read-write variable inside the subprograms. Their values can be changed, inside the subprograms.In the calling statement, these parameters should, always be a variable to hold the value from the current subprograms., INOUT:This parameter is used for both giving input and for getting output, from the subprograms.It is a read-write variable inside the subprograms., Their values can be changed inside the subprograms.In the calling, statement, these parameters should always be a variable to hold the value, from the subprograms., Note that OR REPLACE option allows you to overwrite the current procedure, with the new code., PL/SQL procedure body, Similar to an anonymous block, the procedure body has three parts. The, executable part is mandatory whereas the declarative and exceptionhandling parts are optional. The executable part must contain at least, one executable statement., --------------------------------------------------------------------------------------set serveroutput on;, DECLARE, a number;, b number;, c number;, PROCEDURE findMin(x IN number, y IN number, z OUT number) IS, BEGIN, IF x < y THEN, z:= x;, ELSE, z:= y;, END IF;
Page 18 :
END;, BEGIN, a:= &a;, b:= &b;, findMin(a, b, c);, dbms_output.put_line(' Minimum of (a, b) : ' || c);, END;, /, --------------------------------------------------------------------------------------set serveroutput on;, DECLARE, a number;, PROCEDURE squareNum(x IN OUT number) IS, BEGIN, x := x * x;, END;, BEGIN, a:= &a;, squareNum(a);, dbms_output.put_line(' Square of (23): ' || a);, END;, /, --------------------------------------------------------------------------------------set serveroutput on;, CREATE OR REPLACE PROCEDURE print_contact(, p_customer_id NUMBER, ), IS, r_contact contacts%ROWTYPE;, BEGIN, -- get contact based on customer id, SELECT *, INTO r_contact, FROM contacts, WHERE customer_id = p_customer_id;, -- print out contact's information, dbms_output.put_line( r_contact.first_name || ' ' ||, r_contact.last_name || '<' || r_contact.email ||'>' );, EXCEPTION, WHEN OTHERS THEN, dbms_output.put_line( SQLERRM );, END;, /, EXEC print_contact(100);, DROP PROCEDURE print_contact;, -----------------------------------------------------------------------------------Stored Function:, Similar to a procedure, a PL/SQL function is a reusable program unit, stored as a schema object in the Oracle Database., The function header has the function name and a RETURN clause that, specifies the datatype of the returned value. Function can be called from, the procedure block of code.
Page 19 :
The function body is the same as the procedure body which has three, sections: declarative section, executable section, and exception-handling, section., The declarative section is between the IS and BEGIN keywords. It is where, you declare variables, constants, cursors, and user-defined types., The executable section is between the BEGIN and END keywords. It is where, you place the executable statements. Unlike a procedure, you must have at, least one RETURN statement in the executable statement., The exception-handling section is where you put the exception handler, code., Select Count(*) from EMPSTESTS where salary>15000;, CREATE OR REPLACE FUNCTION SAL_GRT_FUN, RETURN NUMBER IS, TOT_RECORDS NUMBER (10):=0;, BEGIN, SELECT COUNT(*) INTO TOT_RECORDS FROM EMPSTESTS where salary>15000;, RETURN TOT_RECORDS;, END;, /, SET SERVEROUTPUT ON;, DECLARE, N NUMBER(10);, BEGIN, N:=SAL_GRT_FUN();, DBMS_OUTPUT.PUT_LINE(N || 'NO OF RECORDS larger than 15000 salary');, END;, /, ------------------------------------------------------------------------DECLARE, a number;, b number;, c number;, FUNCTION findMax(x IN number, y IN number), RETURN number, IS, z number;, BEGIN, IF x > y THEN, z:= x;, ELSE, z:= y;, END IF;, RETURN z;, END;, BEGIN, a:= &a;, b:= &b;, c := findMax(a, b);, dbms_output.put_line(' Maximum in both: ' || c);, END;, /, -------------------------------------------------------------------------5!=5*4*3*2*1=120
Page 20 :
DECLARE, num number;, factorial number;, FUNCTION fact(x number), RETURN number, IS, f number;, BEGIN, IF x=0 THEN, f := 1;, ELSE, f := x * fact(x-1);, END IF;, RETURN f;, END;, BEGIN, num:= #, factorial := fact(num);, dbms_output.put_line(' Factorial '|| num || ' is ' || factorial);, END;, /, -------------------------------------------------------------------------create table section(s_id int, s_name varchar(20), strength int );, insert, insert, insert, insert, insert, , into, into, into, into, into, , section, section, section, section, section, , values(1,, values(2,, values(3,, values(4,, values(5,, , 'computer science', 20);, 'Electronics', 45);, 'IT', 60);, 'Security', 60);, 'RPA', 60);, , create or replace function totalStrength, -- Defining return type, return integer, as, total integer:=0;, begin, -- calculating the sum and storing it in total, select sum(strength) into total from section;, return total;, ---- closing function, end totalStrength;, set serveroutput on;, declare, answer integer;, begin, answer:=totalstrength();, dbms_output.put_line('Total strength of students is ' || answer);, end;, --------------------------------------------------------set serveroutput on;
Page 21 :
declare, a int;, c int;, n int;, rev int:=0;, r int;, --Defining function, function reverse_it( x, return int, as, z int;, , IN int), , -- function code, begin, n := x;, while (n > 0), loop, r := mod(n, 10);, rev := (rev * 10) + r;, n := trunc(n / 10);, end loop;, z := rev;, return z;, end, , ;, , BEGIN, a := 123456789;, c := reverse_it(a);, dbms_output.put_line('the reverse of number is, END;, ---------------------------------------------DROP FUNCTION fact;, , ' || c);
