Financial Year Oracle PLSQL Program

 CREATE OR REPLACE function FINANCIAL_YEAR(p_date DATE) return varchar2 IS

   v_first     varchar2(4);

   v_second    varchar2(4);

   v_year      varchar2(4);

   first_date  date;

   second_date date;

   third_date  date;

   four_date date;

   financial_year varchar2(9);

BEGIN

  v_year := to_char(p_date,'yyyy');

  first_date := to_date('01-APR-'||v_year);

  second_date := to_date('31-dec-'||v_year);

  

  third_date := to_date('01-JAN-'||v_year);

  four_date := to_date('31-MAR-'||v_year);

  

  IF p_date >= first_date AND  p_date <= second_date   THEN

        financial_year := to_char(v_year||'-'||to_number(v_year+1));

  END IF;

  

  IF p_date >= third_date AND  p_date <= four_date   THEN

        financial_year := to_char(to_number(v_year)-1||'-'||to_number(v_year));

  END IF;

 

  return financial_year;

END;

क्रमादेशन भाषा/संरचित पृच्छा भाषा पुस्तक की लिपि

CREATE TABLE vibhag

(   

  id         NUMBER(2,0),   

  naam       VARCHAR2(14),   

  sthan      VARCHAR2(13),   

  CONSTRAINTS pk_vibhag PRIMARY KEY (id)   

);

INSERT INTO vibhag(id,naam,sthan)

VALUES(10,'DHARM','KEDARNATH');

INSERT INTO vibhag(id,naam,sthan)

VALUES(20,'ARTH','SOMNATH');

INSERT INTO vibhag(id,naam,sthan)

VALUES(30,'KAAM','VISHWANATH');

INSERT INTO vibhag(id,naam,sthan)

VALUES(30,'MOKSHA','MAHAKAAL');

CREATE TABLE karmchari 

(   

  id             NUMBER(4,0),   

  naam           VARCHAR2(10),   

  karya          VARCHAR2(9),   

  adhikari       NUMBER(4,0),   

  pratham_dinank DATE,   

  vetan          NUMBER(7,2),   

  dastoor        NUMBER(7,2),   

  vibhagid       NUMBER(2,0),   

  CONSTRAINTS pk_karmchari PRIMARY KEY ( id),   

  CONSTRAINTS fk_vibhag FOREIGN  KEY (vibhagid) REFERENCES vibhag (id)   

);

INSERT INTO karmchari(id,naam,karya,adhikari,pratham_dinank,,vetan, dastoor, vibhagid)

VALUES(7839,'OMKAR','ADHYAKSH',NULL,to_date('17-11-1981','dd-mm-yyyy'), 5000, NULL, 10 ); 

INSERT INTO karmchari(id,naam,karya,adhikari,pratham_dinank,,vetan, dastoor, vibhagid) 

VALUES(7698, 'BRAMHA', 'VYAVASTHAP', 7839,   to_date('1-5-1981','dd-mm-yyyy'),   2850, NULL, 30  );

INSERT INTO karmchari(id,naam,karya,adhikari,pratham_dinank,,vetan, dastoor, vibhagid) 

VALUES(7782, 'VISHNU', 'VYAVASTHAP', 7839,   to_date('9-6-1981','dd-mm-yyyy'),   2450, NULL, 10  );

INSERT INTO karmchari(id,naam,karya,adhikari,pratham_dinank,,vetan, dastoor, vibhagid)  

VALUES(7566, 'MAHESH', 'VYAVASTHAP', 7839,   to_date('2-4-1981','dd-mm-yyyy'),   2975, NULL, 20  );

INSERT INTO karmchari(id,naam,karya,adhikari,pratham_dinank,,vetan, dastoor, vibhagid) 

VALUES(7788, 'KASHYAP', 'MUNI', 7566,   to_date('13-JUL-87','dd-mm-rr') - 85,   3000, NULL, 20  );

INSERT INTO karmchari(id,naam,karya,adhikari,pratham_dinank,,vetan, dastoor, vibhagid) 

VALUES(7902, 'PARASHAR', 'MUNI', 7566,  to_date('3-12-1981','dd-mm-yyyy'),   3000, NULL, 20 );

INSERT INTO karmchari(id,naam,karya,adhikari,pratham_dinank,,vetan, dastoor, vibhagid) 

VALUES(7369, 'VYAS', 'LIPIK', 7902,   to_date('17-12-1980','dd-mm-yyyy'),   800, NULL, 20  );

INSERT INTO karmchari(id,naam,karya,adhikari,pratham_dinank,,vetan, dastoor, vibhagid) 

VALUES(7499, 'MAHARISHIDAYANAND', 'PRACHARAK', 7698,   to_date('20-2-1981','dd-mm-yyyy'),   1600, 300, 30  );

INSERT INTO karmchari(id,naam,karya,adhikari,pratham_dinank,,vetan, dastoor, vibhagid)  

VALUES(7521, 'VIVEKANAND', 'PRACHARAK', 7698,   to_date('22-2-1981','dd-mm-yyyy'),   1250, 500, 30  );

INSERT INTO karmchari(id,naam,karya,adhikari,pratham_dinank,,vetan, dastoor, vibhagid)

VALUES(7654, 'KESHAV', 'PRACHARAK', 7698,   to_date('28-9-1981','dd-mm-yyyy'),   1250, 1400, 30  );

INSERT INTO karmchari(id,naam,karya,adhikari,pratham_dinank,,vetan, dastoor, vibhagid) 

VALUES(7844, 'MADHAV', 'PRACHARAK', 7698,   to_date('8-9-1981','dd-mm-yyyy'),   1500, 0, 30  );

INSERT INTO karmchari(id,naam,karya,adhikari,pratham_dinank,,vetan, dastoor, vibhagid) 

VALUES(7876, 'VALMEEKI', 'LIPIK', 7788,   to_date('13-JUL-87', 'dd-mm-rr') - 51,   1100, NULL, 20  );

INSERT INTO karmchari(id,naam,karya,adhikari,pratham_dinank,,vetan, dastoor, vibhagid) 

VALUES(7900, 'KAALIDAS', 'LIPIK', 7698,   to_date('3-12-1981','dd-mm-yyyy'),   950, NULL, 30  );

INSERT INTO karmchari(id,naam,karya,adhikari,pratham_dinank,,vetan, dastoor, vibhagid)

VALUES(7934, 'RAJSHEKHAR', 'LIPIK', 7782,   to_date('23-1-1982','dd-mm-yyyy'),   1300, NULL, 10  );

Database Connectivity ( NetBean Configuration)

Link: Download Connectivity Documents

Step-1:Configure Database in NetBeans 8.1

Step-2: Right click on database following  window will popup

Step-3:

Step-4:

Step-5:Give your username, password, service ID.

Step-6: Click Test Button to Check The Connectivity: It Connection is successful then message will be displayed in the screen.

Step-7:Select Schema (User Name)

Step-8:Connection String will be generated by NetBean

Step-9: 

Oracle - SQL - PLSQL - RDBMS

1.  Integrity Constraints

2.  Oracle Attributes,  Database Definition, Entity

3.  Oracle In-Built Function

4.  PL/SQL Programming - 1 , PL/SQL Programming - 2 

5.   Set Operation(Union,Intersect, Minus),  Oracle Exception  ,

6.   Cursor  Introduction , Explicit Cursor, Explicit Cursor Life Cycle , Explicit Cursor Attribute
     ,Implicit Cursor, Cursor Variable,

7.  Group By and Having,  Order By Statement


8. Notes for Installation:  Oracle Installation Guide 11g Database


Web Link
1. Triggers : Definition, Application, Type.